The Internet Marketing Driver

  • GSQi Home
  • About Glenn Gabe
  • SEO Services
    • Algorithm Update Recovery
    • Technical SEO Audits
    • Website Redesigns and Site Migrations
    • SEO Training
  • Blog
  • Contact GSQi

How To Bulk Export Top Landing Pages From Google Analytics Using Analytics Edge in Excel

March 18, 2016 By Glenn Gabe 10 Comments

Share
Tweet
Share
Email
23 Shares

Using Analytics Edge For Bulk Exports

I help a lot of companies with website redesigns or CMS migrations (from an SEO standpoint). As part of that process, it’s extremely important to download all of a website’s top landing pages so you can check them in staging and when the redesign or migration goes live. By doing so, you can test the pages to ensure they resolve correctly before you pull the trigger, and then right after you launch. Basically, you can nip serious SEO problems in the bud by crawling those urls.

I also help a lot of large-scale websites with redesigns or migrations. For example, sites with 500K, 1M, or more pages that need to be downloaded, crawled, and analyzed. For situations like that, it’s not simple to export the top landing pages from Google Analytics. Unfortunately, you cannot export urls in bulk from the UI. You are limited to 5K urls per report. Then you would need to click to the next page in the pagination to retrieve the next 5K rows. That’s not efficient, to say the least.

You could also tap into the GA API, but you need programming skills. That’s unless you leverage a tool or plugin that has the functionality built for you. More on that soon. :)

The GA rowCount Hack is Gone
There used to be a hack in Google Analytics that let you manually set the row count in your reports via the url in your browser. I loved that feature, since it enabled me to get around the issue I mentioned above. For example, I could add rowCount%3D15000 in the url, and boom, I had a report with 15K rows of data.

Well, Google Analytics removed the ability to do that and now the most you can get in one batch is five 5K rows. Sure, you can click to the second page of results and keep exporting, but that gets old quickly. Exporting up to 5K pages is fine for some smaller sites, but not if you have hundreds of thousands of pages, or more.

So, wouldn’t it be great if there was some plugin or add-in that could help with this? Well, there is. Read on!

Analytics Edge for Excel
While researching options last year, I came across an Excel add-in called Analytics Edge. The add-in is packed with functionality, including the ability to export pages from Google Analytics with ease. And not just export pages, but export them in bulk! That’s exactly what I was looking for.

Again, the plugin can do many things for you, but I’ll just focus on exporting top landing pages from Google Analytics. If you are going through a redesign or migration, and want to export all of the top landing pages from organic search, then this could be a great solution for you. It has worked extremely well for me.

Note, there is a free version of the add-in that contains the Google Analytics connector. You can also pay more for the “Core” add-in, which gives you even more functionality. The screenshots below are from the core add-in, so you might see slightly different options with the free version. Now let’s get started.

How To Export Top Landing Pages From Google Analytics (Beyond 5K)

1. Download and install the Analytics Edge add-in for Excel. Then register the add-in to get started.

Registering Analytics Edge in Excel

2. Click the Analytics Edge menu at the top of the worksheet to access the various options available to you.

Analytics Edge Ribbon in Excel

3. Click “Free Google Analytics” in the menu and then “Accounts”. You will need to connect the add-in to your Google account so you can access various properties and views in GA. You should enter the account that’s tied to your Google Analytics properties and views. Note, you can add several Google accounts for future access, if needed.

Entering An Account In Analytics Edge

4. Next, click “Free Google Analytics” and then “Analytics Reporting”. A small macro box will show up, which will capture the commands you are using via the add-in. Simply name your macro to move on.

5. Here’s where the magic takes place. This is where you will choose the settings for your new report. First, select a login and then a view. The options will match the Google account(s) you entered previously.

Select GA View in Analytics Edge

6. Next, choose the dimensions and metrics you want to see in your report. For this example, I’ll keep it simple. We’ll choose landing pages, sessions, and bounce rate.

Selecting Dimensions and Metrics in Analytics Edge

7. Instead of clicking finish, let’s select the Dates tab. There are some presets here like “Last 30 Days”, but you can enter a custom date as well. I’ll choose “Last 30 Days” to keep this simple.

Selecting a Date Range in Analytics Edge

8. If you click the “Sort” tab, you can sort by sessions (which is typically what we would want for top landing pages).

Sorting Data in Analytics Edge

9. Last, but not least, we want organic search data for this example. So head over to the “Segments” tab and select “Organic Traffic” in the list. This will limit the data to landing pages from organic search.

Choosing a Segment in Analytics Edge

