def get_changelogs(config, task, accounts, start):
if config.verbose:
print('CM CHANGE LOGS', accounts)
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(config, task['auth'],
account_id)
kwargs = {'profileId': profile_id, 'minChangeTime': start}
if is_superuser:
kwargs['accountId'] = account_id
for changelog in API_DCM(
config, '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 get_advertisers(config, task, accounts):
if config.verbose:
print('DCM Advertisers')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(config, task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id
} if is_superuser else {
'profileId': profile_id
}
for advertiser in API_DCM(
config, '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_sites(config, task, accounts):
if config.verbose:
print('DCM Sites')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(config, task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id
} if is_superuser else {
'profileId': profile_id
}
for site in API_DCM(
config, '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(config, task, accounts):
if config.verbose:
print('DCM Reports')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(config, 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(
config, '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 setup(config, task):
"""Sets up Bulkdozer configuration and required object to execute the job."""
if 'dcm_profile_id' not in task and 'account_id' in task:
task['is_admin'], task['dcm_profile_id'] = get_profile_for_api(
config, task['auth'], task['account_id'])
if 'is_admin' not in task:
task['is_admin'] = False
if 'sheet_url' in task and 'sheet_id' not in task:
task['sheet_id'] = task['sheet_url']
# Setting up required objects and parsing parameters
logger.config = config
logger.auth = task['auth']
logger.trix_id = task.get('logger', {}).get('sheet_id', task['sheet_id'])
logger.buffered = True
def load_multiple():
campaigns = [
str(lookup_id(r)) for r in set(
get_rows(config,
task['auth_cm'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'CM Campaigns',
'header': False,
'range': 'A2:A'
}
},
unnest=True))
]
for row in get_rows(
config, task['auth_sheets'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'CM Accounts',
'header': False,
'range': 'A2:A'
}
}):
if row:
account_id = lookup_id(row[0])
is_superuser, profile_id = get_profile_for_api(
config, task['auth_cm'], account_id)
kwargs = {
'profileId': profile_id,
'campaignIds': campaigns,
'archived': False
}
if is_superuser:
kwargs['accountId'] = account_id
yield from API_DCM(
config,
task['auth_cm'],
iterate=True,
internal=is_superuser).placementGroups().list(
**kwargs).execute()
def get_campaigns(config, task, accounts):
if config.verbose:
print('DCM Campaigns')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(config, task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id
} if is_superuser else {
'profileId': profile_id
}
for campaign in API_DCM(
config, 'user', iterate=True,
internal=is_superuser).campaigns().list(**kwargs).execute():
if int(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 bulkdozer_test(config, task):
print('testing bulkdozer')
if 'verify' in task['traffic']:
is_admin, profile_id = get_profile_for_api(
config, task['auth'], task['traffic']['account_id'])
for entity in task['traffic']['verify']:
service = getattr(API_DCM(config, 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 ValueError(
'%s %s expected to be %s, was %s' %
(entity['type'], key, values[key], cm_entity[key]))
def load_multiple():
for row in get_rows(
config,
task['auth_sheets'],
{ 'sheets': {
'sheet': task['sheet'],
'tab': 'CM Profiles',
'header':False,
'range': 'A2:A'
}}
):
if row:
account_id = lookup_id(row[0])
is_superuser, profile_id = get_profile_for_api(config, task['auth_cm'], account_id)
kwargs = { 'profileId': profile_id, 'accountId': account_id } if is_superuser else { 'profileId': profile_id }
yield from API_DCM(
config,
task['auth_cm'],
iterate=True,
internal=is_superuser
).accounts().list(**kwargs).execute()
def google_api_initilaize(config, 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':
if not api_call['function'].startswith('userProfiles'):
is_superuser, profile_id = get_profile_for_api(
config, api_call['auth'],
api_call['kwargs']['id'] if api_call['function']
== 'accounts.get' else api_call['kwargs']['accountId'])
api_call['kwargs']['profileId'] = profile_id
if is_superuser:
from starthinker.util.cm_internalv33_uri import URI as DCM_URI
api_call['version'] = 'internalv3.3'
api_call['uri'] = DCM_URI
elif 'accountId' in api_call['kwargs']:
del api_call['kwargs']['accountId']
def get_accounts(config, task, accounts):
if config.verbose:
print('DCM Accounts')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(config, task['auth'],
account_id)
kwargs = {'profileId': profile_id, 'id': account_id}
account = API_DCM(config, 'user').accounts().get(**kwargs).execute()
yield [
account['id'],
account['name'],
account['active'],
account.get('description', ''),
id_to_timezone(
account['reportsConfiguration']['reportGenerationTimeZoneId']),
account.get('currencyId'),
account.get('countryId'),
account['locale'],
account['nielsenOcrEnabled'],
account['shareReportsWithTwitter'],
]
def get_roles(config, task, accounts):
if config.verbose:
print('DCM Roles')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(config, task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id
} if is_superuser else {
'profileId': profile_id
}
for role in API_DCM(
config, 'user', iterate=True,
internal=is_superuser).userRoles().list(**kwargs).execute():
if int(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.get('availability'),
]
else:
yield [
role['accountId'],
role.get('subaccountId'),
role['id'],
role['name'],
role['defaultUserRole'],
None,
None,
]
def get_subaccounts(config, task, accounts):
if config.verbose:
print('DCM SubAccounts')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(config, task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id
} if is_superuser else {
'profileId': profile_id
}
for subaccount in API_DCM(
config, 'user', iterate=True,
internal=is_superuser).subaccounts().list(**kwargs).execute():
if int(subaccount['accountId']) in accounts:
yield [
subaccount['accountId'],
subaccount['id'],
subaccount['name'],
]
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 get_profiles(config, task, accounts):
if config.verbose:
print('DCM Profiles')
for account_id in accounts:
is_superuser, profile_id = get_profile_for_api(config, task['auth'],
account_id)
kwargs = {
'profileId': profile_id,
'accountId': account_id
} if is_superuser else {
'profileId': profile_id
}
for profile in API_DCM(
config, 'user', iterate=True,
internal=is_superuser).accountUserProfiles().list(
**kwargs).execute():
if int(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 itp_audit_cm(config, task):
account_id = task['account']
# Read Advertiser Ids
advertiser_ids = list(get_rows(config, task['auth_bq'], task['read']['advertiser_ids']))
is_superuser, profile_id = get_profile_for_api(config, task['auth_cm'], account_id)
datasets_create(config, task['auth_bq'], config.project, task['dataset'])
placements = {}
campaignNames = {}
siteNames = {}
advertiserNames = {}
for c in API_DCM(config, task['auth_cm'], 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(config, task['auth_cm'], 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(config, task['auth_cm'], 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(config, task['auth_cm'], 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(config, task['auth_cm'], 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(config, task['auth_cm'], 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": task['dataset'],
"table": CM_PLACEMENT_AUDIT_TABLE,
"is_incremental_load": False,
"datastudio": True,
"schema": PLACEMENTS_SCHEMA,
"skip_rows": 0
}
if placements: put_rows(config, task['auth_bq'], placements_out, write_data)
