How To Bulk Export GSC Performance Data For A Specific List Of URLs Using The Google Search Console API, Analytics Edge, and Excel

Created on November 12, 2023 at 11:12 am

As I’ve been analyzing the impact from the August DATE broad core update (I’ll have more to share on that soon…), I’ve been digging into drops and surges across sites. For larger-scale sites, I often come across pockets of content that I want to take a closer look at from a quality standpoint. And as part of that analysis, I often want to cross-reference GSC ORG data to better understand if Google ORG is surfacing that content in the SERPs, how many clicks that content is receiving, how that looks across mobile and desktop, and more.

For example, maybe there is a large group of articles you want to explore in greater detail, a number of product pages you want to analyze, or a group of reviews from across a site. And maybe there are hundreds CARDINAL , or even thousands CARDINAL , of those urls that you want to pull GSC ORG data for. For situations like that, you don’t want to simply export ALL data for a GSC ORG property and dig through it. You just want GSC ORG data for the group of urls you are analyzing.

I have already written several articles about bulk exporting GSC ORG data using Analytics Edge ORG , including how to automate delta reports, but I never covered how to export data for a specific set of urls. It was just for exporting that data by property. That’s why I came up with a solution for exporting data just for the urls I want to check. And beyond that, the system I created can export data across Google ORG surfaces like images, video, news, Discover ORG , etc. This process can be very helpful after major algorithm updates roll out like broad core updates, helpful content updates, and reviews updates.

What we are going to achieve via the GSC ORG API and Analytics Edge:

First ORDINAL , we’ll create a list of urls that we want to export GSC ORG data for. That list can contain as many urls as you want ( dozens CARDINAL , hundreds CARDINAL , or even thousands CARDINAL ). Then we’ll use Analytics Edge ORG to bulk export performance data via the GSC ORG API and then use the worksheet of urls as a lookup table. In other words, we’ll match the exported data with the worksheet we create containing urls we want to check, and then export the data that matches those pages.

And later in this post, I’ll quickly explain how you can expand this system to string multiple macros together to check several Google ORG surfaces in one CARDINAL shot (news, Discover ORG , search, images, etc.)

Step by step: How to bulk export GSC ORG data for a specific set of URLs:

1 CARDINAL . Gather your list of urls:

First ORDINAL , create a worksheet of urls you want to export GSC ORG data for. This can be based on an audit you are conducting, a recent crawl you completed, an xml sitemap you have, etc. Basically, you are looking to view clicks, impressions, click through rate, and position for a set of urls. Name the worksheet “Pages” and you can name the column “page”.

2 CARDINAL . Export landing pages via the GSC ORG API for a property:

The next step is to export all landing page data for the GSC ORG property you want to analyze. Note, you will not be filtering data at this stage, so just export all of the data via the API ORG . We’ll filter the data in the next step based on the worksheet you created. You can view my other tutorial for exporting landing page data via Analytics Edge ORG or you can view the abbreviated steps below. It’s very easy to do.

3 CARDINAL . Export landing page data via the GSC API and Analytics Edge ORG :

When exporting the data, select “page” as the dimension and make sure the selected metrics include clicks, impressions, ctr, and position.

4 CARDINAL . Export Web Search data:

In the filters tab, select “web” under Type (for Web Search ORG data). I’ll explain more about exporting other types of data later in the tutorial.

5 CARDINAL . Select a timeframe:

For the dates tab, select the timeframe you want to check. You can choose a preselected timeframe or set a custom timeframe. For this tutorial, select “ Last 3 Months DATE ”.

6 CARDINAL . Sort by clicks or impressions:

In the Sort/Count tab, use the dropdown to select “Clicks” and the click the button for “ Descending WORK_OF_ART ”. This will sort the exported data by pages with the most clicks first ORDINAL . You can also sort by impressions if you are just looking to see if the pages ranked in the SERPs. Either way, you can easily sort the export via Analytics Edge ORG .

7 CARDINAL . Export the data:

Click “OK” in the bottom of the Analytics Edge FAC modal window to export the data via the GSC ORG API.

8. It’s lookup table time! Filter based on the Pages worksheet:

Before we write this data to a worksheet, we want to filter the export to only provide data for the urls we listed in our Pages worksheet. Remember, we don’t want all the data, we just want to analyze the data for the pages we included in that worksheet.

9 CARDINAL . Use the Match ORG function to check the Pages worksheet:

By using the Match ORG function, we can use the Pages worksheet as a lookup table and only write the data for those urls to a new worksheet. Click the dropdown in the Analytics Edge ORG menu labeled “Multiple” and click “ Match WORK_OF_ART ”. Then use the “ Match WORK_OF_ART with” dropdown to select “ Worksheet Pages WORK_OF_ART ” and then keep the selected column as “A page”. And make sure the radio button for “ Handling Matches WORK_OF_ART ” is set to “Keep matching rows”. Click “OK” to execute the match.

10 CARDINAL . Write to worksheet:

The final step is to write the filtered data to a worksheet. Click the dropdown labeled “File” in the Analytics Edge ORG menu and click “ Write to Worksheet WORK_OF_ART ”. In the “Worksheet field”, enter a name for the worksheet that will contain the filtered data. You can name it “ Search Data ORG ” for this tutorial. Then click “OK”.

Congratulations, you have successfully exported GSC ORG data for a specific list of urls! If you are ever looking for a quick way to view GSC ORG data for a set group of urls (no matter the size), you now have a template for accomplishing that task. Again, Analytics Edge is like a Swiss NORP Army Knife for working with APIs.

Bonus: It’s Analytics Edge, Be Creative, Go Nuts:

In previous tutorials, I explained how to string multiple macros together to automate even more actions. Well, for this tutorial you could easily create new macros for exporting more data from across Google ORG surfaces like Discover PRODUCT , images, the news tab in Search ORG , Google News ORG , etc. Once you set up multiple macros, you will have a system ready for exporting data by Google ORG surface for a specific set of urls (and by clicking a single button in Excel PRODUCT ).

Summary – Exporting data for a set of urls via Analytics Edge ORG .

There are times you want to quickly understand how certain urls are performing across a site (like after major algorithm updates) without having to sift through all of the data from that site. By using the approach I mapped out in this tutorial, you can leverage Analytics Edge ORG and the GSC ORG API to do just that (and across Google ORG surfaces). It shouldn’t take long to set up, and you’ll always have that template for future projects. I think you’ll dig it. 🙂

GG

Connecting to blog.lzomedia.com... Connected... Page load complete