Vlookup for SEO Example

VLOOKUP with Google Analytics SEO Example

Did this the other day, well Mark did…

 

So we were given a list of URLs to find the amount of pageviews for –

– Have the URLs you need to find pageviews for listed in an excel sheet, then…do the following:

1.  Export this report from Google Analytics (GA):

google analytics report

2. Show – as many rows as you need to, so that all the data is displayed in the table. Use the drop down menu at the bottom right of the table:
Capture

3. Export as CSV (use Export drop down near top of page)

4. Copy and paste the CSV data into the second tab of your original excel spreadsheet with your URLs on:

URLs
URLs

 

5. Use a vlookup, to match your original list of URLs, with the GA report:
Vlookup excel google analytics seo

when looking at the vlookup formula, remember that the tabs on my spreadsheet were called “URLs” and “Analytics All Web Site Data Pag”

Here is the breakdown of the VLookup syntax to help you to understand what is going on here:

Vlookup explained and tutorial
VLookup Syntax

 

It basically tells Excel to find the URL in the cell A1 on the first tab, in the table on the second tab, return a value from the second column of that table when the URL is matched.  The 0 just means make sure it’s an exact match

 

Remember, vlookup can only look left to right. so if pageview had been in the first column, and URLs in the first, we would have had to copied and pasted the table data around, or used INDEX & MATCH

 

– please note, my new awesome screenshot for chrome plugin stupped working after the first screenshot from GA, so I couldn’t do nice arrows and annotations.

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