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

Archives for April 2021

Google’s Product Reviews Update – Analysis and findings from a major algorithm update impacting affiliate marketers, review sites, and more

April 22, 2021 By Glenn Gabe Leave a Comment

Update: May 2022
I just published my findings based on analyzing the March 2022 Product Reviews Update (PRU). My post contains a number of interesting observations, including the use of video, linking to multiple sellers, intent shifts, PRU loopholes, the power of links (or not), and more.

Update: December 2021
I just published my analysis and findings based on the December 2021 Product Reviews Update. The Dec PRU is the second product reviews update that Google has rolled out (following the initial rollout in April 2021). It was another massive update that took three weeks to fully roll out. You can read my post for more information about the December update.

—–

On April 8, 2021 Google announced a new algorithm update called the Product Reviews Update. The update was designed to reward in-depth reviews versus thinner review content (whether that’s a single product review, a group of products, service reviews, etc.) I’ve been heavily analyzing the update since it launched, including impact across different niche categories, content that was impacted, site-level findings, user experience factors across sites affected, and more. Based on what I’ve been seeing, the update has been core update-like for some sites. For example, some sites impacted are experiencing heavy drops or surges based on the Product Reviews Update.

For example, here are some visibility changes for sites focused on reviews:

Google’s Danny Sullivan explained the update can take up to two weeks to fully roll out, and I absolutely saw that in action. For example, I saw a lot of volatility early in the rollout around 4/9 and then have seen volatility later in the rollout as well (like 4/15 and 4/19). I tweeted about that movement along with screenshots of what I was seeing. That additional movement could have been based on the two-week rollout or it could have been based on tremors – where Google is tweaking the algorithm over time based on what they are seeing in the SERPs (which is definitely possible).

Here are some examples of late movement for two sites focused on reviews (both starting on 4/19). Remember, the update started rolling out on 4/8:

Below, I’ll cover what I’ve found during my analysis while I’ll also providing some final bullets covering key takeaways and recommendations for site owners that have been impacted. I’ll start with important points to understand about the update overall and then I’ll cover my findings based on digging into a number of sites impacted by the update (across various categories). Also, I published a companion Web Story for site owners and affiliate marketers covering those key points about the Product Reviews Update.

Table of Contents:
Since the post is long with a number of sections, here’s a table of contents so you can jump around:

  • Key points about the launch and rollout.
  • Google evaluating sites more broadly (site and section level impact).
  • Sites impacted by previous core updates.
  • Frequency of updates and recovery.
  • Discover impact.
  • Analysis and findings.
  • Reviews content quality.
  • Thin versus valuable.
  • Examples of thinner reviews.
  • The anatomy of a strong review (Wirecutter).
  • Affiliate aggressiveness.
  • Content organization.
  • Affiliate disclosure.
  • Authors and branding.
  • Aggressive advertising plus affiliate content.
  • The Authority Impact.
  • Collateral Improvement – Holes in the algo.
  • Structured data.
  • Key takeaways and recommendations for site owners.

The Product Reviews Algorithm Update: Key Points To Understand
Launch and Rollout:
First, and as I explained earlier, the update officially launched on 4/8/21 and Google explained it can take up to two weeks to fully roll out. The update feels a lot like a broad core update for some sites impacted, but it’s just focused on reviews content. Note, the two-week rollout is exactly what Google says when a broad core update rolls out. Just an interesting side note.

This is the timing on the rollout completing (basically two weeks). And it started today https://t.co/oTqGqOSuHZ

— Danny Sullivan (@dannysullivan) April 8, 2021

It’s worth noting that I asked Danny Sullivan if the update was still rolling out on 4/20. He replied and said it was indeed still rolling out. That makes total sense based on what I was seeing across sites impacted (you can see my screenshots from earlier about impact over the two-week rollout).

It is not.

— Danny Sullivan (@dannysullivan) April 20, 2021

And then we finally heard on 4/22 that the rollout was complete. So it was a full two-week rollout for the update:

Actually, just checked, and it's done. So yeah. Caveat is with any update, you can still sometimes have some edge case lingering. But effectively done.

— Danny Sullivan (@dannysullivan) April 22, 2021

Site and Section-level Evaluation, Not Just Pages:
This is super-important to understand, and again, it’s similar to how broad core updates work. Google has explained that the Product Reviews algorithm is looking more broadly at sites and sections. For example, analyzing the site overall and/or specific parts or sections of a site. With core updates, Google is evaluating the site overall, where with this update, it can be a mixture of the site overall or sections of a site (that contain reviews).

Google's Product Reviews Update evaluates sites more broadly on the site or section level.

