1. Home
  2. Content Marketing
  3. SEO reporting with Google Big Query & Data Studio

SEO reporting with Google Big Query & Data Studio

There appears to have been a general move amongst SEOs to move their client reporting to Google Data Studio over the past couple of years, so we’ve made the decision to follow this trend by retiring our own internal reporting section and simply add Google Big Query to the Integrations section of the platform, as this is the first step which will allow you to pull data from there into Google Data Studio and then you will have the ability to quickly generate visualisations in your reports like these 👇🏻

Google Data Studio – sample SEO widgets

So the simple steps to get this up and running are as follows:

  1. Setup a Big Query project
  2. Create a Big Query service key
  3. Connect that BQ project to the platform in any of your projects in your Authoritas account from the Settings > Integration module.
  4. Select the specific projects you want to be synchronised with Big Query or just choose ‘All Projects’ and we’ll send everything across.
  5. Go to Google Data Studio, search for and copy one of our publicly accessible reporting templates.
  6. Change the data source to your own Big Query project.
  7. Make any required adjustments to the report (e.g. adding commentary, filtering tables, etc).

1. Setup a Big Query project

Setting up a Big Query project is a quick and painless process. Big Query also has a free tier (overage pricing beyond the limits of the free tier is based on the queries you run and the amount of data you store – at the time of writing, the first 1TB of data processed with BigQuery each month is free – but please check Google’s latest pricing for Google Big Query).

blank
Big Query documentation

If you haven’t used Big Query before, you can follow the above links from the above BQ documentation page or you can go directly to this URL: https://console.cloud.google.com/bigquery.

Then you’ll simply need to setup a new project.

blank
Big Query – create new project

NB: you only need to setup one Big Query project – all your individual projects will show up as ‘data sets’ underneath that project – as indicated here: 👇🏻

blank
Switch datasets under your Big Query project

2. Create a Big Query service account

In order for the Authoritas platform to be able to access your Big Query project, we will need something called a service account, together with a key file. You should only need to create only one each of these!

(a) Login to your Google Console

(b) Create a Service Account

  • Go to IAM & Admin > Service Accounts and follow the wizard to create a service account:
blank
Google Cloud Platform > IAM (Identity & Access Management) & Admin > Service Accounts
blank
IAM > Create Service Account
  • Just provide a name and (optionally) a description and grant it access to your project with a role of ‘BigQuery user’. Google will automatically create the Service account

(c) Create a key file

blank
Service Account > Keys
  • Click on the Keys tab and click on ‘Add key’. 
  • Leave ‘JSON’ selected and click on ‘CREATE’. This will download a JSON file to your machine.

3. Connect that Big Query project to Authoritas

Click on the Settings link in the header and then the Integrations module from the left-hand side Navigation…

blank
Settings > Integrations

…and then you’ll see this Integrations screen ☝️:

3. Select your projects to send over to Big Query

Underneath ‘Google Big Query’, click on the ‘Authorise Now’ button and this dialog window will appear:

blank
Configure Big Query Integration > All Projects

You can either upload the JSON file you created in Step 2(c) and hit ‘Save’ or you can select one or more Authoritas projects to send across to Big Query.

If you click on ‘Select Projects’ like so…

blank
Configure Big Query Integration > Select Projects

…it will default to selecting just the project you have selected in the platform, but you can choose here to synchronise your Authoritas account with your Big Query project for any number of Authoritas projects (there is currently no limit on the amount of data you send across, but you may want to check the Big Query costs for storing and querying your datasets first, if you are planning on sending across a lot of data).

To proceed, you will also need to upload the JSON file you created in Step 2(c). Once you’ve followed these steps, we’ll start to send the following data from Authoritas to your Big Query project:

  • Project settings
  • Keyword Ranking
  • Market Winners (Share of Search)
  • Google Search Console
  • Google Analytics
  • Crawl data
  • Backlinks

The above data will show up in the following tables:

Project Settings

blank

Keyword Ranking

blank
blank

Google Search Console

blank

Google Analytics

blank

Crawl data

blank
blank

NB: The syncing works from Authoritas => Big Query. If you make changes in the Authoritas platform, such as renaming your Authoritas project, this won’t be reflected automatically in Big Query. Similarly, if you stop pushing the data over to Big Query from Authoritas for any reason, the dataset will still be present in your Big Query project until you manually delete it from the Big Query console.

Customers with current Big Query integrations:

If our Engineering team has already got a custom Big Query integration up and running for you, we would recommend that you still follow the above steps to create a new service key for a new instance. We will then send all the above data over to that instance and you can compare the data in both Big Query projects, until you are happy that the standard format of data is fulfilling your reporting needs; if anything is missing from the standard integration detailed above that you are currently seeing in your custom integration, please let us know (product@authoritas.com) and we will endeavour to include this data in the standard integration too.

4. Copy a Google Data Studio template

Open up one of our demo templates and then click on File > Make a copy…

blank

5. Change the data source to your own Big Query project

Change the data source to your own data source in your Big Query project.

blank
Change Data Source

NB: you will need to do with every data source listed.

6. Customise the report

That’s it! Your report should now be populated with data from your own Big Query project.

What you can do now is, of course, entirely up to you. You may, for instance, want to remove to replace some of the sample widgets or tables or whole pages; you may want to add your own visualisations, make use of community visualisations or add some perceptive insights or commentaries to the report. Google Data Studio is a very flexible way of creating SEO reports. Combine that with the power of Google Big Query and we hope you agree that it makes for a powerful way to generate client reports for your SEO campaigns. Hopefully, you will agree that this reporting solution provides the following key benefits:

  • You can create better, more insightful customised reports using our Google Data Studio templates or your own
  • You will now have access to your platform data at a granular level
  • The data will be synchronised with changes you make to the platform so it’s always up-to-date
  • You can now mash-up Authoritas SEO data with other internal business data or other external SEO tools
  • You can take advantage of Google’s free Big Query account – so any incremental costs should be negligible

Further reading:

Data Studio: https://datastudio.google.com/u/0/

Big Query: https://cloud.google.com/bigquery.

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu