How to use Search Engine Optimization keywords’data in Adwords

  1. Export all of your Adwords keywords
  2. Export 1000 of the Analytics keywords
  3. Create 3 columns in Excel, in the following way:

 

ADWORDS ANALYTICS OVERLAP/MISSING
Keyword 1 Keyword 3
Keyword 2 Keyword 8
Keyword 3 Keyword 2
Keyword 4 Keyword 10

 

In overlap column, create a VLOOKUP formula using the following values:

 

 

Screen Shot 2016-06-22 at 11.34.46

It should look like this in the column: 

 

ADWORDS ANALYTICS OVERLAP/MISSING
Keyword 1 Keyword 3 =VLOOKUP(B2;A2:A1131;1;FALSE)
Keyword 2 Keyword 8
Keyword 3 Keyword 2
Keyword 4 Keyword 10

 

Drag the formula across the column and you press “enter”. The results will look like this: 

 

ADWORDS ANALYTICS OVERLAP/MISSING
Keyword 1 Keyword 3 Keyword 3
Keyword 2 Keyword 8 #N/A
Keyword 3 Keyword 2 Keyword 2
Keyword 4 Keyword 10 #N/A

 

Once you identify the missing Analytics keywords that you could possibly use in Adwords, you will also need the monthly sessions (taken from GA) that they generate so you could understand which of the missing keywords is worth for you to integrate in your Adwords campaigns.

  1. Sort the column with Overlap/Missing keywords and copy only the keywords from the Analytics column that correspond to the missing values.
  2. Paste them in another sheet in the third column. In the first two columns, you need to have the keywords and their sessions as downloaded from Analytics.
  3. Download the keyword report once again from Analytics with the keywords and their monthly generated sessions.
  4. The columns should look like that: Analytics keywords | Sessions | Missing keywords.
  5. Execute VLOOKUP formula in the exact same way. This time we want to return the values from the sessions, not the words themselves. So instead of using “1” in the formula, we use “2”, i.e. second column values.

 

ANALYTICS KEYWORDS SESSIONS MISSING KEYWORDS
Keyword 1 Keyword 3 =VLOOKUP(C2;$A$2:$B$1000;2;FALSE)
Keyword 2 Keyword 8
Keyword 3 Keyword 2
Keyword 4 Keyword 10

Once we have the missing keywords, the third column will look like that:

*#N/A means that the missing analytics keywords in Adwords

 

ANALYTICS KEYWORDS SESSIONS MISSING KEYWORDS
Keyword 1 Keyword 3 102
Keyword 2 Keyword 8 101
Keyword 3 Keyword 2 89
Keyword 4 Keyword 10 #N/A

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s