So, this is not always going to be a page versus page comparison. I bring this up because I’ve seen site owners sometimes make a big mistake with broad core updates by not reviewing the site overall from a quality and relevance standpoint. This is also why Google has explained time and time again to significantly improve a site overall to see recovery from a broad core update. I have hammered this point in all of my posts about core updates. This advice also applies to the Product Reviews Update.

Sites Surging That Were Previously Impacted By Broad Core Updates:
It’s also worth noting that I have seen sites surge or drop after seeing a lot movement during broad core updates. For example, some sites that dropped heavily during the December broad core update started surging with the Product Reviews Update. So, it’s like recovery from a broad core update, but not during a subsequent broad core update. That is not the typical way recovery from broad core updates works. This is more evidence that the Product Reviews Update is evaluating sites overall. For example, here are two sites that surged during the Product Reviews Update that dropped heavily during the December broad core update. And as I mentioned above, sites typically do not see recovery (or significant movement back) until another broad core update rolls out. So this was super-interesting to see.

Types of Reviews Impacted:
Google has explained that various types of reviews can be impacted, including single product reviews, lists of products being reviewed, services being reviewed, etc. In addition, sites with heavy UGC reviews can be impacted as well. I have seen all of these types of sites and content impacted based on my analysis of the Product Reviews Update. I’ll cover more about that soon.

It looks to improve for all types of product reviews, single reviews or round-ups.

— Danny Sullivan (@dannysullivan) April 8, 2021

Frequency of Updates and Recovery:
After the announcement, I asked Danny Sullivan if the update will roll out like broad core updates (every few months) or if sites can see improvement as they improve their reviews content and the site overall. Danny explained that the update requires a periodic refresh, and we might not be notified when that happens. This is extremely important to understand. If you’ve been impacted by the update, you have to work to improve the site and content over time and wait for a refresh. You probably cannot see a lot of improvement until that refresh occurs.

At the moment, there's a periodic refresh. Unlike with core updates, we might not always post when a refresh happens given the more limited nature of content involved here. So overall, sites should consider the advice & keep working to it (true of core updates as well!).

— Danny Sullivan (@dannysullivan) April 9, 2021

In addition, John Mueller explained in a Search Central hangout that he wouldn’t be surprised if the algorithm is baked into core web ranking at some point. It’s just another reason to tackle this situation NOW and try to improve your site significantly. Here is a video of Google’s John Mueller explaining this (at 29:38 in the video).

Discover Impact (like broad core updates):
Google has also explained that the Product Reviews Update can impact Discover visibility (just like it can with broad core updates). Again, this underscores the point I mentioned earlier about Google evaluating a site or sections more broadly. It’s not unusual for sites impacted by broad core updates to see a lot of volatility in Discover and now it’s similar with the Product Reviews Update.

Barry covered 2 points today that are important for site owners to understand. 1) There will be a periodic refresh w/the Product Reviews Update (and it might not be announced). So keep working to improve… It's not real-time. 2) Discover visibility can be impacted by the update. https://t.co/W6T2ZycVWf

— Glenn Gabe (@glenngabe) April 14, 2021

And I have an amazing example of this happening. There is a site I helped that recovered from a broad core update hit last year. It’s a news publisher that surged during the December broad core update. That was amazing to see, but they still weren’t showing up in Discover at all. The Discover reporting didn’t even show up in GSC! Well, with the Product Reviews Update, the site finally started showing up in Discover. It was amazing to see. And it was right on 4/8 when the increase started. The strange part is the site doesn’t focus on reviews at all. :)

But beyond this one example, there are a number of site owners reaching out to me that have seen Discover volatility since the update started rolling out on 4/8. That makes total sense based on what Google has explained about the update impacting Discover visibility.

Analysis and Findings: Google’s Product Reviews Update
Now that I’ve covered key points about the update, I’m going to cover my findings based on analyzing many different sites impacted since the update started rolling out. The sites are across a number of different niche categories, including consumer goods, health, technology and software, professional services, education, finance, food and recipe sites, and more. As I said earlier, some of the impact has been core update-like with significant visibility changes since 4/8/21.

Below, I’ll cover some important findings based on what I’ve been seeing across sites both negatively and positively impacted. I have reviewed content that dropped or surged, the SERPs before and after the update, compared link profiles of sites competing in each space, and more. I obviously can’t analyze every situation, site, and piece of content, but I dug into many sites across a number of categories. It’s also worth noting that you can definitely find examples of sites that surged that shouldn’t, or dropped that shouldn’t. Remember, this is an algorithmic, so there are always going to be examples of collateral damage or improvement. Actually, I cover some examples of collateral improvement later in the post.

