WebSVN – SmartDukaan – Diff – //trunk/PyDTR/src/dtr/reports/acquisition/dtrcohortanalysis.py



SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587
buyer="""select year(u.created) creationyear,month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(created) yearly, month(created) monthly 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 a1.user_id, year(a1.created),month(a1.created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created), uu.yearly, uu.monthly;"""
 
activeuser="""select year(u.created) creationyear, month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(visited) yearly,month(visited) monthly  from daily_visitors group by user_id, year(visited), month(visited)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created),uu.yearly, uu.monthly;"""
 
activegroup = """select year(created) creationmonth,month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(visited) yearly,month(visited) monthly  from daily_visitors group by user_id,  year(visited),month(visited)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null)  group by u.usergroup_id, uu.yearly, uu.monthly) a group by year(created), month(created), a.yearly, a.monthly;"""
 
 
buyergroup = """select year(created) creationmonth, month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(created) yearly, month(created) monthly from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') union all select user_id, year(created) yearly, month(created) monthly from flipkartorders where date(created) >'2015-03-22' group by user_id, year(created), month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null)  group by u.usergroup_id, uu.monthly) a group by year(created), month(created),a.yearly,a.monthly;"""
 
months = {1:"Jan",2:"Feb",3:"Mar",4:"Apr",5:"May",6:"Jun",7:"Jul",8:"Aug",9:"Sep",10:"Oct",11:"Nov",12:"Dec"}

        cursor.execute(buyergroup)
        result = cursor.fetchall()
        parseResultToSheet(worksheet,result, "Buyer Groups", nextrow)
        
        workbook.save("/tmp/cohortreport.xls")
        sendmail(['rajneesh.arora@saholic.com','manas.kapoor@shop2020.in'], "", "/tmp/cohortreport.xls", "Report for Cohort analysis")
    finally:
        conn.close()
 
def parseResultToSheet(sheet, result, title, startrow):
    sheet.write(startrow, 0, title, boldStyle)

Subversion Repositories SmartDukaan

(root)//trunk/PyDTR/src/dtr/reports/acquisition/dtrcohortanalysis.py – Rev 15851 → 17744

Rev 15851		Rev 17744
Line 35...		Line 35...
35	`SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()`	35	`SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()`
36	`SMTP_SERVER = "smtp.gmail.com"`	36	`SMTP_SERVER = "smtp.gmail.com"`
37	`SMTP_PORT = 587`	37	`SMTP_PORT = 587`
38	buyer="""select year(u.created) creationyear,month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(created) yearly, month(created) monthly 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 a1.user_id, year(a1.created),month(a1.created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created), uu.yearly, uu.monthly;"""	38	buyer="""select year(u.created) creationyear,month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(created) yearly, month(created) monthly 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 a1.user_id, year(a1.created),month(a1.created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created), uu.yearly, uu.monthly;"""
39		39
40	`activeuser="""select year(u.created) creationyear, month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(created) yearly,month(created) monthly from user_urls group by user_id, year(created), month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created),uu.yearly, uu.monthly;"""`	40	`activeuser="""select year(u.created) creationyear, month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(visited) yearly,month(visited) monthly from daily_visitors group by user_id, year(visited), month(visited)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created),uu.yearly, uu.monthly;"""`
41		41
42	activegroup = """select year(created) creationmonth,month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(created) yearly,month(created) monthly from user_urls group by user_id, year(created),month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by u.usergroup_id, uu.yearly, uu.monthly) a group by year(created), month(created), a.yearly, a.monthly;"""	42	activegroup = """select year(created) creationmonth,month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(visited) yearly,month(visited) monthly from daily_visitors group by user_id, year(visited),month(visited)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by u.usergroup_id, uu.yearly, uu.monthly) a group by year(created), month(created), a.yearly, a.monthly;"""
43		43
44		44
45	buyergroup = """select year(created) creationmonth, month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(created) yearly, month(created) monthly from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') union all select user_id, year(created) yearly, month(created) monthly from flipkartorders where date(created) >'2015-03-22' group by user_id, year(created), month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by u.usergroup_id, uu.monthly) a group by year(created), month(created),a.yearly,a.monthly;"""	45	buyergroup = """select year(created) creationmonth, month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(created) yearly, month(created) monthly from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') union all select user_id, year(created) yearly, month(created) monthly from flipkartorders where date(created) >'2015-03-22' group by user_id, year(created), month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by u.usergroup_id, uu.monthly) a group by year(created), month(created),a.yearly,a.monthly;"""
46		46
47	`months = {1:"Jan",2:"Feb",3:"Mar",4:"Apr",5:"May",6:"Jun",7:"Jul",8:"Aug",9:"Sep",10:"Oct",11:"Nov",12:"Dec"}`	47	`months = {1:"Jan",2:"Feb",3:"Mar",4:"Apr",5:"May",6:"Jun",7:"Jul",8:"Aug",9:"Sep",10:"Oct",11:"Nov",12:"Dec"}`
Line 75...		Line 75...
75	`cursor.execute(buyergroup)`	75	`cursor.execute(buyergroup)`
76	`result = cursor.fetchall()`	76	`result = cursor.fetchall()`
77	`parseResultToSheet(worksheet,result, "Buyer Groups", nextrow)`	77	`parseResultToSheet(worksheet,result, "Buyer Groups", nextrow)`
78		78
79	`workbook.save("/tmp/cohortreport.xls")`	79	`workbook.save("/tmp/cohortreport.xls")`
80	`sendmail(['rajneesh.arora@saholic.com'], "", "/tmp/cohortreport.xls", "Report for Cohort analysis")`	80	`sendmail(['rajneesh.arora@saholic.com','manas.kapoor@shop2020.in'], "", "/tmp/cohortreport.xls", "Report for Cohort analysis")`
81	`finally:`	81	`finally:`
82	`conn.close()`	82	`conn.close()`
83		83
84	`def parseResultToSheet(sheet, result, title, startrow):`	84	`def parseResultToSheet(sheet, result, title, startrow):`
85	`sheet.write(startrow, 0, title, boldStyle)`	85	`sheet.write(startrow, 0, title, boldStyle)`