r78960 MediaWiki - Code Review archive

Repository:	MediaWiki
Revision:	< r78959‎ \| r78960 \| r78961 >
Date:	14:16, 24 December 2010
Author:	rfaulk
Status:	deferred
Tags:
Comment:	Adding confidence test for landing pages.
Modified paths:	/trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py (modified) (history) /trunk/fundraiser-statistics/fundraiser-scripts/query_store.py (modified) (history) /trunk/fundraiser-statistics/fundraiser-scripts/run_confidence_plot.py (added) (history) /trunk/fundraiser-statistics/fundraiser-scripts/sql/report_confidence_banner.sql (added) (history) /trunk/fundraiser-statistics/fundraiser-scripts/sql/report_confidence_lp.sql (added) (history) /trunk/fundraiser-statistics/reporting/banner_test_by_lp.sql (added) (history) /trunk/fundraiser-statistics/reporting/compare_test.php (modified) (history) /trunk/fundraiser-statistics/reporting/fundraiser_analytics.html (modified) (history) /trunk/fundraiser-statistics/reporting/landing_page_compare.html (added) (history) /trunk/fundraiser-statistics/standalone-queries/imp.sql (modified) (history) /trunk/fundraiser-statistics/standalone-queries/imp_vis.sql (modified) (history) /trunk/fundraiser-statistics/standalone-queries/imp_vis_ecomm.sql (modified) (history) /trunk/fundraiser-statistics/standalone-queries/vis.sql (modified) (history)

Diff [purge]

Index: trunk/fundraiser-statistics/standalone-queries/imp.sql
—	—	@@ -1,8 +1,8 @@
2	2
3	3
4		~~-set @s = '20101201013000';~~
5		~~-set @e = '20101202013000';~~
6		~~-set @ctrl1 = 'ControlBanner';~~
	4	+set @s = '20101206000000';
	5	+set @e = '20101219000000';
	6	+-- set @ctrl1 = 'ControlBanner';
7	7
8	8
9	9	drop table if exists query_bin_imp;
—	—	@@ -10,12 +10,14 @@
11	11	create table query_bin_imp as
12	12
13	13	select
	14	+DATE_FORMAT(on_minute,'%Y-%m-%d') as ts_day,
14	15	utm_source,
15	16	sum(counts) as impressions
16	17	from impression
17		~~-where on_minute >= @s and on_minute < @e~~
18		~~-group by 1 order by 1;~~
	18	+where on_minute >= @s and on_minute < @e
	19	+and (utm_source = '2010_JA1_Banner3_NL' or utm_source = '2010_JA1_Banner3')
	20	+and country = 'NL'
	21	+group by 1,2 order by 1;
19	22
20	23
21	24
Index: trunk/fundraiser-statistics/standalone-queries/imp_vis_ecomm.sql
—	—	@@ -27,9 +27,9 @@
28	28
29	29	use faulkner;
30	30
31		~~-set @s = '20101217185500';~~
32		~~-set @e = '20101217215500';~~
33		~~-set @campaign = '20101217JA033';~~
	31	+set @s = '20101223183800';
	32	+set @e = '20101224010000';
	33	+set @campaign = '20101223JA056';
34	34
35	35
36	36	drop table if exists query_bin_imp_vis_ecomm;
Index: trunk/fundraiser-statistics/standalone-queries/vis.sql
—	—	@@ -1,10 +1,10 @@
2	2
3	3	use faulkner;
4	4
5		~~-set @ctrl = 'ControlBanner';~~
6		~~-set @s = '20101201013000';~~
7		~~-set @e = '20101202013000';~~
8		~~-set @campaign = '20101125JA007';~~
	5	+-- set @ctrl = 'ControlBanner';
	6	+set @s = '20101206000000';
	7	+set @e = '20101219000000';
	8	+-- set @campaign = '20101125JA007';
