Script 43: Tag AdGroup Dimensions per ROAS CPA Performance

Purpose

Tag AdGroup Dimensions per ROAS/CPA Performance

To Elaborate

The Python script tags or clears dimensions based on historical and intraday performance of Google AdGroups. It applies specific rules to determine the appropriate tag for each AdGroup based on its performance metrics such as ROAS (Return on Ad Spend), revenue, and cost. The script categorizes AdGroups into different dimensions such as “Push Now,” “Slow Down,” “1X CPA,” “2X CPA,” “3X CPA,” and “Concern - Examine” based on their performance. The script also considers a 6-day lookback period and includes intraday updates every 2 hours.

Walking Through the Code

The script defines column constants for various metrics such as group, date, publisher, account, campaign, ROAS, revenue, etc.
It sets the client timezone to be used in the script.
The script performs data preparation steps such as removing non-breaking spaces in revenue values and converting them to numeric format.
It prints the minimum and maximum dates in the input data and the shape of the data.
The script sets the start and end dates for the 6-day lookback period.
It reduces the input data to only include rows within the specified date range.
If there is data available for the current day, the script applies intraday performance rules.
It creates temporary columns to hold new tag values and sets their initial values based on the current values.
The script applies various rules to determine the appropriate tag for each AdGroup based on its ROAS and other metrics.
It fills NaN values with empty strings and calculates the number of AdGroups that have changed tags.
If there is historical data available, the script applies historical performance rules.
It aggregates the data over the remaining date range and calculates the ROAS for each AdGroup.
The script sets a temporary column to hold the “Concern - Examine” tag value.
It applies a rule to set the “Concern - Examine” tag if the cost is greater than 100 and the ROAS is less than 5 over the last 6 days plus today.
The script applies a rule to unset the “Concern - Examine” tag if the cost is greater than 100 and the ROAS is greater than 6 over the last 6 days plus today.
It checks if there was any spend yesterday and clears the “Concern - Examine” tag if there was no spend.
The script fills NaN values with empty strings and calculates the number of AdGroups that have changed tags.
It combines the output from the intraday and historical rules into a single output DataFrame.
The script creates separate DataFrames for intraday and historical output.
It joins the two DataFrames based on account, campaign, and group and resets the index.
The script prints the final output DataFrame.

Vitals

Script ID : 43
Client ID / Customer ID: 1306925293 / 60269377
Action Type: Bulk Upload
Item Changed: AdGroup
Output Columns: Account, Campaign, Group, Daily (Losing Today), Daily (Overspent), Daily Winner, Over Time (Concern - Examine)
Linked Datasource: M1 Report
Reference Datasource: None
Owner: Megan Madden (mmadden@marinsoftware.com)
Created by Megan Madden on 2023-03-30 20:12
Last Updated by Michael Huang on 2023-12-06 04:01

> See it in Action

Python Code

#
# Tag/Clear Dimensions based on Historical and Intraday Perforamnce
# For details please see: https://docs.google.com/document/d/1Xu9eHdZ1ks54WGjGr7ChpPVHgtEdeVrf8JL8FhEP2V4/edit
#


RPT_COL_GROUP = 'Group'
RPT_COL_DATE = 'Date'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ROAS = 'ROAS'
RPT_COL_REVENUE = 'Revenue $'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_CONV = 'Conv.'
RPT_COL_DAILY_WINNER = 'Daily Winner'
RPT_COL_DAILY_OVERSPENT = 'Daily (Overspent)'
RPT_COL_DAILY_LOSINGTODAY = 'Daily (Losing Today)'
RPT_COL_OVER_TIMECONCERNEXAMINE = 'Over Time (Concern - Examine)'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_DAILY_OVERSPENT = 'Daily (Overspent)'
BULK_COL_DAILY_LOSINGTODAY = 'Daily (Losing Today)'
BULK_COL_DAILY_WINNER = 'Daily Winner'
BULK_COL_OVER_TIMECONCERNEXAMINE = 'Over Time (Concern - Examine)'

CLIENT_TIMEZONE = datetime.timezone(datetime.timedelta(hours=-5))

# Dim Value Constants
DIM_VAL_PUSH_NOW = "Push Now"
DIM_VAL_SLOW_DOWN = "Slow Down"
DIM_VAL_1XCPA = "1X CPA"
DIM_VAL_2XCPA = "2X CPA"
DIM_VAL_3XCPA = "3X CPA"
DIM_VAL_CONCERN = "Concern - Examine"

