Script 1461: Campaign Benchmarking

Purpose:

The Python script sets benchmarks for individual campaigns and strategies by analyzing performance metrics over specified time periods.

To Elaborate

The script is designed to evaluate and set benchmarks for advertising campaigns and strategies by analyzing key performance indicators (KPIs) such as cost per conversion (CPC), conversion rates, and click-through rates (CTR) over different time frames. It calculates these metrics for recent periods and compares them to historical data to assess performance trends. The script groups data by campaign and strategy, applies custom aggregation functions to compute benchmarks, and assigns performance scores based on predefined criteria. This helps in understanding how campaigns and strategies are performing relative to their historical benchmarks, enabling informed decision-making for optimizing advertising efforts.

Walking Through the Code

Data Preparation and Lookback Windows:
- The script begins by defining lookback windows for recent 14 days and the 90 days prior to that. These windows are used to analyze campaign performance over different time frames.
- It calculates the number of days with data available for each campaign within these windows, providing insights into data completeness.
Performance Metric Calculations:
- Functions are defined to calculate CTR, CPC, cost per conversion, and total conversions for specified date ranges. These metrics are crucial for evaluating campaign effectiveness.
- The script groups data by campaign and applies these functions to compute benchmarks for each metric over the defined lookback periods.
Performance Scoring and Ranking:
- Custom functions assign performance scores based on the comparison of recent metrics with historical benchmarks. Lower scores indicate better performance.
- Campaigns and strategies are ranked based on their PPC scores, which aggregate performance across multiple metrics.
Strategy Benchmarking:
- Similar calculations are performed at the strategy level, aggregating data across campaigns mapped to each strategy.
- Strategy-level performance scores and ranks are computed to provide a broader view of strategic effectiveness.
Output Preparation:
- The script merges campaign and strategy benchmarks into a final output DataFrame, formatting CTR values as percentages.
- The output includes detailed performance metrics and scores, ready for further analysis or reporting.

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 2025-03-11 01:25:51 GMT

29 Oct 2024

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

MarinOne Scripts Creator's Corner