예제 #1
0
파일: udf.py 프로젝트: ytworks/private
def pulldown_menu(kpi_id):
    cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
    sql = "select distinct(app_id) as app_id from mnl_kpi_" + kpi_id
    log = cdg_db.db_anyselect(sql)
    app_ids = []
    for v in log:
        app_ids.append(Applist(app_id=v["app_id"]))
    sql = "select distinct(os_type) as os_type from mnl_kpi_" + kpi_id
    log = cdg_db.db_anyselect(sql)
    os_types = []
    for v in log:
        os_types.append(Oslist(os_type=v["os_type"]))
    sql = "select distinct(year) as year from mnl_kpi_" + kpi_id
    log = cdg_db.db_anyselect(sql)
    year = []
    for v in log:
        year.append(Yearlist(year=v["year"]))
    month = []
    for v in range(1, 13):
        month.append(Monthlist(month=str(v)))
    date = []
    for v in range(1, 32):
        date.append(Datelist(date=str(v)))

    return app_ids, os_types, year, month, date
예제 #2
0
def get_rr(lower, upper):
    cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
    sql = "select app_id, os_type, "
    sql += "avg(rr_001) as rr_001, avg(rr_007) as rr_007 "
    sql += "from mnl_kpi_rr where yyyymmdd >= "
    sql += str(lower) + " and yyyymmdd < " + str(upper) + " group by app_id, os_type"
    log = cdg_db.db_anyselect(sql)
    return log
예제 #3
0
def get_cvr(lower, upper):
    cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
    sql = "select app_id, os_type, "
    sql += "avg(firstdaytrial) as ft, avg(firstdaysubscription) as fs, avg(subscription01) as s01, avg(trial2subscription) as t2s, "
    sql += "avg(trial2subscription02) as t2s02, avg(trial2subscription03) as t2s03 "
    sql += "from mnl_kpi_cvr where yyyymmdd >= "
    sql += str(lower) + " and yyyymmdd < " + str(upper) + " group by app_id, os_type"
    log = cdg_db.db_anyselect(sql)
    return log
예제 #4
0
파일: udf.py 프로젝트: ytworks/private
def pulldown_menu_yyyymm(kpi_id):
    cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
    sql = "select distinct(year) as year from mnl_kpi_" + kpi_id
    log = cdg_db.db_anyselect(sql)
    year = []
    for v in log:
        year.append(Yearlist(year=v["year"]))
    month = []
    for v in range(1, 13):
        month.append(Monthlist(month=str(v)))

    return year, month
예제 #5
0
import sys
sys.path.append('/home/takagi/git/analytics/analytics_tool')
import utility.db_utility as db_util
from datetime import datetime as dt
import commands

argvs = sys.argv
lower = int(argvs[1])

print lower
#DB

##########kidsnbooks
#access log
kidsnbooks_log = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
where = "date = " + str(lower)
access_log = kidsnbooks_log.db_select("*", "analytics_metalog", where, None)
summary = {}
for v in access_log:
    summary.setdefault(v["uid"], {})
    summary[v["uid"]].setdefault(v["app_id"], {})
    summary[v["uid"]][v["app_id"]].setdefault(v["os_type"], {})
    summary[v["uid"]][v["app_id"]][v["os_type"]].setdefault("regdate", v["regdate"])
    summary[v["uid"]][v["app_id"]][v["os_type"]].setdefault("moddate", v["date"])

#output
fname = "/home/takagi/git/analytics/analytics_tool/data_temp/lastsessionuu" + str(lower)
out = open(fname, 'w')

for uid, app in summary.items():
import utility.device_master as dev_master

user_data = {}
# DB
db = {
    "meta": {
        "server": "inhousedb.smartsrv.in",
        "db_name": "statistics",
        "table": ["mnl_metalog"],
        "colum": "id,created",
    }
}


