Script 379: 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 processes input data to filter out records older than two days, aggregates key metrics like publication cost, revenue, and impressions, and calculates the Return on Advertising Spend (ROAS) and impression share. The script then computes a new headroom adjustment value for each keyword, ensuring that any negative adjustments are set to zero. Finally, it filters the results to include only those with an impression share below 95%, preparing the data for further analysis or action.

Walking Through the Code

  1. Data Preparation:
    • The script begins by filtering the input data to exclude records with a date 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 then aggregates the publication cost, revenue, impressions, and impression opportunities, which are essential metrics for calculating performance.
  3. Performance Calculation:
    • The script calculates the ROAS by dividing the total revenue by the total publication cost for each group. It also computes the impression share by dividing the total impressions by the total impression opportunities.
  4. Headroom Adjustment:
    • A new keyword headroom adjustment value is calculated based on the ROAS. If the calculated adjustment is negative, it is set to zero to avoid reducing the budget allocation.
  5. Filtering Results:
    • The final step filters the grouped data to include only those records with an impression share below 95%, ensuring that only underperforming keywords are considered for further budget adjustments.

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 2025-03-11 01:25:51 GMT

comments powered by Disqus