Purpose:

The script qualifies suggested keywords based on conversion rates, CPA thresholds, and token count limits for effective ad targeting.

To Elaborate

The Python script is designed to filter and qualify suggested keywords for advertising campaigns by applying specific criteria related to conversions, cost per acquisition (CPA), and the number of tokens in the keywords. The goal is to identify keywords that are likely to perform well in terms of conversions while maintaining a cost-effective CPA. The script processes input data to calculate CPA and applies thresholds to ensure that only keywords with sufficient conversions, acceptable CPA, and manageable token counts are selected. This helps in optimizing ad spend by focusing on keywords that meet predefined performance metrics.

Walking Through the Code

1. Initialization and Setup
   • The script begins by defining match types for keywords and setting up the primary data source.
   • It initializes constants for column names used in the input and output data frames.
2. Custom Parameters
   • User-changeable parameters are defined, including default bid value, minimum conversions, maximum CPA, and maximum token count. These parameters allow customization of the filtering criteria.
3. CPA Calculation
   • The script calculates the CPA for each keyword, handling cases where conversions are zero to avoid division errors.
4. Criteria Application
   • It applies three main criteria to filter keywords:
     • Keywords must have more than a specified number of conversions.
     • Keywords must have a CPA below a certain threshold.
     • Keywords must have fewer tokens than the specified maximum.
5. Output Construction
   • The script constructs the output data frame by selecting keywords that meet all criteria.
   • It sets the match type to ‘Exact’ and the status to ‘Active’ for qualified keywords.
   • The initial bid for each keyword is set based on the average cost per click, with a default bid used if no clicks are recorded.
6. Output Display
   • Finally, the script prints the shape of the output data frame and a sample of the output for verification.

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