Purpose:

The Python script processes marketing data to assign strategies based on performance metrics over specific time periods.

To Elaborate

The Python script is designed to analyze marketing campaign data and assign strategies based on specific performance metrics. It calculates the Return on Advertising Spend (ROAS) and evaluates conversion rates over a 7-day period, while also considering the advertising cost over a 14-day period. The script applies business rules to determine the appropriate strategy for each campaign. If a campaign has a ROAS of 10 or more and more than 5 conversions, it is assigned a “Brand Awareness Targeting” strategy. Alternatively, if the ROAS is 10 or less and the 14-day advertising cost is 30 or more, the campaign is assigned a “10:1 ROAS” strategy. The output is a simplified dataset indicating the account, campaign, and assigned strategy.

Walking Through the Code

1. Data Preparation:
   • The script begins by defining constants for column names used in the data processing.
   • It calculates the start and end dates for 7-day and 14-day lookback periods to filter the data accordingly.
2. Data Aggregation:
   • The script filters the input data for the 7-day period and aggregates it by account and campaign, summing up the publication cost, revenue, and conversions.
   • It also calculates the total publication cost over the 14-day period for each campaign.
3. Data Merging:
   • The 14-day publication cost data is merged with the 7-day aggregated data to create a comprehensive dataset for analysis.
4. ROAS Calculation:
   • The script calculates the ROAS by dividing the total revenue by the total publication cost for each campaign.
5. Strategy Assignment:
   • A temporary column for strategy is initialized with NaN values.
   • The script applies two business rules to assign strategies based on ROAS and conversion metrics, updating the strategy column accordingly.
6. Output Preparation:
   • The final output dataset is prepared, containing only the account, campaign, and assigned strategy columns, which is then printed for review.

Vitals

• Script ID : 223
• Client ID / Customer ID: 1306922797 / 60269073
• Action Type: Bulk Upload (Preview)
• Item Changed: Campaign
• Output Columns: Account, Campaign, Strategy
• Linked Datasource: M1 Report
• Reference Datasource: None
• Owner: Jonathan Reichl (jreichl@marinsoftware.com)
• Created by Jonathan Reichl on 2023-06-21 14:03
• Last Updated by Grégory Pantaine on 2024-05-28 11:38

> 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
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DATE = 'Date'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_PUB_COST = 'Pub. Cost £'
RPT_COL_CONV = 'Conv.'
RPT_COL_ROAS = 'ROAS'
RPT_COL_REVENUE = 'Revenue £'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
RPT_COL_STRATEGY = 'Strategy'
BULK_COL_STRATEGY = 'Strategy'
RPT_COL_LAST_14DAYSPUBCOST = 'Last 14 Days Pub Cost'

#outputDf[BULK_COL_STRATEGY] = inputDf['Date']

today = datetime.datetime.now(CLIENT_TIMEZONE).date()
print(tableize(inputDf))
print("=== input ===")
#print(tableize(inputDf))
print(inputDf.dtypes)

# 7-day lookback 
start_date_7 = pd.to_datetime(datetime.date.today() - datetime.timedelta(days=7))
end_date_7 = pd.to_datetime(datetime.date.today() - datetime.timedelta(days=1))

print(start_date_7)
print(end_date_7)

# 14-day lookback 
start_date_14 = pd.to_datetime(datetime.date.today() - datetime.timedelta(days=14))
end_date_14 = pd.to_datetime(datetime.date.today() - datetime.timedelta(days=1))

working_df = inputDf[(inputDf['Date'].between(start_date_7, end_date_7))].groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN]).agg({
    RPT_COL_PUB_COST: 'sum',
    RPT_COL_REVENUE: 'sum',
    RPT_COL_CONV: 'sum'
}).reset_index()

pub_cost_14days = inputDf[(inputDf['Date'].between(start_date_14, end_date_14))].groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN])[RPT_COL_PUB_COST].sum()

# Add the extra column to working_df
working_df = working_df.merge(pub_cost_14days.rename(RPT_COL_LAST_14DAYSPUBCOST).reset_index(), on=[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN], how='left')
print('TEST--------------##################----------------')
print(tableize(working_df))

# calc roas 
working_df[RPT_COL_ROAS] = working_df[RPT_COL_REVENUE] / working_df[RPT_COL_PUB_COST]

# blank out tmp field
working_df[RPT_COL_STRATEGY] = np.nan

# assignment rule 1
working_df.loc[(working_df[RPT_COL_ROAS] >= 10) & (working_df[RPT_COL_CONV] > 5), RPT_COL_STRATEGY] = 'Brand Awareness Targeting'

# assignment rule 2
working_df.loc[(working_df[RPT_COL_ROAS] <= 10) & (working_df[RPT_COL_LAST_14DAYSPUBCOST] >= 30), RPT_COL_STRATEGY] = '10:1 ROAS'

outputDf = working_df[[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_STRATEGY]]

print("=== output ===")
print(outputDf)

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