Google Search Console (GSC) is powerful and provides a boatload of data directly from Google. One of my favorite reports in GSC is the Search Analytics report, which enables you to drill into queries that your site ranks for and the landing pages ranking for those queries, then slice and dice that information by device, country, date and more. You can also compare queries, pages, countries and devices by date, which is extremely helpful when diagnosing surges or drops in traffic.
When analyzing websites, I often export data from GSC to Excel for further analysis. For example, when helping companies with website redesigns or CMS migrations, it’s important to have a solid understanding of all landing pages ranking for queries and receiving Google traffic. You can find that data in the Search Analytics reporting by selecting the Pages group. See below.
There’s a nifty Download button at the bottom of the report which enables you to export a spreadsheet with your landing page data. That’s great, but I’m about to rain on your parade: The UI in Google Search Console is limited to only one thousand URLs. Yes, just one thousand. That may be enough for some sites, but it’s extremely limiting for many large-scale sites.
So we’ve got a tricky situation. We have valuable data directly from Google, but we’re handcuffed by not having enough of it (at least to download). What’s an SEO to do?
I’ve written in the past about how to hack Google Search Console to get more data from your reports. For example, setting up directories in GSC, which will focus your reporting on URLs located just in those directories. That will help, and it can enable you to find more landing pages in GSC. But it’s not perfect, especially for sites that don’t have categories mapped out as distinct directories. And you’re still limited to one thousand rows of data per directory.
You can also learn how to code and use the Search Console API directly. Just understand that there’s a learning curve involved. Unfortunately, I know many people who have started to dig into the API and then wasted time, never got it to work, and pulled their hair out in frustration. If you have the technical aptitude to code and tap into the API, I recommend doing that. But if you’re looking for a solid solution right out of the box, that might not be your best option. Let’s move on.
So, based on the limitations I listed above, wouldn’t it be nice to have a tool that can easily and seamlessly export all of your landing pages (or queries) from GSC to Excel? And I mean ALL of them. Enter Analytics Edge.
In March, I wrote a post explaining how to easily export all of your landing pages from Google Analytics via Analytics Edge. I ended up finding the solution since I work on a lot of large-scale websites and didn’t have an easy way to export bulk reports. Analytics Edge works extremely well when exporting many landing pages from Google Analytics. You can use the Google Analytics Connector for Analytics Edge to work seamlessly with your data.
Well, it wasn’t long before I started testing other “connectors” that Analytics Edge supports. And one is for Google Search Console.
There are several important tasks you can execute by using the connector for Search Console, but I’ll focus on exporting all of your landing pages for this post. I think once you try it out, you’ll be hooked. I know I am… I use it all the time, especially when working with large-scale sites.
Let’s jump in.
1. Download and install the Analytics Edge free or core add-in. There’s a free trial for the core add-in if you wanted to simply test it out. But the free add-in will work as well (just with less functionality). After installing the add-in, you should register it in Excel.
2. Next, install the Search Console connector by clicking the Manage Connectors button in the menu.
3. Once you install Analytics Edge and the Search Console connector, access the options in the Analytics Edge menu at the top of Excel. Click the Google Search drop-down and select Accounts. This is where you will connect Analytics Edge with the Google account(s) you want to download data from. Go through the process of connecting the Google account you want to work with.
4. Now that you’ve connected a Google account to Analytics Edge, click the Google Search drop-down again and select Search Analytics. This will start the wizard for working with the Search Analytics report from GSC. Name the macro whatever you want, and you’ll be taken to the next screen.
5. Now pick an account to work with and a specific website. You should see all of the sites you have access to in the list. Also, select the page dimension, since we want to export all landing pages from GSC. Next you can select filters and the date range.
6. There are three tabs in the report interface. The first is labeled Fields, and we just set that up by selecting the page dimension. If you click the Filters tab, you can filter your report by query, page, country, device and type. You can also limit the number of rows you want to export. For our purposes today, I would leave all blank (but you can focus your export by using these filters in the future.)
7. The third tab, labeled Dates, enables you to select the date range for your report. There are some presets available, like “Last 28 Days” and “Last 90 Days,” or you can select a specific date range. Note, GSC data only goes back 90 days. This is a serious limitation, and I know Google is thinking about extending that to one year. There’s no ETA on that yet, so let’s choose “Last 90 Days” to get as much landing page data as possible. Then click Finish to start the export.
8. Analytics Edge will export your results, but it will only show a sample of the export in the worksheet highlighted in green (in memory). If all looks good with the sample, then you must “write to worksheet” to see all of the data that was exported. In order to do this, click File, and then Write Worksheet. Simply enter a name for your new worksheet and click Finish. Boom, check the new worksheet that was created to find all of your landing pages.
If you followed the instructions above, then you are staring at glorious landing page data in its entirety. Not a one-thousand row sample of that data, but the entire enchilada. Awesome, right?
So whether you’re working on a redesign or CMS migration, analyzing an algorithm update or simply archiving Google Search Console data, Analytics Edge makes it easy to export ALL of your data without running into the one-thousand-row limitation.
If you’re working with a large-scale website in GSC, then I’m sure you’re eager to use Analytics Edge to seamlessly export all of your top landing pages. But what if I told you there’s another report that could blow your mind?
Using the Search Console connector for Analytics Edge, you can download all queries by landing page. Yes, that means you can quickly pivot that data to have a killer report in Excel listing each landing page, along with each query that the page ranks for. I won’t cover every step in the process as I did above, but I’ll explain the main differences with running the new report below.
First, when choosing settings in the Analytics Edge wizard for the Search Analytics reporting, you should select both the page and query dimensions. That will enable you to export the raw data containing queries by URL.
Once you export the data and “write to worksheet” as I explained earlier in the tutorial, you’ll see all queries by landing page. You’ll notice that the data isn’t organized well, but we can take care of that quickly with a pivot table.
To create a pivot table using our data, click the Insert link in the main menu of Excel, and then Pivot Table. Leave the default options and click OK. The pivot table should be created in a new worksheet.
Then select page, query, and then metrics like clicks, impressions, and average position. And boom, you’ll now see each landing page and its associated queries. See below.
Now you’ll have each landing page that is ranking in Google and driving traffic, along with the queries that the page is ranking for. Awesome.
Google Search Console contains a powerful set of features and data directly from Google. And the search analytics report within GSC provides a wealth of data regarding queries that are ranking in Google, the pages that rank for those queries and more. But there’s a one-thousand-row limit on exporting from GSC, which is extremely limiting for many larger sites.
That’s why using a tool like Analytics Edge can help liberate your data. With just a few clicks, you can export all of your landing pages. And I mean ALL of them. Have fun.