def item_master():
db = db_util.db_command("takagi", "7TgYiaI9", "apps.db.smartsrv.jp", "smartapps")
master = db.db_select("platform_product_id, content_name_jp", "purchase_products", None)
data = {}
for m in master:
data.setdefault(m["platform_product_id"], m["content_name_jp"])
return data
def app_master() :
db = db_util.db_command("takagi", "7TgYiaI9", "apps.db.smartsrv.jp", "smartapps")
master = db.db_select("id, id_str", "applications", None)
data = {}
for m in master:
data.setdefault(m["id"], m["id_str"])
return data
def fast_meta_log(where, start, end):
logdb = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
log = []
for date in range(start, end):
cmd = "select * from analytics_metalog where " + where + " and date = " + str(date)
result = logdb.db_anyselect(cmd)
log.append(result)
return log
def first_subscription(title, start, end):
logdb = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
sql = "select * from analytics_metalog where app_id = '" + title + "' and regdate >= " + str(start) + " and regdate <= " + str(end) + " and date >= "
sql += str(start)
log = logdb.db_anyselect(sql)
data = {}
for v in log:
data.setdefault(v["uid"], {})
data[v["uid"]].setdefault("regdate", int(v["regdate"]))
data[v["uid"]].setdefault("subdate", None)
if int(v["sub_pay"]) > 0:
if data[v["uid"]]["subdate"] == None or data[v["uid"]]["subdate"] > int(v["date"]):
data[v["uid"]]["subdate"] = int(v["date"])
return data
def get_db(self, db):
master = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
cmd = "select id from gdb_master where name = '" + str(db) + "'"
spreadsheet_key = master.db_anyselect(cmd)
if len(spreadsheet_key) > 0:
print "spreadsheet_key = " + spreadsheet_key[0]["id"],
print "found"
key = spreadsheet_key[0]["id"]
result = self.client.GetDatabases(spreadsheet_key=key)[0]
return result
else:
print "created"
created = self.client.CreateDatabase(db)
# created = self.client.CreateDatabase_ext(db, "0B3PUZYPoTCEOUWR4OVFhMThMaGs")
cmd = "insert into gdb_master (name, id) values "
cmd += "('" + str(db) + "','" + str(created.spreadsheet_key) + "')"
master.db_anyselect(cmd)
result = self.client.GetDatabases(spreadsheet_key=created.spreadsheet_key)[0]
return result
def kidsnbooks_booklog(yyyymmdd):
logdb = db_util.db_command("smartedu", "nakadama", "log.kidsnbooksdb.smartsrv.jp", "kidsnbooks_log")
yyyymmdd, yyyymm, year, month, date = date_func.get_yyyymmdd(yyyymmdd)
start = datetime.datetime(int(year), int(month), int(date), 0, 0, 0, 0)
one_day = datetime.timedelta(days=1)
end = start + one_day
user_data = {}
s = start.strftime('%Y-%m-%d %H:%M:%S')
e = end.strftime('%Y-%m-%d %H:%M:%S')
where = " where created >= '" + s + "' and created < '" + e + "'"
print where
sql = "select user_id,book_id,is_start,created from play_log" + where
log = logdb.db_anyselect(sql)
for v in log:
created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S')
created = int(created.strftime('%Y%m%d'))
user_data.setdefault(v["user_id"], {})
user_data[v["user_id"]].setdefault("start", None)
user_data[v["user_id"]].setdefault("book_id", None)
user_data[v["user_id"]].setdefault("book_name", None)
if int(v["is_start"]) == 1:
if user_data[v["user_id"]]["start"] == None or created < user_data[v["user_id"]]["start"] :
user_data[v["user_id"]]["start"] = created
user_data[v["user_id"]]["book_id"] = v["book_id"]
sql = "select user_id,book_id,is_start,created from play_log_android" + where
log = logdb.db_anyselect(sql)
for v in log:
created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S')
created = int(created.strftime('%Y%m%d'))
user_data.setdefault(v["user_id"], {})
user_data[v["user_id"]].setdefault("start", None)
user_data[v["user_id"]].setdefault("book_id", None)
user_data[v["user_id"]].setdefault("book_name", None)
if int(v["is_start"]) == 1:
if user_data[v["user_id"]]["start"] == None or created < user_data[v["user_id"]]["start"] :
user_data[v["user_id"]]["start"] = created
user_data[v["user_id"]]["book_id"] = v["book_id"]
sql = "select user_id,book_id,location as is_start,created from play_log_quiz" + where
log = logdb.db_anyselect(sql)
for v in log:
created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S')
created = int(created.strftime('%Y%m%d'))
user_data.setdefault(v["user_id"], {})
user_data[v["user_id"]].setdefault("start", None)
user_data[v["user_id"]].setdefault("book_id", None)
user_data[v["user_id"]].setdefault("book_name", None)
if int(v["is_start"]) == 0:
if user_data[v["user_id"]]["start"] == None or created < user_data[v["user_id"]]["start"] :
user_data[v["user_id"]]["start"] = created
user_data[v["user_id"]]["book_id"] = v["book_id"]
logdb = db_util.db_command("smartedu", "nakadama", "kidsnbooksdb.smartsrv.jp", "kidsnbooks")
sql = "select id as book_id,name_jp from books"
log = logdb.db_anyselect(sql)
for uid, data in user_data.items():
for v in log:
if v["book_id"] == data["book_id"]:
user_data[uid]["book_name"] = v["name_jp"]
return user_data
'today' : u'当日登録ユーザー数',
'this_month' : u'当月登録ユーザー数',
'over_one_month_ago' : u'１ヶ月前登録ユーザー数',
'under_two_month_ago' : u'２ヶ月未満前登録ユーザー数',
'under_three_month_ago' : u'３ヶ月未満前登録ユーザー数',
'under_six_manth_ago' : u'半年未満前登録ユーザー数',
'under_one_year_ago' : u'１年未満前登録ユーザー数',
'over_year_ago' : u'１年以上前登録ユーザー数',
'app_id' : 'appid',
'os_type' : 'ostype',
'subscription_type' : 'subscriptiontype'
}
#データを抽出する
for name, db in dbs.items():
logdb = db_util.db_command("takagi", "7TgYiaI9", db["server"], db["db_name"])
sql = "select "
for c in db["colum"]:
sql += c + ","
sql = sql[:-1]
sql += " from " + db["table"] + " where yyyymmdd = " + str(yyyymmdd)
log = logdb.db_anyselect(sql)
gdb = gutil.google_db()
for v in log:
if name == "ltv" or name == 'playcountdau':
gdb_name = v["app_id"] + " " + name
sheet_name = v["os_type"]
record = {}
for key, value in v.items():
key = key.replace("_", "")
"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", {})
argvs = sys.argv
lower = argvs[1]
upper = argvs[2]
print lower,upper
db = { "rhythmdb.smartsrv.jp" : "rhythm_api",
"apps.db.smartsrv.jp" : "smartapps_log",
}
data = {}
keywords = []
#google referer log
for h, t in db.items():
logdb = db_util.db_command("smartedu", "nakadama", h, t)
table = "google_play_referer_log"
colum = "id,keyword,install_date,app_name"
where = "install_date >= '" + lower + " 00:00:00' and install_date < '" + upper + " 00:00:00'"
log = logdb.db_select(colum, table, where, None)
for v in log:
if v["keyword"] != "":
keyword = v["keyword"]
data.setdefault(keyword, {})
app_names = apm.app_name()
for app_name in app_names:
data[keyword].setdefault(app_name, {})
data[keyword][app_name].setdefault("install", 0)
data[keyword][app_name].setdefault("rank", None)
data[keyword][app_name].setdefault("record", 0)