# 課金データを取得
logdb = db_util.db_command("takagi", "7TgYiaI9", db["meta"]["server"], db["meta"]["db_name"])
start = datetime.datetime(2013, 6, 25, 0, 0, 0, 0)
one_day = datetime.timedelta(days=1)
end = start + one_day
user_data = {}
for i in range(130):
    s = start + i * one_day
    e = end + i * one_day
    month = s.strftime("%Y-%m")
    s = s.strftime("%Y%m%d")
    e = e.strftime("%Y%m%d")
    where = " where date = " + s + " and app_id = 'kidsnbooks' and sub_pay > 0"
    print where
    sql = "select uid, app_id, os_type, sub_pay from mnl_metalog" + where
    log = logdb.db_anyselect(sql)
    for v in log:
예제 #7
0
def growthrate(request):
    if not request.user.is_authenticated():
        return render_to_response('servicekpi/index.html', {},
                                  context_instance=RequestContext(request))
    else:
        if request.POST:
            wb = Workbook()
            c_border = xlwt.Borders()
            c_border.top    = xlwt.Borders.THICK
            c_border.bottom = xlwt.Borders.THICK
            c_border.left   = xlwt.Borders.THICK
            c_border.right  = xlwt.Borders.THICK
            font0 = xlwt.Font()
            font0.name = 'Osaka'
            c_style = xlwt.XFStyle()
            c_style.borders = c_border
            c_style.font = font0
            year, month = int(request.POST["sy"]), int(request.POST["sm"])
            start0 = str(year) + str(month).zfill(2)
            cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
            sql = "select * "
            sql += "from mnl_kpi_monthly_subscription where date <= "
            sql += str(start0)
            log = cdg_db.db_anyselect(sql)
            data = {}
            for v in log:
                data.setdefault(v["app_id"], {})
                data[v["app_id"]].setdefault(v["os_type"], {})
                data[v["app_id"]][v["os_type"]].setdefault(v["regdate"], {})
                data[v["app_id"]][v["os_type"]][v["regdate"]].setdefault(v["date"], float(v["uu"]))
            ws = []
            i = 0
            for app_id, d0 in data.items():
                for os, d1 in d0.items():
                    j = 0
                    ws.append(wb.add_sheet(str(app_id) + "_" + str(os)))
                    total = {}
                    for regdate, d2 in sorted(d1.items()):
                        k = j
                        ws[i].write(j + 1, 0, regdate, c_style)
                        for date, uu in sorted(d2.items()):
                            if k == j:
                                ws[i].write(0, k + 1, date, c_style)
                            ws[i].write(j + 1, k + 1, uu, c_style)
                            total.setdefault(date, {})
                            total[date].setdefault("uu", 0) 
                            if k == j:
                                total[date].setdefault("col", k + 1) 
                            total[date]["uu"]+= uu
                            k += 1
                        j += 1
                    ws[i].write(j + 1, 0, u"合計", c_style)
                    ws[i].write(j + 2, 0, u"成長率", c_style)
                    p = 0
                    for key, value in sorted(total.items()):
                        try:
                            col = value["col"]
                        except:
                            col += 1
                        ws[i].write(j + 1, col, value["uu"], c_style)
                        g =  100 * value["uu"] / p if p != 0 else "NONE"
                        ws[i].write(j + 2, col, g, c_style)
                        p = value["uu"]
                    i += 1


            file_name = 'growthrate_' + str(start0) + '.xls'
            response = HttpResponse(mimetype='application/ms-excel')
            response['Content-Disposition'] = 'attachment; filename=%s' % file_name
            wb.save(response)
            return response
        else:
            year, month = udf.pulldown_menu_yyyymm("cvr")
            context = RequestContext(request, {
                    'user': User,
                    "year" : year,
                    "month" : month,
                    })
            return render_to_response('servicekpi/growthrate.html', {},
                                      context_instance=context)
예제 #8
0
argvs = sys.argv
install_date = int(argvs[1].replace("-", ""))
user_data = {}
#DB
db = {
    "meta"   : { "server" : "inhousedb.smartsrv.in",
                 "db_name" : "statistics",
                 "table" : ["mnl_subscriptionlog_fast"],
                 "colum" : "*"
                 },
    }