## Workaround to '\xa0' non-breaking space in Revenue value "-\xa05.72"
# https://jira.marinsoftware.com/browse/FEND-17601
inputDf[RPT_COL_REVENUE] = inputDf[RPT_COL_REVENUE].astype(str)
inputDf[RPT_COL_REVENUE] = inputDf[RPT_COL_REVENUE].str.split().str.join('')
inputDf[RPT_COL_REVENUE] = pd.to_numeric(inputDf[RPT_COL_REVENUE])

## Data Prep

print(inputDf[RPT_COL_DATE].min(), inputDf[RPT_COL_DATE].max(), inputDf.shape)

# 6-day lookback including today
today = datetime.datetime.now(CLIENT_TIMEZONE).date()
start_date = pd.to_datetime(today - datetime.timedelta(days=6))
end_date = pd.to_datetime(today)

df_reduced = inputDf[ (inputDf[RPT_COL_DATE] >= start_date) & (inputDf[RPT_COL_DATE] <= end_date) ]

if (df_reduced.shape[0] > 0):
    print("reduced date range and shape\\n", min(df_reduced[RPT_COL_DATE]), max(df_reduced[RPT_COL_DATE]), df_reduced.shape)
else:
    print("no more input to process")



## Intraday Performance Rules

df_today = inputDf[ inputDf[RPT_COL_DATE] == end_date ].copy()

if not df_today.empty:

    print("df_today", df_today.to_string())

    ## set up temp columns to hold new tag values
    # set initial value as current value so won't be cleared here
    winner_tmp = RPT_COL_DAILY_WINNER + "_"
    df_today[winner_tmp] = df_today[RPT_COL_DAILY_WINNER]
    overspend_tmp = RPT_COL_DAILY_OVERSPENT + "_"
    df_today[overspend_tmp] = df_today[RPT_COL_DAILY_OVERSPENT]
    losing_tmp = RPT_COL_DAILY_LOSINGTODAY + "_"
    df_today[losing_tmp] = df_today[RPT_COL_DAILY_LOSINGTODAY]

    # Rule #1: Set "Daily Winner" = "Push Now" if TODAY's ROAS (high watermark) exceeds 8; skip if already set
    df_today.loc[ (df_today[RPT_COL_ROAS] > 8) & \
                  (df_today[winner_tmp] != DIM_VAL_PUSH_NOW), \
                  winner_tmp \
                ] = DIM_VAL_PUSH_NOW

    # Rule #3: Set "Daily Overspent" = "Slow Down" if TODAY's ROAS dips below 6 while being tagged as PUSH_NOW; skip if already set
    df_today.loc[ (df_today[RPT_COL_ROAS] < 6) & \
                  (df_today[RPT_COL_DAILY_WINNER] == DIM_VAL_PUSH_NOW) & \
                  (df_today[overspend_tmp] != DIM_VAL_SLOW_DOWN), \
                  overspend_tmp \
                ] = DIM_VAL_SLOW_DOWN

    # Rule #4,#5,#6: Set "Daily: Losing Today" to "1/2/3X CPA" depending on TODAY's Pub Cost
    ## These need to be run in defined order!
    df_today.loc[ (df_today[RPT_COL_ROAS] < 5) & \
                  (df_today[RPT_COL_PUB_COST] > 60), \
                  losing_tmp \
                ] = DIM_VAL_1XCPA

    df_today.loc[ (df_today[RPT_COL_ROAS] < 5) & \
                  (df_today[RPT_COL_PUB_COST] > 120), \
                  losing_tmp \
                ] = DIM_VAL_2XCPA

    df_today.loc[ (df_today[RPT_COL_ROAS] < 5) & \
                  (df_today[RPT_COL_PUB_COST] > 180), \
                  losing_tmp \
                ] = DIM_VAL_3XCPA


    # nan are hard to compare, change to empty string instead
    df_today.fillna('', inplace=True)
    df_today['changed'] = (df_today[RPT_COL_DAILY_WINNER] != df_today[winner_tmp]) | \
              (df_today[RPT_COL_DAILY_OVERSPENT] != df_today[overspend_tmp]) | \
              (df_today[RPT_COL_DAILY_LOSINGTODAY] != df_today[losing_tmp])

    print("intraday changed: ", sum(df_today['changed']))


## Historical Performance Rules

