def pulldown_menu(kpi_id): cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics") sql = "select distinct(app_id) as app_id from mnl_kpi_" + kpi_id log = cdg_db.db_anyselect(sql) app_ids = [] for v in log: app_ids.append(Applist(app_id=v["app_id"])) sql = "select distinct(os_type) as os_type from mnl_kpi_" + kpi_id log = cdg_db.db_anyselect(sql) os_types = [] for v in log: os_types.append(Oslist(os_type=v["os_type"])) sql = "select distinct(year) as year from mnl_kpi_" + kpi_id log = cdg_db.db_anyselect(sql) year = [] for v in log: year.append(Yearlist(year=v["year"])) month = [] for v in range(1, 13): month.append(Monthlist(month=str(v))) date = [] for v in range(1, 32): date.append(Datelist(date=str(v))) return app_ids, os_types, year, month, date
def get_rr(lower, upper): cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics") sql = "select app_id, os_type, " sql += "avg(rr_001) as rr_001, avg(rr_007) as rr_007 " sql += "from mnl_kpi_rr where yyyymmdd >= " sql += str(lower) + " and yyyymmdd < " + str(upper) + " group by app_id, os_type" log = cdg_db.db_anyselect(sql) return log
def get_cvr(lower, upper): cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics") sql = "select app_id, os_type, " sql += "avg(firstdaytrial) as ft, avg(firstdaysubscription) as fs, avg(subscription01) as s01, avg(trial2subscription) as t2s, " sql += "avg(trial2subscription02) as t2s02, avg(trial2subscription03) as t2s03 " sql += "from mnl_kpi_cvr where yyyymmdd >= " sql += str(lower) + " and yyyymmdd < " + str(upper) + " group by app_id, os_type" log = cdg_db.db_anyselect(sql) return log
def pulldown_menu_yyyymm(kpi_id): cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics") sql = "select distinct(year) as year from mnl_kpi_" + kpi_id log = cdg_db.db_anyselect(sql) year = [] for v in log: year.append(Yearlist(year=v["year"])) month = [] for v in range(1, 13): month.append(Monthlist(month=str(v))) return year, month
import sys sys.path.append('/home/takagi/git/analytics/analytics_tool') import utility.db_utility as db_util from datetime import datetime as dt import commands argvs = sys.argv lower = int(argvs[1]) print lower #DB ##########kidsnbooks #access log kidsnbooks_log = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics") where = "date = " + str(lower) access_log = kidsnbooks_log.db_select("*", "analytics_metalog", where, None) summary = {} for v in access_log: summary.setdefault(v["uid"], {}) summary[v["uid"]].setdefault(v["app_id"], {}) summary[v["uid"]][v["app_id"]].setdefault(v["os_type"], {}) summary[v["uid"]][v["app_id"]][v["os_type"]].setdefault("regdate", v["regdate"]) summary[v["uid"]][v["app_id"]][v["os_type"]].setdefault("moddate", v["date"]) #output fname = "/home/takagi/git/analytics/analytics_tool/data_temp/lastsessionuu" + str(lower) out = open(fname, 'w') for uid, app in summary.items():
import utility.device_master as dev_master user_data = {} # DB db = { "meta": { "server": "inhousedb.smartsrv.in", "db_name": "statistics", "table": ["mnl_metalog"], "colum": "id,created", } } # 課金データを取得 logdb = db_util.db_command("takagi", "7TgYiaI9", db["meta"]["server"], db["meta"]["db_name"]) start = datetime.datetime(2013, 6, 25, 0, 0, 0, 0) one_day = datetime.timedelta(days=1) end = start + one_day user_data = {} for i in range(130): s = start + i * one_day e = end + i * one_day month = s.strftime("%Y-%m") s = s.strftime("%Y%m%d") e = e.strftime("%Y%m%d") where = " where date = " + s + " and app_id = 'kidsnbooks' and sub_pay > 0" print where sql = "select uid, app_id, os_type, sub_pay from mnl_metalog" + where log = logdb.db_anyselect(sql) for v in log:
def growthrate(request): if not request.user.is_authenticated(): return render_to_response('servicekpi/index.html', {}, context_instance=RequestContext(request)) else: if request.POST: wb = Workbook() c_border = xlwt.Borders() c_border.top = xlwt.Borders.THICK c_border.bottom = xlwt.Borders.THICK c_border.left = xlwt.Borders.THICK c_border.right = xlwt.Borders.THICK font0 = xlwt.Font() font0.name = 'Osaka' c_style = xlwt.XFStyle() c_style.borders = c_border c_style.font = font0 year, month = int(request.POST["sy"]), int(request.POST["sm"]) start0 = str(year) + str(month).zfill(2) cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics") sql = "select * " sql += "from mnl_kpi_monthly_subscription where date <= " sql += str(start0) log = cdg_db.db_anyselect(sql) data = {} for v in log: data.setdefault(v["app_id"], {}) data[v["app_id"]].setdefault(v["os_type"], {}) data[v["app_id"]][v["os_type"]].setdefault(v["regdate"], {}) data[v["app_id"]][v["os_type"]][v["regdate"]].setdefault(v["date"], float(v["uu"])) ws = [] i = 0 for app_id, d0 in data.items(): for os, d1 in d0.items(): j = 0 ws.append(wb.add_sheet(str(app_id) + "_" + str(os))) total = {} for regdate, d2 in sorted(d1.items()): k = j ws[i].write(j + 1, 0, regdate, c_style) for date, uu in sorted(d2.items()): if k == j: ws[i].write(0, k + 1, date, c_style) ws[i].write(j + 1, k + 1, uu, c_style) total.setdefault(date, {}) total[date].setdefault("uu", 0) if k == j: total[date].setdefault("col", k + 1) total[date]["uu"]+= uu k += 1 j += 1 ws[i].write(j + 1, 0, u"合計", c_style) ws[i].write(j + 2, 0, u"成長率", c_style) p = 0 for key, value in sorted(total.items()): try: col = value["col"] except: col += 1 ws[i].write(j + 1, col, value["uu"], c_style) g = 100 * value["uu"] / p if p != 0 else "NONE" ws[i].write(j + 2, col, g, c_style) p = value["uu"] i += 1 file_name = 'growthrate_' + str(start0) + '.xls' response = HttpResponse(mimetype='application/ms-excel') response['Content-Disposition'] = 'attachment; filename=%s' % file_name wb.save(response) return response else: year, month = udf.pulldown_menu_yyyymm("cvr") context = RequestContext(request, { 'user': User, "year" : year, "month" : month, }) return render_to_response('servicekpi/growthrate.html', {}, context_instance=context)
argvs = sys.argv install_date = int(argvs[1].replace("-", "")) user_data = {} #DB db = { "meta" : { "server" : "inhousedb.smartsrv.in", "db_name" : "statistics", "table" : ["mnl_subscriptionlog_fast"], "colum" : "*" }, } #インストールユーザーを抽出する logdb = db_util.db_command("takagi", "7TgYiaI9", db["meta"]["server"], db["meta"]["db_name"]) sql = "select * from mnl_subscriptionlog_fast where sub_pay > 0 and date = " + str(install_date) log = logdb.db_anyselect(sql) for v in log: month = round(float(v["is_sub"]) / 30) #created異常のユーザー回避 if month == 0: month = 1 price = float(v["sub_pay"]) / float(month) user_data.setdefault(v["uid"], {}) user_data[v["uid"]].setdefault(v["app_id"], {}) user_data[v["uid"]][v["app_id"]].setdefault("month", month) user_data[v["uid"]][v["app_id"]].setdefault("price", price) user_data[v["uid"]][v["app_id"]].setdefault("date", []) for i in range(0, int(month)):
}, "access" : { "server" : "apps.db.smartsrv.jp", "db_name" : "smartapps_log", "table" : ["Access_201310",], "where" : "application = 122" }, "play" : { "server" : "apps.db.smartsrv.jp", "db_name" : "smartapps_log", "table" : ["Play_201310","Play_201311"], "where" : "(application = 122 or application = 121) and is_start = 0 and date > '2013-10-22'" }, } #インストールユーザーを抽出する logdb = db_util.db_command("takagi", "7TgYiaI9", db["users"]["server"], db["users"]["db_name"]) for table in db["users"]["table"]: sql = "select user_id from " sql += table + " where " + db["users"]["where"] log = logdb.db_anyselect(sql) for v in log: user_data.setdefault(v["user_id"], {}) user_data[v["user_id"]].setdefault("iOS", []) user_data[v["user_id"]].setdefault("Android", []) #プレイログを読む logdb = db_util.db_command("takagi", "7TgYiaI9", db["play"]["server"], db["play"]["db_name"]) for table in db["play"]["table"]: sql = "select user_id, content_id, type, extra_val_01, application from "
"Goccozoo-iOS" : {"app_id" : "goccozoo", "os_type" : "os_type != 'Android'", "application" : "goccozoo", "pay" : "and (name = 'sales_contents')"}, } #全体の売上 rec = {"売上" : 0, "サブスクリプション売上" : 0, "従量売上" : 0, "当月累計売上" : 0, "先月同日比" : 0, "当月着地見込み" : 0 } logdb = db_util.db_command("takagi", "7TgYiaI9", db["revenue"]["server"], db["revenue"]["db_name"]) #今日の売上 sql = "select name, sum(cnt) as cnt from analyze_summary " sql += "where " sql += "date = '" + date + "' " sql += "and name like '%sales%' and country = ''" log = logdb.db_anyselect(sql) for v in log: rec["売上"] += float(v["cnt"]) #今日のサブスクリプション売上 sql = "select name, sum(cnt) as cnt from analyze_summary " sql += "where " sql += "date = '" + date + "' " sql += "and name = 'sales_subscription' and country = ''" log = logdb.db_anyselect(sql)
sys.path.append("/home/takagi/git/analytics/analytics_tool") import utility.db_utility as db_util from datetime import datetime as dt import commands import utility.device_master as dev_master argvs = sys.argv lower = "2013-07-28" upper = "2013-08-05" print lower, upper # DB ##########kidsnbooks kidsnbooks = db_util.db_command("takagi", "7TgYiaI9", "kidsnbooksdb.smartsrv.jp", "kidsnbooks") user_data = {} # access log where = "created >= '" + lower + " 00:00:00' and created < '" + upper + " 00:00:00'" access_log = kidsnbooks.db_select( "user_id,subscription_id,application,status,created", "subscription_users", where, None ) user_data = {} for v in access_log: dev = "Android" if v["application"] == "31" else "iOS" tdt = dt.strptime(v["created"], "%Y-%m-%d %H:%M:%S") tdtf = tdt.strftime("%Y%m%d") user_data.setdefault(v["user_id"], {}) user_data[v["user_id"]].setdefault("device", dev) user_data[v["user_id"]].setdefault("status", v["status"])
"user" : { "server" : "kidsnbooksdb.smartsrv.jp", "db_name" : "kidsnbooks", "table" : ["users"], "colum" : "id,created" }, "play" : { "server" : "log.kidsnbooksdb.smartsrv.jp", "db_name" : "kidsnbooks_log", "table" : ["play_log"], "colum" : "id,is_start,book_id,created" }, } #インストールデータを日別に取得する logdb = db_util.db_command("smartedu", "nakadama", db["user"]["server"], db["user"]["db_name"]) sql = "select id,created,platform_full from users where created >= '2013-06-01 00:00:00'" log = logdb.db_anyselect(sql) for v in log: created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S') created = created.strftime('%Y%m%d') user_data.setdefault(v["id"], {}) user_data[v["id"]].setdefault("regdate", created) dev = dev_master.os_name(v["platform_full"]) user_data[v["id"]].setdefault("device", dev) user_data[v["id"]].setdefault("book_start", None) user_data[v["id"]].setdefault("book_end", None) user_data[v["id"]].setdefault("start", None) user_data[v["id"]].setdefault("end", None) user_data[v["id"]].setdefault("book_num", 0)
def get_data(sql): cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics") log = cdg_db.db_anyselect(sql) return log
db = { "smartapps" : { "server" : "apps.db.smartsrv.jp", "db_name" : "smartapps_user", "table" : ["Users_0", "Users_1", "Users_2", "Users_3", "Users_4", "Users_5", "Users_6", "Users_7", "Users_8", "Users_9",], "colum" : "user_id as id, application, created" }, } kdmode = {"server" : "apps.db.smartsrv.jp", "db_name" : "kdmode" } user_data = {} #ユーザーデータの取得 for app_id, server in db.items(): logdb = db_util.db_command("smartedu", "nakadama", server["server"], server["db_name"]) tables = server["table"] for table in tables: where = "application = 65" log = logdb.db_select(server["colum"], table, where, None) am = app_master.app_master() for v in log: if v["created"] != "NULL" and am.has_key(v["application"]): created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S') created = created.strftime('%Y%m%d') user_data.setdefault(v["id"], {}) user_data[v["id"]].setdefault("regdate", created) user_data[v["id"]].setdefault("apps", {}) user_data[v["id"]].setdefault("install", {}) user_data[v["id"]].setdefault("add_on_video", {})
}, "smartapps" : { "server" : "apps.db.smartsrv.jp", "db_name" : "smartapps_user", "table" : ["Users_0", "Users_1", "Users_2", "Users_3", "Users_4", "Users_5", "Users_6", "Users_7", "Users_8", "Users_9",], "colum" : "user_id as id, application, created" }, } ##########kidsnbooks for app_id, server in db.items(): logdb = db_util.db_command("smartedu", "nakadama", server["server"], server["db_name"]) tables = server["table"] for table in tables: user_data = {} #subscription log log = logdb.db_select(server["colum"], table, None, None) am = app_master.app_master() for v in log: if app_id != "smartapps": if v["created"] != "NULL": created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S') created = created.strftime('%Y%m%d') user_data.setdefault(v["id"], {}) user_data[v["id"]].setdefault(app_id, {}) user_data[v["id"]][app_id].setdefault("regdate", created) else:
"table" : ["play_log"], "colum" : "id,is_start,book_id,created" }, } #インストールデータを日別に取得する #本読みデータを取得 logdb = db_util.db_command("smartedu", "nakadama", db["play"]["server"], db["play"]["db_name"]) metadb = db_util.db_command("takagi", "7TgYiaI9", db["meta"]["server"], db["meta"]["db_name"]) start = datetime.datetime(2013, 6, 25, 0, 0, 0, 0) one_day = datetime.timedelta(days=1) end = start + one_day sql = "select * from analytics_metalog where app_id = 'kidsnbooks' and is_sub > 0" log = metadb.db_anyselect(sql) for v in log: user_data.setdefault(v["uid"], {}) user_data[v["uid"]].setdefault("sub_day", {}) user_data[v["uid"]]["sub_day"].setdefault(v["date"], 0) for i in range(110): s = start + i * one_day e = end + i * one_day
if summary_app_name == 'rhythmonstage': title = 'リズムオンステージ' app_analyze = {'iOS' : 'rhythmonstage', 'Android' : 'rhythmonstageandroid'} if summary_app_name == 'goccozoo': title = 'Goccozoo' app_analyze = {'iOS' : 'goccozoo', 'Android' : 'goccozooandroid'} index_tab = {} db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics") #売上 comment = "<div>定義:<br>サービス個別の商品セグメントで分類したデイリーの売上<br>使い方:" comment += "<br>1. 日々の売上を追って課金者のサービスの利用状況に変化がないかをチェックする。" comment += "<br>2.時系列で売上の推移が追えるので課金に有効な施策を行ったときの前後で変化があったなどを見る。</div><br>" log = [] record = {} for os, app in app_analyze.items(): sql = "select date, name, sum(cnt) as cnt" sql += ' from analyze_summary where ' sql += "date <= '" + str(date_end) + "' and date >= '" + str(date_start) + "' " sql += " and application = '" + app + "'" + " and name like '%sales%' and country = '' group by date, name, application" log_data = db.db_anyselect(sql) for v in log_data:
db = { "sub" : { "server" : "log.kidsnbooksdb.smartsrv.jp", "db_name" : "kidsnbooks_log", "table" : "subscription_log", "colum" : "user_id,created,expiration_date" }, "tra" : { "server" : "log.kidsnbooksdb.smartsrv.jp", "db_name" : "kidsnbooks_log", "table" : "subscription_play_log", "colum" : "user_id,created,book_id" }, } logdb = db_util.db_command("smartedu", "nakadama", db["sub"]["server"], db["sub"]["db_name"]) table = db["sub"]["table"] #subscription log where = "created >= '2013-07-20 00:00:00' and created < '2013-08-05 00:00:00'" log = logdb.db_select(db["sub"]["colum"], table, where, None) for v in log: created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S') created2 = datetime.datetime(created.year, created.month, created.day, 0, 0, 0, 0) created2 = created.strftime('%Y%m%d') expire = dt.strptime(v["expiration_date"] + " 00:00:00", '%Y-%m-%d %H:%M:%S') if (expire -created).days >= 14: user_data.setdefault(v["user_id"] , {}) user_data[v["user_id"]].setdefault("regdate", created2) user_data[v["user_id"]].setdefault("books", {})