BigQuery

Google BigQuery data warehouse, SQL queries, and analytics data processing (179 posts)
Measurelab awarded Google Cloud Marketing Analytics Specialisation

Measurelab awarded Google Cloud Marketing Analytics Specialisation

At the start of the year, if you’d asked us whether Measurelab would be standing shoulder to shoulder with Europe’s biggest consultancies by September, we would've been surprised. Not because we don't believe in ourselves, but because these things feel so distant - until suddenly, they’re not. So, here it is: we’ve been awarded the Marketing Analytics Services Partner Specialisation in Google Cloud Partner Advantage. What’s the big deal? In Google’s own words (with the obligatory Zs): “Spec

Will Hayes•11 Sept 2025

Measurelab awarded Google Cloud Marketing Analytics Specialisation

At the start of the year, if you’d asked us whether Measurelab would be standing shoulder to shoulder with Europe’s biggest consultancies by September, we would've been surprised. Not because we don't believe in ourselves, but because these things feel so distant - until suddenly, they’re not. So, here it is: we’ve been awarded the Marketing Analytics Services Partner Specialisation in Google Cloud Partner Advantage. What’s the big deal? In Google’s own words (with the obligatory Zs): “Spec

Will Hayes•11 Sept 2025

BigQuery AI.GENERATE tutorial: turn SQL queries into AI-powered insights

BigQuery just got a major upgrade, you can now plug directly into Vertex AI using the new AI.GENERATE function. Translation: your analytics data and generative AI are now best friends, and they’re hanging out right inside SQL. That opens up a whole world of new analysis options for GA4 data, but it also raises some questions: * How do you actually set it up? * What’s it good for (and when should you avoid it)? * Why would you batch the query? Let’s walk through it step by step. Step 1: H

Katie Kaczmarek•3 Sept 2025

GA4 Dataform integration: how Springer Nature modernised analytics with Measurelab

The Springer Nature Group is an academic publishing company, with brands dating back to 1842, that advances scientific discovery by publishing robust and insightful research, supporting the development of new areas of knowledge, making ideas and information accessible around the world, and leading the way on open access. The challenge Springer Nature needed to migrate their Universal Analytics dashboards to GA4 data. Their reporting relied on multiple stacked scheduled queries in BigQuery tha

Steven Elliott•28 Aug 2025

How to start forecasting in BigQuery with zero training

If you’d told me five years ago that I’d be forecasting product demand using a model trained on 100 billion time points… without writing a single line of ML code… I probably would’ve asked how many coffees you’d had that day ☕️ But its a brand new world. And it’s possible. Let me explain What is TimesFM? TimesFM is a new foundation model from Google, built specifically for time-series forecasting. Think of it like GPT for time, instead of predicting the next word in a sentence, it predicts t

Katie Kaczmarek•14 Jul 2025

Webinar: Stop the martech madness! Go composable. Deliver value faster.

In this 30-minute webinar, we use three real-world examples to demonstrate how a composable approach can help you do more with the data and tech you already have - delivering value at speed. We cover three practical examples of how your marketing data warehouse can support better insights and experiences - without the need for more martech. Learn how you can: * Score wins quickly and regularly * Streamline your martech stack * Stop planning and start building Watch the replay 👇 Have que

Will Hayes•8 Jul 2025

Behind the Cloud – Data Preparations in BigQuery

In this episode of Behind the Cloud, Matt explores BigQuery’s new Data Preparation feature, an AI-powered, point-and-click tool for transforming data with ease. From natural language transformations using Gemini to automated workflows and scheduled outputs, learn how Google is simplifying data prep for analytics and machine learning. Watch below 👇 on Youtube, or catch more from the series here. Transcript [00:00:00] Hello and welcome to another episode of Behind the Cloud. Today we are gonn

Matthew Hooson•24 Apr 2025

Easy ways to prepare your BigQuery warehouse for AI