9	9
10	10	drop table if exists query_bin_vis;
11	11
—	—	@@ -12,19 +12,21 @@
13	13
14	14	select * from
15	15	(select
	16	+DATE_FORMAT(request_time,'%Y-%m-%d') as ts_day,
16	17	utm_source,
17	18	landing_page,
18		~~-utm_campaign,~~
	19	+-- utm_campaign,
19	20	count(*) as views
20	21
21	22	from landing_page
22	23
23	24	where request_time >= @s and request_time < @e
24		~~-and (utm_campaign = @campaign or utm_campaign like @ctrl)~~
25		~~-group by 2,3,4) as lp~~
	25	+and (utm_source = '2010_JA1_Banner3_NL' or utm_source = '2010_JA1_Banner3')
	26	+and (page_url REGEXP 'WMFJA1' and (page_url REGEXP '/NL' or page_url REGEXP 'country_code=NL'))
	27	+-- and (utm_campaign = @campaign or utm_campaign like @ctrl)
	28	+group by 1,2,3) as lp;
26	29
27		~~-where views > 10;~~
	30	+-- where views > 10;
28	31
29	32
30	33
Index: trunk/fundraiser-statistics/standalone-queries/imp_vis.sql
—	—	@@ -1,57 +1,58 @@
2	2
3	3
4		~~-set @campaign = '20101125JA007';~~
5		~~-set @s = '20101201013000';~~
6		~~-set @e = '20101202013000';~~
7		~~-set @ctrl1 = 'ControlBanner';~~
	4	+set @campaign = '20101222JA052';
	5	+set @s = '20101222225500';
	6	+set @e = '20101222235500';
	7	+-- set @ctrl1 = 'ControlBanner';
8	8
9	9	-- set @cntry = 'NL';
10	10
11	11
12		~~-drop table if exists query_bin_imp_vis;~~
	12	+-- drop table if exists query_bin_imp_vis;
13	13
14		~~-create table query_bin_imp_vis as~~
	14	+-- create table query_bin_imp_vis as
15	15
16	16	select
17	17	imp.utm_source,
18		~~-lp.landing_page,~~
	18	+-- lp.landing_page,
19	19	-- imp.hr as hr,
20		~~-sum(impressions) as impressions,~~
21		~~-sum(views) as views,~~
22		~~-sum(views) / sum(impressions) as rate~~
	20	+impressions as impressions,
	21	+views as views,
	22	+views / impressions as rate
23	23
24	24	from
25	25
26	26	(select
27		~~-DATE_FORMAT(on_minute,'%Y-%m-%d %H') as hr,~~
	27	+-- DATE_FORMAT(on_minute,'%Y-%m-%d %H') as hr,
28	28	utm_source,
29	29	sum(counts) as impressions
30	30	from impression
31	31	where on_minute >= @s and on_minute < @e -- and utm_source in (@ctrl1, @ctrl2)
32		~~-and utm_campaign = @campaign or utm_campaign = @ctrl~~
33		~~-and country = @cntry~~
34		~~-group by 2) as imp~~
	32	+-- and utm_campaign = @campaign
	33	+-- and country = @cntry
	34	+and (utm_source = '20101222_JA028A_US' or utm_source = '20101222_JA028C_US')
	35	+group by 1) as imp
35	36
36	37	join
37	38
38	39	(select
39		~~-DATE_FORMAT(request_time,'%Y-%m-%d %H') as hr,~~
	40	+-- DATE_FORMAT(request_time,'%Y-%m-%d %H') as hr,
40	41	utm_source,
41		~~-landing_page,~~
	42	+-- landing_page,
42	43	count(*) as views
43	44	from landing_page
44	45	where request_time >= @s and request_time < @e
45		~~-and utm_campaign = @campaign~~
46		~~-and utm_campaign = @campaign or utm_campaign = @ctrl~~
47		~~-group by 2,3) as lp~~
	46	+-- and utm_campaign = @campaign
	47	+and (utm_source = '20101222_JA028A_US' or utm_source = '20101222_JA028C_US')
	48	+group by 1) as lp
48	49
49	50	on lp.utm_source = imp.utm_source -- and imp.hr = lp.hr
50	51
51		~~-group by 1,2 order by 1;~~
	52	+group by 1 order by 1;
