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'