Setup guide · Google Search Console

Google Search Console in Dataki, through the bulk data export

Search Console's bulk export writes each day's search performance into BigQuery: every query and every page, not the top 1,000 rows the interface shows. Dataki connects to that dataset and answers questions about clicks, rankings and pages.

  1. Search Console
  2. Bulk data export
  3. Dataki
Setup
About 10 minutes, then up to 48 hours for the first export.
Cost
The export is free, but needs a Cloud project with billing. BigQuery storage and queries are billed beyond the free tier.
History
From the day you set it up. Earlier data stays in Search Console.
You need
Owner of the Search Console property, and a Cloud project where you can grant IAM roles.
Open Dataki Go to the steps

01Before you start

  • Owner of the Search Console property. Other permission levels cannot set up the export.
  • A Google Cloud project with billing. The BigQuery sandbox is not supported.

02Set it up

5 steps, about 10 minutes

  1. 01

    Prepare the Cloud project

    In the project, enable the BigQuery API and the BigQuery Storage API.

  2. 02

    Let Search Console write to it

    Open IAM & Admin › IAM › Grant access, and add the principal search-console-data-export@system.gserviceaccount.com with two roles: BigQuery Job User and BigQuery Data Editor.

  3. 03

    Start the export

    In Search Console, open Settings › Bulk data export. Enter the project ID (the ID, not the project number), a dataset name and a location, then Continue. The dataset name starts with searchconsole, and each property needs its own dataset.

  4. 04

    Wait for the first export

    The first export arrives within 48 hours and runs daily after that. It writes three tables:

    • searchdata_site_impression: performance by property, with query, country, device, impressions, clicks and sum_top_position.
    • searchdata_url_impression: the same by page, with url, search appearance flags and sum_position.
    • ExportLog: one row per successful export.
  5. 05

    Connect the dataset in Dataki

    Open app.dataki.ai/connect and, under Linked Google Cloud Projects, choose Link New Google Project. Sign in with a Google account that can manage IAM in the project holding the data, and pick that project. Dataki creates a service account named Dataki in it with two roles, BigQuery Data Viewer and BigQuery Job User: it can read tables and run queries.

    The project's datasets then appear among your sources. Pick searchconsole. Each dataset is one source in Dataki, and the free plan includes one.

03Ask it

Your first questions

Two first questions, each with a query that answers it.

Which queries brought the most clicks in the last 28 days, and where do we rank for them?

Positions are stored zero-based and summed, so the average is SUM(sum_top_position) / SUM(impressions) + 1. Anonymized queries count in the totals but have no text, so they are left out here.

BigQuery · searchconsole 
SELECT
  query,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  ROUND(SUM(sum_top_position) / SUM(impressions) + 1, 1) AS avg_position
FROM `your-project.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
  AND NOT is_anonymized_query
GROUP BY query
ORDER BY clicks DESC
LIMIT 25

Which pages lost the most clicks compared with the 28 days before?

BigQuery · searchconsole 
SELECT
  url,
  SUM(IF(data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY), clicks, 0)) AS last_28_days,
  SUM(IF(data_date < DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY), clicks, 0)) AS previous_28_days
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 56 DAY)
GROUP BY url
HAVING previous_28_days > 0
ORDER BY last_28_days - previous_28_days
LIMIT 25

04Worth knowing

What you will run into

Every metric is a sum
Rows repeat the same query, page or country many times, so clicks and impressions must always be summed. Dataki does this, and the SQL beside each answer shows it.
Dates are Pacific Time
data_date follows Search Console's own day, which is Pacific Time, not your time zone.
Leave the tables as they are
Changing a table's schema, or setting a partition expiration under 14 days, breaks the export. Failed days are retried for about a week, and a month of failures stops the export.
Queries are billed to your project
Dataki runs each query in the project that holds the data, and Google bills that project for it. The first 1 TiB of queries each month is free. A question that names a period lets the query read only those days, which is what keeps it small.
Your organization may block the key Dataki needs
Linking a project creates a key for the Dataki service account. Google Cloud organizations created on or after 3 May 2024 block key creation by default, through the iam.disableServiceAccountKeyCreation policy. If linking fails there, an organization admin can lift that policy for this one project. Projects outside an organization are not affected.

Checked against

Last checked 15 September 2026.

Related guides

Free while we are in beta

Connect Google Search Console. Ask it something.

Once the data is where Dataki can read it, the first answer is a question away, and anything worth keeping becomes a dashboard with a link that stays live.

Open Dataki Stuck? Ask us
One data source
Free tier. Connect a second on any paid plan.
Read-only
Every query runs read-only. Dataki cannot change your data.
No card
There is nothing to cancel if you stop.