Script 223: Strategy assignment 3

Purpose

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

To Elaborate

The Python script is designed to process marketing campaign data and assign appropriate strategies based on performance metrics such as Return on Advertising Spend (ROAS) and conversion rates. It utilizes a 7-day and 14-day lookback period to aggregate data on publication costs, revenue, and conversions. The script then applies specific business rules to determine the strategy for each campaign. For instance, if a campaign’s ROAS is greater than or equal to 10 and it has more than 5 conversions, it is assigned a ‘Brand Awareness Targeting’ strategy. Alternatively, if the ROAS is less than or equal to 10 and the publication cost over the last 14 days is at least 30, 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 and calculating date ranges for a 7-day and 14-day lookback period. These date ranges are used to filter the input data for analysis.

  2. Data Aggregation: The script filters the input data to create a working DataFrame that aggregates publication costs, revenue, and conversions over the last 7 days, grouped by account and campaign. It also calculates the total publication cost over the last 14 days.

  3. Data Merging: The 14-day publication cost data is merged into the working DataFrame, allowing for comparison and strategy assignment based on both 7-day and 14-day metrics.

  4. ROAS Calculation: The script calculates the ROAS for each campaign by dividing the total revenue by the total publication cost over the 7-day period.

  5. Strategy Assignment: Using predefined business rules, the script assigns strategies to campaigns. Campaigns with a high ROAS and conversion rate are assigned ‘Brand Awareness Targeting’, while those with lower ROAS but higher publication costs are assigned ‘10:1 ROAS’.

  6. Output Preparation: Finally, the script prepares the output DataFrame, which includes only the account, campaign, and assigned strategy, and prints it 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 2024-11-27 06:58:46 GMT

comments powered by Disqus