By combining Google Analytics data with the powerful VLOOKUP and PivotTable functions offered by Microsoft Excel, marketing strategists can take their understanding of their Google Analytics data to the next level. In this article, we'll discuss how you can get started using VLOOKUP and PivotTable to summarize and analyze your Google Analytics data.
Once you've successfully imported your data into Excel, you can analyze it using VLOOKUP.
The term VLOOKUP is derived from "vertical lookup." In its most basic sense, an Excel formula that includes VLOOKUP will search the left-most column in a data set for a particular number or text string, and then return a value from another column based on where the search term is found.
A basic VLOOKUP formula would look like this:
- "lookup_value" refers to the term you wish to search for
- "table_array" refers to where the formula should search
- "col_index_num" refers to the number of the column the formula will pull its output value from
- "range_lookup" is an optional term that determines whether the VLOOKUP formula will search for an exact match or an approximate match
As a simple example, assume you wanted to compare your website's keyword data with overall Google keyword search volume. By adding a column with VLOOKUP formulas to the table with the overall Google search data, you could easily pull in the value from your own keyword data set that matches each keyword in the Google list.
Using the formula "=VLOOKUP((A11),Keyword,2,False)", you could automatically search the site visits data set to pull out the amount of site visits driven by the keyword "excel", which would allow you to compare the amount of site visits with the overall Google search volume. Drag the formula down the column to fill in the formula for the rest of the search terms in the table. A VLOOKUP formula like this could allow you to compare very large sets of keywords with their overall search volumes, showing you where opportunity for greater optimization exists.
PivotTables offers the ability to "pivot" your data between multiple metrics, allowing you to see and compare the data points that matter the most to you.
To create a new PivotTable, just click inside the formatted data you've imported into Excel, and then select Insert > Tables > Pivot Table (or Tables > Tools > Summarize with Pivot Table for Mac users).
The PivotTable wizard should automatically find the boundaries of your data set, and you'll be able to indicate where the new PivotTable should be placed.
After you've placed the new PivotTable, the PivotTable Field List will appear to the right of your screen. The list will include all of the fields found in your table—that is, all of the metrics included in your Google Analytics data set.
To start analyzing your data, select the fields from the list that you wish to analyze, and then drag them into the appropriate fields at the bottom of the PivotTable Field List, until you've found the data comparison you were looking for.
For example, you might use a PivotTable to compare different URLs within your website. To do this, you would drag the URL field from the list at the top to the Row Labels field. You could then add the values you wanted to analyze for each URL to the Values field. Once you have your initial PivotTable set up, you can add or remove Value fields to change what data you're comparing, add a Report filter, or group your different URLs together.
This article is intended to give you a very basic introduction to VLOOKUP and PivotTable, and how they can be used to better assess Google Analytics data. Now that you know how to get started, try playing around with your data in Excel to learn more about the opportunities offered by these two powerful tools.
Author Bio: Jeff Shjarback, MBA is a Digital Marketing Strategist, Writer and Blogger that enjoys blogging about online marketing, small business, lead generation, economics, innovation & emerging technology, future trend analysis and business philosophy. To learn more about Jeff, you can visit his Google Author Profile.