def line_item_insert(commit=False): inserts = [] rows = get_rows( project.task["auth_bigquery"], { "bigquery": { "dataset": project.task["dataset"], "table":"INSERT_LineItems", }}, as_object=True ) for row in rows: inserts.append({ "operation": "Line Items", "action": "INSERT", "partner": None, "advertiser": row['advertiserId'], "campaign": row['campaignId'], "line_item": row['displayName'], "parameters": { "advertiserId": row['advertiserId'], "body":row } }) patch_preview(inserts) if commit: line_item_commit(inserts)
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 insertion_order_insert(config, task, commit=False): inserts = [] rows = get_rows( config, task["auth_bigquery"], { "bigquery": { "dataset": task["dataset"], "table":"INSERT_InsertionOrders", }}, as_object=True ) for row in rows: inserts.append({ "operation": "Insertion Orders", "action": "INSERT", "partner": None, "advertiser": row['advertiserId'], "campaign": row['campaignId'], "insertion_order": row['displayName'], "parameters": { "advertiserId": row['advertiserId'], "body":row } }) patch_preview(config, task, inserts) if commit: insertion_order_commit(config, task, inserts)
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)