Stuifbergen.com

Analytics & Growth Hacking

  • About
  • Contact
  • Professional Services

Find me here

  • Mastodon (social)
  • Mastodon (measure)
  • No more Twitter
You are here: Home / Blog / Prepare your cloud project for “Bulk data export” Google Search Console

Prepare your cloud project for “Bulk data export” Google Search Console

2 March 2023 by jules Leave a Comment

Google released the possibility to export your Search Console data to BigQuery recently. Totally awesome! Now we don’t have to use the API or 3rd party ETL tools anymore.

In this post I’ll give you a script to create and prepare your Cloud Project for this.

But first

One caveat: you can only add 1 (one) search console property data export to a cloud project.

If you only have 1 property: no problem, just plug the data in your main Cloud Project and you’ll be fine.

If you have multiple properties, a best practice is to create a Google Cloud Project for every search console project.

Steps to prepare the cloud project via a script

The export needs

  • a project
  • the BigQuery API enabled
  • permissions given to an existing Google service account

Simple enough if you follow the steps in the support article.

Even simpler if you use the cloud shell

Open the cloud shell via the button

In the shell that appears, paste the script below (line by line)

# REPLACE this by your project name
export PROJECT_ID=ga4-nl-gsc
export ORG_ID=1057498872770


# create project
gcloud projects create $PROJECT_ID --organization=$ORG_ID


# set project
gcloud config set project $PROJECT_ID

# enable bq api
gcloud services enable bigquery.googleapis.com


# grant serviceaccount access to BQ user

for ROLE in bigquery.jobUser bigquery.dataEditor ; do

    gcloud projects add-iam-policy-binding $PROJECT_ID \
        --member=serviceAccount:search-console-data-export@system.gserviceaccount.com \
        --role=roles/$ROLE
done

Steps for the script

  1. edit the first lines of the script
    • replace PROJECT_ID by the name of your new project (or existing one)
    • replace ORG_ID by the ID of your organisation
  2. if you already have a project, skip the “gcloud projects create” line
  3. paste in the rest of the script to enable the API and grant access to the service account

Add a Billing account to the project

You will need a billing account associated with the project. You can do that via the interface, or if you’re feeling l33t:

# still in the same cloud shell..

export BILLING_ACCOUNT=$(gcloud alpha billing accounts list | grep ^ACCOUNT_ID | cut -f2 -d' ')

if test -z $BILLING_ACCOUNT ; then
  echo "no billing account found"
else
  gcloud alpha billing projects link $PROJECT_ID --billing-account $BILLING_ACCOUNT
fi

Steps after the script

  1. go to https://search.google.com/search-console/settings/bulk-data-export
  2. choose your property
  3. Fill in the project ID and click Continue

Then.. wait up to 48 hours, and data will be rolling in

You can run gcloud alpha bq datasets list to check if the dataset is created succesfully.

Don’t forget

When the data export setup is done, read the pages in the help center to know what to do next, how to troubeshoot errors, and how to set expiry for data (you know.. all this data storage is going to add up),

Thanks for reading!

Let me know via a comment if you want to know more, and share the article if you like it.

Related posts:

Visualize Snowplow data with Google Data Studio graphTrack, Store and Analyze granular Page Performance data: a practical guide Making sense of Event Parameters in GA4 Google Analytics 4 truncates page location

Filed Under: Blog Tagged With: big query, data warehousing, google cloud, howto, search console

Liked this post?

Buy Me a Coffee

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

  • Analyze AB tests in GA4 via Big Query
  • How to make sure your GA4 events tables do not expire
  • Prepare your cloud project for “Bulk data export” Google Search Console
  • Making Sense of the GA4 Configuration Tag
  • Using Big Query to calculate DAU / MAU

Need help?

  • Contact me

Search

© Copyright Jules Stuifbergen · Powered by Genesis ·