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>１. 日々の売上を追って課金者のサービスの利用状況に変化がないかをチェックする。"
comment += "<br>２．時系列で売上の推移が追えるので課金に有効な施策を行ったときの前後で変化があったなどを見る。</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", {})