http://www.mediawiki.org/wiki/Special:Code/MediaWiki/90624
Revision: 90624
Author: rfaulk
Date: 2011-06-22 23:02:01 +0000 (Wed, 22 Jun 2011)
Log Message:
-----------
used in live reporting of donations and clicks for all campaigns, banners, and
LPs
Added Paths:
-----------
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely_all.sql
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_campaign_metrics_minutely_all.sql
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_lp_metrics_minutely_all.sql
Added:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely_all.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely_all.sql
(rev 0)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely_all.sql
2011-06-22 23:02:01 UTC (rev 90624)
@@ -0,0 +1,29 @@
+
+
+select
+
+if(dt_min < 10, concat(dt_hr, '0', dt_min,'00'), concat(dt_hr, dt_min,'00'))
as ts,
+banner,
+donations,
+clicks
+
+from
+
+(select
+
+DATE_FORMAT(ts,'%sY%sm%sd%sH') as dt_hr,
+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner,
+sum(not isnull(civicrm.civicrm_contribution.id)) as donations,
+sum(civicrm.civicrm_contribution.id) as clicks
+
+from
+
+drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution
+ON (drupal.contribution_tracking.contribution_id =
civicrm.civicrm_contribution.id)
+
+where ts >= '%s' and ts < '%s'
+group by 1,2,3) as ecomm
+
+
+order by 2,1 asc;
Added:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_campaign_metrics_minutely_all.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_campaign_metrics_minutely_all.sql
(rev 0)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_campaign_metrics_minutely_all.sql
2011-06-22 23:02:01 UTC (rev 90624)
@@ -0,0 +1,29 @@
+
+
+select
+
+if(dt_min < 10, concat(dt_hr, '0', dt_min,'00'), concat(dt_hr, dt_min,'00'))
as ts,
+utm_campaign,
+donations,
+clicks
+
+from
+
+(select
+
+DATE_FORMAT(ts,'%sY%sm%sd%sH') as dt_hr,
+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
+utm_campaign,
+sum(not isnull(civicrm.civicrm_contribution.id)) as donations,
+sum(civicrm.civicrm_contribution.id) as clicks
+
+from
+
+drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution
+ON (drupal.contribution_tracking.contribution_id =
civicrm.civicrm_contribution.id)
+
+where ts >= '%s' and ts < '%s'
+group by 1,2,3) as ecomm
+
+
+order by 2,1 asc;
Added:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_lp_metrics_minutely_all.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_lp_metrics_minutely_all.sql
(rev 0)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_lp_metrics_minutely_all.sql
2011-06-22 23:02:01 UTC (rev 90624)
@@ -0,0 +1,29 @@
+
+
+select
+
+if(dt_min < 10, concat(dt_hr, '0', dt_min,'00'), concat(dt_hr, dt_min,'00'))
as ts,
+landing_page,
+donations,
+clicks
+
+from
+
+(select
+
+DATE_FORMAT(ts,'%sY%sm%sd%sH') as dt_hr,
+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page,
+sum(not isnull(civicrm.civicrm_contribution.id)) as donations,
+sum(civicrm.civicrm_contribution.id) as clicks
+
+from
+
+drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution
+ON (drupal.contribution_tracking.contribution_id =
civicrm.civicrm_contribution.id)
+
+where ts >= '%s' and ts < '%s'
+group by 1,2,3) as ecomm
+
+
+order by 2,1 asc;
_______________________________________________
MediaWiki-CVS mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-cvs
