Deduplicate Your Google Analytics Reports in Excel

One of the steps in my last post on finding 404 pages worth saving involved determining if any of your404 pages received traffic in the past year. This can be accomplished by pulling a landing page report and using VLOOKUPs in Excel to see any of your broken pages used to receive traffic. However, Google Analytics (GA) content reports are case sensitive, so you might have multiple versions of what should be a single line item because of this.

For example, /default.aspx and /Default.aspx are considered two different pages in Google Analytics. If you’re used to exporting your content or landing page reports without taking this extra step, you’re probably reporting inaccurate data.

Deduplicating With Pivot Tables

A simple solution to this would be to create a pivot table. This will automatically merge your data — and isn’t case sensitive.

pivot table in Excel

However, that won’t help you in a situation where you need to do VLOOKUPs to stitch your data together; so I’m going to tell you about a cool tool in Excel that few seem to know about but is very useful. It’s the Consolidate command, and you can find it under the Data tab.

Common Use For Consolidate Command

The most common use of the Consolidate command is to merge datasets from different worksheets or even workbooks (i.e., completely different files). So let’s say you have a reporting dashboard that has a separate worksheet for each month of data. You could use the Consolidate command to combine all of the monthly datasets into one aggregated dataset. You can even create links so that the consolidated dataset is updated whenever its individual elements are.

However, it also very useful in deduplicating datasets without losing any of the data. If you just have a list of, let’s say, keywords you’re bidding on, and you want to deduplicate that list, you’d use the Remove Duplicates command. This command is also found under the Data tab. But if that list also includes traffic and revenue metrics, you do not want to merely dedupe the list; you want to merge those values. See the difference?

Consolidate Landing Page Reports

What I’m going to use in this demonstration is a list of landing pages from Google Analytics that includes visit and revenue data. You can see below that we have two instances of duplicated data. The second landing-page01 was actually the result of removing all query parameters from URLs. (Then I, of course, anonymized the data.) If your data is littered with unnecessary query parameters (the values that follow a question mark in a URL), you can use Text to Columns to remove everything after the “?” symbol. (However, you should really be filtering out any query parameters you don’t need to report on in your profile settings.)

duplicate data

Step 1: Copy your column headings over to where your new, deduplicated dataset will ultimately live. Then put your mouse cursor where the dataset will begin.

Consolidate command in Excel

Step 2: Choose Data > Data Tools > Consolidate, which will bring up the Consolidate menu.

Consolidate command in Excel

Step 3: Since we’re going to want to add the visits and revenues for the duplicate rows, we’ll choose Sum from the Function drop-down menu.

Step 4: For the Reference, click-and-drag over your dataset. Or, if you’re working with a formatted table (which you should always be), just select any cell inside your dataset and press Ctrl-A (Mac: Command-A) to select all of the cells. Lastly, you could use a named range and reference that.

Step 5: Since we’re only dealing with one dataset, we won’t need to add more references. But if you were merging multiple datasets, you would select the Add button to add them.

Step 6: Since the values we’re using to deduplicate the list with are in the left column, we’re going to use that option under “Use labels in.” Here’s what the dialog should look like when you’re finished. Press OK to let Excel do its magic.

Consolidate command in Excel

Step 7: And voila! Your consolidated data.

Consolidate command in Excel

I use this command most frequently to deduplicate content reports in GA. If your URLs don’t automatically redirect to lowercase, or you don’t have a filter in place in GA to force URLs to lowercase, it’s really important that you take this extra step to make sure you’re presenting accurate data.

Opinions expressed in the article are those of the guest author and not necessarily Search Engine Land.

Related Topics: Channel: Analytics | How To | How To: Analytics | Search & Analytics

Sponsored


About The Author: is an SEO and analytics consultant. Her areas of expertise are analytics, technical SEO, and everything to do with data — collection, analysis, and beautification. She’s on a mission to rid the world of ugly data, one spreadsheet at a time. If you just can’t get enough data visualization tips, you can check out her blog, Annielytics.com.

Connect with the author via: Email | Twitter | Google+ | LinkedIn



SearchCap:

Get all the top search stories emailed daily!  

Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • Guest

    Hi, Annie ! Thank you once again for this excellent effort to guide SEO experts with the great tool in excel.

 

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!

 


 

Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide