| Index: trunk/fundraiser-statistics/reporting/ecomm_test_by_hour.sql |
| — | — | @@ -1,12 +1,14 @@ |
| 2 | 2 | |
| 3 | 3 | select |
| 4 | 4 | DATE_FORMAT(ts,'%sY-%sm-%sd %sH') as hr, |
| | 5 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner, |
| 5 | 6 | SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| 6 | 7 | utm_campaign, |
| 7 | 8 | count(*) as total_clicks, |
| 8 | 9 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 9 | 10 | sum(converted_amount) AS amount, |
| 10 | 11 | sum(if(converted_amount > 50, 50, converted_amount)) as amount50, |
| | 12 | +sum(if(converted_amount > 100, 100, converted_amount)) as amount100, |
| 11 | 13 | -- sum(if(converted_amount > 100, 100, converted_amount)) AS modified_amount, -- truncates donations over 100 |
| 12 | 14 | max(converted_amount) AS max_amt, |
| 13 | 15 | sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks, |
| — | — | @@ -20,7 +22,7 @@ |
| 21 | 23 | ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| 22 | 24 | where ts >= '%s' and ts < '%s' |
| 23 | 25 | and (utm_campaign REGEXP '%s') |
| 24 | | -group by 1,2,3 |
| | 26 | +group by 1,2,3,4 |
| 25 | 27 | |
| 26 | 28 | |
| 27 | 29 | |
| Index: trunk/fundraiser-statistics/reporting/ecomm_test_by_interval.sql |
| — | — | @@ -1,12 +1,14 @@ |
| 2 | 2 | |
| 3 | 3 | select |
| 4 | 4 | if( FLOOR(MINUTE(ts) / %s) * %s) < 10, concat(DATE_FORMAT(ts,'%sY-%sm-%sd %sH'), ' 0', FLOOR(MINUTE(ts) / %s) * %s), concat(DATE_FORMAT(ts,'%sY-%sm-%sd %sH'), ' ', FLOOR(MINUTE(ts) / %s) * %s)) as day_hr, |
| | 5 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner, |
| 5 | 6 | SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| 6 | 7 | utm_campaign, |
| 7 | 8 | count(*) as total_clicks, |
| 8 | 9 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 9 | 10 | sum(converted_amount) AS amount, |
| 10 | 11 | sum(if(converted_amount > 50, 50, converted_amount)) as amount50, |
| | 12 | +sum(if(converted_amount > 100, 100, converted_amount)) as amount100, |
| 11 | 13 | -- sum(if(converted_amount > 100, 100, converted_amount)) AS modified_amount, -- truncates donations over 100 |
| 12 | 14 | max(converted_amount) AS max_amt, |
| 13 | 15 | sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks, |
| — | — | @@ -20,7 +22,7 @@ |
| 21 | 23 | ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| 22 | 24 | where ts >= '%s' and ts < '%s' |
| 23 | 25 | and (utm_campaign REGEXP '%s') |
| 24 | | -group by 1,2,3 |
| | 26 | +group by 1,2,3,4 |
| 25 | 27 | |
| 26 | 28 | |
| 27 | 29 | |
| Index: trunk/fundraiser-statistics/reporting/ecomm_test.sql |
| — | — | @@ -1,11 +1,13 @@ |
| 2 | 2 | |
| 3 | 3 | select |
| | 4 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner, |
| 4 | 5 | SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| 5 | 6 | utm_campaign, |
| 6 | 7 | count(*) as total_clicks, |
| 7 | 8 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 8 | 9 | sum(converted_amount) AS amount, |
| 9 | 10 | sum(if(converted_amount > 50, 50, converted_amount)) as amount50, |
| | 11 | +sum(if(converted_amount > 100, 100, converted_amount)) as amount100, |
| 10 | 12 | -- sum(if(converted_amount > 100, 100, converted_amount)) AS modified_amount, -- truncates donations over 100 |
| 11 | 13 | max(converted_amount) AS max_amt, |
| 12 | 14 | sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks, |
| — | — | @@ -19,7 +21,7 @@ |
| 20 | 22 | ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| 21 | 23 | where ts >= '%s' and ts < '%s' |
| 22 | 24 | and (utm_campaign REGEXP '%s') |
| 23 | | -group by 1,2 |
| | 25 | +group by 1,2,3 |
| 24 | 26 | |
| 25 | 27 | |
| 26 | 28 | |