Script 737: Profit Maximising Campaign Target (Preview)

Purpose

Preview version of Profit Maximising Campaign Target script

To Elaborate

The Python script analyzes campaign-level forecasts to identify profit maximizing targets and generates a bulk sheet to update targets. It supports Google Smart Bidding targets only, such as TargetCPA, TargetROAS, MaximizeConversions, and MaximizeConversionValue. The script aims to optimize campaign targets based on daily profit and gross profit, taking into account factors like budget type, bidding strategy, and damping percentages.

Walking Through the Code

The script starts by defining configurable parameters such as the tolerance of deviation from the expected target and the folders to limit the analysis to.
It then checks if the code is running on a server or locally and loads the necessary data accordingly.
The script cleans up the input data, converting certain columns to the correct data types and filtering the data based on folder IDs and campaign overrides.
Next, the script calculates the optimal targets for each campaign by sorting the data based on daily profit, gross profit, boost percentage, and target values.
Damping is applied to the ideal targets based on the current target and damping percentage using numpy.
The script calculates the expected and acceptable targets based on the bidding strategy and boost percentage.
The results are stored in a results dataframe, which includes columns for account, campaign, dampened target CPA, dampened target ROAS, acceptability, boost percentage, and bulk sheet columns for dampened and undampened targets.
The script selects only the changed rows and drops unnecessary columns from the output dataframe.
Finally, the output dataframe is renamed to match the bulk sheet column headers and is printed as a table. In local development mode, the output and debug dataframes are also saved as CSV files.

Vitals

Script ID : 737
Client ID / Customer ID: 1306927569 / 60270325
Action Type: Bulk Upload (Preview)
Item Changed: Campaign
Output Columns: Account, Campaign, Publisher Target CPA-ROAS Damped, Publisher Target CPA-ROAS UnDamped
Linked Datasource: FTP/Email Feed
Reference Datasource: None
Owner: Joe Southin (jsouthin@marinsoftware.com)
Created by Joe Southin on 2024-03-04 14:23
Last Updated by Grégory Pantaine on 2024-04-15 11:39

> See it in Action

Python Code

##
## name: Profit Maximising Campaign Target Trafficking
## description:
##  * Analyses campaign-level forecasts to identify profit maximising targets.  Generates bulk sheet to update targets
##  * Supports Google Smart Bidding targets only (TargetCPA, TargetROAS, MaximizeConversions, MaximizeConversionValue)
## 
## author: Joe Southin
## created: 2024-02-24
## updated: 2024-02-29  

##### Configurable Param #####
# Define tolerance of deviation from expected target
TARGET_TOLERANCE = 0.02
VAL_LIMIT_FOLDERS = [778828, 778910, 778826]
VAL_TARGET_MODE = 'Preview'
##############################


########### START - Local Mode Config ###########
# Step 1: Uncomment download_preview_input flag and run Preview successfully with the Datasources you want
download_preview_input=True
# Step 2: In MarinOne, go to Scripts -> Preview -> Logs, download 'dataSourceDict' pickle file, and update pickle_path below
pickle_path = '/Users/jsouthin/Downloads/datasource_dict_1709114229331.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=0))

    # 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"]
FEED_COL_ACCOUNT = 'Account'
FEED_COL_CAMPAIGN = 'Campaign'
FEED_COL_DAILY_BUDGET = 'daily_budget'
FEED_COL_BUDGET_TYPE = 'budget_type'
FEED_COL_CAMPAIGN_ID = 'campaign_id'
FEED_COL_CAMPAIGN_OVERRIDE = 'campaign_override'
FEED_COL_BIDDING_STRATEGY_OLD_TYPE = 'bidding_strategy_old_type'
FEED_COL_TARGET_CPA = 'target_cpa'
FEED_COL_TARGET_ROAS = 'target_roas'
FEED_COL_IS_PORTFOLIO = 'is_portfolio'
FEED_COL_IS_SHARED_BUDGET = 'is_shared_budget'
FEED_COL_FOLDER_ID = 'folder_id'
FEED_COL_START_DATE_TIME_ID = 'start_date_time_id'
FEED_COL_END_DATE_TIME_ID = 'end_date_time_id'
FEED_COL_OBJECTIVE_UNIT = 'objective_unit'
FEED_COL_CONSTRAINT_UNIT = 'constraint_unit'
FEED_COL_CONSTRAINT_VALUE = 'constraint_value'
FEED_COL_BID_AND_TARGET_DAMPING = 'bid_and_target_damping'
FEED_COL_BUDGET_DAMPING = 'budget_damping'
FEED_COL_BUDGET_CAP = 'budget_cap'
FEED_COL_FOLDER_BID_TRAFFICKING = 'folder_bid_trafficking'
FEED_COL_PUBLISHER_BUDGET_TRAFFICKING = 'publisher_budget_trafficking'
FEED_COL_PUBLISHER_BID_TRAFFICKING = 'publisher_bid_trafficking'
FEED_COL_BOOST_PERCENT = 'boost_percent'
FEED_COL_ABS_BOOST_PERCENT = 'abs_boost_percent'
FEED_COL_DIRECTION = 'direction'
FEED_COL_TARGET = 'target'
FEED_COL_DAILY_SPEND = 'daily_spend'
FEED_COL_DAILY_GROSS_PROFIT = 'daily_gross_profit'
FEED_COL_DAILY_PROFIT = 'daily_profit'
FEED_COL_DAYS = 'days'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_PUBLISHER_TARGET_CPA = 'Publisher Target CPA'
BULK_COL_PUBLISHER_TARGET_ROAS = 'Publisher Target ROAS'
outputDf[BULK_COL_PUBLISHER_TARGET_CPA] = "<<YOUR VALUE>>"
outputDf[BULK_COL_PUBLISHER_TARGET_ROAS] = "<<YOUR VALUE>>"
BULK_COL_DIM_DAMPED = 'Publisher Target CPA-ROAS Damped'
BULK_COL_DIM_UNDAMPED = 'Publisher Target CPA-ROAS UnDamped'

