Table names and location
Search Console bulk data exports create the following tables in your BigQuery project:
Dataset name: searchconsole (note that this is customizable, so you or someone else may have chosen another name during set up)
List of tables:
- searchdata_site_impression: Contains performance data for your property aggregated by property.
- searchdata_url_impression: Contains performance data for your property aggregated by URL.
- ExportLog: Contains information about each successful export to one of the previous data tables. Note that Search Console typically exports to those tables separately. Unsuccessful export attempts are not recorded here.
Bulk data exports: Tables and tips - Google Search Console Training
Table save schedule
Search Console exports bulk data once per day, though not necessarily at the same time for each table.
If Search Console encounters a transitory error, such as a connection problem, it will keep retrying immediately without triggering any notifications.
If a non-transitory error occurs during a write attempt—for example a permissions error—Search Console won't try exporting the table again until the next day's scheduled export. Search Console will retry exporting the missed day's data for about a week, then stop trying to save data for that day. So, for example, if Search Console tries to save data for day 1 but encounters an authentication error that is not fixed until day 9, data for days 1, 2 will not be exported, but the remaining days will be added when access is regained on day 9.
If Search Console must revise previously saved data because of some processing error that was fixed, all changes will be atomic, meaning that all data for that day will be updated in a single commit by Search Console. Fixing old data doesn't happen often, and won't trigger an email, but will be noted in the export log.
Performance data is accumulated by Search Console incrementally, resulting in table rows with repeated keys. This data is not compressed before exporting it to your tables. Therefore, you should almost always aggregate all your metrics.
Data retention time
Tables are retained forever, by default, as are partitions, subject to any global defaults set by your Google Cloud project or organization.
If you want to avoid accruing data indefinitely, we recommend putting an expiration on the partition after an acceptable period of time: a month, six months, twelve months, or whatever is reasonable for your needs and the amount of data that you accrue. Putting an expiration date on the entire table is probably not what you want, as it will delete all your data.
Table schema
Here are the schema for the tables exported by Search Console. All tables have a prefix of <your_project_ID>.searchconsole.
Table searchdata_site_impression
This table contains data aggregated by property. The table contains the following fields:
- data_date: The day on which the data in this row was generated (Pacific Time).
- site_url: URL of the property. For domain-level properties, this will be sc-domain:property-name. For URL-prefix properties, it will be the full URL of the property definition. Examples: sc-domain:developers.google.com, https://developers.google.com/webmaster-tools/
- query: The user query. When is_anonymized_query is true, this will be a zero-length string.
- is_anonymized_query: Rare queries (called anonymized queries) are marked with this bool. The query field will be null when it’s true to protect the privacy of users making the query.
- Country: Country from where the query was made, in ISO-3166-1-Alpha-3 format.
- search_type - One of the following string values:
- web: The default ("All") tab in Google Search.
- image: The "Image" tab in Google Search.
- video: The "Video" tab in Google Search.
- news: The "News" tab in Google Search
- discover: Discover results
- googleNews: news.google.com and the Google News app on Android and iOS
- device: The device from which the query was made.
- impressions: The number of impressions for this row.
- clicks: The number of clicks for this row.
- sum_top_position: The sum of the topmost position of the site in the search results for each impression in that table row, where zero is the top position in the results. To calculate average position (which is 1-based), calculate
SUM(sum_top_position)/SUM(impressions) + 1
Table searchdata_url_impression
This table contains data aggregated by URL. The table contains the following fields:
- data_date: Same as above.
- site_url: Same as above.
- url: The fully-qualified URL where the user eventually lands when they click the search result or Discover story.
- query: Same as above.
- is_anonymized_query: Same as above
- is_anonymized_discover: Whether the data row is under the Discover anonymization threshold. When under the threshold, some other fields (like URL and country) will be missing to protect user privacy.
- country: Same as above.
- search_type: Same as above.
- device: Same as above.
- is_[search_appearance_type]: There are several boolean fields used to indicate search appearance type, such as is_amp_top_stories, is_job_listing, and is_job_details. A field will be true if the row in question appears for the specific rich result.
- impressions: Same as above.
- clicks: Same as above.
- sum_position: A zero-based number indicating the topmost position of this URL in the search results for the query. (Zero is the top position in the results.) To calculate average position (which is 1-based), calculate
SUM(sum_position)/SUM(impressions) + 1.
Table ExportLog
This table is a record of what data was saved for that day. Failed exports are not recorded here. Learn more about tracking failures. The table contains the following fields:
- agenda: Which type of data was saved. Currently the only value is SEARCHDATA.
- namespace: Which table was saved to. Search Console typically exports data separately for each table, so each day typically has two export rows.
- data_date: Date of the click or impression, in Pacific time. This is the partition date used by the tables.
- epoch_version: An integer, where 0 is the first time data was saved to this table for this date. In the rare case that Search Console needs to go back and adjust previous data, perhaps because of a data anomaly, this value will be incremented by 1 each time the data is updated for that table and date.
- publish_time: The Pacific Time when the export completed.
