Script 779: Pacing Campaign Bulk Sheet

Purpose:

The Python script processes campaign data to adjust daily budgets based on pacing and traffic conditions.

To Elaborate

The script is designed to manage and adjust daily budgets for advertising campaigns based on specific pacing and traffic conditions. It filters out campaigns that have ended and focuses on those with active traffic. The script then evaluates the recommended daily budget for each campaign, applying adjustments if the budget is below a certain threshold or if the campaign is underpacing with limited days remaining. Additionally, it flags campaigns where the recommended daily budget exceeds a specified amount, ensuring that budget alerts are set for further review. This process helps in optimizing the allocation of advertising budgets to ensure effective campaign pacing and spending.

Walking Through the Code

  1. Data Filtering:
    • The script begins by filtering out campaigns that have ended, ensuring only active campaigns are considered.
    • It further narrows down the dataset to include only campaigns with active traffic.
  2. Budget Evaluation:
    • For campaigns with a recommended daily budget below $200, the script retains the recommended budget value, rounding it to two decimal places.
    • It introduces a “Daily Budget Adjustment Factor” for campaigns that are underpacing and have five or fewer days remaining, setting this factor to 0.05.
  3. Budget Adjustment:
    • The script converts the daily budget column to a numeric format to ensure accurate calculations.
    • It applies the adjustment factor to the daily budget for applicable campaigns, recalculating the budget accordingly.
  4. Budget Alert Configuration:
    • Campaigns with a recommended daily budget of $200 or more are flagged for review, with a “Daily Budget Alert” set to ‘Flagged’.
  5. Output Preparation:
    • The final filtered and adjusted data is prepared for output, with columns renamed to match the desired output format.

Vitals

  • Script ID : 779
  • Client ID / Customer ID: 1306927631 / 60270139
  • Action Type: Bulk Upload
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Daily Budget, Pacing Calculation Date
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: ascott@marinsoftware.com (ascott@marinsoftware.com)
  • Created by ascott@marinsoftware.com on 2024-03-06 22:29
  • Last Updated by ascott@marinsoftware.com on 2024-09-11 18:04
> 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
94

## 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'

# 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].apply(lambda x: round(x, 2))
)

# Add the new logic for Daily Budget Adjustment Factor
# Ensure the column exists before setting values
if BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR not in filteredDf.columns:
    filteredDf[BULK_COL_DAILY_BUDGET_ADJUSTMENT_FACTOR] = np.nan

# Now you can safely set the value
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])                                 
# Convert the 'RPT_COL_DAILY_BUDGET' column to numeric
filteredDf[RPT_COL_DAILY_BUDGET] = pd.to_numeric(filteredDf[RPT_COL_DAILY_BUDGET], errors='coerce')

# 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
})

# Assuming you want to display or utilize outputDf
print(outputDf)

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

comments powered by Disqus