### User Code Starts Here

# intermediate columns
COL_DAMPENED_TARGET_CPA = FEED_COL_TARGET_CPA + '_dampened'
COL_DAMPENED_TARGET_ROAS = FEED_COL_TARGET_ROAS + '_dampened'
COL_EXPECTED = 'expected'
COL_ACCEPTABLE = 'acceptable'

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

# define ObjectiveUnit values
VAL_OBJECTIVE_UNIT_UNKNOWN = -1
VAL_OBJECTIVE_UNIT_CONVERSIONS = 1
VAL_OBJECTIVE_UNIT_REVENUE = 2
VAL_OBJECTIVE_UNIT_NONE = 3
 
VAL_CONSTRAINT_UNIT_UNKNOWN = -1
VAL_CONSTRAINT_UNIT_SPEND = 1
VAL_CONSTRAINT_UNIT_ROAS = 2
VAL_CONSTRAINT_UNIT_CPA = 3

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

## cleanup data
#inputDf.drop(columns=['Unnamed: 30'], inplace=True)

inputDf[FEED_COL_FOLDER_ID] = inputDf[FEED_COL_FOLDER_ID].astype(int)
inputDf[FEED_COL_CAMPAIGN_OVERRIDE] = inputDf[FEED_COL_CAMPAIGN_OVERRIDE].astype(int)


inputDf = inputDf[inputDf[FEED_COL_FOLDER_ID].isin(VAL_LIMIT_FOLDERS)]
inputDf = inputDf[inputDf[FEED_COL_CAMPAIGN_OVERRIDE] != 1]

if VAL_TARGET_MODE == 'Traffic':
    inputDf = inputDf[inputDf[FEED_COL_PUBLISHER_BID_TRAFFICKING]=='Traffic']


## force expected types
inputDf.loc[:,[FEED_COL_TARGET_CPA]] = pd.to_numeric(inputDf[FEED_COL_TARGET_CPA], errors='coerce')
inputDf.loc[:,[FEED_COL_TARGET_ROAS]] = pd.to_numeric(inputDf[FEED_COL_TARGET_ROAS], errors='coerce')


## Grab current target stored on zero boost; basis for change detection later
comparison_cols = [FEED_COL_ACCOUNT, FEED_COL_CAMPAIGN, FEED_COL_TARGET_CPA, FEED_COL_TARGET_ROAS]

original_df = inputDf.loc[inputDf[FEED_COL_BOOST_PERCENT] == 0, comparison_cols]

## maximize profit for picking out boost with highest daily profit
optimal_df = inputDf \
    .sort_values(by=[ \
                    FEED_COL_DAILY_PROFIT, \
                    FEED_COL_DAILY_GROSS_PROFIT, \
                    FEED_COL_ABS_BOOST_PERCENT, \
                    FEED_COL_TARGET
                ], \
                ascending=[ \
                    False, \
                    False, \
                    True, \
                    True \
                ]) \
    .groupby([FEED_COL_CAMPAIGN_ID, FEED_COL_FOLDER_ID]) \
    .first() \
    .reset_index()

## Apply damping to the ideal targets based on current target and damping percentage using numpy
optimal_df[COL_DAMPENED_TARGET_CPA] = np.where(optimal_df[FEED_COL_TARGET],
    np.where(
    optimal_df[FEED_COL_BIDDING_STRATEGY_OLD_TYPE].isin(['TargetCPA','MaximizeConversions']),
    np.clip(
        optimal_df[FEED_COL_TARGET],
        optimal_df[FEED_COL_TARGET_CPA] * (1 - optimal_df[FEED_COL_BID_AND_TARGET_DAMPING]/100),
        optimal_df[FEED_COL_TARGET_CPA] * (1 + optimal_df[FEED_COL_BID_AND_TARGET_DAMPING]/100)
        ).round(2),
    np.nan),optimal_df[FEED_COL_TARGET].round(2))


