Esempio n. 1
0
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"14日後", u"30日後", u"60日後", u"90日後",
                 u"180日後", u"360日後", u"720日後"]
        #タブメニューの中身を検索する
        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)
Esempio n. 2
0
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)
Esempio n. 3
0
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"1ヶ月課金率",
            u"2ヶ月課金率",
            u"3が月課金率",
            u"6ヶ月課金率",
            u"12ヶ月課金率",
            u"13ヶ月課金率",
            u"おためし有料化率",
            u"有料2ヶ月継続率",
            u"有料3ヶ月継続率",
            u"有料6ヶ月継続率",
            u"有料12ヶ月継続率",
            u"有料13ヶ月継続率",
        ]
        # タブメニューの中身を検索する
        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>2. 変化の原因は施策による内部要因と市場環境やプラットフォーマーの仕様変更などの外部要因が考えられるので、変化があった場合には数字をもとに仮説を出す"
 comment += "<br>3.特に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 = ["日にち", "1日後", "3日後", "7日後", "14日後", "30日後", "60日後", "90日後", "180日後",
          "360日後", "720日後"]
 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)
Esempio n. 5
0
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)