| Index: trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py |
| — | — | @@ -48,8 +48,8 @@ |
| 49 | 49 | def init_db(self): |
| 50 | 50 | """ Establish connection """ |
| 51 | 51 | #db = MySQLdb.connect(host='db10.pmtpa.wmnet', user='rfaulk', db='faulkner') |
| 52 | | - self.db = MySQLdb.connect(host='127.0.0.1', user='rfaulk', db='faulkner', port=3307) |
| 53 | | - #self.db = MySQLdb.connect(host='storage3.pmtpa.wmnet', user='rfaulk', db='faulkner') |
| | 52 | + #self.db = MySQLdb.connect(host='127.0.0.1', user='rfaulk', db='faulkner', port=3307) |
| | 53 | + self.db = MySQLdb.connect(host='storage3.pmtpa.wmnet', user='rfaulk', db='faulkner') |
| 54 | 54 | |
| 55 | 55 | """ Create cursor """ |
| 56 | 56 | self.cur = self.db.cursor() |
| — | — | @@ -213,7 +213,7 @@ |
| 214 | 214 | sql_stmnt = mh.read_sql(filename) |
| 215 | 215 | sql_stmnt = query_obj.format_query(query_name + descriptor, sql_stmnt, [start_time, end_time]) |
| 216 | 216 | |
| 217 | | - labels = [None] * 9 |
| | 217 | + labels = [None] * 20 |
| 218 | 218 | labels[0] = 'clicks' |
| 219 | 219 | labels[1] = 'donations' |
| 220 | 220 | labels[2] = 'total amount' |
| — | — | @@ -223,6 +223,18 @@ |
| 224 | 224 | labels[6] = 'Other Amount' |
| 225 | 225 | labels[7] = 'Email Amount' |
| 226 | 226 | labels[8] = 'Recurring Guess' |
| | 227 | + labels[9] = 'completion_rate' |
| | 228 | + labels[10] = 'pp_clicks' |
| | 229 | + labels[11] = 'pp_donations' |
| | 230 | + labels[12] = 'pp_completion' |
| | 231 | + labels[13] = 'pp_amount' |
| | 232 | + labels[14] = 'pp_max_amount' |
| | 233 | + labels[15] = 'cc_clicks' |
| | 234 | + labels[16] = 'cc_donations' |
| | 235 | + labels[17] = 'cc_completion' |
| | 236 | + labels[18] = 'cc_amount' |
| | 237 | + labels[19] = 'cc_max_amount' |
| | 238 | + |
| 227 | 239 | |
| 228 | 240 | num_keys = len(labels) |
| 229 | 241 | |
| — | — | @@ -248,7 +260,7 @@ |
| 249 | 261 | self.close_db() |
| 250 | 262 | |
| 251 | 263 | # Only interested in amounts |
| 252 | | - return [labels[2:9], lists[2:9]] |
| | 264 | + return [labels, lists] |
| 253 | 265 | |
| 254 | 266 | |
| 255 | 267 | |
| — | — | @@ -274,7 +286,7 @@ |
| 275 | 287 | |
| 276 | 288 | |
| 277 | 289 | |
| 278 | | - def run_hr(self): |
| | 290 | + def run_hr(self, type): |
| 279 | 291 | |
| 280 | 292 | |
| 281 | 293 | # Current date & time |
| — | — | @@ -300,12 +312,38 @@ |
| 301 | 313 | # RUN BY HOUR |
| 302 | 314 | descriptor = '_by_hr' |
| 303 | 315 | return_val = self.run_query(start_time, end_time, query_name, descriptor) |
| | 316 | + |
| 304 | 317 | labels = return_val[0] # curve labels |
| 305 | 318 | counts = return_val[1] # curve data - lists |
| 306 | | - |
| 307 | | - title = 'Total Amounts: ' + start_time + ' -- ' + end_time |
| | 319 | + |
| | 320 | + if type == 'BAN_EM': |
| | 321 | + indices = range(2,9) |
| | 322 | + title = 'Total Amounts: ' + start_time + ' -- ' + end_time |
| | 323 | + ylabel = 'Amount' |
| | 324 | + elif type == 'CC_PP_completion': |
| | 325 | + indices = [12,17] |
| | 326 | + title = 'Credit Card & Paypal Completion Rates: ' + start_time + ' -- ' + end_time |
| | 327 | + ylabel = 'Rate' |
| | 328 | + elif type == 'CC_PP_amount': |
| | 329 | + indices = [13,18] |
| | 330 | + title = 'Credit Card & Paypal Total Amounts: ' + start_time + ' -- ' + end_time |
| | 331 | + ylabel = 'Amount' |
| | 332 | + else: |
| | 333 | + sys.exit("Total Amounts: You must enter a valid report type.\n" ) |
| | 334 | + |
| | 335 | + # Exract relevant labels and values |
| | 336 | + labels_temp = list() |
| | 337 | + counts_temp = list() |
| | 338 | + |
| | 339 | + for i in range(len(labels)): |
| | 340 | + if i in indices: |
| | 341 | + labels_temp.append(labels[i]) |
| | 342 | + counts_temp.append(counts[i]) |
| | 343 | + |
| | 344 | + labels = labels_temp |
| | 345 | + counts = counts_temp |
| | 346 | + |
| 308 | 347 | xlabel = 'Time - Hours' |
| 309 | | - ylabel = 'Amount' |
| 310 | 348 | subplot_index = 111 |
| 311 | 349 | |
| 312 | 350 | # plot the curves |
| — | — | @@ -315,7 +353,8 @@ |
| 316 | 354 | |
| 317 | 355 | ranges = [min(time_range), max(time_range)] |
| 318 | 356 | |
| 319 | | - self.gen_plot(time_range, counts, labels, title, xlabel, ylabel, ranges, subplot_index, query_name+descriptor) |
| | 357 | + fname = query_name + descriptor + '_' + type |
| | 358 | + self.gen_plot(time_range, counts, labels, title, xlabel, ylabel, ranges, subplot_index, fname) |
| 320 | 359 | |
| 321 | 360 | |
| 322 | 361 | |
| — | — | @@ -489,9 +528,9 @@ |
| 490 | 529 | |
| 491 | 530 | # Current date & time |
| 492 | 531 | now = datetime.datetime.now() |
| 493 | | - UTC = 8 |
| 494 | | - delta = datetime.timedelta(hours=UTC) |
| 495 | | - now = now + delta |
| | 532 | + #UTC = 8 |
| | 533 | + #delta = datetime.timedelta(hours=UTC) |
| | 534 | + #now = now + delta |
| 496 | 535 | |
| 497 | 536 | # ESTABLISH THE START TIME TO PULL ANALYTICS |
| 498 | 537 | hours_back = 24 |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_total_amounts.sql |
| — | — | @@ -13,18 +13,17 @@ |
| 14 | 14 | sum(if(utm_source REGEXP '.rpp' ,1 ,0))*0.5*8*10 as recurring_guess, |
| 15 | 15 | |
| 16 | 16 | sum(not isnull(contribution_tracking.contribution_id)) /count(*) as completion_rate, |
| 17 | | -sum(converted_amount) AS amount, |
| 18 | | -sum(if(right(utm_source,2)='pp' and contribution_tracking.contribution_id,1,0))/sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as pp_over_cc_dons, |
| | 17 | +-- sum(if(right(utm_source,2)='pp' and contribution_tracking.contribution_id,1,0))/sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as pp_over_cc_dons, |
| 19 | 18 | sum(if(right(utm_source,2)='pp',1,0)) as pp_clicks, |
| 20 | 19 | sum(if(right(utm_source,2)='pp' and contribution_tracking.contribution_id,1,0)) as pp_donations, |
| 21 | 20 | sum(if(right(utm_source,2)='pp' and contribution_tracking.contribution_id,1,0))/sum(if(right(utm_source,2)='pp',1,0)) as pp_completion, |
| | 21 | +sum(if(right(utm_source,2)='pp',converted_amount,0)) as pp_amt, |
| | 22 | +max(if(right(utm_source,2)='pp',converted_amount,0)) as max_pp_amt, |
| 22 | 23 | sum(if(right(utm_source,2)='cc',1,0)) as cc_clicks, |
| 23 | 24 | sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0)) as cc_donations, |
| 24 | 25 | sum(if(right(utm_source,2)='cc' and contribution_tracking.contribution_id,1,0))/sum(if(right(utm_source,2)='cc',1,0)) as cc_completion, |
| 25 | 26 | sum(if(right(utm_source,2)='cc',converted_amount,0)) as cc_amt, |
| 26 | | -sum(if(right(utm_source,2)='pp',converted_amount,0)) as pp_amt, |
| 27 | 27 | max(if(right(utm_source,2)='cc',converted_amount,0)) as max_cc_amt, |
| 28 | | -max(if(right(utm_source,2)='pp',converted_amount,0)) as max_pp_amt, |
| 29 | 28 | avg(converted_amount) as average, |
| 30 | 29 | max(converted_amount) as max_amount |
| 31 | 30 | |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/query_store.py |
| — | — | @@ -173,7 +173,7 @@ |
| 174 | 174 | return 7 |
| 175 | 175 | elif metric_name == 'click_rate': |
| 176 | 176 | return 8 |
| 177 | | - elif metric_name == 'conversion_rate': |
| | 177 | + elif metric_name == 'completion_rate': |
| 178 | 178 | return 9 |
| 179 | 179 | elif metric_name == 'don_per_imp': |
| 180 | 180 | return 10 |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/run_plots.py |
| — | — | @@ -20,14 +20,16 @@ |
| 21 | 21 | |
| 22 | 22 | |
| 23 | 23 | # Run the total amount plots |
| 24 | | -tar.run_hr() |
| | 24 | +tar.run_hr('BAN_EM') |
| | 25 | +tar.run_hr('CC_PP_completion') |
| | 26 | +tar.run_hr('CC_PP_amount') |
| 25 | 27 | tar.run_day() |
| 26 | 28 | |
| 27 | 29 | # Run the banner/lp plots |
| 28 | 30 | blpr.run('LP', 'don_per_view') |
| 29 | 31 | blpr.run('BAN', 'don_per_imp') |
| 30 | 32 | blpr.run('BAN', 'click_rate') |
| 31 | | -blpr.run('LP', 'conversion_rate') |
| | 33 | +blpr.run('LP', 'completion_rate') |
| 32 | 34 | |
| 33 | 35 | # Run the banner/lp plots |
| 34 | 36 | blpr.run('LP-TEST', 'don_per_view') |
| Index: trunk/fundraiser-statistics/reporting/fundraiser_analytics.html |
| — | — | @@ -29,8 +29,9 @@ |
| 30 | 30 | <h1><u>Wikimedia Fundraiser Analystics</u></h1> |
| 31 | 31 | <br> |
| 32 | 32 | |
| 33 | | -<h3><a href="http://fundraising.wikimedia.org/stats/reporting.html">Reporting</a></h3> |
| | 33 | +<h3><a href="http://fundraising.wikimedia.org/stats/reporting_totals.html">Reporting - Totals</a></h3> |
| 34 | 34 | <h3><a href="http://fundraising.wikimedia.org/stats/reporting_latest.html">Reporting - Latest Test</a></h3> |
| | 35 | +<h3><a href="http://fundraising.wikimedia.org/stats/reporting_all.html">Reporting - All Banners / LPs</a></h3> |
| 35 | 36 | |
| 36 | 37 | <br> |
| 37 | 38 | |
| Index: trunk/fundraiser-statistics/bash/plot_build_hr.sh |
| — | — | @@ -7,7 +7,9 @@ |
| 8 | 8 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_LP_metrics_don_per_view.png /srv/org.wikimedia.fundraising/stats/ |
| 9 | 9 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_day.png /srv/org.wikimedia.fundraising/stats/ |
| 10 | 10 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_banner_metrics_don_per_imp.png /srv/org.wikimedia.fundraising/stats/ |
| 11 | | -cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_hr.png /srv/org.wikimedia.fundraising/stats/ |
| | 11 | +cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_hr_BAN_EM.png /srv/org.wikimedia.fundraising/stats/ |
| | 12 | +cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_hr_CC_PP_amount.png /srv/org.wikimedia.fundraising/stats/ |
| | 13 | +cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_total_amounts_by_hr_CC_PP_completion.png /srv/org.wikimedia.fundraising/stats/ |
| 12 | 14 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_banner_metrics_click_rate.png /srv/org.wikimedia.fundraising/stats/ |
| 13 | 15 | cp /home/rfaulk/fundraiser-statistics/fundraiser-scripts/report_LP_metrics_conversion_rate.png /srv/org.wikimedia.fundraising/stats/ |
| 14 | 16 | |