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
    • Web Stories
  • 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

  • Continuous Scroll And The GSC Void: Did The Launch Of Continuous Scroll In Google’s Desktop Search Results Impact Impressions And Clicks? [Study]
  • How to analyze the impact of continuous scroll in Google’s desktop search results using Analytics Edge and the GSC API
  • Percent Human: A list of tools for detecting lower-quality AI content
  • True Destination – Demystifying the confusing, but often accurate, true destination url for redirects in Google Search Console’s coverage reporting
  • Google’s September 2022 Broad Core Product Reviews Update (BCPRU) – The complexity and confusion when major algorithm updates overlap
  • Google Multisearch – Exploring how “Searching outside the box” is being tracked in Google Search Console (GSC) and Google Analytics (GA)
  • Sitebulb Server – Technical Tips And Tricks For Setting Up A Powerful DIY Enterprise Crawler (On A Budget)
  • Google’s Helpful Content Update Introduces A New Site-wide Ranking Signal Targeting “Search engine-first Content”, and It’s Always Running
  • The Google May 2022 Broad Core Update – 5 micro-case studies that once again underscore the complexity of broad core algorithm updates
  • Amazing Search Experiments and New SERP Features In Google Land (2022 Edition)

Web Stories

  • Google’s December 2021 Product Reviews Update – Key Findings
  • Google’s April 2021 Product Reviews Update – Key Points For Site Owners and Affiliate Marketers
  • Google’s New Page Experience Signal
  • Google’s Disqus Indexing Bug
  • Learn more about Web Stories developed by Glenn Gabe

Archives

  • January 2023
  • December 2022
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • August 2021
  • July 2021
  • June 2021
  • April 2021
  • March 2021
  • 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 © 2023 G-Squared Interactive LLC. All Rights Reserved. | Privacy Policy
This website uses cookies to improve your experience. Are you ok with the site using cookies? You can opt-out at a later time if you wish. Cookie settings ACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience. You can read our privacy policy for more information.
Cookie Consent