def sdf():
if project.verbose:
print "SDF TO TABLE", project.task['out']['bigquery']['table']
# Set is time partition and write disposition
is_time_partition = project.task['out']['bigquery'].get(
'is_time_partition', False)
disposition = 'WRITE_TRUNCATE'
if is_time_partition:
disposition = 'WRITE_APPEND'
# Read Filter Ids
filter_id_rows = get_rows(project.task['auth'],
project.task['read']['filter_ids'])
filter_ids = [
filter_id_rows[i:i + FILTER_ID_CHUNK_SIZE]
for i in xrange(0, len(filter_id_rows), FILTER_ID_CHUNK_SIZE)
]
# Loop through requested file types
for file_type in project.task['file_types']:
current_filter_id_iteration = 0
i = 0
table_names = []
# Create the destination table
destination_table = '%s_%s' % (
project.task['out']['bigquery']['table'], file_type.lower())
create_table_if_not_exist(
project.task['auth'],
project.task['out']['bigquery']['project_id'],
project.task['out']['bigquery']['dataset'], destination_table,
is_time_partition)
# Request 5 filter ids at a time so the API doesn't timeout
for partial_filter_ids in filter_ids:
rows = sdf_read(project.task['auth'], [file_type],
project.task['filter_type'], partial_filter_ids)
if rows:
schema = _sdf_schema(rows.next)
table_suffix = '%s_%s' % (current_filter_id_iteration,
file_type.lower())
table_name = '%s%s' % (
project.task['out']['bigquery']['table'], table_suffix)
filename = '%s_%s.csv' % (file_type, project.date)
# Check to see if the table exists, if not create it
create_table_if_not_exist(
project.task['auth'],
project.task['out']['bigquery']['project_id'],
project.task['out']['bigquery']['dataset'], table_name)
if 'bigquery' in project.task['out']:
project.task['out']['bigquery']['schema'] = schema
project.task['out']['bigquery']['skip_rows'] = 0
put_rows(project.task['auth'],
project.task['out'],
filename,
rows,
variant=table_suffix)
table_names.append(table_name)
current_filter_id_iteration = current_filter_id_iteration + 1
query = _construct_combine_query(
file_type, table_names,
project.task['out']['bigquery']['project_id'],
project.task['out']['bigquery']['dataset'], destination_table)
query_to_table(project.task['auth'],
project.task['out']['bigquery']['project_id'],
project.task['out']['bigquery']['dataset'],
destination_table,
query,
disposition=disposition,
legacy=False)
# Delete all the temporary tables that were created
for table_name in table_names:
drop_table(project.task['auth'],
project.task['out']['bigquery']['project_id'],
project.task['out']['bigquery']['dataset'], table_name)
def bid_strategy_load():
# write bid_strategy to sheet
rows = get_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"query":
"""SELECT * FROM (
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId) AS IO_Display,
CAST(NULL AS STRING),
I.bidStrategy.fixedBid.bidAmountMicros / 100000,
I.bidStrategy.fixedBid.bidAmountMicros / 100000,
I.bidStrategy.maximizeSpendAutoBid.performanceGoalType,
I.bidStrategy.maximizeSpendAutoBid.performanceGoalType,
I.bidStrategy.maximizeSpendAutoBid.maxAverageCpmBidAmountMicros / 100000,
I.bidStrategy.maximizeSpendAutoBid.maxAverageCpmBidAmountMicros / 100000,
I.bidStrategy.maximizeSpendAutoBid.customBiddingAlgorithmId,
I.bidStrategy.maximizeSpendAutoBid.customBiddingAlgorithmId,
I.bidStrategy.performanceGoalAutoBid.performanceGoalType,
I.bidStrategy.performanceGoalAutoBid.performanceGoalType,
I.bidStrategy.performanceGoalAutoBid.performanceGoalAmountMicros / 100000,
I.bidStrategy.performanceGoalAutoBid.performanceGoalAmountMicros / 100000,
I.bidStrategy.performanceGoalAutoBid.maxAverageCpmBidAmountMicros / 100000,
I.bidStrategy.performanceGoalAutoBid.maxAverageCpmBidAmountMicros / 100000,
I.bidStrategy.performanceGoalAutoBid.customBiddingAlgorithmId,
I.bidStrategy.performanceGoalAutoBid.customBiddingAlgorithmId
FROM `{dataset}.DV_InsertionOrders` AS I
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON I.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON I.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
UNION ALL
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId) AS IO_Display,
CONCAT(L.displayName, ' - ', L.lineItemId),
L.bidStrategy.fixedBid.bidAmountMicros / 100000,
L.bidStrategy.fixedBid.bidAmountMicros / 100000,
L.bidStrategy.maximizeSpendAutoBid.performanceGoalType,
L.bidStrategy.maximizeSpendAutoBid.performanceGoalType,
L.bidStrategy.maximizeSpendAutoBid.maxAverageCpmBidAmountMicros / 100000,
L.bidStrategy.maximizeSpendAutoBid.maxAverageCpmBidAmountMicros / 100000,
L.bidStrategy.maximizeSpendAutoBid.customBiddingAlgorithmId,
L.bidStrategy.maximizeSpendAutoBid.customBiddingAlgorithmId,
L.bidStrategy.performanceGoalAutoBid.performanceGoalType,
L.bidStrategy.performanceGoalAutoBid.performanceGoalType,
L.bidStrategy.performanceGoalAutoBid.performanceGoalAmountMicros / 100000,
L.bidStrategy.performanceGoalAutoBid.performanceGoalAmountMicros / 100000,
L.bidStrategy.performanceGoalAutoBid.maxAverageCpmBidAmountMicros / 100000,
L.bidStrategy.performanceGoalAutoBid.maxAverageCpmBidAmountMicros / 100000,
L.bidStrategy.performanceGoalAutoBid.customBiddingAlgorithmId,
L.bidStrategy.performanceGoalAutoBid.customBiddingAlgorithmId
FROM `{dataset}.DV_LineItems` AS L
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON L.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_InsertionOrders` AS I
ON L.insertionOrderId=I.insertionOrderId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON L.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId )
ORDER BY IO_Display
""".format(**project.task),
"legacy":
False
}
})
put_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Bid Strategy",
"range": "A2:U"
}
}, rows)
def dv_algorithm_load(config, task):
# load multiple partners from user defined sheet
def load_multiple():
for row in get_rows(
config, task['auth_sheets'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'DV Partners',
'header': False,
'range': 'A2:A'
}
}):
if row:
yield from API_DV360(
config, task['auth_dv'],
iterate=True).customBiddingAlgorithms().list(
partnerId=lookup_id(row[0])).execute()
dv_algorithm_clear(config, task)
# write algorithms to database
put_rows(
config, task['auth_bigquery'], {
'bigquery': {
'dataset':
task['dataset'],
'table':
'DV_Algorithms',
'schema':
Discovery_To_BigQuery(
'displayvideo',
'v1').method_schema('customBiddingAlgorithms.list'),
'format':
'JSON'
}
}, load_multiple())
# write algorithms to sheet
put_rows(
config, task['auth_sheets'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'DV Algorithms',
'range': 'B2'
}
},
get_rows(
config, task['auth_bigquery'], {
'bigquery': {
'dataset':
task['dataset'],
'query':
"""SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(B.displayName, ' - ', B.customBiddingAlgorithmId),
B.entityStatus
FROM `{dataset}.DV_Algorithms` AS B
LEFT JOIN `{dataset}.DV_Partners` AS P
ON B.partnerId=P.partnerId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON B.advertiserId=A.advertiserId
""".format(**task),
'legacy':
False
}
}))
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 and sheet
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())
# write campaigns to sheet
rows = get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"""SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
C.entityStatus
FROM `{dataset}.DV_Campaigns` AS C
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON C.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
ORDER BY C.displayName
""".format(**project.task),
'legacy':
False
}
})
put_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Campaigns',
'range': 'B2'
}
}, rows)
def bigquery():
if 'run' in project.task and 'query' in project.task.get('run', {}):
if project.verbose: print("QUERY", project.task['run']['query'])
run_query(
project.task['auth'],
project.id,
project.task['run']['query'],
project.task['run'].get('legacy', True),
#project.task['run'].get('billing_project_id', None)
)
elif 'values' in project.task['from']:
rows = get_rows(project.task['auth'], project.task['from'])
rows_to_table(project.task['to'].get('auth', project.task['auth']),
project.id, project.task['to']['dataset'],
project.task['to']['table'], rows,
project.task.get('schema', []), 0)
elif 'query' in project.task['from']:
if 'table' in project.task['to']:
if project.verbose:
print("QUERY TO TABLE", project.task['to']['table'])
if 'pre_process_query' in project.task['to']:
print('executing statement')
execute_statement(project.task['auth'],
project.id,
project.task['to']['dataset'],
project.task['to']['pre_process_query'],
use_legacy_sql=project.task['from'].get(
'legacy', project.task['from'].get(
'useLegacySql', True)))
query_to_table(
project.task['auth'],
project.id,
project.task['to']['dataset'],
project.task['to']['table'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
disposition=project.task['write_disposition']
if 'write_disposition' in project.task else 'WRITE_TRUNCATE',
legacy=project.task['from'].get(
'legacy', project.task['from'].get(
'useLegacySql', True)), # DEPRECATED: useLegacySql,
target_project_id=project.task['to'].get(
'project_id', project.id))
# NOT USED SO RIPPING IT OUT
# Mauriciod: Yes, it is used, look at project/mauriciod/target_winrate.json
elif 'storage' in project.task['to']:
if project.verbose:
print("QUERY TO STORAGE", project.task['to']['storage'])
local_file_name = '/tmp/%s' % str(uuid.uuid1())
rows = query_to_rows(project.task['auth'], project.id,
project.task['from']['dataset'],
project.task['from']['query'])
f = open(local_file_name, 'wb')
writer = csv.writer(f)
writer.writerows(rows)
f.close()
f = open(local_file_name, 'rb')
object_put(project.task['auth'], project.task['to']['storage'], f)
f.close()
os.remove(local_file_name)
elif 'sheet' in project.task['to']:
if project.verbose:
print("QUERY TO SHEET", project.task['to']['sheet'])
rows = query_to_rows(project.task['auth'],
project.id,
project.task['from']['dataset'],
project.task['from']['query'],
legacy=project.task['from'].get(
'legacy', True))
# makes sure types are correct in sheet
rows = rows_to_type(rows)
sheets_clear(project.task['auth'], project.task['to']['sheet'],
project.task['to']['tab'],
project.task['to'].get('range', 'A2'))
sheets_write(project.task['auth'], project.task['to']['sheet'],
project.task['to']['tab'],
project.task['to'].get('range', 'A2'), rows)
elif 'sftp' in project.task['to']:
rows = query_to_rows(project.task['auth'],
project.id,
project.task['from']['dataset'],
project.task['from']['query'],
legacy=project.task['from'].get(
'use_legacy_sql', True))
if rows:
if project.verbose: print("QUERY TO SFTP")
put_rows(project.task['auth'], project.task['to'], rows)
else:
if project.verbose:
print("QUERY TO VIEW", project.task['to']['view'])
query_to_view(
project.task['auth'],
project.id,
project.task['to']['dataset'],
project.task['to']['view'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
project.task['from'].get(
'legacy', project.task['from'].get(
'useLegacySql', True)), # DEPRECATED: useLegacySql
project.task['to'].get('replace', False))
else:
if project.verbose:
print("STORAGE TO TABLE", project.task['to']['table'])
storage_to_table(
project.task['auth'], project.id, project.task['to']['dataset'],
project.task['to']['table'], project.task['from']['bucket'] + ':' +
project.task['from']['path'], project.task.get('schema', []),
project.task.get('skip_rows', 1),
project.task.get('structure', 'CSV'),
project.task.get('disposition', 'WRITE_TRUNCATE'))
def creative_load(config, task):
# load multiple partners from user defined sheet
def creative_load_multiple():
rows = get_rows(
config, task["auth_sheets"], {
"sheets": {
"sheet": task["sheet"],
"tab": "Advertisers",
"header": False,
"range": "A2:A"
}
})
for row in rows:
yield from API_DV360(
config, task["auth_dv"], iterate=True
).advertisers().creatives().list(
advertiserId=lookup_id(row[0]),
filter='entityStatus="ENTITY_STATUS_ACTIVE"',
fields=
'creatives.displayName,creatives.creativeId,creatives.entityStatus,creatives.creativeType,creatives.dimensions,creatives.reviewStatus,nextPageToken'
).execute(limit=CREATIVE_COUNT_LIMIT)
# write creatives to database and sheet
put_rows(
config, task["auth_bigquery"], {
"bigquery": {
"dataset":
task["dataset"],
"table":
"DV_Creatives",
"schema":
Discovery_To_BigQuery(
"displayvideo",
"v1").method_schema("advertisers.creatives.list"),
"format":
"JSON"
}
}, creative_load_multiple())
# write creatives to sheet
rows = get_rows(
config, task["auth_bigquery"], {
"bigquery": {
"dataset":
task["dataset"],
"query":
"""SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.creativeId),
C.entityStatus,
C.creativeType,
C.dimensions.widthPixels,
C.dimensions.heightPixels,
C.reviewStatus.approvalStatus,
C.reviewStatus.creativeAndLandingPageReviewStatus,
C.reviewStatus.contentAndPolicyReviewStatus,
COUNTIF(RS.status='REVIEW_STATUS_UNSPECIFIED') OVER() AS Exchanges_Unspecified,
COUNTIF(RS.status='REVIEW_STATUS_PENDING') OVER() AS Exchanges_Pending,
COUNTIF(RS.status='REVIEW_STATUS_REJECTED') OVER() AS Exchanges_Rejected,
COUNTIF(RS.status='REVIEW_STATUS_APPROVED') OVER() AS Exchanges_Approved,
COUNTIF(RP.status='REVIEW_STATUS_UNSPECIFIED') OVER() AS Publishers_Unspecified,
COUNTIF(RP.status='REVIEW_STATUS_PENDING') OVER() AS Publishers_Pending,
COUNTIF(RP.status='REVIEW_STATUS_REJECTED') OVER() AS Publishers_Rejected,
COUNTIF(RP.status='REVIEW_STATUS_APPROVED') OVER() AS Publishers_Approved,
FROM `{dataset}.DV_Creatives` AS C, UNNEST(reviewStatus.exchangeReviewStatuses) AS RS, UNNEST(reviewStatus.publisherReviewStatuses) AS RP
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON C.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
""".format(**task),
"legacy":
False
}
})
put_rows(
config, task["auth_sheets"], {
"sheets": {
"sheet": task["sheet"],
"tab": "Creatives",
"header": False,
"range": "B2"
}
}, rows)
def frequency_cap_load(config, task):
# write frequency_caps to sheet
rows = get_rows(
config,
task["auth_bigquery"], {
"bigquery": {
"dataset":
task["dataset"],
"query":
"""SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CAST(NULL AS STRING),
CAST(NULL AS STRING),
IFNULL(C.frequencyCap.unlimited, FALSE),
IFNULL(C.frequencyCap.unlimited, FALSE),
C.frequencyCap.timeUnit,
C.frequencyCap.timeUnit,
C.frequencyCap.timeUnitCount,
C.frequencyCap.timeUnitCount,
C.frequencyCap.maxImpressions,
C.frequencyCap.maxImpressions
FROM `{dataset}.DV_Campaigns` AS C
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON C.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
UNION ALL
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
CAST(NULL AS STRING),
IFNULL(I.frequencyCap.unlimited, FALSE),
IFNULL(I.frequencyCap.unlimited, FALSE),
I.frequencyCap.timeUnit,
I.frequencyCap.timeUnit,
I.frequencyCap.timeUnitCount,
I.frequencyCap.timeUnitCount,
I.frequencyCap.maxImpressions,
I.frequencyCap.maxImpressions
FROM `{dataset}.DV_InsertionOrders` AS I
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON I.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON I.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
UNION ALL
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
CONCAT(L.displayName, ' - ', L.lineItemId),
IFNULL(L.frequencyCap.unlimited, FALSE),
IFNULL(L.frequencyCap.unlimited, FALSE),
L.frequencyCap.timeUnit,
L.frequencyCap.timeUnit,
L.frequencyCap.timeUnitCount,
L.frequencyCap.timeUnitCount,
L.frequencyCap.maxImpressions,
L.frequencyCap.maxImpressions
FROM `{dataset}.DV_LineItems` AS L
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON L.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_InsertionOrders` AS I
ON L.insertionOrderId=I.insertionOrderId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON L.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
""".format(**task),
"legacy":
False
}
})
put_rows(
config,
task["auth_sheets"], {
"sheets": {
"sheet": task["sheet"],
"tab": "Frequency Caps",
"header":False,
"range": "A2"
}
}, rows)
def audit_load():
bid_strategy_audit()
integration_detail_audit()
insertion_order_audit()
frequency_cap_audit()
line_item_audit()
line_item_map_audit()
pacing_audit()
partner_cost_audit()
segment_audit()
# write audit to sheet
sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Audit',
'A2')
# write audits to sheet
rows = get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"""SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_InsertionOrders`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_Segments`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_LineItems`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_LineItemMaps`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_Pacing`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_BidStrategy`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_FrequencyCaps`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_PartnerCosts`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_IntegrationDetails`
""".format(**project.task),
'legacy':
False
}
})
put_rows(project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Audit',
'range': 'A2'
}
}, rows)
def insertion_order_audit(config, task):
# Move Insertion Order To BigQuery
rows = get_rows(
config,
task["auth_sheets"], {
"sheets": {
"sheet": task["sheet"],
"tab": "Insertion Orders",
"header":False,
"range": "A2:U"
}
}
)
put_rows(
config,
task["auth_bigquery"], {
"bigquery": {
"dataset": task["dataset"],
"table": "SHEET_InsertionOrders",
"schema": [
{ "name": "Partner", "type": "STRING" },
{ "name": "Advertiser", "type": "STRING" },
{ "name": "Campaign", "type": "STRING" },
{ "name": "Insertion_Order", "type": "STRING" },
{ "name": "Action", "type": "STRING" },
{ "name": "Status", "type": "STRING" },
{ "name": "Status_Edit", "type": "STRING" },
{ "name": "Name", "type": "STRING" },
{ "name": "Name_Edit", "type": "STRING" },
{ "name": "Budget_Unit", "type": "STRING" },
{ "name": "Budget_Unit_Edit", "type": "STRING" },
{ "name": "Budget_Automation", "type": "STRING" },
{ "name": "Budget_Automation_Edit", "type": "STRING" },
{ "name": "Performance_Goal_Type", "type": "STRING" },
{ "name": "Performance_Goal_Type_Edit", "type": "STRING" },
{ "name": "Performance_Goal_Amount", "type": "FLOAT" },
{ "name": "Performance_Goal_Amount_Edit", "type": "FLOAT" },
{ "name": "Performance_Goal_Percent", "type": "FLOAT" },
{ "name": "Performance_Goal_Percent_Edit", "type": "FLOAT" },
{ "name": "Performance_Goal_String", "type": "STRING" },
{ "name": "Performance_Goal_String_Edit", "type": "STRING" },
],
"format": "CSV"
}
},
rows
)
# Create Insert View
query_to_view(
config,
task["auth_bigquery"],
config.project,
task["dataset"],
"INSERT_InsertionOrders",
"""SELECT
REGEXP_EXTRACT(S_IO.Advertiser, r' - (\d+)$') AS advertiserId,
REGEXP_EXTRACT(S_IO.Campaign, r' - (\d+)$') AS campaignId,
S_IO.Insertion_Order AS displayName,
S_IO.Status_Edit AS entityStatus,
STRUCT(
S_PC.Cost_Type_Edit As costType,
S_PC.Fee_Type_Edit As feeType,
S_PC.Invoice_Type_Edit AS invoiceType,
S_PC.Fee_Amount_Edit AS feeAmount,
S_PC.Fee_Percent_Edit * 1000 AS feePercentageMillis
) AS partnerCosts,
STRUCT(
S_P.Period_Edit As pacingPeriod,
S_P.Type_Edit As pacingType,
S_P.Daily_Budget_Edit AS dailyMaxMicros,
S_P.Daily_Impressions_Edit AS dailyMaxImpressions
) AS pacing,
STRUCT(
S_FC.Unlimited_Edit AS unlimited,
S_FC.Time_Unit_Edit AS timeUnit,
S_FC.Time_Count_Edit AS timeUnitCount,
S_FC.Max_impressions_Edit AS maxImpressions
) AS frequencyCap,
STRUCT(
S_ID.Integration_Code_Edit As integrationCode,
S_ID.Details_Edit As details
) AS integrationDetails,
STRUCT(
S_IO.Performance_Goal_Type_Edit AS performanceGoalType,
S_IO.Performance_Goal_Amount_Edit * 1000000 AS performanceGoalAmountMicros,
S_IO.Performance_Goal_Percent_Edit * 1000000 AS performanceGoalPercentageMicros,
S_IO.Performance_Goal_String_Edit AS performanceGoalString
) AS performanceGoal,
STRUCT(
S_IO.Budget_Unit_Edit AS budgetUnit,
S_IO.Budget_Automation_Edit AS automationType,
(SELECT ARRAY(
SELECT
STRUCT(
S_S.Budget_Edit * 1000000 AS budgetAmountMicros,
S_S.Description_Edit AS description,
STRUCT (
STRUCT (
EXTRACT(YEAR FROM CAST(S_S.Start_Date_Edit AS Date)) AS year,
EXTRACT(MONTH FROM CAST(S_S.Start_Date_Edit AS DATE)) AS month,
EXTRACT(DAY FROM CAST(S_S.Start_Date_Edit AS DATE)) AS day
) AS startDate,
STRUCT (
EXTRACT(YEAR FROM CAST(S_S.End_Date_Edit AS Date)) AS year,
EXTRACT(MONTH FROM CAST(S_S.End_Date_Edit AS DATE)) AS month,
EXTRACT(DAY FROM CAST(S_S.End_Date_Edit AS DATE)) AS day
) AS endDate
) AS dateRange
) AS budgetSegments
FROM `{dataset}.SHEET_Segments` AS S_S
WHERE S_IO.Insertion_Order=S_S.Insertion_Order
)) AS budgetSegments
) AS budget,
STRUCT(
IF(S_BS.Fixed_Bid_Edit IS NOT NULL,
STRUCT(
S_BS.Fixed_Bid_Edit * 1000000 AS bidAmountMicros
),
NULL
) AS fixedBid,
IF(S_BS.Auto_Bid_Goal_Edit IS NOT NULL,
STRUCT(
S_BS.Auto_Bid_Goal_Edit AS performanceGoalType,
S_BS.Auto_Bid_Amount_Edit * 1000000 AS maxAverageCpmBidAmountMicros,
S_BS.Auto_Bid_Algorithm_Edit AS customBiddingAlgorithmId
),
NULL
) AS maximizeSpendAutoBid,
IF(S_BS.Performance_Goal_Type_Edit IS NOT NULL,
STRUCT(
S_BS.Performance_Goal_Type_Edit AS performanceGoalType,
S_BS.Performance_Goal_Amount_Edit * 1000000 AS performanceGoalAmountMicros,
S_BS.Performance_Goal_Average_CPM_Bid_Edit * 1000000 AS maxAverageCpmBidAmountMicros,
S_BS.Performance_Goal_Algorithm_Edit AS customBiddingAlgorithmId
),
NULL
) AS performanceGoalAutoBid
)
AS bidStrategy
FROM `{dataset}.SHEET_InsertionOrders` As S_IO
LEFT JOIN `{dataset}.SHEET_Segments` As S_S ON S_IO.Insertion_Order=S_S.Insertion_Order
LEFT JOIN `{dataset}.SHEET_PartnerCosts` As S_PC ON S_IO.Insertion_Order=S_PC.Insertion_Order
LEFT JOIN `{dataset}.SHEET_Pacing` As S_P ON S_IO.Insertion_Order=S_P.Insertion_Order
LEFT JOIN `{dataset}.SHEET_FrequencyCaps` As S_FC ON S_IO.Insertion_Order=S_FC.Insertion_Order
LEFT JOIN `{dataset}.SHEET_IntegrationDetails` As S_ID ON S_IO.Insertion_Order=S_ID.Insertion_Order
LEFT JOIN `{dataset}.SHEET_BidStrategy` As S_BS ON S_IO.Insertion_Order=S_BS.Insertion_Order
LEFT JOIN `{dataset}.DV_InsertionOrders` As DV_IO ON S_IO.Insertion_Order=DV_IO.displayName
WHERE S_IO.Action="INSERT"
AND DV_IO IS NULL
""".format(**task),
legacy=False
)
# Create Audit View And Write To Sheets
query_to_view(
config,
task["auth_bigquery"],
config.project,
task["dataset"],
"AUDIT_InsertionOrders",
"""WITH
/* Check if sheet values are set */
INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Insertion Order' AS Operation,
CASE
WHEN Name_Edit IS NULL THEN 'Missing Name.'
WHEN Budget_Unit_Edit IS NULL THEN 'Missing Budget Unit.'
WHEN Budget_Automation_Edit IS NULL THEN 'Missing Budget Automation.'
WHEN Performance_Goal_Type_Edit IS NULL THEN 'Missing Goal Type.'
WHEN Performance_Goal_Amount_Edit IS NULL AND Performance_Goal_Percent_Edit IS NULL AND Performance_Goal_String_Edit IS NULL THEN 'Missing Goal Amount / Percent / String.'
WHEN Performance_Goal_Amount_Edit IS NOT NULL
AND Performance_Goal_Percent_Edit IS NOT NULL
AND Performance_Goal_String_Edit IS NOT NULL THEN 'Amount / Percent / String all exist when there can only be 1.'
WHEN Performance_Goal_Amount_Edit IS NOT NULL AND Performance_Goal_Percent_Edit IS NOT NULL THEN 'Cannot have both Goal Amount and Percent'
WHEN Performance_Goal_Amount_Edit IS NOT NULL
AND Performance_Goal_String_Edit IS NOT NULL THEN 'Cannot have both Goal Amount and String'
WHEN Performance_Goal_Percent_Edit IS NOT NULL AND Performance_Goal_String_Edit IS NOT NULL THEN 'Cannot have both Percent Amount and String'
ELSE
NULL
END AS Error,
'ERROR' AS Severity,
COALESCE(Insertion_Order, 'BLANK') AS Id
FROM
`{dataset}.SHEET_InsertionOrders`
)
WHERE
Error IS NOT NULL
),
/* Check duplicate inserts */
DUPLICATE_ERRORS AS (
SELECT
'Insertion_Order' AS Operation,
'Duplicate Insertion Order name, insert will be ignored.' AS Error,
'WARNING' AS Severity,
COALESCE(S_IO.Insertion_Order, 'BLANK') AS Id
FROM `{dataset}.SHEET_InsertionOrders` As S_IO
LEFT JOIN `{dataset}.DV_InsertionOrders` AS DV_IO ON S_IO.Insertion_Order=DV_IO.displayName
WHERE S_IO.Action="INSERT"
AND DV_IO IS NOT NULL
)
SELECT * FROM INPUT_ERRORS
UNION ALL
SELECT * FROM DUPLICATE_ERRORS
;
""".format(**task),
legacy=False
)
query_to_view(
config,
task["auth_bigquery"],
config.project,
task["dataset"],
"PATCH_InsertionOrders",
"""SELECT *
FROM `{dataset}.SHEET_InsertionOrders`
WHERE Insertion_Order NOT IN (SELECT Id FROM `{dataset}.AUDIT_InsertionOrders` WHERE Severity='ERROR')
""".format(**task),
legacy=False
)
def google_audience_load():
# load multiple from user defined sheet
def load_multiple():
partners = get_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Partners',
'header': False,
'range': 'A2:A'
}
})
for partner in partners:
yield from API_DV360(
project.task['auth_dv'], iterate=True).googleAudiences().list(
partnerId=lookup_id(partner[0])).execute()
advertisers = get_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Advertisers',
'header': False,
'range': 'A2:A'
}
})
for advertiser in advertisers:
yield from API_DV360(
project.task['auth_dv'], iterate=True).googleAudiences().list(
advertiserId=lookup_id(advertiser[0])).execute()
google_audience_clear()
# write to database
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'table':
'DV_Google_Audiences',
'schema':
Discovery_To_BigQuery(
'displayvideo',
'v1').method_schema('googleAudiences.list'),
'format':
'JSON'
}
}, load_multiple())
# write to sheet
put_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Targeting Options',
'header': False,
'range': 'N2:N'
}
},
get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"""SELECT
CONCAT(SUBSTR(I.googleAudienceType, 22), ': ', I.displayName, ' - ', I.googleAudienceId)
FROM `{dataset}.DV_Google_Audiences` AS I
ORDER BY 1
""".format(**project.task),
'legacy':
False
}
}))
def dcm():
if project.verbose: print('DCM')
# stores existing report json
report = None
# check if report is to be deleted
if project.task.get('delete', False):
if project.verbose: print('DCM DELETE', project.task['report'].get('name', None) or project.task['report'].get('body', {}).get('name', None) or project.task['report'].get('report_id', None))
report_delete(
project.task['auth'],
project.task['report']['account'],
project.task['report'].get('report_id', None),
project.task['report'].get('name', None) or project.task['report'].get('body', {}).get('name', None),
)
# check if report is to be run
if project.task.get('report_run_only', False):
if project.verbose: print('DCM REPORT RUN', project.task['report'].get('name', None) or project.task['report'].get('report_id', None))
report_run(
project.task['auth'],
project.task['report']['account'],
project.task['report'].get('report_id', None),
project.task['report'].get('name', None),
)
# check if report is to be created
if 'body' in project.task['report']:
if project.verbose: print('DCM BUILD', project.task['report']['body']['name'])
if 'filters' in project.task['report']:
project.task['report']['body'] = report_filter(
project.task['auth'],
project.task['report']['body'],
project.task['report']['filters']
)
report = report_build(
project.task['auth'],
project.task['report']['body'].get('accountId') or project.task['report']['account'],
project.task['report']['body']
)
# moving a report
if 'out' in project.task:
filename, report = report_file(
project.task['auth'],
project.task['report']['account'],
project.task['report'].get('report_id', None),
project.task['report'].get('name', None) or project.task['report'].get('body', {}).get('name', None),
project.task['report'].get('timeout', 10),
)
if report:
if project.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 project.task['out']:
schema = report_schema(next(rows))
project.task['out']['bigquery']['schema'] = schema
project.task['out']['bigquery']['skip_rows'] = 0
# write rows using standard out block in json ( allows customization across all scripts )
if rows: put_rows(project.task['auth'], project.task['out'], rows)
def monthly_budget_mover():
if project.verbose:
print('MONTHLY BUDGET MOVER')
# Get Spend report
r = report_get(project.task['auth'], name=project.task['report_name'])
report_id = report_get(project.task['auth'],
name=project.task['report_name'])['queryId']
# report = report_to_list(project.task['auth'], report_id)
# Get Insertion Order SDF
# sdf = list(get_single_sdf_rows(
# project.task['auth'],
# project.task['sdf']['version'],
# project.task['sdf']['partner_id'],
# project.task['sdf']['file_types'],
# project.task['sdf']['filter_type'],
# project.task['sdf']['read']['filter_ids'],
# 'InsertionOrders'))
# print('sdf============================================================')
# print(sdf)
# print('sdf============================================================')
sdf = [
[
'Io Id', 'Campaign Id', 'Name', 'Timestamp', 'Status', 'Io Type',
'Billable Outcome', 'Fees', 'Integration Code', 'Details',
'Pacing', 'Pacing Rate', 'Pacing Amount', 'Frequency Enabled',
'Frequency Exposures', 'Frequency Period', 'Frequency Amount',
'Performance Goal Type', 'Performance Goal Value', 'Measure DAR',
'Measure DAR Channel', 'Budget Type', 'Budget Segments',
'Auto Budget Allocation', 'Geography Targeting - Include',
'Geography Targeting - Exclude', 'Language Targeting - Include',
'Language Targeting - Exclude', 'Device Targeting - Include',
'Device Targeting - Exclude', 'Browser Targeting - Include',
'Browser Targeting - Exclude', 'Digital Content Labels - Exclude',
'Brand Safety Sensitivity Setting', 'Brand Safety Custom Settings',
'Third Party Verification Services',
'Third Party Verification Labels', 'Channel Targeting - Include',
'Channel Targeting - Exclude', 'Site Targeting - Include',
'Site Targeting - Exclude', 'App Targeting - Include',
'App Targeting - Exclude', 'App Collection Targeting - Include',
'App Collection Targeting - Exclude',
'Category Targeting - Include', 'Category Targeting - Exclude',
'Keyword Targeting - Include', 'Keyword Targeting - Exclude',
'Keyword List Targeting - Exclude',
'Audience Targeting - Similar Audiences',
'Audience Targeting - Include', 'Audience Targeting - Exclude',
'Affinity & In Market Targeting - Include',
'Affinity & In Market Targeting - Exclude',
'Custom List Targeting',
'Inventory Source Targeting - Authorized Seller Options',
'Inventory Source Targeting - Include',
'Inventory Source Targeting - Exclude',
'Inventory Source Targeting - Target New Exchanges',
'Daypart Targeting', 'Daypart Targeting Time Zone',
'Environment Targeting', 'Viewability Targeting Active View',
'Position Targeting - Display On Screen',
'Position Targeting - Video On Screen',
'Position Targeting - Display Position In Content',
'Position Targeting - Video Position In Content',
'Position Targeting - Audio Position In Content',
'Video Player Size Targeting', 'Demographic Targeting Gender',
'Demographic Targeting Age',
'Demographic Targeting Household Income',
'Demographic Targeting Parental Status',
'Connection Speed Targeting', 'Carrier Targeting - Include',
'Carrier Targeting - Exclude', 'Insertion Order Optimization',
'Bid Strategy Unit', 'Bid Strategy Do Not Exceed',
'Apply Floor Price For Deals', 'Algorithm Id'
],
[
'3489402', '294948', 'Audit_creas_Mustang',
'2020-03-06T22:04:11.821000', 'Paused', 'Standard', 'Impression',
'(Media; 0.0; Display & Video 360 Fee; True;);', '', '', 'Flight',
'Even', '0', 'True', '1', 'Lifetime', '0', 'None', '0', 'False',
'', 'Amount',
'(12000.0; 05/01/2020; 05/31/2020;);(12000.0; 06/01/2020; 06/30/2020;);',
'False', '', '', '', '', '2; 502; 202; 302;', '', '', '', '',
'Use custom',
'Adult; Alcohol; Derogatory; Downloads & Sharing; Drugs; Gambling; '
'Profanity; Religion; Sensitive social issues; Suggestive; Tobacco; '
'Tragedy; Transportation Accidents; Violence; Weapons;', 'None',
'', '',
'2203109; 2998109; 2998110; 2998111; 2998112; 2998113; 2998114;',
'', '', '', '', '', '', '', '', '', '', '', 'False', '', '', '',
'', '', 'Authorized and Non-Participating Publisher',
'1; 6; 8; 9; 10; 2; 11; 12; 13; 16; 17; 19; 20; 21; 23; 27; 31; 34; '
'36; 37; 38; 41; 42; 43; 50; 52; 60;', '', 'True', '', '',
'Web; App;', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', 'False', '', '', '', ''
],
[
'3502002', '294948', 'FR_Mindshare_Ford_Mustang_CTX_VOL_Avril17',
'2020-03-06T12:07:04.366000', 'Paused', 'Standard', 'Impression',
'(Media; 0.0; Display & Video 360 Fee; True;);', '', '', 'Flight',
'Even', '0', 'False', '0', 'Minutes', '0', 'None', '0', 'False',
'', 'Amount',
'(12000.0; 05/01/2020; 05/31/2020;);(12000.0; 06/01/2020; 06/30/2020;);',
'False', '2250;', '', '', '', '2;', '502; 202; 302;', '', '', '',
'Use custom',
'Adult; Alcohol; Derogatory; Downloads & Sharing; Drugs; Gambling; '
'Politics; Profanity; Religion; Sensitive social issues; Suggestive;'
' Tobacco; Tragedy; Transportation Accidents; Violence; Weapons;',
'None', '', '',
'2203109; 2998109; 2998110; 2998111; 2998112; 2998113; 2998114;',
'', '', '', '', '', '', '', '', '', '', '', 'False', '', '', '',
'', '', 'Authorized and Non-Participating Publisher',
'1; 6; 8; 9; 10; 2; 11; 12; 13; 16; 17; 19; 20; 21; 23; 27; 31; 34; '
'36; 37; 38; 41; 42; 43; 50; 52; 60;', '', 'True', '', '',
'Web; App;', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', 'False', '', '', '', ''
],
[
'3522675', '294948', 'FR_Mindshare_Ford_Sales_Juin_CTX_VOL_Juin17',
'2020-03-06T10:44:48.709000', 'Paused', 'Standard', 'Impression',
'(Media; 0.0; Display & Video 360 Fee; True;);', '', '', 'Flight',
'Ahead', '0', 'False', '0', 'Minutes', '0', 'None', '0', 'False',
'', 'Amount',
'(12000.0; 05/01/2020; 05/31/2020;);(12000.0; 06/01/2020; 06/30/2020;);',
'False', '2250;', '', '', '', '2;', '502; 202; 302;', '', '', '',
'Use custom',
'Adult; Alcohol; Derogatory; Downloads & Sharing; Drugs; Gambling; '
'Profanity; Religion; Sensitive social issues; Suggestive; Tobacco; '
'Tragedy; Transportation Accidents; Violence; Weapons;', 'None',
'', '',
'2203109; 2998109; 2998110; 2998111; 2998112; 2998113; 2998114;',
'', '', '', '', '', '', '', '', '', '', '', 'False', '', '', '',
'', '', 'Authorized and Non-Participating Publisher',
'1; 6; 8; 9; 10; 2; 11; 12; 13; 16; 17; 19; 20; 21; 23; 27; 31; 34; '
'36; 37; 38; 41; 42; 43; 50; 52; 60;', '', 'True', '', '',
'Web; App;', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', 'False', '', '', '', ''
]
]
report = [[
'Advertiser_Currency', 'Insertion_Order_Id', 'Revenue_Adv_Currency'
], ['EUR', '3489402', '893.195881'], ['EUR', '3502002', '14893.195881'],
['EUR', '3522675', '893.195881']]
print('report============================================================')
print(report)
print('report============================================================')
# Prep out blocks depending on where the outputs should be stored
if project.task['is_colab']:
project.task['out_old_sdf'].pop('bigquery')
project.task['out_new_sdf'].pop('bigquery')
project.task['out_changes'].pop('bigquery')
else:
project.task['out_old_sdf'].pop('file')
project.task['out_new_sdf'].pop('file')
project.task['out_changes'].pop('file')
# Build Schemas
schema = make_schema(sdf[0])
schema_changes = make_schema(CHANGES_SCHEMA)
project.task['out_old_sdf']['bigquery']['schema'] = schema
project.task['out_new_sdf']['bigquery']['schema'] = schema
project.task['out_changes']['bigquery']['schema'] = schema_changes
# Write old sdf to table
put_rows(project.task['auth'], project.task['out_old_sdf'],
(n for n in sdf))
# Categorize the IOs to be aggregated together
if (project.task['budget_categories'] != {}
and project.task['budget_categories'] != ''
and project.task['budget_categories'] != None):
categories = remove_excluded_ios_from_categories(
project.task['budget_categories'], project.task['excluded_ios'])
categories_spend = aggregate_io_spend_to_categories(report, categories)
categories_budget = aggregate_io_budget_to_categories(sdf, categories)
category_budget_deltas = calc_budget_spend_deltas(
categories_budget, categories_spend, categories)
new_sdf, changes = apply_category_budgets(sdf, category_budget_deltas,
categories)
# Don't split up the IOs by categories
else:
report_dict = convert_report_to_dict(report)
new_sdf, changes = calc_new_sdf_no_categories(
sdf, report_dict, project.task['excluded_ios'])
if project.task['is_colab']:
changes.insert(0, CHANGES_SCHEMA)
# Write new sdf to table
put_rows(project.task['auth'], project.task['out_new_sdf'],
(n for n in new_sdf))
# Write log file to table
put_rows(project.task['auth'], project.task['out_changes'],
(n for n in changes))
def first_and_third_party_audience_load():
# load multiple from user defined sheet
def load_multiple():
#partners = 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
# ).firstAndThirdPartyAudiences().list(
# partnerId=lookup_id(partner[0])
# ).execute()
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).firstAndThirdPartyAudiences().list(
advertiserId=lookup_id(advertiser[0])).execute()
# write to database
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'table':
'DV_First_And_Third_Party_Audiences',
'schema':
Discovery_To_BigQuery(
'displayvideo',
'v1').method_schema('firstAndThirdPartyAudiences.list'),
'format':
'JSON'
}
}, load_multiple())
# write to sheet
put_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Targeting Options',
'range': 'Q2'
}
},
get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"""SELECT
CONCAT(
SUBSTR(A.firstAndThirdPartyAudienceType, 37), ' > ',
A.audienceType, ' > ',
COALESCE(A.audienceSource, 'UNSPECIFIED'), ' > ',
A.displayName, ' - ', A.firstAndThirdPartyAudienceId
),
FROM `{dataset}.DV_First_And_Third_Party_Audiences` AS A
ORDER BY 1
""".format(**project.task),
'legacy':
False
}
}))
def cm_placement_load(config, task):
# load multiple partners from user defined sheet
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
).placements().list( **kwargs).execute()
cm_placement_clear(config, task)
# write placements to database
put_rows(
config,
task['auth_bigquery'],
{ 'bigquery': {
'dataset': task['dataset'],
'table': 'CM_Placements',
'schema': Discovery_To_BigQuery(
'dfareporting',
'v3.4'
).method_schema(
'placements.list',
iterate=True
),
'format':'JSON'
}},
load_multiple()
)
def dv_line_item_audit(config, task):
put_rows(
config, task["auth_bigquery"], {
"bigquery": {
"dataset":
task["dataset"],
"table":
"SHEET_LineItems",
"schema": [
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "Campaign",
"type": "STRING"
},
{
"name": "Insertion_Order",
"type": "STRING"
},
{
"name": "Line_Item",
"type": "STRING"
},
{
"name": "Action",
"type": "STRING"
},
{
"name": "Status",
"type": "STRING"
},
{
"name": "Status_Edit",
"type": "STRING"
},
{
"name": "Warning",
"type": "STRING"
},
{
"name": "Line_Item_Type",
"type": "STRING"
},
{
"name": "Line_Item_Type_Edit",
"type": "STRING"
},
{
"name": "Flight_Data_Type",
"type": "STRING"
},
{
"name": "Flight_Data_Type_Edit",
"type": "STRING"
},
{
"name": "Flight_Start_Date",
"type": "STRING"
},
{
"name": "Flight_Start_Date_Edit",
"type": "STRING"
},
{
"name": "Flight_End_Date",
"type": "STRING"
},
{
"name": "Flight_End_Date_Edit",
"type": "STRING"
},
{
"name": "Flight_Trigger",
"type": "STRING"
},
{
"name": "Flight_Trigger_Edit",
"type": "STRING"
},
{
"name": "Budget_Allocation_Type",
"type": "STRING"
},
{
"name": "Budget_Allocation_Type_Edit",
"type": "STRING"
},
{
"name": "Budget_Unit",
"type": "STRING"
},
{
"name": "Budget_Unit_Edit",
"type": "STRING"
},
{
"name": "Budget_Max",
"type": "FLOAT"
},
{
"name": "Budget_Max_Edit",
"type": "FLOAT"
},
{
"name": "Partner_Revenue_Model_Type",
"type": "STRING"
},
{
"name": "Partner_Revenue_Model_Type_Edit",
"type": "STRING"
},
{
"name": "Partner_Revenue_Model_Markup_Percent",
"type": "FLOAT"
},
{
"name": "Partner_Revenue_Model_Markup_Percent_Edit",
"type": "FLOAT"
},
{
"name": "Conversion_Percent",
"type": "FLOAT"
},
{
"name": "Conversion_Percent_Edit",
"type": "FLOAT"
},
{
"name": "Targeting_Expansion_Level",
"type": "STRING"
},
{
"name": "Targeting_Expansion_Level_Edit",
"type": "STRING"
},
{
"name": "Exclude_1P",
"type": "STRING"
},
{
"name": "Exclude_1P_Edit",
"type": "STRING"
},
],
"format":
"CSV"
}
},
get_rows(
config, task["auth_sheets"], {
"sheets": {
"sheet": task["sheet"],
"tab": "Line Items",
"header": False,
"range": "A2:AI"
}
}))
# Create Insert View
query_to_view(config,
task["auth_bigquery"],
config.project,
task["dataset"],
"INSERT_LineItems",
"""SELECT
REGEXP_EXTRACT(S_LI.Advertiser, r' - (\d+)$') AS advertiserId,
REGEXP_EXTRACT(S_LI.Campaign, r' - (\d+)$') AS campaignId,
REGEXP_EXTRACT(S_LI.Insertion_Order, r' - (\d+)$') AS insertionOrderId,
S_LI.Line_Item AS displayName,
S_LI.Line_Item_Type_Edit AS lineItemType,
S_LI.Status_Edit AS entityStatus,
STRUCT(
S_PC.Cost_Type_Edit As costType,
S_PC.Fee_Type_Edit As feeType,
S_PC.Invoice_Type_Edit AS invoiceType,
S_PC.Fee_Amount_Edit AS feeAmount,
S_PC.Fee_Percent_Edit * 1000 AS feePercentageMillis
) AS partnerCosts,
STRUCT(
S_LI.Flight_Data_Type_Edit AS flightDateType,
STRUCT (
STRUCT (
EXTRACT(YEAR FROM CAST(S_LI.Flight_Start_Date_Edit AS Date)) AS year,
EXTRACT(MONTH FROM CAST(S_LI.Flight_Start_Date_Edit AS DATE)) AS month,
EXTRACT(DAY FROM CAST(S_LI.Flight_Start_Date_Edit AS DATE)) AS day
) AS startDate,
STRUCT (
EXTRACT(YEAR FROM CAST(S_LI.Flight_End_Date_Edit AS Date)) AS year,
EXTRACT(MONTH FROM CAST(S_LI.Flight_End_Date_Edit AS DATE)) AS month,
EXTRACT(DAY FROM CAST(S_LI.Flight_End_Date_Edit AS DATE)) AS day
) AS endDate
) AS dateRange,
S_LI.Flight_Trigger_Edit AS triggerId
) AS flight,
STRUCT(
S_LI.Budget_Allocation_Type_Edit AS budgetAllocationType,
S_LI.Budget_Unit_Edit AS budgetUnit,
S_LI.Budget_Max_Edit * 1000000 AS maxAmount
) AS budget,
STRUCT(
S_P.Period_Edit As pacingPeriod,
S_P.Type_Edit As pacingType,
S_P.Daily_Budget_Edit AS dailyMaxMicros,
S_P.Daily_Impressions_Edit AS dailyMaxImpressions
) AS pacing,
STRUCT(
S_FC.Unlimited_Edit AS unlimited,
S_FC.Time_Unit_Edit AS timeUnit,
S_FC.Time_Count_Edit AS timeUnitCount,
S_FC.Max_impressions_Edit AS maxImpressions
) AS frequencyCap,
STRUCT(
S_LI.Partner_Revenue_Model_Type_Edit AS markupType,
S_LI.Partner_Revenue_Model_Markup_Percent_Edit * IF(S_LI.Partner_Revenue_Model_Type_Edit='PARTNER_REVENUE_MODEL_MARKUP_TYPE_CPM', 1000000, 1000) AS markupAmount
) AS partnerRevenueModel,
STRUCT(
S_LI. Conversion_Percent_Edit * 1000 AS postViewCountPercentageMillis,
[] AS floodlightActivityConfigs
) AS conversionCounting,
STRUCT(
IF(S_BS.Fixed_Bid_Edit IS NOT NULL,
STRUCT(
S_BS.Fixed_Bid_Edit * 1000000 AS bidAmountMicros
),
NULL
) AS fixedBid,
IF(S_BS.Auto_Bid_Goal_Edit IS NOT NULL,
STRUCT(
S_BS.Auto_Bid_Goal_Edit AS performanceGoalType,
S_BS.Auto_Bid_Amount_Edit * 1000000 AS maxAverageCpmBidAmountMicros,
S_BS.Auto_Bid_Algorithm_Edit AS customBiddingAlgorithmId
),
NULL
) AS maximizeSpendAutoBid,
IF(S_BS.Performance_Goal_Type_Edit IS NOT NULL,
STRUCT(
S_BS.Performance_Goal_Type_Edit AS performanceGoalType,
S_BS.Performance_Goal_Amount_Edit * 1000000 AS performanceGoalAmountMicros,
S_BS.Performance_Goal_Average_CPM_Bid_Edit * 1000000 AS maxAverageCpmBidAmountMicros,
S_BS.Performance_Goal_Algorithm_Edit AS customBiddingAlgorithmId
),
NULL
) AS performanceGoalAutoBid
)
AS bidStrategy,
STRUCT(
S_ID.Integration_Code_Edit AS integrationCode,
S_ID.Details_Edit AS details
) AS integrationDetails,
STRUCT(
S_LI.Targeting_Expansion_Level_Edit AS targetingExpansionLevel,
S_LI.Exclude_1P_Edit AS excludeFirstPartyAudience
) AS targetingExpansion
FROM `{dataset}.SHEET_LineItems` AS S_LI
LEFT JOIN `{dataset}.SHEET_PartnerCosts` AS S_PC ON S_LI.Line_Item=S_PC.Line_Item
LEFT JOIN `{dataset}.SHEET_Pacing` AS S_P ON S_LI.Line_Item=S_P.Line_Item
LEFT JOIN `{dataset}.SHEET_FrequencyCaps` AS S_FC ON S_LI.Line_Item=S_FC.Line_Item
LEFT JOIN `{dataset}.SHEET_IntegrationDetails` AS S_ID ON S_LI.Line_Item=S_ID.Line_Item
LEFT JOIN `{dataset}.SHEET_BidStrategy` AS S_BS ON S_LI.Line_Item=S_BS.Line_Item
LEFT JOIN `{dataset}.DV_LineItems` AS DV_LI ON S_LI.Line_Item=DV_LI.displayName
WHERE S_LI.Action="INSERT"
AND DV_LI IS NULL
""".format(**task),
legacy=False)
# Create Audit View
query_to_view(config,
task["auth_bigquery"],
config.project,
task["dataset"],
"AUDIT_LineItems",
"""WITH
/* Check if sheet values are set */
INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Line Item' AS Operation,
CASE
WHEN Budget_Allocation_Type_Edit IS NULL THEN 'Missing Budget Allocation Type.'
WHEN Budget_Unit_Edit IS NULL THEN 'Missing Budget Unit.'
ELSE
NULL
END AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item, 'BLANK') AS Id
FROM
`{dataset}.SHEET_LineItems`
)
WHERE
Error IS NOT NULL
),
/* Check duplicate inserts */
DUPLICATE_ERRORS AS (
SELECT
'Line Item' AS Operation,
'Duplicate Line Item name, insert will be ignored.' AS Error,
'WARNING' AS Severity,
COALESCE(S_LI.Line_Item, 'BLANK') AS Id
FROM `{dataset}.SHEET_LineItems` As S_LI
LEFT JOIN `{dataset}.DV_LineItems` AS DV_LI ON S_LI.Line_Item=DV_LI.displayName
WHERE S_LI.Action="INSERT"
AND DV_LI IS NOT NULL
)
SELECT * FROM INPUT_ERRORS
UNION ALL
SELECT * FROM DUPLICATE_ERRORS
""".format(**task),
legacy=False)
query_to_view(config,
task["auth_bigquery"],
config.project,
task["dataset"],
"PATCH_LineItems",
"""SELECT *
FROM `{dataset}.SHEET_LineItems`
WHERE Line_Item NOT IN (SELECT Id FROM `{dataset}.AUDIT_LineItems` WHERE Severity='ERROR')
""".format(**task),
legacy=False)
def insertion_order_load(config, task):
# load multiple partners from user defined sheet
def insertion_order_load_multiple():
campaigns = set([lookup_id(row[0]) for row in get_rows(
config,
task["auth_sheets"],
{ "sheets": {
"sheet": task["sheet"],
"tab": "Campaigns",
"header":False,
"range": "A2:A"
}}
)])
rows = get_rows(
config,
task["auth_sheets"],
{ "sheets": {
"sheet": task["sheet"],
"tab": "Advertisers",
"header":False,
"range": "A2:A"
}}
)
# String for filtering which entityStatus enums we want to see in the sheet
for row in rows:
for record in API_DV360(
config,
task["auth_dv"],
iterate=True
).advertisers().insertionOrders().list(
advertiserId=lookup_id(row[0]),
filter='entityStatus="ENTITY_STATUS_PAUSED" OR entityStatus="ENTITY_STATUS_ACTIVE" OR entityStatus="ENTITY_STATUS_DRAFT"'
).execute():
if not campaigns or record['campaignId'] in campaigns:
yield record
# write insertion orders to database and sheet
put_rows(
config,
task["auth_bigquery"],
{ "bigquery": {
"dataset": task["dataset"],
"table": "DV_InsertionOrders",
"schema": Discovery_To_BigQuery(
"displayvideo",
"v1"
).method_schema("advertisers.insertionOrders.list"),
"format": "JSON"
}},
insertion_order_load_multiple()
)
# write insertion orders to sheet
put_rows(
config,
task["auth_sheets"],
{ "sheets": {
"sheet": task["sheet"],
"tab": "Insertion Orders",
"header":False,
"range": "A2"
}},
get_rows(
config,
task["auth_bigquery"],
{ "bigquery": {
"dataset": task["dataset"],
"query": """SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
'PATCH',
I.entityStatus,
I.entityStatus,
I.displayName,
I.displayName,
I.budget.budgetUnit,
I.budget.budgetUnit,
I.budget.automationType,
I.budget.automationType,
I.performanceGoal.performanceGoalType,
I.performanceGoal.performanceGoalType,
I.performanceGoal.performanceGoalAmountMicros / 1000000,
I.performanceGoal.performanceGoalAmountMicros / 1000000,
I.performanceGoal.performanceGoalPercentageMicros / 1000000,
I.performanceGoal.performanceGoalPercentageMicros / 1000000,
I.performanceGoal.performanceGoalString,
I.performanceGoal.performanceGoalString
FROM `{dataset}.DV_InsertionOrders` AS I
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON I.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON I.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
ORDER BY I.displayName
""".format(**task),
"legacy": False
}}
)
)
def inventory_source_load():
# load multiple from user defined sheet
def load_multiple():
partners = 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).inventorySources().list(
partnerId=lookup_id(partner[0])).execute()
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).inventorySources().list(
advertiserId=lookup_id(advertiser[0])).execute()
# write to database
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'table':
'DV_Inventory_Sources',
'schema':
Discovery_To_BigQuery(
'displayvideo',
'v1').method_schema('inventorySources.list'),
'format':
'JSON'
}
}, load_multiple())
# write to sheet
put_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Inventory Sources',
'range': 'A2'
}
},
get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"""SELECT
CONCAT(I.displayName, ' - ', I.inventorySourceId),
inventorySourceType,
status.entityStatus,
status.sellerStatus,
status.configStatus,
exchange,
publisherName,
dealId
FROM `{dataset}.DV_Inventory_Sources` AS I
GROUP BY 1,2,3,4,5,6,7,8
ORDER BY 1
""".format(**project.task),
'legacy':
False
}
}))
def fred():
if 'series' in project.task:
for parameters in project.task['series']:
name = 'FRED_SERIES_%s' % parameters['series_id']
rows = fred_series(project.task['api_key'],
project.task['frequency'], **parameters)
if 'bigquery' in project.task['out']:
project.task['out']['bigquery']['schema'] = [{
"name": "realtime_start",
"type": "DATE",
"mode": "REQUIRED"
}, {
"name": "realtime_end",
"type": "DATE",
"mode": "REQUIRED"
}, {
"name": "day",
"type": "DATE",
"mode": "REQUIRED"
}, {
"name": "value",
"type": "FLOAT",
"mode": "REQUIRED"
}]
elif 'regions' in project.task:
for parameters in project.task['regions']:
name = 'FRED_SERIES_%s' % parameters['series_group']
rows = fred_regional(project.task['api_key'],
project.task['frequency'],
project.task['region_type'], **parameters)
if 'bigquery' in project.task['out']:
project.task['out']['bigquery']['schema'] = [{
"name": "region",
"type": "STRING",
"mode": "REQUIRED"
}, {
"name": "code",
"type": "INTEGER",
"mode": "REQUIRED"
}, {
"name": "series_id",
"type": "STRING",
"mode": "REQUIRED"
}, {
"name": "value",
"type": "FLOAT",
"mode": "REQUIRED"
}]
else:
raise Excpetion(
"MISSING CONFIGURATION: Specify either series_id or series_group.")
put_rows(project.task['auth'], name, rows)
def frequency_cap_audit(config, task):
rows = get_rows(
config,
task["auth_sheets"], {
"sheets": {
"sheet": task["sheet"],
"tab": "Frequency Caps",
"header":False,
"range": "A2:M"
}
})
put_rows(
config,
task["auth_bigquery"], {
"bigquery": {
"dataset": task["dataset"],
"table": "SHEET_FrequencyCaps",
"schema": [
{ "name": "Partner", "type": "STRING" },
{ "name": "Advertiser", "type": "STRING" },
{ "name": "Campaign", "type": "STRING" },
{ "name": "Insertion_Order", "type": "STRING" },
{ "name": "Line_Item", "type": "STRING" },
{ "name": "Unlimited", "type": "BOOLEAN" },
{ "name": "Unlimited_Edit", "type": "BOOLEAN" },
{ "name": "Time_Unit", "type": "STRING" },
{ "name": "Time_Unit_Edit", "type": "STRING" },
{ "name": "Time_Count", "type": "INTEGER" },
{ "name": "Time_Count_Edit", "type": "INTEGER" },
{ "name": "Max_impressions", "type": "INTEGER" },
{ "name": "Max_impressions_Edit", "type": "INTEGER" },
],
"format": "CSV"
}
}, rows)
query_to_view(
config,
task["auth_bigquery"],
config.project,
task["dataset"],
"AUDIT_FrequencyCaps",
"""WITH
/* Check if sheet values are set */
INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Frequency Caps' AS Operation,
CASE WHEN Unlimited_Edit IS TRUE THEN
CASE WHEN Time_Unit_Edit IS NOT NULL OR Time_Count_Edit IS NOT NULL OR Max_Impressions_Edit IS NOT NULL THEN 'Time Unit and the Other Options are Mutually Exclusive.'
ELSE NULL
END
ELSE
IF(Time_Unit_Edit IS NULL OR Time_Count_Edit IS NULL OR Max_Impressions_Edit IS NULL, 'If Time Unit is FALSE, the other options are required.', NULL)
END AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item, Insertion_Order, 'BLANK') AS Id
FROM
`{dataset}.SHEET_FrequencyCaps`
)
WHERE
Error IS NOT NULL
)
SELECT * FROM INPUT_ERRORS
;
""".format(**task),
legacy=False)
query_to_view(
config,
task["auth_bigquery"],
config.project,
task["dataset"],
"PATCH_FrequencyCaps",
"""SELECT *
FROM `{dataset}.SHEET_FrequencyCaps`
WHERE Line_Item NOT IN (SELECT Id FROM `{dataset}.AUDIT_FrequencyCaps` WHERE Severity='ERROR')
AND Insertion_Order NOT IN (SELECT Id FROM `{dataset}.AUDIT_FrequencyCaps` WHERE Severity='ERROR')
AND Campaign NOT IN (SELECT Id FROM `{dataset}.AUDIT_FrequencyCaps` WHERE Severity='ERROR')
""".format(**task),
legacy=False
)
def integration_detail_audit():
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Integration Details",
"range": "A2:I"
}
})
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset": project.task["dataset"],
"table": "SHEET_IntegrationDetails",
"schema": [
{ "name": "Partner", "type": "STRING" },
{ "name": "Advertiser", "type": "STRING" },
{ "name": "Campaign", "type": "STRING" },
{ "name": "Insertion_Order", "type": "STRING" },
{ "name": "Line_Item", "type": "STRING" },
{ "name": "Integration_Code", "type": "STRING" },
{ "name": "Integration_Code_Edit", "type": "STRING" },
{ "name": "Details", "type": "STRING" },
{ "name": "Details_Edit", "type": "STRING" },
],
"format": "CSV"
}
}, rows)
query_to_view(
project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"AUDIT_IntegrationDetails",
"""WITH
/* Check if advertiser values are set */
INPUT_ERRORS AS (
SELECT
'Integration Details' AS Operation,
'Missing Advertiser.' AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item, Insertion_Order, Advertiser, 'BLANK') AS Id
FROM
`{dataset}.SHEET_IntegrationDetails`
WHERE Advertiser IS NULL
)
SELECT * FROM INPUT_ERRORS
""".format(**project.task),
legacy=False
)
query_to_view(
project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"PATCH_IntegrationDetails",
"""SELECT *
FROM `{dataset}.SHEET_IntegrationDetails`
WHERE Line_Item NOT IN (SELECT Id FROM `{dataset}.AUDIT_IntegrationDetails` WHERE Severity='ERROR')
AND Insertion_Order NOT IN (SELECT Id FROM `{dataset}.AUDIT_IntegrationDetails` WHERE Severity='ERROR')
AND Campaign NOT IN (SELECT Id FROM `{dataset}.AUDIT_IntegrationDetails` WHERE Severity='ERROR')
""".format(**project.task),
legacy=False
)
def inventory_group_load(config, task):
# load multiple from user defined sheet
def load_multiple():
partners = get_rows(
config, task['auth_sheets'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'Partners',
'header': False,
'range': 'A2:A'
}
})
for partner in partners:
yield from API_DV360(
config, task['auth_dv'],
iterate=True).inventorySourceGroups().list(
partnerId=lookup_id(partner[0])).execute()
advertisers = get_rows(
config, task['auth_sheets'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'Advertisers',
'header': False,
'range': 'A2:A'
}
})
for advertiser in advertisers:
yield from API_DV360(
config, task['auth_dv'],
iterate=True).inventorySourceGroups().list(
advertiserId=lookup_id(advertiser[0])).execute()
inventory_group_clear(config, task)
# write inventorys to database
put_rows(
config, task['auth_bigquery'], {
'bigquery': {
'dataset':
task['dataset'],
'table':
'DV_Inventory_Groups',
'schema':
Discovery_To_BigQuery(
'displayvideo',
'v1').method_schema('inventorySourceGroups.list'),
'format':
'JSON'
}
}, load_multiple())
# write inventorys to sheet
put_rows(
config, task['auth_sheets'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'Targeting Options',
'header': False,
'range': 'M2'
}
},
get_rows(
config, task['auth_bigquery'], {
'bigquery': {
'dataset':
task['dataset'],
'query':
"""SELECT
CONCAT(I.displayName, ' - ', I.inventorySourceGroupId),
FROM `{dataset}.DV_Inventory_Groups` AS I
ORDER BY 1
""".format(**task),
'legacy':
False
}
}))
def integration_detail_load():
# write integration_details to sheet
rows = get_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"query":
"""SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CAST(NULL AS STRING),
CAST(NULL AS STRING),
CAST(NULL AS STRING),
A.integrationDetails.integrationCode,
A.integrationDetails.integrationCode,
A.integrationDetails.details,
A.integrationDetails.details
FROM `{dataset}.DV_Advertisers` AS A
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
UNION ALL
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
CAST(NULL AS STRING),
I.integrationDetails.integrationCode,
I.integrationDetails.integrationCode,
I.integrationDetails.details,
I.integrationDetails.details
FROM `{dataset}.DV_InsertionOrders` AS I
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON I.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON I.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
UNION ALL
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
CONCAT(L.displayName, ' - ', L.lineItemId),
L.integrationDetails.integrationCode,
L.integrationDetails.integrationCode,
L.integrationDetails.details,
L.integrationDetails.details
FROM `{dataset}.DV_LineItems` AS L
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON L.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_InsertionOrders` AS I
ON L.insertionOrderId=I.insertionOrderId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON L.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
""".format(**project.task),
"legacy":
False
}
})
put_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Integration Details",
"range": "A2"
}
}, rows)
def cm_advertiser_load(config, task):
# load multiple partners from user defined sheet
def load_multiple():
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, 'accountId': account_id } if is_superuser else { 'profileId': profile_id }
yield from API_DCM(
config,
task['auth_cm'],
iterate=True,
internal=is_superuser
).advertisers().list( **kwargs).execute()
cm_advertiser_clear(config, task)
# write advertisers to database
put_rows(
config,
task['auth_bigquery'],
{ 'bigquery': {
'dataset': task['dataset'],
'table': 'CM_Advertisers',
'schema': Discovery_To_BigQuery(
'dfareporting',
'v3.4'
).method_schema(
'advertisers.list',
iterate=True
),
'format':'JSON'
}},
load_multiple()
)
# write advertisers to sheet
put_rows(
config,
task['auth_sheets'],
{ 'sheets': {
'sheet': task['sheet'],
'tab': 'CM Advertisers',
'header':False,
'range': 'B2'
}},
get_rows(
config,
task['auth_bigquery'],
{ 'bigquery': {
'dataset': task['dataset'],
'query': '''SELECT
CONCAT(AC.name, ' - ', AC.id),
CONCAT(AD.name, ' - ', AD.id),
AD.status
FROM `{dataset}.CM_Advertisers` AS AD
LEFT JOIN `{dataset}.CM_Accounts` AS AC
ON AD.accountId=AC.id
'''.format(**task),
'legacy': False
}}
)
)
def pacing_audit():
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Pacing",
"range": "A2:Z"
}
})
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_Pacing",
"schema": [
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "Campaign",
"type": "STRING"
},
{
"name": "Insertion_Order",
"type": "STRING"
},
{
"name": "Line_Item",
"type": "STRING"
},
{
"name": "Period",
"type": "STRING"
},
{
"name": "Period_Edit",
"type": "STRING"
},
{
"name": "Type",
"type": "STRING"
},
{
"name": "Type_Edit",
"type": "STRING"
},
{
"name": "Daily_Budget",
"type": "FLOAT"
},
{
"name": "Daily_Budget_Edit",
"type": "FLOAT"
},
{
"name": "Daily_Impressions",
"type": "INTEGER"
},
{
"name": "Daily_Impressions_Edit",
"type": "INTEGER"
},
],
"format":
"CSV"
}
}, rows)
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"AUDIT_Pacing",
"""WITH
/* Check if sheet values are set */
INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Pacing' AS Operation,
CASE
WHEN Period_Edit IS NULL THEN 'Missing Period.'
WHEN Type_Edit IS NULL THEN 'Missing Type.'
ELSE
NULL
END AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item, Insertion_Order, 'BLANK') AS Id
FROM
`{dataset}.SHEET_Pacing`
)
WHERE
Error IS NOT NULL
)
SELECT * FROM INPUT_ERRORS
;
""".format(**project.task),
legacy=False)
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"PATCH_Pacing",
"""SELECT *
FROM `{dataset}.SHEET_Pacing`
WHERE (
REGEXP_CONTAINS(Insertion_Order, r" - (\d+)$")
OR REGEXP_CONTAINS(Line_Item, r" - (\d+)$")
)
AND Line_Item NOT IN (SELECT Id FROM `{dataset}.AUDIT_Pacing` WHERE Severity='ERROR')
AND Insertion_Order NOT IN (SELECT Id FROM `{dataset}.AUDIT_Pacing` WHERE Severity='ERROR')
""".format(**project.task),
legacy=False)
def bid_strategy_audit():
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Bid Strategy",
"range": "A2:U"
}
})
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_BidStrategy",
"schema": [
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "Campaign",
"type": "STRING"
},
{
"name": "Insertion_Order",
"type": "STRING"
},
{
"name": "Line_Item",
"type": "STRING"
},
{
"name": "Fixed_Bid",
"type": "FLOAT"
},
{
"name": "Fixed_Bid_Edit",
"type": "FLOAT"
},
{
"name": "Auto_Bid_Goal",
"type": "STRING"
},
{
"name": "Auto_Bid_Goal_Edit",
"type": "STRING"
},
{
"name": "Auto_Bid_Amount",
"type": "FLOAT"
},
{
"name": "Auto_Bid_Amount_Edit",
"type": "FLOAT"
},
{
"name": "Auto_Bid_Algorithm",
"type": "STRING"
},
{
"name": "Auto_Bid_Algorithm_Edit",
"type": "STRING"
},
{
"name": "Performance_Goal_Type",
"type": "STRING"
},
{
"name": "Performance_Goal_Type_Edit",
"type": "STRING"
},
{
"name": "Performance_Goal_Amount",
"type": "FLOAT"
},
{
"name": "Performance_Goal_Amount_Edit",
"type": "FLOAT"
},
{
"name": "Performance_Goal_Average_CPM_Bid",
"type": "FLOAT"
},
{
"name": "Performance_Goal_Average_CPM_Bid_Edit",
"type": "FLOAT"
},
{
"name": "Performance_Goal_Algorithm",
"type": "STRING"
},
{
"name": "Performance_Goal_Algorithm_Edit",
"type": "STRING"
},
],
"format":
"CSV"
}
}, rows)
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"AUDIT_BidStrategy",
"""WITH
/* Check if sheet values are set */ INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Bid Strategy' AS Operation,
CASE
WHEN Insertion_Order IS NOT NULL AND Line_Item IS NOT NULL THEN
CASE
WHEN Fixed_Bid_Edit IS NOT NULL AND Auto_Bid_Goal_Edit IS NULL AND Auto_Bid_Algorithm_Edit IS NOT NULL THEN 'Both Fixed Bid and Bid Algorithm exist.'
WHEN Fixed_Bid_Edit IS NULL AND Auto_Bid_Goal_Edit IS NOT NULL AND Auto_Bid_Algorithm_Edit IS NOT NULL THEN 'Both Bid Goal and Bid Algorithm exist.'
WHEN Fixed_Bid_Edit IS NOT NULL AND Auto_Bid_Goal_Edit IS NOT NULL AND Auto_Bid_Algorithm_Edit IS NULL THEN 'Both Fixed Bid and Bid Goal exist.'
WHEN Fixed_Bid_Edit IS NOT NULL AND Auto_Bid_Goal_Edit IS NOT NULL AND Auto_Bid_Algorithm_Edit IS NOT NULL THEN 'All bid fields exist.'
ELSE NULL
END
ELSE
NULL
END
AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item,
Insertion_Order,
'BLANK') AS Id
FROM
`{dataset}.SHEET_BidStrategy` )
WHERE
Error IS NOT NULL )
SELECT
*
FROM
INPUT_ERRORS ;
""".format(**project.task),
legacy=False)
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"PATCH_BidStrategy",
"""SELECT *
FROM `{dataset}.SHEET_BidStrategy`
WHERE (
REGEXP_CONTAINS(Insertion_Order, r" - (\d+)$")
OR REGEXP_CONTAINS(Line_Item, r" - (\d+)$")
)
AND Line_Item NOT IN (SELECT Id FROM `{dataset}.AUDIT_BidStrategy` WHERE Severity='ERROR')
AND Insertion_Order NOT IN (SELECT Id FROM `{dataset}.AUDIT_BidStrategy` WHERE Severity='ERROR')
""".format(**project.task),
legacy=False)
def pacing_load():
# write pacings to sheet
rows = get_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"query":
"""SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
NULL,
I.pacing.pacingPeriod,
I.pacing.pacingPeriod,
I.pacing.pacingType,
I.pacing.pacingType,
CAST(I.pacing.dailyMaxMicros AS INT64) / 100000,
CAST(I.pacing.dailyMaxMicros AS INT64) / 100000,
I.pacing.dailyMaxImpressions,
I.pacing.dailyMaxImpressions
FROM `{dataset}.DV_InsertionOrders` AS I
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON I.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON I.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
UNION ALL
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
CONCAT(L.displayName, ' - ', L.lineItemId),
L.pacing.pacingPeriod,
L.pacing.pacingPeriod,
L.pacing.pacingType,
L.pacing.pacingType,
CAST(L.pacing.dailyMaxMicros AS INT64) / 100000,
CAST(L.pacing.dailyMaxMicros AS INT64) / 100000,
L.pacing.dailyMaxImpressions,
L.pacing.dailyMaxImpressions
FROM `{dataset}.DV_LineItems` AS L
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON L.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_InsertionOrders` AS I
ON L.insertionOrderId=I.insertionOrderId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON L.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
""".format(**project.task),
"legacy":
False
}
})
put_rows(project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Pacing",
"range": "A2"
}
}, rows)
def custom_list_load(config, task):
# load multiple from user defined sheet
def load_multiple():
advertisers = get_rows(
config, task['auth_sheets'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'Advertisers',
'header': False,
'range': 'A2:A'
}
})
for advertiser in advertisers:
yield from API_DV360(
config, task['auth_dv'], iterate=True).customLists().list(
advertiserId=lookup_id(advertiser[0])).execute()
custom_list_clear(config, task)
# write to database
put_rows(
config, task['auth_bigquery'], {
'bigquery': {
'dataset':
task['dataset'],
'table':
'DV_Custom_Lists',
'schema':
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('customLists.list'),
'format':
'JSON'
}
}, load_multiple())
# write to sheet
put_rows(
config, task['auth_sheets'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'Targeting Options',
'header': False,
'range': 'P2:P'
}
},
get_rows(
config, task['auth_bigquery'], {
'bigquery': {
'dataset':
task['dataset'],
'query':
"""SELECT
CONCAT(I.displayName, ' - ', I.customListId),
FROM `{dataset}.DV_Custom_Lists` AS I
GROUP BY 1
ORDER BY 1
""".format(**task),
'legacy':
False
}
}))
def line_item_load():
# load multiple partners from user defined sheet
def 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().lineItems().list(
advertiserId=lookup_id(row[0])).execute()
# write line_items to database
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"DV_LineItems",
"schema":
Discovery_To_BigQuery(
"displayvideo",
"v1").method_schema("advertisers.lineItems.list"),
"format":
"JSON"
}
}, load_multiple())
# write line items to sheet
rows = get_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"query":
"""SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
CONCAT(L.displayName, ' - ', L.lineItemId),
L.entityStatus,
ARRAY_TO_STRING(L.warningMessages, '\\n'),
FROM `{dataset}.DV_LineItems` AS L
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON L.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON L.advertiserId=C.advertiserId
LEFT JOIN `{dataset}.DV_InsertionOrders` AS I
ON L.insertionOrderId=I.insertionOrderId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
""".format(**project.task),
"legacy":
False
}
})
put_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Line Items",
"range": "B2"
}
}, rows)
def line_item_map_audit():
rows = get_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Line Items Map',
'range': 'A2:Z'
}
})
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset': project.task['dataset'],
'table': 'SHEET_LineItemMaps',
'schema': [{
'name': 'Action',
'type': 'STRING'
}, {
'name': 'Line_Item',
'type': 'STRING'
}, {
'name': 'Creative',
'type': 'STRING'
}],
'format': 'CSV'
}
}, rows)
query_to_view(
project.task['auth_bigquery'],
project.id,
project.task['dataset'],
'AUDIT_LineItemMaps',
"""WITH
LINEITEM_ERRORS AS (
SELECT
'Line Items Map' AS Operation,
'Missing Line Item.' AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item, 'BLANK') AS Id
FROM
`{dataset}.SHEET_LineItemMaps` AS M
LEFT JOIN
`{dataset}.DV_LineItems` AS L
ON
M.Line_Item=CONCAT(L.displayName, ' - ', L.lineItemId)
WHERE L IS NULL
),
CREATIVE_ERRORS AS (
SELECT
'Line Items Map' AS Operation,
'Missing Line Item.' AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item, 'BLANK') AS Id
FROM
`{dataset}.SHEET_LineItemMaps` AS M
LEFT JOIN
`{dataset}.DV_Creatives` AS C
ON
M.Line_Item=CONCAT(C.displayName, ' - ', C.creativeId)
WHERE C IS NULL
)
SELECT * FROM LINEITEM_ERRORS
UNION ALL
SELECT * FROM CREATIVE_ERRORS
;
""".format(**project.task),
legacy=False)
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',
'header': False,
'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()
negative_keyword_list_clear()
# write to database
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 to sheet
put_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Targeting Options',
'header': False,
'range': 'J2:J'
}
},
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
}
}))
