r78607 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r78606‎ | r78607 | r78608 >
Date:21:29, 19 December 2010
Author:rfaulk
Status:deferred
Tags:
Comment:
Updated reporting html, sql, and php to allow for testing of landing pages only both aggregate and hour by hour.
Modified paths:
  • /trunk/fundraiser-statistics/reporting/landing_page_test.html (added) (history)
  • /trunk/fundraiser-statistics/reporting/landing_page_test.sql (added) (history)
  • /trunk/fundraiser-statistics/reporting/landing_page_test_by_hour.html (added) (history)
  • /trunk/fundraiser-statistics/reporting/landing_page_test_by_hour.sql (added) (history)

Diff [purge]

Index: trunk/fundraiser-statistics/reporting/landing_page_test_by_hour.html
——@@ -0,0 +1,56 @@
 2+
 3+<html>
 4+
 5+<head>
 6+
 7+<title>Fundraiser Test Form - Landing Page Data</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 Landing Page Test by Hour:</u></h1>
 27+
 28+<form action="banner_test_proc.php" method="post">
 29+<input type="hidden" name="sqlFile" value="landing_page_test_by_hour.sql">
 30+<br/>
 31+<br/>
 32+<label for="utm_campaign">
 33+<pre>UTM CAMPAIGN: </pre>
 34+</label>
 35+<input type="text" name="utm_campaign" />
 36+<br/>
 37+<label for="start_time">
 38+<pre>START TIMESTAMP: </pre>
 39+</label>
 40+ <input type="text" name="start_time" />
 41+<br/>
 42+<label for="end_time">
 43+<pre>END TIMESTAMP: </pre>
 44+</label>
 45+<input type="text" name="end_time" />
 46+<br/>
 47+<br/>
 48+<label for="pwd">
 49+<pre>Key: </pre>
 50+</label>
 51+<input type="password" name="pwd" size="20">
 52+<br/>
 53+<input type="submit" value="Run Banner Test">
 54+</form>
 55+
 56+</body>
 57+</html>
\ No newline at end of file
Index: trunk/fundraiser-statistics/reporting/landing_page_test.sql
——@@ -0,0 +1,65 @@
 2+
 3+
 4+select
 5+
 6+lp.utm_campaign,
 7+lp.landing_page,
 8+views as views,
 9+total_clicks as clicks,
 10+donations as donations,
 11+amount as amount,
 12+donations / total_clicks as conversion_rate,
 13+donations / views as don_per_view,
 14+amount / views as amt_per_view,
 15+max_amt,
 16+pp_don,
 17+cc_don,
 18+pp_don / pp_clicks as paypal_click_thru,
 19+cc_don / cc_clicks as credit_card_click_thru
 20+
 21+
 22+from
 23+
 24+(select
 25+landing_page,
 26+utm_campaign,
 27+count(*) as views
 28+
 29+from landing_page
 30+
 31+where request_time >= '%s' and request_time < '%s'
 32+and (utm_campaign REGEXP '%s')
 33+group by 1,2) as lp
 34+
 35+left join
 36+
 37+(select
 38+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page,
 39+utm_campaign,
 40+count(*) as total_clicks,
 41+sum(not isnull(contribution_tracking.contribution_id)) as donations,
 42+sum(converted_amount) AS amount,
 43+max(converted_amount) AS max_amt,
 44+sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks,
 45+sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as cc_don,
 46+sum(if(right(utm_source,2)='pp',1,0)) as pp_clicks,
 47+sum(if(right(utm_source,2)='pp' and contribution_tracking.contribution_id,1,0)) as pp_don
 48+
 49+
 50+from
 51+drupal.contribution_tracking LEFT JOIN civicrm.public_reporting
 52+ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id)
 53+where ts >= '%s' and ts < '%s'
 54+and (utm_campaign REGEXP '%s')
 55+group by 1,2) as ecomm
 56+
 57+on ecomm.landing_page = lp.landing_page and ecomm.utm_campaign = lp.utm_campaign
 58+
 59+where views > 100
 60+
 61+group by 1,2,3 order by 8 desc;
 62+
 63+
 64+
 65+
 66+
