Script 35: Auto Create Suggested Keywords

Script 35: Auto Create Suggested Keywords

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 match types and active status.

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, ensuring that these keywords are added with an ‘Exact’ match type for Google accounts, ‘Broad’ for Walmart, and ‘Phrase’ for Amazon. The script also sets the initial bid for these keywords based on the projected average cost-per-click (CPC) and assigns them an active status. This process is intended to run on a weekly basis, streamlining the management of keyword recommendations and optimizing them for different advertising platforms.

Walking Through the Code

  1. Initialization and Setup
    • The script begins by copying the input DataFrame and renaming columns to match the required output format.
    • It initializes the match type and status columns, setting the status to ‘Active’.
  2. Simulating Data for CPC Calculation
    • Since the demo account lacks cost and click data, the script simulates impressions, clicks, and cost to compute the CPC.
  3. Setting Initial Keyword Bid
    • The initial bid for each keyword is calculated as the projected CPC, derived from the simulated cost and clicks.
  4. Assigning Match Types Based on Account
    • The script assigns ‘Exact’ match type for Google accounts, ‘Broad’ for Walmart, and ‘Phrase’ for Amazon, based on the account name.
  5. Filtering Long Keywords
    • Finally, the script filters the DataFrame to include only those keywords 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 2024-11-27 06:58:46 GMT

comments powered by Disqus