Script 901: update keywords cost conv dimension

Purpose

The script updates the “Keyword Cost/Conv Performance” dimension for keywords whose cost per conversion is 30% greater than their campaign’s cost per conversion.

To Elaborate

The Python script is designed to evaluate the performance of keywords in advertising campaigns by comparing their cost per conversion to the campaign’s average cost per conversion over the past 30 days. If a keyword’s cost per conversion exceeds the campaign’s average by more than 30%, it is marked as “underperforming” in the “Keyword Cost/Conv Performance” dimension. This allows advertisers to quickly identify and address keywords that are not performing efficiently, potentially optimizing their advertising spend and improving overall campaign performance.

Walking Through the Code

  1. Initialization and Data Preparation
    • The script begins by defining a dictionary for match types and setting up the primary data source, inputDf, which contains various columns related to keyword performance metrics.
    • It initializes the output DataFrame, outputDf, with columns for account, campaign, group, keyword, match type, and a placeholder for the “Keyword Cost/Conv Performance” dimension.
  2. Performance Calculation
    • A function calculate_keyword_performance is defined to determine if a keyword is underperforming. It compares the keyword’s cost per conversion to 1.3 times the campaign’s 30-day average cost per conversion.
    • If the keyword’s cost per conversion exceeds this threshold, it returns “underperforming”; otherwise, it returns an empty string.
  3. Data Processing and Output
    • The script selects relevant columns from inputDf and creates a copy for processing.
    • It populates the outputDf with data from inputDf and applies the calculate_keyword_performance function to each row to update the “Keyword Cost/Conv Performance” dimension based on the calculated performance.

Vitals

  • Script ID : 901
  • Client ID / Customer ID: 1306913420 / 60268008
  • Action Type: Bulk Upload
  • Item Changed: Keyword
  • Output Columns: Account, Campaign, Group, Keyword, Match Type, Keyword Cost/Conv Performance
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Chris Jetton (cjetton@marinsoftware.com)
  • Created by Chris Jetton on 2024-04-03 17:12
  • Last Updated by Chris Jetton on 2024-04-03 18:10
> 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

##
## name: update keywords cost/conv dimension
## description:
##  this script will update the "Keyword Cost/Conv Performance" dimension for any keyword where the cost/conv is 30% greater than its Campaign's cost/conversion. The campaign cost/conversion is saved at the keyword level using the "Campaign 30 Day Cost Per Conversion" dimension value.
## 
## author: 
## created: 2024-04-03
## 

MATCH_TYPE = {
  'EXACT': 'exact',
  'PHRASE': 'phrase',
  'BROAD': 'broad',
}
today = datetime.datetime.now(CLIENT_TIMEZONE).date()

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_KEYWORD = 'Keyword'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_30_DAY_COST_PER_CONVERSION = 'Campaign 30 Day Cost per Conversion'
RPT_COL_STATUS = 'Status'
RPT_COL_SEARCH_BID = 'Search Bid'
RPT_COL_BID_OVERRIDE = 'Bid Override'
RPT_COL_OVERRIDE_UNTIL = 'Override Until'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_TEST = 'Test'
RPT_COL_IMPR = 'Impr.'
RPT_COL_IMPR_SHARE = 'Impr. share %'
RPT_COL_GROUP = 'Group'
RPT_COL_GROUP_STATUS = 'Group Status'
RPT_COL_UNIQUE_ID = 'Unique ID'
RPT_COL_HISTORICAL_CONVERSIONS = 'Historical Conversions'
RPT_COL_MATCH_TYPE = 'Match Type'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_CONV = 'Conv.'
RPT_COL_COST_PER_CONV = 'Cost/Conv. $'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_CAMPAIGN_TYPE = 'Campaign Type'
RPT_COL_CTR = 'CTR %'
RPT_COL_AVG_CPC = 'Avg. CPC $'
RPT_COL_CONV_RATE = 'Conv. Rate %'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_KEYWORD = 'Keyword'
BULK_COL_MATCH_TYPE = 'Match Type'
BULK_COL_KEYWORD_COST_PER_CONV_PERFORMANCE = 'Keyword Cost/Conv Performance'
outputDf[BULK_COL_KEYWORD_COST_PER_CONV_PERFORMANCE] = "<<YOUR VALUE>>"

# user code start here
def calculate_keyword_performance(row):
    cost_per_conv = row[RPT_COL_COST_PER_CONV]
    campaign_30_day_cost_per_conv = row[RPT_COL_CAMPAIGN_30_DAY_COST_PER_CONVERSION]
    
    if cost_per_conv > 1.3 * campaign_30_day_cost_per_conv:
        return "underperforming"
    else:
        return ""

cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, RPT_COL_KEYWORD,RPT_COL_MATCH_TYPE, RPT_COL_COST_PER_CONV, RPT_COL_CAMPAIGN_30_DAY_COST_PER_CONVERSION]
df = inputDf[cols].copy()
outputDf[BULK_COL_ACCOUNT] = inputDf[RPT_COL_ACCOUNT]
outputDf[BULK_COL_CAMPAIGN] = inputDf[RPT_COL_CAMPAIGN]
outputDf[BULK_COL_GROUP] = inputDf[RPT_COL_GROUP]
outputDf[BULK_COL_KEYWORD] = inputDf[RPT_COL_KEYWORD]
outputDf[BULK_COL_MATCH_TYPE] = inputDf[RPT_COL_MATCH_TYPE]
outputDf[BULK_COL_KEYWORD_COST_PER_CONV_PERFORMANCE] = df.apply(calculate_keyword_performance, axis=1)

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

comments powered by Disqus