52	53
53	54
54	55
55	56
56	57
	58	+
	59	+
Index: trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py
—	—	@@ -20,8 +20,8 @@
21	21	import datetime
22	22	import MySQLdb
23	23	import pylab
	24	+import HTML
24	25
25		-
26	26	import query_store as qs
27	27	import miner_help as mh
28	28
—	—	@@ -172,6 +172,15 @@
173	173
174	174	return new_values
175	175
	176	+ # workaround for issue with tuple objects in HTML.py
	177	+ # MySQLdb returns unfamiliar tuple elements from its fetchall method
	178	+ # this is probably a version problem since the issue popped up in 2.5 but not 2.6
	179	+ def listify(row):
	180	+ l = []
	181	+ for i in row:
	182	+ l.append(i)
	183	+ return l
	184	+
176	185	def run_query(self, start_time, end_time, query_name, metric_name):
177	186	return
178	187
—	—	@@ -429,7 +438,8 @@
430	439
431	440	metric_lists = mh.AutoVivification()
432	441	time_lists = mh.AutoVivification()
433		-
	442	+ # table_data = [] # store the results in a table for reporting
	443	+
434	444	# Load the SQL File & Format
435	445	filename = './sql/' + query_name + '.sql'
436	446	sql_stmnt = mh.read_sql(filename)
—	—	@@ -448,6 +458,10 @@
449	459
450	460	results = self.cur.fetchall()
451	461
	462	+ # Compile Table Data
	463	+ # cpRow = self.listify(row)
	464	+ # table_data.append(cpRow)
	465	+
452	466	for row in results:
453	467
454	468	key_name = row[key_index]
—	—	@@ -502,6 +516,7 @@
503	517
504	518	self.close_db()
505	519
	520	+ # return [metric_lists, time_norm, table_data]
506	521	return [metric_lists, time_norm]
507	522
508	523
—	—	@@ -632,4 +647,78 @@
633	648	self.close_db()
634	649
635	650	return [campaign, timestamp]
	651	+
	652	+"""
	653	+
	654	+CLASS :: ^ConfidenceReporting^
	655	+
	656	+To be called primarily for reporting
	657	+
	658	+"""
	659	+
	660	+class ConfidenceReporting(FundraiserReporting):
	661	+
	662	+ def __init__(self, query_name, cmpgn1, cmpgn2, item_1, item_2, start_time , end_time, metric):
	663	+ self.query_name = query_name
	664	+ self.cmpgn1 = cmpgn1
	665	+ self.cmpgn2 = cmpgn2
	666	+ self.item1 = item1
	667	+ self.item2 = item2
	668	+ self.start_time = start_time
	669	+ self.end_time = end_time
	670	+ self.metric = metric
	671	+
	672	+
	673	+ def run_query(self):
	674	+
	675	+ self.init_db()
	676	+ query_obj = qs.query_store()
	677	+
	678	+ metric_list_1 = mh.AutoVivification()
	679	+ metric_list_2 = mh.AutoVivification()
	680	+ time_list = mh.AutoVivification()
	681	+
	682	+ # Load the SQL File & Format
	683	+ filename = './sql/' + self.query_name + '.sql'
	684	+ sql_stmnt = mh.read_sql(filename)
	685	+
	686	+ query_name = 'report_bannerLP_metrics' # rename query to work with query store
	687	+ sql_stmnt = query_obj.format_query(self.query_name, sql_stmnt, [self.start_time, self.end_time, self.cmpgn1, self.item1])
	688	+
	689	+ time_index = query_obj.get_time_index(query_name)
	690	+ metric_index = query_obj.get_metric_index(query_name, metric_name)
	691	+
	692	+ # Composes the data for each banner
	693	+ try:
	694	+ err_msg = sql_stmnt
	695	+ self.cur.execute(sql_stmnt)
	696	+
	697	+ results = self.cur.fetchall()
	698	+
	699	+ for row in results:
	700	+
	701	+ key_name = row[key_index]
	702	+
	703	+ try:
	704	+ metric_lists[key_name].append(row[metric_index])
	705	+ time_lists[key_name].append(row[time_index])
	706	+ except:
	707	+ metric_lists[key_name] = list()
	708	+ time_lists[key_name] = list()
	709	+
	710	+ metric_lists[key_name].append(row[metric_index])
	711	+ time_lists[key_name].append(row[time_index])
	712	+
	713	+ except:
	714	+ self.db.rollback()
	715	+ sys.exit("Database Interface Exception:\n" + err_msg)
	716	+
	717	+ self.close_db()
	718	+
	719	+ def gen_plot(self,counts, times, title, xlabel, ylabel, ranges, subplot_index, fname):
	720	+ return
	721	+
	722	+ def run(self):
	723	+ self.run_query()
	724	+ self.gen_plot(counts, times, title, xlabel, ylabel, ranges, subplot_index, fname)
