| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_bannerLP_metrics_minutely.sql |
| — | — | @@ -0,0 +1,84 @@ |
| | 2 | + |
| | 3 | + |
| | 4 | +select |
| | 5 | + |
| | 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 | +imp.utm_source, |
| | 8 | +lp.landing_page, |
| | 9 | +floor(impressions * (views / total_views)) as impressions, |
| | 10 | +views, |
| | 11 | +-- total_clicks, |
| | 12 | +donations, |
| | 13 | +amount, |
| | 14 | +amount50, |
| | 15 | +(views / impressions) * (total_views / views) as click_rate, |
| | 16 | +-- donations / total_clicks as completion_rate, |
| | 17 | +round((donations / impressions) * (total_views / views), 6) as don_per_imp, |
| | 18 | +(amount / impressions) * (total_views / views) as amt_per_imp, |
| | 19 | +(amount50 / impressions) * (total_views / views) as amt50_per_imp |
| | 20 | + |
| | 21 | +from |
| | 22 | + |
| | 23 | +(select |
| | 24 | +DATE_FORMAT(on_minute,'%sY%sm%sd%sH') as dt_hr, |
| | 25 | +FLOOR(MINUTE(on_minute) / %s) * %s as dt_min, |
| | 26 | +utm_source, |
| | 27 | +landing_page, |
| | 28 | +sum(counts) as impressions |
| | 29 | +from banner_impressions |
| | 30 | +where on_minute > '%s' and on_minute < '%s' |
| | 31 | +group by 1,2,3) as imp |
| | 32 | + |
| | 33 | +join |
| | 34 | + |
| | 35 | +(select |
| | 36 | +DATE_FORMAT(request_time,'%sY%sm%sd%sH') as dt_hr, |
| | 37 | +FLOOR(MINUTE(request_time) / %s) * %s as dt_min, |
| | 38 | +utm_source, |
| | 39 | +landing_page, |
| | 40 | +count(*) as views, |
| | 41 | +utm_campaign |
| | 42 | +from landing_page_requests |
| | 43 | +where request_time >= '%s' and request_time < '%s' |
| | 44 | +and utm_campaign REGEXP '%s' |
| | 45 | +group by 1,2,3,4) as lp |
| | 46 | + |
| | 47 | +on imp.utm_source = lp.utm_source and imp.dt_hr = lp.dt_hr and imp.dt_min = lp.dt_min |
| | 48 | + |
| | 49 | +join |
| | 50 | + |
| | 51 | +(select |
| | 52 | +DATE_FORMAT(request_time,'%sY%sm%sd%sH') as dt_hr, |
| | 53 | +FLOOR(MINUTE(request_time) / %s) * %s as dt_min, |
| | 54 | +utm_source, |
| | 55 | +count(*) as total_views |
| | 56 | +from landing_page_requests |
| | 57 | +where request_time >= '%s' and request_time < '%s' |
| | 58 | +group by 1,2,3) as lp_tot |
| | 59 | + |
| | 60 | +on imp.utm_source = lp_tot.utm_source and imp.dt_hr = lp_tot.dt_hr and imp.dt_min = lp_tot.dt_min |
| | 61 | + |
| | 62 | +left join |
| | 63 | + |
| | 64 | +(select |
| | 65 | +DATE_FORMAT(receive_date,'%sY%sm%sd%sH') as hr, |
| | 66 | +FLOOR(MINUTE(receive_date) / %s) * %s as dt_min, |
| | 67 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner, |
| | 68 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| | 69 | +count(*) as total_clicks, |
| | 70 | +sum(not isnull(drupal.contribution_tracking.contribution_id)) as donations, |
| | 71 | +sum(total_amount) as amount, |
| | 72 | +sum(if(total_amount > 50, 50, total_amount)) as amount50 |
| | 73 | +from |
| | 74 | +drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution |
| | 75 | +ON (drupal.contribution_tracking.contribution_id = civicrm.civicrm_contribution.id) |
| | 76 | +where receive_date >= '%s' and receive_date < '%s' |
| | 77 | +and utm_campaign REGEXP '%s' |
| | 78 | +group by 1,2,3,4) as ecomm |
| | 79 | + |
| | 80 | +on ecomm.banner = lp.utm_source and ecomm.landing_page = lp.landing_page and ecomm.hr = lp.dt_hr and ecomm.dt_min = lp.dt_min |
| | 81 | + |
| | 82 | +where lp.utm_campaign REGEXP '%s' |
| | 83 | +group by 1,2 |
| | 84 | +-- having impressions > 100000 and donations > 10 |
| | 85 | +order by 1 asc; |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_bannerLP_confidence.sql |
| — | — | @@ -0,0 +1,79 @@ |
| | 2 | + |
| | 3 | + |
| | 4 | + |
| | 5 | +select |
| | 6 | + |
| | 7 | +lp.utm_source, |
| | 8 | +impressions as total_impressions, |
| | 9 | +impressions * (views / total_views) as impressions, |
| | 10 | +views as views, |
| | 11 | +total_clicks as clicks, |
| | 12 | +donations as donations, |
| | 13 | +amount as amount, |
| | 14 | +(views / impressions) * (total_views / views) as click_rate, |
| | 15 | +donations / total_clicks as completion_rate, |
| | 16 | +round((donations / impressions) * (total_views / views), 6) as don_per_imp, |
| | 17 | +(amount / impressions) * (total_views / views) as amt_per_imp, |
| | 18 | +donations / views as don_per_view, |
| | 19 | +amount / views as amt_per_view, |
| | 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 |
| | 23 | + |
| | 24 | +from |
| | 25 | + |
| | 26 | +(select |
| | 27 | +utm_source, |
| | 28 | +sum(counts) as impressions |
| | 29 | +from banner_impressions |
| | 30 | +where on_minute > '%s' and on_minute < '%s' |
| | 31 | +and utm_source REGEXP '%s' |
| | 32 | +group by 1) as imp |
| | 33 | + |
| | 34 | +join |
| | 35 | + |
| | 36 | +(select |
| | 37 | +utm_source, |
| | 38 | +landing_page, |
| | 39 | +count(*) as views |
| | 40 | +from landing_page_requests |
| | 41 | +where request_time >= '%s' and request_time < '%s' |
| | 42 | +and utm_campaign REGEXP '%s' |
| | 43 | +group by 1,2) as lp |
| | 44 | + |
| | 45 | +on imp.utm_source = lp.utm_source |
| | 46 | + |
| | 47 | +join |
| | 48 | + |
| | 49 | +(select |
| | 50 | +utm_source, |
| | 51 | +count(*) as total_views |
| | 52 | +from landing_page_requests |
| | 53 | +where request_time >= '%s' and request_time < '%s' |
| | 54 | +and utm_source REGEXP '%s' |
| | 55 | +group by 1) as lp_tot |
| | 56 | + |
| | 57 | +on imp.utm_source = lp_tot.utm_source |
| | 58 | + |
| | 59 | +left join |
| | 60 | + |
| | 61 | +(select |
| | 62 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner, |
| | 63 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| | 64 | +count(*) as total_clicks, |
| | 65 | +sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| | 66 | +sum(total_amount) AS amount, |
| | 67 | +sum(if(total_amount > 50, 50, total_amount)) as amount50, |
| | 68 | +sum(if(total_amount > 100, 100, total_amount)) as amount100 |
| | 69 | +from |
| | 70 | +drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution |
| | 71 | +ON (contribution_tracking.contribution_id = civicrm.civicrm_contribution.id) |
| | 72 | +where receive_date >= '%s' and receive_date < '%s' |
| | 73 | +and utm_campaign REGEXP '%s' |
| | 74 | +and utm_source REGEXP '%s' |
| | 75 | +group by 1,2) as ecomm |
| | 76 | + |
| | 77 | +on ecomm.banner = lp.utm_source and ecomm.landing_page = lp.landing_page |
| | 78 | + |
| | 79 | +group by 1 |
| | 80 | +-- having impressions > 100000 and donations > 10; |