10. Then click Finish. Analytics Edge will begin pulling the data from Google Analytics. Note, for larger sites, this could take a while. Be patient. You’ll notice the plugin says, “Running…” and you might see the Query Status window showing the number of rows downloaded.

Analytics Edge Query Status

11. Once completed, you’ll see a “Step Results” worksheet filled with data. You’ll also notice there are only 50 records in the sheet. This is where most people freak out and think the plugin screwed up. It didn’t. It’s simply showing you a sample of the data that was pulled. If all looks ok, then you need to write the data to a new worksheet.

Step Results in Analytics Edge

12. To write the data to a new worksheet, you need to click “File” in the Analytics Edge menu, and then “Write Worksheet”. You can name your new worksheet whatever you like and then click OK. Once you do, you’ll notice a new worksheet with all of your data. For the view I used, I retrieved 98K top landing pages from organic search over the past 30 days. Awesome.

Write Worksheet in Analytics Edge

Bonus:
The only thing left to do here is to use the concatenate function to build the full url. Once you do, you can export that list and crawl it via Screaming Frog, Deep Crawl, etc. You can read more about checking top landing pages in a recent post of mine. There are some helpful tips in that post once you have a final set of urls.

Finalizing Landing Pages with Concatenation in Excel

Summary – Breaking Through The 5K Limit
For those of you that are working on large-scale sites, the process I documented above can help you export many more urls than you could via the UI in Google Analytics. By using an Excel add-in like Analytics Edge, you can export hundreds of thousands of urls, if needed (and all in one shot.) That will save you time, and your sanity.

So gather your intel now. Then crawl away. It’s a great way to check urls in bulk when going through a redesign or CMS migration. Good luck.

GG

 

Share
Tweet
Share
Email
23 Shares

Filed Under: google, google-analytics, seo, tools, web-analytics

Connect with Glenn Gabe today!

Latest Blog Posts

  • Google Search Console (GSC) reporting for Soft 404s is now more accurate. But where did those Soft 404s go?
  • Google’s December 2020 Broad Core Algorithm Update Part 2: Three Case Studies That Underscore The Complexity and Nuance of Broad Core Updates
  • Google’s December 2020 Broad Core Algorithm Update: Analysis, Observations, Tremors and Reversals, and More Key Points for Site Owners [Part 1 of 2]
  • Exit The Black Hole Of Web Story Tracking – How To Track User Progress In Web Stories Via Event Tracking In Google Analytics
  • Image Packs in Google Web Search – A reason you might be seeing high impressions and rankings in GSC but insanely low click-through rate (CTR)
  • Google’s “Found on the Web” Mobile SERP Feature – A Knowledge Graph and Carousel Frankenstein That’s Hard To Ignore
  • Image Migrations and Lost Signals – How long before images lose signals after a flawed url migration?
  • Web Stories Powered by AMP – 12 Tips and Recommendations For Creating Your First Story
  • Visualizing The SEO Engagement Trap – How To Use Behavior Flow In Google Analytics To View User Frustration [Case Study]
  • The May 2020 Google Core Update – 4 Case Studies That Emphasize The Complexity Of Broad Core Algorithm Updates

Web Stories

  • Google’s Disqus Indexing Bug
  • Google’s New Page Experience Signal

Archives

  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • September 2019
  • August 2019
  • July 2019
  • June 2019
  • May 2019
  • April 2019
  • March 2019
  • February 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • August 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • January 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015
  • January 2015
  • December 2014
  • November 2014
  • October 2014
  • September 2014
  • August 2014
  • July 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • January 2014
  • December 2013
  • November 2013
  • October 2013
  • September 2013
  • August 2013
  • July 2013
  • June 2013
  • May 2013
  • April 2013
  • March 2013
  • February 2013
  • January 2013
  • December 2012
  • November 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • June 2012
  • May 2012
  • April 2012
  • March 2012
  • GSQi Home
  • About Glenn Gabe
  • SEO Services
  • Blog
  • Contact GSQi
Copyright © 2021 G-Squared Interactive LLC. All Rights Reserved. | Privacy Policy

We are using cookies to give you the best experience on our website.

You can find out more about which cookies we are using or switch them off in settings.

The Internet Marketing Driver
Powered by  GDPR Cookie Compliance
Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

Strictly Necessary Cookies

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.

3rd Party Cookies

This website uses Google Analytics to collect anonymous information such as the number of visitors to the site, and the most popular pages.

Keeping this cookie enabled helps us to improve our website.

This site also uses pixels from Facebook, Twitter, and LinkedIn so we publish content that reaches you on those social networks.

Please enable Strictly Necessary Cookies first so that we can save your preferences!