You’ve probably heard that AI is coming to make our lives easier, especially in tools like BigQuery. But here’s the thing: AI isn't magic. If you want it to be accurate and useful, you need to set it up for success. One of the best ways to do that? Improve the metadata in your BigQuery warehouse. Metadata is like the index or contents page in a book, it quickly tells you exactly what’s inside and where to find it. Creating clear metadata means AI can more easily understand your data warehouse

Katie Kaczmarek•23 Apr 2025

Data pipeline optimisation with Google Cloud and Dataform

In our recent engagement with a client, we went on a journey to transform their data pipelines, tackling inefficiencies in performance and cost within their Google Cloud BigQuery environment. Our efforts culminated in a comprehensive optimisation strategy that used Dataform, improved SQL practices, and implemented tailored solutions for significant performance gains and cost savings. Here’s a deep dive into the highlights of our project. Identifying inefficiencies in BigQuery workflows We beg

Prasanna Venkatesan•22 Apr 2025

Dataform for BigQuery: A basic end-to-end guide

Dataform is a powerful tool for managing your data workflows in a structured, version-controlled, and automated way. Whether you're a beginner or an experienced data engineer, Dataform simplifies SQL-based transformations while integrating seamlessly with Google BigQuery. Although this blog offers a basic introduction to Dataform's functionality, users can achieve significantly more with Dataform. From advanced scheduling, parameterised queries, and dependency management to complex data modelli

Prasanna Venkatesan•18 Mar 2025

How to extract GA4's event sequencing in BigQuery using the new batch fields

Google Analytics 4 (GA4) exports event data to BigQuery, enabling detailed user behavior analysis. However, GA4 batches events before sending them, making GA4 event sequencing in BigQuery more complex. Fortunately, three fields—batch_event_index, batch_ordering_id, and batch_page_id—help provide precise sequencing information. For a full schema of the GA4 export, head over to google documentation. This article breaks down these fields in a clear, practical way and shows how to use them togethe

Katie Kaczmarek•24 Feb 2025

Automating BigQuery workflows with conditional logic and data readiness checks

BigQuery offers powerful scripting capabilities that can simplify complex workflows. In this post, we’ll explore two essential techniques: using IF...THEN...END IF for procedural control and leveraging the metadata in project.dataset.__TABLES__ to ensure your Google Analytics 4 (GA4) data is ready before running queries. These strategies help you avoid unnecessary processing, reduce costs, and improve efficiency in your data pipelines. 1. Leveraging IF...THEN...END IF for procedural logic Whi

Katie Kaczmarek•20 Feb 2025

Mastering data loading in BigQuery using Dataform

Efficient data loading is crucial for managing and updating tables in Dataform. Various strategies exist to handle different use cases, including truncate and load, appending data, and leveraging incremental tables with unique keys. This blog explores these primary methods and more: Truncate and Load In this method, all existing records in the target table are deleted and replaced with a fresh table. This approach works well when a full table refresh is necessary or if managing slowly changin

Prasanna Venkatesan•7 Feb 2025

A step-by-step guide to migrating scheduled queries to Dataform

Managing scheduled queries in BigQuery often feels limiting — there’s no version control, no easy collaboration, and scaling can be difficult. If you’ve ever wondered how to make SQL workflows smoother, Dataform is your answer. In this post, I’ll show you how I migrated a BigQuery scheduled query to Dataform and how it transformed the way I manage my data pipelines. After all, we all want to know who’s been touching our queries, don’t we? Getting started in Dataform First thing you need to

Katie Kaczmarek•27 Nov 2024

How to set up a Dataform repository with GitHub & Google Cloud integration

Setting up a Dataform repository can be challenging without the right steps. Whether you’re new to Dataform or want to optimise your workflow, this guide will show you how to seamlessly connect it with GitHub and Google Cloud (GC). What is Dataform and why use it? Dataform is a powerful tool for managing version-controlled SQL workflows in a collaborative way. GC incorporates BigQuery and GitHub integration, providing an efficient way to organise and maintain complex data pipelines. Let’s bre

