How to Build Keywords and New Ads

Today we’re going to tackle one of the fundamental tasks in running AdWords accounts: building keywords and new ads. Our process covers how to do this in bulk, for campaigns that are already running. Let’s dive in!

Why?

INCREASE TRAFFIC. As JDM creates new keywords, client ads are shown to more potential customers. The goal is not to simply increase the volume of keywords but to increase the volume of relevant keywords. As the client’s keyword “portfolio” expands, ads will show up more accurately and more frequently for potential customers. 

TEST WHAT WORKS. Building new ads is important in order to be dynamic and respond to changing customer preferences, psyches, and engagement patterns. New ads are also important to test out what works and what doesn’t. This is why JDM conducts regular ad test analyses to measure performance, keep effective ads running, and pause ineffective ads. 

How?

1. Keyword Build

  • Download a search terms report

  • Flag potential alpha and beta keywords

  • Identify and build new keywords

2. Ad Build

  • Build prep

  • Write headline 1

  • Fill out required columns

  • Fill In Current Ads

  • Tracking Template and QA


Skills

1. Excel concatenate function

2. Excel text to column feature

3. Excel calculation functions

4. Create a pivot table in Excel

5. Conditional and number formatting in excel


End product:

Keyword Build

1. Download a search term report off Google Ads

Filters:

  • Dates: last 30 days up to yesterday

  • Impressions > 0

  • Campaign Name: does not contain “alpha”


Columns:

  • Impressions

  • Clicks

  • CTR

  • Cost

  • Avg. pos.

  • Conversions

  • Cost/Conv.

  • CVR

 

2. Flag potential alpha and beta keywords

INSERT 6 COLUMNS. Insert the following 6 columns to the left of the report: Alpha Clicks Flag, Alpha Conversions Flag, Alpha Compiled Flag, Beta Impressions Flag, Beta Click Flag, Beta Compiled Flag.

  • ALPHA CLICKS FLAG. Write “Flag” in this column for the terms that have Clicks > 4.

  • ALPHA CONVERSIONS FLAG. Write “Flag” in this column for the terms that have 2+ conversions and 3+ impressions.

  • ALPHA COMPILED FLAG. Concatenate the cells in the alpha clicks flag and alpha conversions flag columns. This formula should look like this: =B2&A2.


  • BETA IMPRESSIONS FLAG. Write “Flag” in this column for the terms that meet the following criteria: 10 impressions, or 200 rows (whichever comes first).

  • BETA CLICKS FLAG. Write “Flag” in this column for the terms that meet the following criteria: 3 clicks, or 200 rows (whichever comes first).

  • BETA COMPILED FLAG. Concatenate the cells in the beta impressions flag and beta clicks flag columns. This formula should look like this: =E2&D2.


3. Identify and Build New Keywords

 SNIFF TEST PREP. Filter out any terms that are “exact match”. Now, filter out blanks from the following and copy terms to a new tab:

  • Alpha Compiled Flag column (column C). This is the list of terms you will create alpha keywords for

  • Beta Compiled Flag column (column F). This is the list of terms you will create beta keywords for

THE BUILD. Add 2 columns next to the search terms column called “Keyword” and “Match Type”.

  • Label all search terms flagged alpha as “exact” and all terms flagged beta as “broad” under the Match Type column.

  • Under the “Keyword” column, if alpha use exact match ([search term]) and if beta use broad match modified (+search +term).

TIP: To create the broad match modified keyword use this formula: 

="+"&SUBSTITUTE(B2," "," +")


COMPARE TO EXISTING KEYWORDS. Make sure the keywords don’t already exist by downloading a keywords report and using the VLOOKUP function.

Sample formula: =VLOOKUP(C2,'KW Report'!B:B,1,FALSE)

SNIFF TEST. Remove duplicates and look at what is left. If anything is not relevant to the business, delete the row.


FORMATTING. Rename the Search Term column “Ad Group”. Then Insert a “campaign” column to the left of the sheet, and a “Max CPC” column all the way to the right.

  • For Campaign, use a VLOOKUP function to bring in the campaign name from the original search term report

Sample formula: =VLOOKUP(B2,'Search terms report (8)'!A:D,4,FALSE)

  • Under Match Type, replace all Alpha with “Exact” and all Beta with “Broad”

  • Under Max CPC, set $3 for Broad match types. For Exact match types, use the VLOOKUP function to bring in the Avg. CPC from the Search Terms report and multiply that value by 1.3.

Sample Formula: =1.3*VLOOKUP(B2,'Search terms report (8)'!A:J,9,FALSE)

COPY FOR EACH CITY. For accounts that have geos in the campaign: for all non-geo specific keywords, duplicate the keyword build for each geo. For example, if “how to motivate employees” is in the NYC campaign and the account also has Chicago, SF, and LA geos then make 3 copies of the row and replace NYC with each of the other geo locations.

You can find each client’s geos in their ad cheat sheet. That is saved in the client’s team drive as part of the spreadsheet called “[client name] | Ad Testing Doc”.


AD BUILD

  1. Build Prep

Copy existing ad copy from similar ads and paste in a new sheet. 

COLUMNS ON NEW TAB. Next, name columns: Ad ID, (then leave a blank column), Campaign, Ad Group, Headline 1, Headline 2, Description, Path 1, Path 2, LP ID, Final URL, Mobile URL, and Tracking Template.

FILL COLUMNS FROM KEYWORD BUILD. Copy the campaign and ad group from the keyword build into this sheet under those respective columns.

  1. Write headline 1

PROPER([Ad group]). Headline 1s are determined based on the ad group. Copy all ad group entries to a new tab and remove duplicates. Then, use the PROPER function to capitalize each word in the ad group phrases. 


Sample formula: =PROPER(A2)

CHARACTER COUNT. In a new column, use the LEN function to do a character count and make sure that none of the headlines are longer than 30 characters. 

Sample formula: =LEN(B2)

If a headline has more than 30 characters, change it so that it is shorter- try to retain as much of the original ad group as possible.

INSERT HEADLINE INTO AD BUILD. Once you’ve written all the headline 1s, use VLOOKUP to put them back into the ad build tab of your spreadsheet.

Sample formula: =VLOOKUP(D5,Sheet4!A:D,4,FALSE)



  1. Fill Out Required Columns


DETERMINE AD CATEGORY. Look at the campaigns in the sheet and determine which ad categories you will be working with. The category is generally listed just after (google-search) in the campaign name. For Blueboard, it will be either brand or nonbrand. For other clients, there are many more categories like cleaning, moving, etc. In this example, there are only nonbrand keywords. Locate these ads in the ad cheat sheet, these are the ones you will be working with.


  1. QA

QA. Check that the tracking template is correct by copy and pasting its values into a new column. Paste it once for every dynamic value that’s in it. If it is just campaign and ad group, copy the tracking template twice. 


Then, use excel’s replace tool to delete the static information and isolate the campaign in the first column and then ad group in the second column.

This is what the final isolated campaign and ad group looks like:


Then, use an equal function to check if the campaign that you just isolated truly matches the campaign in that row. Do the same for ad group. 

Sample formula: =P5=C5


Copy this formula down and make sure all the values return as TRUE. If anything returns as FALSE then you know there’s an issue to be resolved.


Once everything returns as true, you can delete these QA columns. 

Claire Forgan