def setup():
"""Sets up Bulkdozer configuration and required object to execute the job.
"""
if not 'dcm_profile_id' in project.task and 'account_id' in project.task:
project.task['dcm_profile_id'] = get_profile_for_api(
project.task['auth'], project.task['account_id'])[1]
if 'sheet_url' in project.task and not 'sheet_id' in project.task:
project.task['sheet_id'] = sheets_id(project.task['auth'],
project.task['sheet_url'])
# Setting up required objects and parsing parameters
config.auth = project.task['auth']
config.trix_id = project.task.get('store',
{}).get('sheet_id',
project.task['sheet_id'])
config.load()
logger.auth = project.task['auth']
logger.trix_id = project.task.get('logger',
{}).get('sheet_id',
project.task['sheet_id'])
logger.buffered = True
def get_changelogs(accounts, start):
if project.verbose:
print('CM CHANGE LOGS', accounts)
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'],
account_id)
kwargs = {'profileId': profile_id, 'minChangeTime': start}
if is_superuser:
kwargs['accountId'] = account_id
for changelog in API_DCM(
'user', iterate=True,
internal=is_superuser).changeLogs().list(**kwargs).execute():
yield [
changelog.get('userProfileId'),
changelog['accountId'],
changelog.get('subaccountId'),
changelog['id'],
changelog['transactionId'],
changelog['objectType'],
changelog['objectId'],
changelog['action'],
changelog.get('fieldName'),
changelog['changeTime'],
changelog.get('oldValue'),
changelog.get('newValue'),
]
def google_api_initilaize(api_call, alias=None):
"""Some Google API calls require a lookup or pre-call, add it here.
Modifies the API call before actual execution with any data
specifically required by an endpoint. Currently:
> dfa-reporting - look up user profile and add to call.
Args:
api_call (dict): the JSON for the API call as defined in recipe.
alias (string): mostly used to signal a list behavior (change to iterate in future?)
Returns (dict):
A modified JSON with additional API values added.
Currently mostly used by dfareporting API to add profile and account.
Raises:
ValueError: If a required key in the recipe is missing.
"""
if api_call['function'].endswith('list') or alias == 'list':
api_call['iterate'] = True
if api_call['api'] == 'dfareporting':
is_superuser, profile_id = get_profile_for_api(
api_call['auth'], api_call['kwargs']['accountId'])
api_call['kwargs']['profileId'] = profile_id
if is_superuser:
from starthinker.util.dcm.internalv33_uri import URI as DCM_URI
api_call['version'] = 'internalv3.3'
api_call['uri'] = DCM_URI
else:
del api_call['kwargs']['accountId']
def get_advertisers(accounts):
if project.verbose: print('DCM Advertisers')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id
} if is_superuser else {
'profileId': profile_id
}
for advertiser in API_DCM(
"user", iterate=True,
internal=is_superuser).advertisers().list(**kwargs).execute():
if int(advertiser['accountId']) in accounts:
yield [
advertiser['accountId'],
advertiser.get('subaccountId'),
advertiser['id'],
advertiser.get('advertiserGroupId'),
advertiser['name'],
advertiser['status'],
advertiser.get('defaultEmail'),
advertiser.get('clickThroughUrlSuffix'),
advertiser.get('defaultClickThroughEventTagId'),
advertiser['suspended'],
advertiser['floodlightConfigurationId'],
advertiser['originalFloodlightConfigurationId'],
]
def get_campaigns(accounts):
if project.verbose: print 'DCM Campaigns'
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'], account_id)
kwargs = { 'profileId':profile_id, 'accountId':account_id } if is_superuser else { 'profileId':profile_id }
for campaign in API_DCM("user", iterate=True, internal=is_superuser).campaigns().list(**kwargs).execute():
if long(campaign['accountId']) in accounts:
yield [
campaign['accountId'],
campaign.get('subaccountId'),
campaign['advertiserId'],
campaign.get('advertiserGroupId'),
campaign['id'],
campaign['name'],
campaign['archived'],
campaign['startDate'],
campaign['endDate'],
campaign.get('comment', ''),
epoch_to_datetime(campaign['createInfo']['time'], 1000),
epoch_to_datetime(campaign['lastModifiedInfo']['time'], 1000),
campaign.get('externalId'),
campaign['defaultLandingPageId'],
campaign['adBlockingConfiguration']['enabled'],
campaign['adBlockingConfiguration']['overrideClickThroughUrl'],
campaign['adBlockingConfiguration'].get('clickThroughUrl'),
campaign['adBlockingConfiguration'].get('creativeBundleId'),
campaign['nielsenOcrEnabled'],
]
def dcm_api_list(endpoint):
accounts = set(get_rows('user', project.task['accounts']))
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
}
if is_superuser:
kwargs['accountId'] = account_id
if 'advertisers' in project.task:
advertisers = set(get_rows('user', project.task['advertisers']))
for advertiser in advertisers:
kwargs['advertiserIds'] = str(advertiser)
yield from API_DCM(project.task['auth'],
iterate=True,
internal=is_superuser).call(endpoint).list(
**kwargs).execute()
else:
yield from API_DCM(
project.task['auth'], iterate=True,
internal=is_superuser).call(endpoint).list(**kwargs).execute()
def get_roles(accounts):
if project.verbose: print 'DCM Roles'
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'], account_id)
kwargs = { 'profileId':profile_id, 'accountId':account_id } if is_superuser else { 'profileId':profile_id }
for role in API_DCM("user", iterate=True, internal=is_superuser).userRoles().list(**kwargs).execute():
if long(role['accountId']) in accounts:
if 'permissions' in role:
for permission in role['permissions']:
yield [
role['accountId'],
role.get('subaccountId'),
role['id'],
role['name'],
role['defaultUserRole'],
permission['name'],
permission['availability'],
]
else:
yield [
role['accountId'],
role.get('subaccountId'),
role['id'],
role['name'],
role['defaultUserRole'],
None,
None,
]
def get_sites(accounts):
if project.verbose:
print('DCM Sites')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id
} if is_superuser else {
'profileId': profile_id
}
for site in API_DCM(
'user', iterate=True,
internal=is_superuser).sites().list(**kwargs).execute():
if int(site['accountId']) in accounts:
for contact in site.get('siteContacts', []):
SITE_CONTACTS.append([
site['accountId'],
site.get('subaccountId'),
site.get('directorySiteId'),
site['id'],
contact['id'],
contact['email'],
contact.get('firstName', ''),
contact.get('lastName', ''),
contact.get('title', ''),
contact.get('address', ''),
contact.get('phone', ''),
contact['contactType'],
])
yield [
site['accountId'],
site.get('subaccountId'),
site.get('directorySiteId'),
site['id'],
site['name'],
site['keyName'],
site['approved'],
site.get('orientation'),
site['siteSettings'].get('disableNewCookie'),
site['siteSettings'].get('activeViewOptOut'),
site['siteSettings'].get('adBlockingOptOut'),
site['siteSettings'].get('videoActiveViewOptOutTemplate'),
site['siteSettings'].get('vpaidAdapterChoiceTemplate'),
site['siteSettings'].get('tagSetting',
{}).get('additionalKeyValues'),
site['siteSettings'].get('tagSetting',
{}).get('includeClickTracking'),
site['siteSettings'].get(
'tagSetting', {}).get('includeClickThroughUrls'),
site['siteSettings'].get('tagSetting',
{}).get('keywordOption'),
]
def get_reports(accounts):
if project.verbose:
print('DCM Reports')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id,
'scope': 'ALL'
} if is_superuser else {
'profileId': profile_id,
'scope': 'ALL'
}
for report in API_DCM(
'user', iterate=True,
internal=is_superuser).reports().list(**kwargs).execute():
if int(report['accountId']) in accounts:
for delivery in report.get('delivery',
{}).get('recipients', []):
REPORT_DELIVERIES.append((
report['ownerProfileId'],
report['accountId'],
report.get('subaccountId'),
report['id'],
report['delivery']['emailOwner'],
report['delivery'].get('emailOwnerDeliveryType'),
report['delivery'].get('message', ''),
delivery['email'],
delivery['deliveryType'],
))
yield [
report['ownerProfileId'],
report['accountId'],
report.get('subaccountId'),
report['id'],
report['name'],
report['type'],
report.get('format'),
epoch_to_datetime(report.get('lastModifiedTime'), 1000),
report.get('criteria', {}).get('startDate'),
report.get('criteria', {}).get('endDate'),
report.get('criteria', {}).get('relativeDateRange'),
report.get('schedule', {}).get('active'),
report.get('schedule', {}).get('startDate'),
report.get('schedule', {}).get('expirationDate'),
report.get('schedule', {}).get('runsOnDayOfMonth'),
report.get('schedule', {}).get('repeats'),
report.get('schedule', {}).get('every'),
','.join(
report.get('schedule', {}).get('repeatsOnWeekDays',
[])),
]
def get_profiles(accounts):
if project.verbose: print 'DCM Profiles'
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'], account_id)
kwargs = { 'profileId':profile_id, 'accountId':account_id } if is_superuser else { 'profileId':profile_id }
for profile in API_DCM("user", iterate=True, internal=is_superuser).accountUserProfiles().list(**kwargs).execute():
if long(profile['accountId']) in accounts:
for campaign in profile.get('campaignFilter', {}).get('objectIds', []):
PROFILE_CAMPAIGNS.append([
profile['id'],
profile['accountId'],
profile.get('subaccountId'),
campaign,
])
for site in profile.get('siteFilter', {}).get('objectIds', []):
PROFILE_SITES.append([
profile['id'],
profile['accountId'],
profile.get('subaccountId'),
site,
])
for role in profile.get('userRoleFilter', {}).get('objectIds', []):
PROFILE_ROLES.append([
profile['id'],
profile['accountId'],
profile.get('subaccountId'),
role,
])
for advertiser in profile.get('advertiserFilter', {}).get('objectIds', []):
PROFILE_ADVERTISERS.append([
profile['id'],
profile['accountId'],
profile.get('subaccountId'),
advertiser,
])
yield [
profile['id'],
profile['accountId'],
profile.get('subaccountId'),
profile['name'],
profile['email'],
profile.get('locale'),
profile.get('userRoleId'),
profile.get('userAccessType'),
profile['active'],
profile.get('comments', ''),
profile.get('traffickerType'),
]
def get_subaccounts(accounts):
if project.verbose: print 'DCM SubAccounts'
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'], account_id)
kwargs = { 'profileId':profile_id, 'accountId':account_id } if is_superuser else { 'profileId':profile_id }
for subaccount in API_DCM("user", iterate=True, internal=is_superuser).subaccounts().list(**kwargs).execute():
if long(subaccount['accountId']) in accounts:
yield [
subaccount['accountId'],
subaccount['id'],
subaccount['name'],
]
def dcm_api_list(endpoint):
accounts = set(get_rows("user", project.task['accounts']))
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id
} if is_superuser else {
'profileId': profile_id
}
for item in API_DCM(
project.task['auth'], iterate=True,
internal=is_superuser).call(endpoint).list(**kwargs).execute():
yield item
def google_api_initilaize(api_call, alias=None):
if api_call['function'].endswith('list') or alias == 'list':
api_call['iterate'] = True
if api_call['api'] == 'dfareporting':
is_superuser, profile_id = get_profile_for_api(
api_call['auth'], api_call['kwargs']['accountId'])
api_call['kwargs']['profileId'] = profile_id
if is_superuser:
from starthinker.util.dcm.internalv33_uri import URI as DCM_URI
api_call['version'] = 'internalv3.3'
api_call['uri'] = DCM_URI
else:
del api_call['kwargs']['accountId']
def get_creatives(accounts):
if project.verbose: print 'DCM Ads'
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id
} if is_superuser else {
'profileId': profile_id
}
for ad in API_DCM(
"user", iterate=True,
internal=is_superuser).creatives().list(**kwargs).execute():
yield ad
def get_accounts(accounts):
if project.verbose: print 'DCM Accounts'
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(project.task['auth'], account_id)
kwargs = { 'profileId':profile_id, 'id':account_id }
account = API_DCM("user").accounts().get(**kwargs).execute()
yield [
account['id'],
account['name'],
account['active'],
account['description'],
id_to_timezone(account['reportsConfiguration']['reportGenerationTimeZoneId']),
account.get('currencyId'),
account.get('countryId'),
account['locale'],
account['nielsenOcrEnabled'],
account['shareReportsWithTwitter'],
]
def bulkdozer_test():
print('testing bulkdozer')
if 'verify' in project.task['traffic']:
is_admin, profile_id = get_profile_for_api(
project.task['auth'], project.task['traffic']['account_id'])
for entity in project.task['traffic']['verify']:
service = getattr(API_DCM(project.task['auth'], internal=is_admin),
entity['type'])
cm_entity = service().get(profileId=profile_id,
id=entity['id']).execute()
values = entity['values']
for key in values:
if values[key] != cm_entity[key]:
raise '%s %s expected to be %s, was %s' % (
entity['type'], key, values[key], cm_entity[key])
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 helper.py --account [id] --list -u [user credentials path]
To get report: python helper.py --account [id] --report [id] -u [user credentials path]
To get report files: python helper.py --account [id] --files [id] -u [user credentials path]
To get report sample: python helper.py --account [id] --sample [id] -u [user credentials path]
To get report schema: python helper.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
project.from_commandline(parser=parser, arguments=('-u', '-c', '-s', '-v'))
auth = 'service' if project.args.service else 'user'
is_superuser, profile = get_profile_for_api(auth, project.args.account)
kwargs = {
'profileId': profile,
'accountId': project.args.account
} if is_superuser else {
'profileId': profile
}
# get report list
if project.args.report:
kwargs['reportId'] = project.args.report
report = API_DCM(
auth, internal=is_superuser).reports().get(**kwargs).execute()
print(json.dumps(report, indent=2, sort_keys=True))
# get report files
elif project.args.files:
kwargs['reportId'] = project.args.files
for rf in API_DCM(
auth, internal=is_superuser).reports().files().list(**kwargs).execute():
print(json.dumps(rf, indent=2, sort_keys=True))
# get schema
elif project.args.schema:
filename, report = report_file(auth, project.args.account,
project.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 project.args.sample:
filename, report = report_file(auth, project.args.account,
project.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(
auth, internal=is_superuser).reports().list(**kwargs).execute():
print(json.dumps(report, indent=2, sort_keys=True))
def itp_audit_cm():
account_id = project.task['account']
# Read Advertiser Ids
advertiser_ids = list(
get_rows('service', project.task['read']['advertiser_ids']))
is_superuser, profile_id = get_profile_for_api('user', account_id)
datasets_create('user', project.id, project.task['dataset'])
placements = {}
campaignNames = {}
siteNames = {}
advertiserNames = {}
for c in API_DCM('user', iterate=True,
internal=is_superuser).campaigns().list(
archived=False,
profileId=profile_id,
accountId=account_id,
advertiserIds=advertiser_ids
if advertiser_ids else None).execute():
# only keep campaigns with end dates in the future or less than 90 days ago
if ((date.fromisoformat(c['endDate']) - date.today()).days > -90):
campaignNames[c['id']] = {
'id': c['id'],
'name': c['name'],
'startDate': c['startDate'],
'endDate': c['endDate']
}
validCampaignIds = [int(i) for i in campaignNames.keys()]
for s in API_DCM('user', iterate=True, internal=is_superuser).sites().list(
profileId=profile_id,
accountId=account_id,
campaignIds=validCampaignIds).execute():
siteNames[s['id']] = s['name']
for a in API_DCM('user', iterate=True,
internal=is_superuser).advertisers().list(
profileId=profile_id,
accountId=account_id,
ids=advertiser_ids[:500]
if advertiser_ids else None).execute():
advertiserNames[a['id']] = a['name']
if len(advertiser_ids) > 500:
for a in API_DCM('user', iterate=True,
internal=is_superuser).advertisers().list(
profileId=profile_id,
accountId=account_id,
ids=advertiser_ids[500:]
if advertiser_ids else None).execute():
advertiserNames[a['id']] = a['name']
for p in API_DCM(
'user', iterate=True, internal=is_superuser).placements().list(
archived=False,
profileId=profile_id,
accountId=account_id,
advertiserIds=advertiser_ids if advertiser_ids else None,
campaignIds=validCampaignIds).execute():
# exclude 1x1 tracking placements
if not (p['size']['height'] == 1 and p['size']['width'] == 1):
placements[p['id']] = {
'id': p['id'],
'name': p['name'],
'advertiserId': p['advertiserId'],
'advertiserName': advertiserNames[p['advertiserId']],
'campaignId': p['campaignId'],
'campaignName': campaignNames[p['campaignId']]['name'],
'siteId': p['siteId'],
'siteName': siteNames[p['siteId']],
'adsTotal': 0,
'adsNotImpacted': 0,
'adsFrequencyCapped': 0,
'adsAudienceSegmentation': 0,
'adsAudienceListTargeting': 0,
'adsDynamicCreative': 0,
'adsPlatformBrowserTargeting': 0
}
for ad in API_DCM('user', iterate=True, internal=is_superuser).ads().list(
type='AD_SERVING_STANDARD_AD',
profileId=profile_id,
accountId=account_id,
campaignIds=validCampaignIds).execute():
# only analyze standard, non-default priority ads that have been assigned 1+ placements and creatives
if ad['deliverySchedule'][
'priority'] != "AD_PRIORITY_16" and 'placementAssignments' in ad and 'creativeAssignments' in ad[
'creativeRotation']:
hasDynamicCreative = False
for creative in ad['creativeRotation']['creativeAssignments']:
if 'richMediaExitOverrides' in creative:
hasDynamicCreative = True
break
for p in ad['placementAssignments']:
if p['placementId'] in placements:
knownPlacement = placements[p['placementId']]
knownPlacement['adsTotal'] += 1
if 'frequencyCap' in ad['deliverySchedule']:
knownPlacement['adsFrequencyCapped'] += 1
if 'audienceSegmentId' in ad:
knownPlacement['adsAudienceSegmentation'] += 1
if 'remarketingListExpression' in ad:
knownPlacement['adsAudienceListTargeting'] += 1
if 'technologyTargeting' in ad and (
'browsers' in ad['technologyTargeting']
or 'platformTypes' in ad['technologyTargeting']):
knownPlacement['adsPlatformBrowserTargeting'] += 1
if hasDynamicCreative:
knownPlacement['adsDynamicCreative'] += 1
if not 'frequencyCap' in ad[
'deliverySchedule'] and not 'audienceSegmentId' in ad and not 'remarketingListExpression' in ad:
knownPlacement['adsNotImpacted'] += 1
write_data = []
for p in placements.values():
write_data.append([
p['name'] + ' - ' + str(p['id']), p['id'], p['name'],
p['advertiserName'] + ' - ' + str(p['advertiserId']),
p['advertiserId'], p['advertiserName'],
p['campaignName'] + ' - ' + str(p['campaignId']), p['campaignId'],
p['campaignName'], p['siteId'], p['siteName'], p['adsTotal'],
p['adsNotImpacted'], p['adsFrequencyCapped'],
p['adsAudienceListTargeting'], p['adsAudienceSegmentation'],
p['adsPlatformBrowserTargeting'], p['adsDynamicCreative']
])
placements_out = {}
placements_out["bigquery"] = {
"dataset": project.task['dataset'],
"table": CM_PLACEMENT_AUDIT_TABLE,
"is_incremental_load": False,
"datastudio": True,
"schema": PLACEMENTS_SCHEMA,
"skip_rows": 0
}
if placements: put_rows('service', placements_out, write_data)
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
project.from_commandline(parser=parser)
auth = 'service' if project.args.service else 'user'
is_superuser, profile = get_profile_for_api(auth, project.args.account)
kwargs = {
'profileId': profile,
'accountId': project.args.account
} if is_superuser else {
'profileId': profile
}
# get report list
if project.args.report:
kwargs['reportId'] = project.args.report
report = API_DCM(
auth, internal=is_superuser).reports().get(**kwargs).execute()
print json.dumps(report, indent=2, sort_keys=True)
# get report files