Script 499: keyword harvest 2

Purpose

Python script to auto-push a subset of suggested keywords from a keyword recommendations grid.

To Elaborate

The Python script solves the problem of automatically pushing a subset of suggested keywords from a keyword recommendations grid. The script applies certain rules to filter and format the keywords before pushing them. The key business rules are as follows:

  • Only long keywords with 6 or more tokens are considered.
  • The initial bid for each keyword is set to the projected average cost per click (CPC).
  • The keywords are added as ‘Exact’ match type.
  • The keywords are added with an ‘Active’ status.

Walking Through the Code

  1. The script starts by defining column constants for various fields in the input and output dataframes.
  2. The input dataframe is filtered to include only keywords with a minimum number of conversions (MIN_CONVERSIONS).
  3. The output dataframe is created as a copy of the input dataframe, with the ‘Search Query’ column renamed to ‘Keyword’.
  4. The ‘Match Type’ and ‘Status’ columns are added to the output dataframe, with ‘Exact’ and ‘Active’ values respectively.
  5. The ‘Impr.’, ‘Clicks’, and ‘Pub. Cost $’ columns in the output dataframe are set to the corresponding values from the input dataframe for preview debugging.
  6. The initial keyword bid is calculated as the ratio of ‘Pub. Cost $’ to ‘Clicks’, rounded to 2 decimal places. It is capped at a maximum value of 6.5.
  7. The ‘Match Type’ column in the output dataframe is updated based on the account name in the input dataframe. For Google accounts, it is set to ‘Exact’. For Walmart accounts, it is set to ‘Broad’. For Amazon accounts, it is set to ‘Phrase’.
  8. The output dataframe is sorted in descending order based on the ‘Conversions’ column.

Vitals

  • Script ID : 499
  • Client ID / Customer ID: 1306924405 / 60269313
  • Action Type: Bulk Upload (Preview)
  • Item Changed: Keyword
  • Output Columns: Account, Campaign, Group, Keyword, Match Type, Search Bid, Status
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: austin@pittedlabs.com (austin@pittedlabs.com)
  • Created by austin@pittedlabs.com on 2023-11-04 23:59
  • Last Updated by austin@pittedlabs.com 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

# Simple script to auto-push subset of Suggested Keywords from Keyword Recommendations Grid
#
# Rules:
#  - limited to long keywords with 6 or more tokens
#  - set initial bid to projected Avg CPC
#  - add as 'Exact' match
#  - add with ACTIVE status

RPT_COL_KEYWORD = '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 = 'Pub. Cost $'
RPT_COL_CONV = 'Conversions'
RPT_COL_CHARACTER_LENGTH = 'Character Length'
RPT_COL_GENERATED_DATE = 'Generated Date'
#RPT_COL_TOKENS = 'Tokens'
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'

# Limit to keywords with MIN_CONVERSIONS
inputDf = inputDf[inputDf[RPT_COL_CONV].astype('int') >= 2].copy()

outputDf = inputDf.copy().rename(columns={RPT_COL_KEYWORD:BULK_COL_KEYWORD})

outputDf[BULK_COL_MATCH_TYPE] = 'Exact'
outputDf[BULK_COL_STATUS] = 'Active'

# set it on Output for Preview Debugging
outputDf[RPT_COL_IMPR] = inputDf[RPT_COL_IMPR]
outputDf[RPT_COL_CLICKS] = inputDf[RPT_COL_CLICKS]
outputDf[RPT_COL_COST] = inputDf[RPT_COL_COST]

# set initial keyword bid as projected CPC, capped at 6.5
MAX_BID = 6.5
outputDf[BULK_COL_SEARCH_BID] = round(inputDf[RPT_COL_COST] / inputDf[RPT_COL_CLICKS], 2)
outputDf[BULK_COL_SEARCH_BID].loc[outputDf[BULK_COL_SEARCH_BID] > MAX_BID] = MAX_BID


# for Google accounts, set match type to Exact
outputDf.loc[inputDf[RPT_COL_ACCOUNT].str.contains("Google", case=False, regex=False), BULK_COL_MATCH_TYPE] = 'Exact'
# for Walmart, set to Broad
outputDf.loc[inputDf[RPT_COL_ACCOUNT].str.contains("Walmart", case=False, regex=False), BULK_COL_MATCH_TYPE] = 'Broad'
# for Amazon, set to Phrase
outputDf.loc[inputDf[RPT_COL_ACCOUNT].str.contains("Amazon", case=False, regex=False), BULK_COL_MATCH_TYPE] = 'Phrase'


outputDf.sort_values(RPT_COL_CONV, ascending=False, inplace=True)

Post generated on 2024-05-15 07:44:05 GMT

comments powered by Disqus