| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_confidence.sql |
| — | — | @@ -0,0 +1,44 @@ |
| | 2 | + |
| | 3 | + |
| | 4 | + |
| | 5 | +select |
| | 6 | + |
| | 7 | +lp.utm_source, |
| | 8 | +views as views, |
| | 9 | +total_clicks as clicks, |
| | 10 | +donations as donations, |
| | 11 | +amount as amount, |
| | 12 | +donations / total_clicks as completion_rate, |
| | 13 | +donations / views as don_per_view, |
| | 14 | +amount / views as amt_per_view, |
| | 15 | +amount / donations as amt_per_donation |
| | 16 | + |
| | 17 | +from |
| | 18 | + |
| | 19 | +select |
| | 20 | +landing_page, |
| | 21 | +count(*) as views |
| | 22 | +from landing_page |
| | 23 | +where request_time >= '%s' and request_time < '%s' |
| | 24 | +and utm_campaign REGEXP '%s' |
| | 25 | +and landing_page REGEXP '%s' |
| | 26 | +group by 1) as lp |
| | 27 | + |
| | 28 | +join |
| | 29 | + |
| | 30 | +(select |
| | 31 | +SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page, |
| | 32 | +count(*) as total_clicks, |
| | 33 | +sum(not isnull(contribution_tracking.contribution_id)) as donations, |
| | 34 | +sum(converted_amount) AS amount |
| | 35 | +from |
| | 36 | +drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| | 37 | +ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| | 38 | +where ts >= '%s' and ts < '%s' |
| | 39 | +and utm_campaign REGEXP '%s' |
| | 40 | +and SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) REGEXP '%s' |
| | 41 | +group by 1) as ecomm |
| | 42 | + |
| | 43 | +on ecomm.landing_page = lp.landing_page and ecomm.utm_campaign = lp.utm_campaign |
| | 44 | + |
| | 45 | +group by 1; |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/query_store.py |
| — | — | @@ -103,7 +103,14 @@ |
| 104 | 104 | banner = args[2] |
| 105 | 105 | campaign = args[3] |
| 106 | 106 | sql_stmnt = sql_stmnt % (start, end, banner, start, end, campaign, start, end, campaign, banner) |
| 107 | | - |
| | 107 | + |
| | 108 | + elif query_name == 'report_LP_confidence': |
| | 109 | + start = args[0] |
| | 110 | + end = args[1] |
| | 111 | + lp = args[2] |
| | 112 | + campaign = args[3] |
| | 113 | + sql_stmnt = sql_stmnt % (start, end, campaign, lp, start, end, campaign, lp) |
| | 114 | + |
| 108 | 115 | else: |
| 109 | 116 | return 'no such table\n' |
| 110 | 117 | |
| — | — | @@ -251,6 +258,17 @@ |
| 252 | 259 | return 10 |
| 253 | 260 | else: |
| 254 | 261 | return -1 |
| | 262 | + elif query_name == 'report_LP_confidence': |
| | 263 | + if metric_name == 'completion_rate': |
| | 264 | + return 5 |
| | 265 | + elif metric_name == 'don_per_view': |
| | 266 | + return 6 |
| | 267 | + elif metric_name == 'amt_per_view': |
| | 268 | + return 7 |
| | 269 | + elif metric_name == 'amt_per_donation': |
| | 270 | + return 8 |
| | 271 | + else: |
| | 272 | + return -1 |
| 255 | 273 | else: |
| 256 | 274 | return 'no such table' |
| 257 | 275 | |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/miner_help.py |
| — | — | @@ -187,14 +187,8 @@ |
| 188 | 188 | return new_collection |
| 189 | 189 | |
| 190 | 190 | """ Given an IP localizes the country """ |
| 191 | | -def localize_IP(ip_string): |
| | 191 | +def localize_IP(cur, ip_string): |
| 192 | 192 | |
| 193 | | - """ Get db object / Create cursor """ |
| 194 | | - # db = MySQLdb.connect(host='127.0.0.1', user='rfaulk', db='faulkner', port=3307) |
| 195 | | - db = MySQLdb.connect(host='storage3.pmtpa.wmnet', user='rfaulk', db='faulkner') |
| 196 | | - cur = db.cursor() |
| 197 | | - |
| 198 | | - |
| 199 | 193 | # compute ip number |
| 200 | 194 | ip_fields = ip_string.split('.') |
| 201 | 195 | w = int(ip_fields[0]) |
| — | — | @@ -211,17 +205,13 @@ |
| 212 | 206 | row = cur.fetchone() |
| 213 | 207 | except: |
| 214 | 208 | db.rollback() |
| 215 | | - sys.exit("Could execute: " + sql_stmnt) |
| | 209 | + sys.exit("Could not execute: " + sql_stmnt) |
| 216 | 210 | |
| 217 | | - country = row[0] |
| | 211 | + try: |
| | 212 | + country = row[0] |
| | 213 | + except: |
| | 214 | + country = '' |
| 218 | 215 | |
| 219 | | - # Commit to the db |
| 220 | | - db.commit() |
| 221 | | - |
| 222 | | - # Close connection |
| 223 | | - cur.close() |
| 224 | | - db.close() |
| 225 | | - |
| 226 | 216 | return country |
| 227 | 217 | |
| 228 | 218 | """ Load data into the IP localization table to associate IPs with countries """ |