http://www.mediawiki.org/wiki/Special:Code/MediaWiki/84668
Revision: 84668
Author: rfaulk
Date: 2011-03-24 06:59:33 +0000 (Thu, 24 Mar 2011)
Log Message:
-----------
SQL queries used in interval reporting. Break out campaign data by the minute.
Added Paths:
-----------
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
Added:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
(rev 0)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
2011-03-24 06:59:33 UTC (rev 84668)
@@ -0,0 +1,56 @@
+
+select
+
+if(lp.dt_min < 10, concat(lp.dt_hr, ' 0', lp.dt_min), concat(lp.dt_hr, ' ',
lp.dt_min)) as day_hr,
+lp.landing_page,
+views,
+total_clicks,
+donations,
+amount,
+donations / total_clicks as completion_rate,
+donations / views as don_per_view,
+amount / views as amt_per_view,
+amount50 / views as amt50_per_view
+
+from
+
+(select
+DATE_FORMAT(request_time,'%sY-%sm-%sd %sH') as dt_hr,
+FLOOR(MINUTE(request_time) / %s) * %s as dt_min,
+landing_page,
+count(*) as views
+
+from landing_page
+
+where request_time >= '%s' and request_time < '%s'
+and utm_campaign REGEXP '%s'
+group by 1,2) as lp
+
+join
+
+(select
+DATE_FORMAT(ts,'%sY-%sm-%sd %sH') as hr,
+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page,
+count(*) as total_clicks,
+sum(not isnull(contribution_tracking.contribution_id)) as donations,
+sum(converted_amount) AS amount,
+sum(if(converted_amount > 50, 50, converted_amount)) as amount50
+
+from
+drupal.contribution_tracking LEFT JOIN civicrm.public_reporting
+ON (contribution_tracking.contribution_id =
civicrm.public_reporting.contribution_id)
+where ts >= '%s' and ts < '%s'
+and utm_campaign REGEXP '%s'
+group by 1,2) as ecomm
+
+on ecomm.landing_page = lp.landing_page and ecomm.hr = lp.dt_hr and
ecomm.dt_min = lp.dt_min
+
+group by 1,2
+-- having views > 1000 and donations > 10
+order by 1 asc;
+
+
+
+
+
Added:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
(rev 0)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
2011-03-24 06:59:33 UTC (rev 84668)
@@ -0,0 +1,66 @@
+
+
+select
+
+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,
+lp.utm_source,
+impressions,
+views,
+total_clicks,
+donations,
+amount,
+amount50,
+views / impressions as click_rate,
+donations / total_clicks as completion_rate,
+round(donations / impressions, 6) as don_per_imp,
+amount / impressions as amt_per_imp,
+amount50 / impressions as amt50_per_imp
+
+from
+
+(select
+DATE_FORMAT(on_minute,'%sY%sm%sd%sH') as dt_hr,
+FLOOR(MINUTE(on_minute) / %s) * %s as dt_min,
+utm_source,
+sum(counts) as impressions
+from impression
+where on_minute >= '%s' and on_minute < '%s'
+group by 1,2,3) as imp
+
+join
+
+(select
+DATE_FORMAT(request_time,'%sY%sm%sd%sH') as dt_hr,
+FLOOR(MINUTE(request_time) / %s) * %s as dt_min,
+utm_source,
+count(*) as views
+from landing_page
+where request_time >= '%s' and request_time < '%s'
+and utm_campaign REGEXP '%s'
+group by 1,2,3) as lp
+
+on imp.utm_source = lp.utm_source and imp.dt_hr = lp.dt_hr and imp.dt_min =
lp.dt_min
+
+
+join
+
+(select
+DATE_FORMAT(ts,'%sY%sm%sd%sH') as hr,
+FLOOR(MINUTE(ts) / %s) * %s as dt_min,
+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner,
+count(*) as total_clicks,
+sum(not isnull(contribution_tracking.contribution_id)) as donations,
+sum(converted_amount) as amount,
+sum(if(converted_amount > 50, 50, converted_amount)) as amount50
+from
+drupal.contribution_tracking LEFT JOIN civicrm.public_reporting
+ON (contribution_tracking.contribution_id =
civicrm.public_reporting.contribution_id)
+where ts >= '%s' and ts < '%s'
+and utm_campaign REGEXP '%s'
+group by 1,2,3) as ecomm
+
+on ecomm.banner = lp.utm_source and ecomm.hr = lp.dt_hr and ecomm.dt_min =
lp.dt_min
+
+group by 1,2
+-- having impressions > 100000 and donations > 10
+order by 1 asc;
_______________________________________________
MediaWiki-CVS mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-cvs
