| Index: trunk/fundraiser-statistics/reporting/banner_test_by_banner.sql |
| — | — | @@ -12,8 +12,10 @@ |
| 13 | 13 | donations / total_clicks as conversion_rate, |
| 14 | 14 | round(donations / impressions,6) as don_per_imp, |
| 15 | 15 | amount / impressions as amt_per_imp, |
| | 16 | +amount50 / impressions as amt50_per_imp, |
| 16 | 17 | donations / views as don_per_view, |
| 17 | | -amount / views as amt_per_view |
| | 18 | +amount / views as amt_per_view, |
| | 19 | +amount50 / views as amt50_per_view |
| 18 | 20 | |
| 19 | 21 | |
| 20 | 22 | from |
| — | — | @@ -59,7 +61,8 @@ |
| 60 | 62 | SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner, |
| 61 | 63 | count(*) as total_clicks, |
| 62 | 64 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 63 | | -sum(converted_amount) AS amount |
| | 65 | +sum(converted_amount) AS amount, |
| | 66 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50 |
| 64 | 67 | from |
| 65 | 68 | drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| 66 | 69 | ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| — | — | @@ -70,6 +73,6 @@ |
| 71 | 74 | |
| 72 | 75 | on ecomm.banner = imp.utm_source and imp.dt_hr = ecomm.dt_hr and imp.dt_min = ecomm.dt_min |
| 73 | 76 | |
| 74 | | -where impressions > 10000 |
| | 77 | +where impressions > 50000 |
| 75 | 78 | |
| 76 | 79 | group by 1,2 ; |
| Index: trunk/fundraiser-statistics/reporting/ecomm_test_by_hour.sql |
| — | — | @@ -6,7 +6,8 @@ |
| 7 | 7 | count(*) as total_clicks, |
| 8 | 8 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 9 | 9 | sum(converted_amount) AS amount, |
| 10 | | -sum(if(converted_amount > 100, 100, converted_amount)) AS modified_amount, -- truncates donations over 100 |
| | 10 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50, |
| | 11 | +-- sum(if(converted_amount > 100, 100, converted_amount)) AS modified_amount, -- truncates donations over 100 |
| 11 | 12 | max(converted_amount) AS max_amt, |
| 12 | 13 | sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks, |
| 13 | 14 | sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as cc_don, |
| Index: trunk/fundraiser-statistics/reporting/banner_test_by_lp.sql |
| — | — | @@ -7,9 +7,11 @@ |
| 8 | 8 | total_clicks as clicks, |
| 9 | 9 | donations as donations, |
| 10 | 10 | amount as amount, |
| | 11 | +amount50 as amount50, |
| 11 | 12 | donations / total_clicks as conversion_rate, |
| 12 | 13 | donations / views as don_per_view, |
| 13 | | -amount / views as amt_per_view |
| | 14 | +amount / views as amt_per_view, |
| | 15 | +amount50 / views as amt50_per_view |
| 14 | 16 | |
| 15 | 17 | |
| 16 | 18 | from |
| — | — | @@ -33,7 +35,8 @@ |
| 34 | 36 | SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| 35 | 37 | count(*) as total_clicks, |
| 36 | 38 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 37 | | -sum(converted_amount) AS amount |
| | 39 | +sum(converted_amount) AS amount, |
| | 40 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50 |
| 38 | 41 | from |
| 39 | 42 | drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| 40 | 43 | ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| Index: trunk/fundraiser-statistics/reporting/banner_test_by_hour.sql |
| — | — | @@ -12,12 +12,16 @@ |
| 13 | 13 | total_clicks as clicks, |
| 14 | 14 | donations as donations, |
| 15 | 15 | amount as amount, |
| | 16 | +amount50 as amount50, |
| 16 | 17 | views / floor(impressions * views / views_banner) as click_rate_lp, |
| 17 | 18 | donations / total_clicks as conversion_rate, |
| 18 | 19 | round(donations / floor(impressions * views / views_banner) ,6) as don_per_imp, |
| 19 | | -donations / views as don_per_view |
| | 20 | +round(amount / floor(impressions * views / views_banner) ,6) as amt_per_imp, |
| | 21 | +round(amount50 / floor(impressions * views / views_banner) ,6) as amt50_per_imp, |
| | 22 | +donations / views as don_per_view, |
| | 23 | +amount / views as amt_per_view, |
| | 24 | +amount50 / views as amt50_per_view |
| 20 | 25 | |
| 21 | | - |
| 22 | 26 | from |
| 23 | 27 | |
| 24 | 28 | (select |
| — | — | @@ -74,7 +78,8 @@ |
| 75 | 79 | utm_campaign, |
| 76 | 80 | count(*) as total_clicks, |
| 77 | 81 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 78 | | -sum(converted_amount) AS amount |
| | 82 | +sum(converted_amount) AS amount, |
| | 83 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50 |
| 79 | 84 | from |
| 80 | 85 | drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| 81 | 86 | ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| Index: trunk/fundraiser-statistics/reporting/landing_page_test_by_hour.sql |
| — | — | @@ -11,7 +11,7 @@ |
| 12 | 12 | donations / total_clicks as completion_rate, |
| 13 | 13 | donations / views as don_per_view, |
| 14 | 14 | amount / views as amt_per_view, |
| 15 | | -modified_amount / views as amt_per_view_reduced, |
| | 15 | +amount50 / views as amt50_per_view, |
| 16 | 16 | max_amt, |
| 17 | 17 | pp_don, |
| 18 | 18 | cc_don, |
| — | — | @@ -42,7 +42,7 @@ |
| 43 | 43 | count(*) as total_clicks, |
| 44 | 44 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 45 | 45 | sum(converted_amount) AS amount, |
| 46 | | -sum(if(converted_amount > 100, 100, converted_amount)) AS modified_amount, -- truncates donations over 100 |
| | 46 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50, |
| 47 | 47 | max(converted_amount) AS max_amt, |
| 48 | 48 | sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks, |
| 49 | 49 | sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as cc_don, |
| — | — | @@ -59,7 +59,7 @@ |
| 60 | 60 | |
| 61 | 61 | on ecomm.landing_page = lp.landing_page and ecomm.utm_campaign = lp.utm_campaign and lp.hr = ecomm.hr |
| 62 | 62 | |
| 63 | | -where views > 10 |
| | 63 | +where views > 100 |
| 64 | 64 | |
| 65 | 65 | group by 1,2,3 order by 1,8 desc; |
| 66 | 66 | |
| Index: trunk/fundraiser-statistics/reporting/custom_campaign_plot.php |
| — | — | @@ -35,14 +35,10 @@ |
| 36 | 36 | |
| 37 | 37 | // generate plots |
| 38 | 38 | $args = ' ' . $cmpgn . ' ' . $start; |
| 39 | | -// $cmd = 'echo "baggin5" | sudo -S ./plot_build_latest.sh' . $args; |
| 40 | | -$cmd = './plot_build_latest.sh' . $args; |
| | 39 | +$cmd = '/home/rfaulk/fundraiser-statistics/bash/plot_build_latest.sh' ; |
| 41 | 40 | |
| 42 | | -chdir('/home/rfaulk/fundraiser-statistics/bash/'); |
| 43 | | - |
| 44 | 41 | // Execute the shell command |
| 45 | | -//$output1 = shell_exec($cmd . ' 2>&1'); |
| 46 | | -//$output2 = shell_exec('whoami'); |
| | 42 | +chdir('/home/rfaulk/fundraiser-statistics/bash/'); |
| 47 | 43 | $output = shell_exec('echo ' . $cmd. $args . ' >async_plotter.sh'); |
| 48 | 44 | |
| 49 | 45 | echo '<html>'; |
| Index: trunk/fundraiser-statistics/reporting/ecomm_test.sql |
| — | — | @@ -5,7 +5,8 @@ |
| 6 | 6 | count(*) as total_clicks, |
| 7 | 7 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 8 | 8 | sum(converted_amount) AS amount, |
| 9 | | -sum(if(converted_amount > 100, 100, converted_amount)) AS modified_amount, -- truncates donations over 100 |
| | 9 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50, |
| | 10 | +-- sum(if(converted_amount > 100, 100, converted_amount)) AS modified_amount, -- truncates donations over 100 |
| 10 | 11 | max(converted_amount) AS max_amt, |
| 11 | 12 | sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks, |
| 12 | 13 | sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as cc_don, |
| Index: trunk/fundraiser-statistics/reporting/banner_test.sql |
| — | — | @@ -15,8 +15,10 @@ |
| 16 | 16 | donations / total_clicks as conversion_rate, |
| 17 | 17 | round(donations / floor(impressions * views / views_banner) ,6) as don_per_imp, |
| 18 | 18 | round(amount / floor(impressions * views / views_banner) ,6) as amt_per_imp, |
| | 19 | +round(amount50 / floor(impressions * views / views_banner) ,6) as amt50_per_imp, |
| 19 | 20 | donations / views as don_per_view, |
| 20 | | -amount / views as amt_per_view |
| | 21 | +amount / views as amt_per_view, |
| | 22 | +amount50 / views as amt50_per_view |
| 21 | 23 | |
| 22 | 24 | |
| 23 | 25 | from |
| — | — | @@ -70,7 +72,8 @@ |
| 71 | 73 | utm_campaign, |
| 72 | 74 | count(*) as total_clicks, |
| 73 | 75 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 74 | | -sum(converted_amount) AS amount |
| | 76 | +sum(converted_amount) AS amount, |
| | 77 | +sum(if(converted_amount > 50, 50, converted_amount)) as amount50 |
| 75 | 78 | from |
| 76 | 79 | drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| 77 | 80 | ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| Index: trunk/fundraiser-statistics/reporting/landing_page_test.sql |
| — | — | @@ -13,7 +13,6 @@ |
| 14 | 14 | donations / views as don_per_view, |
| 15 | 15 | amount / views as amt_per_view, |
| 16 | 16 | amount50 / views as amt50_per_view, |
| 17 | | -modified_amount / views as amt_per_view_reduced, |
| 18 | 17 | max_amt, |
| 19 | 18 | pp_don, |
| 20 | 19 | cc_don, |
| — | — | @@ -43,7 +42,6 @@ |
| 44 | 43 | sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| 45 | 44 | sum(converted_amount) AS amount, |
| 46 | 45 | sum(if(converted_amount > 50, 50, converted_amount)) as amount50, |
| 47 | | -sum(if(converted_amount > 100, 100, converted_amount)) AS modified_amount, -- truncates donations over 100 |
| 48 | 46 | max(converted_amount) AS max_amt, |
| 49 | 47 | sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks, |
| 50 | 48 | sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as cc_don, |
| — | — | @@ -60,7 +58,7 @@ |
| 61 | 59 | |
| 62 | 60 | on ecomm.landing_page = lp.landing_page and ecomm.utm_campaign = lp.utm_campaign |
| 63 | 61 | |
| 64 | | -where views > 100 |
| | 62 | +where views > 1000 |
| 65 | 63 | |
| 66 | 64 | group by 1,2,3 order by 8 desc; |
| 67 | 65 | |
| Index: trunk/fundraiser-statistics/reporting/banner_test_banners.sql |
| — | — | @@ -63,6 +63,6 @@ |
| 64 | 64 | |
| 65 | 65 | on ecomm.banner = imp.utm_source |
| 66 | 66 | |
| 67 | | -where impressions > 10000 |
| | 67 | +where impressions > 50000 |
| 68 | 68 | |
| 69 | 69 | group by 1,2 order by 10 desc; |