def general_report(input_dict):
conf = ConfParameters()
easy_sql = EasyMysql()
easy_date = DateList()
mysql_conf = conf.mysql_conf
stat_base = mysql_conf['stat_base']
# argv
x_list = list()
y_list = ['DAU', 'NewUsers', 'PayRate', 'Money', 'OnlineTime']
# input dict
date_list = sorted(list(input_dict['date_list']))
where_channel_zone = easy_sql.combine_where_clause(input_dict)
cursor = input_dict['cursor']
where_date_between = ' date between \'' + date_list[0] + '\' and \'' + date_list[len(date_list)-1] + '\' ' + where_channel_zone
sql = 'select date,sum(activeUsers),sum(newUsers),sum(payUsers)/sum(activeUsers),ceil(sum(money)/100),ceil(sum(online_time)/(60*sum(activeUsers))) from day_summary where '+where_date_between+' group by date;'
print(sql)
cursor.execute(sql)
all_data = cursor.fetchall()
result_dict = dict()
for y in y_list:
result_dict[y] = dict()
if all_data:
for rec in all_data:
date = str(rec[0])
DAU = int(rec[1])
NewUsers = int(rec[2])
PayRate = float(rec[3])
Money = int(rec[4])
OnlineTime = int(rec[5])
tmp = [DAU, NewUsers, PayRate, Money, OnlineTime]
for i in range(0, len(tmp)):
result_dict[y_list[i]][date] = result_dict[y_list[i]].setdefault(date,0) + tmp[i]
print('[Check1]len(result_dict):', len(result_dict.keys()))
x_list = date_list
res_dict = dict()
res_dict['data_dict'] = result_dict
res_dict['X_list'] = x_list
res_dict['Y_list'] = y_list
res_dict['default_value'] = ''
res_dict['head_name'] = '概览'
res_dict['note'] = ''
# print(res_dict)
return res_dict
def user_guidance(input_dict, level_pass, stat_reg_name):
is_legal_input(input_dict)
easy_sql = EasyMysql()
easy_date = DateList()
cursor = input_dict['cursor']
where_clause_main = easy_sql.combine_where_clause(input_dict)
x_list = input_dict['date_list']
y_list = range(1, level_pass + 1)
result_dict = dict()
for date in input_dict['date_list']:
time_stamp = easy_date.trans_date_form(date, '%Y%m%d')
where_clause = '1 ' + where_clause_main + ' and uid in (select uid from ( select uid from ' + stat_reg_name + '.user_register0 where date=\'' + date + '\''
for i in range(1, 10):
where_clause = where_clause + ' union all select uid from ' + stat_reg_name + '.user_register' + str(
i) + ' where date=\'' + date + '\''
where_clause = where_clause + ')a)'
sql_cmd = 'select level,count(uid) from user_active_' + time_stamp + ' where ' + where_clause + ' group by level'
# print sql_cmd
cursor.execute(sql_cmd)
all_data = cursor.fetchall()
level_map = dict()
if all_data:
for rec in all_data:
level = int(rec[0])
users = int(rec[1])
level_map[level] = users
for level in y_list:
if level not in result_dict.keys():
result_dict[level] = dict()
result_dict[level][date] = _pass_certain_lv(level_map, level)[0]
if 'TotalReg' not in result_dict.keys():
result_dict['TotalReg'] = dict()
result_dict['TotalReg'][date] = _pass_certain_lv(level_map, 0)[1]
res_dict = dict()
res_dict['data_dict'] = result_dict
res_dict['X_list'] = x_list
res_dict['Y_list'] = ['TotalReg'] + y_list
res_dict['default_value'] = 0
res_dict['head_name'] = '新用户新手引导通过率'
res_dict['note'] = '*按等级处理新手引导通过率，大于等于' + str(level_pass) + '认为通过新手引导'
return res_dict
def online_time(input_dict, stat_active_name, stat_pay_name, currency_rate,
*args):
is_legal_input(input_dict)
easy_sql = EasyMysql()
easy_date = DateList()
cursor = input_dict['cursor']
where_clause_main = easy_sql.combine_where_clause(input_dict)
x_list = input_dict['date_list']
y_list = sorted(list(args))
# print(y_list)
res_dict = dict()
ress_dict = dict()
if len(input_dict['date_list']) != 0:
min_time_stamp = min(input_dict['date_list'])
max_time_stamp = max(input_dict['date_list'])
# print(max_time_stamp)
#考察充值情况 距离开服x天
pay_dict = dict()
sql_cmd_pay = 'select uid,sum(money/100)*' + str(
currency_rate
) + ' as money from ' + stat_pay_name + '.pay_syn_day where date<=\'' + max_time_stamp + '\'' + where_clause_main + ' group by uid;'
# print(sql_cmd_pay)
cursor.execute(sql_cmd_pay)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
pay_dict[rec[0]] = rec[1]
gettime_dict = dict()
getnum_dict = dict()
data_dict = dict()
# gettime_dict['total'] = dict()
# getnum_dict['total'] = dict()
data_dict['total'] = dict()
for date in input_dict['date_list']:
# 活跃时间，人数，容器
for j in range(0, len(y_list)):
# gettime_dict[y_list[j]] = dict()
# getnum_dict[y_list[j]] = dict()
data_dict[y_list[j]] = dict()
for date in input_dict['date_list']:
# 活跃时间，人数，容器
time_dict = dict()
num_dict = dict()
time_stamp = datetime.datetime.strptime(date,
"%Y-%m-%d").strftime("%Y%m%d")
sql_cmd_active = ''
if hastable(cursor, 'user_active_' + time_stamp, stat_active_name):
# print(time_stamp)
sql_str = 'select uid,online_time/60,1 from ' + stat_active_name + '.user_active_' + time_stamp + ' where 1 ' + where_clause_main + ';'
cursor.execute(sql_str)
# print(sql_str)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
time_dict[rec[0]] = rec[1]
num_dict[rec[0]] = rec[2]
for key in time_dict.keys():
gettime_dict['total'][date] = gettime_dict.setdefault(
'total', dict()).setdefault(date, 0) + time_dict[key]
getnum_dict['total'][date] = getnum_dict.setdefault(
'total', dict()).setdefault(date, 0) + num_dict[key]
# print(len(time_dict))
for j in range(0, len(y_list)):
for key in time_dict.keys():
if pay_dict.has_key(key):
if j == 0:
if int(pay_dict[key]) <= int(y_list[j]):
gettime_dict[
y_list[j]][date] = gettime_dict.setdefault(
y_list[j], dict()).setdefault(
date, 0) + time_dict[key]
getnum_dict[y_list[j]][date] = getnum_dict.setdefault(y_list[j], dict()).setdefault(date, 0) + \
num_dict[key]
# print("this is 0")
elif j != 0 and j <= len(y_list) - 1:
if int(y_list[j - 1]) < int(pay_dict[key]) <= int(
y_list[j]):
gettime_dict[
y_list[j]][date] = gettime_dict.setdefault(
y_list[j], dict()).setdefault(
date, 0) + time_dict[key]
getnum_dict[y_list[j]][date] = getnum_dict.setdefault(y_list[j], dict()).setdefault(date, 0) + \
num_dict[key]
for key in time_dict.keys():
if pay_dict.has_key(key) == False:
gettime_dict[y_list[0]][date] = gettime_dict.setdefault(
y_list[0], dict()).setdefault(date, 0) + time_dict[key]
getnum_dict[y_list[0]][date] = getnum_dict.setdefault(y_list[0], dict()).setdefault(date, 0) + \
num_dict[key]
for key in pay_dict.keys():
if time_dict.has_key(key) and int(pay_dict[key]) > int(
y_list[len(y_list) - 1]):
gettime_dict[str(y_list[len(y_list) - 1]) +
'+'][date] = gettime_dict.setdefault(
str(y_list[len(y_list) - 1]) + '+',
dict()).setdefault(date, 0) + time_dict[key]
getnum_dict[str(y_list[len(y_list)-1])+'+'][date] = getnum_dict.setdefault(str(y_list[len(y_list)-1])+'+', dict()).setdefault(date, 0) + \
num_dict[key]
# print('get5000+'+str(getnum_dict))
# print('get5000t+' + str(gettime_dict))
data_dict['total'][
date] = gettime_dict['total'][date] / getnum_dict['total'][date]
for j in range(0, len(y_list)):
if gettime_dict.has_key(
y_list[j]) and gettime_dict[y_list[j]].has_key(date):
if gettime_dict[y_list[j]][date] != 0:
data_dict[y_list[j]][date] = gettime_dict[
y_list[j]][date] / getnum_dict[y_list[j]][date]
else:
data_dict[y_list[j]][date] = ''
if gettime_dict.has_key(str(y_list[len(y_list) - 1]) +
'+') and gettime_dict[y_list[j]].has_key(date):
if gettime_dict[str(y_list[len(y_list) - 1]) + '+'][date] != 0:
data_dict[str(y_list[len(y_list) - 1]) +
'+'][date] = data_dict.setdefault(
str(y_list[len(y_list) - 1]) + '+',
dict()).setdefault(
date,
gettime_dict[str(y_list[len(y_list) - 1]) +
'+'][date] /
getnum_dict[str(y_list[len(y_list) - 1]) +
'+'][date])
else:
data_dict[str(y_list[len(y_list) - 1]) + '+'][date] = ''
# print(data_dict)
res_dict['data_dict'] = data_dict
# res_dict['data_dict'] = get_dict
res_dict['X_list'] = x_list
res_dict['Y_list'] = ['total'
] + y_list + [str(y_list[len(y_list) - 1]) + '+']
res_dict['Y_trans'] = dict()
for j in range(0, len(y_list)):
if j == 0:
res_dict['Y_trans'][y_list[j]] = '0--' + str(y_list[j])
elif j > 0 and j <= len(y_list) - 1:
res_dict['Y_trans'][y_list[j]] = str(y_list[j - 1]) + '--' + str(
y_list[j])
res_dict['Y_trans'][str(y_list[len(y_list) - 1]) +
'+'] = str(y_list[len(y_list) - 1]) + '以上'
res_dict['default_value'] = 0
res_dict['head_name'] = '每日人均在线时长/分钟'
res_dict['note'] = '每日人均在线时长/分钟'
ress_dict['data_dict'] = getnum_dict
ress_dict['X_list'] = x_list
ress_dict['Y_list'] = ['total'
] + y_list + [str(y_list[len(y_list) - 1]) + '+']
ress_dict['Y_trans'] = dict()
for j in range(0, len(y_list)):
if j == 0:
ress_dict['Y_trans'][y_list[j]] = '0--' + str(y_list[j])
elif j > 0 and j <= len(y_list) - 1:
ress_dict['Y_trans'][y_list[j]] = str(y_list[j - 1]) + '--' + str(
y_list[j])
ress_dict['Y_trans'][str(y_list[len(y_list) - 1]) +
'+'] = str(y_list[len(y_list) - 1]) + '以上'
ress_dict['default_value'] = 0
ress_dict['head_name'] = '每日活跃人数'
ress_dict['note'] = '每日活跃人数'
return [res_dict, ress_dict]
def user_return(input_dict,
money_type_list,
between_days=7): # money_type_list as reception of pay levels
conf = ConfParameters()
easy_sql = EasyMysql()
easy_date = DateList()
currency_rate = conf.current_rate
mysql_conf = conf.mysql_conf
stat_base = mysql_conf['stat_base']
stat_pay = mysql_conf['stat_pay']
stat_userreg = mysql_conf['stat_userreg']
user_active_openid = mysql_conf['user_active_openid']
# argv
x_list = list()
y_trans = dict()
money_list = list()
y_list = [0]
if len(money_type_list) > 0:
money_list = sorted(list(money_type_list))
y_trans = _money_trans(money_list)
y_list = list(range(0, len(money_list) + 1))
print('money_list:', money_list)
print('currency_rate:', currency_rate)
# input dict
date_list = sorted(list(input_dict['date_list']))
where_channel_zone = easy_sql.combine_where_clause(input_dict)
cursor = input_dict['cursor']
# compute day by day
result_return_dict = dict()
result_return_pay_dict = dict()
result_return_money_dict = dict()
for date in date_list:
date_min = easy_date.add_date(date, -1 * between_days)
where_date_between = ' date between \'' + date_min + '\' and \'' + date + '\' ' + where_channel_zone
where_date_lower = ' date < \'' + date_min + '\' ' + where_channel_zone
where_date_eq = ' date = \'' + date + '\' ' + where_channel_zone
sql = 'select uid,ifnull((select ceil(sum(money/100)) from ' + stat_pay + '.pay_syn_day where uid=c.uid and ' + where_date_lower + ' group by uid),0) as money, ifnull((select ceil(sum(money/100)) from ' + stat_pay + '.pay_syn_day where uid=c.uid and ' + where_date_eq + ' group by uid),0) as money from ' + stat_base + '.' + user_active_openid + ' as c where ' + where_date_between + ' and uid not in (select uid from ( select uid from ' + stat_userreg + '.user_register0 where ' + where_date_between + ' union all select uid from ' + stat_userreg + '.user_register1 where ' + where_date_between + ' union all select uid from ' + stat_userreg + '.user_register2 where ' + where_date_between + ' union all select uid from ' + stat_userreg + '.user_register3 where ' + where_date_between + ' union all select uid from ' + stat_userreg + '.user_register4 where ' + where_date_between + ' union all select uid from ' + stat_userreg + '.user_register5 where ' + where_date_between + ' union all select uid from ' + stat_userreg + '.user_register6 where ' + where_date_between + ' union all select uid from ' + stat_userreg + '.user_register7 where ' + where_date_between + ' union all select uid from ' + stat_userreg + '.user_register8 where ' + where_date_between + ' union all select uid from ' + stat_userreg + '.user_register9 where ' + where_date_between + ' )a) group by uid having max(date)=\'' + date + '\' and count(date)=1;'
# print(sql)
cursor.execute(sql)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
uid = rec[0]
money = int(rec[1])
money_today = int(rec[2])
money_type = _get_money_type(money, money_list, currency_rate)
if money_type not in result_return_dict.keys():
result_return_dict[money_type] = dict()
if money_type not in result_return_pay_dict.keys():
result_return_pay_dict[money_type] = dict()
if money_type not in result_return_money_dict.keys():
result_return_money_dict[money_type] = dict()
if money_today > 0:
result_return_pay_dict[money_type][
date] = result_return_pay_dict[money_type].setdefault(
date, 0) + 1
result_return_money_dict[money_type][
date] = result_return_money_dict[
money_type].setdefault(date, 0) + money_today
result_return_dict[money_type][date] = result_return_dict[
money_type].setdefault(date, 0) + 1
print('[Check1]len(result_return_dict):', len(result_return_dict.keys()))
x_list = date_list
res_dict = dict()
res_dict['data_dict'] = result_return_dict
res_dict['X_list'] = x_list
res_dict['Y_list'] = y_list
res_dict['Y_trans'] = y_trans
res_dict['default_value'] = ''
res_dict['head_name'] = '付费用户分段回流'
res_dict['note'] = '*定义回流： ' + str(between_days) + '日前注册的用户，在最近的连续' + str(
between_days - 1) + '日都没有活跃，而当日活跃了，记为当日的回流用户'
res_pay_users = dict()
res_pay_users['data_dict'] = result_return_pay_dict
res_pay_users['X_list'] = x_list
res_pay_users['Y_list'] = y_list
res_pay_users['Y_trans'] = y_trans
res_pay_users['default_value'] = ''
res_pay_users['head_name'] = '回归用户付费人数'
res_pay_users['note'] = '*表1中回归人数中，在当日发生付费的人数'
res_pay_money = dict()
res_pay_money['data_dict'] = result_return_money_dict
res_pay_money['X_list'] = x_list
res_pay_money['Y_list'] = y_list
res_pay_money['Y_trans'] = y_trans
res_pay_money['default_value'] = ''
res_pay_money['head_name'] = '回归用户付费金额'
res_pay_money['note'] = '*表1中回归人数中，在当日发生付费的金额汇总'
return [res_dict, res_pay_users, res_pay_money]
def renratio(input_dict,stat_active_name,stat_reg_name,stat_pay_name,currency_rate, *args):
is_legal_input(input_dict)
easy_sql = EasyMysql()
easy_date = DateList()
cursor = input_dict['cursor']
where_clause_main = easy_sql.combine_where_clause(input_dict)
#[0,100,500,20,30,40,60]
y_list = sorted(list(args))
x_list = [0, 1, 2, 6, 14, 29, 59, 89]
result_dict = dict()
get_dict = dict()
get_dict_list=list()
for i in range(0, len(x_list)):
get_dict_list.append(dict())
# 当日有多少人 建造字典
# get_dict['total'] = dict()
# put_dict['total'] = dict()
# for j in range(0, len(y_list)):
# get_dict[y_list[j]] = dict()
# put_dict[y_list[j]] = dict()
#获得某段时间内的活跃人数
if len(input_dict['date_list'])!=0:
min_time_stamp = min(input_dict['date_list'])
max_time_stamp = max(input_dict['date_list'])
# print(max_time_stamp)
#考察充值情况 距离开服x天
pay_dict=dict()
sql_cmd_pay = 'select uid,sum(money/100)*'+str(currency_rate)+' as money from '+stat_pay_name+'.pay_syn_day where date<=\''+max_time_stamp+'\'' +where_clause_main+' group by uid;'
# print(sql_cmd_pay)
cursor.execute(sql_cmd_pay)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
pay_dict[rec[0]]=rec[1]
for date in input_dict['date_list']:
where_clause = ' select uid from ( select uid from ' + stat_reg_name + '.user_register0 where date=\''+date+ '\''+where_clause_main
for i in range(1, 10):
where_clause = where_clause + ' union all select uid from ' + stat_reg_name + '.user_register' + str(i) + ' where date=\''+date+ '\' '+where_clause_main
where_clause = where_clause + ')a '
sql_cmd_regdate = where_clause
# print(sql_cmd_regdate)
total_dict = dict()
cursor.execute(sql_cmd_regdate)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
total_dict[rec[0]] = dict()
total_dict[rec[0]]['money'] = pay_dict.setdefault(rec[0],0)
# print(total_dict)
for i in range(0,len(x_list)):
time_stamp = addtime(date,x_list[i]).strftime('%Y%m%d')
if hastable(cursor,'user_active_'+time_stamp,stat_active_name):
# print(time_stamp)
if time_stamp <= addtime(max_time_stamp,0).strftime('%Y%m%d'):
sql_str = 'select uid from '+stat_active_name+'.user_active_' + time_stamp +' where 1 '+where_clause_main+';'
# print(sql_str)
cursor.execute(sql_str)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
if total_dict.has_key(rec[0]):
if rec[0] in total_dict.keys():
total_dict[rec[0]][x_list[i]]=1
else:
total_dict[rec[0]][x_list[i]]=0
# print(total_dict)
for i in range(0,len(x_list)):
for k in total_dict.keys():
if total_dict[k].has_key(x_list[i]):
get_dict_list[i]['total'][date] = get_dict_list[i].setdefault('total',dict()).setdefault(date,0)+ total_dict[k].setdefault(x_list[i],0)
for j in range(0, len(y_list)):
if j == 0:
for k in total_dict.keys():
if total_dict[k]['money'] <= int(y_list[j]):
if total_dict[k].has_key(x_list[i]):
get_dict_list[i][y_list[j]][date] = get_dict_list[i].setdefault(y_list[j], dict()).setdefault(date, 0) + \
total_dict[k].setdefault(x_list[i], 0)
elif j != 0 and j <= len(y_list) - 1:
for k in total_dict.keys():
if int(y_list[j - 1]) < total_dict[k]['money'] <= int(y_list[j]):
if total_dict[k].has_key(x_list[i]):
get_dict_list[i][y_list[j]][date] = get_dict_list[i].setdefault(y_list[j], dict()).setdefault(date, 0) + \
total_dict[k].setdefault(x_list[i], 0)
for k in total_dict.keys():
if total_dict[k]['money'] > int(y_list[len(y_list) - 1]):
if total_dict[k].has_key(x_list[i]):
get_dict[str(y_list[len(y_list) - 1]) + '+'][date] = get_dict.setdefault(str(y_list[len(y_list) - 1]) + '+', dict()).setdefault(date, 0) + total_dict[k].setdefault(i,0)
# print(get_dict_list)
put_dict_list=[]
for i in range(0, len(get_dict_list)):
put_dict = dict()
for key in get_dict_list[i].keys():
for keyin in get_dict_list[i][key].keys():
if i == 0:
put_dict.setdefault(key, dict()).setdefault(keyin, get_dict_list[i][key][keyin])
else:
put_dict.setdefault(key, dict()).setdefault(keyin, round(float(get_dict_list[i][key][keyin]) / get_dict_list[0][key][keyin], 2))
put_dict_list.append(put_dict)
# print(put_dict_list)
res_dict_list=[]
for i in range(0,len(put_dict_list)):
res_dict = dict()
# res_dict['data_dict'] = get_dict
res_dict['data_dict'] = put_dict_list[i]
res_dict['X_list'] = sorted(input_dict['date_list'])
# res_dict['X_trans'] = dict(zip(x_list,list(map(lambda x:"第"+str(x+1)+"日留存",x_list))))
res_dict['Y_list'] = ['total'] + y_list+[str(y_list[len(y_list)-1])+'+']
res_dict['Y_trans'] = dict()
for j in range(0,len(y_list)):
if j==0:
res_dict['Y_trans'][y_list[j]]='0--'+str(y_list[j])
elif j>0 and j<=len(y_list)-1:
res_dict['Y_trans'][y_list[j]] = str(y_list[j-1])+'--'+str(y_list[j])
res_dict['Y_trans'][str(y_list[len(y_list)-1])+'+'] = str(y_list[len(y_list)-1])+'以上'
res_dict['default_value'] = ''
res_dict['head_name'] = '第'+str(x_list[i]+1)+'日留存情况'
res_dict['note'] = '付费段的留存情况'
res_dict_list.append(res_dict)
return res_dict_list
def life_time_compute(input_dict, *argv): # *argv as reception of pay levels
conf = ConfParameters()
easy_sql = EasyMysql()
currency_rate = conf.current_rate
mysql_conf = conf.mysql_conf
stat_base = mysql_conf['stat_base']
stat_pay = mysql_conf['stat_pay']
user_active_openid = mysql_conf['user_active_openid']
# argv\
x_list = list()
y_trans = dict()
money_list = list()
y_list = [0]
if len(argv) > 0:
money_list = sorted(list(argv[0]))
y_trans = _money_trans(money_list)
y_list = list(range(0, len(money_list) + 1))
print('money_list:', money_list)
print('currency_rate:', currency_rate)
# input dict
date_list = sorted(list(input_dict['date_list']))
where_clause_main = easy_sql.combine_where_clause(input_dict)
min_date = date_list[0]
max_date = date_list[len(date_list) - 1]
cursor = input_dict['cursor']
# where clause
where_clause = 'where date between \'' + min_date + '\' and \'' + max_date + '\' '
where_clause = where_clause + where_clause_main
user_count_dict = dict() # user count
user_count_remain_dict = dict() # user remain count
user_count_remain_ratio_dict = dict() # user remain ratio
user_count_lost_dict = dict() # user lost life time
user_count_lost_user_dict = dict() # user lost count
sql_cmd = 'select uid,count(distinct date), max(date), min(date),ifnull((select ceil(sum(money/100)) from ' + stat_pay + '.pay_syn_day ' + where_clause + ' and uid=a.uid group by uid),0) as money from ' + stat_base + '.' + user_active_openid + ' as a ' + where_clause + ' group by uid'
print(sql_cmd)
cursor.execute(sql_cmd)
one_data = cursor.fetchone()
while one_data:
rec = one_data
uid = str(rec[0])
life_time = int(rec[1])
last_date = str(rec[2])
reg_date = str(rec[3])
money = int(rec[4])
money_type = _get_money_type(money, money_list, currency_rate)
lost = 1
if max_date == last_date:
lost = 0
if reg_date not in x_list:
x_list.append(reg_date)
if money_type not in user_count_dict.keys():
user_count_dict[money_type] = dict()
user_count_remain_dict[money_type] = dict()
user_count_remain_ratio_dict[money_type] = dict()
user_count_lost_dict[money_type] = dict()
user_count_lost_user_dict[money_type] = dict()
user_count_dict[money_type][
reg_date] = user_count_dict[money_type].setdefault(reg_date, 0) + 1
if lost == 1:
user_count_lost_dict[money_type][
reg_date] = user_count_lost_dict[money_type].setdefault(
reg_date, 0) + life_time
user_count_lost_user_dict[money_type][
reg_date] = user_count_lost_user_dict[money_type].setdefault(
reg_date, 0) + 1
else:
user_count_remain_dict[money_type][
reg_date] = user_count_remain_dict[money_type].setdefault(
reg_date, 0) + 1
user_count_remain_ratio_dict[money_type][
reg_date] = user_count_remain_ratio_dict[
money_type].setdefault(reg_date, 0) + 1
one_data = cursor.fetchone()
print('[Check1]len(user_count_dict):', len(user_count_dict.keys()))
x_list = sorted(x_list)
for money_type in user_count_lost_dict.keys():
for reg_date in user_count_lost_dict[money_type].keys():
value = 0
if user_count_lost_dict[money_type][
reg_date] > 0 and user_count_lost_user_dict[
money_type].setdefault(reg_date, 0) > 0:
value = float(user_count_lost_dict[money_type][reg_date]) / (
user_count_lost_user_dict[money_type].setdefault(
reg_date, 0))
user_count_lost_dict[money_type][reg_date] = value
for money_type in user_count_remain_ratio_dict.keys():
for reg_date in user_count_remain_ratio_dict[money_type].keys():
value = 0
if user_count_remain_ratio_dict[money_type][
reg_date] > 0 and user_count_dict[money_type].setdefault(
reg_date, 0) > 0:
value = float(
user_count_remain_ratio_dict[money_type]
[reg_date]) / user_count_dict[money_type][reg_date]
user_count_remain_ratio_dict[money_type][reg_date] = value
res_dict = dict()
res_dict['data_dict'] = user_count_dict
res_dict['X_list'] = x_list
res_dict['Y_list'] = y_list
res_dict['Y_trans'] = y_trans
res_dict['default_value'] = ''
res_dict['head_name'] = '注册用户按付费分层'
res_dict['note'] = ''
res_dict_remain = dict()
res_dict_remain['data_dict'] = user_count_remain_dict
res_dict_remain['X_list'] = x_list
res_dict_remain['Y_list'] = y_list
res_dict_remain['Y_trans'] = y_trans
res_dict_remain['default_value'] = ''
res_dict_remain['head_name'] = '付费段留存人数'
res_dict_remain['note'] = ''
res_dict_remain_ratio = dict()
res_dict_remain_ratio['data_dict'] = user_count_remain_ratio_dict
res_dict_remain_ratio['X_list'] = x_list
res_dict_remain_ratio['Y_list'] = y_list
res_dict_remain_ratio['Y_trans'] = y_trans
res_dict_remain_ratio['default_value'] = ''
res_dict_remain_ratio['head_name'] = '付费段留存率'
res_dict_remain_ratio['note'] = ''
res_dict_lost = dict()
res_dict_lost['data_dict'] = user_count_lost_user_dict
res_dict_lost['X_list'] = x_list
res_dict_lost['Y_list'] = y_list
res_dict_lost['Y_trans'] = y_trans
res_dict_lost['default_value'] = ''
res_dict_lost['head_name'] = '付费段流失人数'
res_dict_lost['note'] = ''
res_dict_lost_life = dict()
res_dict_lost_life['data_dict'] = user_count_lost_dict
res_dict_lost_life['X_list'] = x_list
res_dict_lost_life['Y_list'] = y_list
res_dict_lost_life['Y_trans'] = y_trans
res_dict_lost_life['default_value'] = ''
res_dict_lost_life['head_name'] = '付费段流失用户生命周期'
res_dict_lost_life['note'] = ''
return [
res_dict, res_dict_remain, res_dict_remain_ratio, res_dict_lost,
res_dict_lost_life
]
def event_login(input_dict, stat_reg_name):
is_legal_input(input_dict)
easy_sql = EasyMysql()
cursor = input_dict['cursor']
where_clause_main = easy_sql.combine_where_clause(input_dict, use_zoneids=False)
x_list = input_dict['date_list']
y_list = ['n_device', 'n_openid', 'pass_rate']
y_trans = {'n_device' : '新注册设备数', 'n_openid' : '新注册openid数', 'pass_rate': '转化率'}
result_dict = dict()
date_list = input_dict['date_list']
min_date = date_list[0] + ' 00:00:00'
max_date = date_list[len(date_list)-1] + ' 23:59:59'
where_clause = ' where date between \'' + min_date + '\' and \'' + max_date + '\' ' + where_clause_main
# user reg
sql_cmd = 'select date,sum(reg_count) from (select date,count(*) as reg_count from ' + stat_reg_name + '.user_register_openid0 ' + where_clause + ' group by date'
for i in range(1, 10):
sql_cmd = sql_cmd + ' union all select date,count(*) as reg_count from ' + stat_reg_name + '.user_register_openid' + str(i) + where_clause + ' group by date'
sql_cmd = sql_cmd + ')a group by date'
print(sql_cmd)
cursor.execute(sql_cmd)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
date = str(rec[0])
user = int(rec[1])
if 'n_openid' not in result_dict.keys():
result_dict['n_openid'] = dict()
result_dict['n_openid'][date] = user
# new device
sql_cmd = 'select date_n,sum(reg_count) from (select date_format(date,\'%Y-%m-%d\') as date_n,count(*) as reg_count from ' + stat_reg_name + '.user_device0 ' + where_clause + ' group by date_n'
for i in range(1, 10):
sql_cmd = sql_cmd + ' union all select date_format(date,\'%Y-%m-%d\') as date_n,count(*) as reg_count from ' + stat_reg_name + '.user_device' + str(
i) + where_clause + ' group by date_n'
sql_cmd = sql_cmd + ')a group by date_n'
print(sql_cmd)
cursor.execute(sql_cmd)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
date = str(rec[0])
user = int(rec[1])
if 'n_device' not in result_dict.keys():
result_dict['n_device'] = dict()
result_dict['n_device'][date] = user
# compute ratio
result_dict['pass_rate'] = dict()
for date in date_list:
if date in result_dict['n_device'].keys() and date in result_dict['n_openid'].keys() and result_dict['n_device'][date]>0:
result_dict['pass_rate'][date] = result_dict['n_openid'][date] / float(result_dict['n_device'][date])
res_dict = dict()
res_dict['data_dict'] = result_dict
res_dict['X_list'] = x_list
res_dict['Y_list'] = y_list
res_dict['Y_trans'] = y_trans
res_dict['default_value'] = 0
res_dict['head_name'] = '启动加载项通过率'
res_dict['note'] = '*按照当日的新增OPEN数/新增设备数，作为设备通过率的通用指标'
#
return res_dict