http://www.mediawiki.org/wiki/Special:Code/MediaWiki/90633
Revision: 90633
Author: rfaulk
Date: 2011-06-22 23:30:47 +0000 (Wed, 22 Jun 2011)
Log Message:
-----------
pull banner and lp data by time. updated to use new Fundraiser db tables
Modified Paths:
--------------
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
Modified:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
2011-06-22 23:24:03 UTC (rev 90632)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
2011-06-22 23:30:47 UTC (rev 90633)
@@ -18,9 +18,10 @@
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
+count(*) as views,
+utm_campaign
-from landing_page
+from landing_page_requests
where request_time >= '%s' and request_time < '%s'
and utm_campaign REGEXP '%s'
@@ -45,6 +46,7 @@
on ecomm.landing_page = lp.landing_page and ecomm.hr = lp.dt_hr and
ecomm.dt_min = lp.dt_min
+where lp.utm_campaign REGEXP '%s'
group by 1,2
-- having views > 1000 and donations > 10
order by 1 asc;
Modified:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
2011-06-22 23:24:03 UTC (rev 90632)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
2011-06-22 23:30:47 UTC (rev 90633)
@@ -3,7 +3,7 @@
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,
+imp.utm_source,
floor(impressions * (views / total_views)) as impressions,
views,
-- total_clicks,
@@ -23,7 +23,7 @@
FLOOR(MINUTE(on_minute) / %s) * %s as dt_min,
utm_source,
sum(counts) as impressions
-from impression
+from banner_impressions
where on_minute > '%s' and on_minute < '%s'
group by 1,2,3) as imp
@@ -33,8 +33,9 @@
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
+count(*) as views,
+utm_campaign
+from landing_page_requests
where request_time >= '%s' and request_time < '%s'
and utm_campaign REGEXP '%s'
group by 1,2,3) as lp
@@ -48,13 +49,13 @@
FLOOR(MINUTE(request_time) / %s) * %s as dt_min,
utm_source,
count(*) as total_views
-from landing_page
+from landing_page_requests
where request_time >= '%s' and request_time < '%s'
group by 1,2,3) as lp_tot
on imp.utm_source = lp_tot.utm_source and imp.dt_hr = lp_tot.dt_hr and
imp.dt_min = lp_tot.dt_min
-join
+left join
(select
DATE_FORMAT(receive_date,'%sY%sm%sd%sH') as hr,
@@ -73,6 +74,7 @@
on ecomm.banner = lp.utm_source and ecomm.hr = lp.dt_hr and ecomm.dt_min =
lp.dt_min
+where lp.utm_campaign REGEXP '%s'
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
