Script 1461: Campaign Benchmarking

Purpose

The Python script sets benchmarks for individual campaigns and all campaigns mapped to a strategy to evaluate their performance.

To Elaborate

The script is designed to analyze and set performance benchmarks for marketing campaigns and strategies. It processes data related to campaigns, such as clicks, impressions, conversions, and costs, to calculate key performance indicators (KPIs) like Cost Per Click (CPC), Click-Through Rate (CTR), and Cost Per Conversion (CPConv). The script evaluates these KPIs over different time periods, specifically the recent 14 days and the prior 90 days, to establish benchmarks and assess performance trends. It assigns performance scores and ranks to campaigns and strategies based on their recent performance compared to historical benchmarks. This analysis helps in identifying underperforming campaigns and strategies, allowing for data-driven decision-making to optimize marketing efforts.

Walking Through the Code

Data Preparation
- The script begins by loading the primary data source into a DataFrame named inputDf.
- It defines several constants representing column names for easy reference throughout the script.
Lookback Windows and Date Calculations
- The script calculates the current date and defines lookback windows for the recent 14 days and the 90 days prior to that.
- These windows are used to segment the data for performance analysis.
Data Analysis Functions
- Several functions are defined to calculate KPIs like CTR, CPC, CPConv, and conversions over specified date ranges.
- These functions are used to aggregate data and compute benchmarks for campaigns and strategies.
Campaign and Strategy Benchmarking
- The script groups data by campaign and strategy to calculate benchmarks and recent performance metrics.
- It applies custom aggregation functions to compute KPIs for each campaign and strategy.
Performance Scoring and Ranking
- Performance scores are assigned based on the deviation of recent performance from historical benchmarks.
- Campaigns and strategies are ranked using a scoring system, where lower scores indicate better performance.
Output Preparation
- The script merges campaign and strategy data, formats the output, and prepares it for further analysis or reporting.
- It ensures that the output DataFrame contains all necessary columns and formatted data for easy interpretation.

Vitals

Script ID : 1461
Client ID / Customer ID: 1306927457 / 60270313
Action Type: Bulk Upload
Item Changed: Campaign
Output Columns: Account, Campaign, Campaign - Conv - Benchmark, Campaign - Conv - Last 14 Days, Campaign - Conv - Performance, Campaign - Cost/Conv - Benchmark, Campaign - Cost/Conv - Last 14 Days, Campaign - Cost/Conv - Performance, Campaign - CPC - Benchmark, Campaign - CPC - Last 14 Days, Campaign - CPC - Performance, Campaign - CTR - Benchmark, Campaign - CTR - Last 14 Days, Campaign - CTR - Performance, Campaign - PPC Rank, Campaign - PPC Score, Strategy - Conv - Benchmark, Strategy - Conv - Last 14 Days, Strategy - Conv - Performance, Strategy - Cost/Conv - Benchmark, Strategy - Cost/Conv - Last 14 Days, Strategy - Cost/Conv - Performance, Strategy - CPC - Benchmark, Strategy - CPC - Last 14 Days, Strategy - CPC - Performance, Strategy - CTR - Benchmark, Strategy - CTR - Last 14 Days, Strategy - CTR - Performance, Strategy - PPC Rank, Strategy - PPC Score
Linked Datasource: M1 Report
Reference Datasource: None
Owner: Mingxia Wu (mwu@marinsoftware.com)
Created by Mingxia Wu on 2024-10-29 10:18
Last Updated by Mingxia Wu on 2024-11-18 06:54

> See it in Action

Python Code

##
## name: Campaign Benchmarking
## description:
##  Set benchmarks for the individual campaigns and for all of the campaigns mapped to a strategy.
## 
## author: Mingxia Wu
## created: 2024-10-30
## 

today = datetime.datetime.now(CLIENT_TIMEZONE).date()


# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DATE = 'Date'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_ID = 'Campaign ID'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_SEGMENT = 'Segment'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_IMPR = 'Impr.'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_CONV = 'Conv.'

