Script 1727: Pacing Campaign Bulk Sheet

Purpose:

The Python script processes campaign data to adjust daily budgets based on pacing cycles and specific business rules.

To Elaborate

The script is designed to manage and adjust the daily budgets of advertising campaigns based on their pacing cycles and specific criteria. It filters out campaigns that have ended and focuses on those with active traffic. The script then applies a daily budget adjustment factor for campaigns that are underpacing and nearing the end of their pacing cycle. Additionally, it ensures that campaigns with a CPM (Cost Per Mille) goal meet a minimum daily budget. The script also flags campaigns with recommended daily budgets exceeding a certain threshold for further review. This process helps in optimizing budget allocation and ensuring campaigns meet their performance goals efficiently.

Walking Through the Code

  1. Data Filtering:
    • The script begins by filtering out campaigns that have ended, focusing only on those with active traffic.
    • It uses specific columns from the input data to identify and exclude campaigns that do not meet the criteria.
  2. Daily Budget Adjustment:
    • The script checks if a daily budget adjustment factor column exists; if not, it creates one.
    • It applies a 5% adjustment factor to campaigns that are underpacing and have five or fewer days remaining in their pacing cycle.
  3. Budget Conversion and Adjustment:
    • The daily budget column is converted to a numeric format to ensure calculations can be performed.
    • The adjustment factor is applied to the daily budget for applicable campaigns.
  4. Minimum Budget Enforcement:
    • A function is defined and applied to ensure that campaigns with a CPM goal have a minimum daily budget of $0.5.
  5. Budget Alert Configuration:
    • The script flags campaigns with recommended daily budgets over $200 for review, marking them as ‘Flagged’ or ‘Checked’ based on existing alerts.
  6. Output Preparation:
    • The final filtered data is renamed with appropriate column headers and prepared for output, ensuring it meets the required format for further processing or review.

Vitals

  • Script ID : 1727
  • Client ID / Customer ID: 1306928777 / 60270139
  • Action Type: Bulk Upload
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Daily Budget, Daily Budget Adjustment Factor, Daily Budget Alert
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Arayla Caldwell (acaldwell@marinsoftware.com)
  • Created by Arayla Caldwell on 2025-02-07 22:11
  • Last Updated by Arayla Caldwell on 2025-02-07 22:11
> See it in Action

Python Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

## name: Pacing - Campaign Bulk Sheet
## description:
##  
## 
## author: Jesus Garza
## created: 2024-07-03
## 7/1 Updated version with Daily Budget Adjustment Factor 

today = datetime.datetime.now().date()  # Removed CLIENT_TIMEZONE for simplicity

# Primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_AUTO_PACING_CYCLE_START_DATE = 'Auto. Pacing Cycle Start Date'
RPT_COL_AUTO_PACING_CYCLE_END_DATE = 'Auto. Pacing Cycle End Date'
RPT_COL_AUTO_PACING_CYCLE_DAYS_ELAPSED = 'Auto. Pacing Cycle Days Elapsed'
RPT_COL_AUTO_PACING_CYCLE_DAYS_REMAINING = 'Auto. Pacing Cycle Days Remaining'
RPT_COL_AUTO_PACING_CYCLE_PACING = 'Auto. Pacing Cycle Pacing'
RPT_COL_AUTO_PACING_CYCLE_THRESHOLD = 'Auto. Pacing Cycle Threshold'
RPT_COL_TOTAL_TARGET_SPEND_PER_IMPRVIEWS = 'Total Target (Spend/Impr./Views)'
RPT_COL_TOTAL_DAYS = 'Total Days'
RPT_COL_TOTAL_DAYS_ELAPSED = 'Total Days Elapsed'
RPT_COL_TOTAL_PACING = 'Total Pacing'
RPT_COL_DELIVERY_STATUS = 'Delivery Status'
RPT_COL_RECOMMENDED_DAILY_BUDGET = 'Recommended Daily Budget'
RPT_COL_DAILY_BUDGET = 'Daily Budget'
RPT_COL_PACING_CALCULATION_DATE = 'Pacing Calculation Date'
RPT_COL_SOCIAL_BUDGET = 'Social Budget'
RPT_COL_SOCIAL_BUDGET_UPDATE_STATUS = 'Social Budget Update Status'
RPT_COL_AUTO_PACING_CYCLE_PUB_COST = 'Auto. Pacing Cycle Pub. Cost'
RPT_COL_AUTO_PACING_CYCLE_IMPR = 'Auto. Pacing Cycle Impr.'
RPT_COL_AUTO_PACING_CYCLE_CLICKS = 'Auto. Pacing Cycle Clicks'
RPT_COL_AUTO_PACING_CYCLE_VIEWS = 'Auto. Pacing Cycle Views'
RPT_COL_SBA_TRAFFIC = 'SBA Traffic'
RPT_COL_DAILY_BUDGET_ALERT = 'Daily Budget Alert'
RPT_COL_GOAL = 'Goal'

