| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_campaign_metrics_minutely.sql |
| — | — | @@ -0,0 +1,44 @@ |
| | 2 | + |
| | 3 | + |
| | 4 | +select |
| | 5 | + |
| | 6 | +if(lp.dt_min < 10, concat(lp.dt_hr, '0', lp.dt_min,'00'), concat(lp.dt_hr, lp.dt_min,'00')) as ts, |
| | 7 | +concat('%s', ' - ', lp.banner,' - ', lp.landing_page) as pipeline_name, |
| | 8 | +views, |
| | 9 | +donations |
| | 10 | + |
| | 11 | +from |
| | 12 | + |
| | 13 | +(select |
| | 14 | +DATE_FORMAT(request_time,'%sY%sm%sd%sH') as dt_hr, |
| | 15 | +FLOOR(MINUTE(request_time) / %s) * %s as dt_min, |
| | 16 | +utm_source as banner, |
| | 17 | +landing_page, |
| | 18 | +count(*) as views |
| | 19 | +from landing_page |
| | 20 | +where request_time >= '%s' and request_time < '%s' and utm_campaign REGEXP '%s' |
| | 21 | +group by 1,2,3,4) as lp |
| | 22 | + |
| | 23 | +join |
| | 24 | + |
| | 25 | +(select |
| | 26 | + |
| | 27 | +DATE_FORMAT(receive_date,'%sY%sm%sd%sH') as hr, |
| | 28 | +FLOOR(MINUTE(receive_date) / %s) * %s as dt_min, |
| | 29 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner, |
| | 30 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| | 31 | +sum(not isnull(drupal.contribution_tracking.contribution_id)) as donations |
| | 32 | + |
| | 33 | +from |
| | 34 | + |
| | 35 | +drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution |
| | 36 | +ON (drupal.contribution_tracking.contribution_id = civicrm.civicrm_contribution.id) |
| | 37 | + |
| | 38 | +where receive_date >= '%s' and receive_date < '%s' and utm_campaign REGEXP '%s' |
| | 39 | +group by 1,2,3,4) as ecomm |
| | 40 | + |
| | 41 | +on ecomm.banner = lp.banner and ecomm.landing_page = lp.landing_page and ecomm.hr = lp.dt_hr and ecomm.dt_min = lp.dt_min |
| | 42 | + |
| | 43 | +group by 1,2 |
| | 44 | + |
| | 45 | +order by 1 asc; |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_campaign_metrics_minutely_total.sql |
| — | — | @@ -0,0 +1,40 @@ |
| | 2 | + |
| | 3 | + |
| | 4 | +select |
| | 5 | + |
| | 6 | +if(lp_tot.dt_min < 10, concat(lp_tot.dt_hr, '0', lp_tot.dt_min,'00'), concat(lp_tot.dt_hr, lp_tot.dt_min,'00')) as ts, |
| | 7 | +'%s' as pipeline_name, |
| | 8 | +views, |
| | 9 | +donations |
| | 10 | + |
| | 11 | +from |
| | 12 | + |
| | 13 | +(select |
| | 14 | +DATE_FORMAT(request_time,'%sY%sm%sd%sH') as dt_hr, |
| | 15 | +FLOOR(MINUTE(request_time) / %s) * %s as dt_min, |
| | 16 | +count(*) as views |
| | 17 | +from landing_page |
| | 18 | +where request_time >= '%s' and request_time < '%s' and utm_campaign REGEXP '%s' |
| | 19 | +group by 1,2) as lp_tot |
| | 20 | + |
| | 21 | +join |
| | 22 | + |
| | 23 | +(select |
| | 24 | + |
| | 25 | +DATE_FORMAT(receive_date,'%sY%sm%sd%sH') as hr, |
| | 26 | +FLOOR(MINUTE(receive_date) / %s) * %s as dt_min, |
| | 27 | +sum(not isnull(drupal.contribution_tracking.contribution_id)) as donations |
| | 28 | + |
| | 29 | +from |
| | 30 | + |
| | 31 | +drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution |
| | 32 | +ON (drupal.contribution_tracking.contribution_id = civicrm.civicrm_contribution.id) |
| | 33 | + |
| | 34 | +where receive_date >= '%s' and receive_date < '%s' and utm_campaign REGEXP '%s' |
| | 35 | +group by 1,2) as ecomm |
| | 36 | + |
| | 37 | +on ecomm.hr = lp_tot.dt_hr and ecomm.dt_min = lp_tot.dt_min |
| | 38 | + |
| | 39 | +group by 1,2 |
| | 40 | + |
| | 41 | +order by 1 asc; |