Beispiel #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)
Beispiel #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)
Beispiel #3
0
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"1ヶ月未満", u"2ヶ月未満", u"3ヶ月未満", u"半年未満", u"1年未満", u"1年以上", 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)
Beispiel #4
0
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)
Beispiel #5
0
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"1日以内", u"2日以内", u"3日以内",
                 u"7日以内", u"14日以内", u"30日以内", u"30日以上"]
        #タブメニューの中身を検索する
        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)
Beispiel #6
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)
Beispiel #7
0
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)
Beispiel #8
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)
Beispiel #9
0
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"1日", u"3日", u"7日", u"10日", u"14日", u"18日", u"21日", u"30日", 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)
Beispiel #10
0
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)
Beispiel #11
0
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)
Beispiel #12
0
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)