636	725
\ No newline at end of file
Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_confidence_lp.sql
—	—	@@ -0,0 +1,49 @@
	2	+
	3	+select
	4	+
	5	+if(lp.dt_hr < 10, concat(lp.dt_hr, ' 0', lp.dt_min), concat(lp.dt_hr, ' ', lp.dt_min)) as day_hr,
	6	+ecomm.landing_page,
	7	+views as views,
	8	+total_clicks as clicks,
	9	+donations as donations,
	10	+amount as amount,
	11	+donations / total_clicks as conversion_rate,
	12	+donations / views as don_per_view,
	13	+amount / views as amt_per_view
	14	+
	15	+
	16	+from
	17	+
	18	+(select
	19	+DATE_FORMAT(request_time,'%sY-%sm-%sd %sH') as dt_hr,
	20	+FLOOR(MINUTE(request_time) / %s) * %s as dt_min,
	21	+landing_page,
	22	+count(*) as views
	23	+from landing_page
	24	+where request_time >= '%s' and request_time < '%s'
	25	+and (utm_campaign REGEXP '%s')
	26	+and (landing_page REGEXP '%s')
	27	+group by 1,2,3) as lp
	28	+
	29	+join
	30	+
	31	+(select
	32	+DATE_FORMAT(ts,'%sY-%sm-%sd %sH') as dt_hr,
	33	+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
	34	+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page,
	35	+count(*) as total_clicks,
	36	+sum(not isnull(contribution_tracking.contribution_id)) as donations,
	37	+sum(converted_amount) AS amount
	38	+from
	39	+drupal.contribution_tracking LEFT JOIN civicrm.public_reporting
	40	+ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id)
	41	+where ts >= '%s' and ts < '%s'
	42	+and (utm_campaign REGEXP '%s')
	43	+and (SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) REGEXP '%s')
	44	+group by 1,2,3) as ecomm
	45	+
	46	+on ecomm.landing_page = lp.landing_page and lp.dt_hr = ecomm.dt_hr and lp.dt_min = ecomm.dt_min
	47	+
	48	+where views > 1000 and donations > 20
	49	+
	50	+group by 1,2 ;
Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_confidence_banner.sql
—	—	@@ -0,0 +1,75 @@
	2	+
	3	+select
	4	+
	5	+if(imp.dt_hr < 10, concat(imp.dt_hr, ' 0', imp.dt_min), concat(imp.dt_hr, ' ', imp.dt_min)) as day_hr,
	6	+ecomm.banner,
	7	+impressions,
	8	+views as views,
	9	+total_clicks as clicks,
	10	+donations as donations,
	11	+amount as amount,
	12	+views / impressions as click_rate,
	13	+donations / total_clicks as conversion_rate,
	14	+round(donations / impressions,6) as don_per_imp,
	15	+amount / impressions as amt_per_imp,
	16	+donations / views as don_per_view,
	17	+amount / views as amt_per_view
	18	+
	19	+
	20	+from
	21	+
	22	+(select
	23	+imp_i.dt_hr,
	24	+imp_i.dt_min,
	25	+imp_i.utm_source,
	26	+imp_i.impressions as impressions,
	27	+lp_i.views as views
	28	+from
	29	+(select
	30	+DATE_FORMAT(on_minute,'%sY-%sm-%sd %sH') as dt_hr,
	31	+FLOOR(MINUTE(on_minute) / %s) * %s as dt_min,
	32	+utm_source,
	33	+sum(counts) as impressions
	34	+from impression
	35	+where on_minute >= '%s' and on_minute < '%s'
	36	+and utm_source = '%s'
	37	+group by 1,2,3) as imp_i
	38	+
	39	+join
	40	+
	41	+(select
	42	+DATE_FORMAT(request_time,'%sY-%sm-%sd %sH') as dt_hr,
	43	+FLOOR(MINUTE(request_time) / %s) * %s as dt_min,
	44	+utm_source,
	45	+count(*) as views
	46	+from landing_page
	47	+where request_time >= '%s' and request_time < '%s'
	48	+and (utm_campaign REGEXP '%s')
	49	+and (utm_source REGEXP '%s')
	50	+group by 1,2,3) as lp_i
	51	+
	52	+on imp_i.utm_source = lp_i.utm_source and imp_i.dt_hr = lp_i.dt_hr and imp_i.dt_min = lp_i.dt_min
	53	+) as imp
	54	+
	55	+join
	56	+
	57	+(select
	58	+DATE_FORMAT(ts,'%sY-%sm-%sd %sH') as dt_hr,
	59	+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
	60	+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner,
	61	+count(*) as total_clicks,
	62	+sum(not isnull(contribution_tracking.contribution_id)) as donations,
	63	+sum(converted_amount) AS amount
	64	+from
	65	+drupal.contribution_tracking LEFT JOIN civicrm.public_reporting
	66	+ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id)
	67	+where ts >= '%s' and ts < '%s'
	68	+and (utm_campaign REGEXP '%s')
	69	+and (SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) REGEXP '%s')
	70	+group by 1,2,3) as ecomm
	71	+
	72	+on ecomm.banner = imp.utm_source and imp.dt_hr = ecomm.dt_hr and imp.dt_min = ecomm.dt_min
	73	+
	74	+where impressions > 10000
	75	+
	76	+group by 1,2 ;