# output columns and initial values
# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_CAMPAIGN__CONV__BENCHMARK = 'Campaign - Conv - Benchmark'
BULK_COL_CAMPAIGN__CONV__LAST_14_DAYS = 'Campaign - Conv - Last 14 Days'
BULK_COL_CAMPAIGN__CONV__PERFORMANCE = 'Campaign - Conv - Performance'
BULK_COL_CAMPAIGN__COST_PER_CONV__BENCHMARK = 'Campaign - Cost/Conv - Benchmark'
BULK_COL_CAMPAIGN__COST_PER_CONV__LAST_14_DAYS = 'Campaign - Cost/Conv - Last 14 Days'
BULK_COL_CAMPAIGN__COST_PER_CONV__PERFORMANCE = 'Campaign - Cost/Conv - Performance'
BULK_COL_CAMPAIGN__CPC__BENCHMARK = 'Campaign - CPC - Benchmark'
BULK_COL_CAMPAIGN__CPC__LAST_14_DAYS = 'Campaign - CPC - Last 14 Days'
BULK_COL_CAMPAIGN__CPC__PERFORMANCE = 'Campaign - CPC - Performance'
BULK_COL_CAMPAIGN__CTR__BENCHMARK = 'Campaign - CTR - Benchmark'
BULK_COL_CAMPAIGN__CTR__LAST_14_DAYS = 'Campaign - CTR - Last 14 Days'
BULK_COL_CAMPAIGN__CTR__PERFORMANCE = 'Campaign - CTR - Performance'
BULK_COL_CAMPAIGN__PPC_RANK = 'Campaign - PPC Rank'
BULK_COL_CAMPAIGN__PPC_SCORE = 'Campaign - PPC Score'
BULK_COL_STRATEGY__CONV__BENCHMARK = 'Strategy - Conv - Benchmark'
BULK_COL_STRATEGY__CONV__LAST_14_DAYS = 'Strategy - Conv - Last 14 Days'
BULK_COL_STRATEGY__CONV__PERFORMANCE = 'Strategy - Conv - Performance'
BULK_COL_STRATEGY__COST_PER_CONV__BENCHMARK = 'Strategy - Cost/Conv - Benchmark'
BULK_COL_STRATEGY__COST_PER_CONV__LAST_14_DAYS = 'Strategy - Cost/Conv - Last 14 Days'
BULK_COL_STRATEGY__COST_PER_CONV__PERFORMANCE = 'Strategy - Cost/Conv - Performance'
BULK_COL_STRATEGY__CPC__BENCHMARK = 'Strategy - CPC - Benchmark'
BULK_COL_STRATEGY__CPC__LAST_14_DAYS = 'Strategy - CPC - Last 14 Days'
BULK_COL_STRATEGY__CPC__PERFORMANCE = 'Strategy - CPC - Performance'
BULK_COL_STRATEGY__CTR__BENCHMARK = 'Strategy - CTR - Benchmark'
BULK_COL_STRATEGY__CTR__LAST_14_DAYS = 'Strategy - CTR - Last 14 Days'
BULK_COL_STRATEGY__CTR__PERFORMANCE = 'Strategy - CTR - Performance'
BULK_COL_STRATEGY__PPC_RANK = 'Strategy - PPC Rank'
BULK_COL_STRATEGY__PPC_SCORE = 'Strategy - PPC Score'

outputDf[BULK_COL_CAMPAIGN__CONV__BENCHMARK] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__CONV__LAST_14_DAYS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__CONV__PERFORMANCE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__COST_PER_CONV__BENCHMARK] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__COST_PER_CONV__LAST_14_DAYS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__COST_PER_CONV__PERFORMANCE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__CPC__BENCHMARK] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__CPC__LAST_14_DAYS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__CPC__PERFORMANCE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__CTR__BENCHMARK] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__CTR__LAST_14_DAYS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__CTR__PERFORMANCE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__PPC_RANK] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN__PPC_SCORE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__CONV__BENCHMARK] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__CONV__LAST_14_DAYS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__CONV__PERFORMANCE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__COST_PER_CONV__BENCHMARK] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__COST_PER_CONV__LAST_14_DAYS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__COST_PER_CONV__PERFORMANCE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__CPC__BENCHMARK] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__CPC__LAST_14_DAYS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__CPC__PERFORMANCE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__CTR__BENCHMARK] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__CTR__LAST_14_DAYS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__CTR__PERFORMANCE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__PPC_RANK] = "<<YOUR VALUE>>"
outputDf[BULK_COL_STRATEGY__PPC_SCORE] = "<<YOUR VALUE>>"

