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 app and web data. However, they encountered complications due to an outdated app schema. Inconsistent naming conventions hindered the rollout, putting their plans at risk with limited resources and budget to update the schema.

The solution

After considering various options, the focus shifted towards using BigQuery to import, transform, and consolidate data from both the app and web sources. By establishing appropriate naming conventions and harnessing the power of user-defined functions, EDF successfully obtained a unified and comprehensive view of their app and web data for reporting purposes.

The results

By leveraging the consolidated snapshot table, we proceeded with migrating and condensing the legacy code, eliminating redundant queries whenever feasible. As a result, EDF successfully reduced the data query volume by a substantial 250GB per day, leading to a more cost-effective and efficient data warehouse. Besides the immediate savings on query costs, this optimisation also offered the advantage of reducing the overall number of tables within BigQuery.

What’s next?

Moving forward, EDF is further developing the snapshot table as the central hub for their internal reporting. This not only enables significant cost savings but also opens up the possibility of incorporating Ad data into the snapshot table, ultimately advancing toward a comprehensive sales and marketing dataset.