Script 1529: Auto Pause & Re enable

Purpose

The Python script manages the pausing and resuming of advertising campaigns based on their monthly budget utilization.

To Elaborate

The script is designed to automate the management of advertising campaigns by pausing them when their month-to-date (MTD) spending approaches or exceeds their allocated monthly budget, and resuming them when the spending falls below the budget threshold. This is achieved by comparing the MTD spend against the monthly budget stored in the campaign’s strategy. The script also ensures that these actions are only taken within a specified campaign end date. It uses a safety margin to account for system lags and non-linear spending patterns, ensuring that campaigns are paused or resumed at the right time. The script processes data locally or on a server, depending on the environment, and uses a pickled data source for local execution. It also includes mechanisms to handle duplicate campaigns and ensure data integrity throughout the process.

Walking Through the Code

Configuration and Setup
- The script begins by defining a configurable parameter, BUDGET_CAP_SAFETY_MARGIN, which determines how close the MTD spend can get to the monthly budget before pausing.
- It checks if the script is running on a server or locally, loading data from a pickled file if running locally.
Data Preparation
- The script loads the primary data source into a DataFrame and ensures that certain columns are of the correct data type, such as converting budget targets to numeric and date columns to date types.
- It fills any missing values with blanks to prevent comparison errors.
Campaign Management Logic
- The script calculates the MTD budget group spend for each campaign and identifies campaigns that need to be paused or resumed based on their spending relative to their budget.
- It sets flags for campaigns to be paused or resumed and updates their status accordingly.
Traffic Management
- The script determines if campaigns should be trafficked (paused or resumed) based on their status, traffic flag, and end date.
- It updates the campaign status and pause date for campaigns that meet the criteria for pausing or resuming.
Output Preparation
- The script cleans up any orphaned pause dates and selects only the changed rows for output.
- It renames columns for the output DataFrame and writes the results to CSV files if running in local development mode.

Vitals

Script ID : 1529
Client ID / Customer ID: 1306922277 / 60268979
Action Type: Bulk Upload (Preview)
Item Changed: Campaign
Output Columns: Account, Campaign, Auto Pause Date, Auto Pause Rec. Campaign Status, Status, Campaign ID
Linked Datasource: M1 Report
Reference Datasource: None
Owner: ascott@marinsoftware.com (ascott@marinsoftware.com)
Created by ascott@marinsoftware.com on 2024-11-15 19:51
Last Updated by Michael Huang on 2024-11-27 03:01

> See it in Action

Python Code

### name: Intraday Budget Cap via Strategy
## description:
##  Pause campaigns when MTD spend reaches Monthly Budget (stored in Strategy)
##  Resume previously paused campaigns when MTD spend falls below Monthly Budget
##  Skip trafficking Pause/Resume if outside of Auto Pause Campaign End Date
## 
## author: Michael S. Huang
## created: 2024-11-27
## 

##### Configurable Param #####
# Define how close MTD spend can get to Monthly Budget before being Paused
#  - compensates for lag in system
#  - compendates for non-linearity in intraday spend
BUDGET_CAP_SAFETY_MARGIN = 0.01 # set to 1%
##############################


########### START - Local Mode Config ###########
# Step 1: Uncomment download_preview_input flag and run Preview successfully with the Datasources you want
download_preview_input=False
# Step 2: In MarinOne, go to Scripts -> Preview -> Logs, download 'dataSourceDict' pickle file, and update pickle_path below
pickle_path = '/Users/mhuang/Downloads/pickle/alumni_venture_strategy_level_autopause_20241127_3.pkl'
# Step 3: Copy this script into local IDE with Python virtual env loaded with pandas and numpy.
# Step 4: Run locally with below code to init dataSourceDict

# determine if code is running on server or locally
def is_executing_on_server():
    try:
        # Attempt to access a known restricted builtin
        dict_items = dataSourceDict.items()
        return True
    except NameError:
        # NameError: dataSourceDict object is missing (indicating not on server)
        return False

local_dev = False

if is_executing_on_server():
    print("Code is executing on server. Skip init.")
