Script 377: Adjust for KW Headroom

Purpose

The Python script adjusts keyword headroom based on historical performance data to optimize budget allocation.

To Elaborate

The script is designed to optimize keyword budget allocation by adjusting the “KW Headroom Adj” value based on historical performance data. It filters out data older than two days and groups it by keyword, match type, account, campaign, and group. The script calculates the Return on Advertising Spend (ROAS) and impression share for each group. If the calculated headroom adjustment is less than or equal to zero, it is set to zero. Finally, the script filters out groups with an impression share of 95% or more, indicating that these groups are already capturing most of the available impressions and may not need further budget adjustments. This process helps in making informed decisions for structured budget allocation (SBA) by focusing on underperforming keywords that have room for growth.

Walking Through the Code

  1. Data Filtering
    • The script begins by filtering the input data to exclude records older than two days. This ensures that only recent data is considered for analysis.
  2. Data Grouping
    • The filtered data is grouped by keyword, match type, account, campaign, and group. Aggregations are performed to sum up the publisher cost, revenue, impressions, and impression opportunities.
  3. Calculating Metrics
    • The script calculates the ROAS by dividing the total revenue by the total publisher cost for each group. It also calculates the impression share by dividing the total impressions by the total impression opportunities.
  4. Adjusting Headroom
    • The “KW Headroom Adj” is calculated based on the ROAS. If the adjustment value is less than or equal to zero, it is set to zero to avoid negative adjustments.
  5. Filtering for Output
    • Finally, the script filters the grouped data to include only those groups with an impression share of less than 95%, indicating potential for increased budget allocation.

Vitals

  • Script ID : 377
  • Client ID / Customer ID: 248317190 / 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:54
  • Last Updated by Jeremy Brown 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

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] = ((grouped_df[RPT_COL_ROAS] / 1)-1)*100

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