| Index: trunk/fundraiser-statistics/fundraiser-scripts/compute_confidence.py |
| — | — | @@ -360,10 +360,15 @@ |
| 361 | 361 | print '\nCOMMAND = ' + test_call |
| 362 | 362 | file.write('\nCOMMAND = ' + test_call) |
| 363 | 363 | |
| | 364 | + |
| 364 | 365 | print '\n\n' + metric_name |
| | 366 | + print '\nitem 1 = ' + labels[0] |
| | 367 | + print 'item 2 = ' + labels[1] |
| 365 | 368 | print win_str |
| 366 | 369 | print '\ninterval\tmean1\t\tmean2\t\tstddev1\t\tstddev2\n' |
| 367 | 370 | file.write('\n\n' + metric_name) |
| | 371 | + file.write('\nitem 1 = ' + labels[0] + '\n') |
| | 372 | + file.write('\nitem 2 = ' + labels[1] + '\n') |
| 368 | 373 | file.write(win_str) |
| 369 | 374 | file.write('\n\ninterval\tmean1\t\tmean2\t\tstddev1\t\tstddev2\n\n') |
| 370 | 375 | |
| — | — | @@ -380,7 +385,7 @@ |
| 381 | 386 | line_args = '%.5f\t\t' + '%.5f\t\t' + '%.5f\t\t' + '%.5f\n' |
| 382 | 387 | line_str = line_args % (av_means_1, av_means_2, av_std_dev_1, av_std_dev_2) |
| 383 | 388 | |
| 384 | | - print '\n\nOverall Parameters:\n' |
| | 389 | + print '\n\nOverall Parameters -- the confidence test was run with these parameters:\n' |
| 385 | 390 | print '\nmean1\t\tmean2\t\tstddev1\t\tstddev2\n' |
| 386 | 391 | print line_str |
| 387 | 392 | |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql |
| — | — | @@ -4,17 +4,17 @@ |
| 5 | 5 | |
| 6 | 6 | if(imp.dt_min < 10, concat(imp.dt_hr, '0', imp.dt_min,'00'), concat(imp.dt_hr, imp.dt_min,'00')) as day_hr, |
| 7 | 7 | lp.utm_source, |
| 8 | | -impressions, |
| | 8 | +impressions * (views / total_views) as impressions, |
| 9 | 9 | views, |
| 10 | 10 | total_clicks, |
| 11 | 11 | donations, |
| 12 | 12 | amount, |
| 13 | 13 | amount50, |
| 14 | | -views / impressions as click_rate, |
| | 14 | +(views / impressions) * (total_views / views) as click_rate, |
| 15 | 15 | donations / total_clicks as completion_rate, |
| 16 | | -round(donations / impressions, 6) as don_per_imp, |
| 17 | | -amount / impressions as amt_per_imp, |
| 18 | | -amount50 / impressions as amt50_per_imp |
| | 16 | +round((donations / impressions) * (total_views / views), 6) as don_per_imp, |
| | 17 | +(amount / impressions) * (total_views / views) as amt_per_imp, |
| | 18 | +(amount50 / impressions) * (total_views / views) as amt50_per_imp |
| 19 | 19 | |
| 20 | 20 | from |
| 21 | 21 | |
| — | — | @@ -24,7 +24,7 @@ |
| 25 | 25 | utm_source, |
| 26 | 26 | sum(counts) as impressions |
| 27 | 27 | from impression |
| 28 | | -where on_minute >= '%s' and on_minute < '%s' |
| | 28 | +where on_minute > '%s' and on_minute < '%s' |
| 29 | 29 | group by 1,2,3) as imp |
| 30 | 30 | |
| 31 | 31 | join |
| — | — | @@ -41,7 +41,19 @@ |
| 42 | 42 | |
| 43 | 43 | on imp.utm_source = lp.utm_source and imp.dt_hr = lp.dt_hr and imp.dt_min = lp.dt_min |
| 44 | 44 | |
| | 45 | +join |
| 45 | 46 | |
| | 47 | +(select |
| | 48 | +DATE_FORMAT(request_time,'%sY%sm%sd%sH') as dt_hr, |
| | 49 | +FLOOR(MINUTE(request_time) / %s) * %s as dt_min, |
| | 50 | +utm_source, |
| | 51 | +count(*) as total_views |
| | 52 | +from landing_page |
| | 53 | +where request_time >= '%s' and request_time < '%s' |
| | 54 | +group by 1,2,3) as lp_tot |
| | 55 | + |
| | 56 | +on imp.utm_source = lp_tot.utm_source and imp.dt_hr = lp_tot.dt_hr and imp.dt_min = lp_tot.dt_min |
| | 57 | + |
| 46 | 58 | join |
| 47 | 59 | |
| 48 | 60 | (select |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_confidence.sql |
| — | — | @@ -5,18 +5,20 @@ |
| 6 | 6 | |
| 7 | 7 | lp.utm_source, |
| 8 | 8 | impressions as total_impressions, |
| 9 | | -impressions as impressions, |
| | 9 | +impressions * (views / total_views) as impressions, |
| 10 | 10 | views as views, |
| 11 | 11 | total_clicks as clicks, |
| 12 | 12 | donations as donations, |
| 13 | 13 | amount as amount, |
| 14 | | -views / impressions as click_rate, |
| | 14 | +(views / impressions) * (total_views / views) as click_rate, |
| 15 | 15 | donations / total_clicks as completion_rate, |
| 16 | | -round(donations / impressions, 6) as don_per_imp, |
| 17 | | -amount / impressions as amt_per_imp, |
| | 16 | +round((donations / impressions) * (total_views / views), 6) as don_per_imp, |
| | 17 | +(amount / impressions) * (total_views / views) as amt_per_imp, |
| 18 | 18 | donations / views as don_per_view, |
| 19 | 19 | amount / views as amt_per_view, |
| 20 | | -amount / donations as amt_per_donation |
| | 20 | +amount / donations as amt_per_donation, |
| | 21 | +(amount50 / impressions) * (total_views / views) as amt50_per_imp, |
| | 22 | +(amount100 / impressions) * (total_views / views) as amt100_per_imp |
| 21 | 23 | |
| 22 | 24 | from |
| 23 | 25 | |
| — | — | @@ -40,14 +42,27 @@ |
| 41 | 43 | |
| 42 | 44 | on imp.utm_source = lp.utm_source |
| 43 | 45 | |
| | 46 | +join |
| 44 | 47 | |
| | 48 | +(select |
| | 49 | +utm_source, |
| | 50 | +count(*) as total_views |
| | 51 | +from landing_page |
| | 52 | +where request_time >= '%s' and request_time < '%s' |
| | 53 | +and utm_source REGEXP '%s' |
| | 54 | +group by 1) as lp_tot |
| | 55 | + |
| | 56 | +on imp.utm_source = lp_tot.utm_source |
| | 57 | + |
| 45 | 58 | join |
| 46 | 59 | |
| 47 | 60 | (select |
| 48 | 61 | SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner, |
| 49 | 62 | count(*) as total_clicks, |
| 50 | 63 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 51 | | -sum(converted_amount) AS amount |
| | 64 | +sum(converted_amount) AS amount, |
| | 65 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50, |
| | 66 | +sum(if(converted_amount > 100, 100, converted_amount)) as amount100 |
| 52 | 67 | from |
| 53 | 68 | drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| 54 | 69 | ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/query_store.py |
| — | — | @@ -102,7 +102,7 @@ |
| 103 | 103 | end = args[1] |
| 104 | 104 | banner = args[2] |
| 105 | 105 | campaign = args[3] |
| 106 | | - sql_stmnt = sql_stmnt % (start, end, banner, start, end, campaign, start, end, campaign, banner) |
| | 106 | + sql_stmnt = sql_stmnt % (start, end, banner, start, end, campaign, start, end, banner, start, end, campaign, banner) |
| 107 | 107 | |
| 108 | 108 | elif query_name == 'report_LP_confidence': |
| 109 | 109 | start = args[0] |
| — | — | @@ -136,7 +136,7 @@ |
| 137 | 137 | imp_start_time = args[4] |
| 138 | 138 | |
| 139 | 139 | sql_stmnt = sql_stmnt % ('%', '%', '%', '%', interval, interval, imp_start_time, end_time, '%', '%', '%', '%', interval, interval, start_time, end_time, campaign, \ |
| 140 | | - '%', '%', '%', '%', interval, interval, start_time, end_time, campaign) |
| | 140 | + '%', '%', '%', '%', interval, interval, start_time, end_time, '%', '%', '%', '%', interval, interval, start_time, end_time, campaign) |
| 141 | 141 | |
| 142 | 142 | elif query_name == 'report_LP_metrics_minutely': |
| 143 | 143 | start_time = args[0] |