Script 677: Budget Pacing Example

Purpose:

The Python script optimizes budget allocation for advertising campaigns to minimize lost impression share due to budget constraints.

To Elaborate

The script addresses the challenge of efficiently allocating advertising budgets across multiple campaigns to minimize lost impression share due to budget limitations. It considers factors such as remaining budget, days left in the month, historical spending, and potential spending. The script aggregates data by budget groups and calculates the full potential spend, adjusting for lost impression share. It excludes inactive campaigns and those without recent spending. The script then calculates a budget allocation ratio, capping potential spend at twice the historical amount, and allocates the remaining budget based on this ratio. It also calculates the daily budget needed to pace spending evenly over the remaining days of the month. The script ensures that only campaigns flagged for traffic receive budget allocations and checks for changes in budget calculations to update only the necessary campaigns.

Walking Through the Code

Configurable Parameters:
- The script begins by defining a configurable parameter, MINIMUM_DAILY_BUDGET, which allows users to set a minimum threshold for daily budget allocation.
Data Preparation:
- The script sets up column constants for various data fields and initializes the input DataFrame, inputDf, with campaign data.
- It adjusts historical spend data by accounting for lost impression share due to budget constraints.
Data Aggregation:
- The script groups data by budget-related columns and aggregates metrics such as campaign status, daily budget, and spend target.
- It calculates the month-to-date (MTD) spend for each campaign.
Campaign Filtering:
- Inactive campaigns and those without recent spending are excluded from budget allocation.
Budget Allocation Ratio Calculation:
- The script calculates a capped full potential spend for each campaign and determines the allocation ratio within each budget group.
Remaining Budget Calculation:
- It computes the remaining budget for each budget group by subtracting the MTD spend from the spend target.
Budget Allocation:
- The remaining budget is allocated to each campaign based on the calculated allocation ratio.
Daily Budget Calculation:
- The script calculates the daily budget for the next day by dividing the allocated budget by the number of days left in the month.
- It ensures that only campaigns flagged for traffic receive budget allocations.
Pacing Compliance Calculation:
- The script calculates the pacing compliance percentage to ensure budgets are spent evenly throughout the month.
Output Generation:
- The script checks for changes in budget calculations and generates an output DataFrame, outputDf, with updated budget allocations for campaigns with changes.

Vitals

Script ID : 677
Client ID / Customer ID: 314475863 / 63970
Action Type: Bulk Upload (Preview)
Item Changed: Campaign
Output Columns: Account, Campaign, Daily Budget, SBA Allocation, SBA Budget Pacing, SBA Calculated Budget Daily
Linked Datasource: M1 Report
Reference Datasource: None
Owner: dwaidhas@marinsoftware.com (dwaidhas@marinsoftware.com)
Created by dwaidhas@marinsoftware.com on 2024-02-05 22:07
Last Updated by dwaidhas@marinsoftware.com on 2024-02-06 14:50

> See it in Action

Python Code

#
# 4 All Promos- Budget Pacing - Minimize Lost IS (Budget)
#
# Allocates according to:
# * Remaining budget for each Budget
# * Remaining days in month
# * Historical spend and spend potential
# * Campaigns with spend in lookback period
# * Minimum daily budget
#
# Author: Dana Waidhas
#
# Created: 2024-02-05
# Updated: 2024-02-06

##### Configurable Param #####

MINIMUM_DAILY_BUDGET = 0

##############################

RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DATE = 'Date'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_DAILY_BUDGET = 'Daily Budget'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_CONV = 'Conv.'
RPT_COL_IMPR_SHARE = 'Impr. share %'
RPT_COL_LOST_IMPR_SHARE_BUDGET = 'Lost Impr. Share (Budget) %'
RPT_COL_LOST_IMPR_SHARE_RANK = 'Lost Impr. Share (Rank) %'
RPT_COL_BUDGET = 'Budget'
RPT_COL_SPEND_TARGET = 'Spend Target'
RPT_COL_SBA_ALLOCATION = 'SBA Allocation'
RPT_COL_SBA_CALCULATED_BUDGET_DAILY = 'SBA Calculated Budget Daily'
RPT_COL_SBA_BUDGET_PACING = 'SBA Budget Pacing'
RPT_COL_SBA_TRAFFIC = 'SBA Traffic'

BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_DAILY_BUDGET = 'Daily Budget'
BULK_COL_SBA_ALLOCATION = 'SBA Allocation'
BULK_COL_SBA_BUDGET_PACING = 'SBA Budget Pacing'
BULK_COL_SBA_CALCULATED_BUDGET_DAILY = 'SBA Calculated Budget Daily'

COL_SPEND_FULL_POTENTIAL = 'spend_lookback_full_potential'
COL_SPEND_FULL_POTENTIAL_CAPPED = 'spend_lookback_full_potential_capped'
COL_SPEND_MTD = 'spend_mtd'
COL_SBA_ALLOCATION_NEW_FLOAT = RPT_COL_SBA_ALLOCATION + '_new_float'
COL_SBA_ALLOCATION_NEW = RPT_COL_SBA_ALLOCATION + '_new'
COL_BUDGET_REMAINING = 'budget_remaining'
COL_SBA_BUDGET_PACING_NEW = RPT_COL_SBA_BUDGET_PACING + '_new'
COL_BUDGET_REMAINING = 'budget_remaining'
COL_DAILY_BUDGET_NEW = RPT_COL_DAILY_BUDGET + '_new'
COL_SBA_CALCULATED_BUDGET_DAILY_NEW = RPT_COL_SBA_CALCULATED_BUDGET_DAILY + '_new'
COL_DAYS_REMAINING= 'days_remaining'
COL_DAYS_TOTAL= 'days_total'
COL_PACING_CALC = 'pacing_calc'

outputDf[BULK_COL_DAILY_BUDGET] = "<<YOUR VALUE>>"

today = datetime.datetime.now(CLIENT_TIMEZONE).date()
print("inputDf shape", inputDf.shape)
print("inputDf dtypes", inputDf.dtypes)

# change back to percent string
if inputDf[RPT_COL_SBA_ALLOCATION].dtype == "float":
    inputDf[RPT_COL_SBA_ALLOCATION] = round(inputDf[RPT_COL_SBA_ALLOCATION] * 100.0, 0).astype(str) + '%'
if inputDf[BULK_COL_SBA_BUDGET_PACING].dtype == "float":
    inputDf[BULK_COL_SBA_BUDGET_PACING] = round(inputDf[BULK_COL_SBA_BUDGET_PACING] * 100.0, 0).astype(str) + '%'

inputDf = inputDf.set_index([RPT_COL_BUDGET])
group_by_budget = inputDf.groupby(RPT_COL_BUDGET)

# ## Calculate Full-Potential Spend
# * Adjust Historical Spend by _Lost Impression Share due to Budget_ (see [Formula](https://docs.google.com/document/d/1EbCQ5z9Up8TZ6GISEeCaRSB3Fc15vCPCfeIydree23M/edit#bookmark=id.5fsx7jlseze6))
# 

adj_ratio = 1 + (inputDf[RPT_COL_LOST_IMPR_SHARE_BUDGET] / (1 - inputDf[RPT_COL_LOST_IMPR_SHARE_BUDGET]))

inputDf[COL_SPEND_FULL_POTENTIAL] = round(inputDf[RPT_COL_PUB_COST] * adj_ratio, 2)

# ## Remove Date Segmentation
# * Calculate MTD Spend

# SUM Series with Date index and only includes current month
def current_month_sum(x):
    x = x.sort_index()
    mtd = x[ (x.index.month == today.month) & (x.values > 0)]
    return mtd.sum()

groupby_cols = [ \
    RPT_COL_BUDGET, \
    RPT_COL_STRATEGY, \
    RPT_COL_PUBLISHER, \
    RPT_COL_ACCOUNT, \
    RPT_COL_CAMPAIGN, \
]


agg_spec = {
    RPT_COL_CAMPAIGN_STATUS: 'last', \
    RPT_COL_DAILY_BUDGET: 'last', \
    RPT_COL_SPEND_TARGET: 'last', \
    RPT_COL_SBA_ALLOCATION: 'last', \
    RPT_COL_SBA_CALCULATED_BUDGET_DAILY: 'last', \
    RPT_COL_SBA_BUDGET_PACING: 'last', \
    RPT_COL_SBA_TRAFFIC: 'last', \
    RPT_COL_CLICKS: 'sum', \
    RPT_COL_CONV: 'sum', \
    RPT_COL_PUB_COST: 'sum', \
    COL_SPEND_MTD: current_month_sum, \
    COL_SPEND_FULL_POTENTIAL: 'sum', \
}