# user code start here
# use Gross Lead for CPL calc
# COL_CONV = RPT_COL_GROSS_LEAD_CONV
# # recent leads for calculating expected interviews
# COL_7_DAY_LEADS = "7 Day Leads"
# # Prorated ratio
# COL_PRORATED_RATIO = "Prorated Ratio"

## user code starts here

print("inputDf.shape", inputDf.shape)
print("inputDf.dtypes\n", inputDf.dtypes)
print("inputDf sample\n", inputDf.head())

# in order to correctly run old reports, need to pretend today is the day after the latest date in report
today_pd = inputDf[RPT_COL_DATE].max() + pd.Timedelta(days=1)
print(f"looking at report, inferred today = {today_pd.date()}")

# define lookback start dates
lookback_14_start = today_pd - pd.Timedelta(days=14)
lookback_104_start = today_pd - pd.Timedelta(days=104)
# Define the start and end of the current month
current_month_start = today_pd.replace(day=1)
next_month_start = current_month_start + pd.DateOffset(months=1)


# Define lookback windows with their respective start and end dates
# start date is inclusive, but end date is EXCLUSIVE
lookback_windows = {
    'recent_14_days': (lookback_14_start, today_pd),
    '90_days_prior_to_recent_14_days': (lookback_104_start, lookback_14_start)
}

download_preview_input = True
# Print out lookback windows for debugging
for window_name, (start_date, end_date) in lookback_windows.items():
    print(f"Lookback Window: {window_name}, Start Date (inclusive): {start_date.date()}, End Date (exclusive): {end_date.date()}")

## get idea of how many campaigns satisfy the various lookback windows

# Calculate the number of days with data for each campaign
def count_trafficking_days_in_date_range(df, start_date, end_date):
    period_data = df[(df[RPT_COL_DATE] >= start_date) & (df[RPT_COL_DATE] < end_date)]
    min_date = period_data[RPT_COL_DATE].min()
    max_date = period_data[RPT_COL_DATE].max()
    num_days = (max_date - min_date).days + 1
    return num_days

# Define a function to count the number of days with data for each lookback window
def count_days_with_data_for_windows(df):
    return pd.Series({
        window: count_trafficking_days_in_date_range(df, start_date, end_date)
        for window, (start_date, end_date) in lookback_windows.items()
    })

# Group by Campaign and count the number of days with data
days_with_data = inputDf.groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN]).apply(count_days_with_data_for_windows).reset_index()

# Calculate the distribution of campaigns with data for each lookback window
distributions = {window: days_with_data[window].value_counts(normalize=True).sort_index() for window in lookback_windows}

# Print the percentage of campaigns with full data for each lookback window
for window, distribution in distributions.items():
    if not distribution.empty:
        last_value = distribution.index[-1]
        last_percentage = distribution.iloc[-1]
        print(f"Data in Lookback Window: {window}, Actual Days: {last_value}, Campaigns with full data: {last_percentage:.2%}")

### Common Functions
# Calculate CTR for a given lookback window
def calculate_ctr(df, start_date, end_date):
    data = df[(df[RPT_COL_DATE] >= start_date) & (df[RPT_COL_DATE] < end_date)]
    total_clicks = data[RPT_COL_CLICKS].sum()
    total_impr = data[RPT_COL_IMPR].sum()
    ctr = total_clicks / total_impr if total_impr != 0 else float(total_impr)

    # print(f"CTR from {start_date.date()} to {end_date.date()}: {total_clicks} / {total_impr} => {ctr}")

    return np.round(ctr, 4)


# Calculate CPC for a given lookback window
def calculate_cpc(df, start_date, end_date):
    data = df[(df[RPT_COL_DATE] >= start_date) & (df[RPT_COL_DATE] < end_date)]
    total_cost = data[RPT_COL_PUB_COST].sum()
    total_clicks = data[RPT_COL_CLICKS].sum()
    cpc = total_cost / total_clicks if total_clicks != 0 else float(total_clicks)

    # print(f"CPC from {start_date.date()} to {end_date.date()}: {total_cost} / {total_clicks} => {cpc}")

    return np.round(cpc, 2)


# Calculate Cost/Conv for a given lookback window
def calculate_cpconv(df, start_date, end_date):
    data = df[(df[RPT_COL_DATE] >= start_date) & (df[RPT_COL_DATE] < end_date)]
    total_cost = data[RPT_COL_PUB_COST].sum()
    total_conv = data[RPT_COL_CONV].sum()
    cpconv = total_cost / total_conv if total_conv != 0 else float(total_conv)

    # print(f"CPConv from {start_date.date()} to {end_date.date()}: {total_cost} / {total_conv} => {cpconv}")

    return np.round(cpconv, 2)


