Script 35: Auto Create Suggested Keywords

Script 35: Auto Create Suggested Keywords

Purpose

Automatically create Keywords from Keyword Expansion report.

To Elaborate

This Python script automates the process of creating suggested keywords from a keyword expansion report. It applies certain rules to filter and format the keywords, such as limiting them to long keywords with 6 or more tokens, setting the initial bid to the projected average cost per click (CPC), adding them as ‘Exact’ match, and setting them to ‘Active’ status.

Walking Through the Code

  1. The script starts by defining column constants for various attributes related to the keyword data.
  2. It creates a copy of the input dataframe and renames the ‘Search Query’ column to ‘Keyword’ for the output dataframe.
  3. It adds two new columns, ‘Match Type’ and ‘Status’, to the output dataframe and sets the ‘Status’ column to ‘Active’.
  4. The script simulates the missing cost and clicks data by calculating them based on other available attributes.
  5. The simulated cost and clicks data are added to the output dataframe for preview debugging.
  6. The script calculates the initial keyword bid by dividing the simulated cost by the simulated clicks and rounds it to 2 decimal places.
  7. It sets the ‘Match Type’ column based on the account name in the input dataframe. For Google accounts, it sets it to ‘Exact’, for Walmart accounts, it sets it to ‘Broad’, and for Amazon accounts, it sets it to ‘Phrase’.
  8. The output dataframe is filtered to include only long keywords with 6 or more tokens based on the ‘Tokens’ column in the input dataframe.

Vitals

  • Script ID : 35
  • Client ID / Customer ID: 309909744 / 14196
  • 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: Michael Huang (mhuang@marinsoftware.com)
  • Created by Michael Huang on 2023-03-28 01:49
  • Last Updated by Michael Huang 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
60

# 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 = 'Cost $'
RPT_COL_CONV = 'Conv.'
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'

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

outputDf[BULK_COL_MATCH_TYPE] = np.nan
outputDf[BULK_COL_STATUS] = 'Active'

# Demo Account Keyword Expansion lack Cost/Clicks to compute CPC. Simulate it.
inputDf[RPT_COL_IMPR] = round(inputDf[RPT_COL_CLICKS] / 0.035, 0)
inputDf[RPT_COL_CLICKS] = round(inputDf[RPT_COL_CHARACTER_LENGTH] * 0.7, 0)
inputDf[RPT_COL_COST] = round(inputDf[RPT_COL_TOKENS] * 7.7, 2)


# 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
outputDf[BULK_COL_SEARCH_BID] = round(inputDf[RPT_COL_COST] / inputDf[RPT_COL_CLICKS], 2)

# 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'


# Limit to long keywords with tokens >= 6
outputDf = outputDf.loc[inputDf[RPT_COL_TOKENS] >= 6]

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

comments powered by Disqus