if not df_reduced.empty:

    # aggregate over remaining date range, summing cost and rev
    agg_func_selection = {
        RPT_COL_PUB_COST: ['sum'],
        RPT_COL_REVENUE: ['sum'],
        RPT_COL_OVER_TIMECONCERNEXAMINE: ['last']
    }
    df_historical = df_reduced.groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP]) \
                       .agg(agg_func_selection) \
                       .droplevel(1, axis=1)

    # calculate ROAS
    df_historical[RPT_COL_ROAS] = df_historical[RPT_COL_REVENUE] / df_historical[RPT_COL_PUB_COST]

    # set temp column
    tmp_concern = RPT_COL_OVER_TIMECONCERNEXAMINE + '_'
    df_historical[tmp_concern] = df_historical[RPT_COL_OVER_TIMECONCERNEXAMINE]


    ## Rule #7 Set "Over Time Concern" to "Concern - Examine" if Cost > 100 but ROAS < 5, over last 6 days plus today

    df_historical.loc[ (df_historical[RPT_COL_PUB_COST] > 100) & \
                       (df_historical[RPT_COL_ROAS] < 5) & \
                       (df_historical[tmp_concern] != DIM_VAL_CONCERN), \
                       tmp_concern \
                     ] = DIM_VAL_CONCERN


    ## Rule #8 Unset "Over Time Concern" and remove "Concern - Examine" if Cost > 100 but ROAS > 6, over last 6 days plus today

    df_historical.loc[ (df_historical[RPT_COL_PUB_COST] > 100) & \
                       (df_historical[RPT_COL_ROAS] > 6) & \
                       (df_historical[tmp_concern] == DIM_VAL_CONCERN), \
                       tmp_concern \
                     ] = np.nan


    # Rule #9, clear "Over Time Concern" if no spend YESTERDAY
    
    yesterday = pd.to_datetime(today - datetime.timedelta(days=1))
    cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, RPT_COL_PUB_COST]
    df_yesterday = df_reduced.loc[ df_reduced[RPT_COL_DATE] == yesterday, cols ] \
                             .copy() \
                             .set_index([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP])
    
    
    if not df_yesterday.empty:

        df_clear = df_yesterday.loc[ df_yesterday[RPT_COL_PUB_COST] < 0.5 ]
        df_historical.loc[ df_clear.index, tmp_concern ] = np.nan

    ## set change flag    
    # nan are hard to compare, change to empty string instead
    df_historical.fillna('', inplace=True)
    df_historical['changed'] = df_historical[tmp_concern] != df_historical[RPT_COL_OVER_TIMECONCERNEXAMINE]

    print("historical changed: ", sum(df_historical['changed']))   



## Combine output from Intraday and Historical Rules

df_intraday_out = pd.DataFrame()

# get intraday output
if not df_reduced.empty and not df_today.empty and df_today['changed'].any():
    
    # only include adgroups with changed tags in bulk file
    df_intraday_out = df_today.loc[ df_today['changed'], [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, winner_tmp, overspend_tmp, losing_tmp]] \
                           .copy() \
                           .rename(columns={ \
                                winner_tmp: BULK_COL_DAILY_WINNER, \
                                overspend_tmp: BULK_COL_DAILY_OVERSPENT, \
                                losing_tmp: BULK_COL_DAILY_LOSINGTODAY \
                            }) \
                           .set_index([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP])

df_historical_out = pd.DataFrame()

# get historical output
if not df_reduced.empty and not df_historical.empty and df_historical['changed'].any():

    # only include adgroups with changed tags in bulk file
    df_historical_out = df_historical.loc[ df_historical['changed']] \
                           .reset_index() \
                           .loc[:, [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, tmp_concern]] \
                           .copy() \
                           .rename(columns={ \
                                tmp_concern: BULK_COL_OVER_TIMECONCERNEXAMINE \
                            }) \
                           .set_index([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP])
    

outputDf = outputDf.iloc[0:0]

if (not df_intraday_out.empty) and (not df_historical_out.empty):

    outputDf = df_intraday_out.join(df_historical_out, how='outer', on=[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP]) \
                              .reset_index()

elif not df_intraday_out.empty:
    
    outputDf = df_intraday_out.copy().reset_index()
    outputDf[BULK_COL_OVER_TIMECONCERNEXAMINE] = np.nan
    
elif not df_historical_out.empty:
    
    outputDf = df_historical_out.copy().reset_index()
    outputDf[BULK_COL_DAILY_WINNER] = np.nan
    outputDf[BULK_COL_DAILY_OVERSPENT] = np.nan
    outputDf[BULK_COL_DAILY_LOSINGTODAY] = np.nan
    
print("outputDf", outputDf.to_string())
    

Post generated on 2024-05-15 07:44:05 GMT

30 Mar 2023

« Script 41: Monthly Budget Cap GSheets Script 45: Daily Clear Labels »

MarinOne Scripts Creator's Corner