Purpose

The script qualifies suggested keywords based on conversion rates, cost-per-acquisition (CPA) thresholds, and token count limits.

To Elaborate

The Python script is designed to filter and qualify suggested keywords for advertising campaigns by applying specific criteria related to conversions, CPA, and the number of tokens in the keywords. It processes input data to identify keywords that have achieved a minimum number of conversions within a set period, have a CPA below a specified threshold, and contain fewer than a certain number of tokens. The script then prepares these qualified keywords for further use by setting their match type to “Exact” and assigning an initial bid based on the average cost-per-click (CPC). This automated process helps streamline the selection of effective keywords for advertising, ensuring that only those meeting the defined performance and cost criteria are considered.

Walking Through the Code

1. Initialization and Setup
   • The script begins by defining a dictionary for match types and setting up the primary data source, which includes various columns related to search queries and campaign details.
   • Customizable parameters are defined, such as the default bid amount, minimum conversions, maximum CPA, and maximum tokens, allowing users to adjust these thresholds as needed.
2. CPA Calculation and Criteria Definition
   • The script calculates the CPA for each keyword, handling cases where conversions are zero to avoid division errors.
   • It then defines criteria for keyword selection: keywords must have more than a specified number of conversions, a CPA below the threshold, and fewer tokens than the maximum allowed.
3. Filtering and Output Preparation
   • Keywords meeting all criteria are filtered from the input data and copied to the output data frame, with columns renamed appropriately.
   • The match type for all selected keywords is set to “Exact,” and their status is marked as “Active.”
   • The initial bid for each keyword is calculated based on the average CPC, with a fallback to the default bid if necessary.
4. Final Output
   • The script concludes by printing the shape of the output data frame and a sample of the output, providing a quick overview of the qualified keywords.

Vitals

• Script ID : 1499
• Client ID / Customer ID: 1306928469 / 60270543
• Action Type: Bulk Upload (Preview)
• Item Changed: Keyword
• Output Columns: Account, Campaign, Group, Keyword, Match Type, Status, Search Bid
• Linked Datasource: M1 Report
• Reference Datasource: None
• Owner: Michael Huang (mhuang@marinsoftware.com)
• Created by Michael Huang on 2024-11-08 12:01
• Last Updated by Michael Huang on 2024-11-08 12:15

> 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
78
##
## name: Auto Create Suggested Keywords with CPA Criteria
## description:
##  Qualify suggested keywords by Conversion, CPA threshold, and token count thresholds
## 
## author: Michael S Huang
## created: 2024-11-08
## 

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_SEARCH_QUERY = 'Search Query'
RPT_COL_SEED_KEYWORD = 'Seed Keyword'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_GROUP = 'Group'
RPT_COL_STATUS = 'Status'
RPT_COL_SOURCE = 'Source'
RPT_COL_IMPR = 'Impr.'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_COST = 'Cost $'
RPT_COL_CONV = 'Conv.'
RPT_COL_CHARACTER_LENGTH = 'Character Length'
RPT_COL_GENERATED_DATE = 'Generated Date'
RPT_COL_TOKENS = 'Tokens'
COL_CPA = 'CPA'

# 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_STATUS = 'Status'
BULK_COL_SEARCH_BID = 'Search Bid'
outputDf[BULK_COL_STATUS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_SEARCH_BID] = "<<YOUR VALUE>>"

###### Custom Parameters ######
VAL_DEFAULT_BID = 2.0
VAL_MIN_CONV = 5
VAL_MAX_CPA = 20
VAL_MAX_TOKENS = 5
###############################

# calc CPA with handling division by zero
inputDf[COL_CPA] = np.where(inputDf[RPT_COL_CONV] > 0, inputDf[RPT_COL_COST] / inputDf[RPT_COL_CONV], np.inf)

## criterias
# more than 5 conversions in 7 days
has_enough_conversions = inputDf[RPT_COL_CONV] > VAL_MIN_CONV
# CPA < $20
has_low_cpa = inputDf[COL_CPA] < VAL_MAX_CPA
# keywords with less than 5 tokens
is_short_keyword = inputDf[RPT_COL_TOKENS] < VAL_MAX_TOKENS

## build output based on criterias
outputDf = inputDf.loc[has_enough_conversions & has_low_cpa & is_short_keyword] \
                  .copy() \
                  .rename(columns={RPT_COL_SEARCH_QUERY:BULK_COL_KEYWORD})

# default to Exact match
outputDf[BULK_COL_MATCH_TYPE] = MATCH_TYPE['EXACT']
outputDf[BULK_COL_STATUS] = 'Active'

# set initial keyword bid as average CPC with handling division by zero
outputDf[BULK_COL_SEARCH_BID] = np.where(outputDf[RPT_COL_CLICKS] > 0, round(outputDf[RPT_COL_COST] / outputDf[RPT_COL_CLICKS], 2), VAL_DEFAULT_BID)

print(f"output shape: {outputDf.shape}")
print("sample output", tableize(outputDf))

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