| Line 46... |
Line 46... |
| 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
|
| 47 |
from users u left join useractive ua on ua.user_id=u.id left join usergroups ug on ug.id=u.usergroup_id
|
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 |
left join (select distinct user_id from price_preferences) pp on pp.user_id=u.id
|
48 |
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
|
49 |
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
|
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
|
| 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 lower(u.referrer) is null) and u.activated = 1) final;
|
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_source is not null) and u.activated = 1) final;
|
| 52 |
"""
|
52 |
"""
|
| 53 |
|
53 |
|
| 54 |
GROUP_SEGMENTATION_QUERY = """
|
54 |
GROUP_SEGMENTATION_QUERY = """
|
| 55 |
select case
|
55 |
select case
|
| 56 |
when created >= date(now()) - interval 3 day and total > 0 then 'ABU'
|
56 |
when created >= date(now()) - interval 3 day and total > 0 then 'ABU'
|
| Line 65... |
Line 65... |
| 65 |
max(u.created) ucreated, min(u.created) minucreated, max(ua.last_active) last_active, max(ow.created) created, if(sum(ow.total) is null, 0, sum(ow.total)) as total
|
65 |
max(u.created) ucreated, min(u.created) minucreated, max(ua.last_active) last_active, max(ow.created) created, if(sum(ow.total) is null, 0, sum(ow.total)) as total
|
| 66 |
from users u left join useractive ua on ua.user_id=u.id left join usergroups ug on ug.id=u.usergroup_id
|
66 |
from users u left join useractive ua on ua.user_id=u.id left join usergroups ug on ug.id=u.usergroup_id
|
| 67 |
left join (select *, count(*) as total from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED')
|
67 |
left join (select *, count(*) as total from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED')
|
| 68 |
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
|
68 |
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
|
| 69 |
on ow.user_id = u.id
|
69 |
on ow.user_id = u.id
|
| 70 |
where (lower(u.referrer) not like 'emp%' or lower(u.referrer) is null) and u.activated=1 group by usergroup_id) final;
|
70 |
where (lower(u.referrer) not like 'emp%' or u.utm_source is not null) and u.activated=1 group by usergroup_id) final;
|
| 71 |
"""
|
71 |
"""
|
| 72 |
|
72 |
|
| 73 |
|
73 |
|
| 74 |
date_format = xlwt.XFStyle()
|
74 |
date_format = xlwt.XFStyle()
|
| 75 |
date_format.num_format_str = 'dd/mm/yyyy'
|
75 |
date_format.num_format_str = 'dd/mm/yyyy'
|