| Index: trunk/fundraiser-statistics/fundraiser-scripts/query_store.py |
| — | — | @@ -64,7 +64,12 @@ |
| 65 | 65 | elif query_name == 'report_bannerLP_metrics': |
| 66 | 66 | start_time = args[0] |
| 67 | 67 | end_time = args[1] |
| 68 | | - sql_stmnt = sql_stmnt % ('%', '%', '%', '%', start_time, end_time, '%', '%', '%', '%', start_time, end_time, '%', '%', '%', '%', start_time, end_time, '%', '%', '%', '%', start_time, end_time) |
| | 68 | + campaign = args[2] |
| | 69 | + sql_stmnt = sql_stmnt % ('%', '%', '%', '%', start_time, end_time, '%', '%', '%', '%', start_time, end_time, '%', '%', '%', '%', start_time, end_time, campaign, '%', '%', '%', '%', start_time, end_time, campaign) |
| | 70 | + |
| | 71 | + elif query_name == 'report_latest_campaign': |
| | 72 | + start_time = args[0] |
| | 73 | + sql_stmnt = sql_stmnt % (start_time) |
| 69 | 74 | |
| 70 | 75 | else: |
| 71 | 76 | print 'no such table\n' |
| — | — | @@ -98,6 +103,8 @@ |
| 99 | 104 | return 0 |
| 100 | 105 | elif query_name == 'report_bannerLP_metrics': |
| 101 | 106 | return 0 |
| | 107 | + elif query_name == 'report_latest_campaign': |
| | 108 | + return 1 |
| 102 | 109 | else: |
| 103 | 110 | 'no such table' |
| 104 | 111 | |
| — | — | @@ -110,6 +117,8 @@ |
| 111 | 118 | return 3 |
| 112 | 119 | elif query_name == 'report_bannerLP_metrics': |
| 113 | 120 | return 1 |
| | 121 | + elif query_name == 'report_latest_campaign': |
| | 122 | + return 0 |
| 114 | 123 | else: |
| 115 | 124 | 'no such table' |
| 116 | 125 | |
| 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() |
| — | — | @@ -373,7 +373,7 @@ |
| 374 | 374 | |
| 375 | 375 | class BannerLPReporting(FundraiserReporting): |
| 376 | 376 | |
| 377 | | - def run_query(self,start_time, end_time, query_name, metric_name): |
| | 377 | + def run_query(self,start_time, end_time, campaign, query_name, metric_name): |
| 378 | 378 | |
| 379 | 379 | self.init_db() |
| 380 | 380 | |
| — | — | @@ -387,12 +387,12 @@ |
| 388 | 388 | sql_stmnt = mh.read_sql(filename) |
| 389 | 389 | |
| 390 | 390 | query_name = 'report_bannerLP_metrics' # rename query to work with query store |
| 391 | | - sql_stmnt = query_obj.format_query(query_name, sql_stmnt, [start_time, end_time]) |
| | 391 | + sql_stmnt = query_obj.format_query(query_name, sql_stmnt, [start_time, end_time, campaign]) |
| 392 | 392 | |
| 393 | 393 | key_index = query_obj.get_banner_index(query_name) |
| 394 | 394 | time_index = query_obj.get_time_index(query_name) |
| 395 | 395 | metric_index = query_obj.get_metric_index(query_name, metric_name) |
| 396 | | - |
| | 396 | + |
| 397 | 397 | # Composes the data for each banner |
| 398 | 398 | try: |
| 399 | 399 | err_msg = sql_stmnt |
| — | — | @@ -418,7 +418,6 @@ |
| 419 | 419 | self.db.rollback() |
| 420 | 420 | sys.exit("Database Interface Exception:\n" + err_msg) |
| 421 | 421 | |
| 422 | | - |
| 423 | 422 | """ Convert Times to Integers """ |
| 424 | 423 | # Find the earliest date |
| 425 | 424 | max_i = 0 |
| — | — | @@ -483,16 +482,16 @@ |
| 484 | 483 | |
| 485 | 484 | """ |
| 486 | 485 | |
| 487 | | - type = 'LP' || 'BAN' |
| | 486 | + type = 'LP' || 'BAN' || 'BAN-TEST' || 'LP-TEST' |
| 488 | 487 | |
| 489 | 488 | """ |
| 490 | 489 | def run(self, type, metric_name): |
| 491 | 490 | |
| 492 | 491 | # Current date & time |
| 493 | 492 | now = datetime.datetime.now() |
| 494 | | - #UTC = 8 |
| 495 | | - #delta = datetime.timedelta(hours=UTC) |
| 496 | | - #now = now + delta |
| | 493 | + UTC = 8 |
| | 494 | + delta = datetime.timedelta(hours=UTC) |
| | 495 | + now = now + delta |
| 497 | 496 | |
| 498 | 497 | # ESTABLISH THE START TIME TO PULL ANALYTICS |
| 499 | 498 | hours_back = 24 |
| — | — | @@ -500,52 +499,89 @@ |
| 501 | 500 | |
| 502 | 501 | start_time = times[0] |
| 503 | 502 | end_time = times[1] |
| 504 | | - |
| | 503 | + |
| 505 | 504 | print '\nGenerating ' + type +' for ' + str(hours_back) + ' hours back. The start and end times are: ' + start_time + ' - ' + end_time +' ... \n' |
| 506 | 505 | |
| 507 | 506 | if type == 'LP': |
| 508 | | - query_name = 'report_LP_metrics' |
| | 507 | + query_name = 'report_LP_metrics' |
| | 508 | + campaign = '[0-9](JA|SA|EA)[0-9]' |
| | 509 | + title = query_name + '_' + metric_name |
| | 510 | + fname = title + '.png' |
| 509 | 511 | elif type == 'BAN': |
| 510 | 512 | query_name = 'report_banner_metrics' |
| | 513 | + campaign = '[0-9](JA|SA|EA)[0-9]' |
| | 514 | + title = query_name + '_' + metric_name |
| | 515 | + fname = title + '.png' |
| 511 | 516 | elif type == 'BAN-TEST': |
| 512 | | - '[0-9](JA|SA|EA)[0-9]' |
| | 517 | + r = self.get_latest_campaign() |
| | 518 | + query_name = 'report_banner_metrics' |
| | 519 | + campaign = r[0] |
| | 520 | + start_time = r[1] |
| | 521 | + title = query_name + '_' + metric_name + '_' + campaign |
| | 522 | + fname = title + '.png' |
| 513 | 523 | elif type == 'LP-TEST': |
| 514 | | - '[0-9](JA|SA|EA)[0-9]' |
| | 524 | + r = self.get_latest_campaign() |
| | 525 | + query_name = 'report_LP_metrics' |
| | 526 | + campaign = r[0] |
| | 527 | + start_time = r[1] |
| | 528 | + title = query_name + '_' + metric_name + '_' + campaign |
| | 529 | + fname = title + '.png' |
| 515 | 530 | else: |
| 516 | 531 | sys.exit("Invalid type name - must be 'LP' or 'BAN'.") |
| 517 | 532 | |
| 518 | | - return_val = self.run_query(start_time, end_time, query_name, metric_name) |
| | 533 | + |
| | 534 | + return_val = self.run_query(start_time, end_time, campaign, query_name, metric_name) |
| 519 | 535 | metrics = return_val[0] |
| 520 | 536 | times = return_val[1] |
| 521 | 537 | |
| 522 | | - title = metric_name + ': ' + start_time + ' -- ' + end_time |
| | 538 | + # title = metric_name + ': ' + start_time + ' -- ' + end_time |
| 523 | 539 | xlabel = 'Time - Hours' |
| 524 | 540 | ylabel = metric_name |
| 525 | 541 | subplot_index = 111 |
| 526 | 542 | |
| 527 | | - ranges = [-hours_back, -1] |
| | 543 | + min_time = 99 |
| | 544 | + for key in times.keys(): |
| | 545 | + min_elem = min(times[key]) |
| | 546 | + if min_elem < min_time: |
| | 547 | + min_time = min_elem |
| 528 | 548 | |
| 529 | | - self.gen_plot(metrics, times, title, xlabel, ylabel, ranges, subplot_index, query_name + '_' + metric_name + '.png') |
| | 549 | + ranges = [min_time, -1] |
| 530 | 550 | |
| | 551 | + self.gen_plot(metrics, times, title, xlabel, ylabel, ranges, subplot_index, fname) |
| | 552 | + |
| 531 | 553 | return [metrics, times] |
| 532 | 554 | |
| 533 | 555 | |
| 534 | | -""" |
| 535 | | - |
| 536 | | -CLASS :: ^TestReporting^ |
| 537 | | - |
| 538 | | -This subclass handles reporting on specific tests as defined by a utm campaign. |
| 539 | | - |
| 540 | | -""" |
| 541 | | - |
| 542 | | -class TestReporting(FundraiserReporting): |
| 543 | | - |
| 544 | | - def run_query(self,start_time, end_time, query_name, metric_name): |
| 545 | | - 'report_latest_campaign' |
| 546 | | - # select the first row |
| | 556 | + def get_latest_campaign(self): |
| 547 | 557 | |
| | 558 | + query_name = 'report_latest_campaign' |
| | 559 | + self.init_db() |
| 548 | 560 | |
| 549 | | - def gen_plot(self,counts, times, title, xlabel, ylabel, ranges, subplot_index, fname): |
| | 561 | + # Look at campaigns over the past 24 hours |
| | 562 | + now = datetime.datetime.now() |
| | 563 | + hours_back = 24 |
| | 564 | + times = self.gen_date_strings_hr(now, hours_back) |
| 550 | 565 | |
| 551 | | - def run(self, type, metric_name): |
| 552 | | - |
| \ No newline at end of file |
| | 566 | + query_obj = qs.query_store() |
| | 567 | + sql_stmnt = mh.read_sql('./sql/report_latest_campaign.sql') |
| | 568 | + sql_stmnt = query_obj.format_query(query_name, sql_stmnt, [times[0]]) |
| | 569 | + |
| | 570 | + campaign_index = query_obj.get_campaign_index(query_name) |
| | 571 | + time_index = query_obj.get_time_index(query_name) |
| | 572 | + |
| | 573 | + try: |
| | 574 | + err_msg = sql_stmnt |
| | 575 | + self.cur.execute(sql_stmnt) |
| | 576 | + |
| | 577 | + row = self.cur.fetchone() |
| | 578 | + except: |
| | 579 | + self.db.rollback() |
| | 580 | + sys.exit("Database Interface Exception:\n" + err_msg) |
| | 581 | + |
| | 582 | + campaign = row[campaign_index] |
| | 583 | + timestamp = row[time_index] |
| | 584 | + |
| | 585 | + self.close_db() |
| | 586 | + |
| | 587 | + return [campaign, timestamp] |
| | 588 | + |
| \ No newline at end of file |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_latest_campaign.sql |
| — | — | @@ -0,0 +1,14 @@ |
| | 2 | + |
| | 3 | +-- sorts utm_campaigns by most recently added |
| | 4 | + |
| | 5 | +select utm_campaign, min(ts) as min_ts, count(*) as hits |
| | 6 | + |
| | 7 | +from |
| | 8 | +drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| | 9 | +ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| | 10 | + |
| | 11 | +where ts > '%s' and utm_campaign REGEXP '[0-9](JA|SA|EA)[0-9]' |
| | 12 | +group by 1 |
| | 13 | +having hits > 100 |
| | 14 | +order by 2 desc |
| | 15 | +; |
| \ No newline at end of file |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics.sql |
| — | — | @@ -53,7 +53,7 @@ |
| 54 | 54 | drupal.contribution_tracking LEFT JOIN civicrm.public_reporting |
| 55 | 55 | ON (contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id) |
| 56 | 56 | where ts >= '%s' and ts < '%s' |
| 57 | | -and utm_campaign REGEXP '[0-9](JA|SA|EA)[0-9]' |
| | 57 | +and utm_campaign REGEXP '%s' |
| 58 | 58 | group by 1,2) as ecomm |
| 59 | 59 | |
| 60 | 60 | on ecomm.landing_page = lp.landing_page and ecomm.hr = lp.hr |
| Index: trunk/fundraiser-statistics/reporting/landing_page_test.sql |
| — | — | @@ -8,7 +8,7 @@ |
| 9 | 9 | total_clicks as clicks, |
| 10 | 10 | donations as donations, |
| 11 | 11 | amount as amount, |
| 12 | | -donations / total_clicks as conversion_rate, |
| | 12 | +donations / total_clicks as completion_rate, |
| 13 | 13 | donations / views as don_per_view, |
| 14 | 14 | amount / views as amt_per_view, |
| 15 | 15 | modified_amount / views as amt_per_view_reduced, |
| Index: trunk/fundraiser-statistics/reporting/landing_page_test_by_hour.sql |
| — | — | @@ -8,7 +8,7 @@ |
| 9 | 9 | total_clicks as clicks, |
| 10 | 10 | donations as donations, |
| 11 | 11 | amount as amount, |
| 12 | | -donations / total_clicks as conversion_rate, |
| | 12 | +donations / total_clicks as completion_rate, |
| 13 | 13 | donations / views as don_per_view, |
| 14 | 14 | amount / views as amt_per_view, |
| 15 | 15 | modified_amount / views as amt_per_view_reduced, |