While this would be very useful for Movement-Insights, from our perspective it's not top priority (unlike, for example, T418032).

Presumably of interest to @Maryana!

I see the following data sets in Big Query:

• wikipedia.org
  • site
  • url
• wikimedia.org
  • site
  • url

@nshahquinn-wmf should we make all of them available, and download what is available in Big Query once and then pulled from the API periodically going forward? Thanks!

We definitely want the wikipedia.org data. But we may not want the wikimedia.org data as I haven't seen any interest in the sites under that domain like Commons and Meta-Wiki. There was some SEO work on Commons last year (T400022) but it seems like the regular GSC data was enough for that.

@SCherukuwada, since you set up the wikimedia.org export, should we pull it into the Data Lake? One goal here is to delete the data in BigQuery after importing it to save on storage costs, so I think the question is largely equivalent to "do we really want to keep wikimedia.org data indefinitely?".

Since the site data is just an aggregation of the url data but doesn't provide the wiki dimension, I suggest we just import the site data and then make a better aggregation inside the Data Platform.

We don't need to touch the API here as the complete GSC dataset is exported to BigQuery on an daily basis. Basically, we want to have an ongoing daily job that exports the site dataset and then deletes it from BigQuery (plus doing the one-time catch-up run from the start of the data in BigQuery).

Thanks @nshahquinn-wmf ! Storing wikimedia data in addition should be trivial, see sizes below.
Do we want to keep any data in BigQuery then? E.g. use it as a short term buffer?

Stats

All data sets have data since 2025-08-27.

wikipedia.org

• site impressions
  • 150.41G rows • 13.52 TB • Last modified Apr 30, 2026, 12:51 AM
• url impressions
  • 468.2G rows • 78.37 TB • Last modified Apr 30, 2026, 1:28 AM

wikimedia.org

• site impressions
  • 5.27G rows • 477 GB • Last modified Apr 30, 2026, 1:53 AM
• url impressions
  • 13.9G rows • 2.51 TB • Last modified Apr 30, 2026, 1:53 AM

Do we want to keep any data in BigQuery then? E.g. use it as a short term buffer?

Yeah, my understanding is that we want to use it as a short-term buffer only, as we're already paying a major BigQuery storage cost which will only increase unless we start purging it. But @JerryWang-WMF, please confirm.

Some more specific thoughts about the import mechanics:

• Google sometimes goes back and revises past data, which is recorded in the ExportLog table. They state on the support page that this is rare, and it has only affected two days of our data since its start on 2025-08-27 (2026-04-23 and 2026-04-24, both revised on 2026-04-27; I don't think we have a way to know the scale of the revision).
  • We could set up a second-pass job that runs after, say, a month and reimports if necessary (and delete the BigQuery data only after that job has run), but it doesn't seem worth the effort.
  • @mpopov do you have an opinion about this since you tried to export GSC data via the API years ago but ran into problems due to Google's data revisions? The BigQuery bulk export feature didn't exist back then so it's possible that they're now more restrained in revising data.
• In addition to the BigQuery storage cost, there's also a query cost. When @santhosh tried importing the data in March, it ended up costing a lot (roughly double the storage cost for that month), so we should be sure we assess the cost of the different data extraction options.
  • @santhosh can you share what method you used and how much of the data you ended up extracting that way?
• There's no reason to import the site_url field as it simply restates the property name from the table name (wikipedia.org or wikimedia.org).
• Similarly, there are a number of is_[search_appearance_type] fields that are deprecated and can be excluded from the import.
• If the import costs look to be large, we could explore excluding the following fields, but these do have meaningful data so we would be cutting off some use cases.
  • the non-deprecated is_[search_appearance_type] fields
  • the query field. This is likely to be the largest single-field, so the savings from excluding it might be very significant, but so might be the loss of utility. Looking at the search performance of different articles (i.e. different values of url) would be our main use case, but there could definitely be interest in look at query patterns as well. @santhosh is there any plan to look at queries as part of your project?
• If resources permit, it would be useful to do some basic transformations on the data during import to make things easier for data users.
  • • Change field names to align with the data modeling guidelines:
      • data_date → date
      • impressions → impression_count
      • clicks → click_count
      • device → device_type (not from the modeling guidelines, but just seems like a much more intuitive name to me)
  • Parse the url field to extract the wiki and page name into separate fields.

I just happened to run across this project (WMF only) which recently used the GSC data in BigQuery to gather a large dataset of question-style search terms. From this document explaining how they generated they dataset, they were very conscious of the BigQuery query costs.

So I think it's a good illustration of both why getting this data into the Data Lake would be very valuable and, more crucially, why we really should not exclude the query field as I mentioned above unless the cost considerations become overwhelming.

@santhosh can you share what method you used and how much of the data you ended up extracting that way?

This the code I used to extract google search data from BigQuery:
https://gitlab.wikimedia.org/santhosh/wiki-search-analytics/-/blob/410246c99b703c5bc9f10dd6c6c573faaf7defd2/src/bq_export_local.py

I did only one test of this script - for a single day day and that itself was quite costly. It exported 50GB and aborted with quota issue. So I did not run it again.

@santhosh is there any plan to look at queries as part of your project?

No. Not as part of content observability project. We are interested only in how search impressions and clicks vary for a given article. But it is possibile that search related WMF projects are interested in raw search queries.

I would very much recommend importing and keeping wikimedia.org data - at least for commons. We're likely going to need to have some visibility into web search metrics around commons.

After evaluating various technical download options and considering cost profiles (thanks @nshahquinn-wmf for all the considerations and references!), we suggest the following approach:

Export process

BigQuery Table
      │
      │  (Scheduled Full BQ export - no extra costs)
      ▼
Google Cloud Storage (GCS)
      │
      │  (Hive external table pointing to GCS - download costs)
      ▼
Hive External Source Table
      │
      │  (dbt incremental model — appends only new dates)
      ▼
Hive Target Table

Exported data

Full BigQuery (BQ) table exports for

• wikipedia.org url impressions
• wikimedia.org url impressions

wikipedia.org site impressions and wikimedia.org site impressions will be derived from these on the data platform.

We are not considering Export Table logs (retrospective changes) ingestion for now.

Cost considerations
Projecting the BQ tables, i.e. omitting columns, to GCS requires an export query which is billed on the processed data which is probably more expensive than the costs for the extra download data, hence full table export.

One time cost:
Downloading 2.51 TB (wikimedia.org url impressions) + 78.37 TB (wikipedia.org url impressions) = ~85TB (meanwhile) downloaded once at ~$6,350.

Ongoing operations

• BQ table data will be retained for 1 week
• GCS export data will be retained for 1 week of data

@nshahquinn-wmf can you confirm that the approach makes sense?
@JerryWang-WMF for cost tracking

Thank you for putting this together! Yes, it makes good sense to me.

We are not considering Export Table logs (retrospective changes) ingestion for now.

I agree; that seems like a ton of extra work and substantial extra storage cost for what is probably a very minor benefit.

In the presumably very rare case that Google does a major data revision, we can just note the issue and, if necessary, not use the affected data, just like we'd do for a data issue in, say, pageviews. In that situation, we'd also still be able to look at Google Search Console to see the post-correction aggregates.

Also, the reasoning for doing a full BigQuery export makes total sense to me. But, if it's possible to reshape the table a bit during the loading to the Hive target table, it would still be nice to do the following:

In T420996#11880822, @nshahquinn-wmf wrote:

• • Change field names to align with the data modeling guidelines:
    • data_date → date
    • impressions → impression_count
    • clicks → click_count
    • device → device_type (not from the modeling guidelines, but just seems like a much more intuitive name to me)
• Parse the url field to extract the wiki and page name into separate fields.

Just noting here that @Ahoelzl confirmed in a Slack conversation that the destination Data Lake table will have the complete dataset (all columns) from BigQuery.

TIL

Google Search Console's bulk export writes two separate tables per property, and they are not aggregations of each other. The site-level table includes impressions and clicks for anonymized queries (long-tail queries Google strips from URL-level data for privacy). The URL-level table drops those rows entirely.
So if you SUM(impressions) from the URL-level table and group by (data_date, site_url, country, search_type, device), you will systematically get fewer impressions and clicks than the same aggregation over the site-level table. For high-traffic Wikipedia/Wikimedia properties, the gap from anonymized queries can be substantial — often double-digit percentages.
This is the difference Google warns about in their export docs: site-level totals are authoritative; URL-level totals undercount.

(^^ incorrect statement by Google's console AI)

also

Site-Level (Property) Aggregation: Groups data by the entire GSC Property. If three of your pages appear for one query, Google counts only one impression for the site. This is the default for reports grouped by Query, Country, or Device.

URL-Level (Page) Aggregation: Groups data by individual Canonical URLs. If three of your pages appear for one query, Google counts three impressions—one for each specific URL. This aggregation occurs whenever you filter or group by "Page" or "Search Appearance".

This implies we need to export all 4 tables: wikimedia | wikipedia x site | url.
For metrics reporting use cases we'll start with the site tables and then add the url tables for full data analysis.

The site-level table includes impressions and clicks for anonymized queries (long-tail queries Google strips from URL-level data for privacy). The URL-level table drops those rows entirely.

Are you sure this is true? I can't find it mentioned anywhere, and Google's schema documentation lists the is_anonymized_query field for both tables, without saying anything about anonymized queries being dropped.

Site-Level (Property) Aggregation: Groups data by the entire GSC Property. If three of your pages appear for one query, Google counts only one impression for the site. This is the default for reports grouped by Query, Country, or Device.

URL-Level (Page) Aggregation: Groups data by individual Canonical URLs. If three of your pages appear for one query, Google counts three impressions—one for each specific URL. This aggregation occurs whenever you filter or group by "Page" or "Search Appearance".

But this is true (confirmed on this Google doc page). Good catch! And it alone seems like reason enough to also load the site_impression tables.

Thanks @nshahquinn-wmf for double-checking.
You are right the first statement regarding supposedly dropped long-tail queries is wrong (Google's console AI fooled me ... shame on me).
The second statement however is correct, both tables have different deduplication granularity, with url-level counts aggregated per site / property being higher.

For the record, this data is now being automatically loaded into wmf_raw.google_search_console_{site|url}_impression (but note T434625). The data for day D is available in the Data Lake on either D+4 or D+5. The data is generally available in BigQuery on D+2, with the remaining delay being an effort to account for Google possibly revising the data.

@Ahoelzl @Antoine_Quhen this schedule actually doesn't work for our metric reporting needs. The current expectation from leadership is that metric data for a given Monday–Sunday week is available by the following Wednesday at the latest (although this might change as the reporting schedule is under discussion). With the current schedule, Google Search data would not be available until (I think) Friday, which is definitely too late.

I just rechecked and there have still been only been two data revisions since we started exporting the data (2026-04-23 and 2026-04-24, both revised on 2026-04-27; we don't have any way to know the scale of the revisions).

Given that, I think the best tradeoff is to load the data from BigQuery as soon as it becomes available, even though that means missing any data revisions that happen in the first 2-3 days (just like how we're already missing any revisions that happen after that ).

Query in BigQuery to see log of data revisions:

SELECT *
FROM `search-console-export-470421`.searchconsole_wikipedia_org.ExportLog
WHERE epoch_version > 0

In T420996#12206331, @nshahquinn-wmf wrote:

Given that, I think the best tradeoff is to load the data from BigQuery as soon as it becomes available, even though that means missing any data revisions that happen in the first 2-3 days (just like how we're already missing any revisions that happen after that ).

@Ahoelzl should I file a separate ticket for this request to change the loading schedule?

@Ahoelzl said he's just waiting on a definite statement of what we want the loading schedule to be.

It just so happened that Google had a data problem and did a backfill for Aug 13-17, so I'm going to look at the impact of that (since I happen to have pre-correction data saved) and check with stakeholders. Then I'll follow up with @Ahoelzl about technical constraints and file a separate task for whatever we decide.

@nshahquinn-wmf looks like we need options here. Would an immediate daily update paired with a 2-4 days backfill make sense? That way we could ingest recent data timely, but have accurate data long-term. We can evaluate the technical effort.

@Ahoelzl did you see my proposal T436193? Let's discuss there 😊