Index: trunk/fundraiser-statistics/fundraiser-scripts/run_confidence_plot.py
—	—	@@ -0,0 +1,37 @@
	2	+
	3	+"""
	4	+
	5	+run_confidence_plot.py
	6	+
	7	+wikimediafoundation.org
	8	+Ryan Faulkner
	9	+December 22nd, 2010
	10	+
	11	+
	12	+Shell to generate errorbar plots for confidence analysis.
	13	+
	14	+
	15	+"""
	16	+
	17	+import fundraiser_reporting as fr
	18	+
	19	+
	20	+# process sys args
	21	+$script_args = $sql_file . ' ' . $cmpgn1 . ' ' . $cmpgn2 . ' ' . $item1 . ' ' . $item2 . ' ' . $start . ' ' . $end . ' ' . $metric;
	22	+try:
	23	+ type = sys.argv[1]
	24	+ cmpgn1 = sys.argv[2]
	25	+ cmpgn2 = sys.argv[3]
	26	+ item_1 = sys.argv[4]
	27	+ item_2 = sys.argv[5]
	28	+ start_time = sys.argv[6]
	29	+ end_time = sys.argv[7]
	30	+ metric = sys.argv[8]
	31	+
	32	+except IndexError:
	33	+ sys.exit('Invalid command args.\n')
	34	+
	35	+query = sql_filename.split('.')[0]
	36	+r = fr.ConfidenceReporting(query, cmpgn1, cmpgn2, item_1, item_2, start_time , end_time, metric)
	37	+
	38	+r.run()
