Awight has uploaded a new change for review.
https://gerrit.wikimedia.org/r/300090
Change subject: Snapshot of pcoombe's campaign statistics tools
......................................................................
Snapshot of pcoombe's campaign statistics tools
Note that this is not currently the authoritative source. You might find
more recent code in staging:/srv/br/, in which case we should re-sync.
Change-Id: I4de528cf5b7f6cbe391ec9a427b1a3b54a61799a
---
A stats/fr_util.py
A stats/methods.py
2 files changed, 225 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools
refs/changes/90/300090/1
diff --git a/stats/fr_util.py b/stats/fr_util.py
new file mode 100644
index 0000000..939732d
--- /dev/null
+++ b/stats/fr_util.py
@@ -0,0 +1,58 @@
+"""Fundraising utility belt
+"""
+
+import MySQLdb
+
+FORMAT_CHOICES = ['tsv', 'csv', 'pretty', 'mediawiki']
+
+def get_db_cursor(type='default'):
+ """Get a cursor to the database
+
+ Type can be:
+ - 'default'
+ - 'dict': returns rows as a dictionary
+
+ User must have .my.cnf file configured in their home directory
+ """
+ db = MySQLdb.connect(host='localhost',
+ unix_socket='/tmp/mysql.sock',
+ read_default_file="~/.my.cnf")
+ if type == 'dict':
+ cursor = db.cursor(MySQLdb.cursors.DictCursor)
+ else:
+ cursor = db.cursor()
+
+ return cursor
+
+
+def print_table(columns, rows, format):
+ # Put results in a nice table for output
+
+ if format == 'tsv':
+ print '\t'.join(columns)
+ for row in rows:
+ print '\t'.join(row)
+
+ elif format == 'csv':
+ print ', '.join(columns)
+ for row in rows:
+ print ', '.join(row)
+
+ elif format == 'pretty':
+ from prettytable import PrettyTable
+ x = PrettyTable(columns)
+ x.align = 'r'
+ for row in rows:
+ x.add_row(row)
+ print x
+
+ elif format == 'mediawiki':
+ print '{| class="wikitable sortable"'
+ print '|+ Caption'
+ print '|-'
+ print '! ' + ' !! '.join( columns )
+ for row in rows:
+ print '|-'
+ print '! scope="row" | ' + row[0]
+ print '| ' + ' || '.join( row[1:] )
+ print '|}'
diff --git a/stats/methods.py b/stats/methods.py
new file mode 100755
index 0000000..9a87f8b
--- /dev/null
+++ b/stats/methods.py
@@ -0,0 +1,167 @@
+#! /usr/bin/env python
+# -*- coding: utf-8 -*-
+
+from datetime import datetime
+import dateutil.parser
+import dateutil.relativedelta
+import MySQLdb, sys
+import fr_util
+
+TSFORMAT = '%Y%m%d%H%M%S'
+DATEFORMAT = '%Y-%m-%d %H:%M:%S'
+
+
+def get_options():
+ from argparse import ArgumentParser
+ parser = ArgumentParser(description='Get payment method results')
+
+ # We'll run the date parser on these later
+ parser.add_argument('-s', '--start', dest='start',
+ help='Start time (UTC). If not specified, defaults to one month ago.')
+ parser.add_argument('-e', '--end', dest='end',
+ help='End time (UTC). If not specified, defaults to now.')
+
+ parser.add_argument('--source', dest='source', default='',
+ help='utm_source substring e.g. B1516_0902_en6C_ipd. Can be a regular
expression.')
+ parser.add_argument('--campaign', dest='campaign', default='',
+ help='utm_campaign substring e.g. C1516_en6C_dsk_FR. Can be a regular
expression.')
+
+ parser.add_argument('--country', dest='country', default='',
+ help='Filter to country ISO code e.g. US')
+ parser.add_argument('--language', dest='language', default='',
+ help='Filter to language ISO code e.g. en')
+
+ parser.add_argument('--format', dest='format', default='tsv',
+ choices=fr_util.FORMAT_CHOICES,
+ help='Output format. For example "mediawiki" gives a table suitable
for posting on wiki.')
+
+ parser.add_argument('--sql', action='store_true',
+ help='Show SQL used (for debugging)')
+
+ if len(sys.argv) == 1:
+ # No arguments, show instructions
+ parser.print_help()
+ sys.exit(1)
+
+ args = vars(parser.parse_args())
+ return args
+
+
+def build_query():
+ where = "ts BETWEEN %(start_ts)s AND %(end_ts)s"
+ if args['source']:
+ where += " AND utm_source REGEXP %(source)s"
+ if args['campaign']:
+ where += " AND utm_campaign REGEXP %(campaign)s"
+ if args['country']:
+ where += " AND country=%(country)s"
+ if args['language']:
+ where += " AND language=%(language)s"
+
+ query = """
+SELECT
+ SUBSTRING_INDEX(ct.utm_source, '.', -1) AS method,
+ SUM(cc.id IS NOT NULL) AS donations,
+ SUM(cc.id IS NOT NULL) / total.donations AS donations_pct,
+ COUNT(ct.id) AS clicks,
+ COUNT(ct.id) / total.clicks AS clicks_pct,
+ SUM(cc.id IS NOT NULL) / COUNT(ct.id) AS conversion,
+ SUM(cc.total_amount) AS amount,
+ SUM(cc.total_amount) / total.amount AS amount_pct,
+ SUM(cc.total_amount) / SUM(cc.id IS NOT NULL) AS avg
+FROM
+ drupal.contribution_tracking ct
+ LEFT JOIN civicrm.civicrm_contribution cc on ct.contribution_id = cc.id,
+ (
+ SELECT
+ SUM(cc.id IS NOT NULL) AS donations,
+ COUNT(ct.id) AS clicks,
+ SUM(cc.total_amount) AS amount
+ FROM
+ drupal.contribution_tracking ct
+ LEFT JOIN civicrm.civicrm_contribution cc on ct.contribution_id =
cc.id
+ WHERE """ + where + """
+ ) AS total
+WHERE
+ """ + where + """
+GROUP BY method;
+ """
+ return query
+
+
+def get_data():
+ # Gets the raw results from the database
+ cursor.execute(query, args)
+ data = cursor.fetchall()
+ return data
+
+
+def format_results(data):
+ # Format results nicely and bundle into lists.
+ num = '{:,}' # number with thousands separator
+ usd = '${:,.2f}'
+ pct = '{:.2%}'
+ columns = ['method', 'donations', 'donations%', 'clicks', 'clicks%',
'amount', 'amount%', 'avg', 'conversion']
+ results = []
+ for i in data:
+
+ # Change any None to 0 so formatter doesn't choke
+ for x in i:
+ if i[x] == None:
+ i[x] = 0
+
+ row = [
+ str( i['method'] ),
+ num.format( i['donations'] ),
+ pct.format( i['donations_pct'] ),
+ num.format( i['clicks'] ),
+ pct.format( i['clicks_pct'] ),
+ usd.format( i['amount'] ),
+ pct.format( i['amount_pct'] ),
+ usd.format( i['avg'] ),
+ pct.format( i['conversion'] )
+ ]
+ results.append(row)
+ return columns, results
+
+
+if __name__ == '__main__':
+ args = get_options()
+
+ if args['start']:
+ args['start'] = dateutil.parser.parse(args['start'])
+ else:
+ # default to 1 month ago
+ args['start'] = (datetime.now() +
dateutil.relativedelta.relativedelta(months = -1))
+
+ if args['end']:
+ args['end'] = dateutil.parser.parse(args['end'])
+ else:
+ # default to now
+ args['end'] = datetime.now()
+
+ # Timestamp format for queries
+ args['start_ts'] = args['start'].strftime(TSFORMAT)
+ args['end_ts'] = args['end'].strftime(TSFORMAT)
+
+ cursor = fr_util.get_db_cursor(type='dict')
+ query = build_query()
+ data = get_data()
+ if args['sql']:
+ print cursor._last_executed
+
+ print "From: " + args['start'].strftime(DATEFORMAT)
+ print "To: " + args['end'].strftime(DATEFORMAT)
+
+ print """
+Campaign filter: {campaign}
+utm_source filter: {source}
+Country filter: {country}
+Language filter: {language}
+ """.format(**args)
+
+ columns, results = format_results(data)
+
+ fr_util.print_table(columns, results, args['format'])
+ print
+
--
To view, visit https://gerrit.wikimedia.org/r/300090
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I4de528cf5b7f6cbe391ec9a427b1a3b54a61799a
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Awight <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