elif len(pickle_path) > 3:
    print("Code is NOT executing on server. Doing init.")
    local_dev = True
    # load dataSourceDict via pickled file
    import pickle
    dataSourceDict = pickle.load(open(pickle_path, 'rb'))

    # print shape and first 5 rows for each entry in dataSourceDict
    for key, value in dataSourceDict.items():
        print(f"Shape of dataSourceDict[{key}]: {value.shape}")
        # print(f"First 5 rows of dataSourceDict[{key}]:\n{value.head(5)}")

    # set outputDf same as inputDf
    inputDf = dataSourceDict["1"]
    outputDf = inputDf.copy()

    # setup timezone
    import datetime
    # Chicago Timezone is GMT-5. Adjust as needed.
    CLIENT_TIMEZONE = datetime.timezone(datetime.timedelta(hours=-5))

    # import pandas
    import pandas as pd
    import numpy as np

    # Printing out the version of Python, Pandas and Numpy
    # import sys
    # python_version = sys.version
    # pandas_version = pd.__version__
    # numpy_version = np.__version__

    # print(f"python version: {python_version}")
    # print(f"pandas version: {pandas_version}")
    # print(f"numpy version: {numpy_version}")

    # other imports
    import re
    import urllib

    # import Marin util functions
    from marin_scripts_utils import tableize, select_changed
else:
    print("Running locally but no pickle path defined. dataSourceDict not loaded.")
    exit(1)
########### END - Local Mode Setup ###########

today = datetime.datetime.now(CLIENT_TIMEZONE).date()

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_CAMPAIGN_ID = 'Campaign ID'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_AUTO_PAUSE_STATUS = 'Auto Pause Status'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_STRATEGY_TARGET = 'Strategy Target'
RPT_COL_AUTO_PAUSE_DATE = 'Auto Pause Date'
RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS = 'Auto Pause Rec. Campaign Status'
RPT_COL_STRATEGY_CONSTRAINT_TYPE = 'Strategy Constraint Type'
RPT_COL_AUTO_PAUSE_CAMPAIGN_END_DATE = 'Auto Pause Campaign End Date'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_STATUS = 'Status'
BULK_COL_AUTO_PAUSE_DATE = 'Auto Pause Date'
BULK_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS = 'Auto Pause Rec. Campaign Status'
BULK_COL_CAMPAIGN_ID = 'Campaign ID'

### User Code Starts Here

originalDf = dataSourceDict["1"]

# # Workaround: COMBINE duplicate Meta campaigns
# originalDf = originalDf.groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN], as_index=False).agg({
#     RPT_COL_PUB_COST: 'sum',
#     RPT_COL_CAMPAIGN_STATUS: 'first',
#     RPT_COL_AUTO_PAUSE_STATUS: 'first',
#     RPT_COL_STRATEGY: 'first',
#     RPT_COL_STRATEGY_TARGET: 'first',
#     RPT_COL_AUTO_PAUSE_DATE: 'first',
#     RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS: 'first'
# })

# Make inputDf a copy of original to keep dataSourceDict pristine
inputDf = originalDf.copy()

# define some intermediate columns
COL_MTD_BUDGET_GROUP_SPEND = 'mtd_budget_group_spend'

# define Status values
VAL_STATUS_ACTIVE = 'Active'
VAL_STATUS_PAUSED = 'Paused'
VAL_BLANK = ''

print("inputDf shape", inputDf.shape)
print("inputDf info", inputDf.info())

## force expected types
# Convert RPT_COL_SBA_MONTHLY_BUDGET to numeric, coercing errors to NaN
inputDf[RPT_COL_STRATEGY_TARGET] = pd.to_numeric(inputDf[RPT_COL_STRATEGY_TARGET], errors='coerce')
# Replace NaN values with 0.0 if that's the desired behavior
inputDf[RPT_COL_STRATEGY_TARGET].fillna(0.0, inplace=True)
# Force RPT_COL_AUTO_PAUSE_DATE to be Date type
inputDf[RPT_COL_AUTO_PAUSE_DATE] = pd.to_datetime(inputDf[RPT_COL_AUTO_PAUSE_DATE], errors='coerce').dt.date
# Force RPT_COL_AUTO_PAUSE_CAMPAIGN_END_DATE to be Date type
inputDf[RPT_COL_AUTO_PAUSE_CAMPAIGN_END_DATE] = pd.to_datetime(inputDf[RPT_COL_AUTO_PAUSE_CAMPAIGN_END_DATE], errors='coerce').dt.date


