Google Analytics 4 (GA4) - querying raw data with SQL

Google Analytics 4 (GA4) is the newest version of Google Analytics, designed to provide businesses with a more comprehensive view of customer interactions across multiple platforms and devices. GA4 provides businesses with valuable insights that can help improve their online presence and increase conversions.

While GA4 reports can be confusing for some, it is possible to query raw data using SQL to gain insights that are relevant to your business. In this article, we will discuss how to use GA4 and Google BigQuery to query raw data and gain valuable insights.

Connecting Google Analytics 4 to Google BigQuery

The first step in using GA4 to query raw data is to connect it to Google BigQuery. This can be done easily by following these steps:

1. Create a new BigQuery project or use an existing one.
2. In the GA4 admin panel, select the Data Streams tab.
3. Select the data stream that you want to export to BigQuery and click on the BigQuery Export tab.
4. Click on the Link to BigQuery button and follow the prompts to link your GA4 account to your BigQuery project.

Once your GA4 account is linked to your BigQuery project, you can begin querying raw data using SQL.

Querying Raw Data using SQL

One of the most powerful features of GA4 is its ability to export raw data to BigQuery. This allows you to create custom queries using SQL and gain insights that are not available in GA4 reports.

For example, if you want to know the details of users who made purchases during this year, you can use the following SQL query:

 SELECT 
  event_date, 
  event_timestamp, 
  p.value.string_value AS order_id, 
  device.mobile_model_name, 
  device.operating_system_version, 
  traffic_source.name AS campaign, 
  traffic_source.medium, 
  traffic_source.source  
 FROM 
  `ga4-gellifique-uk.analytics_287630133.events_*`, 
  `ga4-gellifique-uk.analytics_287630133.events_*`.event_params p 
 WHERE 
  event_name='purchase' 
  AND key='transaction_id' 
  AND event_date>='20230101' 
 ORDER BY 
  event_timestamp 
 LIMIT 
  1000

In this SQL query, we are selecting the event_date, event_timestamp, transaction_id, mobile_model_name, operating_system_version, name, medium, and source from the raw data. We are also filtering the results to only show events with the event name "purchase" and a transaction ID, and only events that occurred after January 1st, 2023.

Here is the result:

Next SQL query provides an example of how to use Google Analytics 4 (GA4) and Google BigQuery to gain insights into the efficiency of your campaigns based on the number of purchases.

In this query, we are first selecting the event_date, event_timestamp, transaction_id, mobile_model_name, operating_system_version, name, medium, and source from the raw data. We are also filtering the results to only show events with the event name "purchase" and a transaction ID, and only events that occurred after January 1st, 2023.

Next, we are using a subquery to count the number of purchases for each campaign. We are grouping the results by campaign name and ordering the results by the count of purchases in descending order.

SELECT 
  name AS campaign, 
  COUNT(name) AS count 
FROM 
  (
    SELECT 
      event_date, 
      event_timestamp, 
      p.value.string_value AS transaction_id, 
      device.mobile_model_name, 
      device.operating_system_version, 
      traffic_source.name, 
      traffic_source.medium, 
      traffic_source.source 
    FROM 
      `ga4-gellifique-uk.analytics_287630133.events_*`, 
      `ga4-gellifique-uk.analytics_287630133.events_*`.event_params p 
    WHERE 
      event_name='purchase' 
      AND key='transaction_id' 
      AND event_date>='20230101' 
    ORDER BY 
      event_timestamp 
    LIMIT 
      1000
  )
GROUP BY 
  name 
ORDER BY 
  count(name) DESC

Here is the result:

By using this query, you can gain insights into which campaigns are driving the most purchases and adjust your marketing strategies accordingly.

Conclusion

While GA4 reports can be overwhelming, connecting GA4 to Google BigQuery and querying raw data using SQL can provide valuable insights for your business. By using custom queries, you can gain insights that are relevant to your business and help you make data-driven decisions.