| Line 37... |
Line 37... |
| 37 |
when ucreated < date(now()) - interval 3 day and total = 0 and last_active < date(now()) - interval 3 day then 'INU'
|
37 |
when ucreated < date(now()) - interval 3 day and total = 0 and last_active < date(now()) - interval 3 day then 'INU'
|
| 38 |
when ucreated >= date(now()) - interval 3 day and total = 0 and date(last_active) = date(ucreated) then 'FIU'
|
38 |
when ucreated >= date(now()) - interval 3 day and total = 0 and date(last_active) = date(ucreated) then 'FIU'
|
| 39 |
else 'OTH'
|
39 |
else 'OTH'
|
| 40 |
end
|
40 |
end
|
| 41 |
usersegment, final.*
|
41 |
usersegment, final.*
|
| 42 |
from (select u.id, u.username, u.first_name, u.mobile_number, u.referrer,
|
42 |
from (select u.id, u.username, u.first_name, u.mobile_number, u.mobile_verified, u.referrer,
|
| 43 |
if(bp.user_id is null, 'FALSE', 'TRUE') as bpref,
|
43 |
if(bp.user_id is null, 'FALSE', 'TRUE') as bpref,
|
| 44 |
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
|
44 |
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 |
from users u left join useractive ua on ua.user_id=u.id
|
45 |
from users u left join useractive ua on ua.user_id=u.id
|
| 46 |
left join (select distinct user_id from price_preferences) pp on pp.user_id=u.id
|
46 |
left join (select distinct user_id from price_preferences) pp on pp.user_id=u.id
|
| 47 |
left join (select distinct user_id from brand_preferences) bp on bp.user_id=u.id
|
47 |
left join (select distinct user_id from brand_preferences) bp on bp.user_id=u.id
|
| Line 85... |
Line 85... |
| 85 |
worksheet.write(row, 0, 'User Segment', boldStyle)
|
85 |
worksheet.write(row, 0, 'User Segment', boldStyle)
|
| 86 |
worksheet.write(row, 1, 'User Id', boldStyle)
|
86 |
worksheet.write(row, 1, 'User Id', boldStyle)
|
| 87 |
worksheet.write(row, 2, 'Username', boldStyle)
|
87 |
worksheet.write(row, 2, 'Username', boldStyle)
|
| 88 |
worksheet.write(row, 3, 'Name', boldStyle)
|
88 |
worksheet.write(row, 3, 'Name', boldStyle)
|
| 89 |
worksheet.write(row, 4, 'Mobile', boldStyle)
|
89 |
worksheet.write(row, 4, 'Mobile', boldStyle)
|
| - |
|
90 |
worksheet.write(row, 5, 'Mobile Verified', boldStyle)
|
| 90 |
worksheet.write(row, 5, 'Referrer', boldStyle)
|
91 |
worksheet.write(row, 6, 'Referrer', boldStyle)
|
| 91 |
worksheet.write(row, 6, 'Brand Preference', boldStyle)
|
92 |
worksheet.write(row, 7, 'Brand Preference', boldStyle)
|
| 92 |
worksheet.write(row, 7, 'Pricing Preference', boldStyle)
|
93 |
worksheet.write(row, 8, 'Pricing Preference', boldStyle)
|
| 93 |
worksheet.write(row, 8, 'User Created On', boldStyle)
|
94 |
worksheet.write(row, 9, 'User Created On', boldStyle)
|
| 94 |
worksheet.write(row, 9, 'User Last Active On', boldStyle)
|
95 |
worksheet.write(row, 10, 'User Last Active On', boldStyle)
|
| 95 |
worksheet.write(row, 10, 'Order Last Created On', boldStyle)
|
96 |
worksheet.write(row, 11, 'Order Last Created On', boldStyle)
|
| 96 |
worksheet.write(row, 11, 'Total Orders', boldStyle)
|
97 |
worksheet.write(row, 12, 'Total Orders', boldStyle)
|
| 97 |
|
98 |
|
| 98 |
for r in result:
|
99 |
for r in result:
|
| 99 |
row += 1
|
100 |
row += 1
|
| 100 |
column = 0
|
101 |
column = 0
|
| 101 |
for data in r :
|
102 |
for data in r :
|