\ No newline at end of file
Index: trunk/fundraiser-statistics/fundraiser-scripts/query_store.py
—	—	@@ -71,6 +71,21 @@
72	72	start_time = args[0]
73	73	sql_stmnt = sql_stmnt % (start_time)
74	74
	75	+ elif query_name == 'report_confidence_banner':
	76	+ start = args[0]
	77	+ end = args[1]
	78	+ cmpgn = args[2]
	79	+ banner = args[3]
	80	+ sql_stmnt = sql_stmnt % ('%','%','%','%','10','10', start, end, banner, '%','%','%','%','10','10', start, end, cmpgn, banner, \
	81	+ '%','%','%','%','10','10', start, end, cmpgn, banner)
	82	+
	83	+ elif query_name == 'report_confidence_lp':
	84	+ start = args[0]
	85	+ end = args[1]
	86	+ cmpgn = args[2]
	87	+ banner = args[3]
	88	+ sql_stmnt = sql_stmnt % ('%','%','%','%','10','10', start, end, cmpgn, banner, \
	89	+ '%','%','%','%','10','10', start, end, cmpgn, banner)
75	90	else:
76	91	print 'no such table\n'
77	92
Index: trunk/fundraiser-statistics/reporting/landing_page_compare.html
—	—	@@ -0,0 +1,78 @@
	2	+
	3	+<html>
	4	+
	5	+<head>
	6	+
	7	+<title>Banner Compare Test Form</title>
	8	+<!--
	9	+<script type="text/javascript">
	10	+
	11	+ var _gaq = _gaq \|\| [];
	12	+ _gaq.push(['_setAccount', 'UA-20321172-1']);
	13	+ _gaq.push(['_trackPageview']);
	14	+
	15	+ (function() {
	16	+ var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
	17	+ ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
	18	+ var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
	19	+ })();
	20	+
	21	+</script>
	22	+-->
	23	+</head>
	24	+
	25	+<body>
	26	+<h1><u>Custom Banner / LP Test:</u></h1>
	27	+
	28	+<form action="compare_test.php" method="post">
	29	+<input type="hidden" name="sqlFile" value="banner_test_by_lp.sql">
	30	+<br/>
	31	+<br/>
	32	+<label for="utm_campaign_1">
	33	+<pre>UTM CAMPAIGN 1: </pre>
	34	+</label>
	35	+<input type="text" name="utm_campaign_1" />
	36	+<br/>
	37	+<label for="utm_campaign_2">
	38	+<pre>UTM CAMPAIGN 2: </pre>
	39	+</label>
	40	+<input type="text" name="utm_campaign_2" />
	41	+<br/>
	42	+<label for="banner_1">
	43	+<pre>LANDING PAGE 1: </pre>
	44	+</label>
	45	+<input type="text" name="item_1" />
	46	+<br/>
	47	+<label for="banner_2">
	48	+<pre>LANDING PAGE 2: </pre>
	49	+</label>
	50	+<input type="text" name="item_2" />
	51	+<br/>
	52	+<label for="start_time">
	53	+<pre>START TIMESTAMP: </pre>
	54	+</label>
	55	+ <input type="text" name="start_time" />
	56	+<br/>
	57	+<label for="end_time">
	58	+<pre>END TIMESTAMP: </pre>
	59	+</label>
	60	+<input type="text" name="end_time" />
	61	+<br/>
	62	+<label for="end_time">
	63	+<br/>
	64	+<pre>METRIC TO COMPARE: </pre>
	65	+</label>
	66	+<input type="radio" name="metric" value="don_per_view" /> donations / view<br />
	67	+<input type="radio" name="metric" value="amt_per_view" /> amount / view<br />
	68	+<br/>
	69	+<br/>
	70	+<label for="pwd">
	71	+<pre>Key: </pre>
	72	+</label>
	73	+<input type="password" name="pwd" size="20">
	74	+<br/>
	75	+<input type="submit" value="Run Banner Comapre Test">
	76	+</form>
	77	+
	78	+</body>
	79	+</html>