# Calculate Conv for a given lookback window
def calculate_conv(df, start_date, end_date):
    data = df[(df[RPT_COL_DATE] >= start_date) & (df[RPT_COL_DATE] < end_date)]
    total_conv = data[RPT_COL_CONV].sum()
    conv = total_conv if total_conv != 0 else float(total_conv)

    # print(f"Conv from {start_date.date()} to {end_date.date()}: {total_conv} => {conv}")

    return np.round(conv, 2)



def calculate_prorated_ratio(today, start_date, end_date):
    """
    Calculate the prorated ratio of the period from start_date to end_date that has elapsed as of today.

    Args:
    today (pd.Timestamp): The current date.
    start_date (pd.Timestamp): The start date of the period.
    end_date (pd.Timestamp): The end date of the period.

    Returns:
    float: The prorated ratio of the elapsed period.
    """
    if today < start_date:
        return 0.0
    if today >= end_date:
        return 1.0

    total_days = (end_date - start_date).days
    elapsed_days = (today - start_date).days

    proration = elapsed_days / total_days if total_days != 0 else 0.0

    return np.round(proration, 4)


### Campaign Benchmarks

def aggregate_trends(x):
    recent_14_days_start, recent_14_days_end = lookback_windows['recent_14_days']
    prior_90_days_start, prior_90_days_end = lookback_windows['90_days_prior_to_recent_14_days']
    
    return pd.Series({
        BULK_COL_CAMPAIGN__CPC__BENCHMARK: calculate_cpc(x, prior_90_days_start, prior_90_days_end),
        BULK_COL_CAMPAIGN__CPC__LAST_14_DAYS: calculate_cpc(x, recent_14_days_start, recent_14_days_end),
        BULK_COL_CAMPAIGN__CONV__BENCHMARK: calculate_conv(x, prior_90_days_start, prior_90_days_end),
        BULK_COL_CAMPAIGN__CONV__LAST_14_DAYS: calculate_conv(x, recent_14_days_start, recent_14_days_end),
        BULK_COL_CAMPAIGN__COST_PER_CONV__BENCHMARK: calculate_cpconv(x, prior_90_days_start, prior_90_days_end),
        BULK_COL_CAMPAIGN__COST_PER_CONV__LAST_14_DAYS: calculate_cpconv(x, recent_14_days_start, recent_14_days_end),
        BULK_COL_CAMPAIGN__CTR__BENCHMARK: calculate_ctr(x, prior_90_days_start, prior_90_days_end),
        BULK_COL_CAMPAIGN__CTR__LAST_14_DAYS: calculate_ctr(x, recent_14_days_start, recent_14_days_end),
        RPT_COL_STRATEGY: x[RPT_COL_STRATEGY].iloc[-1]  # Take the last value of RPT_COL_STRATEGY
    })


# Group by Campaign and apply custom aggregation functions
campaign_trends = inputDf.groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_CAMPAIGN_ID]).apply(aggregate_trends).reset_index()
print("campaign_trends.dtypes\n", campaign_trends.dtypes)

def assign_performance(cpc_recent, cpc_baseline):
    if cpc_recent == 0 or (cpc_baseline == 0 or cpc_baseline == ""):
        return 7
    
    performance_ratio = (cpc_recent - cpc_baseline) / cpc_baseline * 100.0
    score = calc_performance_score(performance_ratio)

    return score

# customer-defined performance score; lower is better
def calc_performance_score(performance):
    score = 7
    if performance < -50:
        score = 1
    elif -50 <= performance <= -25:
        score = 2
    elif -25 < performance < -7:
        score = 3
    elif -7 <= performance <= 7:
        score = 4
    elif 7 < performance <= 33:
        score = 5
    elif 33 < performance <= 100:
        score = 6
    elif performance > 100:
        score = 7
    return int(score) if not np.isnan(score) else score


def assign_conv_performance(recent, baseline):
    if recent == 0 or (baseline == 0 or baseline == ""):
        return 7
    
    performance_ratio = (recent - baseline) / baseline * 100.0
    score = calc_conv_performance_score(performance_ratio)

    return score