Katie Kaczmarek•27 Nov 2024

Behind the Cloud – IAM roles in Google Cloud

In this episode of Behind the Cloud, Matthew dives into Identity and Access Management (IAM) on Google Cloud. He discusses the common frustrations users face, and the significance of assigning specific, granular permissions to mitigate potential risks. Watch below 👇 or head over to our YouTube channel Transcript Introduction to IAM roles [00:00:00] Matt: Hello and welcome to another episode of Behind the Cloud. I’m afraid today isn’t going to be the most glamorous of episodes because what

Matthew Hooson•5 Nov 2024

Integrating siloed data: Springer Nature marketing and sales case study

The Springer Nature Group is an academic publishing company, with brands dating back to 1842, that advances scientific discovery by publishing robust and insightful research, supporting the development of new areas of knowledge, making ideas and information accessible around the world, and leading the way on open access. The challenge The sales and marketing teams depended on incomplete data, which didn’t capture the entire customer journey due to different systems in use. Transactions and re

Mark Rochefort•7 Aug 2024

Behind the Cloud: Using Generative AI in the GCP

In this episode of Behind the Cloud, Matthew delves into the world of generative AI in the Google Cloud Platform, highlighting how Google has integrated generative AI features into its various services. Matthew explores the ways generative AI can be used within BigQuery, such as generating SQL queries and Python notebooks! Watch below 👇 or head over to our YouTube channel Transcript Introduction to Generative AI in Google Cloud [00:00:00] Matt: Hello and welcome to another episode of Behi

Matthew Hooson•30 Jul 2024

Behind the Cloud: GCP foundational best practice

In this episode of Behind the Cloud, Matt explains how to organise GCP projects by specific use cases, how to automate processes, establish clear naming conventions, and follow best practices around security, tagging, and more. Watch below 👇 or head over to our YouTube channel Transcript Introduction [00:00:00] Matthew: Hello and welcome to another episode of Behind the Cloud. We’ve had a little bit of a brief hiatus but now we’re back and we’re going to do another series of videos. Today

Matthew Hooson•27 Jun 2024

Behind the Cloud: Setting up a Dataform project within BigQuery

In this episode of Behind the Cloud, Matthew demonstrates how to enable and set up a Dataform project within BigQuery, connect it to GitHub, and initialise the workspace for building a Dataform project. Matt walks us through enabling BigQuery, creating a repository, setting up the region, and using service accounts. Watch below 👇 or head over to our YouTube channel Transcript Introduction to Dataform in BigQuery [00:00:00] Matt: Hello and welcome to this week’s behind the cloud sticking w

Matthew Hooson•4 Apr 2024

Behind the Cloud: Connecting GA4 to BigQuery

In this episode of Behind the Cloud, Matt explores the practical side of Google Analytics 4 (GA4) and its free export feature to BigQuery. We learn about export limits, batch exports, and streaming options for GA4 data. The episode also touches on setting up BigQuery within a GCP project. Watch below 👇 or head over to our YouTube channel Transcript Introduction to GA4 and BigQuery Export [00:00:00] Matt: Hello and welcome back to Behind the Cloud. This week we’re going to delve into somet

Matthew Hooson•14 Mar 2024

Grand Designs: "The Warehouse to CDP conversion"

Opening scene: A large meeting room at BigCorp. A systems architecture diagram with lots of boxes and arrows flowing from left to right fills a large whiteboard. Kelvin joins Jan (marketing director) and Joe (head of data) at the table, earnest expression, hands clasped in front of him. Kelvin: So, Jan, Joe, tell me a bit about the history of the warehouse. Joe: Well, going way back, it was originally an on-prem system, which was replaced back in 2019 with an Oracle data warehouse as part of a

