Script 1245: Tag Performance of Meta Ads

Purpose:

The Python script tags Meta Ads campaigns at the ad level based on performance metrics such as eCPM and CTR, categorizing them as “winning” or “losing” according to predefined business rules.

To Elaborate

The script is designed to evaluate and tag Meta Ads campaigns at the ad level by analyzing their performance metrics, specifically eCPM (effective cost per mille) and CTR (click-through rate). It applies a set of business rules to determine whether each campaign is “winning” or “losing” based on these metrics. The rules are defined for different campaign dimensions such as “Reach,” “Traffic,” and “Conversion,” with specific thresholds for each. For example, campaigns with names containing certain keywords like “Evergreen” or “GNO” are evaluated against these thresholds to determine their performance status. The script processes input data, calculates missing eCPM values, and applies these rules to update the campaign tags accordingly.

Walking Through the Code

Initialization and Setup
- The script begins by determining whether it is running on a server or locally. If running locally, it loads a data source dictionary from a specified pickle file.
- It imports necessary libraries such as pandas, numpy, and others for data manipulation and processing.
Data Preparation
- The script sets up the input DataFrame (inputDf) from the data source dictionary and initializes the output DataFrame (outputDf) with the same structure.
- Missing eCPM values are calculated using the formula: (Pub. Cost $ / Impressions) * 1000.
Business Rules Definition
- A list of dictionaries defines the business rules, specifying keywords, metrics, thresholds, and the corresponding dimension and values for “winning” or “losing” status.
Applying Business Rules
- The script iterates over each rule, applying conditions to filter the DataFrame based on campaign names and performance metrics.
- It updates the dimensions in the DataFrame according to whether the metrics exceed or fall below the specified thresholds.
Output and Debugging
- The script uses a utility function to select and output the changed data, comparing the original and modified DataFrames.
- If running in local development mode, it writes the output and debug information to CSV files for further analysis.

Vitals

Script ID : 1245
Client ID / Customer ID: 1306920543 / 60268855
Action Type: Bulk Upload
Item Changed: Ad
Output Columns: Account, Campaign, Group, Creative ID, Conversion, Reach, Traffic
Linked Datasource: M1 Report
Reference Datasource: None
Owner: Michael Huang (mhuang@marinsoftware.com)
Created by Michael Huang on 2024-07-01 00:30
Last Updated by afarrokhi@marinsoftware.com on 2024-07-10 18:50

> See it in Action

Python Code

##
## name: Tag Performance of Meta Campaigns
## description: Tags the following Marin Dimensions based on performance
##  - Dimensions: Reach, Traffic, Conversion
##  
## 
## author: Michael S. Huang
## created: 2024-06-19
## 


########### START - Local Mode Config ###########
# Step 1: Uncomment download_preview_input flag and run Preview successfully with the Datasources you want
download_preview_input=False
# Step 2: In MarinOne, go to Scripts -> Preview -> Logs, download 'dataSourceDict' pickle file, and update pickle_path below
# pickle_path = ''
pickle_path = '/Users/mhuang/Downloads/pickle/windsor_fashions_meta_campaign_tagging_20240623.pkl'
# Step 3: Copy this script into local IDE with Python virtual env loaded with pandas and numpy.
# Step 4: Run locally with below code to init dataSourceDict

# determine if code is running on server or locally
def is_executing_on_server():
    try:
        # Attempt to access a known restricted builtin
        dict_items = dataSourceDict.items()
        return True
    except NameError:
        # NameError: dataSourceDict object is missing (indicating not on server)
        return False

local_dev = False

if is_executing_on_server():
    print("Code is executing on server. Skip init.")
elif len(pickle_path) > 3:
    print("Code is NOT executing on server. Doing init.")
    local_dev = True
    # load dataSourceDict via pickled file
    import pickle
    dataSourceDict = pickle.load(open(pickle_path, 'rb'))

    # print shape and first 5 rows for each entry in dataSourceDict
    for key, value in dataSourceDict.items():
        print(f"Shape of dataSourceDict[{key}]: {value.shape}")
        # print(f"First 5 rows of dataSourceDict[{key}]:\n{value.head(5)}")

    # set outputDf same as inputDf
    inputDf = dataSourceDict["1"]
    outputDf = inputDf.copy()

    # setup timezone
    import datetime
    # Chicago Timezone is GMT-5. Adjust as needed.
    CLIENT_TIMEZONE = datetime.timezone(datetime.timedelta(hours=-5))

    # import pandas
    import pandas as pd
    import numpy as np

    # other imports
    import re
    import urllib

    # import Marin util functions
    from marin_scripts_utils import tableize, select_changed

    # pandas settings
    pd.set_option('display.max_columns', None)  # Display all columns
    pd.set_option('display.max_colwidth', None)  # Display full content of each column

else:
    print("Running locally but no pickle path defined. dataSourceDict not loaded.")
    exit(1)
########### END - Local Mode Setup ###########

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

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_GROUP = 'Group'
RPT_COL_CREATIVE_ID = 'Creative ID'
RPT_COL_CAMPAIGN_TYPE = 'Campaign Type'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_IMPR = 'Impr.'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_ECPM = 'eCPM $'
RPT_COL_CTR = 'CTR %'
RPT_COL_REACH = 'Reach'
RPT_COL_TRAFFIC = 'Traffic'
RPT_COL_CONVERSION = 'Conversion'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_CREATIVE_ID = 'Creative ID'
BULK_COL_REACH = 'Reach'
BULK_COL_TRAFFIC = 'Traffic'
BULK_COL_CONVERSION = 'Conversion'
outputDf[BULK_COL_REACH] = "<<YOUR VALUE>>"
outputDf[BULK_COL_TRAFFIC] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CONVERSION] = "<<YOUR VALUE>>"