# customer-defined conv performance score; lower is better
def calc_conv_performance_score(cov_performance):
    score = 7
    if cov_performance < -50:
        score = 7
    elif -50 <= cov_performance <= -25:
        score = 6
    elif -25 < cov_performance < -7:
        score = 5
    elif -7 <= cov_performance <= 7:
        score = 4
    elif 7 < cov_performance <= 33:
        score = 3
    elif 33 < cov_performance <= 100:
        score = 2
    elif cov_performance > 100:
        score = 1
    return int(score) if not np.isnan(score) else score

# CPC Trend Benchmark Performance compares recent 14-day CPL with 90-day period before that
def calculate_campaign_cpc_trend_performance(row):
    return assign_performance(row[BULK_COL_CAMPAIGN__CPC__LAST_14_DAYS], row[BULK_COL_CAMPAIGN__CPC__BENCHMARK])

def calculate_campaign_conv_trend_performance(row):
    # Use the new performance score logic for conversion performance
    return assign_conv_performance(row[BULK_COL_CAMPAIGN__CONV__LAST_14_DAYS] , row[BULK_COL_CAMPAIGN__CONV__BENCHMARK])

def calculate_campaign_cost_per_conv_trend_performance(row):
    return assign_performance(row[BULK_COL_CAMPAIGN__COST_PER_CONV__LAST_14_DAYS], row[BULK_COL_CAMPAIGN__COST_PER_CONV__BENCHMARK])

def calculate_campaign_ctr_trend_performance(row):
    return assign_conv_performance(row[BULK_COL_CAMPAIGN__CTR__LAST_14_DAYS], row[BULK_COL_CAMPAIGN__CTR__BENCHMARK])

def calculate_campaign_ppc_score(row):
    return row[BULK_COL_CAMPAIGN__CPC__PERFORMANCE] + row[BULK_COL_CAMPAIGN__CONV__PERFORMANCE] + row[BULK_COL_CAMPAIGN__CTR__PERFORMANCE] + row[BULK_COL_CAMPAIGN__COST_PER_CONV__PERFORMANCE]

def calculate_campaign_ppc_rank(row):
    rank = ""
    if 4 <= row[BULK_COL_CAMPAIGN__PPC_SCORE] <= 12:
        rank = 'A'
    elif 13 <= row[BULK_COL_CAMPAIGN__PPC_SCORE] <= 18:
        rank = 'B'
    elif 19 <= row[BULK_COL_CAMPAIGN__PPC_SCORE] <= 22:
        rank = 'C'
    elif 23 <= row[BULK_COL_CAMPAIGN__PPC_SCORE] <= 28:
        rank = 'D'
    return rank

campaign_trends[BULK_COL_CAMPAIGN__CPC__PERFORMANCE] = campaign_trends.apply(calculate_campaign_cpc_trend_performance, axis=1)
campaign_trends[BULK_COL_CAMPAIGN__CONV__PERFORMANCE] = campaign_trends.apply(calculate_campaign_conv_trend_performance, axis=1)
campaign_trends[BULK_COL_CAMPAIGN__COST_PER_CONV__PERFORMANCE] = campaign_trends.apply(calculate_campaign_cost_per_conv_trend_performance, axis=1)
campaign_trends[BULK_COL_CAMPAIGN__CTR__PERFORMANCE] = campaign_trends.apply(calculate_campaign_ctr_trend_performance, axis=1)
campaign_trends[BULK_COL_CAMPAIGN__PPC_SCORE] = campaign_trends.apply(calculate_campaign_ppc_score, axis=1)
campaign_trends[BULK_COL_CAMPAIGN__PPC_RANK] = campaign_trends.apply(calculate_campaign_ppc_rank, axis=1)

# Strategy - Conv - Performance calculation
def calculate_strategy_conv_trend_performance(row):
    return assign_conv_performance(row[BULK_COL_STRATEGY__CONV__LAST_14_DAYS], row[BULK_COL_STRATEGY__CONV__BENCHMARK])