Also, I am actively helping several companies that provide reviews (across different categories) and have been able to dig into changes those sites are seeing based on the update. And last, I’ve had a number of site owners reach out to me after seeing impact based on the update. So, there’s been a lot of data to dig into since the update started rolling out.

Before I begin, a quick disclaimer:
I do not work for Google. I do not have access to Google’s Product Reviews algorithm. I have not dressed up as Paul Haahr’s long lost uncle Homer and approached him on the street about trying to learn more about what Google is crafting algorithm-wise. OK, I have thought about trying that… but haven’t moved on it yet. I’m not sure Paul would appreciate a scene like the following… :)

The findings below are based on reviewing many sites impacted by the latest update, based on my experience analyzing major algorithm updates during my career, hearing directly from site owners impacted, and helping several companies that focus on reviews (across different niche categories).

With that out of the way, let’s jump in.

The Product Reviews Update: Key Findings From The Front Lines

Reviews Content: Mostly thorough, helpful, detailed, and balanced (with clear disclosure).
When the update first rolled out, it was easy to think that all sites negatively impacted would have extremely thin review articles, short and meaningless posts, etc. That was definitely the case for some sites I analyzed that dropped, but definitely not all. Not even close actually. Once I dug into the drops, I found many review articles that were actually ok (not great, but not horrible either). In addition, there were several examples of collateral improvement… i.e. sites that surged that had little to do with reviews. I’ll cover more about that later in the post.

Below, I’ll cover key themes based on analyzing the update, while providing several examples of what I found (without revealing the sites and urls). My goal isn’t to “out” any specific company for dropping during this update. Based on my findings, I think you’ll get a good feel for what I was seeing across various niche categories.

Thin vs. Valuable:
If you read Google’s post about the Product Reviews Update, it provides a number of questions that site owners can ask themselves about their own review content. Google also linked to their blog post about core updates, which contains even more questions site owners can ask. Those questions address making sure sites provide unique, original, and thorough review content for consumers.

Reviews should be well-balanced and cover pros and cons, provide as many details as possible so users can truly understand more about each product in order to make an informed decision. That includes providing strong visuals and/or video supporting the review, data about how products or services measure up against the competition, and more. I highly recommend reading through those questions several times.

It’s also important to understand that thin has nothing to do with word count. Instead, it has everything to do with value. Site owners should objectively determine if their content can meet or exceed user expectations based on query. If it can, it’s not thin. If it can’t, I would consider it thin (even if you have the same amount of content). Again, it’s not about word count.

When checking content that dropped, a lot of it wasn’t mind-blowingly extensive. Sure, it might provide a paragraph or two about the product, but it often wasn’t giving me the detail I wanted in order to make a purchase decision. And on the flip side (mostly), many of the reviews that surged (or remained with strong rankings) were more detailed, provided more balance, contained clear affiliate disclosures, strong visuals, and were written by experts or enthusiasts in those categories.

It’s also important to know that you could easily tell when a writer had not used the product or service they were reviewing. The level of detail based on actually using a product, testing it out, etc., cannot be matched if you really didn’t try it out… That sounds obvious, but I came across many reviews that were like that.

Examples of thinner reviews content that dropped:
Here are some screenshots from review content that saw negative movement based on this update where the reviews were thinner in my opinion. Again, a simple paragraph is not sufficient, reiterating what the manufacturer already wrote is not extremely helpful, a lack of data and strong visuals left me wanting more, etc.

E.g. One paragraph and just a set of links to buy products:

Not much original and valuable content:

Not enough content to help consumers and the page is inundated with ads (on top of affiliate content):

Ditto:

On the flip side, strong reviews content:
I know many of you reading this post want to see some good examples of reviews. Again, my intention is not to call out certain sites for bad reviews or focus attention on many sites that surged. That said, there is one site that produces some of the best review content on the web — Wirecutter. I’ll provide one example below highlighting some of the great things they do with their reviews. I totally understand it’s not possible for many sites to match or exceed what Wirecutter is doing, but it would be smart to review their content to get a stronger understanding of how to craft outstanding review content. It’s also important to understand that each vertical is different and will require different levels of expertise, research, and thoroughness.

Example URL: https://www.nytimes.com/wirecutter/reviews/best-bluetooth-headphones/

Clear affiliate disclosure:

Broken down by category:

Table structure with top picks, prices, discounts, and stock updates:

Table of contents:

Detailed research with information about how they pick the top products, how they tested them, etc.:

Strong visuals of each product:

