def get_donations_by_banner_count(start, stop):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
query = """
SELECT
cast(ct.utm_key as int) as impressions_seen,
CONCAT_WS(' ', banner, utm_campaign, substr(DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d'), 1, 10)) as name,
COUNT(*) as n,
SUM(co.total_amount) as amount
FROM civicrm.civicrm_contribution co, drupal.contribution_tracking ct, drupal.contribution_source cs
WHERE ct.id = cs.contribution_tracking_id
AND co.id = ct.contribution_id
AND ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_key is not NULL
group by ct.utm_key, CONCAT_WS(' ', banner, utm_campaign, substr(DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d'), 1, 10));
"""
params = get_time_limits(start, stop)
d = query_lutetium(query, params)
d['impressions_seen'] = d['impressions_seen'].fillna(-1)
d['impressions_seen'] = d['impressions_seen'].astype(int)
d.index = d.impressions_seen
d.drop('impressions_seen', axis=1, inplace=True)
d['amount'] = d['amount'].fillna(0.0)
d['amount'] = d['amount'].astype(float)
return d.sort()
def get_donations(start, stop, campaign):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
params = get_time_limits(start, stop)
params['campaign'] = campaign
query = """
SELECT
DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d %%H') as timestamp,
CONCAT_WS(' ', banner, utm_campaign) as name,
co.total_amount as amount,
ct.country as country
FROM civicrm.civicrm_contribution co, drupal.contribution_tracking ct, drupal.contribution_source cs
WHERE ct.id = cs.contribution_tracking_id
AND co.id = ct.contribution_id
AND ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_medium = 'sitenotice'
AND utm_campaign = %(campaign)s
"""
d = query_lutetium(query, params)
d.index = d['timestamp'].map(lambda t: pd.to_datetime(str(t)))
del d['timestamp']
d['amount'] = d['amount'].fillna(0.0)
d['amount'] = d['amount'].astype(float)
return d.sort()
def get_clicks(start, stop, campaign='.*'):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
params = get_time_limits(start, stop)
params['campaign'] = campaign
query = """
SELECT
DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d %%H') as timestamp, CONCAT_WS(' ', banner, utm_campaign) as name,
COUNT(*) as n,
ct.country as country
FROM drupal.contribution_tracking ct, drupal.contribution_source cs
WHERE ct.id = cs.contribution_tracking_id
AND ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_medium = 'sitenotice'
AND utm_campaign REGEXP %(campaign)s
GROUP BY DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d %%H'), CONCAT_WS(' ', banner, utm_campaign)
"""
print query % params
d = query_lutetium(query, params)
print d.head()
d.index = d['timestamp'].map(lambda t: pd.to_datetime(str(t)))
del d['timestamp']
return d.sort()
def get_impressions(start, stop, country_id=None):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
params = get_time_limits(start, stop)
params['country_id'] = country_id
query = """
SELECT
DATE_FORMAT(CAST(timestamp as datetime), '%%Y-%%m-%%d %%H') as dt, CONCAT_WS(' ', banner, campaign) as name, SUM(count) as n
FROM pgehres.bannerimpressions
WHERE timestamp BETWEEN %(start)s AND %(stop)s
"""
if country_id:
query += " AND country_id = %(country_id)s"
query += " GROUP BY DATE_FORMAT(CAST(timestamp as datetime), '%%Y-%%m-%%d %%H'), CONCAT_WS(' ', banner, campaign)"
d = query_lutetium(query, params)
d.index = d['dt'].map(lambda t: pd.to_datetime(str(t)))
del d['dt']
d['n'] = d['n'].astype(int)
return d.sort()
def get_clicks(start, stop, campaign = '.*'):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
params = get_time_limits(start, stop)
params['campaign'] = campaign
query = """
SELECT
DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d %%H') as timestamp, CONCAT_WS(' ', banner, utm_campaign) as name,
COUNT(*) as n,
ct.country as country
FROM drupal.contribution_tracking ct, drupal.contribution_source cs
WHERE ct.id = cs.contribution_tracking_id
AND ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_medium = 'sitenotice'
AND utm_campaign REGEXP %(campaign)s
GROUP BY DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d %%H'), CONCAT_WS(' ', banner, utm_campaign)
"""
d = query_lutetium(query, params)
d.index = d['timestamp'].map(lambda t: pd.to_datetime(str(t)))
del d['timestamp']
return d.sort()
def get_impressions(start, stop, country_id = None):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
params = get_time_limits(start, stop)
params['country_id'] = country_id
query = """
SELECT
DATE_FORMAT(CAST(timestamp as datetime), '%%Y-%%m-%%d %%H') as dt, CONCAT_WS(' ', banner, campaign) as name, SUM(count) as n
FROM pgehres.bannerimpressions
WHERE timestamp BETWEEN %(start)s AND %(stop)s
"""
if country_id:
query += " AND country_id = %(country_id)s"
query += " GROUP BY DATE_FORMAT(CAST(timestamp as datetime), '%%Y-%%m-%%d %%H'), CONCAT_WS(' ', banner, campaign)"
d = query_lutetium(query, params)
d.index = d['dt'].map(lambda t: pd.to_datetime(str(t)))
del d['dt']
d['n'] = d['n'].astype(int)
try:
d['name'] = d['name'].apply(lambda x: x.decode('utf-8'))
except:
pass
return d.sort()
def get_donations(start, stop, campaign):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
params = get_time_limits(start, stop)
params["campaign"] = campaign
query = """
SELECT
DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d %%H') as timestamp,
CONCAT_WS(' ', banner, utm_campaign) as name,
co.total_amount as amount,
ct.country as country
FROM civicrm.civicrm_contribution co, drupal.contribution_tracking ct, drupal.contribution_source cs
WHERE ct.id = cs.contribution_tracking_id
AND co.id = ct.contribution_id
AND ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_medium = 'sitenotice'
AND utm_campaign = %(campaign)s
"""
d = query_lutetium(query, params)
d.index = d["timestamp"].map(lambda t: pd.to_datetime(str(t)))
del d["timestamp"]
d["amount"] = d["amount"].fillna(0.0)
d["amount"] = d["amount"].astype(float)
return d.sort()
def get_donations(start, stop, campaign = '.*'):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
params = get_time_limits(start, stop)
params['campaign'] = campaign
query = """
SELECT
DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d %%H') as timestamp, CONCAT_WS(' ', banner, utm_campaign) as name,
COUNT(*) as n,
SUM(co.total_amount) as amount,
ct.country as country
FROM civicrm.civicrm_contribution co, drupal.contribution_tracking ct, drupal.contribution_source cs
WHERE ct.id = cs.contribution_tracking_id
AND co.id = ct.contribution_id
AND ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_medium = 'sitenotice'
AND utm_campaign REGEXP %(campaign)s
GROUP BY DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d %%H'), CONCAT_WS(' ', banner, utm_campaign)
"""
d = query_lutetium(query, params)
d.index = d['timestamp'].map(lambda t: pd.to_datetime(str(t)))
del d['timestamp']
d['amount'] = d['amount'].fillna(0.0)
d['amount'] = d['amount'].astype(float)
try:
d['name'] = d['name'].apply(lambda x: x.decode('utf-8'))
except:
pass
return d.sort()
def get_clicks(start, stop, campaign):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
params = get_time_limits(start, stop)
params['campaign'] = campaign
query = """
SELECT
DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d %%H') as timestamp,
CONCAT_WS(' ', banner, utm_campaign) as name,
ct.country as country,
cs.payment_method as payment_method,
COUNT(*) as n,
CASE
WHEN contribution_id is NULL THEN 0.0
ELSE 1.0
END as donation
FROM drupal.contribution_tracking ct INNER JOIN drupal.contribution_source cs ON ct.id = cs.contribution_tracking_id
LEFT JOIN civicrm.civicrm_contribution co ON co.id = ct.contribution_id
WHERE ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_medium = 'sitenotice'
AND utm_campaign REGEXP %(campaign)s
GROUP BY timestamp, name, donation, country, payment_method
"""
print query % params
d = query_lutetium(query, params)
d.index = d['timestamp'].map(lambda t: pd.to_datetime(str(t)))
del d['timestamp']
return d.sort()
def get_donations_by_banner_count(start, stop):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
query = """
SELECT
cast(ct.utm_key as int) as impressions_seen,
CONCAT_WS(' ', banner, utm_campaign, substr(DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d'), 1, 10)) as name,
COUNT(*) as n,
SUM(co.total_amount) as amount
FROM civicrm.civicrm_contribution co, drupal.contribution_tracking ct, drupal.contribution_source cs
WHERE ct.id = cs.contribution_tracking_id
AND co.id = ct.contribution_id
AND ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_key is not NULL
group by ct.utm_key, CONCAT_WS(' ', banner, utm_campaign, substr(DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d'), 1, 10));
"""
params = get_time_limits(start, stop)
d = query_lutetium(query, params)
d['impressions_seen'] = d['impressions_seen'].fillna(-1)
d['impressions_seen'] = d['impressions_seen'].astype(int)
d.index = d.impressions_seen
d.drop('impressions_seen', axis=1, inplace=True)
d['amount'] = d['amount'].fillna(0.0)
d['amount'] = d['amount'].astype(float)
return d.sort()
def get_clicks_helper(start,
stop,
campaign_reg='.*',
banner_reg='.*',
aggregation='hour',
select_fields=[],
groupby_fields=[]):
"""
Gets all donation data within the time range start:stop
"""
select_fields = set(select_fields)
select_fields.add('timestamp')
select_fields.add('n')
groupby_fields = set(groupby_fields)
groupby_fields.add('timestamp')
if aggregation == 'none':
select_fields.add('id')
groupby_fields.add('id')
params = get_time_limits(start, stop)
params['campaign_reg'] = campaign_reg
params['banner_reg'] = banner_reg
params['time_format'] = get_time_format(aggregation)
where_clause = """
FROM drupal.contribution_tracking ct LEFT JOIN drupal.contribution_source cs
ON (ct.id = cs.contribution_tracking_id)
LEFT JOIN civicrm.civicrm_contribution co
ON (co.id = ct.contribution_id)
WHERE ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_medium = 'sitenotice'
AND utm_campaign RLIKE %(campaign_reg)s
AND banner RLIKE %(banner_reg)s
"""
select_clause = craft_select(select_fields, clicks_field_mapping)
groupby_clause = craft_groupby(groupby_fields, clicks_field_mapping)
query = select_clause + where_clause + groupby_clause
d = query_lutetium(query, params)
return cast_fields(d)
def get_clicks_helper(start, stop, campaign_reg = '.*', banner_reg = '.*', aggregation = 'hour', select_fields = [], groupby_fields = []):
"""
Gets all donation data within the time range start:stop
"""
select_fields = set(select_fields)
select_fields.add('timestamp')
select_fields.add('n')
groupby_fields = set(groupby_fields)
groupby_fields.add('timestamp')
if aggregation == 'none':
select_fields.add('id')
groupby_fields.add('id')
params = get_time_limits(start, stop)
params['campaign_reg'] = campaign_reg
params['banner_reg'] = banner_reg
params['time_format'] = get_time_format(aggregation)
where_clause = """
FROM drupal.contribution_tracking ct LEFT JOIN drupal.contribution_source cs
ON (ct.id = cs.contribution_tracking_id)
LEFT JOIN civicrm.civicrm_contribution co
ON (co.id = ct.contribution_id)
WHERE ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_medium = 'sitenotice'
AND utm_campaign RLIKE %(campaign_reg)s
AND banner RLIKE %(banner_reg)s
"""
select_clause = craft_select(select_fields, clicks_field_mapping)
groupby_clause = craft_groupby(groupby_fields, clicks_field_mapping)
query = select_clause + where_clause + groupby_clause
d = query_lutetium(query, params)
return cast_fields(d)
def get_impressions(start,
stop,
campaign_reg='.*',
banner_reg='.*',
aggregation='hour'):
"""
Gets all impression data within the time range start:stop
"""
params = get_time_limits(start, stop)
params['campaign_reg'] = campaign_reg
params['banner_reg'] = banner_reg
params['time_format'] = get_time_format(aggregation)
query = """
SELECT
DATE_FORMAT(CAST(timestamp as datetime), %(time_format)s) AS timestamp,
banner,
campaign,
CONCAT_WS(' ', banner, campaign) AS name,
iso_code AS country,
SUM(count) AS n
FROM pgehres.bannerimpressions imp JOIN pgehres.country c
WHERE imp.country_id = c.id
AND timestamp BETWEEN %(start)s AND %(stop)s
AND campaign RLIKE %(campaign_reg)s
AND banner RLIKE %(banner_reg)s
GROUP BY
DATE_FORMAT(CAST(timestamp as datetime), %(time_format)s),
banner,
campaign,
iso_code
"""
d = query_lutetium(query, params)
d.index = pd.to_datetime(d['timestamp'])
del d['timestamp']
d['n'] = d['n'].astype(int)
return d
def get_impressions(start, stop, campaign_reg = '.*', banner_reg = '.*', aggregation = 'hour'):
"""
Gets all impression data within the time range start:stop
"""
params = get_time_limits(start, stop)
params['campaign_reg'] = campaign_reg
params['banner_reg'] = banner_reg
params['time_format'] = get_time_format(aggregation)
query = """
SELECT
DATE_FORMAT(CAST(timestamp as datetime), %(time_format)s) AS timestamp,
banner,
campaign,
CONCAT_WS(' ', banner, campaign) AS name,
iso_code AS country,
SUM(count) AS n
FROM pgehres.bannerimpressions imp JOIN pgehres.country c
WHERE imp.country_id = c.id
AND timestamp BETWEEN %(start)s AND %(stop)s
AND campaign RLIKE %(campaign_reg)s
AND banner RLIKE %(banner_reg)s
GROUP BY
DATE_FORMAT(CAST(timestamp as datetime), %(time_format)s),
banner,
campaign,
iso_code
"""
d = query_lutetium(query, params)
d.index = pd.to_datetime(d['timestamp'])
del d['timestamp']
d['n'] = d['n'].astype(int)
return d
def get_clicks(start, stop, campaign):
"""
Gets all donation data within the time range start:stop
Groups data by banner, campaign and number of impressions seen
"""
params = get_time_limits(start, stop)
params['campaign'] = campaign
query = """
SELECT
DATE_FORMAT(CAST(ts as datetime), '%%Y-%%m-%%d %%H') as timestamp,
CONCAT_WS(' ', banner, utm_campaign) as name,
ct.country as country,
cs.payment_method as payment_method,
COUNT(*) as n,
CASE
WHEN contribution_id is NULL THEN 0.0
ELSE 1.0
END as donation
FROM drupal.contribution_tracking ct INNER JOIN drupal.contribution_source cs ON ct.id = cs.contribution_tracking_id
LEFT JOIN civicrm.civicrm_contribution co ON co.id = ct.contribution_id
WHERE ts BETWEEN %(start_ts)s AND %(stop_ts)s
AND utm_medium = 'sitenotice'
AND utm_campaign REGEXP %(campaign)s
GROUP BY timestamp, name, donation, country, payment_method
"""
print query % params
d = query_lutetium(query, params)
d.index = d['timestamp'].map(lambda t: pd.to_datetime(str(t)))
del d['timestamp']
return d.sort()