### Strategy Benchmarks
def calculate_strategy_benchmarks(group):
    recent_14_days_start, recent_14_days_end = lookback_windows['recent_14_days']
    prior_90_days_start, prior_90_days_end = lookback_windows['90_days_prior_to_recent_14_days']
    
    return pd.Series({
        BULK_COL_STRATEGY__CPC__BENCHMARK: round(calculate_cpc(group, prior_90_days_start, prior_90_days_end), 2),
        BULK_COL_STRATEGY__CPC__LAST_14_DAYS: round(calculate_cpc(group, recent_14_days_start, recent_14_days_end), 2),
        BULK_COL_STRATEGY__CONV__BENCHMARK: round(calculate_conv(group, prior_90_days_start, prior_90_days_end)/7, 2),
        BULK_COL_STRATEGY__CONV__LAST_14_DAYS: round(calculate_conv(group, recent_14_days_start, recent_14_days_end), 2),
        BULK_COL_STRATEGY__COST_PER_CONV__BENCHMARK: round(calculate_cpconv(group, prior_90_days_start, prior_90_days_end), 2),
        BULK_COL_STRATEGY__COST_PER_CONV__LAST_14_DAYS: round(calculate_cpconv(group, recent_14_days_start, recent_14_days_end), 2),
        BULK_COL_STRATEGY__CTR__BENCHMARK: round(calculate_ctr(group, prior_90_days_start, prior_90_days_end), 2),
        BULK_COL_STRATEGY__CTR__LAST_14_DAYS: round(calculate_ctr(group, recent_14_days_start, recent_14_days_end), 2),
    })

strategy_benchmarks = inputDf.groupby([RPT_COL_STRATEGY]).apply(calculate_strategy_benchmarks).reset_index()
print("strategy_benchmarks.dtypes\n", strategy_benchmarks.dtypes)



# CPC Trend Benchmark Performance compares recent 14-day CPL with 90-day period before that
def calculate_strategy_cpc_trend_performance(row):
    return assign_performance(row[BULK_COL_STRATEGY__CPC__LAST_14_DAYS], row[BULK_COL_STRATEGY__CPC__BENCHMARK])

# def calculate_strategy_conv_trend_performance(row):
#     return assign_performance(row[BULK_COL_STRATEGY__CONV__LAST_14_DAYS], row[BULK_COL_STRATEGY__CONV__BENCHMARK])

def calculate_strategy_cost_per_conv_trend_performance(row):
    return assign_performance(row[BULK_COL_STRATEGY__COST_PER_CONV__LAST_14_DAYS], row[BULK_COL_STRATEGY__COST_PER_CONV__BENCHMARK])

def calculate_strategy_ctr_trend_performance(row):
    return assign_conv_performance(row[BULK_COL_STRATEGY__CTR__LAST_14_DAYS], row[BULK_COL_STRATEGY__CTR__BENCHMARK])

def calculate_strategy_ppc_score(row):
    return row[BULK_COL_STRATEGY__CPC__PERFORMANCE] + row[BULK_COL_STRATEGY__CONV__PERFORMANCE] + row[BULK_COL_STRATEGY__CTR__PERFORMANCE] + row[BULK_COL_STRATEGY__COST_PER_CONV__PERFORMANCE]

def calculate_strategy_ppc_rank(row):
    return row[BULK_COL_STRATEGY__CPC__PERFORMANCE] + row[BULK_COL_STRATEGY__CONV__PERFORMANCE] + row[BULK_COL_STRATEGY__CTR__PERFORMANCE] + row[BULK_COL_STRATEGY__COST_PER_CONV__PERFORMANCE]

def calculate_strategy_ppc_rank(row):
    rank = ""
    if 4<= row[BULK_COL_STRATEGY__PPC_SCORE] <=12 :
        rank = 'A'
    elif 13 <= row[BULK_COL_STRATEGY__PPC_SCORE] <= 18:
        rank = 'B'
    elif 19 <= row[BULK_COL_STRATEGY__PPC_SCORE] <= 22:
        rank = 'C'
    elif 23 <= row[BULK_COL_STRATEGY__PPC_SCORE] <= 28:
        rank = 'D'
    return rank


strategy_benchmarks[BULK_COL_STRATEGY__CPC__PERFORMANCE] = strategy_benchmarks.apply(calculate_strategy_cpc_trend_performance, axis=1)
strategy_benchmarks[BULK_COL_STRATEGY__CONV__PERFORMANCE] = strategy_benchmarks.apply(calculate_strategy_conv_trend_performance, axis=1)
strategy_benchmarks[BULK_COL_STRATEGY__COST_PER_CONV__PERFORMANCE] = strategy_benchmarks.apply(calculate_strategy_cost_per_conv_trend_performance, axis=1)
strategy_benchmarks[BULK_COL_STRATEGY__CTR__PERFORMANCE] = strategy_benchmarks.apply(calculate_strategy_ctr_trend_performance, axis=1)
strategy_benchmarks[BULK_COL_STRATEGY__PPC_SCORE] = strategy_benchmarks.apply(calculate_strategy_ppc_score, axis=1)
strategy_benchmarks[BULK_COL_STRATEGY__PPC_RANK] = strategy_benchmarks.apply(calculate_strategy_ppc_rank, axis=1)

