Script 379: Adjust for KW Headroom

Purpose

The script adjusts keyword headroom based on historical performance data to optimize advertising budgets.

To Elaborate

The Python script is designed to optimize advertising budgets by adjusting the keyword headroom based on historical performance data. It processes input data to filter out records older than two days, aggregates key metrics such as publication cost, revenue, and impressions, and calculates the Return on Advertising Spend (ROAS) and impression share. The script then computes a keyword headroom adjustment factor, ensuring that it is non-negative, and applies this adjustment to keywords with an impression share below 95%. This adjustment aims to enhance the efficiency of budget allocation by focusing on keywords that have the potential for improved performance, thereby maximizing the return on investment in advertising campaigns.

Walking Through the Code

  1. Data Filtering:
    • The script filters the input data to exclude records with dates older than two days from the current date. This ensures that only recent data is considered for analysis.
  2. Data Aggregation:
    • The filtered data is grouped by keyword, match type, account, campaign, and group. It aggregates the publication cost, revenue, impressions, and impression opportunities to calculate total values for each group.
  3. ROAS and Impression Share Calculation:
    • The script calculates the Return on Advertising Spend (ROAS) by dividing the total revenue by the total publication cost. It also computes the impression share by dividing the total impressions by the total impression opportunities.
  4. Keyword Headroom Adjustment:
    • The keyword headroom adjustment is calculated based on the ROAS. If the calculated adjustment is negative, it is set to zero to prevent negative adjustments.
  5. Output Filtering:
    • The final output includes only those records where the impression share is less than 95%, indicating potential for increased exposure and performance improvement.

Vitals

  • Script ID : 379
  • Client ID / Customer ID: 1306912249 / 13095968
  • Action Type: Bulk Upload
  • Item Changed: Keyword
  • Output Columns: Account, Campaign, Group, Keyword, Match Type, KW Headroom Adj
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Jeremy Brown (jbrown@marinsoftware.com)
  • Created by Jeremy Brown on 2023-10-17 15:56
  • Last Updated by Tom Lowes on 2023-12-06 04:01
> 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

RPT_COL_CLIENT = 'Client'
RPT_COL_CURRENCY = 'Currency'
RPT_COL_KEYWORD = 'Keyword'
RPT_COL_DATE = 'Date'
RPT_COL_STATUS = 'Status'
RPT_COL_MATCH_TYPE = 'Match Type'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_GROUP = 'Group'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_GROUP_STATUS = 'Group Status'
RPT_COL_REVENUE_CAMPAIGNTYPE = 'Revenue Campaign Type'
RPT_COL_KW_HEADROOMADJ = 'KW Headroom Adj'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_REVENUE = 'Revenue $'
RPT_COL_ROAS = 'ROAS'
RPT_COL_CONV = 'Conv.'
RPT_COL_IMPR = 'Impr.'
RPT_COL_IMPR_OPS = 'Impression Ops'
RPT_COL_KEYWORD_ID = 'Keyword ID'
RPT_COL_COST_PER_CONV = 'Cost/Conv.'
RPT_COL_CONV_RATE = 'Conv. Rate %'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_KEYWORD = 'Keyword'
BULK_COL_MATCH_TYPE = 'Match Type'
BULK_COL_KW_HEADROOMADJ = 'KW Headroom Adj'

outputDf[BULK_COL_KW_HEADROOMADJ] = "<<YOUR VALUE>>"




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

# print(tableize(inputDf))


two_days_ago =  pd.Timestamp(today - datetime.timedelta(days=2))
print(two_days_ago)


filtered_df = inputDf[inputDf[RPT_COL_DATE] < two_days_ago]

print(tableize(filtered_df))

grouped_df = filtered_df.groupby([
    RPT_COL_KEYWORD,  
    RPT_COL_MATCH_TYPE, 
    RPT_COL_ACCOUNT,
     RPT_COL_CAMPAIGN, 
     RPT_COL_GROUP
     ]).agg({RPT_COL_PUB_COST: 'sum', RPT_COL_REVENUE: 'sum'
    , RPT_COL_IMPR: 'sum',  RPT_COL_IMPR_OPS: 'sum' })

# Reset the index to make the grouping columns standalone columns
grouped_df = grouped_df.reset_index()


grouped_df[RPT_COL_ROAS] = grouped_df[RPT_COL_REVENUE] / grouped_df[RPT_COL_PUB_COST]

grouped_df['Impr_Share'] = grouped_df[RPT_COL_IMPR] / grouped_df[RPT_COL_IMPR_OPS]


#grouped_df[RPT_COL_KW_HEADROOMADJ] = int(round(((grouped_df[RPT_COL_ROAS] / 1)-1)*100,0))
grouped_df[RPT_COL_KW_HEADROOMADJ] = grouped_df[RPT_COL_ROAS].apply(lambda x: int(round(((x / 1)-1)*100,0)))


grouped_df.loc[grouped_df[RPT_COL_KW_HEADROOMADJ] <= 0, RPT_COL_KW_HEADROOMADJ] = 0

# new_names = {RPT_COL_ROAS: 'Promotion', RPT_COL_REVENUE: 'Plan Type', RPT_COL_PUB_COST:'Temp for New Keywords'}
# grouped_df.rename(columns=new_names, inplace=True)  # The inplace=True modifies the DataFrame in place

outputDf = grouped_df[grouped_df['Impr_Share'] < 0.95]

Post generated on 2024-11-27 06:58:46 GMT

comments powered by Disqus