# HACK: replace nan with empty strings so comparison doesn't fail
inputDf.fillna(VAL_BLANK, inplace=True)

# Clear out old Rec Status so they don't get trafficked
inputDf[RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS] = VAL_BLANK

# Calculate MTD Budget Group Spend
inputDf[COL_MTD_BUDGET_GROUP_SPEND] = inputDf.groupby(RPT_COL_STRATEGY)[RPT_COL_PUB_COST].transform('sum')

# Recommend to Pause camapigns with MTD Budget Group Spend over Monthly Budget (by a margin)
has_monthly_group_budget = inputDf[RPT_COL_STRATEGY_TARGET] > 0.0
over_spent_campaigns = inputDf[COL_MTD_BUDGET_GROUP_SPEND] >= inputDf[RPT_COL_STRATEGY_TARGET] * (1 - BUDGET_CAP_SAFETY_MARGIN)
campaigns_to_pause = has_monthly_group_budget & over_spent_campaigns

inputDf.loc[campaigns_to_pause, 'pause'] = 1
print(f"campaigns_to_pause count: {sum(campaigns_to_pause)}")
if campaigns_to_pause.any():
    print("campaigns_to_pause campaigns", tableize(inputDf.loc[campaigns_to_pause].head()))

inputDf.loc[ campaigns_to_pause, \
             RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS \
           ] = VAL_STATUS_PAUSED
 
# Recommend to reactivate campaigns with MTD Budget Group Spend under Monthly Group Budget (by a margin)
# but limited to campaigns with SBA Pause Date populated 10 digit date
under_spent_campaigns = inputDf[COL_MTD_BUDGET_GROUP_SPEND] < inputDf[RPT_COL_STRATEGY_TARGET] * (1 - BUDGET_CAP_SAFETY_MARGIN)
sba_paused_campaigns = inputDf[RPT_COL_AUTO_PAUSE_DATE].astype('str').str.len() >= 10
campaigns_to_resume = under_spent_campaigns & sba_paused_campaigns

inputDf.loc[campaigns_to_resume, 'resume'] = 1
print(f"campaigns_to_resume count: {sum(campaigns_to_resume)}")
if campaigns_to_resume.any():
    print("campaigns_to_resume", tableize(inputDf.loc[campaigns_to_resume].head()))

inputDf.loc[ campaigns_to_resume, \
             RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS  \
           ] = VAL_STATUS_ACTIVE

## Actually taffic PAUSE
# only traffic if within auto pause campaign end date and traffic flag is set
has_traffic_flag = inputDf[RPT_COL_AUTO_PAUSE_STATUS].astype(str).str.lower() == 'traffic'
print(f"has_traffic_flag count: {sum(has_traffic_flag)}")
before_end_date = (inputDf[RPT_COL_AUTO_PAUSE_CAMPAIGN_END_DATE].isna()) | \
                  (inputDf[RPT_COL_AUTO_PAUSE_CAMPAIGN_END_DATE] >= pd.to_datetime(today))
print(f"before_end_date count: {sum(before_end_date)}")
should_traffic =  has_traffic_flag & before_end_date
print(f"should_traffic count: {sum(should_traffic)}")

should_traffic_pause = should_traffic & \
                       campaigns_to_pause & \
                       (inputDf[RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS] == VAL_STATUS_PAUSED) & \
                       (inputDf[RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS] != inputDf[RPT_COL_CAMPAIGN_STATUS])


inputDf.loc[should_traffic_pause, 'traffic_pause'] = 1
print(f"should_traffic_pause count: {sum(should_traffic_pause)}")
if should_traffic_pause.any():
    print("should_traffic_pause campaigns", tableize(inputDf.loc[should_traffic_pause].head()))


