How to combine public global weather and GA4 ecommerce data in BigQuery

Google Analytics is great for measuring web and app behaviour, but it can't tell you anything about contextual events that happen in the real world. Combining your website and/or app data with public available data can be of great value to your business.

A good example of contextual data that can be relevant to you or your business is weather data. BigQuery's public data program provides exactly what we need.

Exploration

Let's take a look at the public noaa_gsod global weather data set.

This public dataset was created by the National Oceanic and Atmospheric Administration (NOAA) and includes global data obtained from the USAF Climatology Center. This dataset covers GSOD data between 1929 and present, collected from over 9000 stations. Update frequency: daily.

This is an incredible source of information. We will explore this data set so we can use it for our purpose: combining it with GA4 data.

Weather stations

Before we dive in the actual weather data, we need to choose the weather station(s) we want to be the source(s) of our weather data. As weather data can differ from continent and country to another, but also can be totally different between regions and cities, we have to spent some time looking at the schema bigquery-public-data.noaa_gsod.stations first.

Remember, the data set contains weather data from 1929 onwards, so probably a lot of weather stations will not be active any more. Using the following query we can define how many stations worldwide are still active during the last week, grouped by country.