Purpose:

The script automates the creation of suggested keywords from a keyword expansion report, focusing on long keywords with six or more tokens and setting them with specific attributes for different platforms.

To Elaborate

The Python script is designed to automate the process of generating and managing suggested keywords from a keyword expansion report. It specifically targets long keywords that consist of six or more tokens. The script assigns an initial bid based on the projected average cost-per-click (CPC) and categorizes the keywords with an ‘Exact’ match type and an ‘Active’ status. The script runs on a weekly basis and is tailored to handle keywords for different advertising platforms, such as Google, Walmart, and Amazon, by setting the appropriate match type for each platform. This automation helps streamline the keyword management process, ensuring that only relevant and potentially high-performing keywords are added to campaigns.

Walking Through the Code

1. Data Preparation:
   • The script begins by copying the input DataFrame and renaming the ‘Search Query’ column to ‘Keyword’ for output purposes.
   • It initializes the ‘Match Type’ column with NaN and sets the ‘Status’ column to ‘Active’.
2. Simulating Data for CPC Calculation:
   • Since the demo account lacks cost and click data, the script simulates impressions, clicks, and cost using predefined formulas to enable CPC calculation.
3. Setting Initial Keyword Bid:
   • The initial bid for each keyword is calculated as the projected CPC, which is the cost divided by the number of clicks.
4. Assigning Match Types:
   • The script assigns ‘Exact’ match type for Google accounts, ‘Broad’ for Walmart, and ‘Phrase’ for Amazon, based on the account name.
5. Filtering Keywords:
   • Finally, the script filters the keywords to include only those with six or more tokens, ensuring that only long keywords are processed.

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