inputDf[COL_SPEND_MTD] = inputDf[RPT_COL_PUB_COST]

df_campaign_agg = inputDf.reset_index() \
                         .set_index(RPT_COL_DATE) \
                         .groupby(groupby_cols) \
                         .agg(agg_spec) \
                         .reset_index() \
                         .set_index(RPT_COL_BUDGET)


# ## Only allocate budget for recently trafficking campaigns
# * Exclude Campaigns that are not ACTIVE and without spend in lookback period

inactive_campaigns = (df_campaign_agg[RPT_COL_CAMPAIGN_STATUS] != 'Active') & (df_campaign_agg[RPT_COL_PUB_COST] == 0)

df_campaign_agg = df_campaign_agg.loc[ ~inactive_campaigns ]


# ## Calculate Budget Allocation Ratio 
# * Cap full potential spend at 2X (don't spend twice as much as before)
# * Compare full potential spend for each campaign to total spend within same Budget budget group

df_campaign_agg[COL_SPEND_FULL_POTENTIAL_CAPPED] = df_campaign_agg \
    .apply(lambda row: min(row[COL_SPEND_FULL_POTENTIAL], 2 * row[RPT_COL_PUB_COST]), axis=1)


# use transform to calculate sum for each Budget and make it available on every row
# note: no need to build aggregate DataFrame and JOIN back to original

df_campaign_agg[COL_SBA_ALLOCATION_NEW_FLOAT] = 100.0 * \
        df_campaign_agg[COL_SPEND_FULL_POTENTIAL_CAPPED] / \
        df_campaign_agg.groupby(RPT_COL_BUDGET)[COL_SPEND_FULL_POTENTIAL_CAPPED].transform('sum')

df_campaign_agg[COL_SBA_ALLOCATION_NEW] = round(df_campaign_agg[COL_SBA_ALLOCATION_NEW_FLOAT],0).astype(str) + '%'

# 
# ## Calculate Remaining Budget
# * For each Budget budget group, calculate how much Budget is left by substracting Budget budget from MTD Budget spend

df_campaign_agg[COL_BUDGET_REMAINING] =  \
        df_campaign_agg[RPT_COL_SPEND_TARGET] - \
        df_campaign_agg.groupby(by=[RPT_COL_BUDGET])[COL_SPEND_MTD].sum()


# ## Allocate Budget
# * Allocate remaining budget to each campaign according to ratio calculated above

df_campaign_agg[COL_BUDGET_REMAINING] = round(df_campaign_agg[COL_BUDGET_REMAINING] * df_campaign_agg[COL_SBA_ALLOCATION_NEW_FLOAT] / 100.0, 1)


# ## Calculate SBA Daily Budget
# 
# * Calcualte next day Daily Budget by dividing allocated budget by number of Days left in the current month


today_numpy = pd.to_datetime(today).to_numpy().astype('datetime64[D]')
next_month_start = (today_numpy + pd.offsets.BMonthBegin()).to_numpy().astype('datetime64[D]')

# ### Traffic Budget
#  * only traffic budget for campaigns with flag enabled
df_campaign_agg[COL_DAILY_BUDGET_NEW] = np.nan
# campaigns to traffic
to_traffic = df_campaign_agg[RPT_COL_SBA_TRAFFIC].notnull() & \
                (df_campaign_agg[RPT_COL_SBA_TRAFFIC].astype(str).str.lower() == 'traffic')

# ## Calculate Budget-level Pacing compliance percentage. Ideally should be 100% each day.

# number of elapsed days
current_month_start = pd.to_datetime(today.replace(day=1)).to_numpy().astype('datetime64[D]')
days_left = (next_month_start - today_numpy).astype('timedelta64[D]').astype(int)
days_in_month = (next_month_start - current_month_start).astype('timedelta64[D]').astype(int)
df_campaign_agg[COL_DAYS_TOTAL] = days_in_month
prorated_ratio = (days_in_month - days_left) / days_in_month