Steven Elliott•14 Mar 2024

Behind the Cloud: Dataform, what is it and why does it matter?

Watch below 👇 or head over to our YouTube channel In this episode of Behind the Cloud, Matt discusses Dataform, what it is, and why it matters? Transcript Cloud Data Warehousing [00:00:00] Matt: Welcome to Behind the Cloud. Today we’re exploring data form, but first a little bit of scene setting. Over the past number of years, cloud computing, specifically cloud data warehousing, has advanced significantly. Huge amounts of data can be queried in seconds. The scalability of the platforms i

Matthew Hooson•26 Feb 2024

Behind the Cloud: The essentials of Google BigQuery

In this episode of Behind the Cloud, Matt discusses the essentials of Google Cloud’s BigQuery. Everything from project structure, data handling, to understanding the costs involved. Watch below 👇 or head over to our YouTube channel Transcript [00:00:00] Matt: Hello and welcome to Behind the Cloud. Today we’re going to be diving into the nuts and bolts of Google Cloud’s BigQuery and how it can help to revolutionise your marketing analytics. Whether you’re really familiar with the cloud or th

Matthew Hooson•7 Feb 2024

Behind the Cloud: What GCP tools should you be familiar with?

In this episode of Behind the Cloud, Matthew aims to answer the question, what are the Google Cloud Platform (GCP) tools of the marketing analytics trade? And more specifically, what are the tools that you should care about in Google Cloud Platform. For a more in-depth write up on GCP tools, check out Matt’s blog post. Watch below 👇 or head over to our YouTube channel Transcript [00:00:00] Matt: Hello and welcome to today’s episode of Behind the Cloud. We’re going to try and answer the ques

Matthew Hooson•13 Jan 2024

2023: Measurelab's year in review

2023 was a momentous year for Measurelab. It marked a decade of delivering analytics excellence to the world, a year when we were named one of the UK’s best places to work and became a fully-certified GCP partner, a summer that saw the sunsetting of Universal Analytics, followed by the new dawn of generative AI and the beginning of an augmented analytics era. Into the sunset From the very beginning of January, right up to the deprecation deadline of July 1st, we were swamped with GA4 migratio

Steven Elliott•22 Dec 2023

GA4 app attribution case study: Pret + Adjust integration

Pret a Manger is a brick and mortar chain of coffee shops serving freshly made food and good organic coffee. They have 600+ stores distributed globally and their analytics team is responsible for over 30 million events every month collected across both web and app.  “Measurelab have been a key partner to us as we’ve undertaken a complete overhaul of our app and web analytics. Having access to the wide range of skillsets within the organisation has been invaluable as we’ve embarked on this journ

Mark Rochefort•1 Nov 2023

Call propensity analysis for Sanderson – bridging online data and call centre insight

Sanderson Design Group designs and manufactures wallpaper and fabrics, with a history stretching back more than a century. It trades under several brands including Arthur Sanderson & Sons, Morris & Co., Zoffany and Harlequin. The analytics team manages 5+ sites and over 20,000 digital transactions per month. The challenge Sanderson has recently launched a new B2B ordering system, aiming to streamline order generation and reduce customer service calls. Following the implementation, call volum

Mark Rochefort•5 Oct 2023

BigQuery optimisation case study: EDF Energy saves 250 GB a day

EDF Energy is the UK division of EDF, the multinational energy company. Their services span electricity generation and the sale of natural gas and electricity to homes and businesses throughout the United Kingdom. Their analytics and insight team manage data from 15+ websites and apps totalling 65 million events every month. We helped reduce their data query volume by an astonishing 250GB per day! The challenge During the migration to Google Analytics 4 (GA4), EDF aimed to streamline their

Mark Rochefort•5 Oct 2023

The Derelict Data Warehouse

