Script 677: Budget Pacing Example

Purpose

Example of what Budget Pacing through script could look like

To Elaborate

This Python script solves the problem of allocating budgets to different campaigns based on various factors such as remaining budget, remaining days in the month, historical spend, spend potential, campaigns with spend in the lookback period, and minimum daily budget. The script calculates the full-potential spend for each campaign, removes date segmentation, calculates the MTD spend, allocates budget to recently trafficking campaigns, calculates the budget allocation ratio, calculates the remaining budget, allocates the remaining budget to each campaign, calculates the SBA daily budget, and calculates the budget-level pacing compliance percentage. The script generates an output dataframe with the updated budget information for campaigns that have changed.

Walking Through the Code

The script starts by defining a configurable parameter for the minimum daily budget.
It then defines various column constants used in the script.
The script sets the input dataframe’s index to the budget column and groups the dataframe by the budget column.
It calculates the full-potential spend for each campaign by adjusting the historical spend based on the lost impression share due to budget.
The script calculates the MTD spend by summing the spend for each campaign within the current month.
It aggregates the campaign data by various columns and applies aggregation functions to calculate various metrics.
The script filters out inactive campaigns and campaigns without spend in the lookback period.
It calculates the budget allocation ratio by capping the full-potential spend at 2X and comparing it to the total spend within the same budget group.
The script calculates the remaining budget for each budget group by subtracting the MTD spend from the budget target.
It allocates the remaining budget to each campaign based on the budget allocation ratio.
The script calculates the SBA daily budget by dividing the allocated budget by the number of days left in the current month.
It calculates the budget-level pacing compliance percentage by dividing the MTD spend by the prorated total budget.
The script generates an output dataframe with the updated budget information for campaigns that have changed.
If no changes are detected, an empty dataframe is returned.

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 2024-05-15 07:44:05 GMT

05 Feb 2024

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

MarinOne Scripts Creator's Corner