def frequency_cap_patch(commit=False):
patches = []
rows = get_rows(
project.task["auth_bigquery"],
{ "bigquery": {
"dataset": project.task["dataset"],
"table":"PATCH_FrequencyCaps",
}},
as_object=True
)
for row in rows:
frequency_cap = {}
if row['Unlimited'] != row['Unlimited_Edit']:
frequency_cap.setdefault("frequencyCap", {})
frequency_cap["frequencyCap"]["unlimited"] = row['Unlimited_Edit']
if row['Time_Unit'] != row['Time_Unit_Edit']:
frequency_cap.setdefault("frequencyCap", {})
frequency_cap["frequencyCap"]["timeUnit"] = row['Time_Unit_Edit']
if row['Time_Count'] != row['Time_Count_Edit']:
frequency_cap.setdefault("frequencyCap", {})
frequency_cap["frequencyCap"]["timeUnitCount"] = row['Time_Count_Edit']
if row['Max_impressions'] != row['Max_impressions_Edit']:
frequency_cap.setdefault("frequencyCap", {})
frequency_cap["frequencyCap"]["maxImpressions"] = row['Max_impressions_Edit']
if frequency_cap:
patch = {
"operation": "Frequency Caps",
"action": "PATCH",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"body": frequency_cap
}
}
if row['Line_Item']:
patch["line_item"] = row['Line_Item']
patch["parameters"]["lineItemId"] = lookup_id(row['Line_Item'])
elif row['Insertion_Order']:
patch["insertion_order"] = row['Insertion_Order']
patch["parameters"]["insertionOrderId"] = lookup_id(row['Insertion_Order'])
else:
patch["campaign"] = row['Campaign']
patch["parameters"]["campaignId"] = lookup_id(row['Campaign'])
patches.append(patch)
patch_masks(patches)
patch_preview(patches)
if commit:
insertion_order_commit(patches)
line_item_commit(patches)
campaign_commit(patches)
def integration_detail_patch(commit=False):
patches = []
rows = get_rows(project.task["auth_bigquery"], {
"bigquery": {
"dataset": project.task["dataset"],
"table": "PATCH_IntegrationDetails",
}
},
as_object=True)
for row in rows:
integration_details = {}
if row['Integration_Code'] != row['Integration_Code_Edit']:
integration_details.setdefault("integrationDetails", {})
integration_details["integrationDetails"]["integrationCode"] = row[
'Integration_Code_Edit']
if row['Details'] != row['Details_Edit']:
integration_details.setdefault("integrationDetails", {})
integration_details["integrationDetails"]["details"] = row[
'Details_Edit']
if integration_details:
patch = {
"operation": "Pacing",
"action": "PATCH",
"partner": row['Partner'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"body": integration_details
}
}
if row['Line_Item']:
patch["line_item"] = row['Line_Item']
patch["parameters"]["lineItemId"] = lookup_id(row['Line_Item'])
elif row['Insertion_Order']:
patch["insertion_order"] = row['Insertion_Order']
patch["parameters"]["insertionOrderId"] = lookup_id(
row['Insertion_Order'])
else:
patch["advertiser"] = row['Advertiser']
patch["parameters"]["advertiserId"] = lookup_id(
row['Advertiser'])
patches.append(patch)
patch_masks(patches)
if commit:
insertion_order_commit(patches)
line_item_commit(patches)
advertiser_commit(patches)
else:
patch_preview(patches)
def partner_cost_patch(commit=False):
patches = {}
changed = set()
rows = get_rows(project.task["auth_bigquery"], {
"bigquery": {
"dataset": project.task["dataset"],
"table": "PATCH_PartnerCosts",
}
},
as_object=True)
for row in rows:
lookup = row['Line_Item'] or row['Insertion_Order']
patches.setdefault(
lookup, {
"operation": "Partner Costs",
"action": "PATCH",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"body": {
"partnerCosts": []
}
}
})
if row['Line_Item']:
patches[lookup]["line_item"] = row['Line_Item']
patches[lookup]["parameters"]["lineItemId"] = lookup_id(
row['Line_Item'])
else:
patches[lookup]["insertion_order"] = row['Insertion_Order']
patches[lookup]["parameters"]["insertionOrderId"] = lookup_id(
row['Insertion_Order'])
patches[lookup]["parameters"]["body"]["partnerCosts"].append({
"costType":
row['Cost_Type_Edit'],
"feeType":
row['Fee_Type_Edit'],
"invoiceType":
row['Invoice_Type_Edit'],
"feeAmount":
int(float(row['Fee_Amount_Edit']) *
100000) if row['Fee_Amount_Edit'] else None,
"feePercentageMillis":
int(float(row['Fee_Percent_Edit']) *
1000) if row['Fee_Percent_Edit'] else None
})
if row['Cost_Type'] != row['Cost_Type_Edit'] \
or row['Fee_Type'] != row['Fee_Type_Edit'] \
or row['Invoice_Type'] != row['Invoice_Type_Edit'] \
or row['Fee_Amount'] != row['Fee_Amount_Edit'] \
or row['Fee_Percent'] != row['Fee_Percent_Edit']:
changed.add(lookup)
# Remove any patches where partner costs have not changed
for pc in list(patches.keys()):
if pc not in changed:
del patches[pc]
patches = list(patches.values())
patch_masks(patches)
if commit:
insertion_order_commit(patches)
line_item_commit(patches)
else:
patch_preview(patches)
def pacing_patch(commit=False):
patches = []
rows = get_rows(
project.task["auth_bigquery"],
{ "bigquery": {
"dataset": project.task["dataset"],
"table":"PATCH_Pacing",
}},
as_object=True
)
for row in rows:
pacing = {}
if row['Period'] != row['Period_Edit']:
pacing.setdefault("pacing", {})
pacing["pacing"]["pacingPeriod"] = row['Period_Edit']
if row['Type'] != row['Type_Edit']:
pacing.setdefault("pacing", {})
pacing["pacing"]["pacingType"] = row['Type_Edit']
if row['Daily_Budget'] != row['Daily_Budget_Edit']:
pacing.setdefault("pacing", {})
pacing["pacing"]["dailyMaxMicros"] = int(float(row['Daily_Budget_Edit']) * 100000)
if row['Daily_Impressions'] != row['Daily_Impressions_Edit']:
pacing.setdefault("pacing", {})
pacing["pacing"]["dailyMaxImpressions"] = row['Daily_Impressions_Edit']
if pacing:
patch = {
"operation": "Pacing",
"action": "PATCH",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"body": pacing
}
}
if row['Line_Item']:
patch["line_item"] = row['Line_Item']
patch["parameters"]["lineItemId"] = lookup_id(row['Line_Item'])
else:
patch["insertion_order"] = row['Insertion_Order']
patch["parameters"]["insertionOrderId"] = lookup_id(row['Insertion_Order'])
patches.append(patch)
patch_masks(patches)
if commit:
insertion_order_commit(patches)
line_item_commit(patches)
else:
patch_preview(patches)
def line_item_patch(commit=False):
def date_edited(value):
y, m, d = value.split("-")
return {"year": y, "month": m, "day": d}
patches = []
rows = get_rows(
project.task["auth_bigquery"],
{ "bigquery": {
"dataset": project.task["dataset"],
"table":"PATCH_LineItems",
}},
as_object=True
)
for row in rows:
if row['Action'] == "DELETE":
patches.append({
"operation": "Line Items",
"action": "DELETE",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"line_item": row['Line_Item'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"lineItemId": lookup_id(row['Line_Item'])
}
})
elif row['Action'] == "PATCH":
line_item = {}
if row['Line_Item_Type'] != row['Line_Item_Type_Edit']:
line_item["lineItemType"] = row['Line_Item_Type_Edit']
if row['Status'] != row['Status_Edit']:
line_item['entityStatus'] = row['Status_Edit']
if row['Flight_Data_Type'] != row['Flight_Data_Type_Edit']:
line_item.setdefault("flight", {})
line_item["flight"]["flightDateType"] = row['Flight_Data_Type_Edit']
if row['Flight_Start_Date'] != row['Flight_Start_Date_Edit']:
line_item.setdefault("flight", {}).setdefault("dateRange", {})
line_item["flight"]["dateRange"]["startDate"] = date_edited(row['Flight_Start_Date_Edit'])
if row['Flight_End_Date'] != row['Flight_End_Date_Edit']:
line_item.setdefault("flight", {}).setdefault("endDate", {})
line_item["flight"]["dateRange"]["endDate"] = date_edited(row['Flight_End_Date_Edit'])
if row['Flight_Trigger'] != row['Flight_Trigger_Edit']:
line_item.setdefault("flight", {})
line_item["flight"]["triggerId"] = row['Flight_Trigger_Edit']
if row['Budget_Allocation_Type'] != row['Budget_Allocation_Type_Edit']:
line_item.setdefault("budget", {})
line_item["budget"]["budgetAllocationType"] = row['Budget_Allocation_Type_Edit']
if row['Budget_Unit'] != row['Budget_Unit_Edit']:
line_item.setdefault("budget", {})
line_item["budget"]["budgetUnit"] = row['Budget_Unit_Edit']
if row['Budget_Max'] != row['Budget_Max_Edit']:
line_item.setdefault("budget", {})
line_item["budget"]["maxAmount"] = int(
float(row['Budget_Max_Edit']) * 100000
)
if row['Partner_Revenue_Model'] != row['Partner_Revenue_Model_Edit']:
line_item.setdefault("partnerRevenueModel", {})
line_item["partnerRevenueModel"]["markupType"] = row['Partner_Revenue_Model_Edit']
if row['Partner_Markup'] != row['Partner_Markup_Edit']:
line_item.setdefault("partnerRevenueModel", {})
line_item["partnerRevenueModel"]["markupAmount"] = int(
float(row['Partner_Markup_Edit']) * 100000
)
if row['Conversion_Percent'] != row['Conversion_Percent_Edit']:
line_item.setdefault("conversionCounting", {})
line_item["conversionCounting"]["postViewCountPercentageMillis"] = int(
float(row['Conversion_Percent_Edit']) * 1000
)
if row['Targeting_Expansion_Level'] != row['Targeting_Expansion_Level_Edit']:
line_item.setdefault("targetingExpansion", {})
line_item["targetingExpansion"]["targetingExpansionLevel"] = row['Targeting_Expansion_Level_Edit']
if row['Exclude_1P'] != row['Exclude_1P_Edit']:
line_item.setdefault("targetingExpansion", {})
line_item["targetingExpansion"]["excludeFirstPartyAudience"] = row['Exclude_1P_Edit']
if line_item:
patches.append({
"operation": "Line Items",
"action": "PATCH",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"line_item": row['Line_Item'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"lineItemId": lookup_id(row['Line_Item']),
"body": line_item
}
})
patch_masks(patches)
patch_preview(patches)
if commit:
line_item_commit(patches)
def insertion_order_patch(commit=False):
patches = []
rows = get_rows(project.task["auth_bigquery"], {
"bigquery": {
"dataset": project.task["dataset"],
"table": "PATCH_InsertionOrders",
}
},
as_object=True)
for row in rows:
if row['Action'] == "DELETE":
patches.append({
"operation": "Insertion Orders",
"action": "DELETE",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"insertion_order": row['Insertion_Order'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"insertionOrderId": lookup_id(row['Insertion_Order'])
}
})
elif row['Action'] == "PATCH":
insertion_order = {}
if row['Name'] != row['Name_Edit']:
insertion_order["displayName"] = row['Name_Edit']
if row['Budget_Unit'] != row['Budget_Unit_Edit']:
insertion_order.setdefault("budget", {})
insertion_order["budget"]["budgetUnit"] = row[
'Budget_Unit_Edit']
if row['Budget_Automation'] != row['Budget_Automation_Edit']:
insertion_order.setdefault("budget", {})
insertion_order["budget"]["automationType"] = row[
'Budget_Automation_Edit']
if row['Performance_Goal_Type'] != row[
'Performance_Goal_Type_Edit']:
insertion_order.setdefault("performanceGoal", {})
insertion_order["performanceGoal"][
"performanceGoalType"] = row['Performance_Goal_Type_Edit']
if row['Performance_Goal_Amount'] != row[
'Performance_Goal_Amount_Edit']:
insertion_order.setdefault("performanceGoal", {})
insertion_order["performanceGoal"][
"performanceGoalAmountMicros"] = int(
float(row['Performance_Goal_Amount_Edit']) * 1000000)
if row['Performance_Goal_Percent'] != row[
'Performance_Goal_Percent_Edit']:
insertion_order.setdefault("performanceGoal", {})
insertion_order["performanceGoal"][
"performanceGoalPercentageMicros"] = int(
float(row['Performance_Goal_Percent_Edit']) * 1000000)
if row['Performance_Goal_String'] != row[
'Performance_Goal_String_Edit']:
insertion_order.setdefault("performanceGoal", {})
insertion_order["performanceGoal"][
"performanceGoalString"] = row[
'Performance_Goal_String_Edit']
if insertion_order:
patches.append({
"operation": "Insertion Orders",
"action": "PATCH",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"insertion_order": row['Insertion_Order'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"insertionOrderId": lookup_id(row['Insertion_Order']),
"body": insertion_order
}
})
patch_masks(patches)
if commit:
insertion_order_commit(patches)
else:
patch_preview(patches)
def bid_strategy_patch(commit=False):
patches = []
rows = get_rows(project.task["auth_bigquery"], {
"bigquery": {
"dataset": project.task["dataset"],
"table": "PATCH_BidStrategy",
}
},
as_object=True)
for row in rows:
bid_strategy = {}
# If we are trying to switch from fixed bid to another bid type
if row['Fixed_Bid_Edit'] is None and row['Fixed_Bid'] is not None:
# If we switched from fixed to goal
if row['Auto_Bid_Goal'] != row['Auto_Bid_Goal_Edit']:
if project.verbose:
print("Switching from Fixed Bid to Auto Bid Goal.")
bid_strategy.setdefault("bidStrategy",
{"maximizeSpendAutoBid": {}})
bid_strategy["bidStrategy"]["maximizeSpendAutoBid"][
"performanceGoalType"] = row['Auto_Bid_Goal_Edit']
# If we switched from fixed to algorithm
elif row['Auto_Bid_Algorithm'] != row['Auto_Bid_Algorithm_Edit']:
if project.verbose:
print("Switching from Fixed Bid to Bid Algorithm.")
bid_strategy.setdefault("bidStrategy",
{"maximizeSpendAutoBid": {}})
bid_strategy["bidStrategy"]["maximizeSpendAutoBid"][
"customBiddingAlgorithmId"] = row[
'Auto_Bid_Algorithm_Edit']
elif row['Fixed_Bid'] != row['Fixed_Bid_Edit']:
bid_strategy.setdefault("bidStrategy", {"fixedBid": {}})
bid_strategy["bidStrategy"]["fixedBid"]["bidAmountMicros"] = int(
float(row['Fixed_Bid_Edit']) * 100000)
if row['Auto_Bid_Goal'] != row['Auto_Bid_Goal_Edit']:
bid_strategy.setdefault("bidStrategy",
{"maximizeSpendAutoBid": {}})
bid_strategy["bidStrategy"]["maximizeSpendAutoBid"][
"performanceGoalType"] = row['Auto_Bid_Goal_Edit']
if row['Auto_Bid_Amount'] != row['Auto_Bid_Amount_Edit']:
bid_strategy.setdefault("bidStrategy",
{"maximizeSpendAutoBid": {}})
bid_strategy["bidStrategy"]["maximizeSpendAutoBid"][
"maxAverageCpmBidAmountMicros"] = int(
float(row['Auto_Bid_Amount_Edit']) * 100000)
if row['Auto_Bid_Algorithm'] != row['Auto_Bid_Algorithm_Edit']:
bid_strategy.setdefault("bidStrategy",
{"maximizeSpendAutoBid": {}})
bid_strategy["bidStrategy"]["maximizeSpendAutoBid"][
"customBiddingAlgorithmId"] = row['Auto_Bid_Algorithm_Edit']
if row['Performance_Goal_Type'] != row['Performance_Goal_Type_Edit']:
bid_strategy.setdefault("bidStrategy",
{"performanceGoalAutoBid": {}})
bid_strategy["bidStrategy"]["performanceGoalAutoBid"][
"performanceGoalType"] = row['Performance_Goal_Type_Edit']
if row['Performance_Goal_Amount'] != row[
'Performance_Goal_Amount_Edit']:
bid_strategy.setdefault("bidStrategy",
{"performanceGoalAutoBid": {}})
bid_strategy["bidStrategy"]["performanceGoalAutoBid"][
"performanceGoalAmountMicros"] = int(
float(row['Performance_Goal_Amount_Edit']) * 100000)
if row['Performance_Goal_Average_CPM_Bid'] != row[
'Performance_Goal_Average_CPM_Bid_Edit']:
bid_strategy.setdefault("bidStrategy",
{"performanceGoalAutoBid": {}})
bid_strategy["bidStrategy"]["performanceGoalAutoBid"][
"maxAverageCpmBidAmountMicros"] = int(
float(row['Performance_Goal_Average_CPM_Bid_Edit']) *
100000)
if row['Performance_Goal_Algorithm'] != row[
'Performance_Goal_Algorithm_Edit']:
bid_strategy.setdefault("bidStrategy",
{"performanceGoalAutoBid": {}})
bid_strategy["bidStrategy"]["performanceGoalAutoBid"][
"customBiddingAlgorithmId"] = row[
'Performance_Goal_Algorithm_Edit']
if bid_strategy:
patch = {
"operation": "Bid Strategy",
"action": "PATCH",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"body": bid_strategy
}
}
if row['Line_Item']:
patch["line_item"] = row['Line_Item']
patch["parameters"]["lineItemId"] = lookup_id(row['Line_Item'])
else:
patch["insertion_order"] = row['Insertion_Order']
patch["parameters"]["insertionOrderId"] = lookup_id(
row['Insertion_Order'])
patches.append(patch)
patch_masks(patches)
if commit:
insertion_order_commit(patches)
line_item_commit(patches)
else:
patch_preview(patches)
def line_item_map_patch(commit=False):
patches = {}
changed = set()
rows = get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"""SELECT advertiserId, lineItemId, creativeIds FROM `{dataset}.DV_LineItems`
""".format(**project.task),
'as_object':
True,
'legacy':
False
}
})
for row in rows:
patches[str(row['lineItemId'])] = {
'operation': 'Line Items Map',
'action': 'PATCH',
'parameters': {
'advertiserId': str(row['advertiserId']),
'lineItemId': str(row['lineItemId']),
'body': {
'creativeIds': [str(c) for c in row['creativeIds']]
}
}
}
rows = get_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Line Items Map',
'range': 'A2:Z'
}
})
rows = rows_pad(rows, 3, '')
for row in rows:
lineitem_id = lookup_id(row[1])
creative_id = lookup_id(row[2])
if lineitem_id in patches:
if row[0] == 'ADD' and creative_id not in patches[lineitem_id][
'parameters']['body']['creativeIds']:
patches[lineitem_id]['line_item'] = row[1]
patches[lineitem_id]['parameters']['body']['creativeIds'].append(
creative_id)
changed.add(lineitem_id)
if row[0] == 'REMOVE' and creative_id in patches[lineitem_id][
'parameters']['body']['creativeIds']:
patches[lineitem_id]['line_item'] = row[1]
patches[lineitem_id]['parameters']['body']['creativeIds'].remove(
creative_id)
changed.add(lineitem_id)
# Remove any patches where creatives have not changed
for li in list(patches.keys()):
if li not in changed:
del patches[li]
patches = list(patches.values())
patch_masks(patches)
if commit:
line_item_commit(patches)
else:
patch_preview(patches)
def segment_patch(commit=False):
def date_edited(value):
y, m, d = value.split("-")
return {"year": y, "month": m, "day": d}
patches = {}
changed = set()
rows = get_rows(project.task["auth_bigquery"], {
"bigquery": {
"dataset": project.task["dataset"],
"table": "PATCH_Segments",
}
},
as_object=True)
# Build list of segements skipping only deletes, track changes
for row in rows:
patches.setdefault(
row["Insertion_Order"], {
"operation": "Segments",
"action": "PATCH",
"partner": row["Partner"],
"advertiser": row["Advertiser"],
"campaign": row["Campaign"],
"insertion_order": row["Insertion_Order"],
"parameters": {
"advertiserId": lookup_id(row["Advertiser"]),
"insertionOrderId": lookup_id(row["Insertion_Order"]),
"body": {
"budget": {
"budgetSegments": []
}
}
}
})
if row['Action'] == "DELETE":
changed.add(row["Insertion_Order"])
else:
patches[row["Insertion_Order"]]["parameters"]["body"]["budget"][
"budgetSegments"].append({
"dateRange": {
"startDate": date_edited(row["Start_Date_Edit"]),
"endDate": date_edited(row["End_Date_Edit"])
},
"budgetAmountMicros":
float(row["Budget_Edit"]) * 1000000,
"description":
row["Description_Edit"]
})
if (row['Action'] == "INSERT"
or row["Start_Date"] != row["Start_Date_Edit"]
or row["End_Date"] != row["End_Date_Edit"]
or row["Budget"] != row["Budget_Edit"]
or row["Description"] != row["Description_Edit"]):
changed.add(row["Insertion_Order"])
# Remove any patches where segments have not changed
for io in list(patches.keys()):
if io not in changed:
del patches[io]
patches = list(patches.values())
patch_masks(patches)
patch_preview(patches)
if commit:
insertion_order_commit(patches)
def segment_patch(commit=False):
def date_edited(value):
y, m, d = value.split("-")
return {"year": y, "month": m, "day": d}
patches = {}
changed = set()
rows = get_rows(
project.task["auth_sheets"],
{ "sheets": {
"sheet": project.task["sheet"],
"tab": "Segments",
"range": "A2:Z"
}}
)
rows = rows_pad(rows, 13, "")
# Build list of segements skipping only deletes, track changes
for row in rows:
# inserts do not have an ID, skip them
if not lookup_id(row[3]): continue
patches.setdefault(
row[3],
{ "operation": "Segments",
"action": "PATCH",
"partner": row[0],
"advertiser": row[1],
"campaign": row[2],
"insertion_order": row[3],
"parameters": {
"advertiserId": lookup_id(row[1]),
"insertionOrderId": lookup_id(row[3]),
"body": {
"budget": {
"budgetSegments": []
}
}
}
}
)
if row[4] == "DELETE":
changed.add(row[3])
else:
patches[row[3]]["parameters"]["body"]["budget"]["budgetSegments"].append({
"dateRange": {
"startDate": date_edited(row[6]),
"endDate": date_edited(row[8])
},
"budgetAmountMicros": float(row[10]) * 100000,
"description": row[12]
})
if row[5] != row[6] or row[7] != row[8] or row[9] != row[10] or row[11] != row[12]:
changed.add(row[3])
# Remove any patches where segments have not changed
for io in list(patches.keys()):
if io not in changed:
del patches[io]
patches = list(patches.values())
patch_masks(patches)
if commit:
insertion_order_commit(patches)
else:
patch_preview(patches)