# Combined Strategy Benchmarks
merged_df = campaign_trends.merge(strategy_benchmarks, on=RPT_COL_STRATEGY, how='outer') 


output_cols = [
    RPT_COL_ACCOUNT, 
    RPT_COL_CAMPAIGN,
    RPT_COL_CAMPAIGN_ID,
    BULK_COL_CAMPAIGN__CTR__BENCHMARK,
    BULK_COL_CAMPAIGN__CTR__LAST_14_DAYS,
    BULK_COL_CAMPAIGN__CTR__PERFORMANCE,
    BULK_COL_CAMPAIGN__CPC__BENCHMARK,
    BULK_COL_CAMPAIGN__CPC__LAST_14_DAYS,
    BULK_COL_CAMPAIGN__CPC__PERFORMANCE, 
    BULK_COL_CAMPAIGN__COST_PER_CONV__BENCHMARK,
    BULK_COL_CAMPAIGN__COST_PER_CONV__LAST_14_DAYS,
    BULK_COL_CAMPAIGN__COST_PER_CONV__PERFORMANCE,
    BULK_COL_CAMPAIGN__CONV__BENCHMARK,
    BULK_COL_CAMPAIGN__CONV__LAST_14_DAYS,
    BULK_COL_CAMPAIGN__CONV__PERFORMANCE, 
    BULK_COL_CAMPAIGN__PPC_SCORE,
    BULK_COL_CAMPAIGN__PPC_RANK,
    BULK_COL_STRATEGY__CTR__BENCHMARK,
    BULK_COL_STRATEGY__CTR__LAST_14_DAYS,
    BULK_COL_STRATEGY__CTR__PERFORMANCE,
    BULK_COL_STRATEGY__CPC__BENCHMARK,
    BULK_COL_STRATEGY__CPC__LAST_14_DAYS,
    BULK_COL_STRATEGY__CPC__PERFORMANCE, 
    BULK_COL_STRATEGY__COST_PER_CONV__BENCHMARK,
    BULK_COL_STRATEGY__COST_PER_CONV__LAST_14_DAYS,
    BULK_COL_STRATEGY__COST_PER_CONV__PERFORMANCE,
    BULK_COL_STRATEGY__CONV__BENCHMARK,
    BULK_COL_STRATEGY__CONV__LAST_14_DAYS,
    BULK_COL_STRATEGY__CONV__PERFORMANCE, 
    BULK_COL_STRATEGY__PPC_SCORE,
    BULK_COL_STRATEGY__PPC_RANK         
]

skip_output_validations = True
outputDf = merged_df.loc[:, output_cols].copy()
outputDf[BULK_COL_CAMPAIGN__CTR__BENCHMARK] = outputDf[BULK_COL_CAMPAIGN__CTR__BENCHMARK].apply(lambda x: f"{x * 100:.2f}%")
outputDf[BULK_COL_CAMPAIGN__CTR__LAST_14_DAYS] = outputDf[BULK_COL_CAMPAIGN__CTR__LAST_14_DAYS].apply(lambda x: f"{x * 100:.2f}%")
outputDf[BULK_COL_STRATEGY__CTR__BENCHMARK] = outputDf[BULK_COL_STRATEGY__CTR__BENCHMARK].apply(lambda x: f"{x * 100:.2f}%")
outputDf[BULK_COL_STRATEGY__CTR__LAST_14_DAYS] = outputDf[BULK_COL_STRATEGY__CTR__LAST_14_DAYS].apply(lambda x: f"{x * 100:.2f}%")

print("outputDf.shape", outputDf.shape)
print("outputDf.dtypes\n", outputDf.dtypes)

Post generated on 2024-11-27 06:58:46 GMT

29 Oct 2024

« Script 1459: Tagging County Dimension for Google Script 1463: Predictive Revenue (Campaigns) »

MarinOne Scripts Creator's Corner