Script 9: AdGroup High CPA Outlier

Purpose:

The Python script identifies and tags AdGroups with a Cost Per Acquisition (CPA) significantly higher than their peers within the same campaign over a 30-day period.

To Elaborate

The script is designed to analyze advertising data to identify AdGroups within campaigns that have a Cost Per Acquisition (CPA) significantly higher than their peers. It focuses on a 30-day lookback period, excluding the most recent three days to account for conversion lag, and only considers AdGroups with recorded spending. The goal is to detect anomalies in CPA performance, which could indicate inefficiencies or issues in the advertising strategy. By tagging these outliers, the script helps marketers and analysts quickly identify areas that may require attention or adjustment to optimize campaign performance.

Walking Through the Code

Data Preparation
- The script begins by filtering the input data to include only the last 30 days, excluding the most recent three days.
- It reduces the dataset to necessary columns and aggregates metrics like cost and conversions across the selected date range.
- Rows without cost or conversions are removed to ensure meaningful analysis.
Feature Calculation
- The script calculates key performance metrics such as Cost Per Conversion, Return on Ad Spend (ROAS), Conversion Rate, and Average Cost Per Click (CPC) for each AdGroup.
Anomaly Detection Functions
- Two functions, get_feature_anomalies and is_anomaly_irq, are defined to identify outliers using the Interquartile Range (IRQ) method.
- These functions determine upper and lower bounds for anomalies and return boolean masks indicating which AdGroups are outliers.
Peer Anomaly Identification
- The find_peer_anomaly function applies the anomaly detection to each campaign, identifying AdGroups with CPA significantly above the median.
- It considers only those AdGroups with spending above the campaign median to avoid false positives from low-spend groups.
Anomaly Tagging and Output Preparation
- For each identified outlier, the script tags it with a message indicating its CPA is much higher than the campaign average.
- The results are compiled into a DataFrame and prepared for output, highlighting the AdGroups that require attention.

Vitals

Script ID : 9
Client ID / Customer ID: 261324439 / 60268239
Action Type: Bulk Upload (Preview)
Item Changed: AdGroup
Output Columns: Account, Campaign, Group, Changes
Linked Datasource: M1 Report
Reference Datasource: None
Owner: Michael Huang (mhuang@marinsoftware.com)
Created by Michael Huang on 2023-02-02 12:36
Last Updated by Michael Huang on 2024-06-25 06:37

> 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_CHANGES = 'Changes'

outputDf[BULK_COL_CHANGES] = numpy.nan

## Data Prep

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

# 30-day lookback without most recent 3 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=3))

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()

# sum metics across dates
df_group_perf = df_reduced.groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP]).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, IRQ 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 * IRQ)
# Returns: tuple
def get_feature_anomalies(data, func, features=None, thresh=3):

    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 IRQ 
# data: DataFrame
# col: str
# thresh: int
#     Number of IRQ to apply 
# Returns: Series 
#     Boolean Series Mask of outliers 
def is_anomaly_irq(data, col, thresh):

    IRQ = data[col].quantile(0.75) - data[col].quantile(0.25)
    upper_bound = data[col].quantile(0.75) + (thresh * IRQ)
    lower_bound = data[col].quantile(0.25) - (thresh * IRQ)
#     print("IRQ calc: ", col, IRQ, 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, irq_threshold=1.8, 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_irq, outlier_over_irq, outlier_under_irq = get_feature_anomalies( \
                df_slice, \
                func=is_anomaly_irq, \
                features=features, \
                thresh=irq_threshold)
    
    median_cost = df_slice[RPT_COL_PUB_COST].median()
    
#     print(f"over: {outlier_over_irq}")
#     print("under: {outlier_under_irq}")
    
    # include over/under outliers as desired
    is_outlier_irq = np.logical_or(
                        np.logical_and(want_outliers_over, outlier_over_irq),
                        np.logical_and(want_outliers_under, outlier_under_irq)
    )
    
#     print("is_outlier\\n", is_outlier_irq)
    
    # ignore anomaly from low spend adgroups (greater than campaign median)
    is_outlier_irq = np.logical_and(is_outlier_irq, df_slice[RPT_COL_PUB_COST] > median_cost)
    
    if sum(is_outlier_irq) > 0:
        print(">>> ANOMALY", idx)
        print(anomalies_summary_irq)
        cols = [RPT_COL_GROUP, RPT_COL_PUB_COST, RPT_COL_CONV, RPT_COL_REVENUE] + features
        print(df_slice.loc[is_outlier_irq, cols])
        
    return is_outlier_irq

## Find CPA Anomalies

print("input shape:", df_group_perf.shape)
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_CHANGES] = np.nan
    outliers = find_peer_anomaly(df_campaign, [RPT_COL_COST_PER_CONV], irq_threshold=2, outliers_desired=(True,False))

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

## Prepare Output
print(tableize(df_anomalies))
outputDf = df_anomalies[[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, BULK_COL_CHANGES]]

Post generated on 2025-03-11 01:25:51 GMT

02 Feb 2023

Script 13: Pause Campaigns with no active groups »

MarinOne Scripts Creator's Corner