Script 391: Adjust for KW Headroom

Purpose:

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

To Elaborate

The script is designed to optimize advertising campaigns by adjusting keyword headroom based on historical performance data. It filters out data older than two days and aggregates key metrics such as publisher cost, revenue, impressions, and impression opportunities by keyword, match type, account, campaign, and group. The script calculates the Return on Advertising Spend (ROAS) and impression share for each group, using these metrics to determine the keyword headroom adjustment. If the calculated adjustment is less than or equal to zero, it is set to zero. The script then filters out groups with an impression share of 95% or higher, ensuring that only underperforming keywords are considered for adjustment. This process helps in structured budget allocation by focusing on keywords that have room for improvement in terms of performance.

Walking Through the Code

  1. Data Filtering:
    • The script filters the input data to exclude records older than two days, ensuring that only recent data is considered for analysis.
  2. Data Aggregation:
    • It groups the filtered data by keyword, match type, account, campaign, and group, aggregating metrics like publisher cost, revenue, impressions, and impression opportunities.
  3. Metric Calculation:
    • The script calculates ROAS by dividing revenue by publisher cost and computes impression share by dividing impressions by impression opportunities.
  4. Keyword Headroom Adjustment:
    • It calculates the keyword headroom adjustment based on ROAS, rounding the result and setting negative or zero adjustments to zero.
  5. Filtering for Output:
    • The script filters the aggregated data to exclude groups with an impression share of 95% or higher, focusing on keywords with potential for performance improvement.

Vitals

  • Script ID : 391
  • Client ID / Customer ID: 248091937 / 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 16:33
  • 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

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