def rr(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
kpi_id = "rr"
label = [u"日にち", u"1日後", u"3日後", u"7日後", u"１４日後", u"３０日後", u"60日後", u"９０日後",
u"１８０日後", u"３６０日後", u"７２０日後"]
#タブメニューの中身を検索する
app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id)
#タブメニューで選択期間を取得する
duration, start, end = udf.search_target(request)
#選択期間のデータを取得する
try:
sql = "select yyyymmdd, app_id, os_type, "
sql += "rr_001, rr_003, rr_007, rr_014, rr_030, rr_060, rr_090, rr_180, rr_360, rr_720 "
sql += ' from mnl_kpi_rr where '
sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' '
sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os
sql += "' group by yyyymmdd, app_id, os_type"
log = udf.get_data(sql)
except:
log = []
#選択期間の表を作成する
data = []
head = []
l = Rr(yyyymmdd = label[0], d001 = label[1], d003 = label[2],
d007 = label[3], d014 = label[4], d030 = label[5],
d060 = label[6], d090 = label[7], d180 = label[8],
d360 = label[9], d720 = label[10])
head.append(l)
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
d = Rr(yyyymmdd = v["yyyymmdd"],
d001 = str(fmt.float1(v["rr_001"])),
d003 = str(fmt.float1(v["rr_003"])),
d007 = str(fmt.float1(v["rr_007"])),
d014 = str(fmt.float1(v["rr_014"])),
d030 = str(fmt.float1(v["rr_030"])),
d060 = str(fmt.float1(v["rr_060"])),
d090 = str(fmt.float1(v["rr_090"])),
d180 = str(fmt.float1(v["rr_180"])),
d360 = str(fmt.float1(v["rr_360"])),
d720 = str(fmt.float1(v["rr_720"])))
data.append(d)
#選択期間のグラフを生成する
graph_data = "["
x = []
for l in label:
x.append("[" + '"' + l + '",')
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
if int(v["yyyymmdd"]) % 100 == 1:
x[0] += v["yyyymmdd"] + ","
else:
x[0] += ","
x[1] += v["rr_001"] + ","
x[2] += v["rr_003"] + ","
x[3] += v["rr_007"] + ","
x[4] += v["rr_014"] + ","
x[5] += v["rr_030"] + ","
x[6] += v["rr_060"] + ","
x[7] += v["rr_090"] + ","
x[8] += v["rr_180"] + ","
x[9] += v["rr_360"] + ","
x[10] += v["rr_720"] + ","
for i in range(0,11):
y = x[i]
x[i] = y[:-1]
x[i] += "]"
graph_data += x[i] + ","
graph_data = graph_data[:-1]
graph_data += "]"
graphconfig = []
graphconfig.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth'))
context = RequestContext(request, {
'user': User,
"app_id" : app_ids,
"os" : os_types,
"year" : year,
"month" : month,
"date" : date,
"duration" : duration,
"label" : head,
"data" : data,
"graphconfig" : graphconfig,
})
return render_to_response('servicekpi/rr.html', {},
context_instance=context)
def basickpi(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
kpi_id = "arpu"
label = [u"日にち", u"ARPU", u"ARPPU", u"課金率"]
#タブメニューの中身を検索する
app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id)
#タブメニューで選択期間を取得する
duration, start, end = udf.search_target(request)
#選択期間のデータを取得する
try:
sql = "select yyyymmdd, app_id, os_type, arpu, arppu, cvr"
sql += ' from mnl_kpi_basickpi where '
sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' '
sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os
sql += "'"
log = udf.get_data(sql)
except:
log = []
#選択期間の表を作成する
data = []
head = []
l = Arpu(yyyymmdd = label[0], arpu = label[1], arppu = label[2],
cvr = label[3])
head.append(l)
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
d = Arpu(yyyymmdd = v["yyyymmdd"],
arpu = str(fmt.float1(v["arpu"])),
arppu = str(fmt.float1(v["arppu"])),
cvr = str(fmt.float1(v["cvr"])))
data.append(d)
#選択期間のグラフを生成する
graph_data = "["
x = []
i = 0
for l in label:
if i <= 1:
x.append("[" + '"' + l + '",')
i += 1
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
if int(v["yyyymmdd"]) % 100 == 1:
x[0] += v["yyyymmdd"] + ","
else:
x[0] += ","
x[1] += v["arpu"] + ","
for i in range(0,2):
y = x[i]
x[i] = y[:-1]
x[i] += "]"
graph_data += x[i] + ","
graph_data = graph_data[:-1]
graph_data += "]"
graphconfig0 = []
graphconfig0.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth'))
graph_data = "["
x = []
i = 0
for l in label:
if i == 0 or i == 2:
x.append("[" + '"' + l + '",')
i += 1
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
if int(v["yyyymmdd"]) % 100 == 1:
x[0] += v["yyyymmdd"] + ","
else:
x[0] += ","
x[1] += v["arppu"] + ","
for i in range(0,2):
y = x[i]
x[i] = y[:-1]
x[i] += "]"
graph_data += x[i] + ","
graph_data = graph_data[:-1]
graph_data += "]"
graphconfig1 = []
graphconfig1.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth'))
graph_data = "["
x = []
i = 0
for l in label:
if i == 0 or i == 3:
x.append("[" + '"' + l + '",')
i += 1
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
if int(v["yyyymmdd"]) % 100 == 1:
x[0] += v["yyyymmdd"] + ","
else:
x[0] += ","
x[1] += v["cvr"] + ","
for i in range(0,2):
y = x[i]
x[i] = y[:-1]
x[i] += "]"
graph_data += x[i] + ","
graph_data = graph_data[:-1]
graph_data += "]"
graphconfig2 = []
graphconfig2.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth'))
context = RequestContext(request, {
'user': User,
"app_id" : app_ids,
"os" : os_types,
"year" : year,
"month" : month,
"date" : date,
"duration" : duration,
"label" : head,
"data" : data,
"graphconfig0" : graphconfig0,
"graphconfig1" : graphconfig1,
"graphconfig2" : graphconfig2,
})
return render_to_response('servicekpi/basickpi.html', {},
context_instance=context)
def cvr(request):
if not request.user.is_authenticated():
return render_to_response("servicekpi/index.html", {}, context_instance=RequestContext(request))
else:
kpi_id = "cvr"
label = [
u"日にち",
u"当日無料登録率",
u"無料体験登録率",
u"当日課金登録率",
u"１ヶ月課金率",
u"２ヶ月課金率",
u"３が月課金率",
u"６ヶ月課金率",
u"１２ヶ月課金率",
u"１３ヶ月課金率",
u"おためし有料化率",
u"有料２ヶ月継続率",
u"有料３ヶ月継続率",
u"有料６ヶ月継続率",
u"有料１２ヶ月継続率",
u"有料１３ヶ月継続率",
]
# タブメニューの中身を検索する
app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id)
# タブメニューで選択期間を取得する
duration, start, end = udf.search_target(request)
# 選択期間のデータを取得する
try:
sql = "select yyyymmdd, app_id, os_type, "
sql += "trial, firstdaytrial, firstdaysubscription, subscription01, subscription02, subscription03, subscription06, subscription12, subscription13, trial2subscription, "
sql += "trial2subscription02, trial2subscription03, trial2subscription06, trial2subscription12, trial2subscription13 "
sql += " from mnl_kpi_cvr where "
sql += "yyyymmdd <= " + str(end) + " and yyyymmdd >= " + str(start) + " "
sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os
sql += "'"
log = udf.get_data(sql)
except:
log = []
# 選択期間の表を作成する
data = []
head = []
l = Cvr(
yyyymmdd=label[0],
trial_today=label[1],
trial=label[2],
pay_today=label[3],
pay_01=label[4],
pay_02=label[5],
pay_03=label[6],
pay_06=label[7],
pay_12=label[8],
pay_13=label[9],
trial2pay=label[10],
trial2pay02=label[11],
trial2pay03=label[12],
trial2pay06=label[13],
trial2pay12=label[14],
trial2pay13=label[15],
)
head.append(l)
for v in sorted(log, key=lambda x: x["yyyymmdd"]):
d = Cvr(
yyyymmdd=v["yyyymmdd"],
trial_today=str(fmt.float1(v["firstdaytrial"])),
trial=str(fmt.float1(v["trial"])),
pay_today=str(fmt.float1(v["firstdaysubscription"])),
pay_01=str(fmt.float1(v["subscription01"])),
pay_02=str(fmt.float1(v["subscription02"])),
pay_03=str(fmt.float1(v["subscription03"])),
pay_06=str(fmt.float1(v["subscription06"])),
pay_12=str(fmt.float1(v["subscription12"])),
pay_13=str(fmt.float1(v["subscription13"])),
trial2pay=str(fmt.float1(v["trial2subscription"])),
trial2pay02=str(fmt.float1(v["trial2subscription02"])),
trial2pay03=str(fmt.float1(v["trial2subscription03"])),
trial2pay06=str(fmt.float1(v["trial2subscription06"])),
trial2pay12=str(fmt.float1(v["trial2subscription12"])),
trial2pay13=str(fmt.float1(v["trial2subscription13"])),
)
data.append(d)
# 選択期間のグラフを生成する
graph_data = "["
x = []
i = 0
for l in label:
if i <= 9:
x.append("[" + '"' + l + '",')
i += 1
for v in sorted(log, key=lambda x: x["yyyymmdd"]):
if int(v["yyyymmdd"]) % 100 == 1:
x[0] += v["yyyymmdd"] + ","
else:
x[0] += ","
x[1] += v["firstdaytrial"] + ","
x[2] += v["trial"] + ","
x[3] += v["firstdaysubscription"] + ","
x[4] += v["subscription01"] + ","
x[5] += v["subscription02"] + ","
x[6] += v["subscription03"] + ","
x[7] += v["subscription06"] + ","
x[8] += v["subscription12"] + ","
x[9] += v["subscription13"] + ","
length = len(x)
for i in range(0, length):
y = x[i]
x[i] = y[:-1]
x[i] += "]"
graph_data += x[i] + ","
graph_data = graph_data[:-1]
graph_data += "]"
graphconfig0 = []
graphconfig0.append(Graphconfig(graph="line", width="1", data=graph_data, type="lineWidth"))
graph_data = "["
x = []
i = 0
for l in label:
if i >= 10 or i == 0:
x.append("[" + '"' + l + '",')
i += 1
for v in sorted(log, key=lambda x: x["yyyymmdd"]):
if int(v["yyyymmdd"]) % 100 == 1:
x[0] += v["yyyymmdd"] + ","
else:
x[0] += ","
x[1] += v["trial2subscription"] + ","
x[2] += v["trial2subscription02"] + ","
x[3] += v["trial2subscription03"] + ","
x[4] += v["trial2subscription06"] + ","
x[5] += v["trial2subscription12"] + ","
x[6] += v["trial2subscription13"] + ","
length = len(x)
for i in range(0, length):
y = x[i]
x[i] = y[:-1]
x[i] += "]"
graph_data += x[i] + ","
graph_data = graph_data[:-1]
graph_data += "]"
graphconfig1 = []
graphconfig1.append(Graphconfig(graph="line", width="1", data=graph_data, type="lineWidth"))
context = RequestContext(
request,
{
"user": User,
"app_id": app_ids,
"os": os_types,
"year": year,
"month": month,
"date": date,
"duration": duration,
"label": head,
"data": data,
"graphconfig0": graphconfig0,
"graphconfig1": graphconfig1,
},
)
return render_to_response("servicekpi/cvr.html", {}, context_instance=context)
comment += "<br>２. 変化の原因は施策による内部要因と市場環境やプラットフォーマーの仕様変更などの外部要因が考えられるので、変化があった場合には数字をもとに仮説を出す"
comment += "<br>３．特にN日後からM日後で落差が大きい場合にはそこになにかしらユーザーが飽きる要因が隠されているので注意する。<br></div><br>"
sql = "select yyyymmdd, app_id, os_type, "
sql += "rr_001, rr_003, rr_007, rr_014, rr_030, rr_060, rr_090, rr_180, rr_360, rr_720 "
sql += ' from mnl_kpi_rr where '
sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' '
sql += " and app_id = '" + summary_app_name + "'"
log = db.db_anyselect(sql)
label = ["日にち", "１日後", "３日後", "７日後", "１４日後", "３０日後", "６０日後", "９０日後", "１８０日後",
"３６０日後", "７２０日後"]
data = {}
graph = {}
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
#テーブル用
data.setdefault(v["os_type"], [])
row = [v["yyyymmdd"], str(fmt.float1(v["rr_001"])),
str(fmt.float1(v["rr_003"])),
str(fmt.float1(v["rr_007"])), str(fmt.float1(v["rr_014"])),
str(fmt.float1(v["rr_030"])), str(fmt.float1(v["rr_060"])),
str(fmt.float1(v["rr_090"])), str(fmt.float1(v["rr_180"])),
str(fmt.float1(v["rr_360"])), str(fmt.float1(v["rr_720"]))]
data[v["os_type"]].append(row)
graph.setdefault(v["os_type"], {})
for l in label:
h = '"' + l + '"'
graph[v["os_type"]].setdefault(l, [h,])
if int(v["yyyymmdd"]) % 100 == 1:
yyyymmdd = v["yyyymmdd"]
else:
yyyymmdd = ""
graph[v["os_type"]]["日にち"].append(yyyymmdd)
def monthlykpibytitle(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
#タブメニューの中身を検索する
kpi_id = "arpu"
app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id)
#タブメニューで選択期間を取得する
duration = udf.search_target_min(request)
label = [u"日にち", u"MAU", u"課金UU", u"新規登録数", u"サブスクリプション課金UU",
u"従量課金UU", u"売上", u"サブスクリプション売上",
u"従量売上", u"ARPU", u"ARPPU", u"課金率"]
#選択期間のデータを取得する
try:
sql = "select * from mnl_kpi_monthly_by_title where "
sql += "app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os + "'"
log = udf.get_data(sql)
except:
log = []
#選択期間の表を作成する
data = []
head = []
l = Monthlykpi(yyyymm = label[0],
mau= label[1], puu = label[2], nuu = label[3], subscription_puu = label[4],
item_puu = label[5], revenue = label[6], subscription_revenue = label[7],
item_revenue = label[8], arpu = label[9], arppu = label[10],
cvr = label[11])
head.append(l)
for v in sorted(log, key = lambda x:x["yyyymm"]):
d = Monthlykpi(yyyymm = v["yyyymm"],
mau = fmt.camma(int(v["mau"])),
puu = fmt.camma(int(v["puu"])),
nuu = fmt.camma(int(v["nuu"])),
subscription_puu = fmt.camma(int(v["subscription_puu"])),
item_puu = fmt.camma(int(v["item_puu"])),
revenue = fmt.camma(int(float(v["revenue"]))),
subscription_revenue = fmt.camma(int(float(v["subscription_revenue"]))),
item_revenue = fmt.camma(int(float(v["item_revenue"]))),
arpu = fmt.float1(float(v["arpu"])),
arppu = fmt.float1(float(v["arppu"])),
cvr = fmt.float1(float(v["cvr"])))
data.append(d)
#選択期間のグラフを生成する
graph_data = "["
x = []
i = 0
for l in label:
if l == u"日にち" or l == u"MAU":
x.append("[" + '"' + l + '",')
i += 1
for v in sorted(log, key = lambda x:x["yyyymm"]):
if (int(v["yyyymm"]) % 100) % 3 == 1:
x[0] += v["yyyymm"] + ","
else:
x[0] += ","
x[1] += v["mau"] + ","
length = len(x)
for i in range(0,length):
y = x[i]
x[i] = y[:-1]
x[i] += "]"
graph_data += x[i] + ","
graph_data = graph_data[:-1]
graph_data += "]"
graphconfig0 = []
length = str(90 / (len(log) + 1))
graphconfig0.append(Graphconfig(graph="stacked", width = length, data = graph_data, type = 'barWidth'))
graph_data = "["
x = []
i = 0
for l in label:
if l == u"日にち" or l == u"サブスクリプション売上" or l == u'従量売上':
x.append("[" + '"' + l + '",')
i += 1
for v in sorted(log, key = lambda x:x["yyyymm"]):
if (int(v["yyyymm"]) % 100) % 3 == 1:
x[0] += v["yyyymm"] + ","
else:
x[0] += ","
x[1] += v["subscription_revenue"] + ","
x[2] += v["item_revenue"] + ","
length = len(x)
for i in range(0,length):
y = x[i]
x[i] = y[:-1]
x[i] += "]"
graph_data += x[i] + ","
graph_data = graph_data[:-1]
graph_data += "]"
graphconfig1 = []
length = str(90 / (len(log) + 1))
graphconfig1.append(Graphconfig(graph="stacked", width = length, data = graph_data, type = 'barWidth'))
context = RequestContext(request, {
'user': User,
"app_id" : app_ids,
"os" : os_types,
"year" : year,
"month" : month,
"date" : date,
"duration" : duration,
"label" : head,
"data" : data,
"graphconfig0" : graphconfig0,
"graphconfig1" : graphconfig1,
})
return render_to_response('servicekpi/monthlykpibytitle.html', {},
context_instance=context)