def targeting_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Targeting_Options',
Discovery_To_BigQuery('displayvideo',
'v1').resource_schema('TargetingOption'))
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Targeting Options', 'A2:Z')
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Targeting_Assigned',
Discovery_To_BigQuery('displayvideo',
'v1').resource_schema('AssignedTargetingOption'))
def put_data(endpoint, method):
schema = Discovery_To_BigQuery('displayvideo',
'v1').method_schema(endpoint, method)
out = {}
if 'dataset' in project.task['out']:
out['bigquery'] = {
'dataset': project.task['out']['dataset'],
'table': 'DV360_%s' % endpoint.replace('.', '_'),
'schema': schema,
'skip_rows': 0,
'format': 'JSON',
}
if 'sheet' in project.task:
out['sheets'] = {
'url': project.task['out']['sheet'],
'tab': endpoint,
'range': 'A1:A1',
'delete': True
}
return out
def inventory_group_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Inventory_Groups',
Discovery_To_BigQuery(
'displayvideo', 'v1').method_schema('inventorySourceGroups.list'))
def insertion_order_clear():
table_create(
project.task["auth_bigquery"], project.id, project.task["dataset"],
"DV_InsertionOrders",
Discovery_To_BigQuery(
"displayvideo",
"v1").method_schema("advertisers.insertionOrders.list"))
def advertiser_load():
# load multiple partners from user defined sheet
def advertiser_load_multiple():
rows = get_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Partners',
'range': 'A2:A'
}
})
for row in rows:
yield from API_DV360(project.task['auth_dv'],
iterate=True).advertisers().list(
partnerId=lookup_id(row[0])).execute()
# write advertisers to database and sheet
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'table':
'DV_Advertisers',
'schema':
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('advertisers.list'),
'format':
'JSON'
}
}, advertiser_load_multiple())
# write advertisers to sheet
put_rows(project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Advertisers',
'range': 'B2'
}
},
rows=get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"""SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
A.entityStatus
FROM `{dataset}.DV_Advertisers` AS A
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
""".format(**project.task),
'legacy':
False
}
}))
def insertion_order_load():
# load multiple from user defined sheet
def insertion_order_load_multiple():
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Advertisers",
"range": "A2:A"
}
})
for row in rows:
yield from API_DV360(
project.task["auth_dv"],
iterate=True).advertisers().insertionOrders().list(
advertiserId=lookup_id(row[0])).execute()
# write insertion orders to database
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"DV_InsertionOrders",
"schema":
Discovery_To_BigQuery(
"displayvideo",
"v1").method_schema("advertisers.insertionOrders.list"),
"format":
"JSON"
}
}, insertion_order_load_multiple())
def first_and_third_party_audience_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_First_And_Third_Party_Audiences',
Discovery_To_BigQuery(
'displayvideo',
'v1').method_schema('firstAndThirdPartyAudiences.list'))
def combined_audience_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Combined_Audiences',
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('combinedAudiences.list'))
def custom_list_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Custom_Lists',
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('customLists.list'))
def negative_keyword_list_load():
# load multiple from user defined sheet
def load_multiple():
advertisers = get_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Advertisers',
'range': 'A2:A'
}
})
for advertiser in advertisers:
yield from API_DV360(
project.task['auth_dv'],
iterate=True).advertisers().negativeKeywordLists().list(
advertiserId=lookup_id(advertiser[0])).execute()
# write inventorys to database and sheet
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'table':
'DV_Negative_Keywod_Lists',
'schema':
Discovery_To_BigQuery('displayvideo', 'v1').method_schema(
'advertisers.negativeKeywordLists.list'),
'format':
'JSON'
}
}, load_multiple())
# write inventorys to sheet
put_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Targeting Options',
'range': 'J2'
}
},
get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"""SELECT
CONCAT(A.displayName, ' - ', A.advertiserId, ' > ', L.displayName, ' - ', L.negativeKeywordListId),
FROM `{dataset}.DV_Negative_Keywod_Lists` AS L
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON L.advertiserId=A.advertiserId
""".format(**project.task),
'legacy':
False
}
}))
def location_list_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Location_Lists',
Discovery_To_BigQuery(
'displayvideo',
'v1').method_schema('advertisers.locationLists.list'))
def google_api_execute(auth, api_call, results, errors, limit=None):
try:
rows = API(api_call).execute()
if results:
# check if single object needs conversion to rows
if isinstance(rows, dict):
rows = [rows]
rows = map(lambda r: Discovery_To_BigQuery.clean(r), rows)
put_rows(auth, results, rows)
if 'bigquery' in results:
results['bigquery']['disposition'] = 'WRITE_APPEND'
except HttpError as e:
if errors:
rows = [{
'Error':
str(e),
'Parameters': [{
'Key': k,
'Value': str(v)
} for k, v in api_call['kwargs'].items()]
}]
put_rows(auth, errors, rows)
if 'bigquery' in errors:
errors['bigquery']['disposition'] = 'WRITE_APPEND'
else:
raise e
def channel_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Channels',
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('advertisers.channels.list'))
def combined_audience_load():
# load multiple from user defined sheet
def load_multiple():
advertisers = get_rows(
project.task['auth_sheets'],
{ 'sheets': {
'sheet': project.task['sheet'],
'tab': 'Advertisers',
'range': 'A2:A'
}}
)
for advertiser in advertisers:
yield from API_DV360(
project.task['auth_dv'],
iterate=True
).combinedAudiences().list(
advertiserId=lookup_id(advertiser[0])
).execute()
# write audience to database and sheet
put_rows(
project.task['auth_bigquery'],
{ 'bigquery': {
'dataset': project.task['dataset'],
'table': 'DV_Combined_Audiences',
'schema': Discovery_To_BigQuery(
'displayvideo',
'v1'
).method_schema(
'combinedAudiences.list'
),
'format': 'JSON'
}},
load_multiple()
)
# write audience to sheet
put_rows(
project.task['auth_sheets'],
{ 'sheets': {
'sheet': project.task['sheet'],
'tab': 'Targeting Options',
'range': 'O2'
}},
get_rows(
project.task['auth_bigquery'],
{ 'bigquery': {
'dataset': project.task['dataset'],
'query': """SELECT
CONCAT(I.displayName, ' - ', I.combinedAudienceId),
FROM `{dataset}.DV_Combined_Audiences` AS I
GROUP BY 1
ORDER BY 1
""".format(**project.task),
'legacy': False
}}
)
)
def negative_keyword_list_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Negative_Keywod_Lists',
Discovery_To_BigQuery(
'displayvideo',
'v1').method_schema('advertisers.negativeKeywordLists.list'))
def line_item_clear():
table_create(
project.task["auth_bigquery"], project.id, project.task["dataset"],
"DV_LineItems",
Discovery_To_BigQuery(
"displayvideo", "v1").method_schema("advertisers.lineItems.list"))
sheets_clear(project.task["auth_sheets"], project.task["sheet"],
"Line Items", "B2:Z")
def advertiser_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Advertisers',
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('advertisers.list'))
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Advertisers', 'B2:D')
def google_api_execute(auth, api_call, results, errors, limit=None):
"""Execute the actual API call and write to the end points defined.
The API call is completely defined at this point.
The results and error definition is optional.
Args:
auth (string): either "user" or "service" to make the API call.
api_call (dict): the JSON for the API call as defined in recipe.
results (dict): defines where the data will be written
errors (dict): defines where the errors will be written
limit (int): Reduce the number of calls ( mostly for debugging )
Returns (dict):
None, all data is transfered between API / BigQuery
Raises:
ValueError: If a required key in the recipe is missing.
"""
try:
rows = API(api_call).execute()
if results:
# check if single object needs conversion to rows
if isinstance(rows, dict):
rows = [rows]
# check if simple string API results
elif results.get('bigquery', {}).get('format', 'JSON') == 'CSV':
rows = [[r] for r in rows]
rows = map(lambda r: Discovery_To_BigQuery.clean(r), rows)
put_rows(auth, results, rows)
if 'bigquery' in results:
results['bigquery']['disposition'] = 'WRITE_APPEND'
except HttpError as e:
if errors:
rows = [{
'Error':
str(e),
'Parameters': [{
'Key': k,
'Value': str(v)
} for k, v in api_call['kwargs'].items()]
}]
put_rows(auth, errors, rows)
if 'bigquery' in errors:
errors['bigquery']['disposition'] = 'WRITE_APPEND'
else:
raise e
def inventory_source_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Inventory_Sources',
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('inventorySources.list'))
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Inventory Sources', 'A2:Z')
def campaign_clear():
table_create(
project.task['auth_bigquery'],
project.id,
project.task['dataset'],
'DV_Campaigns',
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('advertisers.campaigns.list'),
)
sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Campaigns', 'B2:Z')
def partner_clear():
table_create(
project.task['auth_bigquery'],
project.id,
project.task['dataset'],
'DV_Partners',
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('partners.list'),
)
sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Partners', 'B2:Z')
def creative_clear():
table_create(
project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"DV_Creatives",
Discovery_To_BigQuery(
"displayvideo", "v1").method_schema("advertisers.creatives.list"),
)
sheets_clear(project.task["auth_sheets"], project.task["sheet"],
"Creatives", "B2:Z")
def line_item_load_targeting():
def load_bulk():
# TODO: incorporate filters into line item fetch
line_items = [
lookup_id(p[0]) for p in get_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Line Items',
'range': 'A2:A'
}
})
]
parameters = get_rows(project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"query":
"SELECT advertiserId, lineItemId FROM `{dataset}.DV_LineItems`"
.format(**project.task)
}
},
as_object=True)
for parameter in parameters:
yield from API_DV360(
project.task["auth_dv"], iterate=True).advertisers().lineItems(
).bulkListLineItemAssignedTargetingOptions(
advertiserId=str(parameter['advertiserId']),
lineItemId=str(parameter['lineItemId']),
).execute()
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'table':
'DV_Targeting',
'schema':
Discovery_To_BigQuery(
'displayvideo',
'v1').resource_schema('AssignedTargetingOption'),
'disposition':
'WRITE_APPEND',
'format':
'JSON'
}
}, load_bulk())
def targeting_clear():
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Targeting_Options',
Discovery_To_BigQuery('displayvideo',
'v1').resource_schema('TargetingOption'))
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Targeting Options', 'A2:Z')
table_create(
project.task['auth_bigquery'], project.id, project.task['dataset'],
'DV_Targeting_Assigned',
Discovery_To_BigQuery('displayvideo',
'v1').resource_schema('AssignedTargetingOption'))
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Destination Targeting', 'A2:Z')
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Brand Safety Targeting', 'A2:Z')
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Demographic Targeting', 'A2:Z')
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Audience Targeting', 'A2:Z')
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Device Targeting', 'A2:Z')
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Geography Targeting', 'A2:Z')
sheets_clear(project.task['auth_sheets'], project.task['sheet'],
'Viewability Targeting', 'A2:Z')
def partner_load():
partner_clear()
# write partners to BQ
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'table':
'DV_Partners',
'schema':
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('partners.list'),
'format':
'JSON'
}
},
API_DV360(project.task['auth_dv'],
iterate=True).partners().list().execute())
# write partners to sheet
put_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Partners',
'header': False,
'range': 'B2'
}
},
get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"SELECT CONCAT(displayName, ' - ', partnerId), entityStatus FROM `%s.DV_Partners`"
% project.task['dataset'],
'legacy':
False
}
}))
def google_api_build_results(auth, api_call, results):
"""Builds the BigQuery table to house the Google API call results.
Optional piece of the recipe, will create a BigQuery table for results.
Takes results, which defines a bigquery endpoint, and adds fields.
Args:
auth (string): either "user" or "service" to make the BigQuery call.
api_call (dict): the JSON for the API call as defined in recipe.
results (dict): defines where the data will be written
Returns (dict):
A modified results JSON with additional API values added.
Raises:
ValueError: If a required key in the recipe is missing.
"""
if 'bigquery' in results:
if 'schema' not in results['bigquery']:
results['bigquery']['schema'] = Discovery_To_BigQuery(
api_call['api'],
api_call['version'],
api_call.get('key', None),
).method_schema(api_call['function'],
api_call.get('iterate', False))
if 'format' not in results['bigquery']:
results['bigquery']['format'] = 'JSON'
results['bigquery']['skip_rows'] = 0
table_create(results['bigquery'].get('auth', auth),
project.id,
results['bigquery']['dataset'],
results['bigquery']['table'],
results['bigquery']['schema'],
overwrite=False)
return results
def google_api_build_results(auth, api_call, results):
if 'bigquery' in results:
results['bigquery']['schema'] = Discovery_To_BigQuery(
api_call['api'],
api_call['version'],
api_call.get('key', None),
).method_schema(api_call['function'])
#TODO: Fix format to sometimes be CSV, probably refactor BigQuery to
# determine format based on rows or schema
results['bigquery']['format'] = 'JSON'
results['bigquery']['skip_rows'] = 0
#results['bigquery']['disposition'] = 'WRITE_TRUNCATE'
table_create(results['bigquery'].get('auth', auth),
project.id,
results['bigquery']['dataset'],
results['bigquery']['table'],
results['bigquery']['schema'],
overwrite=False)
return results
def campaign_load():
# load multiple partners from user defined sheet
def campaign_load_multiple():
rows = get_rows(
project.task['auth_sheets'],
{ 'sheets': {
'sheet': project.task['sheet'],
'tab': 'Advertisers',
'range': 'A2:A'
}}
)
for row in rows:
yield from API_DV360(
project.task['auth_dv'],
iterate=True
).advertisers().campaigns().list(
advertiserId=lookup_id(row[0])
).execute()
# write campaigns to database
put_rows(
project.task['auth_bigquery'],
{ 'bigquery': {
'dataset': project.task['dataset'],
'table': 'DV_Campaigns',
'schema': Discovery_To_BigQuery(
'displayvideo',
'v1'
).method_schema(
'advertisers.campaigns.list'
),
'format': 'JSON'
}},
campaign_load_multiple()
)
def partner_load_targeting():
def load_bulk():
partners = [
lookup_id(p[0]) for p in get_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Partners',
'range': 'A2:A'
}
})
]
for partner in partners:
yield from API_DV360(
project.task["auth_dv"], iterate=True).partners(
).targetingTypes().assignedTargetingOptions().list(
partnerId=str(partner),
targetingType='TARGETING_TYPE_CHANNEL').execute()
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'table':
'DV_Targeting',
'schema':
Discovery_To_BigQuery(
'displayvideo',
'v1').resource_schema('AssignedTargetingOption'),
'disposition':
'WRITE_APPEND',
'format':
'JSON'
}
}, load_bulk())
def put_data(endpoint, method):
out = {}
schema = Discovery_To_BigQuery('dfareporting',
'v3.4').method_schema(endpoint, method)
if 'dataset' in project.task['out']:
out['bigquery'] = {
'dataset': project.task['out']['dataset'],
'table': 'CM_%s' % endpoint,
'schema': schema,
'skip_rows': 0,
'format': 'JSON',
}
if 'sheet' in project.task:
out['sheets'] = {
'url': project.task['out']['sheet'],
'tab': 'CM_%s' % endpoint,
'range': 'A1:A1',
'delete': True
}
return out