def floodlight_rows(
config, task: dict, report_id: int
) -> Generator[list[str, str, str, str, str, str, int], None, None]:
""" Monitor a report for completion and return rows
Args:
report_id - the report created earlier for a specific floodlight id.
Returns:
A stream of rows, see FLOODLIGHT_* constants for definitions.
"""
# fetch report file if it exists
filename, report = report_file(
config,
task['auth'],
task['account'],
report_id,
None, # no name
10 # wait up to 10 minutes for report to complete
)
# clean up rows
rows = report_to_rows(report)
rows = report_clean(rows)
rows = rows_header_trim(rows)
rows = rows_to_type(rows, column=6)
return rows
def report_main(config,
task,
name,
dateRange,
schedule,
advertiser,
campaign,
shadow=True):
if config.verbose:
print('DYNAMIC COSTS MAIN:', name)
# base report schema
schema = {
'kind': 'dfareporting#report',
'type': 'STANDARD',
'name': 'Dynamic Costs %s - Main Advertiser ( StarThinker )' % name,
'schedule': schedule,
'criteria': {
'dateRange':
dateRange,
'dimensionFilters': [{
'kind': 'dfareporting#dimensionValue',
'dimensionName': 'dfa:advertiser',
'id': advertiser,
'matchType': 'EXACT'
}, {
'kind': 'dfareporting#dimensionValue',
'dimensionName': 'dfa:campaign',
'id': campaign,
'matchType': 'EXACT'
}],
'dimensions': [{
'kind': 'dfareporting#sortedDimension',
'name': 'dfa:placement'
}, {
'kind': 'dfareporting#sortedDimension',
'name': 'dfa:placementId'
}],
'metricNames': ['dfa:impressions', 'dfa:clicks']
}
}
# if not using shadow advertiser, pull DBM cost here
if not shadow:
schema['criteria']['metricNames'].append('dfa:dbmCost')
# create the report if it does not exist
report = report_build(config, task['auth'], task['account'], schema)
# fetch report file if it exists ( timeout = 0 means grab most reent ready )
filename, filedata = report_file(config, task['auth'], task['account'],
report['id'], None, 60, DCM_CHUNK_SIZE)
# write report to a table ( avoid collisions as best as possible )
table_name = 'Dynamic_Costs_%s_Main_Advertiser' % name
write_report(config, task, filedata, task['out']['dataset'], table_name)
return table_name
def report_shadow(config, task, name, dateRange, schedule, advertiser,
campaign):
if config.verbose:
print('DYNAMIC COSTS SHADOW:', name)
# create the report if it does not exist
report = report_build(
config, task['auth'], task['account'], {
'kind': 'dfareporting#report',
'type': 'STANDARD',
'name':
'Dynamic Costs %s - Shadow Advertiser ( StarThinker )' % name,
'schedule': schedule,
'criteria': {
'dateRange':
dateRange,
'dimensionFilters': [{
'dimensionName': 'dfa:advertiser',
'id': advertiser,
'kind': 'dfareporting#dimensionValue',
'matchType': 'EXACT'
}, {
'dimensionName': 'dfa:campaign',
'id': campaign,
'kind': 'dfareporting#dimensionValue',
'matchType': 'EXACT'
}],
'dimensions': [{
'kind': 'dfareporting#sortedDimension',
'name': 'dfa:placement'
}, {
'kind': 'dfareporting#sortedDimension',
'name': 'dfa:placementId'
}],
'metricNames': ['dfa:dbmCost']
}
})
# fetch report file if it exists ( timeout = 0 means grab most reent ready )
filename, filedata = report_file(config, task['auth'], task['account'],
report['id'])
# write report to a table ( avoid collisions as best as possible )
table_name = 'Dynamic_Costs_%s_Shadow_Advertiser' % name
write_report(config, task, filedata, task['out']['dataset'], table_name)
return table_name
def dcm_replicate_download(config, task, account, name):
filename, report = report_file(config, task['auth'], account, None, name)
if report:
if config.verbose:
print('DCM FILE', filename)
# clean up the report
rows = report_to_rows(report)
rows = report_clean(rows)
# if bigquery, remove header and determine schema
schema = None
if 'bigquery' in task['out']:
task['out']['bigquery']['table'] = table_name_sanitize(name)
task['out']['bigquery']['schema'] = report_schema(next(rows))
task['out']['bigquery']['skip_rows'] = 0
# write rows using standard out block in json ( allows customization across all scripts )
if rows:
put_rows(config, task['auth'], task['out'], rows)
def main():
parser = argparse.ArgumentParser(
formatter_class=argparse.RawDescriptionHelpFormatter,
description=textwrap.dedent("""\
Command line to help debug CM reports and build reporting tools.
Examples:
To get list of reports: python cm.py --account [id] --list -u [user credentials path]
To get report: python cm.py --account [id] --report [id] -u [user credentials path]
To get report files: python cm.py --account [id] --files [id] -u [user credentials path]
To get report sample: python cm.py --account [id] --sample [id] -u [user credentials path]
To get report schema: python cm.py --account [id] --schema [id] -u [user credentials path]
"""))
parser.add_argument('--account',
help='Account ID to use to pull the report.',
default=None)
parser.add_argument('--report',
help='Report ID to pull JSON definition.',
default=None)
parser.add_argument('--schema',
help='Report ID to pull achema definition.',
default=None)
parser.add_argument('--sample',
help='Report ID to pull sample data.',
default=None)
parser.add_argument('--files',
help='Report ID to pull file list.',
default=None)
parser.add_argument('--list', help='List reports.', action='store_true')
# initialize project
parser = commandline_parser(parser, arguments=('-u', '-c', '-s', '-v'))
args = parser.parse_args()
config = Configuration(user=args.user,
client=args.client,
service=args.service,
verbose=args.verbose)
auth = 'service' if args.service else 'user'
is_superuser, profile = get_profile_for_api(config, auth, args.account)
kwargs = {
'profileId': profile,
'accountId': args.account
} if is_superuser else {
'profileId': profile
}
# get report list
if args.report:
kwargs['reportId'] = args.report
report = API_DCM(
config, auth,
internal=is_superuser).reports().get(**kwargs).execute()
print(json.dumps(report, indent=2, sort_keys=True))
# get report files
elif args.files:
kwargs['reportId'] = args.files
for rf in API_DCM(
config, auth, internal=is_superuser,
iterate=True).reports().files().list(**kwargs).execute():
print(json.dumps(rf, indent=2, sort_keys=True))
# get schema
elif args.schema:
filename, report = report_file(config, auth, args.account, args.schema,
None, 10)
rows = report_to_rows(report)
rows = report_clean(rows)
print(json.dumps(report_schema(next(rows)), indent=2, sort_keys=True))
# get sample
elif args.sample:
filename, report = report_file(config, auth, args.account, args.sample,
None, 10)
rows = report_to_rows(report)
rows = report_clean(rows)
rows = rows_to_type(rows)
for r in rows_print(rows, row_min=0, row_max=20):
pass
# get list
else:
for report in API_DCM(config,
auth,
internal=is_superuser,
iterate=True).reports().list(**kwargs).execute():
print(json.dumps(report, indent=2, sort_keys=True))
def dcm(config, task):
if config.verbose:
print('DCM')
# stores existing report json
report = None
# check if report is to be deleted
if task.get('delete', False):
if config.verbose:
print(
'DCM DELETE', task['report'].get('name', None)
or task['report'].get('body', {}).get('name', None)
or task['report'].get('report_id', None))
report_delete(
config,
task['auth'],
task['report']['account'],
task['report'].get('report_id', None),
task['report'].get('name', None)
or task['report'].get('body', {}).get('name', None),
)
# check if report is to be run
if task.get('report_run_only', False):
if config.verbose:
print(
'DCM REPORT RUN', task['report'].get('name', None)
or task['report'].get('report_id', None))
report_run(
config,
task['auth'],
task['report']['account'],
task['report'].get('report_id', None),
task['report'].get('name', None),
)
# check if report is to be created
if 'body' in task['report']:
if config.verbose:
print('DCM BUILD', task['report']['body']['name'])
if 'filters' in task['report']:
task['report']['body'] = report_filter(config, task['auth'],
task['report']['body'],
task['report']['filters'])
report = report_build(
config, task['auth'], task['report']['body'].get('accountId')
or task['report']['account'], task['report']['body'])
# moving a report
if 'out' in task:
filename, report = report_file(
config,
task['auth'],
task['report']['account'],
task['report'].get('report_id', None),
task['report'].get('name', None)
or task['report'].get('body', {}).get('name', None),
task['report'].get('timeout', 10),
)
if report:
if config.verbose:
print('DCM FILE', filename)
# clean up the report
rows = report_to_rows(report)
rows = report_clean(rows)
# if bigquery, remove header and determine schema
schema = None
if 'bigquery' in task['out']:
schema = report_schema(next(rows))
task['out']['bigquery']['schema'] = schema
task['out']['bigquery']['skip_rows'] = 0
# write rows using standard out block in json ( allows customization across all scripts )
if rows:
put_rows(config, task['auth'], task['out'], rows)
def run_floodlight_reports(config, task):
if config.verbose:
print('Creating Floodlight reports')
body = {
"kind": "dfareporting#report",
"name": '', # this is updated below based on Floodlight Config ID
"format": "CSV",
"type": "FLOODLIGHT",
"floodlightCriteria": {
"dateRange": {
"kind": "dfareporting#dateRange",
"relativeDateRange": "LAST_60_DAYS"
},
"floodlightConfigId": {
"kind": "dfareporting#dimensionValue",
"dimensionName": "dfa:floodlightConfigId",
"value": 0, # updated below and replaced with Floodlight Config ID
"matchType": "EXACT"
},
"reportProperties": {
"includeUnattributedIPConversions": False,
"includeUnattributedCookieConversions": True
},
"dimensions": [
{
"kind": "dfareporting#sortedDimension",
"name": "dfa:site"
},
{
"kind": "dfareporting#sortedDimension",
"name": "dfa:floodlightAttributionType"
},
{
"kind": "dfareporting#sortedDimension",
"name": "dfa:interactionType"
},
{
"kind": "dfareporting#sortedDimension",
"name": "dfa:pathType"
},
{
"kind": "dfareporting#sortedDimension",
"name": "dfa:browserPlatform"
},
{
"kind": "dfareporting#sortedDimension",
"name": "dfa:platformType"
},
{
"kind": "dfareporting#sortedDimension",
"name": "dfa:week"
},
{
"kind": "dfareporting#sortedDimension",
"name": "dfa:placementId"
},
{
"kind": "dfareporting#sortedDimension",
"name": "dfa:floodlightConfigId"
}],
"metricNames": [
"dfa:activityClickThroughConversions",
"dfa:activityViewThroughConversions",
"dfa:totalConversions",
"dfa:totalConversionsRevenue"
]
},
"schedule": {
"active": True,
"repeats": "WEEKLY",
"every": 1,
"repeatsOnWeekDays":["Sunday"]
},
"delivery": {
"emailOwner": False
}
}
reports = []
floodlightConfigs = task.get('floodlightConfigIds', None)
for configId in floodlightConfigs:
body['name'] = task.get('reportPrefix', '') + "_" + str(configId)
body['floodlightCriteria']['floodlightConfigId']['value'] = configId
report = report_build(
config, task['auth_cm'],
task['account'],
body
)
reports.append(report['id'])
if config.verbose:
print('Finished creating Floodlight reports - moving to BQ')
queries = []
for createdReportId in reports:
filename, report = report_file(
config, task['auth_cm'],
task['account'],
createdReportId,
None,
task.get('timeout', 10),
)
if report:
if config.verbose:
print('Floodlight config report ', filename)
# clean up the report
rows = report_to_rows(report)
rows = report_clean(rows)
# determine schema
schema = report_schema(next(rows))
out_block = {}
out_block['bigquery'] = {}
out_block['bigquery']['dataset'] = task['dataset']
out_block['bigquery']['schema'] = schema
out_block['bigquery']['skip_rows'] = 0
out_block['bigquery']['table'] = 'z_Floodlight_CM_Report_' + str(createdReportId)
# write rows using standard out block in json ( allows customization across all scripts )
if rows: put_rows(config, task['auth_bq'], out_block, rows)
queries.append('SELECT * FROM `{0}.{1}.{2}`'.format(config.project, out_block['bigquery']['dataset'], out_block['bigquery']['table']))
if config.verbose:
print('Moved reports to BQ tables - starting join')
finalQuery = ' UNION ALL '.join(queries)
query_to_table(
config, task['auth_bq'],
config.project,
task['dataset'],
CM_FLOODLIGHT_OUTPUT_TABLE,
finalQuery,
legacy=False
)
if config.verbose:
print('Finished with Floodlight Config reports')
def report_combos(config, task, name, dateRange, schedule, advertiser,
campaign, dynamicProfile):
if config.verbose:
print('DYNAMIC COSTS COMBOS:', name)
# basic report schema, with no dynamic elements
schema = {
'kind': 'dfareporting#report',
'type': 'STANDARD',
'name': 'Dynamic Costs %s - Dynamic Combos ( StarThinker )' % name,
'schedule': schedule,
'criteria': {
'dateRange':
dateRange,
'dimensionFilters': [{
'kind': 'dfareporting#dimensionValue',
'dimensionName': 'dfa:dynamicProfile',
'id': dynamicProfile,
'matchType': 'EXACT'
}, {
'kind': 'dfareporting#dimensionValue',
'dimensionName': 'dfa:advertiser',
'id': advertiser,
'matchType': 'EXACT'
}, {
'kind': 'dfareporting#dimensionValue',
'dimensionName': 'dfa:campaign',
'id': campaign,
'matchType': 'EXACT'
}],
'dimensions': [{
'kind': 'dfareporting#sortedDimension',
'name': 'dfa:placement'
}, {
'kind': 'dfareporting#sortedDimension',
'name': 'dfa:placementId'
}, {
'kind': 'dfareporting#sortedDimension',
'name': 'dfa:activity'
}, {
'kind': 'dfareporting#sortedDimension',
'name': 'dfa:activityId'
}],
'metricNames':
['dfa:impressions', 'dfa:clicks', 'dfa:totalConversions']
}
}
# add in all reasonable dynamic elements
for i in range(1, 5 + 1): # 5 elements/feeds
for j in range(1, 6 + 1): # 6 fields per element
schema['criteria']['dimensions'].append({
'kind':
'dfareporting#sortedDimension',
'name':
'dfa:dynamicElement%iField%iValue' % (i, j)
})
print(json.dumps(schema, indent=2))
# create the report if it does not exist
report = report_build(config, task['auth'], task['account'], schema)
# fetch report file if it exists ( timeout = 0 means grab most reent ready )
filename, filedata = report_file(config, task['auth'], task['account'],
report['id'], None, 60, DCM_CHUNK_SIZE)
# write report to a table ( avoid collisions as best as possible )
table_name = 'Dynamic_Costs_%s_Dynamic_Combos' % name
write_report(config, task, filedata, task['out']['dataset'], table_name)
return table_name