A well-maintained and utilised data warehouse is a thing of beauty. Imagine all your data from disparate sources autonomously extracted, loaded and transformed into nice neat reporting tables. Picture, if you can, impactful analysis, company-wide data-driven decision-making, a true understanding of return on ad spend. It’s enough to make anyone weak at the knees. But therein lies the problem. Companies (or ambitious individuals) can be too desperate to reach the promised land and rush headlong

Matthew Hooson•24 Aug 2023

The Measure Pod: 2 years of podcasting

If you also work in an industry like analytics, there's generally very few people who you can really talk to about work stuff. I bet most of us have had something like "do you have Chandler Bing's job?" said to us at some point. Or is that just me? Anyway, to the point. Almost to the day two years ago I wrote about how we set up and launched The Measure Pod - wow, doesn't time really fly! If you've never come across the podcast The Measure Pod, you'd be forgiven. Like a lot of things, it was b

Daniel Perry-Reed•3 Aug 2023

How to build an interactive GA4 BigQuery data schema

What and why? One of the great features of Google Analytics 4 (GA4) is the ability to pass data into BigQuery (BQ). There are many benefits to this which have already been covered in some of our previous blogs (i.e. 10 reasons to export your GA4 data to BigQuery). Passing data to BigQuery is no longer just available to enterprise GA360 customers, but to anyone using GA4 for free. What I wanted to create was a simple interactive way to explore how GA4 data is saved in BigQuery. I found the GA36

Scott Hellmund•23 Mar 2022

The sun is setting on Universal Analytics

It’s finally happening. Google has announced the sunsetting of Universal Analytics, or more specifically, the date when UA will stop processing new hits. The 1st of July, 2023. For those without a calendar to hand, that’s fifteen and a half months from now. So plenty of time still to think about migrating to Google Analytics 4, right? Wrong. Well, wrong if on 1st July 2023 you want to be able to compare your latest GA4 data with comparable data from the previous year. And what analyst wouldn’

Dara Fitzgerald•16 Mar 2022

Data warehouse: Repair broken tables in BigQuery

At Measurelab, we love a bit of data warehousing using BigQuery; in fact, we are obsessed with finding the best approach to managing data warehouses in the most optimal way possible.  So in this series, we want to share with you our knowledge and expertise to show what a fantastic and dynamic tool BigQuery is. As you may know, BigQuery has some super powerful features, which allow us to build, manipulate and even run machine learning algorithms within the interface. However, we’re all human, an

Lace Rogers•8 Mar 2021

Data warehousing: What is BigQuery and how do I get started?

At Measurelab we live and breathe the Google Cloud Platform, so our specialists are experts in Bigquery.  We have decided in this series to share our knowledge and take you through some great techniques to maintain your BigQuery data warehouse.   So firstly-what is BigQuery? BigQuery is a fully-managed, serverless data warehouse that enables scalable analysis over petabytes of data. ...and that means? Simply put, BigQuery is a fantastic cloud-based data warehouse in which you can store huge

Lace Rogers•21 Dec 2020

Google Analytics data import: automate data import with Python & Cloud functions

Why automate data import into Google Analytics A few months back I was granted access to a Google Cloud Source Repository (private Git repositories hosted on Google Cloud part of Google’s Cloud developer tools) that contained Python code and configuration details for automating the extraction of data from a BigQuery table and uploading to Google Analytics as a custom dimension via the Management API on a desired schedule. Choosing the right approach for blog word counts Being primarily a Jav

Victor Sarker•16 Nov 2020

Google Analytics 4

The new future version of Google Analytics has been announced - now branded as Google Analytics 4. Building on the "App + Web" property that was released last year, this sets the direction for Google Analytics going forwards. It is now the default experiences for all new properties and is where all product developments will be focussed. Google Analytics 3, commonly known as Universal Analytics, was released in October 2012. This next product iteration for Google Analytics is a major step on fro

Mark Rochefort•14 Oct 2020

We are going BIG on DATA

