Purpose:

The Python script processes marketing data to determine and populate the ‘NTB Uplift’ metric based on specific conditions related to revenue and new customer acquisition.

To Elaborate

The script is designed to analyze marketing campaign data and identify instances where there is a significant uplift in new-to-brand (NTB) orders. It evaluates data from a primary source, applying specific conditions to determine if a campaign strategy, particularly for ‘iPhone Brand’ and ‘iPhone Category’, has achieved a notable increase in new customer orders. The conditions include checks on revenue, return on ad spend (ROAS), and the percentage of orders that are new to the brand over a 14-day period. If these conditions are met, the script calculates the NTB uplift and updates the dataset accordingly, marking campaigns that have successfully attracted new customers.

Walking Through the Code

1. Data Initialization:
   • The script begins by defining a dictionary MATCH_TYPE to categorize match types as ‘exact’, ‘phrase’, or ‘broad’.
   • It retrieves the primary data source into inputDf and specifies the relevant columns for processing, such as ‘Keyword’, ‘Match Type’, ‘Revenue R$’, and ‘% Orders New To Brand (14 Day)’.
2. Output Preparation:
   • The script initializes the output DataFrame outputDf with columns like ‘Account’, ‘Campaign’, and ‘NTB Uplift’, setting initial values for ‘NTB Uplift’.
3. Data Processing Function:
   • A function process is defined to handle the main logic. It sets up two conditions (condition1 and condition2) to filter data based on strategy type (‘iPhone Brand’ or ‘iPhone Category’), revenue, ROAS, and the percentage of new-to-brand orders.
   • If either condition is met, the script calculates the NTB uplift as the difference in percentage of new-to-brand orders from a threshold (0.5), rounded to the nearest integer, and updates the ‘NTB Uplift’ column.
4. Execution:
   • The script calls the process function, passing inputDf to generate the processed outputDf.
   • It concludes by printing the processed data, showcasing the updated NTB uplift values.

Vitals

• Script ID : 1557
• Client ID / Customer ID: 1306927461 / 50395
• Action Type: Bulk Upload (Preview)
• Item Changed: Keyword
• Output Columns: Account, Campaign, Group, Keyword, Match Type, NTB Uplift
• Linked Datasource: M1 Report
• Reference Datasource: None
• Owner: Jeremy Brown (jbrown@marinsoftware.com)
• Created by Jeremy Brown on 2024-12-10 13:17
• Last Updated by Jeremy Brown on 2024-12-10 14:12

> 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
##
## name: SCRIPT: Populate NTB Uplift Dimension
## author: Jeremy Brown
## created: 2024-12-10
## 

MATCH_TYPE = {
    'EXACT': 'exact',
    'PHRASE': 'phrase',
    'BROAD': 'broad',
}

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

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_KEYWORD = 'Keyword'
RPT_COL_MATCH_TYPE = 'Match Type'
RPT_COL_GROUP = 'Group'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_TYPE = 'Campaign Type'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_STRATEGY_TARGET = 'Strategy Target'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_REVENUE = 'Revenue R$'
RPT_COL_ROAS = 'ROAS'
RPT_COL_ORDERS_NEW_TO_BRAND_14_DAY = '% Orders New To Brand (14 Day)'
RPT_COL_SALES_NEW_TO_BRAND_14_DAY = '% Sales New To Brand (14 Day)'
RPT_COL_NTB_UPLIFT = 'NTB Uplift'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_KEYWORD = 'Keyword'
BULK_COL_MATCH_TYPE = 'Match Type'
BULK_COL_NTB_UPLIFT = 'NTB Uplift'
outputDf[BULK_COL_NTB_UPLIFT] = "<<YOUR VALUE>>"

# user code starts here
print(tableize(inputDf))

# Function to process the data
def process(inputDf):

    # Define the conditions
    condition1 = (
        (inputDf['Strategy'] == 'iPhone Brand') &
        (inputDf['Revenue R$'] > 0.00) &
        (inputDf['ROAS'] > inputDf['Strategy Target']) &
        (inputDf['% Orders New To Brand (14 Day)'] > 0.5)
    )

    condition2 = (
        (inputDf['Strategy'] == 'iPhone Category') &
        (inputDf['Revenue R$'] > 0.00) &
        (inputDf['ROAS'] > inputDf['Strategy Target']) &  # Dynamically compare to Strategy Target
        (inputDf['% Orders New To Brand (14 Day)'] > 0.5)
    )

    # Apply the conditions to set 'NTB Uplift' to 'YES' where applicable
    inputDf.loc[condition1 | condition2, 'NTB Uplift'] = (
        ((inputDf['% Orders New To Brand (14 Day)'] - 0.5) * 100)
        .apply(lambda x: int(round(x, 0)))
    )

    return inputDf

# Trigger the main process
outputDf = process(inputDf)

print(tableize(outputDf))

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