# Output columns
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_DAILY_BUDGET = 'Daily Budget'
BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR = 'Daily Budget Adjustment Factor'
BULK_COL_DAILY_BUDGET_ALERT = 'Daily Budget Alert'

# First filter: Exclude campaigns with 'Campaign Ended'
campaigns_not_ended = inputDf[inputDf[RPT_COL_AUTO_PACING_CYCLE_THRESHOLD] != 'Campaign Ended'].copy()

# Second filter: From the remaining, only include those where SBA Traffic is 'Traffic'
filteredDf = campaigns_not_ended[campaigns_not_ended[RPT_COL_SBA_TRAFFIC] == 'Traffic'].copy()

# Apply necessary operations on filteredDf
filteredDf[RPT_COL_DAILY_BUDGET] = np.where(filteredDf[RPT_COL_RECOMMENDED_DAILY_BUDGET] >= 200, "", filteredDf[RPT_COL_RECOMMENDED_DAILY_BUDGET])

# Add the new logic for Daily Budget Adjustment Factor
if BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR not in filteredDf.columns:
    filteredDf[BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR] = np.nan

filteredDf.loc[(filteredDf[RPT_COL_AUTO_PACING_CYCLE_DAYS_REMAINING] <= 5) & 
               (filteredDf[RPT_COL_AUTO_PACING_CYCLE_THRESHOLD] == 'Underpacing'), 
               BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR] = 0.05

# Convert the 'RPT_COL_DAILY_BUDGET' column to numeric
filteredDf[RPT_COL_DAILY_BUDGET] = pd.to_numeric(filteredDf[RPT_COL_DAILY_BUDGET], errors='coerce')

# Apply the Daily Budget Adjustment Factor to the Daily Budget column
filteredDf.loc[filteredDf[BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR].notnull(), 
               RPT_COL_DAILY_BUDGET] = filteredDf.loc[filteredDf[BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR].notnull(), 
                                                       RPT_COL_DAILY_BUDGET] * (1 + filteredDf[BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR])

# Function to ensure minimum daily budget for CPM goal
def apply_minimum_budget_for_cpm(df, goal_col, budget_col, minimum_budget=0.5):
    df.loc[df[goal_col].str.lower() == 'cpm', budget_col] = df[budget_col].apply(lambda x: max(x, minimum_budget) if pd.notnull(x) else minimum_budget)

# Apply the minimum budget function
apply_minimum_budget_for_cpm(filteredDf, RPT_COL_GOAL, RPT_COL_DAILY_BUDGET)

# Configured 'Daily Budget Alert' and set to 'Flagged' for daily budgets exceeding $200
filteredDf[BULK_COL_DAILY_BUDGET_ALERT] = filteredDf.apply(
    lambda row: 'Checked' if row[RPT_COL_RECOMMENDED_DAILY_BUDGET] >= 200 and row[RPT_COL_DAILY_BUDGET_ALERT] == 'Checked' else
               'Flagged' if row[RPT_COL_RECOMMENDED_DAILY_BUDGET] >= 200 else '', axis=1)

# Assign the final filtered DataFrame to outputDf with correct column names
outputDf = filteredDf.rename(columns={
    RPT_COL_ACCOUNT: BULK_COL_ACCOUNT,
    RPT_COL_CAMPAIGN: BULK_COL_CAMPAIGN,
    RPT_COL_DAILY_BUDGET: BULK_COL_DAILY_BUDGET,
    BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR: BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR,
    BULK_COL_DAILY_BUDGET_ALERT: BULK_COL_DAILY_BUDGET_ALERT
})

# Display the final output
print(outputDf)

Post generated on 2025-03-11 01:25:51 GMT

comments powered by Disqus