See what we did there? Okay, so maybe a tad too much on the title. It is catchy, so hey. Firstly - we are pretty chuffed to welcome Lace to our team. She joins us to spearhead our offering in all things DATA science and data engineering, which (to be completely frank) is where all roads point in this analytics game. With the advent of App plus Web (from Google Analytics), and particularly the opportunity to access raw data through BigQuery, this is an exciting place to be right now. Secondly -

Mark Rochefort•29 Jun 2020

Emergency Analytics Support

It can be tricky judging how to pitch a new offer in times of crisis. There’s a fine line between being genuinely helpful and appearing exploitative. Sometimes you have to just jump in and try things, knowing they may be misinterpreted by the minority. In business, indecision can be crippling. While people’s lives are undoubtedly the most precious thing, livelihoods are worth saving too. A number of our clients - particularly those in the retail sector - have been hit hard by recent events.

Steven Elliott•2 Apr 2020

How to Conduct an Exceptional Training Session

So… you’ve been asked to conduct a training session and you don’t know where to start. Or, you’re looking for a way to take your current training process to the next level. Whatever your level of experience, the following tips will help you make your next training session exceptional. 1. Know the subject The first step in giving an exceptional training session is being deeply familiar with the subject matter. In the words of Albert Einstein: You may think you know a subject through and throu

Dave Beatty•6 Dec 2019

How to use BigQuery without GA360: insights from Measurefest 2019

Speaking at Measurefest For the first time in a few years, I took to the stage at Measurefest to talk about how to get your data from Google Analytics into BigQuery, and what you can do with it when you get there. Affordable BigQuery solutions Obviously, Analytics 360 can do this, but it costs $$$ per year. I outlined some more affordable methods, including third-party connector solutions, and provided a detailed example of how we would use our own Pipelines solution and what we could achiev

Adam Englebright•12 Sept 2019

Next‑Generation measurement with Google Analytics 4

Why GA4 has us excited This announcement has us rather excited at Measurelab. It has been a long time coming and represents the beginning of a completely new shift in how we use and think about digital measurement with Google Analytics. Leaving the old pageview model behind What we are effectively looking at here is a move away from Google Analytics as we know it, which inherited its underlying data collection schema and methods from Urchin Analytics; the analytics software product Google bo

Steven Elliott•2 Aug 2019

Firebase Analytics demystified

<span style="font-weight: 400;">After a steep learning curve into the world of Firebase Analytics or Google Analytics for Firebase, it seems that a blog post about it would be much valued and appreciated. I’ve compiled a list of the most frequently asked questions.</span> 1. How does Firebase Analytics define a session? Firebase Analytics defines a session as a user engaging with your app for a minimum amount of time (10 seconds by default) followed by your user not engaging with your app

Alex Cirstea•5 Feb 2018

R For Analytics: A Beginner’s Guide, Part 3

<strong>IMPORTANT: THE PACKAGE HAS BEEN UPDATED BUT ADAM HASN'T HAD TIME TO UPDATE THIS YET! PROCEED AT YOUR PERIL!</strong> Last time, we had a look at how we could use RGA to pull in account details and suchlike. It’s not essential, but I’d suggest you go back and read through it, as it might make some of what we do next a little easier – and, of course, if you haven’t done so already, read the first piece too and follow the instructions enumerated therein to allow you to do, well, any of

Adam Englebright•4 Feb 2015

GA Summit 2013: Key Google Analytics updates & insights

The theme of this years summit is a focus on helping customers in three areas: Access, Empower, Act. The slide above was shown by the GA team to summarise the 3 part theme. Babak Pahlavan (Director of Product Management, Google Analytics) let the crowd know that GA have made 70+ releases in 2013 so far, which makes sense on reflection given how busy the year has been. This has included major updates such as the global roll out of Universal Analytics, the introduction of the Attribution Modellin

Dara Fitzgerald•2 Oct 2013