Using BigQuery to Track User Interactions: A Comprehensive Guide to Event Triggers

Understanding BigQuery and Event Triggers

BigQuery is a fully managed enterprise data warehouse service offered by Google Cloud Platform. It allows users to easily query and analyze their data stored in BigTable, another fully managed NoSQL database service provided by Google Cloud.

BigQuery supports a standard SQL dialect for querying data, making it easier for users to work with their data using familiar SQL skills. However, this also means that BigQuery’s events are not part of its standard SQL query capabilities.

How Events in BigQuery Work

When an event occurs in BigQuery, such as when a user clicks on a button or views a screen, the data is logged into a table called events. This table stores information about each event, including the timestamp and any relevant metadata.

However, BigQuery does not have a native way to query events in the same way that SQL databases do. Instead, users must use alternative methods to access and analyze their event data.

Using Firebase Events with BigQuery

One such method is to use Firebase Events with BigQuery. When an event occurs on Firebase platforms like Android or iOS, it can be sent to Google Cloud Logging or BigQuery for storage and analysis.

In this case, we’re interested in using Firebase events to find the event triggered before the setting event was triggered. To do this, we need to use a technique called " windowing" which allows us to analyze data from a specific time range or timeframe.

Windowing in BigQuery

Windowing is a way of partitioning your data into smaller chunks, allowing you to perform calculations and aggregations on those chunks separately. In BigQuery, this can be done using the OVER clause in SQL queries.

Here’s an example of how we might use windowing to find the number of users who triggered the setting event before another event:

SELECT 
  user_pseudo_id,
  COUNT(DISTINCT event_name) AS num_settings_triggers
FROM (
  SELECT 
    user_pseudo_id,
    event_name,
    timestamp(MICROSES(event_timestamp)) AS event_timestamp_micros
  FROM `your-project-id.your-dataset-name.your-table-name`
  WHERE event_name = 'setting'
)
WHERE event_timestamp_micros > (SELECT MIN(timestamp MICROS) FROM `your-project-id.your-dataset-name.your-table-name` WHERE event_name = 'setting')
GROUP BY user_pseudo_id

This query works by first selecting all rows from the table where the event_name is 'setting'. It then groups those rows by user_pseudo_id and counts the number of distinct event_names in each group.

The outer query uses windowing to filter out any rows where the event_timestamp_micros is less than or equal to the minimum timestamp of all the other ‘setting’ events. This effectively selects only the rows where the ‘setting’ event occurred after another event had already been triggered.

Accessing Firebase Events with BigQuery

To access the Firebase events, we need to use a technique called “Data Fusion” which allows us to integrate data from multiple sources into a single dataset in BigQuery.

Here’s an example of how we might use Data Fusion to access the Firebase events:

CREATE TABLE `your-project-id.your-dataset-name.firebase_events` (
  user_pseudo_id STRING,
  event_name STRING,
  timestamp MICROS
)
USING SOURCE = 
  `project-id:firebase-io-bigquery://your-project-id.your-dataset-name.your-table-name`

This creates a new table called firebase_events that contains the data from the Firebase events.

Using Screen Views to Track User Interactions

Another way we can track user interactions is by using screen views. When a user interacts with an app, it can be logged as a screen view event in Google Analytics.

Here’s an example of how we might use the screen_view event to track user interactions:

SELECT 
  user_pseudo_id,
  COUNT(DISTINCT event_name) AS num_screen_views
FROM (
  SELECT 
    user_pseudo_id,
    event_name,
    timestamp(MICROSES(event_timestamp)) AS event_timestamp_micros
  FROM `your-project-id.your-dataset-name.your-table-name`
  WHERE event_name = 'screen_view'
)
GROUP BY user_pseudo_id

This query works by first selecting all rows from the table where the event_name is 'screen_view'. It then groups those rows by user_pseudo_id and counts the number of distinct event_names in each group.

Using Firebase Previous Screen to Track User Interactions

Another way we can track user interactions is by using the firebase_previous_screen parameter. When a user interacts with an app, it can be logged as a previous screen event in Google Analytics.

Here’s an example of how we might use the firebase_previous_screen parameter to track user interactions:

SELECT 
  user_pseudo_id,
  firebase_previous_screen,
  COUNT(DISTINCT event_name) AS num_previous_screens
FROM (
  SELECT 
    user_pseudo_id,
    firebase_previous_screen,
    event_name,
    timestamp(MICROSES(event_timestamp)) AS event_timestamp_micros
  FROM `your-project-id.your-dataset-name.your-table-name`
)
WHERE firebase_previous_screen IS NOT NULL
GROUP BY user_pseudo_id, firebase_previous_screen

This query works by first selecting all rows from the table where the firebase_previous_screen parameter is not null. It then groups those rows by user_pseudo_id and firebase_previous_screen, and counts the number of distinct event_names in each group.

Conclusion

In this article, we’ve discussed how to use BigQuery to track user interactions and find the event triggered before the setting event was triggered. We’ve covered various techniques such as windowing, Data Fusion, screen views, and Firebase previous screen events to analyze user interactions.

We hope that by following these steps and using these techniques, you can gain a better understanding of how your users interact with your app and identify areas for improvement.

Last modified on 2025-04-20