| Index: trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py |
| — | — | @@ -310,7 +310,7 @@ |
| 311 | 311 | sql_stmnt = mh.read_sql(filename) |
| 312 | 312 | sql_stmnt = query_obj.format_query(query_name + descriptor, sql_stmnt, [start_time, end_time]) |
| 313 | 313 | |
| 314 | | - labels = [None] * 20 |
| | 314 | + labels = [None] * 21 |
| 315 | 315 | labels[0] = 'clicks' |
| 316 | 316 | labels[1] = 'donations' |
| 317 | 317 | labels[2] = 'total amount' |
| — | — | @@ -331,6 +331,7 @@ |
| 332 | 332 | labels[17] = 'cc_completion' |
| 333 | 333 | labels[18] = 'cc_amount' |
| 334 | 334 | labels[19] = 'cc_max_amount' |
| | 335 | + labels[20] = 'total_amt50' |
| 335 | 336 | |
| 336 | 337 | |
| 337 | 338 | num_keys = len(labels) |
| — | — | @@ -434,7 +435,7 @@ |
| 435 | 436 | |
| 436 | 437 | |
| 437 | 438 | |
| 438 | | - def run_day(self): |
| | 439 | + def run_day(self,type): |
| 439 | 440 | |
| 440 | 441 | # Current date & time |
| 441 | 442 | now = datetime.datetime.now() |
| — | — | @@ -461,7 +462,7 @@ |
| 462 | 463 | labels = return_val[0] |
| 463 | 464 | counts = return_val[1] |
| 464 | 465 | |
| 465 | | - r = self.get_query_fields(labels, counts, 'BAN_EM', start_time, end_time) |
| | 466 | + r = self.get_query_fields(labels, counts, type, start_time, end_time) |
| 466 | 467 | labels = r[0] |
| 467 | 468 | counts = r[1] |
| 468 | 469 | title = r[2] |
| — | — | @@ -477,7 +478,8 @@ |
| 478 | 479 | |
| 479 | 480 | ranges = [min(time_range), max(time_range)] |
| 480 | 481 | |
| 481 | | - self.gen_plot(time_range, counts, labels, title, xlabel, ylabel, ranges, subplot_index, query_name+descriptor) |
| | 482 | + fname = query_name + descriptor + '_' + type |
| | 483 | + self.gen_plot(time_range, counts, labels, title, xlabel, ylabel, ranges, subplot_index, fname) |
| 482 | 484 | |
| 483 | 485 | |
| 484 | 486 | def get_query_fields(self, labels, counts, type, start_time, end_time): |
| — | — | @@ -494,6 +496,10 @@ |
| 495 | 497 | indices = [13,18] |
| 496 | 498 | title = 'Credit Card & Paypal Total Amounts: ' + start_time + ' -- ' + end_time |
| 497 | 499 | ylabel = 'Amount' |
| | 500 | + elif type == 'AMT_VS_AMT50': |
| | 501 | + indices = [2,20] |
| | 502 | + title = 'Amount50 and Amount Totals: ' + start_time + ' -- ' + end_time |
| | 503 | + ylabel = 'Amount' |
| 498 | 504 | else: |
| 499 | 505 | sys.exit("Total Amounts: You must enter a valid report type.\n" ) |
| 500 | 506 | |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_total_amounts.sql |
| — | — | @@ -24,10 +24,10 @@ |
| 25 | 25 | sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0))/sum(if(right(utm_source,2)='cc',1,0)) as cc_completion, |
| 26 | 26 | sum(if(right(utm_source,2)='cc',converted_amount,0)) as cc_amt, |
| 27 | 27 | max(if(right(utm_source,2)='cc',converted_amount,0)) as max_cc_amt, |
| | 28 | +sum(if(converted_amount > 50,50,converted_amount)) as total_amt50, |
| 28 | 29 | avg(converted_amount) as average, |
| 29 | 30 | max(converted_amount) as max_amount |
| 30 | 31 | |
| 31 | | - |
| 32 | 32 | from (drupal.contribution_tracking left join civicrm.public_reporting on (drupal.contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id)) |
| 33 | 33 | WHERE ts >= '%s' and ts < '%s' |
| 34 | 34 | GROUP BY 1 |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics.sql |
| — | — | @@ -11,7 +11,7 @@ |
| 12 | 12 | donations as donations, |
| 13 | 13 | amount as amount, |
| 14 | 14 | views / impressions as click_rate, |
| 15 | | -donations / total_clicks as conversion_rate, |
| | 15 | +donations / total_clicks as completion_rate, |
| 16 | 16 | round(donations / impressions, 6) as don_per_imp, |
| 17 | 17 | amount / impressions as amt_per_imp, |
| 18 | 18 | donations / views as don_per_view, |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics.sql |
| — | — | @@ -10,7 +10,7 @@ |
| 11 | 11 | donations as donations, |
| 12 | 12 | amount as amount, |
| 13 | 13 | null as click_rate, |
| 14 | | -donations / total_clicks as conversion_rate, |
| | 14 | +donations / total_clicks as completion_rate, |
| 15 | 15 | null as don_per_imp, |
| 16 | 16 | null as amt_per_imp, |
| 17 | 17 | donations / views as don_per_view, |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/run_plots.py |
| — | — | @@ -24,7 +24,9 @@ |
| 25 | 25 | tar.run_hr('BAN_EM') |
| 26 | 26 | tar.run_hr('CC_PP_completion') |
| 27 | 27 | tar.run_hr('CC_PP_amount') |
| 28 | | -tar.run_day() |
| | 28 | +tar.run_hr('AMT_VS_AMT50') |
| | 29 | +tar.run_day('BAN_EM') |
| | 30 | +tar.run_day('AMT_VS_AMT50') |
| 29 | 31 | |
| 30 | 32 | # Run the banner/lp plots |
| 31 | 33 | blpr.run('LP', 'don_per_view') |
| Index: trunk/fundraiser-statistics/reporting/landing_page_compare.html |
| — | — | @@ -24,6 +24,19 @@ |
| 25 | 25 | <body> |
| 26 | 26 | <h1><u>Custom Banner / LP Test:</u></h1> |
| 27 | 27 | |
| | 28 | +<br/> |
| | 29 | +<p> |
| | 30 | +<b><u>To get the best results from this test use it across full hours.</u> That is your start and end timestamps should be |
| | 31 | +right on the hour (ie. ending in '0000'). </b> The statistical test being used is the Wald test which assumes a normal |
| | 32 | +distribution of the data. Due to the known variance over several hours each hour is tested indendently. |
| | 33 | +</p> |
| | 34 | +<p> |
| | 35 | +This test compates one metric across two landing pages running concurrently (which usually also entails running over the same campaign). |
| | 36 | +So to test the performance of the two pages over some metric enter the times, camapaign(s), landing page names, and finally select the metric from the |
| | 37 | +radio button to test. |
| | 38 | +</p> |
| | 39 | +<br/> |
| | 40 | + |
| 28 | 41 | <form action="compare_test.php" method="post"> |
| 29 | 42 | <input type="hidden" name="sqlFile" value="banner_test_by_lp.sql"> |
| 30 | 43 | <br/> |
| Index: trunk/fundraiser-statistics/reporting/banner_test_by_lp.sql |
| — | — | @@ -47,6 +47,6 @@ |
| 48 | 48 | |
| 49 | 49 | on ecomm.landing_page = lp.landing_page and lp.dt_hr = ecomm.dt_hr and lp.dt_min = ecomm.dt_min |
| 50 | 50 | |
| 51 | | -where views > 1000 and donations > 20 |
| | 51 | +where views > 100 and donations > 20 |
| 52 | 52 | |
| 53 | 53 | group by 1,2 ; |
| Index: trunk/fundraiser-statistics/reporting/reporting_totals.html |
| — | — | @@ -63,6 +63,19 @@ |
| 64 | 64 | <br> |
| 65 | 65 | |
| 66 | 66 | |
| | 67 | +<h3><u>Amount 50 and Amount Totals - Last 24 Hours</u></h3> |
| | 68 | +<OBJECT WIDTH="1600" HEIGHT="800" data="report_total_amounts_by_hr_AMT_VS_AMT50.png" type="image/png"> |
| | 69 | +</OBJECT> |
| | 70 | + |
| | 71 | +<br> |
| | 72 | + |
| | 73 | +<h3><u>Amount 50 and Amount Totals - Last 7 Days</u></h3> |
| | 74 | +<OBJECT WIDTH="1600" HEIGHT="800" data="report_total_amounts_by_day_AMT_VS_AMT50.png" type="image/png"> |
| | 75 | +</OBJECT> |
| | 76 | + |
| | 77 | +<br> |
| | 78 | + |
| | 79 | + |
| 67 | 80 | </body> |
| 68 | 81 | |
| 69 | 82 | </html> |
| \ No newline at end of file |
| Index: trunk/fundraiser-statistics/reporting/compare_test.php |
| — | — | @@ -48,13 +48,17 @@ |
| 49 | 49 | } |
| 50 | 50 | |
| 51 | 51 | // Format the query based on the type |
| 52 | | -if ($sql_file == "banner_test_by_banner.sql" || $sql_file == "banner_test_by_lp.sql") { |
| | 52 | +if ($sql_file == "banner_test_by_banner.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 | | -} elseif ($sql_file == "landing_compare.sql") { |
| 56 | | - $query = sprintf($query, $start, $end, $cmpgn, $start, $end, $cmpgn); |
| | 55 | +} elseif ($sql_file == "banner_test_by_lp.sql") { |
| | 56 | + $query1 = sprintf($query, '%','%','%','%','10','10', $start, $end, $cmpgn1, $item1, '%','%','%','%','10','10', $start, $end, $cmpgn1, $item1); |
| | 57 | + $query2 = sprintf($query, '%','%','%','%','10','10', $start, $end, $cmpgn2, $item2, '%','%','%','%','10','10', $start, $end, $cmpgn2, $item2); |
| 57 | 58 | } |
| 58 | 59 | |
| | 60 | +// echo '<br><br>' . $query1 . '<br><br>'; |
| | 61 | +// echo '<br><br>' . $query2 . '<br><br>'; |
| | 62 | + |
| 59 | 63 | // Execute Queries |
| 60 | 64 | $result1 = mysql_query($query1); |
| 61 | 65 | $result2 = mysql_query($query2); |
| — | — | @@ -80,6 +84,7 @@ |
| 81 | 85 | $time = array(); |
| 82 | 86 | $metric1 = array(); |
| 83 | 87 | $metric2 = array(); |
| | 88 | +$num_samples = array(); |
| 84 | 89 | |
| 85 | 90 | // Compute the means of the first item |
| 86 | 91 | $counter = 0; |
| — | — | @@ -87,10 +92,9 @@ |
| 88 | 93 | while ($row = mysql_fetch_assoc($result1)) { |
| 89 | 94 | |
| 90 | 95 | $time[$n1] = $row["day_hr"]; |
| 91 | | - $metric1[$n1] = $row[$metric]; |
| | 96 | + $x1 = $row[$metric]; |
| | 97 | + $metric1[$n1] = $x1; |
| 92 | 98 | |
| 93 | | - $x1 = $row[$metric]; |
| 94 | | - $group1[$n] = $x1; |
| 95 | 99 | $m1[$index] = $x1 + $m1[$index]; |
| 96 | 100 | $n1=$n1+1; |
| 97 | 101 | |
| — | — | @@ -106,10 +110,9 @@ |
| 107 | 111 | $counter = 0; |
| 108 | 112 | $index = 0; |
| 109 | 113 | while ($row = mysql_fetch_assoc($result2)) { |
| 110 | | - $metric2[$n2] = $row[$metric]; |
| | 114 | + $x2 = $row[$metric]; |
| | 115 | + $metric2[$n2] = $x2; |
| 111 | 116 | |
| 112 | | - $x2 = $row[$metric]; |
| 113 | | - $group2[$n] = $x2; |
| 114 | 117 | $m2[$index] = $x2 + $m2[$index]; |
| 115 | 118 | $n2=$n2+1; |
| 116 | 119 | |
| — | — | @@ -137,8 +140,8 @@ |
| 138 | 141 | { |
| 139 | 142 | $index = floor($counter / $interval); |
| 140 | 143 | |
| 141 | | - $diff1 = $group1[$counter] - $m1[$index]; |
| 142 | | - $diff2 = $group2[$counter] - $m2[$index]; |
| | 144 | + $diff1 = $metric1[$counter] - $m1[$index]; |
| | 145 | + $diff2 = $metric2[$counter] - $m2[$index]; |
| 143 | 146 | $v1[$index] = $v1[$index] + pow($diff1,2); |
| 144 | 147 | $v2[$index] = $v2[$index] + pow($diff2,2); |
| 145 | 148 | |
| — | — | @@ -179,6 +182,9 @@ |
| 180 | 183 | |
| 181 | 184 | echo 'The average of Group 1 for hour ' . ($i + 1). ': ' . $m1[$i] . '<br>'; |
| 182 | 185 | echo 'The average of Group 2 for hour ' . ($i + 1). ': ' . $m2[$i] . '<br>'; |
| | 186 | + echo 'The standard deviation of Group 1 for hour ' . ($i + 1). ': ' . pow($v1[$i], 0.5) . '<br>'; |
| | 187 | + echo 'The standard deviation of Group 2 for hour ' . ($i + 1). ': ' . pow($v2[$i], 0.5) . '<br>'; |
| | 188 | + |
| 183 | 189 | |
| 184 | 190 | if ($W[$i] >= 0.1) { |
| 185 | 191 | echo '<br>8% confident about the winner.<br>'; |
| — | — | @@ -234,8 +240,9 @@ |
| 235 | 241 | echo "</br></br><table width='50%'><tr>"; |
| 236 | 242 | //loop thru the field names to print the correct headers |
| 237 | 243 | echo "<th>". "Time Stamp" . "</th>"; |
| 238 | | -echo "<th>". mysql_field_name($result1, $metric) . "_1" . "</th>"; |
| 239 | | -echo "<th>". mysql_field_name($result1, $metric) . "_2" . "</th>"; |
| | 244 | +echo "<th>". $metric . "_1" . "</th>"; |
| | 245 | +echo "<th>". $metric . "_2" . "</th>"; |
| | 246 | +// echo "<th>". mysql_field_name($result2, $metric) . "_2" . "</th>"; |
| 240 | 247 | echo "</tr>"; |
| 241 | 248 | |
| 242 | 249 | for ( $counter = 0; $counter < $n; $counter += 1) { |
| Index: trunk/fundraiser-statistics/reporting/banner_compare.html |
| — | — | @@ -24,6 +24,19 @@ |
| 25 | 25 | <body> |
| 26 | 26 | <h1><u>Custom Banner / LP Test:</u></h1> |
| 27 | 27 | |
| | 28 | +<br/> |
| | 29 | +<p> |
| | 30 | +<b><u>To get the best results from this test use it across full hours.</u> That is your start and end timestamps should be |
| | 31 | +right on the hour (ie. ending in '0000'). </b> The statistical test being used is the Wald test which assumes a normal |
| | 32 | +distribution of the data. Due to the known variance over several hours each hour is tested indendently. |
| | 33 | +</p> |
| | 34 | +<p> |
| | 35 | +This test compates one metric across two banners running concurrently (which usually also entails running over the same campaign). |
| | 36 | +So to test the performance of the two pages over some metric enter the times, camapaign(s), banner names, and finally select the metric from the |
| | 37 | +radio button to test. |
| | 38 | +</p> |
| | 39 | +<br/> |
| | 40 | + |
| 28 | 41 | <form action="compare_test.php" method="post"> |
| 29 | 42 | <input type="hidden" name="sqlFile" value="banner_test_by_banner.sql"> |
| 30 | 43 | <br/> |
| Index: trunk/fundraiser-statistics/bash/plot_build_hr.sh |
| — | — | @@ -4,18 +4,25 @@ |
| 5 | 5 | |
| 6 | 6 | python run_plots.py |
| 7 | 7 | |
| 8 | | -cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_LP_metrics_don_per_view.png /srv/org.wikimedia.fundraising/stats/ |
| 9 | | -cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_day.png /srv/org.wikimedia.fundraising/stats/ |
| 10 | | -cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_banner_metrics_don_per_imp.png /srv/org.wikimedia.fundraising/stats/ |
| | 8 | +# total amounts |
| | 9 | +cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_day_BAN_EM.png /srv/org.wikimedia.fundraising/stats/ |
| 11 | 10 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_hr_BAN_EM.png /srv/org.wikimedia.fundraising/stats/ |
| 12 | 11 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_hr_CC_PP_amount.png /srv/org.wikimedia.fundraising/stats/ |
| 13 | 12 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_hr_CC_PP_completion.png /srv/org.wikimedia.fundraising/stats/ |
| | 13 | +cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_day_AMT_VS_AMT50.png /srv/org.wikimedia.fundraising/stats/ |
| | 14 | +cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_hr_AMT_VS_AMT50.png /srv/org.wikimedia.fundraising/stats/ |
| | 15 | + |
| | 16 | +# Banner / LP |
| | 17 | +cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_LP_metrics_don_per_view.png /srv/org.wikimedia.fundraising/stats/ |
| | 18 | +cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_banner_metrics_don_per_imp.png /srv/org.wikimedia.fundraising/stats/ |
| 14 | 19 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_banner_metrics_click_rate.png /srv/org.wikimedia.fundraising/stats/ |
| 15 | 20 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_LP_metrics_completion_rate.png /srv/org.wikimedia.fundraising/stats/ |
| 16 | 21 | |
| | 22 | +# latest plots |
| 17 | 23 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_LP_metrics_don_per_view_latest.png /srv/org.wikimedia.fundraising/stats/ |
| 18 | 24 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_banner_metrics_don_per_imp_latest.png /srv/org.wikimedia.fundraising/stats/ |
| 19 | 25 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_banner_metrics_click_rate_latest.png /srv/org.wikimedia.fundraising/stats/ |
| 20 | 26 | |
| | 27 | +# mining counts |
| 21 | 28 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_banner_impressions_by_hour.png /srv/org.wikimedia.fundraising/stats/ |
| 22 | 29 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_lp_views_by_hour.png /srv/org.wikimedia.fundraising/stats/ |
| \ No newline at end of file |