Check out the latest version of our comprehensive SEO report. Version 1.1. contains two new reports that will help you report on SEO performance overall and at tag group level and discover opportunities to rank for valuable Universal SERP features.
Google Big Query Integration
We help you overcome the obstacles to using Big Query for your next SEO project
What are the obstacles to using Big Query for your next SEO project?
Well, at first it can be a little daunting. The interface is definitely built for technically minded people. But don't worry, we fully automate the whole process for you.
You want to use Google Big Query but don’t have the engineering resources or technical know-how in-house or budget to outsource?
Don't worry we can take care of it for you.
You’re not sure how to get all the SEO and business data you need into your Big Query dataset and then how to keep it up-to-date?
You authorise connections to your data and we import it all for you automatically and keep it up to date.
Do you need technical help to build the Data Studio dashboards on top of Big Query?
You can just copy and edit one of the many Google Data Studio SEO templates we've already built, or, we can build a custom one for you.
The 4 major challenges in using Big Query for SEO automation
Data Ingestion
We make it so easy to connect to your analytics and search console data and merge it with rankings, backlinks, share of search and SERP data.
Data Maintenance
We automatically update Big Query as your SEO data sources are refreshed. We even stream data that changes more frequently to keep our SEO platform and your Big Query data in sync.
Data Exploration
You do not need to write a single SQL statement. Not one word. We’ve normalised the data to make it easy to get the views you need.
Data Visualisation
Don’t spend days putting together custom dashboards in Data Studio or PowerBI or Tableau. We’ve already done the hard yards, so just copy, paste and (if you like) edit our templates for eCommerce, MNEs, Enterprise SEO, SEO agencies and local search.
Is Big Query the answer to my SEO team’s problems?
Why might you want to use Big Query for SEO? Well, it will almost certainly solve a large number of common issues that enterprise SEO teams encounter.
You’ve reached the limits of Excel or Google Sheets
Millions of keywords to analyse and need to query it quickly
Your existing Google Data Studio reports are running slowly
Your existing dashboards lack vital SEO data sources
Data collection and maintenance is painful and costly
How do I get my SEO data into Big Query?
Well you could manually import it yourself, or, import it using each data provider's API. Or, you could connect our platform to Big Query and press a button!
We connect to all your essential SEO data sources
Keyword research and keyword rankings
Full SERP results for every keyword rank check ever
Website crawl issues
Backlink data for you and your competitors
Google Search Console and Google Analytics
How do I explore and visualise the data in Big Query?
Do I need to learn SQL?
Well the good news is, you do not need to learn SQL (although for some advanced data manipulation you will find this useful) as we prepare everything for you.
You can easily explore the data by viewing the schema of a table, seeing a preview of the top 100 records, or exploring it in Google Sheets.
If you know a little simple SQL then you can run some queries to get exactly the data you need and download it to .csv files and Excel.
Perhaps though, the easiest and the best way to explore it is by using Looker (Google) Data Studio.
Why connecting Big Query with Google Data Studio is perfect for Enterprise SEO teams
Whether you are running one large site or lots of large sites, chances are high that your market is highly competitive and you need to be able to extract insights easily from billions of rows of data.
Data Studio gives you an opportunity to do this and query billions of rows in Big Query with sub-second response times.
Choose one of our standard SEO templates to get you started
Blend different data together to create new insights
FAQs about using Big Query for SEO?
What are the limitations of Excel or Google Sheets – what are the alternatives?
Both Microsoft Excel and Google Sheets are excellent spreadsheet packages and are extensively used on a daily basis by the SEO community at large.
The trouble is there’s only so much data wrangling you can do in either software package before you start to run into issues with data limits.
An excel spreadsheet can handle 1,048,576 rows and 16,384 columns at a time. Google sheets has a cell limit of 10 million cells or 18,278 columns. Big Query is a great alternative if you’ve started to reach these limits as it is designed to handle big data and is much more versatile – it’s also unlimited!
If you are an enterprise SEO team tracking hundreds of thousands (or even millions) of keywords daily then you need more firepower to store, manage, manipulate, analyse and visualise your SEO data.
Google Big Query is the perfect solution and it’s relatively cost-effective.
How do I connect my SEO platform or SEO tools to Big Query?
Ask your SEO tool or platform provider for details of their Big Query integration.
You’re quite likely to get a blank stare after this request, as, at the time of writing, Authoritas is the only major SEO platform with a comprehensive SEO platform to Google Big Query integration that allows for synchronisation and streaming of data to Big Query.
So, your fallback position is to ask for an API key, so that you can programmatically access and export your data to your Big Query tables.
This obviously does create a technical overhead for you, as this is a production system that will need to be created, monitored, maintained and updated in the future.
If you would like to explore alternative cheaper methods of getting and maintaining your marketing data into Big Query then please speak to the Authoritas team.
What if my SEO data is not in the correct format for visualisation in Data Studio?
Well, this happens more often than you think and it does require a slightly more advanced level of SQL know-how.
Big Query allows you to create materialized views of your data, which are like virtual versions of your table, transposed, filtered or oriented in a way that makes sense for the view you need. You can achieve this by writing specific SQL queries and saving them in Big Query.
Fortunately for you, if you are using the Authoritas Big Query integration for Google Data Studio then this complex SQL has already been written by our technical team and there are multiple useful views of your data ready to be visualised waiting for you in your Big Query dataset.
How do I maintain the data in Big Query?
Well, this is often the hard bit, especially if you’ve manually created the tables using .csv uploads that you’ve exported from other SEO tools.
This process is fine once, but soon becomes a painful and tiresome exercise if you have to repeat this daily, weekly or just monthly. You have to automate or suffer surrendering the majority of the benefits you will derive from using Big Query in the first place.
Using Big Query necessitates that you create a data pipeline that needs to be managed and monitored. This requires a mid-level software engineer. It’s not a big job but one that will take a few days to a few weeks depending upon how many data sources you are connecting to and the state of their APIs.
You need to consider how often each data source is updated. For example, if you only pull in backlink data from Majestic once a month then that’s fine, but if you are checking new links across all your competitors daily then you need a better solution that uploads data daily.
You also should consider what happens if you change keyword tags or have other content that is updated multiple times a day; again, you will need the services of an engineer, as you need to stream the data to Big Query.
It's probably a lot faster and cheaper to switch SEO tool suppliers and let SEO platforms like Authoritas automate the whole ETL (Extract Transform Load) and maintenance process for you.
How do I connect to and extract data from the Google Search Console API or the Google Analytics API?
Get a Google API key, download the client libraries and find an engineer to setup a process to query and store the data returned for you on a schedule.
Or, simply authorise the Authoritas platform to do this on your behalf! No engineer or costs required!
Do any SEO tools have native Big Query connections (like Google Analytics 4) or do I have to learn to use their APIs or find and pay a software developer to do it?
As far as we know, only Authoritas has a comprehensive Big Query connection that syncs and importantly streams data to your Big Query instance.
This is included in Business Advanced and Business Expert level packages and above.
Looking to adopt Big Query to power your SEO?
We'd love to help with either our out-of-the-box or custom solution. Let's talk.
Featured Posts from our blog
Expert content from our team and our friends.
Read new about our software and algorithm updates, SEO advice and opinion.
Learn how Koray Tuğberk GÜBÜR and his team worked together to improve the authority of the site and traffic for Encazip in this case study!
We live in a very competitive market and therefore it can be hard to get ahead in the SERPs. This is especially true if the only part of SEO you can amend is the content on the page. Your client or your company may not have budget for outreach or Digital PR or for technical changes.