#インストールユーザーを抽出する
logdb = db_util.db_command("takagi", "7TgYiaI9", db["meta"]["server"], db["meta"]["db_name"])
sql = "select * from mnl_subscriptionlog_fast where sub_pay > 0 and date = " + str(install_date) 
log = logdb.db_anyselect(sql)
for v in log:
     month = round(float(v["is_sub"]) / 30)
     #created異常のユーザー回避
     if month == 0:
          month = 1
     price = float(v["sub_pay"]) / float(month)
     user_data.setdefault(v["uid"], {})
     user_data[v["uid"]].setdefault(v["app_id"], {})
     user_data[v["uid"]][v["app_id"]].setdefault("month", month)
     user_data[v["uid"]][v["app_id"]].setdefault("price", price)
     user_data[v["uid"]][v["app_id"]].setdefault("date", [])

     for i in range(0, int(month)):
예제 #9
0
                   },
     "access"   : { "server" : "apps.db.smartsrv.jp",
                    "db_name" : "smartapps_log",
                    "table" : ["Access_201310",],
                    "where" : "application = 122"
                    },    
     "play"   : { "server" : "apps.db.smartsrv.jp",
                    "db_name" : "smartapps_log",
                    "table" : ["Play_201310","Play_201311"],
                    "where" : "(application = 122 or application = 121) and is_start = 0 and date > '2013-10-22'"
                    },
    }


#インストールユーザーを抽出する
logdb = db_util.db_command("takagi", "7TgYiaI9", db["users"]["server"], db["users"]["db_name"])
for table in db["users"]["table"]:
     sql = "select user_id from "
     sql += table + " where " + db["users"]["where"]
     log = logdb.db_anyselect(sql)
     for v in log:
          user_data.setdefault(v["user_id"], {})
          user_data[v["user_id"]].setdefault("iOS", [])
          user_data[v["user_id"]].setdefault("Android", [])



#プレイログを読む
logdb = db_util.db_command("takagi", "7TgYiaI9", db["play"]["server"], db["play"]["db_name"])
for table in db["play"]["table"]:
     sql = "select user_id, content_id, type, extra_val_01, application from "
           "Goccozoo-iOS" : {"app_id" : "goccozoo",
                             "os_type" : "os_type != 'Android'",
                             "application" : "goccozoo",
                             "pay" : "and (name = 'sales_contents')"},
           }


#全体の売上
rec = {"売上" : 0,
       "サブスクリプション売上" : 0,
       "従量売上" : 0,
       "当月累計売上" : 0,
       "先月同日比" : 0,
       "当月着地見込み" : 0
       }
logdb = db_util.db_command("takagi", "7TgYiaI9", db["revenue"]["server"], db["revenue"]["db_name"])
#今日の売上
sql = "select name, sum(cnt) as cnt from analyze_summary "
sql += "where "
sql += "date = '" + date + "' "
sql += "and name like '%sales%' and country = ''"
log = logdb.db_anyselect(sql)
for v in log:
     rec["売上"] += float(v["cnt"])

#今日のサブスクリプション売上
sql = "select name, sum(cnt) as cnt from analyze_summary "
sql += "where "
sql += "date = '" + date + "' "
sql += "and name = 'sales_subscription' and country = ''"
log = logdb.db_anyselect(sql)
예제 #11
0
sys.path.append("/home/takagi/git/analytics/analytics_tool")
import utility.db_utility as db_util
from datetime import datetime as dt
import commands
import utility.device_master as dev_master

argvs = sys.argv
lower = "2013-07-28"
upper = "2013-08-05"

print lower, upper
# DB

##########kidsnbooks

kidsnbooks = db_util.db_command("takagi", "7TgYiaI9", "kidsnbooksdb.smartsrv.jp", "kidsnbooks")

