http://www.mediawiki.org/wiki/Special:Code/MediaWiki/84721
Revision: 84721
Author: rfaulk
Date: 2011-03-25 00:44:54 +0000 (Fri, 25 Mar 2011)
Log Message:
-----------
Tweaked the interval reporting source to better visualize the fundraiser data.
Coverted to step plots also, added some normalizing of time intervals.
Modified Paths:
--------------
trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py
trunk/fundraiser-statistics/fundraiser-scripts/query_store.py
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
Modified: trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py
===================================================================
--- trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py
2011-03-25 00:44:20 UTC (rev 84720)
+++ trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py
2011-03-25 00:44:54 UTC (rev 84721)
@@ -44,7 +44,6 @@
time_lists, isList = self.timestamps_to_dict(time_lists)
-
""" Depending on args set the start date """
if count_back:
start_date_obj =
self.find_latest_date_in_list(time_lists)
@@ -308,11 +307,11 @@
# Cast the start and end time strings in the proper format
if format == 1:
- if resoution == 1:
+ if resolution == 1:
timestamp = str(time_obj.year) + month + day +
hour + '0000'
- elif resoution == 2:
+ elif resolution == 2:
timestamp = str(time_obj.year) + month + day +
hour + minute + '00'
- elif resoution == 3:
+ elif resolution == 3:
timestamp = str(time_obj.year) + month + day +
hour + minute + second
elif format == 2:
@@ -393,6 +392,30 @@
return [new_times, new_metrics]
+ """
+
+ Converts from one timestamp format to another timestamp format
+
+ format 1 - 20080101000606
+ format 2 - 2008-01-01 00:06:06
+
+ """
+ def timestamp_convert_format(self, ts, format_from, format_to):
+
+ if format_from == 1:
+
+ if format_to == 2:
+ new_timestamp = ts[0:4] + '-' + ts[4:6] + '-' +
ts[6:8] + ' ' + ts[8:10] + ':' + ts[10:12] + ':' + ts[12:14]
+
+ elif format_from == 2:
+ if format_to == 1:
+ new_timestamp = ts[0:4] + ts[5:7] + ts[8:10] +
ts[11:13] + ts[14:16] + ts[15:17]
+
+ return new_timestamp
+
+
+
+
"""
CLASS :: ^FundraiserReporting^
@@ -1189,24 +1212,37 @@
times = mh.AutoVivification()
times_norm = mh.AutoVivification()
- # Load the SQL File & Format
+ """ Load the SQL File & Format """
filename = './sql/' + query_name + '.sql'
sql_stmnt = mh.read_sql(filename)
sql_stmnt = query_obj.format_query(query_name, sql_stmnt,
[start_time, end_time, campaign, interval])
- #print sql_stmnt
+ # print sql_stmnt
- # Get Indexes into Query
+ """ Get Indexes into Query """
key_index = query_obj.get_banner_index(query_name)
metric_index = query_obj.get_metric_index(query_name,
metric_name)
time_index = query_obj.get_time_index(query_name)
- # Composes the data for each banner
+ """ Compose datetime objects to represent the first and last
intervals """
+ start_time_obj = self.timestamp_to_obj(start_time, 1)
+ start_time_obj =
start_time_obj.replace(minute=int(math.floor(start_time_obj.minute / interval)
* interval))
+ start_time_obj_str = self.timestamp_from_obj(start_time_obj, 1,
2)
+
+ end_time_obj = self.timestamp_to_obj(end_time, 1)
+ end_time_obj = end_time_obj + datetime.timedelta(seconds=-1)
+ end_time_obj =
end_time_obj.replace(minute=int(math.floor(end_time_obj.minute / interval) *
interval))
+ end_time_obj_str = self.timestamp_from_obj(end_time_obj,
1, 2)
+
+
+ """ Compose the data for each separate donor pipeline artifact
"""
try:
err_msg = sql_stmnt
self.cur.execute(sql_stmnt)
results = self.cur.fetchall()
+ final_time = dict()
# stores the last timestamp seen
+ interval_obj = datetime.timedelta(minutes=interval)
# timedelta object used to shift times by _interval_ minutes
for row in results:
@@ -1216,26 +1252,46 @@
""" For each new dictionary index by key name
start a new list if its not already there """
try:
metrics[key_name].append(row[metric_index])
- times[key_name].append(time_obj)
+ times[key_name].append(time_obj +
interval_obj)
+ final_time[key_name] = row[time_index]
except:
metrics[key_name] = list()
times[key_name] = list()
-
metrics[key_name].append(row[metric_index])
- times[key_name].append(time_obj)
-
+ """ If the first element is not the
start time add it
+ this will be the case if there
is no data for the first interval
+ NOTE: two datapoints are
added at the beginning to define the first interval """
+ if start_time_obj_str !=
row[time_index]:
+
times[key_name].append(start_time_obj)
+ metrics[key_name].append(0.0)
+
+
times[key_name].append(start_time_obj + interval_obj)
+ metrics[key_name].append(0.0)
+ else:
+
metrics[key_name].append(row[metric_index])
+ times[key_name].append(time_obj)
+
+
metrics[key_name].append(row[metric_index])
+ times[key_name].append(time_obj
+ interval_obj)
+
+
except:
self.db.rollback()
sys.exit("Database Interface Exception:\n" + err_msg)
- """ Convert Times to Integers that indicate relative times AND
normalize the intervals in case any are missing """
+
+ """ Ensure that the last time in the list is the endtime less
the interval """
+
for key in times.keys():
- times_norm[key] = self.normalize_timestamps(times[key],
False, 2)
- times_norm[key], metrics[key] =
self.normalize_intervals(times_norm[key], metrics[key], interval)
+ if final_time[key_name] != end_time_obj_str:
+ times[key].append(end_time_obj)
+ metrics[key].append(0.0)
+
+ # print times
self.close_db()
- return [metrics, times_norm]
+ return [metrics, times]
def gen_plot(self, metrics, times, title, xlabel, ylabel, ranges,
subplot_index, fname):
@@ -1247,7 +1303,7 @@
count = 0
for key in metrics.keys():
- pylab.plot(times[key], metrics[key], line_types[count])
+ pylab.step(times[key], metrics[key], line_types[count])
count = count + 1
pylab.grid()
@@ -1278,17 +1334,22 @@
counts = return_val[0]
times = return_val[1]
+ """ Convert Times to Integers that indicate relative times AND
normalize the intervals in case any are missing """
+ for key in times.keys():
+ times[key] = self.normalize_timestamps(times[key],
False, 2)
+ times[key], counts[key] =
self.normalize_intervals(times[key], counts[key], interval)
+
# Normalize times
min_time = min(times)
ranges = [min_time, 0]
- xlabel = 'Hours'
+ xlabel = 'MINUTES'
subplot_index = 111
- fname = query_name + '.png'
+ fname = campaign + ' ' + query_name + ' ' + metric_name + '.png'
- title = query_obj.get_plot_title(query_name)
- title = title + ' -- ' + start_time + ' - ' + end_time
- ylabel = query_obj.get_plot_ylabel(query_name)
+ metric_full_name = query_obj.get_metric_full_name(metric_name)
+ title = campaign + ': ' + metric_full_name + ' -- ' +
start_time + ' - ' + end_time
+ ylabel = metric_full_name
# Convert counts to float (from Decimal) to prevent exception
when bar plotting
# Bbox::update_numerix_xy expected numerix array
@@ -1312,8 +1373,6 @@
if list_max > times_max:
times_max = list_max
- print times_max
- print metrics_max
ranges = list()
ranges.append(0)
ranges.append(times_max * 1.1)
Modified: trunk/fundraiser-statistics/fundraiser-scripts/query_store.py
===================================================================
--- trunk/fundraiser-statistics/fundraiser-scripts/query_store.py
2011-03-25 00:44:20 UTC (rev 84720)
+++ trunk/fundraiser-statistics/fundraiser-scripts/query_store.py
2011-03-25 00:44:54 UTC (rev 84721)
@@ -348,14 +348,26 @@
else:
return -1
elif query_name == 'report_LP_metrics_minutely':
- if metric_name == 'don_per_view':
+ if metric_name == 'views':
+ return 2
+ elif metric_name == 'donations':
+ return 4
+ elif metric_name == 'amount50':
+ return 5
+ elif metric_name == 'don_per_view':
return 7
elif metric_name == 'amt50_per_view':
return 9
else:
return -1
elif query_name == 'report_banner_metrics_minutely':
- if metric_name == 'don_per_imp':
+ if metric_name == 'imp':
+ return 2
+ elif metric_name == 'donations':
+ return 5
+ elif metric_name == 'amount50':
+ return 7
+ elif metric_name == 'don_per_imp':
return 10
elif metric_name == 'amt50_per_imp':
return 12
@@ -379,4 +391,24 @@
elif query_name == 'report_lp_views_by_hour':
return 'VIEWS'
else:
+ return'no such table'
+
+ def get_metric_full_name(self, metric_name):
+ if metric_name == 'imp':
+ return 'IMPRESSIONS'
+ elif metric_name == 'view':
+ return 'VIEWS'
+ elif metric_name == 'don_per_imp':
+ return 'DONATIONS PER IMPRESSION'
+ elif metric_name == 'don_per_view':
+ return 'DONATIONS PER VIEW'
+ elif metric_name == 'amt50_per_imp':
+ return 'AMOUNT50 PER IMPRESSION'
+ elif metric_name == 'amt50_per_view':
+ return 'AMOUNT50 PER VIEW'
+ elif metric_name == 'amount50':
+ return 'AMOUNT50'
+ elif metric_name == 'donations':
+ return 'DONATIONS'
+ else:
return'no such table'
\ No newline at end of file
Modified:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
2011-03-25 00:44:20 UTC (rev 84720)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_LP_metrics_minutely.sql
2011-03-25 00:44:54 UTC (rev 84721)
@@ -1,12 +1,12 @@
select
-if(lp.dt_min < 10, concat(lp.dt_hr, ' 0', lp.dt_min), concat(lp.dt_hr, ' ',
lp.dt_min)) as day_hr,
+if(lp.dt_min < 10, concat(lp.dt_hr, '0', lp.dt_min,'00'), concat(lp.dt_hr,
lp.dt_min,'00')) as day_hr,
lp.landing_page,
views,
total_clicks,
donations,
-amount,
+amount50,
donations / total_clicks as completion_rate,
donations / views as don_per_view,
amount / views as amt_per_view,
@@ -15,7 +15,7 @@
from
(select
-DATE_FORMAT(request_time,'%sY-%sm-%sd %sH') as dt_hr,
+DATE_FORMAT(request_time,'%sY%sm%sd%sH') as dt_hr,
FLOOR(MINUTE(request_time) / %s) * %s as dt_min,
landing_page,
count(*) as views
@@ -29,20 +29,19 @@
join
(select
-DATE_FORMAT(ts,'%sY-%sm-%sd %sH') as hr,
-FLOOR(MINUTE(ts) / %s) * %s as dt_min,
-SUBSTRING_index(substring_index(utm_source, '.', 2),'.',-1) as landing_page,
+DATE_FORMAT(receive_date,'%sY%sm%sd%sH') as hr,
+FLOOR(MINUTE(receive_date) / %s) * %s as dt_min,
+SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner,
count(*) as total_clicks,
-sum(not isnull(contribution_tracking.contribution_id)) as donations,
-sum(converted_amount) AS amount,
-sum(if(converted_amount > 50, 50, converted_amount)) as amount50
-
+sum(not isnull(drupal.contribution_tracking.contribution_id)) as donations,
+sum(total_amount) as amount,
+sum(if(total_amount > 50, 50, total_amount)) as amount50
from
-drupal.contribution_tracking LEFT JOIN civicrm.public_reporting
-ON (contribution_tracking.contribution_id =
civicrm.public_reporting.contribution_id)
-where ts >= '%s' and ts < '%s'
+drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution
+ON (drupal.contribution_tracking.contribution_id =
civicrm.civicrm_contribution.id)
+where receive_date >= '%s' and receive_date < '%s'
and utm_campaign REGEXP '%s'
-group by 1,2) as ecomm
+group by 1,2,3) as ecomm
on ecomm.landing_page = lp.landing_page and ecomm.hr = lp.dt_hr and
ecomm.dt_min = lp.dt_min
Modified:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
2011-03-25 00:44:20 UTC (rev 84720)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_banner_metrics_minutely.sql
2011-03-25 00:44:54 UTC (rev 84721)
@@ -45,17 +45,17 @@
join
(select
-DATE_FORMAT(ts,'%sY%sm%sd%sH') as hr,
-FLOOR(MINUTE(ts) / %s) * %s as dt_min,
+DATE_FORMAT(receive_date,'%sY%sm%sd%sH') as hr,
+FLOOR(MINUTE(receive_date) / %s) * %s as dt_min,
SUBSTRING_index(substring_index(utm_source, '.', 2),'.',1) as banner,
count(*) as total_clicks,
-sum(not isnull(contribution_tracking.contribution_id)) as donations,
-sum(converted_amount) as amount,
-sum(if(converted_amount > 50, 50, converted_amount)) as amount50
+sum(not isnull(drupal.contribution_tracking.contribution_id)) as donations,
+sum(total_amount) as amount,
+sum(if(total_amount > 50, 50, total_amount)) as amount50
from
-drupal.contribution_tracking LEFT JOIN civicrm.public_reporting
-ON (contribution_tracking.contribution_id =
civicrm.public_reporting.contribution_id)
-where ts >= '%s' and ts < '%s'
+drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution
+ON (drupal.contribution_tracking.contribution_id =
civicrm.civicrm_contribution.id)
+where receive_date >= '%s' and receive_date < '%s'
and utm_campaign REGEXP '%s'
group by 1,2,3) as ecomm
_______________________________________________
MediaWiki-CVS mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-cvs