optimal_df[COL_DAMPENED_TARGET_ROAS] = np.where(optimal_df[FEED_COL_TARGET],
    np.where(
    optimal_df[FEED_COL_BIDDING_STRATEGY_OLD_TYPE].isin(['TargetROAS','MaximizeConversionValue']),
    np.clip(
        100 * optimal_df[FEED_COL_TARGET],
        optimal_df[FEED_COL_TARGET_ROAS] * (1 - optimal_df[FEED_COL_BID_AND_TARGET_DAMPING]/100),
        optimal_df[FEED_COL_TARGET_ROAS] * (1 + optimal_df[FEED_COL_BID_AND_TARGET_DAMPING]/100)
        ).round(0),
    np.nan),(100 * optimal_df[FEED_COL_TARGET]).round(0))



optimal_df[COL_EXPECTED] = np.where(optimal_df[FEED_COL_BIDDING_STRATEGY_OLD_TYPE].isin(['MaximizeConversions','TargetCPA']),
    round(optimal_df[FEED_COL_TARGET_CPA]*(100 + optimal_df[FEED_COL_BOOST_PERCENT])/100,2),
    round((1/100)*optimal_df[FEED_COL_TARGET_ROAS] * 100 / (100 + optimal_df[FEED_COL_BOOST_PERCENT]),2))
                                                                       
optimal_df[COL_ACCEPTABLE] = optimal_df[FEED_COL_TARGET].between(
    (1 - TARGET_TOLERANCE) * optimal_df[COL_EXPECTED],
    (1 + TARGET_TOLERANCE) * optimal_df[COL_EXPECTED]
)

optimal_df[BULK_COL_DIM_DAMPED] = np.where(optimal_df[FEED_COL_BIDDING_STRATEGY_OLD_TYPE].isin(['MaximizeConversions','TargetCPA']),optimal_df[COL_DAMPENED_TARGET_CPA].round(2),optimal_df[COL_DAMPENED_TARGET_ROAS].round(0))
optimal_df[BULK_COL_DIM_UNDAMPED] = np.where(optimal_df[FEED_COL_BIDDING_STRATEGY_OLD_TYPE].isin(['MaximizeConversions','TargetCPA']),optimal_df[FEED_COL_TARGET].round(2), (100 * optimal_df[FEED_COL_TARGET]).round(0))


results_cols = [FEED_COL_ACCOUNT, FEED_COL_CAMPAIGN, COL_DAMPENED_TARGET_CPA, COL_DAMPENED_TARGET_ROAS, COL_ACCEPTABLE, FEED_COL_BOOST_PERCENT, BULK_COL_DIM_DAMPED, BULK_COL_DIM_UNDAMPED]
results_df = optimal_df.loc[:, results_cols] \
                    .rename(columns = { \
                        COL_DAMPENED_TARGET_CPA: FEED_COL_TARGET_CPA, \
                        COL_DAMPENED_TARGET_ROAS: FEED_COL_TARGET_ROAS, \
                    }) \
                       .copy()


debugDf = optimal_df

# only include changed rows in bulk file
(outputDf, _) = select_changed(results_df, \
                                    original_df, \
                                    diff_cols = [ \
                                        FEED_COL_TARGET_CPA, \
                                        FEED_COL_TARGET_ROAS, \
                                    ], \
                                    select_cols = [ \
                                        FEED_COL_ACCOUNT, \
                                        FEED_COL_CAMPAIGN, \
                                        FEED_COL_TARGET_CPA, \
                                        FEED_COL_TARGET_ROAS, \
                                        COL_ACCEPTABLE, \
                                        FEED_COL_BOOST_PERCENT, \
                                        BULK_COL_DIM_DAMPED, \
                                        BULK_COL_DIM_UNDAMPED, \
                                    ], \
                                    merged_cols=[FEED_COL_ACCOUNT, FEED_COL_CAMPAIGN] \
                                    )

outputDf = outputDf[outputDf[COL_ACCEPTABLE]].drop(COL_ACCEPTABLE,axis=1)

outputDf = outputDf[outputDf[FEED_COL_BOOST_PERCENT]!=0].drop(FEED_COL_BOOST_PERCENT,axis=1)

# use Bulk column headers
outputDf = outputDf.rename(columns = { \
                    FEED_COL_ACCOUNT: BULK_COL_ACCOUNT, \
                    FEED_COL_CAMPAIGN: BULK_COL_CAMPAIGN, \
                    FEED_COL_TARGET_CPA: BULK_COL_PUBLISHER_TARGET_CPA, \
                    FEED_COL_TARGET_ROAS: BULK_COL_PUBLISHER_TARGET_ROAS, \
                })

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

04 Mar 2024

« Script 735: Pacing Campaign Bulk Sheet Script 739: Automate Campaigns DimensionTag Tactic »

MarinOne Scripts Creator's Corner