user_data = {}
# access log
where = "created >= '" + lower + " 00:00:00' and created < '" + upper + " 00:00:00'"
access_log = kidsnbooks.db_select(
    "user_id,subscription_id,application,status,created", "subscription_users", where, None
)
user_data = {}
for v in access_log:
    dev = "Android" if v["application"] == "31" else "iOS"
    tdt = dt.strptime(v["created"], "%Y-%m-%d %H:%M:%S")
    tdtf = tdt.strftime("%Y%m%d")
    user_data.setdefault(v["user_id"], {})
    user_data[v["user_id"]].setdefault("device", dev)
    user_data[v["user_id"]].setdefault("status", v["status"])
    "user"   : { "server" : "kidsnbooksdb.smartsrv.jp",
                 "db_name" : "kidsnbooks",
                 "table" : ["users"],
                 "colum" : "id,created"
                 },
    "play"   : { "server" : "log.kidsnbooksdb.smartsrv.jp",
                 "db_name" : "kidsnbooks_log",
                 "table" : ["play_log"],
                 "colum" : "id,is_start,book_id,created"
             },

    }


#インストールデータを日別に取得する
logdb = db_util.db_command("smartedu", "nakadama", db["user"]["server"], db["user"]["db_name"])
sql = "select id,created,platform_full from users where created >= '2013-06-01 00:00:00'"
log = logdb.db_anyselect(sql)
for v in log:
     created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S')
     created = created.strftime('%Y%m%d')
     user_data.setdefault(v["id"], {})
     user_data[v["id"]].setdefault("regdate", created)
     dev = dev_master.os_name(v["platform_full"])
     user_data[v["id"]].setdefault("device", dev)
     user_data[v["id"]].setdefault("book_start", None)
     user_data[v["id"]].setdefault("book_end", None)
     user_data[v["id"]].setdefault("start", None)
     user_data[v["id"]].setdefault("end", None)
     user_data[v["id"]].setdefault("book_num", 0)
예제 #13
0
파일: udf.py 프로젝트: ytworks/private
def get_data(sql):
    cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
    log = cdg_db.db_anyselect(sql)
    return log
예제 #14
0
db = {
    "smartapps"   : { "server" : "apps.db.smartsrv.jp",
                      "db_name" : "smartapps_user",
                      "table" : ["Users_0", "Users_1", "Users_2", "Users_3", "Users_4",
                                  "Users_5", "Users_6", "Users_7", "Users_8", "Users_9",],
                      "colum" : "user_id as id, application, created"
                      },
    
    }

kdmode = {"server" : "apps.db.smartsrv.jp", "db_name" : "kdmode" }
user_data = {}

#ユーザーデータの取得
for app_id, server in db.items():
    logdb = db_util.db_command("smartedu", "nakadama", server["server"], server["db_name"])
    tables = server["table"]
    for table in tables:
        where = "application = 65"
        log = logdb.db_select(server["colum"], table, where, None)
        am = app_master.app_master()
        for v in log:
            if v["created"] != "NULL" and am.has_key(v["application"]):
                created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S')
                created = created.strftime('%Y%m%d')
                user_data.setdefault(v["id"], {})
                user_data[v["id"]].setdefault("regdate", created)
                user_data[v["id"]].setdefault("apps", {})
                user_data[v["id"]].setdefault("install", {})
                user_data[v["id"]].setdefault("add_on_video", {})
예제 #15
0
파일: userlog.py 프로젝트: ytworks/private
                     },
    
    "smartapps"   : { "server" : "apps.db.smartsrv.jp",
                      "db_name" : "smartapps_user",
                      "table" : ["Users_0", "Users_1", "Users_2", "Users_3", "Users_4",
                                  "Users_5", "Users_6", "Users_7", "Users_8", "Users_9",],
                      "colum" : "user_id as id, application, created"
                      },
    
    }


##########kidsnbooks

