How to Measure Anonymised Queries in BigQuery

Here’s one of my favourite SQL queries for quickly measuring how many clicks and impressions reported by Google Search Console come from anonymised queries.

What are anonymised queries?

For those of you who don’t know Google will not show you every single query your site appears for as a proportion will be anonymised as part of privacy filtering.

Google’s criteria for which queries are anonymised:

Anonymized queries are those that aren’t issued by more than a few dozen users over a two-to-three month period. To protect privacy, the actual queries won’t be shown in the Search performance data.

When using the GSC UI you might have noticed when you apply querying filtering you’ll see discrepancies between the total and sum of clicks, impressions for the the listed queries. This is because the aggregate data shown in the chart will include anonymised queries, but will be removed as soon as you apply query filtering.

So how big a problem is this? A piece of research by Ahrefs in 2022 found that almost half of GSC clicks come from hidden terms. Interestingly, the study found that the data generally gets better with more traffic, but gets much worse after 10 million clicks. For small sites that receive traffic from lots of low volume queries and (like this one 😅 ) there’s a good chance a lot of your clicks are coming from anonymised queries.

How can you measure how many clicks are coming from anonymised queries?

Now you can calculate it manually using Looker Studio or pulling the data via the API and comparing the total clicks vs the sum of clicks filtered by query. However, there’s a much more accurate and easy approach now we have the BigQuery bulk export for GSC data. This will give you the most complete view of your data, with studies finding the BQ export can show a greater proportion of clicks, impressions, queries as well as less anonymised queries!

With the bulk export, anonymised queries are treated differently than the API or UI. Instead of being filtered out these queries are still included in the table as NULL values. While we still don’t know what the query is, we can access the metrics like clicks and impressions.

This means we can easily calculate the proportion of clicks, impressions and queries that are anonymised using a SQL query:

WITH query_summary AS (
    
SELECT
        CASE
            WHEN is_anonymized_query IS TRUE THEN 'Anonymised queries'
            ELSE 'Regular queries'
        END AS query_type,
        COUNT(*) AS query_count,
        SUM(impressions) AS impressions,
        SUM(clicks) AS clicks
    FROM
        `yourproject.searchconsole.searchdata_site_impression`
    GROUP BY
        query_type
)

SELECT
    query_type,
    query_count,
    impressions,
    clicks,
    ROUND((query_count * 100.0) / SUM(query_count) OVER(), 2) AS query_percentage,
    ROUND((impressions * 100.0) / SUM(impressions) OVER(), 2) AS impressions_percentage,
    ROUND((clicks * 100.0) / SUM(clicks) OVER(), 2) AS clicks_percentage

FROM
    query_summary;

Breaking down the SQL query

Here’s what we’re doing with this SQL query.

1. First part (WITH query_summary):
   • Looks at your search console data
   • Splits queries into two groups: “Anonymised queries” and “Regular queries”
   • Counts how many queries are in each group
   • Adds up all impressions and clicks for each group
2. Second part (final SELECT):
   • Takes those results and calculates percentages:
     • What percentage of total queries are anonymized vs regular
     • What percentage of total impressions come from each type
     • What percentage of total clicks come from each type
   • Rounds all percentages to 2 decimal places

The end result will show you:

• How many anonymous vs regular queries you have
• Their impressions and clicks
• What percentage each type represents of your total queries, impressions, and clicks

Here’s a look at the output:

As you can see, GSC is hiding the vast majority of queries that are driving clicks to this site. Now time to check it out on your own site!

Taking the query further you could analyse by country to see if Google is anonymising queries in more locations than others.