BigQuery

A BigQuery destination loads your data into tables in your own Google Cloud project, through a Google account you connect. iSearchFrom creates the dataset and tables if they don’t exist, keeps their schema current, and appends new rows on every run.

What you need

• A Google Cloud project with BigQuery enabled.
• A Google account connected under Settings → Integrations with BigQuery access. Google’s consent screen asks to view and manage your BigQuery data; this is the narrowest Google permission that can create tables and load files.
• That Google account needs these roles in the project (or equivalent permissions):
  • BigQuery Job User (roles/bigquery.jobUser) on the project — to run load jobs.
  • BigQuery Data Editor (roles/bigquery.dataEditor) on the dataset — to create and update tables. If the dataset doesn’t exist yet, grant it on the project (or grant BigQuery User) so the dataset can be created.

Settings

Setting	Notes
Project ID	The Google Cloud project id, e.g. acme-analytics.
Dataset ID	Letters, digits and underscores, e.g. isearchfrom. Created on the first run if it doesn’t exist.
Location	Where a new dataset is created — US by default, or e.g. EU, europe-west1. For an existing dataset, use its location.
Table prefix	Put in front of every table name. isf_ by default; may be empty.

Test connection checks that the connected account can reach the dataset — or, if it doesn’t exist yet, the project it will be created in.

Tables

Each dataset gets its own table, named <prefix><dataset> with . replaced by __:

Dataset	Table (default prefix)
position_tracker.results	isf_position_tracker__results
position_tracker.checks	isf_position_tracker__checks
ad_tracker.ads	isf_ad_tracker__ads
maps.results	isf_maps__results
marketplace.results	isf_marketplace__results
ai_visibility.responses	isf_ai_visibility__responses
ai_visibility.mentions	isf_ai_visibility__mentions
search_history	isf_search_history
search_history.results	isf_search_history__results
alerts	isf_alerts

Tables are partitioned by day on measured_at — filter on it to keep queries cheap. Each column carries its description from the dataset reference.

Column types map like this:

Dataset type	BigQuery type
string	STRING
integer	INT64
float	FLOAT64
boolean	BOOL
timestamp	TIMESTAMP
json	JSON
string_array	STRING, mode REPEATED

Every column is NULLABLE in BigQuery, even ones that are never empty in the export. A column added later can only be added as nullable, so every table follows the same rule.

Tables always have every column of the dataset. If you picked columns, the ones you left out are NULL in the rows loaded.

Schema changes

When a dataset gains a column, the next run adds it to the table automatically. Columns are never renamed, removed or retyped, so views and queries you build on these tables keep working. Rows loaded before a column existed have NULL in it.

You can add your own tables and views to the same dataset. Don’t change the exported tables’ columns yourself.

How loading works

Data is loaded with BigQuery load jobs, not streaming inserts. Batch loading is free in BigQuery — you pay Google only for storage and your own queries — and each load job is atomic: a chunk is either fully in the table or not at all.

Every load job has a fixed id derived from the run, so a retried run finds the chunk it already loaded instead of loading it twice.

De-duplicating

A single run never loads a row twice. Rows can repeat across runs when:

• you backfill a date range with Run now, re-sending rows the table already has, or
• a run failed part-way, and the next run delivered the same window again.

Repeated rows have the same row_id. Query through a de-duplicating view:

CREATE OR REPLACE VIEW `acme-analytics.isearchfrom.position_results` AS
SELECT *
FROM `acme-analytics.isearchfrom.isf_position_tracker__results`
WHERE TRUE
QUALIFY ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY measured_at DESC) = 1;

Or clean the table itself after a backfill. Pause the destination first so no load runs while the table is replaced, and resume it afterwards:

CREATE OR REPLACE TABLE `acme-analytics.isearchfrom.isf_position_tracker__results`
PARTITION BY DATE(measured_at)
AS
SELECT *
FROM `acme-analytics.isearchfrom.isf_position_tracker__results`
WHERE TRUE
QUALIFY ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY measured_at DESC) = 1;

Example query

Average position per keyword over the last 30 days:

SELECT keyword, domain, ROUND(AVG(position), 1) AS avg_position, COUNT(*) AS checks
FROM `acme-analytics.isearchfrom.position_results`
WHERE measured_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND position IS NOT NULL
GROUP BY keyword, domain
ORDER BY keyword, avg_position;

Troubleshooting

Code	Likely cause
reconnect_required	The Google connection expired or was revoked. Reconnect it under Settings → Integrations; the destination resumes by itself.
access_denied	The connected Google account lacks a role listed above, or BigQuery is not enabled in the project.
not_found	The project doesn’t exist or isn’t visible to the connected account.
destination_rejected	BigQuery refused a load — often a location that doesn’t match the existing dataset, or a table whose columns were changed by hand.

See all error codes.

Related docs

• Exporting your data
• Dataset reference