for app_id, server in db.items():
    logdb = db_util.db_command("smartedu", "nakadama", server["server"], server["db_name"])
    tables = server["table"]
    for table in tables:
        user_data = {}
    #subscription log
        log = logdb.db_select(server["colum"], table, None, None)
        am = app_master.app_master()
        for v in log:
            if app_id != "smartapps":
                if v["created"] != "NULL":
                    created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S')
                    created = created.strftime('%Y%m%d')
                    user_data.setdefault(v["id"], {})
                    user_data[v["id"]].setdefault(app_id, {})
                    user_data[v["id"]][app_id].setdefault("regdate", created)
            else:
                 "table" : ["play_log"],
                 "colum" : "id,is_start,book_id,created"
             },

    }


#インストールデータを日別に取得する




#本読みデータを取得


logdb = db_util.db_command("smartedu", "nakadama", db["play"]["server"], db["play"]["db_name"])
metadb = db_util.db_command("takagi", "7TgYiaI9", db["meta"]["server"], db["meta"]["db_name"])
start = datetime.datetime(2013, 6, 25, 0, 0, 0, 0) 
one_day = datetime.timedelta(days=1)
end = start + one_day

sql = "select * from analytics_metalog where app_id = 'kidsnbooks' and is_sub > 0" 
log = metadb.db_anyselect(sql)
for v in log:
     user_data.setdefault(v["uid"], {})
     user_data[v["uid"]].setdefault("sub_day", {})
     user_data[v["uid"]]["sub_day"].setdefault(v["date"], 0)

for i in range(110):
     s = start + i * one_day
     e = end + i * one_day
     if summary_app_name == 'rhythmonstage':
          title = 'リズムオンステージ'
          app_analyze = {'iOS' : 'rhythmonstage', 'Android' : 'rhythmonstageandroid'}

     if summary_app_name == 'goccozoo':
          title = 'Goccozoo'
          app_analyze = {'iOS' : 'goccozoo', 'Android' : 'goccozooandroid'}






     index_tab = {}
     db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")


     #売上
     comment = "<div>定義:<br>サービス個別の商品セグメントで分類したデイリーの売上<br>使い方:"
     comment += "<br>1. 日々の売上を追って課金者のサービスの利用状況に変化がないかをチェックする。"
     comment += "<br>2.時系列で売上の推移が追えるので課金に有効な施策を行ったときの前後で変化があったなどを見る。</div><br>"
     log = []
     record = {}
     for os, app in app_analyze.items():
          sql = "select date, name, sum(cnt) as cnt"
          sql += ' from analyze_summary where '
          sql += "date <= '" + str(date_end) + "' and date >= '" + str(date_start) + "' "
          sql += " and application = '" + app + "'" + " and name like '%sales%' and country = '' group by date, name, application"
          log_data = db.db_anyselect(sql)
          for v in log_data:
db = {
    "sub"   : { "server" : "log.kidsnbooksdb.smartsrv.jp",
                "db_name" : "kidsnbooks_log",
                "table" : "subscription_log",
                "colum" : "user_id,created,expiration_date"
                },
    
    "tra"   : { "server" : "log.kidsnbooksdb.smartsrv.jp",
                "db_name" : "kidsnbooks_log",
                "table" : "subscription_play_log",
                "colum" : "user_id,created,book_id"
                },
    
    }

logdb = db_util.db_command("smartedu", "nakadama", db["sub"]["server"], db["sub"]["db_name"])
table = db["sub"]["table"]
#subscription log
where = "created >= '2013-07-20 00:00:00' and created < '2013-08-05 00:00:00'"
log = logdb.db_select(db["sub"]["colum"], table, where, None)
    
for v in log:
    created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S')
    created2 = datetime.datetime(created.year, created.month, created.day, 0, 0, 0, 0)
    created2 = created.strftime('%Y%m%d')
    expire  = dt.strptime(v["expiration_date"] + " 00:00:00", '%Y-%m-%d %H:%M:%S')
    if (expire -created).days >= 14:
        user_data.setdefault(v["user_id"] , {})
        user_data[v["user_id"]].setdefault("regdate", created2)
        user_data[v["user_id"]].setdefault("books", {})