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 regdatedau(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
kpi_id = "regdatedau"
label = [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 += "sum(today) as today, sum(under_1_month) as under_1_month, sum(under_2_month) as under_2_month"
sql += ", sum(under_3_month) as under_3_month, sum(under_6_month) as under_6_month, sum(under_12_month) as under_12_month, sum(over_1_year) as over_1_year "
sql += ' from mnl_kpi_regdatedau 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 = Regdatedau(yyyymmdd = label[0], today = label[1], one_month = label[2],
two_month = label[3], three_month = label[4], six_month = label[5],
one_year = label[6], over_year = label[7], sum = label[8])
head.append(l)
s0, s1, s2, s3, s4, s5, s6 = 0, 0, 0, 0, 0, 0, 0
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
s = int(v["today"]) + int(v["under_1_month"]) + int(v["under_2_month"]) + int(v["under_3_month"])
s += int(v["under_6_month"]) + int(v["under_12_month"]) + int(v["over_1_year"])
d = Regdatedau(yyyymmdd = v["yyyymmdd"],
today = fmt.camma(int(v["today"])),
one_month = fmt.camma(int(v["under_1_month"])),
two_month = fmt.camma(int(v["under_2_month"])),
three_month = fmt.camma(int(v["under_3_month"])),
six_month = fmt.camma(int(v["under_6_month"])),
one_year = fmt.camma(int(v["under_12_month"])),
over_year = fmt.camma(int(v["over_1_year"])),
sum = fmt.camma(s))
s0 += int(v["today"])
s1 += int(v["under_1_month"])
s2 += int(v["under_2_month"])
s3 += int(v["under_3_month"])
s4 += int(v["under_6_month"])
s5 += int(v["under_12_month"])
s6 += int(v["over_1_year"])
data.append(d)
d = Regdatedau(yyyymmdd = "SUM",
today = fmt.camma(s0),
one_month = fmt.camma(s1),
two_month = fmt.camma(s2),
three_month = fmt.camma(s3),
six_month = fmt.camma(s4),
one_year = fmt.camma(s5),
over_year = fmt.camma(s6),
sum = fmt.camma(s0 + s1 + s2 + s3 + s4 + s5 + s6))
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["today"] + ","
x[2] += v["under_1_month"] + ","
x[3] += v["under_2_month"] + ","
x[4] += v["under_3_month"] + ","
x[5] += v["under_6_month"] + ","
x[6] += v["under_12_month"] + ","
x[7] += v["over_1_year"] + ","
for i in range(0,8):
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="stacked", width = "2", 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,
"graphconfig" : graphconfig,
})
return render_to_response('servicekpi/regdatedau.html', {},
context_instance=context)
def revenuedetail(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
kpi_id = "logindaysdau"
label = [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 += "item_name, ctg_name, revenue, puu "
sql += ' from mnl_kpi_revenue 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 = []
#選択期間の表を作成する
#データを整形する
record = {}
table_data = []
table = []
for v in log:
record.setdefault(v["yyyymmdd"], {})
record[v["yyyymmdd"]].setdefault("総売上", 0)
record[v["yyyymmdd"]].setdefault("総課金者数", 0)
record[v["yyyymmdd"]].setdefault(v["item_name"] + "売上", float(v["revenue"]))
record[v["yyyymmdd"]].setdefault(v["item_name"] + "課金者数", int(v["puu"]))
record[v["yyyymmdd"]]["総売上"] += float(v["revenue"])
record[v["yyyymmdd"]]["総課金者数"] += int(v["puu"])
#テーブル作成用にフォーマットを変更
for d, r1 in record.items():
rec = {}
rec.setdefault("yyyymmdd", d)
for item_label, r2 in r1.items():
rec.setdefault(item_label, r2)
table_data.append(rec)
#自動配列のための処理
label_weight = {}
for element in table_data:
for key, value in element.items():
if key != "yyyymmdd" and key != "os_type":
label_weight.setdefault(key, 0)
if label_weight[key] < value:
label_weight[key] = value
label = ["日にち",]
for k, v in sorted(label_weight.items(), key=lambda x:x[1], reverse = True):
label.append(k)
for v in sorted(table_data, key = lambda x:x["yyyymmdd"]):
#テーブル用
row = []
for l in label:
if l == "日にち":
row.append(v["yyyymmdd"])
else:
try:
value = fmt.camma(int(v[l]))
except:
value = "0"
row.append(value)
table.append(row)
doc = html_util.document(title = "", content = "", to_addrs = [])
doc.add_ccchart_header()
t = doc.make_table(label, table)
data = []
data.append(Revenuedetail(data = t))
#選択期間のグラフを生成する
graph_data = "["
x = []
for l in label:
if l.find("売上") > 0 or l == "日にち":
x.append("[" + '"' + l + '",')
length = len(x)
for k, v in sorted(record.items()):
if int(k) % 100 == 1:
x[0] += str(k) + ","
else:
x[0] += ","
for i in range(0, length):
find_flag = 0
for key, value in v.items():
if x[i].find(key) > 0:
x[i] += str(value) + ","
find_flag = 1
if find_flag == 0:
x[i] += "0,"
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 += "]"
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,
"data" : data,
"graphconfig" : graphconfig,
})
return render_to_response('servicekpi/revenuedetail.html', {},
context_instance=context)
def firstpay(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
kpi_id = "firstpay"
label = [u"日にち", u"NUU", 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 *"
sql += ' from mnl_kpi_firstpay 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 = Firstpay(yyyymmdd = label[0], nuu = label[1], day01 = label[2],
day02 = label[3], day03 = label[4], day07 = label[5],
day14 = label[6], day30 = label[7], day60 = label[8])
head.append(l)
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
d = Firstpay(yyyymmdd = v["yyyymmdd"],
nuu = fmt.camma(int(v["nuu"])),
day01 = v["day01"],
day02 = fmt.camma(int(v["day02"])),
day03 = fmt.camma(int(v["day03"])),
day07 = fmt.camma(int(v["day07"])),
day14 = fmt.camma(int(v["day14"])),
day30 = fmt.camma(int(v["day30"])),
day60 = fmt.camma(int(v["day60"])))
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["day01"] + ","
x[2] += v["day02"] + ","
x[3] += v["day03"] + ","
x[4] += v["day07"] + ","
x[5] += v["day14"] + ","
x[6] += v["day30"] + ","
x[7] += v["day60"] + ","
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 += "]"
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/firstpay.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)
def revenue(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
kpi_id = "logindaysdau"
label = [u"日にち", u"総額", u"サブスクリプション", u"従量"]
#タブメニューの中身を検索する
app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id)
#タブメニューで選択期間を取得する
duration, start, end = udf.search_target(request)
#選択期間のデータを取得する
try:
app = str(duration[0].app_id) + str(duration[0].os).lower()
sql = "select date, name, sum(cnt) as cnt"
sql += ' from analyze_summary where '
sql += "date <= '" + str(end) + "' and date >= '" + str(start) + "' "
sql += " and application = '" + app + "'" + " and name like '%sales%' and country = '' group by date, name, application"
log = udf.get_data(sql)
except:
log = []
#選択期間の表を作成する
data = []
head = []
l = Revenue(yyyymmdd = label[0], all = label[1], sub = label[2],
unit = label[3])
head.append(l)
record = {}
for v in log:
record.setdefault(v["date"].replace("-",""), {})
record[v["date"].replace("-","")]
record[v["date"].replace("-","")].setdefault("sub", 0)
record[v["date"].replace("-","")].setdefault("all", 0)
record[v["date"].replace("-","")].setdefault("pay", 0)
record[v["date"].replace("-","")]["all"] += float(v["cnt"])
if v["name"] == "sales_subscription":
record[v["date"].replace("-","")]["sub"] += float(v["cnt"])
else:
record[v["date"].replace("-","")]["pay"] += float(v["cnt"])
for k, v in sorted(record.items()):
d = Revenue(yyyymmdd = k,
all= v["all"], sub = v["sub"], unit = v["pay"])
data.append(d)
#選択期間のグラフを生成する
graph_data = "["
x = []
for l in label:
x.append("[" + '"' + l + '",')
for k, v in sorted(record.items()):
if int(k) % 100 == 1:
x[0] += str(k) + ","
else:
x[0] += ","
x[1] += str(v["all"]) + ","
x[2] += str(v["sub"]) + ","
x[3] += str(v["pay"]) + ","
for i in range(0,4):
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/revenue.html', {},
context_instance=context)
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)
def logindaysdau(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
kpi_id = "logindaysdau"
label = [u"日にち", u"１日", u"３日", u"７日", u"１０日", u"１４日", u"１８日", u"２１日", u"３０日", u"合計"]
#label = ["x", "y", "z", "x", "y", "z", "x", "y", "z"]
#タブメニューの中身を検索する
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, sum(day01) as day01, sum(day03) as day03"
sql += ", sum(day07) as day07, sum(day10) as day10"
sql += ", sum(day14) as day14, sum(day18) as day18"
sql += ", sum(day21) as day21, sum(day30) as day30"
sql += ' from mnl_kpi_logindaysdau 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 = Logindaysdau(yyyymmdd = label[0], d01 = label[1], d03 = label[2],
d07 = label[3], d10 = label[4], d14 = label[5],
d18 = label[6], d21 = label[7], d30 = label[8], sum = label[9])
head.append(l)
s0, s1, s2, s3, s4, s5, s6, s7 = 0, 0, 0, 0, 0, 0, 0, 0
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
s = int(v["day01"]) + int(v["day03"]) + int(v["day07"]) + int(v["day14"]) + int(v["day18"]) + int(v["day21"]) + int(v["day30"])
d = Logindaysdau(yyyymmdd = v["yyyymmdd"],
d01 = fmt.camma(int(v["day01"])),
d03 = fmt.camma(int(v["day03"])),
d07 = fmt.camma(int(v["day07"])),
d10 = fmt.camma(int(v["day10"])),
d14 = fmt.camma(int(v["day14"])),
d18 = fmt.camma(int(v["day18"])),
d21 = fmt.camma(int(v["day21"])),
d30 = fmt.camma(int(v["day30"])),
sum = fmt.camma(s))
s0 += int(v["day01"])
s1 += int(v["day03"])
s2 += int(v["day07"])
s3 += int(v["day10"])
s4 += int(v["day14"])
s5 += int(v["day18"])
s6 += int(v["day21"])
s7 += int(v["day30"])
data.append(d)
d = Logindaysdau(yyyymmdd = "SUM",
d01 = fmt.camma(s0),
d03 = fmt.camma(s1),
d07 = fmt.camma(s2),
d10 = fmt.camma(s3),
d14 = fmt.camma(s4),
d18 = fmt.camma(s5),
d21 = fmt.camma(s6),
d30 = fmt.camma(s7),
sum = fmt.camma(s0 + s1 +s2 +s3 + s4 + s5 +s6 + s7))
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["day01"] + ","
x[2] += v["day03"] + ","
x[3] += v["day07"] + ","
x[4] += v["day10"] + ","
x[5] += v["day14"] + ","
x[6] += v["day18"] + ","
x[7] += v["day21"] + ","
x[8] += v["day30"] + ","
for i in range(0,9):
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="stacked", width = "2", 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,
"graphconfig" : graphconfig,
})
return render_to_response('servicekpi/logindaysdau.html', {},
context_instance=context)
def lastsessionuu(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
#わざと
kpi_id = "logindaysdau"
label = [u"日にち", u"新規登録UU", u"最終ログインUU"]
#タブメニューの中身を検索する
app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id)
#タブメニューで選択期間を取得する
duration, start, end = udf.search_target(request)
#選択期間のデータを取得する
try:
sql = "select app_id, os_type, regdate, count(uid) as uu"
sql += ' from mnl_kpi_lastsessionuu where '
sql += 'moddate <= ' + str(end) + ' and moddate >= ' + str(start) + ' '
sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os
sql += "' group by app_id, os_type, regdate"
reg = udf.get_data(sql)
sql = "select app_id, os_type, moddate, count(uid) as uu"
sql += ' from mnl_kpi_lastsessionuu where '
sql += 'moddate <= ' + str(end) + ' and moddate >= ' + str(start) + ' '
sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os
sql += "' group by app_id, os_type, moddate"
log = udf.get_data(sql)
except:
log, reg = [], []
#選択期間の表を作成する
data = []
head = []
l = Lastsessionuu(yyyymmdd = label[0], uu = label[1], nuu = label[2])
head.append(l)
for v in sorted(log, key = lambda x:x["moddate"]):
for vv in sorted(reg, key = lambda x:x["regdate"]):
if v["moddate"] == vv["regdate"] and v["os_type"] == vv["os_type"]:
d = Lastsessionuu(yyyymmdd = v["moddate"],
uu = fmt.camma(int(v["uu"])),
nuu = fmt.camma(int(vv["uu"])))
data.append(d)
#選択期間のグラフを生成する
graph_data = "["
x = []
for l in label:
x.append("[" + '"' + l + '",')
for v in sorted(log, key = lambda x:x["moddate"]):
for vv in sorted(reg, key = lambda x:x["regdate"]):
if v["moddate"] == vv["regdate"] and v["os_type"] == vv["os_type"]:
if int(v["moddate"]) % 100 == 1:
x[0] += v["moddate"] + ","
else:
x[0] += ","
x[1] += vv["uu"] + ","
x[2] += v["uu"] + ","
for i in range(0,3):
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/lastsessionuu.html', {},
context_instance=context)
def cross(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
kpi_id = "logindaysdau"
label = [u"日にち", u"UU"]
#タブメニューの中身を検索する
app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id)
#タブメニューで選択期間を取得する
duration, start, end = udf.search_target_cross(request)
#選択期間のデータを取得する
try:
sql = "select yyyymmdd, app_id, os_type, counter_app_id, counter_os_type,"
sql += "uu "
sql += ' from mnl_kpi_cross where '
sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' '
sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os
sql += "'"
sql += " and counter_app_id = '" + duration[0].tapp_id + "' and counter_os_type = '" + duration[0].tos
sql += "'"
log = udf.get_data(sql)
except:
log = []
#選択期間の表を作成する
data = []
head = []
l = Cross(yyyymmdd = label[0],
uu = label[1])
head.append(l)
for v in sorted(log, key = lambda x:x["yyyymmdd"]):
d = Cross(yyyymmdd = v["yyyymmdd"],
uu = fmt.camma(int(v["uu"])))
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["uu"] + ","
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 += "]"
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/cross.html', {},
context_instance=context)
def realtimerevenue(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
label = []
today = datetime.datetime.today()
end = today.strftime('%Y%m%d')
start = today.strftime('%Y%m') + "01"
#選択期間のデータを取得する
try:
sql = "select yyyymmdd, app_id, os_type, "
sql += "sum(revenue) as revenue"
sql += ' from mnl_kpi_revenue where '
sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' '
sql += " group by yyyymmdd, app_id, os_type"
log = udf.get_data(sql)
except:
log = []
#選択期間の表を作成する
#データを整形する
record = {}
table_data = []
table = []
for v in log:
record.setdefault(v["yyyymmdd"], {})
record[v["yyyymmdd"]].setdefault("総売上", 0)
record[v["yyyymmdd"]].setdefault(v["app_id"] +"(" + v["os_type"] + ")", int(float(v["revenue"])))
record[v["yyyymmdd"]]["総売上"] += int(float(v["revenue"]))
#テーブル作成用にフォーマットを変更
header = {}
for d, r1 in record.items():
rec = {}
rec.setdefault("yyyymmdd", d)
header.setdefault("yyyymmdd", "ALL")
for item_label, r2 in r1.items():
rec.setdefault(item_label, r2)
header.setdefault(item_label, 0)
header[item_label] += r2
table_data.append(rec)
#自動配列のための処理
label_weight = {}
for element in table_data:
for key, value in element.items():
if key != "yyyymmdd" and key != "os_type":
label_weight.setdefault(key, 0)
label_weight[key] += value
label = ["日にち",]
for k, v in sorted(label_weight.items(), key=lambda x:x[1], reverse = True):
label.append(k)
#テーブル用
row = []
for l in label:
if l == "日にち":
row.append(header["yyyymmdd"])
else:
try:
value = fmt.camma(int(header[l]))
except:
value = "0"
row.append(value)
table.append(row)
for v in sorted(table_data, key = lambda x:x["yyyymmdd"], reverse = True):
row = []
for l in label:
if l == "日にち":
row.append(v["yyyymmdd"])
else:
try:
value = fmt.camma(int(v[l]))
except:
value = "0"
row.append(value)
table.append(row)
doc = html_util.document(title = "", content = "", to_addrs = [])
doc.add_ccchart_header()
t = doc.make_table(label, table)
data = []
data.append(Revenuedetail(data = t))
context = RequestContext(request, {
'user': User,
"data" : data,
})
return render_to_response('servicekpi/realtimerevenue.html', {},
context_instance=context)