Script 595: SBA Budget Pacing 2

Purpose

Pace Daily Budget for each Strategy

To Elaborate

The Python script solves the problem of allocating budget to different campaigns based on various factors such as remaining budget, remaining days in the month, historical spend, spend potential, and minimum daily budget. The goal is to minimize lost impression share due to budget constraints and ensure that the budget is allocated efficiently to achieve the desired campaign performance.

Walking Through the Code

The script starts by defining a configurable parameter for the minimum daily budget.
It then defines column constants used throughout the script.
The script sets the current date and prints the shape and data types of the input dataframe.
It checks if the SBA allocation and budget pacing columns are of type float and converts them back to percent strings if necessary.
The input dataframe is set with the budget column as the index and grouped by the budget column.
The script calculates the full-potential spend by adjusting the historical spend based on the lost impression share due to budget.
It calculates the MTD spend by summing the spend for the current month.
The script aggregates the data by grouping it based on various columns and applying different aggregation functions.
Inactive campaigns (not active and without spend in the lookback period) are excluded from the allocation.
The script calculates the budget allocation ratio by capping the full-potential spend and comparing it to the total spend within the same budget group.
The remaining budget is calculated for each budget group by subtracting the MTD spend from the budget.
The remaining budget is allocated to each campaign based on the allocation ratio.
The script calculates the next day’s daily budget by dividing the allocated budget by the number of days left in the current month.
The budget-level pacing compliance percentage is calculated by dividing the MTD spend by the prorated total budget.
The script generates the output dataframe by selecting the changed campaigns and renaming the columns.
The output dataframe is sorted and printed.
If no changes are detected, an empty dataframe is returned.

Vitals

Script ID : 595
Client ID / Customer ID: 1306926583 / 60268435
Action Type: Bulk Upload
Item Changed: Campaign
Output Columns: Account, Campaign, SBA Allocation, SBA Budget Pacing, SBA Calculated Budget Daily, Daily Budget
Linked Datasource: M1 Report
Reference Datasource: None
Owner: emerryfield@marinsoftware.com (emerryfield@marinsoftware.com)
Created by emerryfield@marinsoftware.com on 2023-12-15 18:38
Last Updated by ascott@marinsoftware.com on 2024-01-04 18:31

> See it in Action

Python Code

#
# Populus Financial - 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: Eric H. Merryfield
#
# Created: 2023-12-14
# Updated: 2023-12-14
#

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

MINIMUM_DAILY_BUDGET = 0

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

RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DATE = 'Date'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_PUBLISHER_NAME = 'Publisher Name'
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_NAME, \
    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-03-10 06:34:12 GMT

15 Dec 2023

« Script 593: Scripts Brand vs Generic Dimension Autotag Script 597: Dimension Tags from Campaign Name »

MarinOne Scripts Creator's Corner