Script 747: Pacing Campaign Bulk Sheet

Purpose

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

To Elaborate

The script is designed to manage and adjust the daily budgets of advertising campaigns based on their pacing cycles. It filters out campaigns that have ended and focuses on those with active traffic. The script applies a daily budget adjustment factor to campaigns that are underpacing and have five or fewer days remaining in their pacing cycle. This adjustment is intended to optimize the budget allocation to meet campaign goals. Additionally, the script flags campaigns with recommended daily budgets exceeding $200 for further review. The final output is a DataFrame with updated budget information and alerts, ready for further analysis or action.

Walking Through the Code

  1. Initialization and Data Preparation
    • The script begins by clearing the ‘Daily Budget Adjustment Factor’ column in the input DataFrame.
    • It sets up the primary data source and defines the necessary columns for processing.
  2. Filtering Campaigns
    • Campaigns that have ended are excluded from further processing.
    • From the remaining campaigns, only those with ‘Traffic’ in the ‘SBA Traffic’ column are retained.
  3. Budget Adjustments
    • The script checks if the ‘Daily Budget Adjustment Factor’ column exists and initializes it if necessary.
    • It applies a 5% adjustment factor to campaigns that are underpacing and have five or fewer days remaining in their pacing cycle.
    • The adjusted daily budgets are calculated and updated in the DataFrame.
  4. Alert Configuration
    • Campaigns with recommended daily budgets over $200 are flagged for review.
    • The script ensures that the ‘Daily Budget Alert’ column reflects these flags appropriately.
  5. Output Preparation
    • The final DataFrame is prepared with the necessary columns renamed for output.
    • The script concludes by printing the processed DataFrame, which contains the updated budget information and alerts.

Vitals

  • Script ID : 747
  • Client ID / Customer ID: 1306927167 / 60270139
  • Action Type: Bulk Upload
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Daily Budget, Daily Budget Alert, Daily Budget Adjustment Factor
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: ascott@marinsoftware.com (ascott@marinsoftware.com)
  • Created by ascott@marinsoftware.com on 2024-03-06 20:26
  • Last Updated by Jesus Garza on 2024-09-12 17:17
> 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

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

# Step 1: Clear the 'Daily Budget Adjustment Factor'
inputDf['Daily Budget Adjustment Factor'] = np.nan

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])

# 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

# 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_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 2024-11-27 06:58:46 GMT

comments powered by Disqus