# user code starts here

### Business Rules as Configurable Params

# Define the business rules in a dictionary
business_rules = [
    # 1. “Reach” dimension - campaign names containing “Evergreen” and “Reach” that perform below $2.21 CPM are “winning”, and if performing above $2.21 “losing”
    {
        "keywords": ["Evergreen", "Reach"],
        "metric": RPT_COL_ECPM,
        "threshold": 2.21,
        "dimension": BULK_COL_REACH,
        "value_high": "Losing",
        "value_low": "Winning"
    },
    # 2. “Reach” dimension - campaign names containing “GNO” and “Reach” that perform below $2.21 CPM are “winning”, and if performing above $2.21 “losing”
    {
        "keywords": ["GNO", "Reach"],
        "metric": RPT_COL_ECPM,
        "threshold": 2.21,
        "dimension": BULK_COL_REACH,
        "value_high": "Losing",
        "value_low": "Winning"
    },
    # 3. “Reach” dimension - campaign names containing “Formal” and “Reach” that perform below $2.36 CPM are “winning”, and if performing above $2.36 “losing”
    {
        "keywords": ["Formal", "Reach"],
        "metric": RPT_COL_ECPM,
        "threshold": 2.36,
        "dimension": BULK_COL_REACH,
        "value_high": "Losing",
        "value_low": "Winning"
    },
    # 4. “Traffic” dimension - campaign names containing “Evergreen” and “Traffic” that perform below 0.62% CTR are “losing”, and if performing above 0.62% “winning”
    {
        "keywords": ["Evergreen", "Traffic"],
        "metric": RPT_COL_CTR,
        "threshold": 0.0062,
        "dimension": BULK_COL_TRAFFIC,
        "value_high": "Winning",
        "value_low": "Losing"
    },
    # 5. “Traffic” dimension - campaign names containing “GNO” and “Traffic” that perform below 0.55% CTR are “losing”, and if performing above 0.55% CTR “winning”
    {
        "keywords": ["GNO", "Traffic"],
        "metric": RPT_COL_CTR,
        "threshold": 0.0055,
        "dimension": BULK_COL_TRAFFIC,
        "value_high": "Winning",
        "value_low": "Losing"
    },
    # 6. “Traffic” dimension - campaign names containing “Formal” and “Traffic” that perform below 0.77% CTR are “losing”, and if performing above 0.77% CTR “winning”
    {
        "keywords": ["Formal", "Traffic"],
        "metric": RPT_COL_CTR,
        "threshold": 0.0077,
        "dimension": BULK_COL_TRAFFIC,
        "value_high": "Winning",
        "value_low": "Losing"
    },
    # 7. “Conversion” dimension - campaign names containing “Evergreen” and “Conv” that perform below 0.33% CTR are “losing”, and if performing above 0.33% “winning”
    {
        "keywords": ["Evergreen", "Conv"],
        "metric": RPT_COL_CTR,
        "threshold": 0.0033,
        "dimension": BULK_COL_CONVERSION,
        "value_high": "Winning",
        "value_low": "Losing"
    },
    # 8. “Conversion” dimension - campaign names containing “GNO” and “Conv” that perform below 0.27% CTR are “losing”, and if performing above 0.27% “winning”
    {
        "keywords": ["GNO", "Conv"],
        "metric": RPT_COL_CTR,
        "threshold": 0.0027,
        "dimension": BULK_COL_CONVERSION,
        "value_high": "Winning",
        "value_low": "Losing"
    },
    # 9. “Conversion” dimension - campaign names containing “Formal” and “Conv” that perform below 0.45% CTR are “losing”, and if performing above 0.45% “winning”
    {
        "keywords": ["Formal", "Conv"],
        "metric": RPT_COL_CTR,
        "threshold": 0.0045,
        "dimension": BULK_COL_CONVERSION,
        "value_high": "Winning",
        "value_low": "Losing"
    }
]

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

# fill in missing eCPM from Ads Perf report
inputDf[RPT_COL_ECPM] = np.round(inputDf[RPT_COL_PUB_COST] / inputDf[RPT_COL_IMPR] * 1000, 2)

originalDf = inputDf.copy()

# Apply the business rules to set dimensions
for rule in business_rules:
    # Build the condition to filter DataFrame based on all keywords
    condition = inputDf[RPT_COL_CAMPAIGN].apply(lambda x: all(keyword in x for keyword in rule["keywords"]))

    # Apply the rule based on the metric threshold
    inputDf.loc[condition & (inputDf[rule["metric"]] > rule["threshold"]), rule["dimension"]] = rule["value_high"]
    inputDf.loc[condition & (inputDf[rule["metric"]] <= rule["threshold"]), rule["dimension"]] = rule["value_low"]

outputDf, debugDf = select_changed(inputDf, 
                                   originalDf,
                                   diff_cols = [RPT_COL_REACH, RPT_COL_TRAFFIC, RPT_COL_CONVERSION],
                                   select_cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, RPT_COL_CREATIVE_ID, RPT_COL_REACH, RPT_COL_TRAFFIC, RPT_COL_CONVERSION],
                                   merged_cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, RPT_COL_CREATIVE_ID]
                                   )

print("outputDf.shape", outputDf.shape)

### local debug

if local_dev:
    output_filename = 'outputDf.csv'
    outputDf.to_csv(output_filename, index=False)
    print(f"Local Dev: Output written to: {output_filename}")

    debug_filename = 'debugDf.csv'
    debugDf.to_csv(debug_filename, index=False)
    print(f"Local Dev: Debug written to: {debug_filename}")

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

01 Jul 2024

« Script 1243: Tag Performance of Meta Ad Groups Script 1247: Alert Active Ads with Expiring Dates »

MarinOne Scripts Creator's Corner