inputDf.loc[should_traffic_pause, RPT_COL_CAMPAIGN_STATUS] = inputDf.loc[should_traffic_pause, RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS]
inputDf.loc[should_traffic_pause, RPT_COL_AUTO_PAUSE_DATE] = today.strftime('%Y-%m-%d')

## Actually taffic RESUME

should_traffic_resume = should_traffic & \
                        campaigns_to_resume & \
                       sba_paused_campaigns & \
                       (inputDf[RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS] == VAL_STATUS_ACTIVE) & \
                       (inputDf[RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS] != inputDf[RPT_COL_CAMPAIGN_STATUS])


inputDf.loc[should_traffic_resume, 'traffic_resume'] = 1
print(f"should_traffic_resume count: {sum(should_traffic_resume)}")
if should_traffic_resume.any():
    print("should_traffic_resume campaigns", tableize(inputDf.loc[should_traffic_resume].head()))

inputDf.loc[should_traffic_resume, RPT_COL_CAMPAIGN_STATUS] = inputDf.loc[should_traffic_resume, RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS]
inputDf.loc[should_traffic_resume, RPT_COL_AUTO_PAUSE_DATE] = VAL_BLANK

## Prepare Output

# Cleanup. RPT_COL_AUTO_PAUSE_DATE is a marker to indicate this Script actioned the Pause. If not Paused, for whatever reason, then a non-blank RPT_COL_AUTO_PAUSE_DATE causes confusion. 
orphan_pause_date = sba_paused_campaigns & (inputDf[RPT_COL_CAMPAIGN_STATUS] == VAL_STATUS_ACTIVE)
inputDf.loc[orphan_pause_date, RPT_COL_AUTO_PAUSE_DATE] = VAL_BLANK
print(f"Cleaned up {orphan_pause_date.sum()} orphaned {RPT_COL_AUTO_PAUSE_DATE}")

# only include changed rows in bulk file
print(f"select_changed with inputDf shape {inputDf.shape} and originalDf shape {originalDf.shape}")
(outputDf, debugDf) = select_changed(inputDf, \
                                    originalDf, \
                                    diff_cols = [ \
                                        RPT_COL_CAMPAIGN_STATUS, \
                                        RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS, \
                                    ], \
                                    select_cols = [ \
                                        RPT_COL_ACCOUNT, \
                                        RPT_COL_CAMPAIGN, \
                                        RPT_COL_CAMPAIGN_ID, \
                                        RPT_COL_CAMPAIGN_STATUS, \
                                        RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS, \
                                        RPT_COL_AUTO_PAUSE_DATE, \
                                    ], \
                                    merged_cols=[RPT_COL_PUBLISHER, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_CAMPAIGN_ID] \
                                    )


changed = (debugDf[RPT_COL_CAMPAIGN_STATUS+'_new'] != debugDf[RPT_COL_CAMPAIGN_STATUS+'_orig']) | \
          (debugDf[RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS+'_new'] != debugDf[RPT_COL_AUTO_PAUSE_REC_CAMPAIGN_STATUS+'_orig'])

debugDf.loc[changed, 'changed'] = 1
print(f"changed count: {sum(changed)}")
if changed.any():
    print("changed campaigns", tableize(debugDf.loc[changed].head()))

# remember to use Bulk column header for Status
outputDf = outputDf.rename(columns = { \
                RPT_COL_CAMPAIGN_STATUS: BULK_COL_STATUS \
                })

print("outputDf shape", outputDf.shape)
print("outputDf", tableize(outputDf.tail(5)))

## local debug
if local_dev:
    output_filename = 'outputDf.csv'
    outputDf.to_csv(output_filename, index=False)
    print(f"Local Dev: Output written to: {output_filename}")

    debug_filename = 'debugDf.csv'
    debugDf.to_csv(debug_filename, index=False)
    print(f"Local Dev: Debug written to: {debug_filename}")
   

Post generated on 2024-11-27 06:58:46 GMT

15 Nov 2024

« Script 1527: NEUN MLAWS USFL MSPGM Budget Trafficking Script 1531: Auto Pause & Re Enable Based On Strategy Target »

MarinOne Scripts Creator's Corner