Additional research and information for consumers (privacy and security for this specific review):

Author details (expertise, links, etc.)

Wirecutter provides a great structure for their reviews, packed with insightful, unique, and valuable information for consumers, presented in a user-friendly way, with clear affiliate disclosures, author information, and more. Again, if you focus on reviews, I would spend some time there.

Affiliate Aggressiveness:
For affiliate marketers, there’s always a fine balance between providing information and getting users to click through (so they can earn a commission). With a number of sites that saw a drop, the affiliate aggressiveness was strong… I felt like the pages were trying to push me to click through in any way possible. There were images leading off the site, links, buttons, and more. I get it, affiliates want users to click through. But as a user researching products, that can get super-annoying. As I’ve said many times with broad core updates, “Hell hath no fury like a user scorned”. I would try to strike a nice balance between providing helpful information and gaining that click… I would not overwhelm users with affiliate links, buttons, image links, and more.

Note, image links can be a bit deceptive since some users might think it would yield larger images to view. But in reality, those images are driving them off the site to buy the product. For those sites that are impacted, you should read my post about running user studies through the lens of broad core updates. That methodology applies here as well and you can include some questions from Google’s post about the Product Reviews Update.

Here is a page that was aggressive with linking out without providing specific reviews of each product:

Content Organization: Comparison, Pros and Cons, etc.
After going through many review pages, this point really stood out. A number of pages on the winning side of the update (or sites that remained strong) seemed to organize their content better. For example, clear sections for each part of the review, using a table structure when comparing features or data across products, providing FAQs at the bottom, summaries for longer reviews, etc. It’s impossible to say if Google is looking at organization like this, but it sure helps from a usability perspective. And since Google is evaluating sites and sections more broadly, I would always try to improve the user experience as much as possible. More about UX soon.

Affiliate Disclosure:
This is something I have been helping clients understand for a long time (for broad core updates) and now it’s front and center with the Product Reviews Update. It’s extremely important that users understand there is an affiliate relationship set up or it can feel very deceptive. For example, making sure users understand that the author is recommending X products, but they are earning a commission from recommending those products. There should be a clear disclaimer for users on the page.

And for me, beyond “clear”, it should be located in areas of the page where users can actually find it. For example, during my analysis (of this update and broad core updates), there are times that affiliate disclosures are buried on the page. They might be at the very bottom and in a smaller font, they might be in the sidebar, and sometimes they aren’t even on the page at all. Instead, there might be a small link to read the “Affiliate Disclosure” on another page.

If you provide reviews on your site and have affiliate links to earn a commission, then you should absolutely have a clear disclaimer that users can easily find and read. And I recommend having one at the top of the content and at the bottom.

Authors and Branding:
Google’s blog posts about the Product Reviews Update and broad core updates both explain more about expertise. For example, each post talks about “experts and enthusiasts” writing content. Let’s face it, anyone can take a manufacturer description, add some fluff, and then add an affiliate link to Amazon. But, not everyone can provide a solid review based on extensive expertise in a certain category.

I already covered thin versus valuable content, but there’s also an aspect of understanding author information or branding information (which can help users learn about who is writing the content and which company is behind the site they are visiting). When reviewing sites and content that dropped or surged, I could see this first-hand. First, not all pages contained author information. That’s a big mistake in my opinion, since users typically want to know that a review is authored by someone with expertise in the subject matter. Second, some authors listed didn’t have much information there (and not even full names).

On the flip side, there were sites that performed well during this update that had reviews written by experts or enthusiasts and provided solid author information on the page or site (via a bio). That definitely reinforces for users that the content is at least written by someone with expertise in the subject matter. It doesn’t mean the content is great… that person still needs to write a valuable post, but it’s important for users to learn more about the person writing the content.

Beyond authors, I found some sites that dropped heavily didn’t even have solid branding on the site. For example, there was no logo or site name at the top of the page, it wasn’t easy to find out who ran the site, which company owned the site, and more. Similar to learning about authors, it’s important for users to understand more about the site in general. Is it a fly-by-night company, is it some guy in his basement, is it a teenager trying to earn a quick buck, etc.?

First, here is a page with no author or site branding:

On the flip side, here is page with two authors listed (Senior Editor and Chief Editor):

Aggressive Advertising + Affiliate Content: A UX Nightmare, but…
If you’ve read any of my posts about broad core algorithm updates, then you know aggressive, disruptive, or deceptive ads can be seriously problematic for a site. One thing I noticed while analyzing many sites with reviews was the weaving of many ads within affiliate content. I found that extremely annoying and those sites are double dipping from a monetization standpoint. They are earning ad revenue while also earning affiliate commissions when people click through and buy something. To be clear, those sites can absolutely do that, but the impact to the user experience can be extreme in certain cases.

That said, I didn’t really see a correlation with this update with heavy ad content (and that’s different from what I have seen with broad core updates where there is a connection). So, I would still be very cautious with your ad situation since it can impact the user experience, but from what I saw while analyzing sites that dropped or surged, ads didn’t really seem to play a part.

Here are two examples of sites with aggressive ads with affiliate content (but it didn’t seem to impact the sites based on the update):

Authority (and a possible fallback?):
When referring to the A in E-A-T (authority), Google has explained that one of the best-known signals it uses is PageRank (or links from across the web). And Google’s Gary Illyes once explained that E-A-T has a lot to do with links and mentions from well-known sites. So, it’s always interesting to dig into a site’s link profile and compare it to sites either surging or dropping in the same vertical.

Since Google is evaluating sites and sections broadly with this update, I wanted to dig into the link profiles of sites seeing big gains versus drops. I wanted to see if there was a correlation with authority (and not just quantity of links, but quality). Surprisingly, I did not see a connection overall. Although there were some extremely authoritative sites doing very well with the update, there were also many examples of sites with weaker link profiles that surged during the Product Reviews Update. I found that super-interesting.

For example, here is a site with just 426 inbound links surge during this update:

And here is a site with 261K links that dropped heavily. It’s not about the quantity of links, but the site has strong links from authoritative sites. And it still got hammered.

But hold on, as I dug deeper, this finding didn’t tell the full story from an authority standpoint. For example, I found when a series of reviews were close in quality and thoroughness, it seemed like Google fell back to authority in those cases. For example, it’s like Google was determining which site overall could be more trusted since the reviews were close in quality… Google seemed to rank the more authoritative sites above the others (overall) when analyzing the SERPs for those queries. I saw this a number of times during my research. It’s impossible to know exactly if this is what Google is doing, but again, I saw this a number of times while analyzing the update.

Collateral Improvement: Holes in the algo.
I mentioned this earlier, but I have definitely seen sites that surged during the update that probably shouldn’t have. The ones I’m referring to are larger and more authoritative sites that don’t necessarily focus on reviews content. For these sites, some content is being seen as legitimate reviews content, but it’s really not. I wish I could provide more information about this, but I can’t. And that has led to a big surge in traffic to those urls. I’m pretty darn sure Google wouldn’t want this content to rank for those queries… My guess is Google will correct this at some point. But as of today, those sites and pages are still ranking well. Remember, algorithms aren’t perfect. There are always edge cases.

Below are two queries that lead to pages that are being seen as reviews content. Notice the surge after the update started rolling out:

Heads-up About Structured Data:
In Google’s blog post, they linked to another post for how to present product information via structured data. We know structured data is not a ranking factor, but I would make sure to provide Product structured data when you are reviewing a single product. If you are reviewing several products on one page (like the best widgets for 2021) or comparing two products, then Product structured data doesn’t make sense. Google’s documentation explains that it’s for single products only (as of now).

Key takeaways and recommendations for site owners:
I know I covered a lot in this post about the Product Reviews Update, so I wanted to end with some key takeaways. If you focus on reviews content and your site has been impacted, then I recommend going through the following bullet points and determine which changes you can implement to improve your reviews (and your site overall).

  • Review the list of questions in Google’s blog post about the Product Reviews Update and in its post about broad core updates. Objectively review those questions and determine gaps in your own approach and reviews content.
  • Make sure to analyze your site overall from a quality and relevance perspective. Google has explained the Product Reviews algorithm is evaluating sites and sections more broadly. It’s not always a page versus page comparison.
  • After implementing changes to your content (and site), understand you need to wait for a refresh to see significant improvement from a rankings standpoint. This is similar to how Google’s broad core updates work.
  • Make sure your content is written by experts or enthusiasts based on the subject matter. Provide insightful, original, and valuable analysis for consumers (way beyond what they can learn from the manufacturer description).
  • Provide data and strong visuals to support your reviews. And organize that content in way that’s easy for users to consume.
  • Provide clear affiliate disclosures and in areas of the page where users can easily find those disclosures.
  • Make sure to check for Discover impact, which Google has explained can be affected by the Product Reviews Update. And if you were negatively impacted, then you will need to wait for a refresh to see recovery there (if you significantly improve your site and content).
  • Watch both affiliate aggressiveness and the heavy mixing of ads on affiliate content. Remember, “Hell hath no fury like a user scorned”. Don’t overwhelm users based on your monetization strategy.
  • And just like with broad core updates, improve your site, content, UX, ad strategy, and more over time. Google wants to see significant improvement over time in order to see recovery during subsequent updates.

