Script 523: Ad Group Outlier

Purpose

Tag AdGroup if CPA performance is abnormally high within Campaign

To Elaborate

The Python script aims to identify AdGroups within a Campaign that have a high Cost per Conversion (CPA) compared to the average CPA of the Campaign. It uses the Interquartile Range (IQR) method to detect outliers and tags the AdGroups as outliers if their CPA is significantly higher than the Campaign average.

Walking Through the Code

The script starts by defining column constants and client timezone, which are not relevant to the problem being solved.
The script then sets up the SBA (Structured Budget Allocation) by specifying the required columns and creating an empty column for the outlier tag.
The configurable parameters are defined, including the IQR threshold and the conversion lag days.
The script performs data preparation by filtering the input data based on the specified date range and reducing the columns to only the necessary ones.
The script calculates the performance metrics for each AdGroup within each Campaign, including the cost per conversion, return on ad spend (ROAS), conversion rate, and average cost per click (CPC).
The script defines two anomaly detection functions: get_feature_anomalies and is_anomaly_iqr.
The get_feature_anomalies function takes a dataset, a function to find anomalies, a list of features to check, and a threshold value. It iterates over the features and applies the specified function to find anomalies, storing the results in a summary dataframe and boolean series for outliers.
The is_anomaly_iqr function calculates the upper and lower bounds for a given feature using the IQR method and returns a boolean series mask of outliers.
The find_peer_anomaly function takes a slice of the dataset, a list of features, an IQR threshold, and a tuple specifying whether to include outliers above and/or below the threshold. It calculates the median cost for the slice and applies the get_feature_anomalies function to find outliers. It also filters out low spend AdGroups and prints the anomalies if any are found.
The script then prints the shape and head of the df_group_perf dataframe, which contains the aggregated performance metrics for each AdGroup within each Campaign.
The script initializes an empty dataframe df_anomalies to store the detected outliers.
The script iterates over each unique Campaign in the df_group_perf dataframe and applies the find_peer_anomaly function to detect outliers in the CPA metric.
If outliers are found, the script creates a copy of the outliers dataframe, adds a column for the outlier tag, and formats the outlier description using the rowFunc function.
The script prints the detected outliers and concatenates them with the df_anomalies dataframe.
Finally, the script prepares the output dataframe by either creating an empty dataframe if no anomalies are found or selecting the relevant columns from the df_anomalies dataframe.
The script prints the size and head of the output dataframe.

Vitals

Script ID : 523
Client ID / Customer ID: 1306923689 / 60269255
Action Type: Bulk Upload (Preview)
Item Changed: AdGroup
Output Columns: Account, Campaign, Group, AUTOMATION - Outlier
Linked Datasource: M1 Report
Reference Datasource: None
Owner: ascott@marinsoftware.com (ascott@marinsoftware.com)
Created by ascott@marinsoftware.com on 2023-11-13 20:36
Last Updated by Michael Huang on 2024-03-13 23:05

> See it in Action

Python Code

#
# Tag AdGroup if CPA performance is abnormally high within Campaign
#
#
# Author: Michael S. Huang
# Date: 2023-02-22

RPT_COL_GROUP = 'Group'
RPT_COL_DATE = 'Date'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_CAMPAIGN_ID = 'Campaign ID'
RPT_COL_GROUP_ID = 'Group ID'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_COST_PER_CONV = 'Cost/Conv. $'
RPT_COL_ROAS = 'ROAS'
RPT_COL_CONV_RATE = 'Conv. Rate %'
RPT_COL_AVG_CPC = 'Avg. CPC $'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_CONV = 'Conv.'
RPT_COL_REVENUE = 'Revenue $'
RPT_COL_IMPR = 'Impr.'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_AUTOMATION_OUTLIER = 'AUTOMATION - Outlier'

outputDf[BULK_COL_AUTOMATION_OUTLIER] = numpy.nan

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

# IQR 1.5 = looks for rare events having less than 3% of occuring; lower includes more events
ANOMALY_IQR_THRESHOLD = 1.5
CONVERSION_LAG_DAYS = 1

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



## Data Prep

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

