def copy_view(view_id):
if project.verbose:
print('ANONYMIZE VIEW', project.task['bigquery']['to']['dataset'],
view_id)
view = API_BigQuery(project.task['auth']).tables().get(
projectId=project.task['bigquery']['from']['project'],
datasetId=project.task['bigquery']['from']['dataset'],
tableId=view_id).execute()['view']
project_dataset_template = '[%s:%s.' if view['useLegacySql'] else '`%s.%s.'
query = view['query'].replace(
project_dataset_template %
(project.task['bigquery']['from']['project'],
project.task['bigquery']['from']['dataset']),
project_dataset_template % (project.task['bigquery']['to']['project'],
project.task['bigquery']['to']['dataset']),
)
query_to_view(project.task['auth'],
project.task['bigquery']['to']['project'],
project.task['bigquery']['to']['dataset'],
view_id,
query,
legacy=view['useLegacySql'],
replace=True)
def mapping():
if project.verbose: print 'MAPPING'
# create the sheet from template if it does not exist
sheets_tab_copy(project.task['auth'], TEMPLATE_SHEET, TEMPLATE_TAB,
project.task['sheet'], project.task['tab'])
# move if specified
dimensions = {}
defaults = {}
rows = sheets_read(project.task['auth'], project.task['sheet'],
project.task['tab'], 'A1:D')
# if rows don't exist, query is still created without mapping ( allows blank maps )
if rows:
# sanitize mapping
# 0 = Dimension, 1 = Tag, 2 = Column, 3 = Keyword
for row in rows[1:]:
if project.verbose: print 'ROW: ', row
# sanitize row
row = map(lambda c: RE_SQLINJECT.sub('', c.strip()), row)
if len(row) == 2: # default
defaults.setdefault(row[0], row[1])
else: # tag
dimensions.setdefault(row[0], {}) # dimension
dimensions[row[0]].setdefault(row[1], {})
dimensions[row[0]].setdefault(row[1], {}) # tag
dimensions[row[0]][row[1]].setdefault(row[2], []) # column
dimensions[row[0]][row[1]][row[2]].extend(
[k.strip() for k in row[3].split(',') if k]) # keywords
# construct query
query = 'SELECT\n *,\n'
for dimension, tags in dimensions.items():
query += ' CASE\n'
for tag, columns in tags.items():
query += ' WHEN '
for column, keywords in columns.items():
for count, keyword in enumerate(keywords):
if count != 0: query += 'OR '
query += '%s CONTAINS "%s" ' % (column, keyword)
query += 'THEN "%s"\n' % tag
query += ' ELSE "%s"\n END AS %s,\n' % (defaults.get(
dimension, ''), dimension)
query += 'FROM [%s.%s]' % (project.task['in']['dataset'],
project.task['in']['table'])
if project.verbose: print 'QUERY: ', query
# write to view
query_to_view(project.task['out']['auth'],
project.id,
project.task['out']['dataset'],
project.task['out']['view'],
query,
replace=True)
def view_combine(name, combos_table, main_table, shadow_table):
if project.verbose: print("DYNAMIC COSTS VIEW:", name)
if shadow_table:
query = """
SELECT
combos.*,
(combos.Impressions / main.Impressions) * shadow.Dbm_Cost_Account_Currency AS Scaled_Dbm_Cost_Account_Currency
FROM `%(project)s.%(dataset)s.%(combos_table)s` combos
JOIN `%(project)s.%(dataset)s.%(main_table)s` main
ON combos.Placement_Id = main.Placement_Id
JOIN `%(project)s.%(dataset)s.%(shadow_table)s` shadow
ON STARTS_WITH(shadow.Placement, combos.Placement)
""" % {
"project": project.id,
"dataset": project.task['out']['dataset'],
"combos_table": combos_table,
"main_table": main_table,
"shadow_table": shadow_table
}
else:
query = """
SELECT
combos.*,
(combos.Impressions / main.Impressions) * main.Dbm_Cost_Account_Currency AS Scaled_Dbm_Cost_Account_Currency
FROM `%(project)s.%(dataset)s.%(combos_table)s` combos
JOIN `%(project)s.%(dataset)s.%(main_table)s` main
ON combos.Placement_Id = main.Placement_Id
""" % {
"project": project.id,
"dataset": project.task['out']['dataset'],
"combos_table": combos_table,
"main_table": main_table
}
query_to_view(
project.task['out']['auth'],
project.id,
project.task['out']['dataset'],
'Dynamic_Costs_%s_Analysis' % name,
query,
False
)
def census_write(query, table):
if project.verbose: print('%s: %s' % (table, query))
if project.task['to']['type'] == 'table':
query_to_view(project.task['auth'],
project.id,
project.task['to']['dataset'],
table,
query,
legacy=False)
elif project.task['to']['type'] == 'view':
query_to_view(project.task['auth'],
project.id,
project.task['to']['dataset'],
table,
query,
legacy=False)
def census_write(config, task, query, table):
if config.verbose:
print('%s: %s' % (table, query))
if task['to']['type'] == 'table':
query_to_table(config,
task['auth'],
config.project,
task['to']['dataset'],
table,
query,
legacy=False)
elif task['to']['type'] == 'view':
query_to_view(config,
task['auth'],
config.project,
task['to']['dataset'],
table,
query,
legacy=False)
def view_combine(config, task, name, combos_table, main_table, shadow_table):
if config.verbose:
print('DYNAMIC COSTS VIEW:', name)
if shadow_table:
query = """
SELECT
combos.*,
(combos.Impressions / main.Impressions) * shadow.Dbm_Cost_Account_Currency AS Scaled_Dbm_Cost_Account_Currency
FROM `%(project)s.%(dataset)s.%(combos_table)s` combos
JOIN `%(project)s.%(dataset)s.%(main_table)s` main
ON combos.Placement_Id = main.Placement_Id
JOIN `%(project)s.%(dataset)s.%(shadow_table)s` shadow
ON STARTS_WITH(shadow.Placement, combos.Placement)
""" % {
'project': config.project,
'dataset': task['out']['dataset'],
'combos_table': combos_table,
'main_table': main_table,
'shadow_table': shadow_table
}
else:
query = """
SELECT
combos.*,
(combos.Impressions / main.Impressions) * main.Dbm_Cost_Account_Currency AS Scaled_Dbm_Cost_Account_Currency
FROM `%(project)s.%(dataset)s.%(combos_table)s` combos
JOIN `%(project)s.%(dataset)s.%(main_table)s` main
ON combos.Placement_Id = main.Placement_Id
""" % {
'project': config.project,
'dataset': task['out']['dataset'],
'combos_table': combos_table,
'main_table': main_table
}
query_to_view(config, task['out']['auth'], config.project,
task['out']['dataset'], 'Dynamic_Costs_%s_Analysis' % name,
query, False)
def preview_li_insert(config, task):
# download IO Inserts
put_rows(
task["auth_bigquery"], {
"bigquery": {
"dataset":
task["dataset"],
"table":
"SHEET_LI_Inserts",
"schema": [
{
"name": "status",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "error",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "action",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "advertiser",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "campaign",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "insertionOrder",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "displayName",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "lineItemType",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "entityStatus",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "bidAmount",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "budgetSegmentStartDate",
"type": "DATE",
"mode": "NULLABLE"
},
{
"name": "budgetSegmentEndDate",
"type": "DATE",
"mode": "NULLABLE"
},
{
"name": "lineItemBudgetAllocationType",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "pacingPeriod",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "pacingType",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "dailyMaxMicros",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "dailyMaxImpressions",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "frequencyCapUnlimited",
"type": "BOOLEAN",
"mode": "NULLABLE"
},
{
"name": "frequencyCapTimeUnit",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "frequencyCapTimeUnitCount",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "frequencyCapMaxImpressions",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "postViewCountPercentageMillis",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "postViewLookbackWindowDays",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "postClickLookbackWindowDays",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "biddingStrategyPerformanceGoalType",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "performanceGoalAmountMicros",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "maxAverageCpmBidAmountMicros",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "customBiddingAlgorithm",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "floodlightActivityIds",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "inventorySourceIds",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "partnerCPMFeeCostType",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "partnerCPMFeeInvoiceType",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "partnerCPMFeeAmount",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "partnerMediaFeeCostType",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "partnerMediaFeeInvoiceType",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "partnerMediaFeePercent",
"type": "FLOAT",
"mode": "NULLABLE"
},
],
"format":
"CSV"
}
},
get_rows(
task["auth_sheets"], {
"sheets": {
"sheet": task["sheet"],
"tab": "LI Preview",
"header": False,
"range": "A2:AJ"
}
}))
# create insert view
query_to_view(task["auth_bigquery"],
config.project,
task["dataset"],
"INSERT_LI",
"""
SELECT
REGEXP_EXTRACT(advertiser, r' - (\d+)$') AS advertiserId,
STRUCT(
REGEXP_EXTRACT(advertiser, r' - (\d+)$') AS advertiserId,
REGEXP_EXTRACT(campaign, r' - (\d+)$') AS campaignId,
REGEXP_EXTRACT(insertionOrder, r' - (\d+)$') AS insertionOrderId,
displayName,
lineItemType,
entityStatus,
ARRAY((
SELECT partnerCost FROM (
SELECT
IF(partnerCPMFeeAmount IS NOT NULL,
STRUCT(
'PARTNER_COST_FEE_TYPE_CPM_FEE' AS feeType,
partnerCPMFeeCostType AS costType,
partnerCPMFeeInvoiceType AS invoiceType,
COALESCE(partnerCPMFeeAmount, 0) * 1000000 AS feeAmount
), NULL) AS partnerCost
UNION ALL
SELECT
IF(partnerMediaFeePercent IS NOT NULL,
STRUCT(
'PARTNER_COST_FEE_TYPE_MEDIA_FEE' AS feeType,
partnerMediaFeeCostType AS costType,
partnerMediaFeeInvoiceType AS invoiceType,
COALESCE(partnerMediaFeePercent, 0) * 1000 AS feePercentageMillis
), NULL) AS partnerCost
) WHERE partnerCost IS NOT NULL)
) AS partnerCosts,
STRUCT( 'LINE_ITEM_FLIGHT_DATE_TYPE_INHERITED' AS flightDateType ) AS flight,
STRUCT ( lineItemBudgetAllocationType AS budgetAllocationType ) AS budget,
STRUCT (
pacingPeriod,
pacingType,
IF(dailyMaxMicros IS NOT NULL, dailyMaxMicros * 1000000, NULL) AS dailyMaxMicros,
IF(dailyMaxMicros IS NULL, dailyMaxImpressions, NULL) AS dailyMaxImpressions
) AS pacing,
STRUCT ( CAST(frequencyCapUnlimited AS BOOL) AS unlimited,
frequencyCapTimeUnit AS timeUnit,
CAST(frequencyCapTimeUnitCount AS INT64) AS timeUnitCount,
CAST(frequencyCapMaxImpressions AS INT64) AS maxImpressions
) AS frequencyCap,
STRUCT ( 'PARTNER_REVENUE_MODEL_MARKUP_TYPE_TOTAL_MEDIA_COST_MARKUP' AS markupType ) AS partnerRevenueModel,
STRUCT ( STRUCT ( CAST(bidAmount * 1000000 AS INT64) AS bidAmountMicros ) AS fixedBid ) AS bidStrategy,
STRUCT(
postViewCountPercentageMillis AS postViewCountPercentageMillis,
ARRAY(
SELECT
STRUCT(
floodlightActivityId,
postClickLookbackWindowDays,
postViewLookbackWindowDays
)
FROM UNNEST(SPLIT(floodlightActivityIds)) AS floodlightActivityId
) AS floodlightActivityConfigs
) AS conversionCounting
) AS body
FROM `{dataset}.SHEET_LI_Inserts`
WHERE action = 'INSERT'
""".format(**task),
legacy=False)
# write LIs to API
for row in get_rows(
task["auth_bigquery"],
{"bigquery": {
"dataset": task["dataset"],
"table": "INSERT_LI",
}},
as_object=True):
try:
response = API_DV360(
task['auth_dv']).advertisers().lineItems().create(
**row).execute()
log_write('LI', row, response['lineItemId'], None)
except Exception as e:
log_write('LI', row, None, str(e))
log_write(config)
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 segment_audit():
# Move Segments To BigQuery
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_Segments",
"schema": [
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "Campaign",
"type": "STRING"
},
{
"name": "Insertion_Order",
"type": "STRING"
},
{
"name": "Action",
"type": "STRING"
},
{
"name": "Start_Date",
"type": "STRING"
},
{
"name": "Start_Date_Edit",
"type": "STRING"
},
{
"name": "End_Date",
"type": "STRING"
},
{
"name": "End_Date_Edit",
"type": "STRING"
},
{
"name": "Budget",
"type": "FLOAT"
},
{
"name": "Budget_Edit",
"type": "FLOAT"
},
{
"name": "Description",
"type": "STRING"
},
{
"name": "Description_Edit",
"type": "STRING"
},
],
"format":
"CSV"
}
},
get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Segments",
"header": False,
"range": "A2:M"
}
}))
# Create Audit View And Write To Sheets
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"AUDIT_Segments",
"""WITH
/* Check if sheet values are set */
INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Segment' AS Operation,
CASE
WHEN Start_Date_Edit IS NULL THEN 'Missing Start Date.'
WHEN End_Date_Edit IS NULL THEN 'Missing End Date .'
WHEN Budget_Edit IS NULL THEN 'Missing Budget.'
ELSE
NULL
END AS Error,
'ERROR' AS Severity,
COALESCE(Insertion_Order, 'BLANK') AS Id
FROM
`{dataset}.SHEET_Segments`
)
WHERE
Error IS NOT NULL
),
/* Check if duplicate Segments */
DUPLICATE_ERRORS AS (
SELECT
'Segment' AS Operation,
'Duplicate Segment.' AS Error,
'WARNING' AS Severity,
COALESCE(Insertion_Order, 'BLANK') AS Id
FROM (
SELECT Insertion_Order, Start_Date_Edit, End_Date_Edit, COUNT(*) AS count
FROM `{dataset}.SHEET_Segments`
GROUP BY 1, 2, 3
HAVING count > 1
)
),
/* Check if budget segments are current */
SEGMENT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Segments' AS Operation,
CASE
WHEN SAFE_CAST(Budget_Edit AS FLOAT64) > 1000000 THEN 'Segment has excessive spend.'
WHEN SAFE_CAST(Start_Date AS DATE) != SAFE_CAST(Start_Date_Edit AS DATE) AND SAFE_CAST(Start_Date_Edit AS DATE) < CURRENT_DATE() THEN 'Segment starts in past.'
WHEN SAFE_CAST(End_Date AS DATE) != SAFE_CAST(End_Date_Edit AS DATE) AND SAFE_CAST(End_Date_Edit AS DATE) < CURRENT_DATE() THEN 'Segment ends in past.'
ELSE
NULL
END
AS Error,
'ERROR' AS Severity,
COALESCE(Insertion_Order, 'BLANK') AS Id
FROM
`{dataset}.SHEET_Segments`
)
WHERE
Error IS NOT NULL
)
SELECT * FROM INPUT_ERRORS
UNION ALL
SELECT * FROM DUPLICATE_ERRORS
UNION ALL
SELECT * FROM SEGMENT_ERRORS
;
""".format(**project.task),
legacy=False)
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"PATCH_Segments",
"""SELECT *
FROM `{dataset}.SHEET_Segments`
WHERE Insertion_Order NOT IN (SELECT Id FROM `{dataset}.AUDIT_Segments` WHERE Severity='ERROR')
""".format(**project.task),
legacy=False)
def targeting_combine():
# read destination targeting
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_Destination_Targeting",
"schema": [
{
"name": "Action",
"type": "STRING"
},
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "LineItem",
"type": "STRING"
},
{
"name": "Authorized_Seller",
"type": "STRING"
},
{
"name": "User_Rewarded_Content",
"type": "STRING"
},
{
"name": "Exchange",
"type": "STRING"
},
{
"name": "Sub_Exchange",
"type": "STRING"
},
{
"name": "Channel",
"type": "STRING"
},
{
"name": "Channel_Negative",
"type": "BOOLEAN"
},
{
"name": "Inventory_Source",
"type": "STRING"
},
{
"name": "Inventory_Group",
"type": "STRING"
},
{
"name": "URL",
"type": "STRING"
},
{
"name": "URL_Negative",
"type": "BOOLEAN"
},
{
"name": "App",
"type": "STRING"
},
{
"name": "App_Negative",
"type": "BOOLEAN"
},
{
"name": "App_Category",
"type": "STRING"
},
{
"name": "App_Category_Negative",
"type": "BOOLEAN"
},
],
"format":
"CSV"
}
},
get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Destination Targeting",
"range": "A2:Z"
}
}))
# read brand safety targeting
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_Brand_Safety_Targeting",
"schema": [
{
"name": "Action",
"type": "STRING"
},
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "LineItem",
"type": "STRING"
},
{
"name": "Content_Label",
"type": "STRING"
},
{
"name": "Sensitive_Category",
"type": "STRING"
},
{
"name": "Negative_Keyword_List",
"type": "STRING"
},
{
"name": "Category",
"type": "STRING"
},
{
"name": "Category_Negative",
"type": "BOOLEAN"
},
{
"name": "Keyword",
"type": "STRING"
},
{
"name": "Keyword_Negative",
"type": "BOOLEAN"
},
],
"format":
"CSV"
}
},
get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Brand Safety Targeting",
"range": "A2:Z"
}
}))
# read demographic targeting
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_Demographic_Targeting",
"schema": [
{
"name": "Action",
"type": "STRING"
},
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "LineItem",
"type": "STRING"
},
{
"name": "Age_Range",
"type": "STRING"
},
{
"name": "Gender",
"type": "STRING"
},
{
"name": "Parental_Status",
"type": "STRING"
},
{
"name": "Household_Income",
"type": "STRING"
},
{
"name": "Language",
"type": "STRING"
},
{
"name": "Language_Negative",
"type": "BOOLEAN"
},
],
"format":
"CSV"
}
},
get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Demographic Targeting",
"range": "A2:Z"
}
}))
# read audience targeting
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_Audience_Targeting",
"schema": [
{
"name": "Action",
"type": "STRING"
},
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "LineItem",
"type": "STRING"
},
{
"name": "Included_1P_And_3P",
"type": "STRING"
},
{
"name": "Included_1P_And_3P_Recency",
"type": "STRING"
},
{
"name": "Excluded_1P_And_3P",
"type": "STRING"
},
{
"name": "Excluded_1P_And_3P_Recency",
"type": "STRING"
},
{
"name": "Included_Google",
"type": "STRING"
},
{
"name": "Excluded_Google",
"type": "STRING"
},
{
"name": "Included_Custom",
"type": "STRING"
},
{
"name": "Included_Combined",
"type": "STRING"
},
],
"format":
"CSV"
}
},
get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Audience Targeting",
"range": "A2:Z"
}
}))
# read device targeting
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_Device_Targeting",
"schema": [
{
"name": "Action",
"type": "STRING"
},
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "LineItem",
"type": "STRING"
},
{
"name": "Device_Type",
"type": "STRING"
},
{
"name": "Make_Model",
"type": "STRING"
},
{
"name": "Make_Model_Negative",
"type": "BOOLEAN"
},
{
"name": "Operating_System",
"type": "STRING"
},
{
"name": "Operating_System_Negative",
"type": "BOOLEAN"
},
{
"name": "Browser",
"type": "STRING"
},
{
"name": "Browser_Negative",
"type": "BOOLEAN"
},
{
"name": "Environment",
"type": "STRING"
},
{
"name": "Carrier_And_ISP",
"type": "STRING"
},
],
"format":
"CSV"
}
},
get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Device Targeting",
"range": "A2:Z"
}
}))
# read geography targeting
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_Geography_Targeting",
"schema": [
{
"name": "Action",
"type": "STRING"
},
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "LineItem",
"type": "STRING"
},
{
"name": "Day_Of_Week",
"type": "STRING"
},
{
"name": "Hour_Start",
"type": "INTEGER"
},
{
"name": "Hour_End",
"type": "INTEGER"
},
{
"name": "Timezone",
"type": "STRING"
},
{
"name": "Geo_Region",
"type": "STRING"
},
{
"name": "Geo_Region_Type",
"type": "STRING"
},
{
"name": "Geo_Region_Negative",
"type": "BOOLEAN"
},
{
"name": "Proximity_Location_List",
"type": "STRING"
},
{
"name": "Proximity_Location_List_Radius_Range",
"type": "STRING"
},
{
"name": "Regional_Location_List",
"type": "STRING"
},
],
"format":
"CSV"
}
},
get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Geography Targeting",
"range": "A2:Z"
}
}))
# read viewability targeting
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_Viewability_Targeting",
"schema": [
{
"name": "Action",
"type": "STRING"
},
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "LineItem",
"type": "STRING"
},
{
"name": "Video_Player_Size",
"type": "STRING"
},
{
"name": "In_Stream_Position",
"type": "STRING"
},
{
"name": "Out_Stream_Position",
"type": "BOOLEAN"
},
{
"name": "On_Screen_Position",
"type": "STRING"
},
{
"name": "Viewability",
"type": "STRING"
},
],
"format":
"CSV"
}
},
get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Viewability Targeting",
"range": "A2:Z"
}
}))
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"SHEET_Combined_Targeting",
"""SELECT
L.advertiserId AS Advertiser_Lookup,
*
FROM (
SELECT
COALESCE(A.Action,B.Action,C.Action,D.Action,E.Action,F.Action,G.Action) AS Action,
COALESCE(A.partner,B.Partner,C.Partner,D.partner,E.Partner,F.Partner,G.Partner) AS Partner,
COALESCE(A.Advertiser,B.Advertiser,C.Advertiser,D.Advertiser,E.Advertiser,F.Advertiser,G.Advertiser) AS Advertiser,
COALESCE(A.LineItem,B.LineItem,C.LineItem,D.LineItem,E.LineItem,F.LineItem,G.LineItem) AS LineItem,
* EXCEPT (Action, Partner, Advertiser, LineItem)
FROM `DV_Target_Demo.SHEET_Destination_Targeting` AS A
FULL OUTER JOIN `DV_Target_Demo.SHEET_Brand_Safety_Targeting` AS B
ON A.Action=B.Action
AND A.Partner=B.Partner
AND A.Advertiser=B.Advertiser
AND A.LineItem=B.LineItem
FULL OUTER JOIN `DV_Target_Demo.SHEET_Demographic_Targeting` AS C
ON A.Action=C.Action
AND A.Partner=C.Partner
AND A.Advertiser=C.Advertiser
AND A.LineItem=C.LineItem
FULL OUTER JOIN `DV_Target_Demo.SHEET_Audience_Targeting` AS D
ON A.Action=D.Action
AND A.Partner=D.Partner
AND A.Advertiser=D.Advertiser
AND A.LineItem=D.LineItem
FULL OUTER JOIN `DV_Target_Demo.SHEET_Device_Targeting` AS E
ON A.Action=E.Action
AND A.Partner=E.Partner
AND A.Advertiser=E.Advertiser
AND A.LineItem=E.LineItem
FULL OUTER JOIN `DV_Target_Demo.SHEET_Geography_Targeting` AS F
ON A.Action=F.Action
AND A.Partner=F.Partner
AND A.Advertiser=F.Advertiser
AND A.LineItem=F.LineItem
FULL OUTER JOIN `DV_Target_Demo.SHEET_Viewability_Targeting` AS G
ON A.Action=G.Action
AND A.Partner=G.Partner
AND A.Advertiser=G.Advertiser
AND A.LineItem=G.LineItem
) AS T
LEFT JOIN `{dataset}.DV_LineItems` AS L
ON CAST(REGEXP_EXTRACT(T.LineItem, r' - (\d+)$') AS INT64)=L.lineItemId
""".format(**project.task),
legacy=False)
def preview_li_load(config, task):
preview_li_clear(config, task)
# download LI Rules
put_rows(
task["auth_bigquery"], {
"bigquery": {
"dataset":
task["dataset"],
"table":
"SHEET_LI_Rules",
"schema": [
{
"name": "CM_Campaign",
"type": "STRING"
},
{
"name": "DV_Campaign",
"type": "STRING"
},
{
"name": "Type",
"type": "STRING"
},
{
"name": "Budget_Allocation",
"type": "STRING"
},
{
"name": "Pacing_Type",
"type": "STRING"
},
{
"name": "Pacing_Period",
"type": "STRING"
},
{
"name": "Pacing_Period_Max_Spend",
"type": "INTEGER"
},
{
"name": "Pacing_Period_Max_Impressions",
"type": "INTEGER"
},
{
"name": "Frequency_Cap_Unlimited",
"type": "BOOLEAN"
},
{
"name": "Frequency_Cap_Time_Unit",
"type": "STRING"
},
{
"name": "Frequency_Cap_Time_Unit_Count",
"type": "INTEGER"
},
{
"name": "Frequency_Cap_Max_Impressions",
"type": "INTEGER"
},
{
"name": "Post_View_Count_Percent",
"type": "INTEGER"
},
{
"name": "Performance_Goal_Type",
"type": "STRING"
},
{
"name": "Performance_Goal_Amount",
"type": "INTEGER"
},
{
"name": "Max_Average_CPM_Amount",
"type": "INTEGER"
},
{
"name": "Custom_Bidding_Algorithm",
"type": "STRING"
},
],
"format":
"CSV"
}
},
get_rows(
task["auth_sheets"], {
"sheets": {
"sheet": task["sheet"],
"tab": "LI Rules",
"header": False,
"range": "A2:AQ"
}
}))
# create LI preview ( main logic )
query_to_view(task["auth_bigquery"],
config.project,
task["dataset"],
"PREVIEW_LI",
"""WITH
cm AS (
SELECT
CM_P.name,
CM_P.advertiserId,
CM_C.id AS campaignId,
CM_C.name AS campaignName,
CM_P.compatibility,
CM_PG.pricingSchedule.startDate AS budgetSegmentStartDate,
CM_PG.pricingSchedule.endDate AS budgetSegmentEndDate,
NULLIF(CAST(CM_PP.rateOrCostNanos / 1000000000 AS INT64), 0) AS bidAmount,
CM_PG.name AS ioDisplayName,
CM_P.name AS liDisplayName
FROM `{dataset}.CM_PlacementGroups` AS CM_PG, UNNEST(childPlacementIds) AS childPlacementId, UNNEST(CM_PG.pricingSchedule.pricingPeriods) AS CM_PP
JOIN `{dataset}.CM_Placements` CM_P
ON childPlacementId = CM_P.id
JOIN `{dataset}.CM_Campaigns` AS CM_C
ON CM_P.campaignId = CM_C.id
JOIN `{dataset}.CM_Sites` AS CM_S
ON CM_PG.siteId = CM_S.id AND CM_S.name = 'Google DBM'
WHERE
pg_ProductCode IS NOT NULL
AND p_ProductCode IS NOT NULL
),
sheet AS (
SELECT
CONCAT(dv_a.displayName, ' - ', dv_a.advertiserid) AS DV_Advertiser,
sheet.*
FROM `{dataset}.SHEET_LI_Rules` as sheet
LEFT JOIN `{dataset}.DV_Campaigns` AS dv_c
ON CAST(REGEXP_EXTRACT(sheet.DV_Campaign, r' - (\d+)$') AS INT64) = dv_c.campaignId
LEFT JOIN `{dataset}.DV_Advertisers` AS dv_a
ON dv_a.advertiserid=dv_c.advertiserId
),
li_flattened AS (
SELECT
lineItemId,
displayName,
MAX(postViewLookbackWindowDays) AS postViewLookbackWindowDays,
MAX(postClickLookbackWindowDays) AS postClickLookbackWindowDays,
ARRAY_TO_STRING(ARRAY_AGG(CAST(floodlightActivityConfig.floodlightActivityId AS STRING) IGNORE NULLS), ",") AS floodlightActivityIds,
ARRAY_TO_STRING(ARRAY_AGG(CAST(inventorySourceId AS STRING) IGNORE NULLS), ",") AS inventorySourceIds
FROM `{dataset}.DV_LineItems`
LEFT JOIN UNNEST(conversionCounting.floodlightActivityConfigs) AS floodlightActivityConfig
LEFT JOIN UNNEST(inventorySourceIds) AS inventorySourceId
GROUP BY 1,2
),
io_flattened AS (
SELECT
insertionOrderId,
displayName,
MIN(DATE(segments.dateRange.startDate.year, segments.dateRange.startDate.month, segments.dateRange.startDate.day)) AS budgetSegmentStartDate,
MAX(DATE(segments.dateRange.endDate.year, segments.dateRange.endDate.month, segments.dateRange.endDate.day)) AS budgetSegmentEndtDate,
FROM `{dataset}.DV_InsertionOrders`
LEFT JOIN UNNEST(budget.budgetSegments) AS segments
GROUP BY 1,2
)
SELECT
'PREVIEW' AS action,
sheet.DV_Advertiser,
sheet.DV_Campaign,
CONCAT(dv_io.displayName, ' - ', dv_io.insertionOrderId) as DV_InsertionOrder,
cm.liDisplayName AS displayName,
sheet.Type AS lineItemType,
'ENTITY_STATUS_DRAFT' AS entityStatus,
CAST(NULL AS INT64) AS bidAmount,
dv_io.budgetSegmentStartDate,
dv_io.budgetSegmentEndtDate,
sheet.Budget_Allocation AS lineItemBudgetAllocationType,
sheet.Pacing_Period AS pacingPeriod,
sheet.Pacing_Type AS pacingType,
sheet.Pacing_Period_Max_Spend AS dailyMaxMicros,
sheet.Pacing_Period_Max_Impressions AS dailyMaxImpressions,
sheet.Frequency_Cap_Unlimited AS frequencyCapUnlimited,
sheet.Frequency_Cap_Time_Unit AS frequencyCapTimeUnit,
sheet.Frequency_Cap_Time_Unit_Count AS frequencyCapTimeUnitCount,
sheet.Frequency_Cap_Max_Impressions AS frequencyCapMaxImpressions,
sheet.Post_View_Count_Percent AS postViewCountPercentageMillis,
90 AS postViewLookbackWindowDays,
90 AS postClickLookbackWindowDays,
sheet.Performance_Goal_Type AS biddingStrategyPerformanceGoalType,
sheet.Performance_Goal_Amount AS performanceGoalAmountMicros,
sheet.Max_Average_CPM_Amount AS maxAverageCpmBidAmountMicros,
sheet.Custom_Bidding_Algorithm,
dv_li.floodlightActivityIds,
dv_li.inventorySourceIds,
CAST(NULL AS STRING) AS Partner_Cost_CPM_Fee_Cost_Type,
CAST(NULL AS STRING) AS Partner_Cost_CPM_Fee_Invoice_Type,
CAST(NULL AS STRING) AS Partner_Cost_CPM_Fee_Amount,
CAST(NULL AS STRING) AS Partner_Cost_Media_Fee_Cost_Type,
CAST(NULL AS STRING) AS Partner_Cost_Media_Fee_Invoice_Type,
CAST(NULL AS STRING) AS Partner_Cost_Media_Fee_Percent
FROM sheet
LEFT JOIN cm
ON CAST(REGEXP_EXTRACT(sheet.CM_Campaign, r' - (\d+)$') AS INT64) = cm.campaignId
AND (
(SPLIT(cm.name,'_')[OFFSET(0)] = 'VID' AND LOWER(SPLIT(sheet.Type , '_')[SAFE_OFFSET(3)]) = 'video')
OR (NOT SPLIT(cm.name, '_')[OFFSET(0)] = 'VID' AND LOWER(SPLIT(sheet.Type, '_')[SAFE_OFFSET(3)]) = 'display')
)
LEFT JOIN io_flattened dv_io
ON dv_io.displayName = cm.ioDisplayName
LEFT JOIN li_flattened dv_li
ON dv_li.displayName = cm.liDisplayName
""".format(**task),
legacy=False)
# create audit view
query_to_view(task["auth_bigquery"],
config.project,
task["dataset"],
"AUDIT_LI",
"""WITH
/* Check if sheet values are set */
INPUT_ERRORS AS (
SELECT
'LI Rules' AS Operation,
'Missing Sheet input value.' AS Error,
'ERROR' AS Severity,
CAST(NULL AS STRING) AS DV_Advertiser,
DV_Campaign,
CAST(NULL AS STRING) AS DV_InsertionOrder,
CM_Campaign AS DV_LineItem
FROM `{dataset}.SHEET_LI_Rules`
WHERE
CM_Campaign IS NULL
OR DV_Campaign IS NULL
OR Type IS NULL
OR Budget_Allocation IS NULL
OR Pacing_Period IS NULL
OR Pacing_Type IS NULL
OR Pacing_Period_Max_Spend IS NULL
OR Pacing_Period_Max_Impressions IS NULL
OR Frequency_Cap_Unlimited IS NULL
OR Frequency_Cap_Time_Unit IS NULL
OR Frequency_Cap_Time_Unit_Count IS NULL
OR Frequency_Cap_Max_Impressions IS NULL
OR Post_View_Count_Percent IS NULL
OR Performance_Goal_Type IS NULL
OR Performance_Goal_Amount IS NULL
OR Max_Average_CPM_Amount IS NULL
OR Custom_Bidding_Algorithm IS NULL
),
/* Check if duplicate LI */
DUPLICATE_ERRORS AS (
SELECT
'LI Rules' AS Operation,
'Duplicate Line Item.' AS Error,
'WARNING' AS Severity,
DV_Advertiser,
DV_Campaign,
DV_InsertionOrder,
DV_R.displayName AS DV_LineItem
FROM
`{dataset}.PREVIEW_LI` AS DV_R
LEFT JOIN (
SELECT
advertiserId,
campaignId,
insertionOrderId,
displayName
FROM `{dataset}.DV_LineItems`
GROUP BY 1,2,3,4
) AS DV_LI
ON DV_R.displayName = DV_LI.displayName
AND CAST(REGEXP_EXTRACT(DV_R.DV_Campaign, r' - (\d+)$') AS INT64) = DV_LI.campaignId
AND CAST(REGEXP_EXTRACT(DV_R.DV_InsertionOrder, r' - (\d+)$') AS INT64) = DV_LI.insertionOrderId
)
SELECT * FROM INPUT_ERRORS
UNION ALL
SELECT * FROM DUPLICATE_ERRORS
""".format(**task),
legacy=False)
# write io preview to sheet
put_rows(
task['auth_sheets'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'LI Preview',
'header': False,
'range': 'A2'
}
},
get_rows(
task['auth_bigquery'], {
'bigquery': {
'dataset':
task['dataset'],
'query':
"""SELECT
A.Severity,
A.Error,
P.*
FROM `{dataset}.PREVIEW_LI` AS P
LEFT JOIN (
SELECT
DV_Advertiser,
DV_Campaign,
DV_InsertionOrder,
DV_LineItem,
CASE
WHEN 'ERROR' IN UNNEST(ARRAY_AGG(Severity)) THEN 'ERROR'
WHEN 'WARNING' IN UNNEST(ARRAY_AGG(Severity)) THEN 'WARNING'
ELSE 'OK'
END AS Severity,
ARRAY_TO_STRING(ARRAY_AGG(CONCAT(Severity, ': ', Error)), '\\n') AS Error,
FROM `{dataset}.AUDIT_LI`
GROUP BY 1,2,3,4
) AS A
ON P.DV_Advertiser=A.DV_Advertiser
AND P.DV_Campaign=A.DV_Campaign
AND P.DV_InsertionOrder=A.DV_InsertionOrder
AND P.displayName=A.DV_LineItem
""".format(**task),
}
}))
def bigquery_query():
"""Execute a query and write results to table.
TODO: Replace with get_rows and put_rows combination.
See: scripts/bigquery_query.json
scripts/bigquery_storage.json
scripts/bigquery_to_sheet.json
scripts/bigquery_view.json
"""
if 'table' in project.task['to']:
if project.verbose:
print('QUERY TO TABLE', project.task['to']['table'])
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))
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'],
query_parameters(
project.task['from']['query'],
project.task['from'].get('parameters')),
legacy=project.task['from'].get('legacy', True))
# makes sure types are correct in sheet
rows = rows_to_type(rows)
sheets_clear(project.task['to'].get('auth', project.task['auth']),
project.task['to']['sheet'], project.task['to']['tab'],
project.task['to'].get('range', 'A2'))
sheets_write(project.task['to'].get('auth', project.task['auth']),
project.task['to']['sheet'], project.task['to']['tab'],
project.task['to'].get('range', 'A2'), rows)
elif 'sftp' in project.task['to']:
if project.verbose:
print('QUERY TO SFTP')
rows = query_to_rows(
project.task['auth'],
project.id,
project.task['from']['dataset'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
legacy=project.task['from'].get('use_legacy_sql', True))
if rows:
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))
def frequency_cap_audit():
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Frequency Caps",
"range": "A2:M"
}
})
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.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(project.task["auth_bigquery"],
project.id,
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(**project.task),
legacy=False)
query_to_view(project.task["auth_bigquery"],
project.id,
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(**project.task),
legacy=False)
def line_item_audit():
rows = get_rows(
project.task["auth_sheets"],
{ "sheets": {
"sheet": project.task["sheet"],
"tab": "Line Items",
"range": "A2:Z"
}}
)
put_rows(
project.task["auth_bigquery"],
{ "bigquery": {
"dataset": project.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": "STRING" },
{ "name": "Partner_Revenue_Model_Edit", "type": "STRING" },
{ "name": "Partner_Markup", "type": "FLOAT" },
{ "name": "Partner_Markup_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"
}},
rows
)
# Create Insert View
query_to_view(
project.task["auth_bigquery"],
project.id,
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 * 100000 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_Edit AS markupType,
S_LI.Partner_Markup_Edit * 100000 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 * 100000 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 * 100000 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 * 100000 AS performanceGoalAmountMicros,
S_BS.Performance_Goal_Average_CPM_Bid_Edit * 100000 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(**project.task),
legacy=False
)
# Create Audit View
query_to_view(
project.task["auth_bigquery"],
project.id,
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(**project.task),
legacy=False
)
query_to_view(
project.task["auth_bigquery"],
project.id,
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(**project.task),
legacy=False
)
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 preview_io_insert(config, task):
# download IO Inserts
put_rows(
config, task["auth_bigquery"], {
"bigquery": {
"dataset":
task["dataset"],
"table":
"SHEET_IO_Inserts",
"schema": [{
"name": "status",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "error",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "action",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "advertiser",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "campaign",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "displayName",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "pacingPeriod",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "pacingType",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "dailyMaxMicros",
"type": "INTEGER",
"mode": "NULLABLE"
}, {
"name": "dailyMaxImpressions",
"type": "INTEGER",
"mode": "NULLABLE"
}, {
"name": "frequencyCapUnlimited",
"type": "BOOLEAN",
"mode": "NULLABLE"
}, {
"name": "frequencyCapTimeUnit",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "frequencyCapTimeUnitCount",
"type": "INTEGER",
"mode": "NULLABLE"
}, {
"name": "frequencyCapMaxImpressions",
"type": "INTEGER",
"mode": "NULLABLE"
}, {
"name": "performanceGoalType",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "performanceGoalAmount",
"type": "INTEGER",
"mode": "NULLABLE"
}, {
"name": "performanceGoalPercentageMicros",
"type": "INTEGER",
"mode": "NULLABLE"
}, {
"name": "performanceGoalString",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "budgetUnit",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "budgetAutomationType",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "budgetSegmentAmount",
"type": "INTEGER",
"mode": "NULLABLE"
}, {
"name": "budgetSegmentDescription",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "budgetSegmentStartDate",
"type": "DATE",
"mode": "NULLABLE"
}, {
"name": "budgetSegmentEndDate",
"type": "DATE",
"mode": "NULLABLE"
}, {
"name": "budgetSegmentCampaignBudgetId",
"type": "INTEGER",
"mode": "NULLABLE"
}, {
"name": "biddingStrategyFixedBid",
"type": "INTEGER",
"mode": "NULLABLE"
}, {
"name": "integrationCode",
"type": "STRING",
"mode": "NULLABLE"
}, {
"name": "integrationDetails",
"type": "STRING",
"mode": "NULLABLE"
}],
"format":
"CSV"
}
},
get_rows(
config, task["auth_sheets"], {
"sheets": {
"sheet": task["sheet"],
"tab": "IO Preview",
"header": False,
"range": "A2:AC"
}
}))
# create insert view
query_to_view(config,
task["auth_bigquery"],
config.project,
task["dataset"],
"INSERT_IO",
"""
SELECT
REGEXP_EXTRACT(advertiser, r' - (\d+)$') AS advertiserId,
STRUCT(
REGEXP_EXTRACT(advertiser, r' - (\d+)$') AS advertiserId,
REGEXP_EXTRACT(campaign, r' - (\d+)$') AS campaignId,
displayName,
'ENTITY_STATUS_DRAFT' AS entityStatus,
STRUCT(
pacingPeriod,
pacingType,
IF(dailyMaxMicros IS NOT NULL, dailyMaxMicros * 1000000, NULL) AS dailyMaxMicros,
IF(dailyMaxMicros IS NULL, dailyMaxImpressions, NULL) AS dailyMaxImpressions
) AS pacing,
STRUCT(
CAST(frequencyCapUnlimited AS bool) AS unlimited,
frequencyCapTimeUnit AS timeUnit,
frequencyCapTimeUnitCount AS timeUnitCount,
frequencyCapMaxImpressions AS maxImpressions
) AS frequencyCap,
STRUCT(
performanceGoalType,
CAST(NULLIF(CAST(performanceGoalAmount AS INT64) * 1000000, 0) AS STRING) AS performanceGoalAmountMicros
) AS performanceGoal,
STRUCT(
budgetUnit,
budgetAutomationType AS automationType,
[
STRUCT(
IF(
budgetSegmentAmount IS NOT NULL,
budgetSegmentAmount * 1000000,
NULL
) AS budgetAmountMicros,
budgetSegmentDescription AS description,
STRUCT(
STRUCT(
EXTRACT(YEAR FROM budgetSegmentStartDate) AS year,
EXTRACT(MONTH FROM budgetSegmentStartDate) AS month,
EXTRACT(DAY FROM budgetSegmentStartDate) AS day
) AS startDate,
STRUCT(
EXTRACT(YEAR FROM budgetSegmentEndDate) AS year,
EXTRACT(MONTH FROM budgetSegmentEndDate) AS month,
EXTRACT(DAY FROM budgetSegmentEndDate) AS day
) AS endDate
) AS dateRange,
budgetSegmentCampaignBudgetId AS campaignBudgetId
)
] AS budgetSegments
) AS budget,
STRUCT(
STRUCT(
NULLIF(biddingStrategyFixedBid * 1000000, 0) AS bidAmountMicros
) AS fixedBid
) AS bidStrategy
) AS body
FROM `{dataset}.SHEET_IO_Inserts`
WHERE action = 'INSERT'
""".format(**task),
legacy=False)
# write IOs to API
for row in get_rows(
config,
task["auth_bigquery"],
{"bigquery": {
"dataset": task["dataset"],
"table": "INSERT_IO",
}},
as_object=True):
try:
response = API_DV360(
config,
task['auth_dv']).advertisers().insertionOrders().create(
**row).execute()
log_write('IO', row, response['insertionOrderId'], None)
except Exception as e:
log_write('IO', row, None, str(e))
log_write(config)
def preview_io_load(config, task):
preview_io_clear(config, task)
# download IO rules
put_rows(
config, task["auth_bigquery"], {
"bigquery": {
"dataset":
task["dataset"],
"table":
"SHEET_IO_Rules",
"schema": [
{
"name": "CM_Campaign",
"type": "STRING"
},
{
"name": "DV_Campaign",
"type": "STRING"
},
{
"name": "Pacing_Period",
"type": "STRING"
},
{
"name": "Pacing_Type",
"type": "STRING"
},
{
"name": "Frequency_Cap_Unlimited",
"type": "BOOLEAN"
},
{
"name": "Frequency_Cap_Time_Unit",
"type": "STRING"
},
{
"name": "Frequency_Cap_Time_Unit_Count",
"type": "INTEGER"
},
{
"name": "Frequency_Cap_Max_Impressions",
"type": "INTEGER"
},
{
"name": "Performance_Goal_Type",
"type": "STRING"
},
{
"name": "Budget_Unit",
"type": "STRING"
},
{
"name": "Budget_Automation_Type",
"type": "STRING"
},
],
"format":
"CSV"
}
},
get_rows(
config, task["auth_sheets"], {
"sheets": {
"sheet": task["sheet"],
"tab": "IO Rules",
"header": False,
"range": "A2:K"
}
}))
# create IO preview (main logic)
query_to_view(config,
task["auth_bigquery"],
config.project,
task["dataset"],
"PREVIEW_IO",
"""WITH
cm AS (
SELECT
CM_C.id AS campaignId,
NULLIF(CAST(CM_PP.rateOrCostNanos / 1000000000 AS INT64), 0) AS performanceGoalAmount,
CM_PG.pricingSchedule.startDate AS budgetSegmentStartDate,
CM_PG.pricingSchedule.endDate AS budgetSegmentEndDate,
CM_PG.name AS displayName
FROM `{dataset}.CM_Advertisers` AS CM_A
LEFT JOIN `{dataset}.CM_Campaigns` AS CM_C
ON CM_A.Id = CM_C.advertiserId
LEFT JOIN `{dataset}.CM_Sites` AS CM_S
ON CM_S.name = 'Google DBM'
LEFT JOIN `{dataset}.CM_PlacementGroups` CM_PG
ON CM_PG.advertiserId = CM_C.advertiserId
AND CM_PG.campaignId = CM_C.Id
AND CM_PG.siteId = CM_S.id,
UNNEST(pricingSchedule.pricingPeriods) AS CM_PP
)
SELECT
'PREVIEW' AS action,
CONCAT(dv_a.displayName, ' - ', dv_a.advertiserId) as DV_Advertiser,
sheet.DV_Campaign AS DV_Campaign,
cm.displayName,
sheet.Pacing_Period AS pacingPeriod,
sheet.Pacing_Type AS pacingType,
CAST(NULL AS INT64) AS dailyMaxMicros,
CAST(NULL AS INT64) dailyMaxImpressions,
sheet.Frequency_Cap_Unlimited AS frequencyCapUnlimited,
sheet.Frequency_Cap_Time_Unit AS frequencyCapTimeUnit,
sheet.Frequency_Cap_Time_Unit_Count AS frequencyCapTimeUnitCount,
sheet.Frequency_Cap_Max_Impressions AS frequencyCapMaxImpressions,
sheet.Performance_Goal_Type AS performanceGoalType,
cm.performanceGoalAmount AS performanceGoalAmount,
CAST(NULL AS INT64) AS performanceGoalPercentageMicros,
CAST(NULL AS STRING) AS performanceGoalString,
sheet.Budget_Unit AS budgetUnit,
CAST(NULL AS STRING) AS budgetAutomationType,
CAST(NULL AS INT64) AS budgetSegmentAmount,
CAST(NULL AS STRING) AS budgetSegmentDescription,
cm.budgetSegmentStartDate AS budgetSegmentStartDate,
cm.budgetSegmentEndDate AS budgetSegmentEndDate,
CAST(NULL AS INT64) AS budgetSegmentCampaignBudgetId,
0 AS biddingStrategyFixedBid,
CAST(NULL AS STRING) AS integrationCode,
CAST(NULL AS STRING) AS integrationDetails
FROM `{dataset}.SHEET_IO_Rules` as sheet
LEFT JOIN cm
ON CAST(REGEXP_EXTRACT(sheet.CM_Campaign, r' - (\d+)$') AS INT64) = cm.campaignId
LEFT JOIN `{dataset}.DV_Campaigns` AS dv_c
ON CAST(REGEXP_EXTRACT(sheet.DV_Campaign, r' - (\d+)$') AS INT64) = dv_c.campaignId
LEFT JOIN `{dataset}.DV_Advertisers` AS dv_a
ON dv_c.advertiserId = dv_a.advertiserId
""".format(**task),
legacy=False)
# create audits
query_to_view(config,
task["auth_bigquery"],
config.project,
task["dataset"],
"AUDIT_IO",
"""WITH
/* Check if sheet values are set */
INPUT_ERRORS AS (
SELECT
'IO Rules' AS Operation,
'Missing Sheet input value.' AS Error,
'ERROR' AS Severity,
DV_Advertiser,
DV_Campaign,
displayName AS DV_InsertionOrder,
CAST(NULL AS STRING) AS DV_LineItem
FROM `{dataset}.PREVIEW_IO`
WHERE
displayName IS NULL
OR pacingPeriod IS NULL
OR pacingType IS NULL
OR (dailyMaxMicros IS NULL AND dailyMaxImpressions IS NULL)
OR frequencyCapUnlimited IS NULL
OR frequencyCapTimeUnitCount IS NULL
OR frequencyCapMaxImpressions IS NULL
OR performanceGoalType IS NULL
OR (performanceGoalAmount IS NULL AND performanceGoalPercentageMicros IS NULL)
OR performanceGoalString IS NULL
OR budgetUnit IS NULL
OR budgetAutomationType IS NULL
OR budgetSegmentAmount IS NULL
OR budgetSegmentStartDate IS NULL
OR budgetSegmentEndDate IS NULL
OR biddingStrategyFixedBid IS NULL
),
/* Check if duplicate IO */
DUPLICATE_ERRORS AS (
SELECT
'IO Rules' AS Operation,
'Duplicate Insertion Order.' AS Error,
'WARNING' AS Severity,
DV_R.DV_Advertiser AS DV_Advertiser,
DV_R.DV_Campaign AS DV_Campaign,
DV_R.displayName AS DV_InsertionOrder,
CAST(NULL AS STRING) AS DV_LineItem
FROM
`{dataset}.PREVIEW_IO` AS DV_R
LEFT JOIN (
SELECT DISTINCT(displayName)
FROM `{dataset}.DV_InsertionOrders` ) AS DV_IO
ON DV_R.displayName = DV_IO.displayName
),
/* Check if budget segments are current */
SEGMENT_ERRORS AS (
SELECT *
FROM (
SELECT
'IO Rules' AS Operation,
CASE
WHEN CAST(budgetSegmentAmount AS INT64) > 1 THEN 'Segment has excessive spend.'
WHEN budgetSegmentStartDate IS NULL THEN 'Segment missing start date.'
WHEN budgetSegmentEndDate IS NULL THEN 'Segment missing end date.'
WHEN CAST(budgetSegmentStartDate AS DATE) < CURRENT_DATE() THEN 'Segment starts in the past.'
WHEN CAST(budgetSegmentEndDate AS DATE) < CURRENT_DATE() THEN 'Segment ends in the past.'
ELSE
NULL
END
AS Error,
'ERROR' AS Severity,
DV_Advertiser,
DV_Campaign,
displayName AS DV_InsertionOrder,
CAST(NULL AS STRING) AS DV_LineItem
FROM `{dataset}.PREVIEW_IO`
)
WHERE Error IS NOT NULL
)
SELECT * FROM INPUT_ERRORS
UNION ALL
SELECT * FROM DUPLICATE_ERRORS
UNION ALL
SELECT * FROM SEGMENT_ERRORS
""".format(**task),
legacy=False)
# write io preview to sheet with audits
put_rows(
config, task['auth_sheets'], {
'sheets': {
'sheet': task['sheet'],
'tab': 'IO Preview',
'header': False,
'range': 'A2'
}
},
get_rows(
config, task['auth_bigquery'], {
'bigquery': {
'dataset':
task['dataset'],
'query':
"""SELECT
A.Severity,
A.Error,
P.*
FROM `{dataset}.PREVIEW_IO` AS P
LEFT JOIN (
SELECT
DV_Advertiser,
DV_Campaign,
DV_InsertionOrder,
DV_LineItem,
CASE
WHEN 'ERROR' IN UNNEST(ARRAY_AGG(Severity)) THEN 'ERROR'
WHEN 'WARNING' IN UNNEST(ARRAY_AGG(Severity)) THEN 'WARNING'
ELSE 'OK'
END AS Severity,
ARRAY_TO_STRING(ARRAY_AGG(CONCAT(Severity, ': ', Error)), '\\n') AS Error,
FROM `{dataset}.AUDIT_IO`
GROUP BY 1,2,3,4
) AS A
ON P.DV_Advertiser=A.DV_Advertiser
AND P.DV_Campaign=A.DV_Campaign
AND P.displayName=A.DV_InsertionOrder
""".format(**task),
}
}))
def bigquery():
if 'function' in project.task:
query = None
if project.task['function'] == 'pearson_significance_test':
query = pearson_significance_test()
if query:
run_query(project.task['auth'], project.id, query, False,
project.task['to']['dataset'])
elif '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,
query_parameters(project.task['run']['query'],
project.task['run'].get('parameters')),
project.task['run'].get('legacy', True),
)
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'])
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))
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'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
legacy=project.task['from'].get('legacy', True))
# makes sure types are correct in sheet
rows = rows_to_type(rows)
sheets_clear(project.task['to'].get('auth', project.task['auth']),
project.task['to']['sheet'],
project.task['to']['tab'],
project.task['to'].get('range', 'A2'))
sheets_write(project.task['to'].get('auth', 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'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
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 bid_strategy_audit():
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Bid Strategy",
"range": "A2:Z"
}
})
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_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 insertion_order_audit():
# Move Insertion Order To BigQuery
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Insertion Orders",
"range": "A2:Z"
}
})
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.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": "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(project.task["auth_bigquery"],
project.id,
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,
'ENTITY_STATUS_DRAFT' 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 * 100000 AS performanceGoalAmountMicros,
S_IO.Performance_Goal_Percent_Edit * 100000 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 * 100000 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 * 100000 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 * 100000 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 * 100000 AS performanceGoalAmountMicros,
S_BS.Performance_Goal_Average_CPM_Bid_Edit * 100000 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(**project.task),
legacy=False)
# Create Audit View And Write To Sheets
query_to_view(project.task["auth_bigquery"],
project.id,
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.'
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(**project.task),
legacy=False)
query_to_view(project.task["auth_bigquery"],
project.id,
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(**project.task),
legacy=False)
def partner_cost_audit():
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Partner Costs",
"range": "A2:Z"
}
})
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_PartnerCosts",
"schema": [
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "Campaign",
"type": "STRING"
},
{
"name": "Insertion_Order",
"type": "STRING"
},
{
"name": "Line_Item",
"type": "STRING"
},
{
"name": "Cost_Type",
"type": "STRING"
},
{
"name": "Cost_Type_Edit",
"type": "STRING"
},
{
"name": "Fee_Type",
"type": "STRING"
},
{
"name": "Fee_Type_Edit",
"type": "STRING"
},
{
"name": "Invoice_Type",
"type": "STRING"
},
{
"name": "Invoice_Type_Edit",
"type": "STRING"
},
{
"name": "Fee_Amount",
"type": "FLOAT"
},
{
"name": "Fee_Amount_Edit",
"type": "FLOAT"
},
{
"name": "Fee_Percent",
"type": "FLOAT"
},
{
"name": "Fee_Percent_Edit",
"type": "FLOAT"
},
],
"format":
"CSV"
}
}, rows)
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"AUDIT_PartnerCosts",
"""WITH
/* Check if sheet values are set */ INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Partner Costs' AS Operation,
CASE
WHEN Cost_Type_Edit IS NULL THEN 'Missing Cost Type.'
WHEN Fee_Type_Edit IS NULL THEN 'Missing Fee Type.'
WHEN Invoice_Type_Edit IS NULL THEN 'Missing Invoice Type.'
WHEN Fee_Amount_Edit IS NULL
AND Fee_Percent_Edit IS NULL THEN 'You must select a Fee Amount OR Fee Percent'
ELSE IF
(Fee_Amount_Edit IS NOT NULL
AND Fee_Percent_Edit IS NOT NULL,
'You must select a Fee Amount OR Fee Percent, not both',
NULL)
END
AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item,
Insertion_Order,
'BLANK') AS Id
FROM
`{dataset}.SHEET_PartnerCosts` )
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_PartnerCosts",
"""SELECT *
FROM `{dataset}.SHEET_PartnerCosts`
WHERE (
REGEXP_CONTAINS(Insertion_Order, r" - (\d+)$")
OR REGEXP_CONTAINS(Line_Item, r" - (\d+)$")
)
AND Line_Item NOT IN (SELECT Id FROM `{dataset}.AUDIT_PartnerCosts` WHERE Severity='ERROR')
AND Insertion_Order NOT IN (SELECT Id FROM `{dataset}.AUDIT_PartnerCosts` WHERE Severity='ERROR')
""".format(**project.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)
