def sov_create_reports():
if project.verbose: print "CLIENT:", project.task['dataset']
# make sure tab exists in sheet
sheets_tab_copy(project.task['auth'],
project.task['sheet']['template']['url'],
project.task['sheet']['template']['tab'],
project.task['sheet']['url'],
project.task['sheet']['template']['tab'])
# read peers from sheet
rows = sheets_read(project.task['auth'], project.task['sheet']['url'],
project.task['sheet']['template']['tab'],
project.task['sheet']['range'])
# CHECK: If minimum number of peers met ( prevents creation of reports )
if len(rows) < 5:
raise Exception(
'Peer sheet needs 5+ DBM entries to ensure anonymity, there are %d!'
% len(rows))
# create a report for the peers ( given in sheet ), make partner_id:advertiser_id
peer_name = sov_report([('%s:%s' % (r[0], r[1])) if len(r) == 2 else r[0]
for r in rows], 'Peer')
# create a report for the client ( given in JSON )
client_name = sov_report(project.task['dbm_accounts'], 'Client')
# names are used to fetch the report
return client_name, peer_name
def mapping():
if project.verbose: print 'MAPPING'
# create the sheet from template if it does not exist
sheets_tab_copy(project.task['auth'], TEMPLATE_SHEET, TEMPLATE_TAB,
project.task['sheet'], project.task['tab'])
# move if specified
dimensions = {}
defaults = {}
rows = sheets_read(project.task['auth'], project.task['sheet'],
project.task['tab'], 'A1:D')
# if rows don't exist, query is still created without mapping ( allows blank maps )
if rows:
# sanitize mapping
# 0 = Dimension, 1 = Tag, 2 = Column, 3 = Keyword
for row in rows[1:]:
if project.verbose: print 'ROW: ', row
# sanitize row
row = map(lambda c: RE_SQLINJECT.sub('', c.strip()), row)
if len(row) == 2: # default
defaults.setdefault(row[0], row[1])
else: # tag
dimensions.setdefault(row[0], {}) # dimension
dimensions[row[0]].setdefault(row[1], {})
dimensions[row[0]].setdefault(row[1], {}) # tag
dimensions[row[0]][row[1]].setdefault(row[2], []) # column
dimensions[row[0]][row[1]][row[2]].extend(
[k.strip() for k in row[3].split(',') if k]) # keywords
# construct query
query = 'SELECT\n *,\n'
for dimension, tags in dimensions.items():
query += ' CASE\n'
for tag, columns in tags.items():
query += ' WHEN '
for column, keywords in columns.items():
for count, keyword in enumerate(keywords):
if count != 0: query += 'OR '
query += '%s CONTAINS "%s" ' % (column, keyword)
query += 'THEN "%s"\n' % tag
query += ' ELSE "%s"\n END AS %s,\n' % (defaults.get(
dimension, ''), dimension)
query += 'FROM [%s.%s]' % (project.task['in']['dataset'],
project.task['in']['table'])
if project.verbose: print 'QUERY: ', query
# write to view
query_to_view(project.task['out']['auth'],
project.id,
project.task['out']['dataset'],
project.task['out']['view'],
query,
replace=True)
def floodlight_monitor() -> None:
""" The task handler. See module description.
Args:
Everuthing is passed using project.task.
Returns:
Nothing.
"""
if project.verbose:
print('FLOODLIGHT MONITOR')
# make sure tab exists in sheet ( deprecated, use sheet task instead )
if 'template' in project.task['sheet']:
sheets_tab_copy(project.task['auth'],
project.task['sheet']['template']['sheet'],
project.task['sheet']['template']['tab'],
project.task['sheet']['sheet'],
project.task['sheet']['tab'])
# read peers from sheet
triggers = sheets_read(project.task['auth'],
project.task['sheet']['sheet'],
project.task['sheet']['tab'],
project.task['sheet']['range'])
if project.verbose and len(triggers) == 0:
print('FLOODLIGHT MONITOR: No floodlight ids specified in sheet.')
alerts = {}
day = None
# create reports first in parallel
for trigger in triggers:
trigger.append(floodlight_report(trigger[TRIGGER_ID]))
# download data from all reports
for trigger in triggers:
# get report rows for each floodlight
rows = floodlight_rows(trigger[TRIGGER_REPORT])
# calculate outliers
last_day, rows = floodlight_analysis(rows)
if last_day:
# find last day report ran
day = last_day if day is None else max(day, last_day)
# group alerts by email
alerts.setdefault(trigger[TRIGGER_EMAIL], [])
alerts[trigger[TRIGGER_EMAIL]].extend(rows)
if alerts:
floodlight_email(day, alerts)
def floodlight_monitor():
if project.verbose:
print('FLOODLIGHT MONITOR')
# make sure tab exists in sheet ( deprecated, use sheet task instead )
if 'template' in project.task['sheet']:
sheets_tab_copy(project.task['auth'],
project.task['sheet']['template']['sheet'],
project.task['sheet']['template']['tab'],
project.task['sheet']['sheet'],
project.task['sheet']['tab'])
# read peers from sheet
triggers = sheets_read(project.task['auth'],
project.task['sheet']['sheet'],
project.task['sheet']['tab'],
project.task['sheet']['range'])
# 0 - Floodlight Id
# 1 - email
# 2 - dcm report id ( added by this script )
# 3 - status, added by the script ( LOW, NORMAL, HIGH )
if project.verbose and len(triggers) == 0:
print('FLOODLIGHT MONITOR: No floodlight ids specified in sheet.')
alerts = {}
day = None
# create reports first in parallel
for trigger in triggers:
trigger.append(floodlight_report(trigger[0]))
# download data from all reports
for trigger in triggers:
# get report rows for each floodlight
rows = floodlight_rows(trigger[2])
# calculate outliers
last_day, rows = floodlight_analysis(rows)
if last_day:
# find last day report ran
day = last_day if day is None else max(day, last_day)
# group alerts by email
alerts.setdefault(trigger[1], [])
alerts[trigger[1]].extend(rows)
if alerts:
floodlight_email(day, alerts)
def floodlight_monitor():
if project.verbose: print("FLOODLIGHT MONITOR")
# make sure tab exists in sheet ( deprecated, use sheet task instead )
if 'template' in project.task['sheet']:
sheets_tab_copy(project.task['auth'],
project.task['sheet']['template']['sheet'],
project.task['sheet']['template']['tab'],
project.task['sheet']['sheet'],
project.task['sheet']['tab'])
# read peers from sheet
triggers = sheets_read(project.task['auth'],
project.task['sheet']['sheet'],
project.task['sheet']['tab'],
project.task['sheet']['range'])
# 0 - Floodlight Id
# 1 - email
if project.verbose and len(triggers) == 0:
print("FLOODLIGHT MONITOR: No floodlight ids specified in sheet.")
alerts = {}
day = None
for trigger in triggers:
# get report data for each floodlight
report = floodlight_report(trigger[0])
rows = report_to_rows(report)
rows = report_clean(rows)
rows = rows_header_trim(rows)
rows = rows_to_type(rows, column=6)
# calculate outliers
last_day, rows = floodlight_analysis(rows)
if last_day:
# find last day report ran
day = last_day if day is None else max(day, last_day)
# group alerts by email
alerts.setdefault(trigger[1], [])
alerts[trigger[1]].extend(rows)
if alerts:
floodlight_email(day, alerts)
def dynamic_costs():
# make sure tab exists in sheet
sheets_tab_copy(project.task['auth'],
project.task['sheet']['template']['url'],
project.task['sheet']['template']['tab'],
project.task['sheet']['url'], project.task['sheet']['tab'])
# read configuration from sheet
inputs = sheets_read(project.task['auth'], project.task['sheet']['url'],
project.task['sheet']['tab'],
project.task['sheet']['range'])
# convert inputs into dictionary
def expand_list(lst):
if len(lst) == 1: return (lst[0], "")
elif len(lst) == 2: return lst
inputs = [expand_list(row) for row in inputs]
inputs = dict(inputs)
if project.verbose: print "DYNAMIC COSTS PARAMETERS", inputs
if 'Main Advertiser ID' in inputs or not inputs['Main Advertiser ID']:
print "Configuration sheet not filled out."
return
# allows each advertiser to run multiple reports ( somewhat collision avoidance )
unique_name = inputs['Dynamic Profile ID']
# check if using wrapped tags
shadow = inputs['Shadow Advertiser ID'] and inputs['Shadow Campaign ID']
# parse date range
if inputs['Relative Date Range'] == 'CUSTOM':
date_range = {
"kind": "dfareporting#dateRange",
"startDate": str(inputs['Start Date']),
"endDate": str(inputs['End Date']),
}
else:
date_range = {
"kind": "dfareporting#dateRange",
"relativeDateRange": str(inputs['Relative Date Range'])
}
combos_table = report_combos(unique_name, date_range,
inputs['Main Advertiser ID'],
inputs['Main Campaign ID'],
inputs['Dynamic Profile ID'])
main_table = report_main(unique_name, date_range,
inputs['Main Advertiser ID'],
inputs['Main Campaign ID'], shadow)
if shadow:
shadow_table = report_shadow(unique_name, date_range,
inputs['Shadow Advertiser ID'],
inputs['Shadow Campaign ID'])
else:
shadow_table = None
view_combine(unique_name, combos_table, main_table, shadow_table)
def sheets():
if project.verbose: print 'SHEETS'
# clear if specified
if project.task.get('clear', False):
sheets_clear(project.task['auth'], project.task['sheet'],
project.task['tab'], project.task['range'])
# delete if specified ( after clear to prevent errors in case both are given )
if project.task.get('delete', False):
sheets_tab_delete(project.task['auth'], project.task['sheet'],
project.task['tab'])
# create or copy if specified
if 'template' in project.task:
sheets_tab_copy(project.task['auth'],
project.task['template']['sheet'],
project.task['template']['tab'], project.task['sheet'],
project.task['tab'])
else:
sheets_tab_create(project.task['auth'], project.task['sheet'],
project.task['tab'])
# write data if specified
if 'write' in project.task:
rows = get_rows(project.task['auth'], project.task['write'])
sheets_write(project.task['auth'], project.task['sheet'],
project.task['tab'], project.task['range'], rows)
# move if specified
if 'out' in project.task:
rows = sheets_read(project.task['auth'], project.task['sheet'],
project.task['tab'], project.task['range'])
if rows:
schema = None
# RECOMMENDED: define schema in json
if project.task['out']['bigquery'].get('schema'):
if project.verbose: print 'SHEETS SCHEMA DEFINED'
schema = project.task['out']['bigquery']['schema']
# NOT RECOMMENDED: determine schema if missing
else:
if project.verbose:
print 'SHEETS SCHEMA DETECT ( Note Recommended - Define Schema In JSON )'
# cast rows to types ( for schema detection )
rows = rows_to_type(rows)
rows, schema = get_schema(rows,
project.task.get('header', False),
infer_type=project.task.get(
'infer_type', True))
# write to table ( not using put because no use cases for other destinations )
rows_to_table(
auth=project.task['out'].get('auth', project.task['auth']),
project_id=project.id,
dataset_id=project.task['out']['bigquery']['dataset'],
table_id=project.task['out']['bigquery']['table'],
rows=rows,
schema=schema,
skip_rows=1 if project.task.get('header', False) else 0,
disposition=project.task['out']['bigquery'].get(
'disposition', 'WRITE_TRUNCATE'))
else:
print 'SHEET EMPTY'
