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 Export All Search Queries From Google Search Console To Compare Clicks And Impressions After An Algorithm Update (using Analytics Edge)

March 18, 2017 By Glenn Gabe 8 Comments

Share
Tweet
Share
Email

How to export queries from GSC via Analytics Edge.

When experiencing a traffic drop due to an algorithm update, redesign, migration, or some other event, it’s important to dig into the drop to understand the queries and landing pages that saw the biggest change. When you do, you can have a strong feel for the pages impacted and the queries leading to those pages (and that can sometimes help you begin to identify the cause of the drop).

When analyzing a traffic drop or surge, there’s nothing better than your own data. For example, digging into Google Search Console and Google Analytics – or whichever analytics package you are using. For example, with GSC’s Search Analytics reporting, you can view clicks, impressions, and average position for queries and landing pages, while also comparing timeframe to see the change. That’s important and can quickly help you gauge the big winners or losers.

In previous posts, I’ve mentioned my tutorial about creating a Panda report, which can help you identify landing pages that saw the biggest change when experiencing a traffic drop. That’s based on Google Analytics data and compares sessions from Google organic after the hit to before the hit (in Excel). It’s a great report that can help surface urls seeing the biggest movement.

But, it doesn’t provide the queries seeing the biggest drop. And that’s exactly what we’re going to tackle today.

The GSC UI Limit and The Power of APIs
If you’re working in GSC a lot, then you know the major limitation with its UI reporting. I’m referring to the one thousand row limit in GSC’s Search Analytics reporting. For larger sites, this is extremely frustrating and can inhibit the amount of data you can work with and export.

The dreaded 1k row limit in GSC.

Thankfully, there’s an API that can enable those with developer resources to pull all of the data. That’s great, but what if you don’t have dev resources readily available? How can you get that data??

Analytics Edge to the rescue (again)!
Last year I wrote a post explaining how to export all of your landing pages from GSC via Analytics Edge. It’s an awesome solution that quickly and efficiently pulls GSC data via the API. You can read my post to learn more about that.

For today’s post, I will explain how to go even further by exporting all queries from two different date ranges, and then using the power of Excel to identify the difference in clicks and impressions. Again, after a traffic drop due to an algorithm update, this is invaluable data to have.

Let’s begin.

Step-by-step: How to compare clicks and impressions for queries in Excel:

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 will register it in Excel.

Register analytics edge.

2. Next, install the Search Console connector by clicking the Manage Connectors button in the menu.

Manage connectors in GSC

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.

Analytics Edge accounts menu in Excel.

Manage accounts in Analytics Edge.

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 reporting from GSC. Name the macro whatever you want, and you’ll be taken to the next screen.

Search Analytics in Analytics Edge.

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 query dimension, since we want to export all queries from GSC. Next you can select filters and the date range.

Select queries in Analytics Edge via Fields tab.

6. There are three tabs in the report interface. The first is labeled Fields, and we just set that up by selecting the query dimension. If you click the Filters tab, you can filter your report by query, page, country, device, type, and search appearance. You can also limit the number of rows you want to export. For our purposes, I would leave them all blank (but you can focus your export by using these filters in the future.)

Filter queries in Analytics Edge.

7. The third tab, labeled Dates, enables you to select the date range for your report. This is extremely important since we want to compare two different time ranges (due to an algorithm update or some other event that caused a traffic change.) For the start date, choose the date the traffic drop began and then choose an appropriate end date. Make sure to give yourself enough days to work with or the comparison might not contain enough data. Then click Finish to start the export.

Select date range in Analytics Edge.

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 like “Current” and click Finish. Boom, check the new worksheet that was created to find all of your queries exported from GSC.

Write To Worksheet in Analytics Edge.

If you followed the instructions above, then you are staring at glorious query data from the timeframe you selected. Not a one-thousand row sample of that data, but the whole enchilada. For example, you can see below that I exported close to 39K queries in just a minute or two. Awesome, right?

Export all queries from GSC via Analytics Edge.

9. Repeat step seven again but this time pull data from the previous timeframe. This is the data we are going to compare against. Make sure the amount of days matches what you pulled in the previous step. For example, compare three weeks of data to three weeks of data, or four to four. Don’t compare three weeks to one week or it’s not a true comparison. Also, if your site does not have consistent traffic all week, then make sure your comparison matches the days of the week (versus simply checking the previous X days). You can name this worksheet “Previous” to keep it simple.

Excel Magic Through Vlookup

Once you export the previous timeframe, you will have two worksheets with query data. This is where the magic of Excel comes in handy. We’re going to use vlookup to check the previous timeframe for a query, and if it matches a query in the current timeframe, then we’re going to pull clicks and impressions into our current worksheet. That will enable you to compare metrics for each query prior to the traffic drop, to after the drop.

10. In the worksheet named “Current”, create a new column titled “Clicks Previous”. This is where we will pull click data from the worksheet containing metrics from the previous timeframe. In that field, enter the following formula:

=VLOOKUP(A2,Previous!A:E,2, FALSE)

Using vlookup to access clicks from previous timeframe.

How it works: vlookup will check the query in the current worksheet and then cross reference the query in the second worksheet (which contains data from the previous timeframe). If the query is present, it will pull the clicks data from the second worksheet into the first.

  • A2 is the column that contains the query in our “Current” worksheet.
  • Previous is the name of our second worksheet containing data from the previous timeframe. Note, if you named the worksheet more than one word, you’ll need to add single quotes around the text.
  • A:E is an array that tells Excel which columns to use for the lookup in the second worksheet (Previous). We want to cover all of the columns, which for me contains data in column A through E.
  • 2 is the column number (by order) we want to cross reference in the Previous worksheet. Count the columns from left to right and you’ll see “clicks” is the second column over. So that’s why we use 2 as the number in the formula for the fourth argument.
  • False is used as the Range_lookup because we want an exact match for the query.

11. I recommend testing the formula on one cell that you know contains a query that’s present in both the Current and Previous worksheets. If the formula worked, then you should see a value show up in the field for Clicks Previous. Quickly check the Previous worksheet to make sure that’s accurate and you’re good to go.

12. Now you need to copy that formula to all rows in the “Clicks Previous” column. The easiest way to do that is to single click the cell containing the new formula and then hover your cursor over the bottom right corner of the cell. Then double-click the small green square. The formula will be copied to all remaining cells in the column. And you should see the previous click count show up in each cell.

13. Repeat the process above for impressions. Create a new column titled “Impressions Previous” and use the same vlookup formula. However, you’ll need to change the column you are using for the lookup. The formula should look like this:

=VLOOKUP(A2,Previous!A:E,3, FALSE)

Using vlookup to import impressions from previous timeframe.

I bolded the 3 since that’s the only difference in the formula. That tells Excel to use the third column for the lookup in our “Previous” worksheet. That third column contains impressions.

14. If all looks good, then copy that formula to the rest of the rows in the “Impressions Previous” column by clicking the small green square in the bottom right corner of the cell again.

15. Now you will create additional columns that simply show the difference in clicks and impressions between the previous and current timeframe. Create new columns titled “Clicks Difference” and “Impressions Difference”. Then enter the following formula which will subtract the previous timeframe metric from the current timeframe:

=B2-F2

Note, B2 is the column containing clicks from the current timeframe and F2 contains clicks from the previous timeframe.

Calculating the difference between clicks.

Then repeat for impressions, but this time using the following formula:

=C2-G2

Note, C2 contains impressions from the current timeframe while G2 contains impressions from the previous timeframe.

To copy those formulas to the rest of the rows in each column, use the trick I explained earlier to single click the cell you want to copy and then hover your cursor over the bottom right corner. Then double click the small green square.

At this point, you should have a worksheet that provides current metrics from GSC along with metrics from a previous timeframe. And you should also see the difference in clicks and impressions by query. This data is invaluable for digging into queries that dropped based on an algorithm update, redesign, migration, or other event that caused a traffic drop.  Awesome, right?

Some Final Tips:

  • You can use conditional formatting to highlight cells that dropped by a certain amount. That can help you and your team quickly identify queries that saw the most impact.
  • You can add a column for “Position Change” and use average position in your vlookup. Then you can see the number of positions each query dropped or gained.
  • You can follow my tutorial about using Analytics Edge to export all landing pages to dig into the pages that dropped as well. Then you can run this report again and use landing pages versus queries. Then you can dig into the specific urls that dropped the most.

Summary – Use the API, Love the API
Google Search Console (GSC) is a killer tool that every SEO should use, but the one thousand row limit can be maddening for site owners of larger websites. Using the API via a tool like Analytics Edge can enable site owners to export well beyond the one thousand row limit. And then you can use the power of Excel to analyze the data. It’s a great combination that I’ve found incredibly valuable while researching traffic drops due to algorithm updates (or other events). I recommend trying this out today. I have a feeling you are going to dig it.

 

Share
Tweet
Share
Email

Filed Under: google, seo, tools

Connect with Glenn Gabe today!

Latest Blog Posts

  • How to compare hourly sessions in Google Analytics 4 to track the impact from major Google algorithm updates (like broad core updates)
  • It’s all in the (site) name: 9 tips for troubleshooting why your site name isn’t showing up properly in the Google search results
  • Google Explore – The sneaky mobile content feed that’s displacing rankings in mobile search and could be eating clicks and impressions
  • Bing Chat in the Edge Sidebar – An AI companion that can summarize articles, provide additional information, and even generate new content as you browse the web
  • The Google “Code Red” That Triggered Thousands of “Code Reds” at Publishers: Bard, Bing Chat, And The Potential Impact of AI in the Search Results
  • 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

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

  • March 2023
  • February 2023
  • 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