| Index: trunk/fundraiser-statistics/reporting/ecomm_test_by_interval.sql |
| — | — | @@ -0,0 +1,29 @@ |
| | 2 | + |
| | 3 | +select |
| | 4 | +if( FLOOR(MINUTE(ts) / %s) * %s) < 10, concat(DATE_FORMAT(ts,'%sY-%sm-%sd %sH'), ' 0', FLOOR(MINUTE(ts) / %s) * %s), concat(DATE_FORMAT(ts,'%sY-%sm-%sd %sH'), ' ', FLOOR(MINUTE(ts) / %s) * %s)) as day_hr, |
| | 5 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| | 6 | +utm_campaign, |
| | 7 | +count(*) as total_clicks, |
| | 8 | +sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| | 9 | +sum(converted_amount) AS amount, |
| | 10 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50, |
| | 11 | +-- sum(if(converted_amount > 100, 100, converted_amount)) AS modified_amount, -- truncates donations over 100 |
| | 12 | +max(converted_amount) AS max_amt, |
| | 13 | +sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks, |
| | 14 | +sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as cc_don, |
| | 15 | +sum(if(right(utm_source,2)='pp',1,0)) as pp_clicks, |
| | 16 | +sum(if(right(utm_source,2)='pp' and contribution_tracking.contribution_id,1,0)) as pp_don |
| | 17 | + |
| | 18 | + |
| | 19 | +from |
| | 20 | +drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| | 21 | +ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| | 22 | +where ts >= '%s' and ts < '%s' |
| | 23 | +and (utm_campaign REGEXP '%s') |
| | 24 | +group by 1,2,3 |
| | 25 | + |
| | 26 | + |
| | 27 | + |
| | 28 | + |
| | 29 | + |
| | 30 | + |
| Index: trunk/fundraiser-statistics/reporting/landing_page_test_by_interval.html |
| — | — | @@ -0,0 +1,66 @@ |
| | 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 Interval:</u></h1> |
| | 27 | + |
| | 28 | +<p><b> |
| | 29 | +Intervals always begin on the hour. |
| | 30 | +The first and / or last interval may be truncated by the start or end times if the period between start and end is not evenly divisible by the interval and does not begin on the hour. |
| | 31 | +</b></p> |
| | 32 | + |
| | 33 | +<form action="banner_test_proc.php" method="post"> |
| | 34 | +<input type="hidden" name="sqlFile" value="landing_page_test_by_interval.sql"> |
| | 35 | +<br/> |
| | 36 | +<br/> |
| | 37 | +<label for="utm_campaign"> |
| | 38 | +<pre>UTM CAMPAIGN: </pre> |
| | 39 | +</label> |
| | 40 | +<input type="text" name="utm_campaign" /> |
| | 41 | +<br/> |
| | 42 | +<label for="start_time"> |
| | 43 | +<pre>START TIMESTAMP: </pre> |
| | 44 | +</label> |
| | 45 | + <input type="text" name="start_time" /> |
| | 46 | +<br/> |
| | 47 | +<label for="end_time"> |
| | 48 | +<pre>END TIMESTAMP: </pre> |
| | 49 | +</label> |
| | 50 | +<input type="text" name="end_time" /> |
| | 51 | +<br/> |
| | 52 | +<label for="interval"> |
| | 53 | +<pre>INTERVAL (MINUTES): </pre> |
| | 54 | +</label> |
| | 55 | +<input type="text" name="interval" /> |
| | 56 | +<br/> |
| | 57 | +<br/> |
| | 58 | +<label for="pwd"> |
| | 59 | +<pre>Key: </pre> |
| | 60 | +</label> |
| | 61 | +<input type="password" name="pwd" size="20"> |
| | 62 | +<br/> |
| | 63 | +<input type="submit" value="Run Banner Test"> |
| | 64 | +</form> |
| | 65 | + |
| | 66 | +</body> |
| | 67 | +</html> |
| \ No newline at end of file |
| Index: trunk/fundraiser-statistics/reporting/fundraiser_analytics.html |
| — | — | @@ -45,14 +45,18 @@ |
| 46 | 46 | |
| 47 | 47 | <a href="http://fundraising.wikimedia.org/stats/banner_test.html">By Campaign and Time - Banner / LPs</a><br> |
| 48 | 48 | <a href="http://fundraising.wikimedia.org/stats/banner_test_by_hour.html">By Campaign and Time - Banner / LPs - by hour</a><br> |
| | 49 | +<a href="http://fundraising.wikimedia.org/stats/banner_test_by_interval.html">By Campaign and Time - Banner / LPs - interval</a><br> |
| 49 | 50 | <br> |
| 50 | 51 | <a href="http://fundraising.wikimedia.org/stats/banner_test_banners.html">By Campaign and Time - Banners</a><br> |
| | 52 | +<a href="http://fundraising.wikimedia.org/stats/banner_test_banners_by_interval.html">By Campaign and Time - Banners - interval</a><br> |
| 51 | 53 | <br> |
| 52 | 54 | <a href="http://fundraising.wikimedia.org/stats/landing_page_test.html">By Campaign and Time - LPs</a><br> |
| 53 | 55 | <a href="http://fundraising.wikimedia.org/stats/landing_page_test_by_hour.html">By Campaign and Time - LPs - by hour</a><br> |
| | 56 | +<a href="http://fundraising.wikimedia.org/stats/landing_page_test_by_interval.html">By Campaign and Time - LPs - interval</a><br> |
| 54 | 57 | <br> |
| 55 | 58 | <a href="http://fundraising.wikimedia.org/stats/ecomm_test.html">By Campaign and Time - Donation Data</a><br> |
| 56 | 59 | <a href="http://fundraising.wikimedia.org/stats/ecomm_test_by_hour.html">By Campaign and Time - Donation Data - by hour</a><br> |
| | 60 | +<a href="http://fundraising.wikimedia.org/stats/ecomm_test_by_interval.html">By Campaign and Time - Donation Data - interval</a><br> |
| 57 | 61 | <br> |
| 58 | 62 | <a href="http://fundraising.wikimedia.org/stats/banner_compare.html">Banner Confidence Test</a><br> |
| 59 | 63 | <a href="http://fundraising.wikimedia.org/stats/landing_page_compare.html">Landing Page Confidence Test</a><br> |
| Index: trunk/fundraiser-statistics/reporting/banner_test_proc.php |
| — | — | @@ -41,6 +41,8 @@ |
| 42 | 42 | $query=$query.$query_parts[$counter]; |
| 43 | 43 | } |
| 44 | 44 | |
| | 45 | + |
| | 46 | +// FORMAT THE SQL QUERY BASED ON THE FILE WHICH INDICATES THE REQUEST |
| 45 | 47 | if ($sql_file == "banner_test.sql" || $sql_file == "banner_test_banners.sql") { |
| 46 | 48 | $query = sprintf($query, $start, $end, $start, $end, $cmpgn, $start, $end, $cmpgn, $start, $end, $cmpgn, "%"); |
| 47 | 49 | } elseif ($sql_file == "landing_page_test.sql") { |
| — | — | @@ -53,6 +55,19 @@ |
| 54 | 56 | $query = sprintf($query, "%", "%", "%", "%", $start, $end, $cmpgn); |
| 55 | 57 | } elseif ($sql_file == "banner_test_by_hour.sql") { |
| 56 | 58 | $query = sprintf($query, "%", "%", "%", "%", $start, $end, "%", "%", "%", "%", $start, $end, $cmpgn, "%", "%", "%", "%", $start, $end, $cmpgn, "%", "%", "%", "%", $start, $end, $cmpgn, "%"); |
| | 59 | +} elseif ($sql_file == "landing_page_test_by_interval.sql") { |
| | 60 | + $interval=$_POST["interval"]; |
| | 61 | + $query = sprintf($query, "%", "%", "%", "%", $interval, $interval, $start, $end, $cmpgn, "%", "%", "%", "%", $interval, $interval, $start, $end, $cmpgn); |
| | 62 | +} elseif ($sql_file == "ecomm_test_by_interval.sql") { |
| | 63 | + $interval=$_POST["interval"]; |
| | 64 | + $query = sprintf($query, $interval, $interval, "%", "%", "%", "%", $interval, $interval, "%", "%", "%", "%", $interval, $interval, $start, $end, $cmpgn); |
| | 65 | +} elseif ($sql_file == "banner_test_banners_by_interval.sql") { |
| | 66 | + $interval=$_POST["interval"]; |
| | 67 | + $query = sprintf($query, "%", "%", "%", "%", $interval, $interval, $start, $end, "%", "%", "%", "%", $interval, $interval, $start, $end, $cmpgn, "%", "%", "%", "%", $interval, $interval, $start, $end, $cmpgn); |
| | 68 | +} elseif ($sql_file == "banner_test_by_interval.sql") { |
| | 69 | + $interval=$_POST["interval"]; |
| | 70 | + $query = sprintf($query, "%", "%", "%", "%", $interval, $interval, $start, $end, "%", "%", "%", "%", $interval, $interval, $start, $end, $cmpgn, "%", "%", "%", "%", |
| | 71 | + $interval, $interval, $start, $end, $cmpgn, "%", "%", "%", "%", $interval, $interval, $start, $end, $cmpgn); |
| 57 | 72 | } |
| 58 | 73 | |
| 59 | 74 | // Perform Query |
| Index: trunk/fundraiser-statistics/reporting/banner_test_by_interval.sql |
| — | — | @@ -0,0 +1,104 @@ |
| | 2 | + |
| | 3 | + |
| | 4 | +select |
| | 5 | + |
| | 6 | +if(imp.dt_hr < 10, concat(lp.dt_hr, ' 0', lp.dt_min), concat(lp.dt_hr, ' ', lp.dt_min)) as day_hr, |
| | 7 | +lp.utm_campaign, |
| | 8 | +lp.utm_source, |
| | 9 | +lp.landing_page, |
| | 10 | +impressions as total_impressions, |
| | 11 | +floor(impressions * views / views_banner) as impressions, |
| | 12 | +views as views, |
| | 13 | +total_clicks as clicks, |
| | 14 | +donations as donations, |
| | 15 | +amount as amount, |
| | 16 | +amount50 as amount50, |
| | 17 | +views / floor(impressions * views / views_banner) as click_rate_lp, |
| | 18 | +donations / total_clicks as conversion_rate, |
| | 19 | +round(donations / floor(impressions * views / views_banner) ,6) as don_per_imp, |
| | 20 | +round(amount / floor(impressions * views / views_banner) ,6) as amt_per_imp, |
| | 21 | +round(amount50 / floor(impressions * views / views_banner) ,6) as amt50_per_imp, |
| | 22 | +donations / views as don_per_view, |
| | 23 | +amount / views as amt_per_view, |
| | 24 | +amount50 / views as amt50_per_view |
| | 25 | + |
| | 26 | +from |
| | 27 | + |
| | 28 | +(select |
| | 29 | +imp_i.dt_hr, |
| | 30 | +imp_i.dt_min, |
| | 31 | +imp_i.utm_source, |
| | 32 | +imp_i.impressions as impressions, |
| | 33 | +lp_i.views as views_banner |
| | 34 | +from |
| | 35 | +(select |
| | 36 | +DATE_FORMAT(on_minute,'%sY-%sm-%sd %sH') as dt_hr, |
| | 37 | +FLOOR(MINUTE(on_minute) / %s) * %s as dt_min, |
| | 38 | +utm_source, |
| | 39 | +sum(counts) as impressions |
| | 40 | +from impression |
| | 41 | +where on_minute >= '%s' and on_minute < '%s' |
| | 42 | +group by 1,2,3) as imp_i |
| | 43 | + |
| | 44 | +join |
| | 45 | + |
| | 46 | +(select |
| | 47 | +DATE_FORMAT(request_time,'%sY-%sm-%sd %sH') as dt_hr, |
| | 48 | +FLOOR(MINUTE(request_time) / %s) * %s as dt_min, |
| | 49 | +utm_source, |
| | 50 | +count(*) as views |
| | 51 | +from landing_page |
| | 52 | +where request_time >= '%s' and request_time < '%s' |
| | 53 | +and (utm_campaign REGEXP '%s') |
| | 54 | +group by 1,2,3) as lp_i |
| | 55 | + |
| | 56 | +on imp_i.utm_source = lp_i.utm_source and lp_i.dt_hr = imp_i.dt_hr and lp_i.dt_min = imp_i.dt_min |
| | 57 | +) as imp |
| | 58 | + |
| | 59 | +join |
| | 60 | + |
| | 61 | +(select |
| | 62 | +DATE_FORMAT(request_time,'%sY-%sm-%sd %sH') as dt_hr, |
| | 63 | +FLOOR(MINUTE(request_time) / %s) * %s as dt_min, |
| | 64 | +utm_source, |
| | 65 | +landing_page, |
| | 66 | +utm_campaign, |
| | 67 | +count(*) as views |
| | 68 | + |
| | 69 | +from landing_page |
| | 70 | + |
| | 71 | +where request_time >= '%s' and request_time < '%s' |
| | 72 | +and (utm_campaign REGEXP '%s') |
| | 73 | +group by 1,2,3,4,5) as lp |
| | 74 | + |
| | 75 | +on lp.utm_source = imp.utm_source and lp.dt_hr = imp.dt_hr and lp.dt_min = imp.dt_min |
| | 76 | + |
| | 77 | +left join |
| | 78 | + |
| | 79 | +(select |
| | 80 | +DATE_FORMAT(ts,'%sY-%sm-%sd %sH') as dt_hr, |
| | 81 | +FLOOR(MINUTE(ts) / %s) * %s as dt_min, |
| | 82 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner, |
| | 83 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| | 84 | +utm_campaign, |
| | 85 | +count(*) as total_clicks, |
| | 86 | +sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| | 87 | +sum(converted_amount) AS amount, |
| | 88 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50 |
| | 89 | +from |
| | 90 | +drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| | 91 | +ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| | 92 | +where ts >= '%s' and ts < '%s' |
| | 93 | +and (utm_campaign REGEXP '%s') |
| | 94 | +group by 1,2,3,4,5) as ecomm |
| | 95 | + |
| | 96 | +on ecomm.banner = lp.utm_source and ecomm.landing_page = lp.landing_page and ecomm.utm_campaign = lp.utm_campaign and lp.dt_hr = ecomm.dt_hr and lp.dt_min = ecomm.dt_min |
| | 97 | + |
| | 98 | +-- where floor(impressions * views / views_banner) > 50000 |
| | 99 | + |
| | 100 | +order by 1,2,3 desc; |
| | 101 | + |
| | 102 | + |
| | 103 | + |
| | 104 | + |
| | 105 | + |
| Index: trunk/fundraiser-statistics/reporting/ecomm_test_by_interval.html |
| — | — | @@ -0,0 +1,66 @@ |
| | 2 | + |
| | 3 | +<html> |
| | 4 | + |
| | 5 | +<head> |
| | 6 | + |
| | 7 | +<title>Fundraiser Test Form - Donation 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 Donation Data Test by Interval:</u></h1> |
| | 27 | + |
| | 28 | +<p><b> |
| | 29 | +Intervals always begin on the hour. |
| | 30 | +The first and / or last interval may be truncated by the start or end times if the period between start and end is not evenly divisible by the interval and does not begin on the hour. |
| | 31 | +</b></p> |
| | 32 | + |
| | 33 | +<form action="banner_test_proc.php" method="post"> |
| | 34 | +<input type="hidden" name="sqlFile" value="ecomm_test_by_interval.sql"> |
| | 35 | +<br/> |
| | 36 | +<br/> |
| | 37 | +<label for="utm_campaign"> |
| | 38 | +<pre>UTM CAMPAIGN: </pre> |
| | 39 | +</label> |
| | 40 | +<input type="text" name="utm_campaign" /> |
| | 41 | +<br/> |
| | 42 | +<label for="start_time"> |
| | 43 | +<pre>START TIMESTAMP: </pre> |
| | 44 | +</label> |
| | 45 | + <input type="text" name="start_time" /> |
| | 46 | +<br/> |
| | 47 | +<label for="end_time"> |
| | 48 | +<pre>END TIMESTAMP: </pre> |
| | 49 | +</label> |
| | 50 | +<input type="text" name="end_time" /> |
| | 51 | +<br/> |
| | 52 | +<label for="interval"> |
| | 53 | +<pre>INTERVAL (MINUTES): </pre> |
| | 54 | +</label> |
| | 55 | +<input type="text" name="interval" /> |
| | 56 | +<br/> |
| | 57 | +<br/> |
| | 58 | +<label for="pwd"> |
| | 59 | +<pre>Key: </pre> |
| | 60 | +</label> |
| | 61 | +<input type="password" name="pwd" size="20"> |
| | 62 | +<br/> |
| | 63 | +<input type="submit" value="Run Banner Test"> |
| | 64 | +</form> |
| | 65 | + |
| | 66 | +</body> |
| | 67 | +</html> |
| \ No newline at end of file |
| Index: trunk/fundraiser-statistics/reporting/landing_page_test_by_interval.sql |
| — | — | @@ -0,0 +1,72 @@ |
| | 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 | +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 completion_rate, |
| | 13 | +donations / views as don_per_view, |
| | 14 | +amount / views as amt_per_view, |
| | 15 | +amount50 / views as amt50_per_view, |
| | 16 | +max_amt, |
| | 17 | +pp_don, |
| | 18 | +cc_don, |
| | 19 | +pp_don / pp_clicks as paypal_click_thru, |
| | 20 | +cc_don / cc_clicks as credit_card_click_thru |
| | 21 | + |
| | 22 | + |
| | 23 | +from |
| | 24 | + |
| | 25 | +(select |
| | 26 | +DATE_FORMAT(request_time,'%sY-%sm-%sd %sH') as dt_hr, |
| | 27 | +FLOOR(MINUTE(request_time) / %s) * %s as dt_min, |
| | 28 | +landing_page, |
| | 29 | +utm_campaign, |
| | 30 | +count(*) as views |
| | 31 | + |
| | 32 | +from landing_page |
| | 33 | + |
| | 34 | +where request_time >= '%s' and request_time < '%s' |
| | 35 | +and (utm_campaign REGEXP '%s') |
| | 36 | +group by 1,2,3) as lp |
| | 37 | + |
| | 38 | +left join |
| | 39 | + |
| | 40 | +(select |
| | 41 | +DATE_FORMAT(ts,'%sY-%sm-%sd %sH') as dt_hr, |
| | 42 | +FLOOR(MINUTE(ts) / %s) * %s as dt_min, |
| | 43 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| | 44 | +utm_campaign, |
| | 45 | +count(*) as total_clicks, |
| | 46 | +sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| | 47 | +sum(converted_amount) AS amount, |
| | 48 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50, |
| | 49 | +max(converted_amount) AS max_amt, |
| | 50 | +sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks, |
| | 51 | +sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as cc_don, |
| | 52 | +sum(if(right(utm_source,2)='pp',1,0)) as pp_clicks, |
| | 53 | +sum(if(right(utm_source,2)='pp' and contribution_tracking.contribution_id,1,0)) as pp_don |
| | 54 | + |
| | 55 | + |
| | 56 | +from |
| | 57 | +drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| | 58 | +ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| | 59 | +where ts >= '%s' and ts < '%s' |
| | 60 | +and (utm_campaign REGEXP '%s') |
| | 61 | +group by 1,2,3) as ecomm |
| | 62 | + |
| | 63 | +on ecomm.landing_page = lp.landing_page and ecomm.utm_campaign = lp.utm_campaign and lp.dt_hr = ecomm.dt_hr and lp.dt_min = ecomm.dt_min |
| | 64 | + |
| | 65 | +-- where views > 100 |
| | 66 | + |
| | 67 | +group by 1,2,3 order by 1,8 desc; |
| | 68 | + |
| | 69 | + |
| | 70 | + |
| | 71 | + |
| | 72 | + |
| | 73 | + |
| Index: trunk/fundraiser-statistics/reporting/banner_test_by_interval.html |
| — | — | @@ -0,0 +1,66 @@ |
| | 2 | + |
| | 3 | +<html> |
| | 4 | + |
| | 5 | +<head> |
| | 6 | + |
| | 7 | +<title>Banner 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 By Interval:</u></h1> |
| | 27 | + |
| | 28 | +<p><b> |
| | 29 | +Intervals always begin on the hour. |
| | 30 | +The first and / or last interval may be truncated by the start or end times if the period between start and end is not evenly divisible by the interval and does not begin on the hour. |
| | 31 | +</b></p> |
| | 32 | + |
| | 33 | +<form action="banner_test_proc.php" method="post"> |
| | 34 | +<input type="hidden" name="sqlFile" value="banner_test_by_interval.sql"> |
| | 35 | +<br/> |
| | 36 | +<br/> |
| | 37 | +<label for="utm_campaign"> |
| | 38 | +<pre>UTM CAMPAIGN: </pre> |
| | 39 | +</label> |
| | 40 | +<input type="text" name="utm_campaign" /> |
| | 41 | +<br/> |
| | 42 | +<label for="start_time"> |
| | 43 | +<pre>START TIMESTAMP: </pre> |
| | 44 | +</label> |
| | 45 | + <input type="text" name="start_time" /> |
| | 46 | +<br/> |
| | 47 | +<label for="end_time"> |
| | 48 | +<pre>END TIMESTAMP: </pre> |
| | 49 | +</label> |
| | 50 | +<input type="text" name="end_time" /> |
| | 51 | +<br/> |
| | 52 | +<label for="interval"> |
| | 53 | +<pre>INTERVAL (MINUTES): </pre> |
| | 54 | +</label> |
| | 55 | +<input type="text" name="interval" /> |
| | 56 | +<br/> |
| | 57 | +<br/> |
| | 58 | +<label for="pwd"> |
| | 59 | +<pre>Key: </pre> |
| | 60 | +</label> |
| | 61 | +<input type="password" name="pwd" size="20"> |
| | 62 | +<br/> |
| | 63 | +<input type="submit" value="Run Banner Test"> |
| | 64 | +</form> |
| | 65 | + |
| | 66 | +</body> |
| | 67 | +</html> |
| \ No newline at end of file |
| Index: trunk/fundraiser-statistics/reporting/banner_test_banners_by_interval.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 | +amount50 / impressions as amt50_per_imp, |
| | 17 | +donations / views as don_per_view, |
| | 18 | +amount / views as amt_per_view, |
| | 19 | +amount50 / views as amt50_per_view |
| | 20 | + |
| | 21 | + |
| | 22 | +from |
| | 23 | + |
| | 24 | +(select |
| | 25 | +imp_i.dt_hr, |
| | 26 | +imp_i.dt_min, |
| | 27 | +imp_i.utm_source, |
| | 28 | +imp_i.impressions as impressions, |
| | 29 | +lp_i.views as views |
| | 30 | +from |
| | 31 | +(select |
| | 32 | +DATE_FORMAT(on_minute,'%sY-%sm-%sd %sH') as dt_hr, |
| | 33 | +FLOOR(MINUTE(on_minute) / %s) * %s as dt_min, |
| | 34 | +utm_source, |
| | 35 | +sum(counts) as impressions |
| | 36 | +from impression |
| | 37 | +where on_minute >= '%s' and on_minute < '%s' |
| | 38 | +group by 1,2,3) as imp_i |
| | 39 | + |
| | 40 | +join |
| | 41 | + |
| | 42 | +(select |
| | 43 | +DATE_FORMAT(request_time,'%sY-%sm-%sd %sH') as dt_hr, |
| | 44 | +FLOOR(MINUTE(request_time) / %s) * %s as dt_min, |
| | 45 | +utm_source, |
| | 46 | +count(*) as views |
| | 47 | +from landing_page |
| | 48 | +where request_time >= '%s' and request_time < '%s' |
| | 49 | +and (utm_campaign 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 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50 |
| | 65 | +from |
| | 66 | +drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| | 67 | +ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| | 68 | +where ts >= '%s' and ts < '%s' |
| | 69 | +and (utm_campaign 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 > 50000 |
| | 75 | + |
| | 76 | +group by 1,2 ; |
| Index: trunk/fundraiser-statistics/reporting/banner_test_banners_by_interval.html |
| — | — | @@ -0,0 +1,66 @@ |
| | 2 | + |
| | 3 | +<html> |
| | 4 | + |
| | 5 | +<head> |
| | 6 | + |
| | 7 | +<title>Banner Test Form - Banner Data Only</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 Interval:</u></h1> |
| | 27 | + |
| | 28 | +<p><b> |
| | 29 | +Intervals always begin on the hour. |
| | 30 | +The first and / or last interval may be truncated by the start or end times if the period between start and end is not evenly divisible by the interval and does not begin on the hour. |
| | 31 | +</b></p> |
| | 32 | + |
| | 33 | +<form action="banner_test_proc.php" method="post"> |
| | 34 | +<input type="hidden" name="sqlFile" value="banner_test_banners_by_interval.sql"> |
| | 35 | +<br/> |
| | 36 | +<br/> |
| | 37 | +<label for="utm_campaign"> |
| | 38 | +<pre>UTM CAMPAIGN: </pre> |
| | 39 | +</label> |
| | 40 | +<input type="text" name="utm_campaign" /> |
| | 41 | +<br/> |
| | 42 | +<label for="start_time"> |
| | 43 | +<pre>START TIMESTAMP: </pre> |
| | 44 | +</label> |
| | 45 | + <input type="text" name="start_time" /> |
| | 46 | +<br/> |
| | 47 | +<label for="end_time"> |
| | 48 | +<pre>END TIMESTAMP: </pre> |
| | 49 | +</label> |
| | 50 | +<input type="text" name="end_time" /> |
| | 51 | +<br/> |
| | 52 | +<label for="interval"> |
| | 53 | +<pre>INTERVAL (MINUTES): </pre> |
| | 54 | +</label> |
| | 55 | +<input type="text" name="interval" /> |
| | 56 | +<br/> |
| | 57 | +<br/> |
| | 58 | +<label for="pwd"> |
| | 59 | +<pre>Key: </pre> |
| | 60 | +</label> |
| | 61 | +<input type="password" name="pwd" size="20"> |
| | 62 | +<br/> |
| | 63 | +<input type="submit" value="Run Banner Test"> |
| | 64 | +</form> |
| | 65 | + |
| | 66 | +</body> |
| | 67 | +</html> |
| \ No newline at end of file |