r90624 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r90623‎ | r90624 | r90625 >
Date:23:02, 22 June 2011
Author:rfaulk
Status:deferred
Tags:
Comment:
used in live reporting of donations and clicks for all campaigns, banners, and LPs
Modified paths:
  • /trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely_all.sql (added) (history)
  • /trunk/fundraiser-statistics/fundraiser-scripts/sql/report_campaign_metrics_minutely_all.sql (added) (history)
  • /trunk/fundraiser-statistics/fundraiser-scripts/sql/report_lp_metrics_minutely_all.sql (added) (history)

Diff [purge]

Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely_all.sql
——@@ -0,0 +1,29 @@
 2+
 3+
 4+select
 5+
 6+if(dt_min < 10, concat(dt_hr, '0', dt_min,'00'), concat(dt_hr, dt_min,'00')) as ts,
 7+banner,
 8+donations,
 9+clicks
 10+
 11+from
 12+
 13+(select
 14+
 15+DATE_FORMAT(ts,'%sY%sm%sd%sH') as dt_hr,
 16+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
 17+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner,
 18+sum(not isnull(civicrm.civicrm_contribution.id)) as donations,
 19+sum(civicrm.civicrm_contribution.id) as clicks
 20+
 21+from
 22+
 23+drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution
 24+ON (drupal.contribution_tracking.contribution_id = civicrm.civicrm_contribution.id)
 25+
 26+where ts >= '%s' and ts < '%s'
 27+group by 1,2,3) as ecomm
 28+
 29+
 30+order by 2,1 asc;
Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_lp_metrics_minutely_all.sql
——@@ -0,0 +1,29 @@
 2+
 3+
 4+select
 5+
 6+if(dt_min < 10, concat(dt_hr, '0', dt_min,'00'), concat(dt_hr, dt_min,'00')) as ts,
 7+landing_page,
 8+donations,
 9+clicks
 10+
 11+from
 12+
 13+(select
 14+
 15+DATE_FORMAT(ts,'%sY%sm%sd%sH') as dt_hr,
 16+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
 17+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page,
 18+sum(not isnull(civicrm.civicrm_contribution.id)) as donations,
 19+sum(civicrm.civicrm_contribution.id) as clicks
 20+
 21+from
 22+
 23+drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution
 24+ON (drupal.contribution_tracking.contribution_id = civicrm.civicrm_contribution.id)
 25+
 26+where ts >= '%s' and ts < '%s'
 27+group by 1,2,3) as ecomm
 28+
 29+
 30+order by 2,1 asc;
Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_campaign_metrics_minutely_all.sql
——@@ -0,0 +1,29 @@
 2+
 3+
 4+select
 5+
 6+if(dt_min < 10, concat(dt_hr, '0', dt_min,'00'), concat(dt_hr, dt_min,'00')) as ts,
 7+utm_campaign,
 8+donations,
 9+clicks
 10+
 11+from
 12+
 13+(select
 14+
 15+DATE_FORMAT(ts,'%sY%sm%sd%sH') as dt_hr,
 16+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
 17+utm_campaign,
 18+sum(not isnull(civicrm.civicrm_contribution.id)) as donations,
 19+sum(civicrm.civicrm_contribution.id) as clicks
 20+
 21+from
 22+
 23+drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution
 24+ON (drupal.contribution_tracking.contribution_id = civicrm.civicrm_contribution.id)
 25+
 26+where ts >= '%s' and ts < '%s'
 27+group by 1,2,3) as ecomm
 28+
 29+
 30+order by 2,1 asc;

Status & tagging log