| Line 41... |
Line 41... |
| 41 |
end
|
41 |
end
|
| 42 |
usersegment, final.*
|
42 |
usersegment, final.*
|
| 43 |
from (select u.id, u.username, u.first_name, u.mobile_number, u.mobile_verified,u.usergroup_id, ug.groupbasis,
|
43 |
from (select u.id, u.username, u.first_name, u.mobile_number, u.mobile_verified,u.usergroup_id, ug.groupbasis,
|
| 44 |
if(bp.user_id is null, 'FALSE', 'TRUE') as bpref,
|
44 |
if(bp.user_id is null, 'FALSE', 'TRUE') as bpref,
|
| 45 |
if(pp.user_id is null, 'FALSE', 'TRUE') as ppref, u.created ucreated, ua.last_active, ow.created, if(ow.total is null, 0, ow.total) as total,
|
45 |
if(pp.user_id is null, 'FALSE', 'TRUE') as ppref, u.created ucreated, ua.last_active, ow.created, if(ow.total is null, 0, ow.total) as total,
|
| 46 |
u.referrer,u.utm_campaign, u.utm_content, u.utm_term, u.utm_medium, u.utm_source, u.activated, r.id as retailer_id, r.status
|
46 |
u.referrer,u.utm_campaign, u.utm_content, u.utm_term, u.utm_medium, u.utm_source, u.activated, r.id as retailer_id, r.status, d.versioncode
|
| - |
|
47 |
, uas.comment, aua.store_name, aua.city, aua.pincode,aua.state
|
| 47 |
from users u left join useractive ua on ua.user_id=u.id left join usergroups ug on ug.id=u.usergroup_id
|
48 |
from users u left join useractive ua on ua.user_id=u.id
|
| - |
|
49 |
left join usergroups ug on ug.id=u.usergroup_id
|
| 48 |
left join (select distinct user_id from price_preferences) pp on pp.user_id=u.id
|
50 |
left join (select distinct user_id from price_preferences) pp on pp.user_id=u.id
|
| 49 |
left join (select distinct user_id from brand_preferences) bp on bp.user_id=u.id
|
51 |
left join (select distinct user_id from brand_preferences) bp on bp.user_id=u.id
|
| 50 |
left join (select *, count(*) as total from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') union all (select user_id, created from flipkartorders where date(created) >'2015-03-22') order by created desc) as a1 group by user_id) as ow on ow.user_id = u.id
|
52 |
left join (select *, count(*) as total from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') union all (select user_id, created from flipkartorders where date(created) >'2015-03-22') order by created desc) as a1 group by user_id) as ow on ow.user_id = u.id
|
| - |
|
53 |
left join user_activity_status uas on uas.user_id=u.id
|
| - |
|
54 |
left join all_user_addresses aua on aua.user_id=u.id
|
| - |
|
55 |
left join (select * from (select user_id, versioncode from devices order by created desc) c group by user_id) d on d.user_id=u.id
|
| 51 |
left join retailerlinks rl on rl.user_id=u.id left join retailers r on r.id=rl.retailer_id where (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) and u.activated = 1) final;
|
56 |
left join retailerlinks rl on rl.user_id=u.id left join retailers r on r.id=rl.retailer_id where (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) and u.activated = 1) final;
|
| 52 |
"""
|
57 |
"""
|
| 53 |
GROUP_SEGMENTATION_QUERY = """
|
58 |
GROUP_SEGMENTATION_QUERY = """
|
| 54 |
select case
|
59 |
select case
|
| 55 |
when created >= date(now()) - interval 3 day and total > 0 then 'ABU'
|
60 |
when created >= date(now()) - interval 3 day and total > 0 then 'ABU'
|
| Line 123... |
Line 128... |
| 123 |
worksheet.write(row, 18, 'Medium', boldStyle)
|
128 |
worksheet.write(row, 18, 'Medium', boldStyle)
|
| 124 |
worksheet.write(row, 19, 'Utm Source', boldStyle)
|
129 |
worksheet.write(row, 19, 'Utm Source', boldStyle)
|
| 125 |
worksheet.write(row, 20, 'Activation Flag', boldStyle)
|
130 |
worksheet.write(row, 20, 'Activation Flag', boldStyle)
|
| 126 |
worksheet.write(row, 21, 'Retailer Id', boldStyle)
|
131 |
worksheet.write(row, 21, 'Retailer Id', boldStyle)
|
| 127 |
worksheet.write(row, 22, 'Retailer Status', boldStyle)
|
132 |
worksheet.write(row, 22, 'Retailer Status', boldStyle)
|
| - |
|
133 |
worksheet.write(row, 23, 'Version Code', boldStyle)
|
| - |
|
134 |
worksheet.write(row, 24, 'Install', boldStyle)
|
| - |
|
135 |
worksheet.write(row, 25, 'Store', boldStyle)
|
| - |
|
136 |
worksheet.write(row, 26, 'City', boldStyle)
|
| - |
|
137 |
worksheet.write(row, 27, 'Pincode', boldStyle)
|
| - |
|
138 |
worksheet.write(row, 28, 'State', boldStyle)
|
| 128 |
|
139 |
|
| 129 |
for r in result:
|
140 |
for r in result:
|
| 130 |
row += 1
|
141 |
row += 1
|
| 131 |
column = 0
|
142 |
column = 0
|
| 132 |
for data in r :
|
143 |
for data in r :
|