Script 613: AdGroup CPA Outlier

Purpose

Tag AdGroup if CPA performance is abnormally high within Campaign 30-lookback excluding recent 3 days.

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 a 30-day lookback period, excluding the most recent 3 days, to calculate the average CPA. If an AdGroup’s CPA is significantly higher than the average, it is flagged as an outlier.

Walking Through the Code

The script starts by defining column constants and the output column name.
It initializes the output column in the output dataframe as NaN.
The script then performs data preparation steps:
- It prints the minimum and maximum dates in the input dataframe.
- It defines the start and end dates for the 30-day lookback period, excluding the most recent 3 days.
- It reduces the input dataframe to only include rows within the defined date range.
- It selects the necessary columns for further analysis.
- It aggregates the selected columns by Account, Campaign, and Group, summing the numerical columns.
- It removes rows without cost or conversions.
- It indexes the dataframe by Account and Campaign.
- It calculates additional features: 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: Finds anomalies using a given function (e.g., sigma rule, IRQ) for a given set of features.
- is_anomaly_irq: Finds outliers/anomalies using IRQ (Interquartile Range) for a specific column.
The script defines a function find_peer_anomaly to find anomalies within a slice of the dataframe:
- It checks if the slice has enough rows for analysis.
- It resets the index of the slice.
- It calls get_feature_anomalies with the slice, is_anomaly_irq as the anomaly detection function, and the desired outliers (over and under).
- It calculates the median cost of the slice.
- It includes over/under outliers as desired.
- It ignores anomalies from low spend AdGroups (greater than the campaign median).
- It prints the anomalies and relevant columns if there are any outliers.
The script prints the shape of the input dataframe.
It initializes an empty dataframe df_anomalies to store the detected anomalies.
It iterates over unique campaign indices in the dataframe:
- It creates a slice of the dataframe for the current campaign.
- It calculates the median CPA for the campaign.
- It adds a column for the automation outlier tag and sets it as NaN.
- It calls find_peer_anomaly with the campaign slice, the CPA column, an IRQ threshold of 2, and the desired outliers (over only).
- If there are outliers, it creates a dataframe df_outliers with the outlier rows.
- It updates the automation outlier tag column in df_outliers with a formatted string indicating the high CPA.
- It prints df_outliers and adds it to df_anomalies.
The script prepares the output by selecting the relevant columns from df_anomalies and assigning it to outputDf.

Vitals

Script ID : 613
Client ID / Customer ID: 1306923845 / 60269271
Action Type: Bulk Upload (Preview)
Item Changed: AdGroup
Output Columns: Account, Campaign, Group, AUTOMATION - Outlier
Linked Datasource: M1 Report
Reference Datasource: None
Owner: emerryfield@marinsoftware.com (emerryfield@marinsoftware.com)
Created by emerryfield@marinsoftware.com on 2023-12-19 18:31
Last Updated by Michael Huang on 2024-01-11 22:06

> 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_DATE = 'Date'
RPT_COL_GROUP = 'Group'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
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_IMPR = 'Impr.'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_CONV = 'Conv.'
RPT_COL_REVENUE = 'Revenue $'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_AUTOMATION_OUTLIER = 'AUTOMATION - Outlier'

outputDf[BULK_COL_AUTOMATION_OUTLIER] = 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 metrics across dates for numerical columns only
agg_columns = {RPT_COL_PUB_COST: 'sum', RPT_COL_CONV: 'sum', RPT_COL_REVENUE: 'sum', RPT_COL_CLICKS: 'sum'}
df_group_perf = df_reduced.groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP]).agg(agg_columns)

# 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(axis=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.66) - data[col].quantile(0.33)
    upper_bound = data[col].quantile(0.66) + (thresh * IRQ)
    lower_bound = data[col].quantile(0.33) - (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(columns=[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, BULK_COL_AUTOMATION_OUTLIER])

# 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], 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_AUTOMATION_OUTLIER] = 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_AUTOMATION_OUTLIER]]

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

19 Dec 2023

« Script 611: SBA Campaigns Watcher Script 615: SBA Campaign Pacing »

MarinOne Scripts Creator's Corner