# 30-day lookback without most recent CONVERSION_LAG_DAYS days due to conversion lag
start_date = pd.to_datetime(datetime.date.today() - datetime.timedelta(days=33))
end_date = pd.to_datetime(datetime.date.today() - datetime.timedelta(days=CONVERSION_LAG_DAYS))

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

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

# reduce to needed columns
df_reduced = df_reduced[[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, RPT_COL_DATE, RPT_COL_PUB_COST, RPT_COL_CONV, RPT_COL_REVENUE, RPT_COL_CLICKS]].copy()

# specify the columns to sum
cols_to_sum = [RPT_COL_PUB_COST, RPT_COL_CONV, RPT_COL_REVENUE, RPT_COL_CLICKS]

# apply sum operation only to the specified columns
df_group_perf = df_reduced.groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP])[cols_to_sum].sum()


# remove rows without cost or conversions
df_group_perf = df_group_perf[(df_group_perf[RPT_COL_CONV] > 0) & (df_group_perf[RPT_COL_PUB_COST] > 0)]

# index by campaign
df_group_perf = df_group_perf.reset_index().set_index([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN]).sort_index()

# calculate features
df_group_perf[RPT_COL_COST_PER_CONV] = (df_group_perf[RPT_COL_PUB_COST] / df_group_perf[RPT_COL_CONV])
df_group_perf[RPT_COL_ROAS] = df_group_perf[RPT_COL_REVENUE] / df_group_perf[RPT_COL_PUB_COST]
df_group_perf[RPT_COL_CONV_RATE] = df_group_perf[RPT_COL_CONV] / df_group_perf[RPT_COL_CLICKS]
df_group_perf[RPT_COL_AVG_CPC] = (df_group_perf[RPT_COL_PUB_COST] / df_group_perf[RPT_COL_CLICKS])

## Define Anomaly Fuctions

# Finds anomalies using a certain function (e.g. sigma rule, iqr etc.)
# data: DataFrame
#     Dataset with features
# func: func
#     Function to use to find anomalies
# features: list
#     Feature list
# thresh: int
#     Threshold value (e.g. 2/3 * sigma, 2/3 * iqr)
# Returns: tuple
def get_feature_anomalies(data, func, features=None, thresh=1.5):

    if features:
        features_to_check = features
    else:
        features_to_check = data.columns 
        
    outliers_over = pd.Series(data=[False] * data.shape[0], index=data[features_to_check].index, name='is_outlier')
    outliers_under = pd.Series(data=[False] * data.shape[0], index=data[features_to_check].index, name='is_outlier')

    anomalies_summary = {}
    for feature in features_to_check:
        anomalies_mask_over, anomalies_mask_under, upper_bound, lower_bound = func(data, feature, thresh=thresh)
        anomalies_mask_combined = pd.concat([anomalies_mask_over, anomalies_mask_under], axis=1).any(1)
        anomalies_summary[feature] = [upper_bound, lower_bound, sum(anomalies_mask_combined), 100*sum(anomalies_mask_combined)/len(anomalies_mask_combined)]
        outliers_over[anomalies_mask_over[anomalies_mask_over].index] = True
        outliers_under[anomalies_mask_under[anomalies_mask_under].index] = True
        
#         print("anomalies_mask_combined: ", anomalies_mask_combined)
#         print("Outliers: ", outliers)
        
    anomalies_summary = pd.DataFrame(anomalies_summary).T
    anomalies_summary.columns=['upper_bound', 'lower_bound', 'anomalies_count', 'anomalies_percentage']
    
    anomalies_ration = round(anomalies_summary['anomalies_percentage'].sum(), 2)
#     print(f'Total Outliers Ration: {anomalies_ration} %')
    
    return anomalies_summary, outliers_over, outliers_under

# Finds outliers/anomalies using iqr 
# data: DataFrame
# col: str
# thresh: int
#     Number of IQR to apply 
# Returns: Series 
#     Boolean Series Mask of outliers 
def is_anomaly_iqr(data, col, thresh):

    IQR = data[col].quantile(0.75) - data[col].quantile(0.25)
    upper_bound = data[col].quantile(0.75) + (thresh * IQR)
    lower_bound = data[col].quantile(0.25) - (thresh * IQR)
