Chapter 1. BigQuery and Automation

Big data set? You need to get familiar with Big Query !

The first one is something I want to talk about, because it’s come into my life during the lockdown period when I’ve been asking myself that question of how to do things faster and more efficiently.

If you’re working with a lot of data, it’s a really great idea to get acquainted with BigQuery. BigQuery, for those who don’t know, is part of Google’s cloud services. It’s all hosted in the cloud, which means it’s extremely fast, which is great for working with huge datasets. I saw one of my colleagues apply a filter to 15 million rows in about four seconds. If you’ve ever been stuck in Google Sheets, with it crashing due to the huge amounts of data, then BigQuery will change your reporting life forever. Who knows what you can achieve if you start to save that time you previously spent in Google Sheets. 

BigQuery uses SQL or SQL, which stands for Structured Query Language. The great thing about SQL is the fact that it’s quite human-oriented in terms of coding language. It’s not something that’s very complicated to learn snf it’s really well-resourced on the internet as well so there’s many places that you can practice or pick up tips. It’s also very economical, around $5 per terabyte and you get given an allowance when you sign up for free on Google. Therefore the first million queries you’ll be running, (unless you’re working with absolutely ginormous datasets),  is going to be super cheap, if not free.

The other great thing about SQL is that it’s already integrated within all the programs that we use as SEOs. It works really well with Google Sheets, Google Data Studio, and Google Drive. If you have a massive data set you can upload it to cloud storage, and it obviously links to that really well.

The one drawback I would probably say about BigQuery is just the fact that it’s not very good for exploratory tasks. If you need to finish a task and you know what it is you want to get out of BigQuery, then it’s really straightforward. However, if it’s something where you’re not entirely sure what you’re looking for and are still in the research stage, then BigQuery is not for you. You need to know what you want to achieve first, break down your data and remove as much kind of excess columns that aren’t relevant before starting the task.

One example of where BigQuery can be really useful:

You have lots of log file data and you wanted to combine that with Google Analytics data  and you also want to combine it with crawl data. Whether you’re using Screaming Frog or another provider, that’s a lot of data. With BigQuery, it’s really easy to mesh all this up and create your own bespoke data source which you can then analyse and work with quickly.

Automation is the future! Embrace it

My second tip is about saving time, and it’s really about automation. It does involve a little bit of set-up at the start, which is an investment on your time, but you will get it back if you set it up efficiently.

  1. Set up Google Analytics alerts.

For example set up an alert where as if conversions drop week on week by 30 plus percent, or month on month you will receive an automated notification. You can also set up all kinds of automation in terms of report delivery. If you have a lot of stakeholders, you can amend within Data Studio the time range for the report so that it automatically updates itself and you don’t have to intervene. It can be delivered to the stakeholders the exact same time of every week, ready for their Monday meetings or reports.

2. Using APIs

Every piece of data we have at Blue Array, we have extracted using an API. Some are cheaper than others. The main tool that we use at Blue Array is Google Data Studio. I really like it because it pretty much connects to everything. The only disadvantage with APIs is that if your company has a lot of bureaucracy or you’re struggling to get the buy-in or the budget for certain APIs, you can download the data that you need to use and then actually connect Google Sheets as a data source within Data Studio. And that can save you a lot of time and kind of circumnavigate that issue a little bit, which is really, really helpful.


‘Organic’ users and pages are not all the same - dig deeper

My last tip is that organic users and pages are not all the same. You really do need to dig deeper into your data, you cannot just merge it all together. In Google Analytics, you can automate the data collection and can set up segments and different filters to then use those with the data that you’re working with. 

Please refrain from saying in your reports, “This is the bounce rate for my website,” because it really doesn’t mean anything. You’re going to have different areas of your site, which are going to have very different visitors, actions and behavior. For example, if you have a blog, that will have a very different user profile in terms of behavioral metrics, to product pages which are transactional or converting. Therefore both pages need to be treated differently.

What is great about Google Analytics is that it comes with some inbuilt segments, but you can set up your own ones depending on what your business KPIs are or anything in particular that you want to look at in more detail. For example it could be days since last visit or how many visits before converting. This can give you a lot more information to build actions and insights.

The last tip is to look for patterns that crossover with above-the-line efforts and this comes back to quite an important principle of reporting, which is communication. It’s not enough to just report in isolation, and say, “These are the results.” It’s really important to understand wider macro factors that are affecting your target market, that are affecting the way that people interact with your website. Make sure you know what is happening in the wider marketing teams. Especially if you work in an agency, for example, where you might not have those conversations with a wider group of stakeholders, with your client’s business. Find out what other marketing activities are being executed at the same time as the SEO projects. Is there a PR campaign going live? This will also affect organic traffic. It is not enough to say, “We did this work and we had a 45% uplift in organic visits.” It actually might be related to a myriad of factors.

Steph-W from Blue Array

Steph Whatley

SEO Manager

Steph is an SEO Manager at Blue Array, with a special interest in data, reporting and analytics. She’s spoken on the main stage at Brighton SEO as well as at Reading SEO, Authoritas’ Tea Time SEO and Women in Tech SEO meetups. She loves both learning and teaching.

Watch our Tea Time SEO session here:

Table of Contents

Can we help?

If you are looking for an easy way to automate much of the advice given in this guide, then please book a call with one of our platform experts to explore whether we have what you need.

Continue reading about SEO Reporting