Summary: The Products Review Update Was Core Update-like For Some Sites
The Product Reviews update heavily impacted many sites focused on reviews, and across various niche categories. It was a significant update and Google explained the algorithm is evaluating sites and sections more broadly (similar to how Google’s broad core updates evaluate sites overall). If you have been impacted, I recommend taking a hard look at your reviews content through the lens of the questions Google provided (and based on my analysis above). Then I would implement as many changes as possible to improve your site and reviews content.

And remember, you’ll have to wait for a refresh to see recovery (and we might not be told when that happens). We’ll definitely see the movement after a refresh happens, but Google might not publicly announce it occurred. I’ll definitely be sharing what I see over time with sites impacted by the Product Reviews Update, so follow me on Twitter if you haven’t yet. We know Google has a busy spring and summer planned, so it should be interesting to see when the first refresh happens. Stay tuned.

GG

[Back to top]

Filed Under: algorithm-updates, google, seo

Smart Delta Reports – How To Automate Exporting, Filtering, and Comparing Google Search Data Across Timeframes Via The Search Console API and Analytics Edge

April 12, 2021 By Glenn Gabe Leave a Comment

How to automate a delta report via Analytics Edge in Excel.

In 2013, I wrote a post explaining how to create what I called a Panda Report, which enabled you to identify landing pages seeing the biggest drop during a major algorithm update. The post explained how to do this based on Google Analytics data, but you can definitely do the same thing with GSC data (and with queries in addition to landing pages).

Well, it’s 2021, the process I use has been enhanced, and I wanted to publish a new post explaining how to automate the process using Analytics Edge. First, since medieval Panda is now missing in the SEO trees, the report needed a new name. For the past several years, I’ve simply called it a Delta Report. That fits much better since you can use this approach to identify the change in impressions, clicks, or rankings based on any event impacting Search (like a broad core update, a site migration, website redesign, or any other situation causing volatility). By identifying the landing pages and/or queries seeing the biggest drop, you can often find glaring issues. It’s a great way to start digging into the data after a big drop or surge in rankings and traffic.

And beyond the name change, there are some great ways to bulk export data via the Search Console API now. Since the GSC UI limits exports to just one thousand rows of data per report, using the Search Console API is critically important for exporting all of your data. For example, I often use the API to mass-export landing pages and queries from GSC (going well beyond the one thousand row limit).

A Delta Report is a Panda Report on Steroids – The Power of Analytics Edge and APIs
I have covered Analytics Edge a number of times before in blog posts about exporting data. It’s an add-on for Microsoft Excel that quickly and efficiently enables you to bulk export data via the Search Console API, Google Analytics API, and more. But that’s not all you can do with Analytics Edge. Beyond just the export, you can create macros that filter and organize the data to create advanced reports (so you can export, compare, filter, etc. and all in one shot).

For our purposes, that means exporting all queries or landing pages, comparing the data based on timeframe, filtering based on your site structure, important query types, etc., and then writing those exports to their own worksheets for further analysis. And this is all done in one shot based on the macro you create (build once, use often).

Excited? Below, I’ll walk you through the process of creating the macro via Analytics Edge. And while you go through this tutorial, you’ll probably think of 100 other things you can use Analytics Edge for while working with data. It’s basically a Swiss Army Knife for working with APIs.

How to export GSC data via the Search Console API, compare data across timeframes, filter the data by page or query type, and create separate worksheets, all in one shot:
First, there are some requirements. Although Analytics Edge isn’t free, it’s extremely cost-effective. The core add-in costs $99 per year and the GSC connector costs $50 per year. Also, the good news is that there is a 30-day free trial so you can walk through this tutorial and use the process for 30 days to see how it works for you.

So, for $150 per year, you can use Analytics Edge to your heart’s delight. If you are helping larger sites where the API is necessary to export all of your data, then Analytics Edge is a great way to go, and it definitely won’t break the bank.

How To Create A Delta Report: Step-by-step instructions

1. Download and Install Analytics Edge (Core Add-in):
You can download and run the Analytics Edge installer to quickly install the add-in. After installing the add-in, click the license button and accept the Terms of Use. Once you do, a 30-day free trial will start for the core add-in.

2. Install the GSC Connector from within Excel:
Now that the Core Add-in is installed, you need to add the GSC connector so you can work with the Search Console API. It’s very easy to install the connectors available in Analytics Edge. Simply click the License button in the Analytics Edge menu and then click the dropdown to add a new connector. Select Google Search and then click Add. Then click Install. Once the connector is installed, a 30-day trial will begin for that connector.

3. Connect Your Google Account:
In order to export data from your GSC properties, you first need to connect your Google Account associated with those properties. Once you set this up, you will not need to do this over and over. And you can add multiple Google Accounts if you have access to GSC properties across various accounts. Click the Google Search dropdown in the Analytics Edge menu and select Accounts. Click Add Account and walk through the process of quickly connecting your Google account. Once you do, you’ll be able to use the API to connect to any property you have access to.

4. Create Your Macro – Exporting All Landing Pages or Queries and Comparing Data Across Timeframes:
Let’s start creating our macro by exporting all landing pages and comparing data across timeframes. In a real-world situation, you would compare the timeframe after a major event (like a broad core update) to the previous timeframe to see the changes per landing page or query. But for this tutorial, we’ll keep it simple. Let’s just pull all landing pages for the last 28 days and compare to the previous timeframe. Once you get the hang of this, you can customize the report for each situation you encounter. Note, if you ever lose the Macro window, just click “Task Pane” in the Analytics Edge menu in Excel. It will show back up on the right side of the spreadsheet. Let’s start creating our macro. Go ahead and click Analytics Edge in the main menu, then Google Search, and then Search Analytics. Name your macro DeltaReport (or whatever you want).

5. Choose The Account and GSC property:
Select the Google account you want to use and then select the GSC property in the site list.

6. Select Fields To Export:
In the available dimensions and metrics list, select page and click “Add” to export all landing pages from Google organic Web Search. Leave the selected metrics as-is (with clicks, impressions, ctr, and position all selected).

7. Leave Filters Tab As-Is, But Review The Settings:
For this tutorial, we’ll leave the filters tab as-is, but note the options you have here while exporting data from GSC. You can filter by page, query, country, device type, search type, and search appearance. You’ll notice the default search type is Web Search. That’s what we want for this specific report, so keep the default settings.

8. Select A Date Range – Comparing Data Across Timeframes Made Easy:
Depending on your situation, select the appropriate timeframe for exporting data. For this tutorial, let’s pull the last 28 days of data and compare to the previous timeframe. Click the Dates tab and simply use the dropdown to select Last 28 Days. To compare timeframes, make sure to select the checkbox for “Compare to” and select a timeframe to compare with. To keep things simple, we’ll just select “Previous period”.

9. Sort By Clicks Or Impressions:
Under the Sort/Count tab, use the dropdown to select a metric to sort the data by. I typically choose clicks in descending order. Make sure to click the button labeled “Descending” to apply the sort preference.

10. Run The Query!
Click OK in the bottom right corner of the wizard to run the query. Depending on how much data needs to be exported, it can take a few seconds (or longer). Once the query has completed, you will see the results highlighted in green. Note, this does NOT show the full results from the export. Analytics Edge just shows a sample of the results and is waiting for more input from you (either to write the full data to a worksheet now or to use the built-in functions to create more advanced reports).

11. Set The Table Name:
Since we’ll be filtering the data we just exported multiple times (by page type), we need to set the table name so we can come back to the full data in future steps. To do this, click the Table dropdown in the Analytics Edge menu and select Table Name. Set the table name to something like “allpages” and click OK. Again, we’ll need this in the future.

12. Write To Worksheet:
Let’s complete the export by writing the data to a new worksheet. In order to do this, click the File dropdown in the Analytics Edge menu and then select Write to Worksheet. Name your worksheet something like “Landing Pages All Data” and then click OK. Analytics Edge will create a new worksheet containing the full export from GSC. Just click the new worksheet to view all of the data. You’ll notice all of the landing pages were exported with columns showing the difference in clicks, impressions, CTR, and position based on comparing the last 28 days to the previous timeframe. Awesome, right? But we’re not done yet. Our macro will be smarter than that. :)

13. Start Filtering Your Data:
Our goal is to create separate worksheets by page type so you can easily analyze each one separately. To keep things simple, let’s say we wanted to break out category pages (/category/), product pages (/products/), and blog posts (/blog/) so we could analyze them separately. Let’s start with category pages. Click the Table dropdown in the Analytics Edge menu and select “Filter”. This menu will enable you to filter the data by any column in the active table. The active table now is “allpages”, which we set up in step 11. Once you click “Filter in the menu”, you can set the filter rules. The column should say “A page”, which will enable you to filter by the column in our active table titled “page”. For Criteria, you have several helpful options, including regex. Yes, you can use regex here if needed, which is awesome. To filter by category pages which contain /category/ in the url, select “Contains” and then enter /category/ in the Value text box. Then click the Add button. Note, you can combine rules here if you want to create more complex filtering options. Click OK to filter the active table. You will see the results again highlighted in green. We’ll write the filtered data to a worksheet in the next step.

14. Write The Filtered Data To A Worksheet:
Just like we did before, let’s write the filtered category data to a new worksheet. Click the File dropdown in the Analytics Edge menu and select “Write Worksheet”. Then name the new worksheet “Landing Pages Category” and click OK. A new worksheet will be created with all of the category page data. At this point, you should have two worksheets, one containing all landing page data and another just containing category page data.

15. Switch The Table Back In Order To Filter Again:
Now we want to filter the full data again for product pages, which contain /product/ in the url. In order to do that, we need to switch the active table back to “allpages”, which contains all of our exported data and not just the filtered category data. If we don’t switch the table name again, then Analytics Edge will use the current active table, which is the category page data. In order to switch the table, click the Table dropdown in the Analytics Edge menu and click Table Name. Click the second radio button to switch to a previously named table and select “allpages”. Now that becomes the active table and we can filter it again.

16. Filter Product Pages:
We’ll use the same approach that we did when filtering the category pages, but this time, we’ll filter by urls containing /products/. After switching the table name, select Table again in the Analytics Edge menu and then Filter. Now enter /products/ in the value field for page. Then click OK. The data will now be filtered by any url with /products/ in it.

17. Write Filtered Data To Worksheet:
Next, we need to write the product filtered data to a new worksheet. Click the File dropdown in the Analytics Edge menu and select “Write Worksheet”. Name the worksheet “Landing Pages Products” and click OK. You will now have a new worksheet with the filtered data. And now you should have three worksheets containing landing page data (full data, category pages, and product pages).

18. Rinse and Repeat For Blog URLs:
Use the same approach to export all data filtered by blog urls (containing /blog/ in the url). First, switch the table name back to “allpages” (see step 14 for how to do this). Then filter the data by any url containing /blog/, and then write to a new worksheet called “Landing Pages Blog”. When you’re done, you should have four worksheets in total with three that contain filtered data (one for category urls, one for product urls, and one for blog urls). And Analytics Edge already took care of comparing data across timeframes and provided difference columns in the worksheets.

Congratulations! You have exported all of your landing pages from GSC, compared data across timeframes, filtered by page type, and then created specific worksheets containing the filtered data. Oh, and now you have a macro using Analytics Edge that you can reuse whenever you want to accomplish a similar task in the future. Just reopen the spreadsheet, save a new file, edit the macro to change the settings like GSC property, click “Refresh All” in the upper left corner of the Analytics Edge menu, and boom, you’re good to go. Time is valuable and this can save you a lot of it in the future…

Beyond The Delta Report: More Functionality = More Advanced Reporting
As I mentioned earlier, Analytics Edge comes with a ton of functionality built-in. You can create advanced reporting by using the various functions available in Analytics Edge when working with data exported from GSC, Google Analytics, and more. So, if you’re feeling ambitious, here are some other things you can try using Analytics Edge and the GSC Connector:

  • Run the same type of report, but for queries instead of landing pages. Then you can analyze drops or surges by query type instead of page type.
  • You can segment by search type to analyze drops and surges for Image Search, Video Search, or the News tab.
  • You can segment by search feature (like AMP, how-to, FAQs, Q&A, reviews, recipes, etc.) Note, you can follow my tutorial for exporting data by search appearance to learn more about that process.
  • And make sure to review all of the functions available in the Analytics Edge menu within the Multiple, Table, and Column dropdowns. For example, you can filter, group, pivot, sort, append, combine, compare, convert, split, and more. Again, Analytics Edge is like a Swiss Army Knife for APIs.

Summary – Automated Delta Reports are Panda Reports on Steroids
It’s always smart to analyze the top landing pages and/or queries when a site sees a big drop or surge in rankings and traffic from Google (due to an algorithm update, site migration, website redesign, etc.) Since the Performance reporting in the GSC UI limits exports by 1K rows, using a tool like Analytics Edge can help you quickly and efficiently export all of your data via the Search Console API.

In addition, Analytics Edge comes with a number of functions for filtering and working with your data to create advanced reports (including comparing data by timeframe). By following this tutorial, you can create a template for quickly exporting data, comparing data across timeframes, filtering by page or query type, and then writing the results to separate worksheets for further analysis. Once you get the hang of Analytics Edge, the sky’s the limit. I think you’ll dig it.

GG

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

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