#     print("IQR calc: ", col, IQR, upper_bound, lower_bound)
#     anomalies_mask = pd.concat([data[col] > upper_bound, data[col] < lower_bound], axis=1).any(1)
    anomalies_mask_over = data[col] > upper_bound
    anomalies_mask_under = data[col] < lower_bound
#     print("Anomalies mask: ", (anomalies_mask_over, anomalies_mask_under))
    
    return anomalies_mask_over, anomalies_mask_under, upper_bound, lower_bound

def find_peer_anomaly(df_slice, features, iqr_threshold=1.5, outliers_desired=(True, True)):
    
    (want_outliers_over, want_outliers_under) = outliers_desired
   
    if (df_slice.shape[0] < 3):
        return
    
    idx = df_slice.index.unique()
    
    df_slice.reset_index(inplace=True)
    
    anomalies_summary_iqr, outlier_over_iqr, outlier_under_iqr = get_feature_anomalies( \
                df_slice, \
                func=is_anomaly_iqr, \
                features=features, \
                thresh=iqr_threshold)
    
    median_cost = df_slice[RPT_COL_PUB_COST].median()
    
#     print(f"over: {outlier_over_iqr}")
#     print("under: {outlier_under_iqr}")
    
    # include over/under outliers as desired
    is_outlier_iqr = np.logical_or(
                        np.logical_and(want_outliers_over, outlier_over_iqr),
                        np.logical_and(want_outliers_under, outlier_under_iqr)
    )
    
#     print("is_outlier\\n", is_outlier_iqr)
    
    # ignore anomaly from low spend adgroups (greater than campaign median)
    is_outlier_iqr = np.logical_and(is_outlier_iqr, df_slice[RPT_COL_PUB_COST] > median_cost)
    
    if sum(is_outlier_iqr) > 0:
        print(">>> ANOMALY", idx)
        print(anomalies_summary_iqr)
        cols = [RPT_COL_GROUP, RPT_COL_PUB_COST, RPT_COL_CONV, RPT_COL_REVENUE] + features
        print(df_slice.loc[is_outlier_iqr, cols])
        
    return is_outlier_iqr

## Find CPA Anomalies

print("df_group_perf shape:", df_group_perf.shape)
print("df_group_perf", tableize(df_group_perf.head()))
df_anomalies = pd.DataFrame()

# annotate via Marin Dimensions
def rowFunc(row):
    return 'CPA ${:,.2f} is much higher than campaign avg ${:,.2f}'.format(
        row[RPT_COL_COST_PER_CONV], \
        row[RPT_COL_COST_PER_CONV + '_median']
    )

for campaign_idx in df_group_perf.index.unique():
    df_campaign = df_group_perf.loc[[campaign_idx]].copy()
    df_campaign[RPT_COL_COST_PER_CONV + '_median'] = df_campaign[RPT_COL_COST_PER_CONV].mean()

    df_campaign[BULK_COL_AUTOMATION_OUTLIER] = np.nan
    outliers = find_peer_anomaly(df_campaign, [RPT_COL_COST_PER_CONV], iqr_threshold=ANOMALY_IQR_THRESHOLD, outliers_desired=(True,False))

    if outliers is not None and sum(outliers) > 0:
        df_outliers = df_campaign.loc[outliers].copy()
        df_outliers[BULK_COL_AUTOMATION_OUTLIER] = df_outliers.apply(rowFunc, axis=1)
        print(df_outliers)
        df_anomalies = pd.concat([df_anomalies, df_outliers], axis=0)

## Prepare Output

if df_anomalies.empty:
    outputDf = pd.DataFrame(columns=[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, BULK_COL_AUTOMATION_OUTLIER])
    print("No anomalies found")
else:
    print("anomaly examples", tableize(df_anomalies.head()))
    outputDf = df_anomalies[[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, BULK_COL_AUTOMATION_OUTLIER]]
    print("output size", outputDf.shape)
    print("output examples", tableize(outputDf.head()))

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

13 Nov 2023

« Script 521: Budget Boosts for Bing Campaigns Script 525: Campaign Set Rate »

MarinOne Scripts Creator's Corner