Script 901: update keywords cost conv dimension

Purpose

Python script to update the “Keyword Cost/Conv Performance” dimension for keywords that have a cost/conv 30% greater than their Campaign’s cost/conversion.

To Elaborate

The Python script aims to update the “Keyword Cost/Conv Performance” dimension for keywords that are underperforming. Keywords are considered underperforming if their cost/conv is 30% greater than their Campaign’s cost/conversion. The campaign cost/conversion is saved at the keyword level using the “Campaign 30 Day Cost Per Conversion” dimension value.

Walking Through the Code

  1. The script defines a dictionary MATCH_TYPE that maps different match types to their corresponding values.
  2. The script initializes the today variable with the current date and time in the client’s timezone.
  3. The script defines various column constants for the input and output data.
  4. The script creates a copy of the input data and selects only the relevant columns for further processing.
  5. The script defines a function calculate_keyword_performance that takes a row of data as input and calculates the keyword’s performance based on the cost/conv and campaign’s cost/conversion.
  6. The script applies the calculate_keyword_performance function to each row of the input data using the apply method, and stores the results in the “Keyword Cost/Conv Performance” column of the output data.

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-05-15 07:44:05 GMT

comments powered by Disqus