print("today", today)
print("current_month_start", current_month_start)
print("next_month_start", next_month_start)
print("days_in_month", days_in_month)
print("days_left", days_left)
print("prorated_ratio", prorated_ratio)

df_campaign_agg[COL_DAYS_REMAINING] = days_left
df_campaign_agg[COL_SBA_CALCULATED_BUDGET_DAILY_NEW] = round(df_campaign_agg[COL_BUDGET_REMAINING] / days_left, 0)

# divide MTD spend by prorated total budget
mask = df_campaign_agg[RPT_COL_SPEND_TARGET] > 0
df_campaign_agg[COL_PACING_CALC] = round(100.0 * \
                                    df_campaign_agg.groupby(by=[RPT_COL_BUDGET])[COL_SPEND_MTD].sum() / \
                                    (prorated_ratio * df_campaign_agg[RPT_COL_SPEND_TARGET]), \
                                    0).astype(str) + '%'
df_campaign_agg.loc[mask, COL_SBA_BUDGET_PACING_NEW] = df_campaign_agg.loc[mask, COL_PACING_CALC]

# Debug DF with full details
df_spend_target = group_by_budget[[RPT_COL_SPEND_TARGET]].transform('max').dropna().drop_duplicates()
print("spend target", df_spend_target.to_string())

# ## Generate outputDf

# Check for changes
changed = df_campaign_agg[COL_SBA_CALCULATED_BUDGET_DAILY_NEW].notnull() & \
    ( \
       (df_campaign_agg[RPT_COL_SBA_CALCULATED_BUDGET_DAILY] != df_campaign_agg[COL_SBA_CALCULATED_BUDGET_DAILY_NEW]) | \
       (df_campaign_agg[RPT_COL_DAILY_BUDGET] != df_campaign_agg[COL_DAILY_BUDGET_NEW]) | \
       (df_campaign_agg[RPT_COL_SBA_ALLOCATION] != df_campaign_agg[COL_SBA_ALLOCATION_NEW]) | \
       (df_campaign_agg[RPT_COL_SBA_BUDGET_PACING] != df_campaign_agg[COL_SBA_BUDGET_PACING_NEW]) \
    )

print("Changed rows:", changed.sum())

# Debug
debugDf = df_campaign_agg.loc[changed] \
      .reset_index() \
      .sort_values(by=[RPT_COL_BUDGET, COL_DAILY_BUDGET_NEW, COL_SBA_CALCULATED_BUDGET_DAILY_NEW], ascending=False) 

# print("debugDf", tableize(debugDf))

# Only emit output for changed campaigns
if changed.sum() > 0:

    # construct outputDf
    outputDf = df_campaign_agg.loc[changed, [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, COL_DAILY_BUDGET_NEW, COL_SBA_CALCULATED_BUDGET_DAILY_NEW, COL_SBA_BUDGET_PACING_NEW, COL_SBA_ALLOCATION_NEW]] \
                      .copy() \
                      .rename(columns={ \
                            COL_DAILY_BUDGET_NEW: BULK_COL_DAILY_BUDGET, \
                            COL_SBA_CALCULATED_BUDGET_DAILY_NEW: BULK_COL_SBA_CALCULATED_BUDGET_DAILY, \
                            COL_SBA_BUDGET_PACING_NEW: BULK_COL_SBA_BUDGET_PACING, \
                            COL_SBA_ALLOCATION_NEW: BULK_COL_SBA_ALLOCATION, \
                        }) \
                      .reset_index() \
                      .sort_values(by=[RPT_COL_BUDGET, BULK_COL_DAILY_BUDGET, BULK_COL_SBA_CALCULATED_BUDGET_DAILY], ascending=False) \
                      .drop(RPT_COL_BUDGET, axis=1)

    print("outputDf shape", outputDf.shape)

else:
    print("No changes detected, returning an empty dataframe")
    outputDf = pd.DataFrame(columns=[BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_DAILY_BUDGET, BULK_COL_SBA_CALCULATED_BUDGET_DAILY, BULK_COL_SBA_BUDGET_PACING, BULK_COL_SBA_ALLOCATION])

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

05 Feb 2024

« Script 675: Campaign Name Dimension Auto Tagging Region Script 681: Dimension Value From Campaign Names »

MarinOne Scripts Creator's Corner