Index: trunk/fundraiser-statistics/reporting/landing_page_test.html
——@@ -0,0 +1,56 @@
 2+
 3+<html>
 4+
 5+<head>
 6+
 7+<title>Fundraiser Test Form - Landing Page Data</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 Landing Page Test:</u></h1>
 27+
 28+<form action="banner_test_proc.php" method="post">
 29+<input type="hidden" name="sqlFile" value="landing_page_test.sql">
 30+<br/>
 31+<br/>
 32+<label for="utm_campaign">
 33+<pre>UTM CAMPAIGN: </pre>
 34+</label>
 35+<input type="text" name="utm_campaign" />
 36+<br/>
 37+<label for="start_time">
 38+<pre>START TIMESTAMP: </pre>
 39+</label>
 40+ <input type="text" name="start_time" />
 41+<br/>
 42+<label for="end_time">
 43+<pre>END TIMESTAMP: </pre>
 44+</label>
 45+<input type="text" name="end_time" />
 46+<br/>
 47+<br/>
 48+<label for="pwd">
 49+<pre>Key: </pre>
 50+</label>
 51+<input type="password" name="pwd" size="20">
 52+<br/>
 53+<input type="submit" value="Run Banner Test">
 54+</form>
 55+
 56+</body>
 57+</html>
\ No newline at end of file
Index: trunk/fundraiser-statistics/reporting/landing_page_test_by_hour.sql
——@@ -0,0 +1,68 @@
 2+
 3+select
 4+
 5+lp.hr,
 6+lp.utm_campaign,
 7+lp.landing_page,
 8+views as views,
 9+total_clicks as clicks,
 10+donations as donations,
 11+amount as amount,
 12+donations / total_clicks as conversion_rate,
 13+donations / views as don_per_view,
 14+amount / views as amt_per_view,
 15+max_amt,
 16+pp_don,
 17+cc_don,
 18+pp_don / pp_clicks as paypal_click_thru,
 19+cc_don / cc_clicks as credit_card_click_thru
 20+
 21+
 22+from
 23+
 24+(select
 25+DATE_FORMAT(request_time,'%sY-%sm-%sd %sH') as hr,
 26+landing_page,
 27+utm_campaign,
 28+count(*) as views
 29+
 30+from landing_page
 31+
 32+where request_time >= '%s' and request_time < '%s'
 33+and (utm_campaign REGEXP '%s')
 34+group by 1,2,3) as lp
 35+
 36+left join
 37+
 38+(select
 39+DATE_FORMAT(ts,'%sY-%sm-%sd %sH') as hr,
 40+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page,
 41+utm_campaign,
 42+count(*) as total_clicks,
 43+sum(not isnull(contribution_tracking.contribution_id)) as donations,
 44+sum(converted_amount) AS amount,
 45+max(converted_amount) AS max_amt,
 46+sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks,
 47+sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as cc_don,
 48+sum(if(right(utm_source,2)='pp',1,0)) as pp_clicks,
 49+sum(if(right(utm_source,2)='pp' and contribution_tracking.contribution_id,1,0)) as pp_don
 50+
 51+
 52+from
 53+drupal.contribution_tracking LEFT JOIN civicrm.public_reporting
 54+ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id)
 55+where ts >= '%s' and ts < '%s'
 56+and (utm_campaign REGEXP '%s')
 57+group by 1,2,3) as ecomm
 58+
 59+on ecomm.landing_page = lp.landing_page and ecomm.utm_campaign = lp.utm_campaign and lp.hr = ecomm.hr
 60+
 61+where views > 10
 62+
 63+group by 1,2,3 order by 1,8 desc;
 64+
 65+
 66+
 67+
 68+
 69+

Status & tagging log