Script 1553: Script Strategy Auto Map

Purpose:

The Python script automatically maps advertising campaigns to specific strategies based on conversion rates and Return on Advertising Spend (ROAS) metrics over defined periods.

To Elaborate

The script is designed to categorize advertising campaigns into different strategic groups based on their performance metrics, specifically focusing on conversion rates over the last 365 days and ROAS over the last 30 days. It operates within the context of Microsoft Ads and excludes certain predefined groups (Group 0 and Group 5) and brand strategies from being altered. The script evaluates each campaign’s performance and assigns it to one of four strategic groups: “Top & HIGH ROAS,” “Long tail & HIGH ROAS,” “Long tail & LOW ROAS,” or “Top & LOW ROAS,” depending on whether the campaign has achieved a threshold of 15 conversions and a ROAS of 4.5. This automated mapping helps streamline the strategic planning process by ensuring campaigns are aligned with their performance metrics.

Walking Through the Code

  1. Data Preparation
    • The script begins by copying the input data into a new DataFrame (outputDf) to preserve the original data.
    • It defines column names for easy reference, such as campaign, strategy, conversions, and ROAS.
  2. Strategy Mapping Function
    • A function map_strategy is defined to determine the appropriate strategy for each campaign based on the specified conditions.
    • It checks if the current strategy belongs to Group 0 or Group 5, in which case no changes are made.
    • The function evaluates the conversion and ROAS metrics to assign one of four new strategy groups.
  3. Applying the Strategy Function
    • The script applies the map_strategy function to each row in the DataFrame, updating the strategy column with the new mapping.
  4. Output and Verification
    • The script prints all rows and specifically highlights those where the strategy has changed.
    • It provides a summary of the total number of rows processed and the number of rows where the strategy was updated, ensuring transparency and verification of the mapping process.

Vitals

  • Script ID : 1553
  • Client ID / Customer ID: 1306914548 / 60268186
  • Action Type: Bulk Upload (Preview)
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Strategy
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Grégory Pantaine (gpantaine@marinsoftware.com)
  • Created by Grégory Pantaine on 2024-11-29 15:34
  • Last Updated by Grégory Pantaine on 2024-12-19 09:56
> See it in Action

Python Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104

##
## name: Script: Strategy Auto Map
## description: Maps campaigns to strategies based on conversions and ROAS.
##
## author: ChatGPT & Gregory Pantaine
## created: 2024-11-29
##
##  Map to strategies based on:
##  Top campaign = Equal to or more than 15 conversions in the last 365 days
##  Long tail: less than 15 conversions in the last 365 days
##  High ROAS: ROAS equal to or greater than 4.5 for last 30 days
##  Low ROAS: ROAS under 4.5 for last 30 days
##  Limited to Microsot Ads.
##  Exclude Brand strategy and group 0 & group 5
##
## This means we have the below combinations or metrics and potential outputs:
## Situation 1:
## if Dimension "Last365Days Conversions" is above or equal to 15 convs last 365 days
## and if ROAS is above or equal to 4.5 for the last 30 days
## then the strategy to map the campaign to is:
## ## Strategy output should be: 'Group 4: Top & HIGH ROAS - new' 
##
## Situation 2:
## if Dimension "Last365Days Conversions" is less than 15 convs last 365 days
## and if ROAS is above or equal to 4.5 for the last 30 days
## then the strategy to map the campaign to is:
## ## Strategy output should be: 'Group 2: Long tail & HIGH ROAS - new' 
##
## Situation 3:
## if Dimension "Last365Days Conversions" is less than 15 convs last 365 days
## and if ROAS is less than 4.5 for the last 30 days
## then the strategy to map the campaign to is:
## ## Strategy output should be: 'Group 1: Long tail & LOW ROAS - new' 
##
## Situation 4:
## if Dimension "Last365Days Conversions" is more or equal to 15 convs last 365 days
## and if ROAS is less than 4.5 for the last 30 days
## then the strategy to map the campaign to is:
## Strategy output should be: 'Group 3: Top & LOW ROAS - new'

# Current date setup
today = datetime.datetime.now(CLIENT_TIMEZONE).date()

# Primary data source and columns
inputDf = dataSourceDict["1"]

# Column mappings
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_LAST365DAYS_CONVERSIONS = 'Last365Days Conversions'
RPT_COL_MSFT_PURCHASE_ROAS = 'MSFT Purchase ROAS'
RPT_COL_PUBLISHER = 'Publisher'

# Output column for the updated strategy
OUTPUT_COL_STRATEGY = 'Strategy'

# Initialize output DataFrame
outputDf = inputDf.copy()

# Function to determine the strategy based on the 4 scenarios
def map_strategy(row):
    last365_conversions = row[RPT_COL_LAST365DAYS_CONVERSIONS]
    roas = row[RPT_COL_MSFT_PURCHASE_ROAS]
    current_strategy = row[RPT_COL_STRATEGY]
    
    # Exclude Group 0 and Group 5
    if '0' in str(current_strategy) or '5' in str(current_strategy):
        return current_strategy  # Skip changes for these
    
    # Apply the 4 scenarios
    if last365_conversions >= 15 and roas >= 4.5:
        return 'Group 4: Top & HIGH ROAS - new'
    elif last365_conversions < 15 and roas >= 4.5:
        return 'Group 2: Long tail & HIGH ROAS - new'
    elif last365_conversions < 15 and roas < 4.5:
        return 'Group 1: Long tail & LOW ROAS - new'
    elif last365_conversions >= 15 and roas < 4.5:
        return 'Group 3: Top & LOW ROAS - new'
    else:
        return current_strategy  # Default to current if no condition is met

# Apply the mapping function to each row
outputDf[OUTPUT_COL_STRATEGY] = inputDf.apply(map_strategy, axis=1)

# Filter out rows where the strategy remains unchanged
#outputDf = outputDf[outputDf[OUTPUT_COL_STRATEGY] != outputDf[RPT_COL_STRATEGY]]

# Replace the filter line with this:
print("\nAll rows:")
print(tableize(outputDf[[RPT_COL_CAMPAIGN, RPT_COL_STRATEGY, OUTPUT_COL_STRATEGY]].head()))

print("\nOnly changed rows:")
changed_df = outputDf[outputDf[OUTPUT_COL_STRATEGY] != outputDf[RPT_COL_STRATEGY]]
print(tableize(changed_df[[RPT_COL_CAMPAIGN, RPT_COL_STRATEGY, OUTPUT_COL_STRATEGY]].head()))

print(f"\nTotal rows: {len(outputDf)}")
print(f"Changed rows: {len(changed_df)}")

# Print the updated DataFrame for verification
print(tableize(outputDf.head()))

# Summary message
print(f"Strategy mapping completed. Rows updated: {len(outputDf)}.")

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

comments powered by Disqus