Chapter 1. BigQuery and Automation

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

The first thing I want to talk about is something that came into my life during lockdown when I’ve been asking myself how to do things faster and more efficiently, Big Query.

If you’re working with a lot of data, it’s a great idea to get acquainted with BigQuery. BigQuery is part of Google’s cloud services. It’s all hosted in the cloud, which means it’s extremely fast and is perfect for working with huge datasets. 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, which stands for Structured Query Language. The great thing about SQL is that it’s quite human-oriented in terms of coding language. SQL can be complicated to learn, luckily, it’s well-resourced on the internet so there’s plenty of places you can practice or pick up tips. Big Query is very economical, around $5 per terabyte and you are given an allowance when you sign up for free on Google. The first million queries you’ll be running, (unless you work with ginormous datasets), will be cheap, if not free.

The other great thing about SQL is that it’s integrated with the programs that SEOs use, including Google Sheets, Google Data Studio, and Google Drive. If you have a massive data set you can upload it to Big Query’s cloud storage and use the data in all these tools and many more besides.

The one drawback of BigQuery is that it’s not great 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 you’re not entirely sure what you’re looking for and are 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 excess columns before starting the task.

For example BigQuery is really useful is when you have lots of log file data and want to combine it with Google Analytics and crawl data. Whatever provider you use, that’s a lot of data. Luckily, with BigQuery, it’s easy to combine this data and create a bespoke data source which you can 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 setting-up at the start, which is an investment on your time, but you will make this time up if you set it up efficiently.

1. Set up Google Analytics alerts

For example, set up an alert where if conversions drop week on week by 30 plus percent, you receive an automated notification. You can 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.

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:

Introduction

SEO Reporting can take a lot of time and often the data is not not widely shared within the company. Get started reading about SEO Reporting in the introduction.

Read Chapter 1

How can you do analysis faster and more efficiently. Get acquainted with BigQuery! Steph shares her insights with us in chapter 1.

Read Chapter 2

SEO reporting, can take many hours but how do you tailor it towards the different stakeholders? Christopher shares his tips with us on Tea Time SEO and how to show the metrics that count in chapter 2.

Read Chapter 3

How can pull in data from Google Search Console, Analytics and Google Data Studio? Barb Davids shows us how in chapter 3.

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.

Book a Call

2. Using APIs

Every piece of data we have at Blue Array, we extract using an API. The main tool that we use at Blue Array is Google Data Studio, which is great because it connects to almost everything. The only disadvantage with APIs is that your company might have a lot of bureaucracy, or you can struggle to get buy-in or budget from your managers. You can work around this by downloading the data that you need to use and connect Google Sheets as a data source within Data Studio. That can save you a lot of time and can circumnavigate that issue a bit.

‘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 need to dig deeper into your data, to understand it properly.

Please refrain from saying in your reports, “This is the bounce rate for my website” because it doesn’t mean anything. You have different areas of your site which have different visitors, actions and behaviours. For example, your blog posts will have a different user profile in terms of behavioural metrics, compared to product pages which are transactional or converting. Both pages need to be treated differently.

In Google Analytics, you can automate data collection and set up segments and filters to use with the data, this will help you understand your data better. Google Analytics comes with some inbuilt segments, but you can set up your own depending on your business KPIs or anything you are looking at in detail. For example it could be days since last visit or number of visits before converting. This can give you more information to build actions and insights.

Finally, look for patterns that crossover with above-the-line efforts. This comes back to an important principle of reporting, which is communication. It’s not enough to report in isolation, and say, “These are the results” it is important to understand wider macro factors that are affecting your target market and how users are interacting with your website. Make sure you know what is happening in the wider marketing teams. Especially if you work in an agency, where you might not have those conversations with your client’s business. What marketing activities are being executed at the same time as your 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 a myriad of factors.

Continue reading about SEO Reporting

Previous Chapter

Next Chapter