1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
from utils import con_sql,get_yesterday_date
from getClickZeroUidDetail import get_click_zero_uid_count
from getRegisterUidDetail import get_register_uid_count
#获取各个平台下的活跃用户点击率
def get_activate_uid_ctr(platform):
"""
platform : 'all';'ios';'android'
rtype : list
"""
if platform == "ios":
platform = "='App Store'"
elif platform == "android":
platform = "!='App Store'"
else:
platform = " is not null"
sql_clk = "select count(device_id) from data_feed_click \
where stat_date = '{0}' \
and device_type{1}".format(get_yesterday_date(), platform.replace(' ','') if platform[-2]=='e' else platform)
clk_count = con_sql(sql_clk)[0][0]
sql_imp = "select count(device_id) from data_feed_exposure \
where stat_date = '{0}' \
and device_id in \
(select device_id from data_feed_click \
where stat_date = '{0}' \
and device_type{1}) \
and device_type{2}".format(get_yesterday_date(), platform.replace(' ','') if platform[-2]=='e' else platform, platform)
imp_count = con_sql(sql_imp)[0][0]
clk_rate = round(clk_count/imp_count, 4 )
if platform == "='App Store'":
platform = "苹果"
elif platform == "!='App Store'":
platform = "安卓"
else:
platform = "所有"
return [platform, clk_count, imp_count, clk_rate]
#获取活跃用户平均每天曝光次数
def get_activate_uid_imp_times(city):
"""
city : 'beijing';'all'
rtype : list
"""
if city == "beijing":
city = "='beijing'"
else:
city = " is not null"
sql_uid = "select count(distinct(device_id)) from data_feed_click \
where stat_date = '{0}' \
and city_id{1}".format(get_yesterday_date(),city)
sql_uid_count = con_sql(sql_uid)[0][0]
sql_imp = "select count(device_id) from data_feed_exposure \
where device_id in \
(select device_id from data_feed_click \
where stat_date = '{0}' \
and city_id{1}) \
and stat_date = '{0}' \
and city_id{1}".format(get_yesterday_date(),city)
sql_imp_times = con_sql(sql_imp)[0][0]
if city == "='beijing'":
city = "北京"
else:
city = "所有"
return [city,sql_uid_count,sql_imp_times,round(sql_imp_times/sql_uid_count,2)]
#获取无点击用户数分布(=无点击用户∩激活用户数 / 激活用户数) ；并且根据平台和激活日记来分
def get_click_zero_uid_rate_detail(platform):
"""
platform : "ios","android","all"
rtype : dict
"""
dct1 = get_click_zero_uid_count(platform)
dct2 = get_register_uid_count()
result = {}
for k in dct1:
result[k] = round(dct1[k]/dct2[k],4)
return result
#获取 (用户点击次数 : 独立用户数)
def get_click_times_to_count_uid():
"""
rtype : tuple
"""
sql = "select times,count(device_id) \
from (select device_id,count(cid_type) as times \
from data_feed_click \
where from_unixtime(time,'%Y-%m-%d')=date_add(curdate(), interval -1 day) \
group by device_id) as t \
group by times order by times"
result = con_sql(sql)
return result