\ No newline at end of file
Index: trunk/fundraiser-statistics/reporting/banner_test_by_lp.sql
—	—	@@ -0,0 +1,49 @@
	2	+
	3	+select
	4	+
	5	+if(lp.dt_hr < 10, concat(lp.dt_hr, ' 0', lp.dt_min), concat(lp.dt_hr, ' ', lp.dt_min)) as day_hr,
	6	+ecomm.landing_page,
	7	+views as views,
	8	+total_clicks as clicks,
	9	+donations as donations,
	10	+amount as amount,
	11	+donations / total_clicks as conversion_rate,
	12	+donations / views as don_per_view,
	13	+amount / views as amt_per_view
	14	+
	15	+
	16	+from
	17	+
	18	+(select
	19	+DATE_FORMAT(request_time,'%sY-%sm-%sd %sH') as dt_hr,
	20	+FLOOR(MINUTE(request_time) / %s) * %s as dt_min,
	21	+landing_page,
	22	+count(*) as views
	23	+from landing_page
	24	+where request_time >= '%s' and request_time < '%s'
	25	+and (utm_campaign REGEXP '%s')
	26	+and (landing_page REGEXP '%s')
	27	+group by 1,2,3) as lp
	28	+
	29	+join
	30	+
	31	+(select
	32	+DATE_FORMAT(ts,'%sY-%sm-%sd %sH') as dt_hr,
	33	+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
	34	+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page,
	35	+count(*) as total_clicks,
	36	+sum(not isnull(contribution_tracking.contribution_id)) as donations,
	37	+sum(converted_amount) AS amount
	38	+from
	39	+drupal.contribution_tracking LEFT JOIN civicrm.public_reporting
	40	+ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id)
	41	+where ts >= '%s' and ts < '%s'
	42	+and (utm_campaign REGEXP '%s')
	43	+and (SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) REGEXP '%s')
	44	+group by 1,2,3) as ecomm
	45	+
	46	+on ecomm.landing_page = lp.landing_page and lp.dt_hr = ecomm.dt_hr and lp.dt_min = ecomm.dt_min
	47	+
	48	+where views > 1000 and donations > 20
	49	+
	50	+group by 1,2 ;
Index: trunk/fundraiser-statistics/reporting/compare_test.php
—	—	@@ -48,7 +48,7 @@
49	49	}
50	50
51	51	// Format the query based on the type
52		~~-if ($sql_file == "banner_test_by_banner.sql") {~~
	52	+if ($sql_file == "banner_test_by_banner.sql" \|\| $sql_file == "banner_test_by_lp.sql") {
53	53	$query1 = sprintf($query, '%','%','%','%','10','10', $start, $end, $item1, '%','%','%','%','10','10', $start, $end, $cmpgn1, $item1, '%','%','%','%','10','10', $start, $end, $cmpgn1, $item1);
54	54	$query2 = sprintf($query, '%','%','%','%','10','10', $start, $end, $item2, '%','%','%','%','10','10', $start, $end, $cmpgn2, $item2, '%','%','%','%','10','10', $start, $end, $cmpgn2, $item2);
55	55	} elseif ($sql_file == "landing_compare.sql") {
Index: trunk/fundraiser-statistics/reporting/fundraiser_analytics.html
—	—	@@ -6,7 +6,7 @@
7	7
8	8	<head>
9	9
10		~~-<title>Wikimedia Fundraiser Analystics</title>~~
	10	+<title>Wikimedia Fundraiser Analytics</title>
11	11	<!--
12	12	<script type="text/javascript">
13	13
—	—	@@ -54,6 +54,7 @@
55	55	<a href="http://fundraising.wikimedia.org/stats/ecomm_test_by_hour.html">By Campaign and Time - Donation Data - by hour</a><br>
56	56	<br>
57	57	<a href="http://fundraising.wikimedia.org/stats/banner_compare.html">Banner Confidence Test</a><br>
	58	+<a href="http://fundraising.wikimedia.org/stats/landing_page_compare.html">Landing Page Confidence Test</a><br>
58	59
59	60	<br>
60	61

Status & tagging log

20:08, 24 December 2010 Reedy (talk | contribs) changed the status of r78960 [removed: new added: deferred]