You've successfully subscribed to GA4BigQuery
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

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

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

Join 1,800+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks

Great! Check your inbox and click the link to confirm your subscription
Please enter a valid email address!
Already have an account? Sign in

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.