Leveraging Firebase Event Tables in Castled

Firebase exports all your app events to BigQuery on a daily basis, creating a separate table for each date. While the date-based sharding helps in fetching only relevant tables based on a date, it can still be very expensive if you are dealing with 100s of distinct product events.

The optimal strategy for marketing and analytics purposes is to maintain separate tables for each event, each partitioned by timestamp — a practice that Castled implements by default. This article outlines a detailed, step-by-step guide for combining the historical Firebase tables with the event tables created by Castled for customer segmentation and personalisation.

1. Enable Event Tracking in Castled

Begin by creating a dataset in BigQuery to store the event tables, enable write permissions for Castled on this dataset, and share the dataset name with our team. We will manage the internal configurations to record all collected events into this dataset.

2. Create Individual Historical Tables for Each Event

After enabling event tracking in Castled and ensuring all relevant events are flowing into your BigQuery, proceed to create individual historical tables for the Firebase events you wish to utilize in Castled for segmentation. This is a one-time setup that can be quickly executed on demand should more historical events need to be included in the future.

For example, consider a product event payment_success in BigQuery with event attributes transaction_amount and payment_method. You can create a payment_success_history table in BigQuery with the following command:

CREATE TABLE `castled-dev-44ede`.`analytics_326728785`.`payment_success_history`
PARTITION BY DATE(formatted_event_ts)
AS
SELECT *,
    TIMESTAMP_MICROS(event_timestamp) AS formatted_event_ts
FROM `castled-dev-44ede.analytics_326728785`.`events_*`
WHERE event_name = 'payment_success'
AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240601'

This command creates a single payment_success_history table partitioned by formatted_event_ts with all events that were synced in the last six months. Please note that the default event_timestamp column created by Firebase is in epoch microseconds, and Castled expects timestamps as TIMESTAMP/DATETIME. Therefore, we have created a new column formatted_event_ts, from the original epoch event_timestamp column.

Configure the date suffix accordingly to include the events you are interested in.

3. Create Event Schema in Castled Using Firebase Historical Tables

Firebase tables usually combine all event parameters (like transaction_amount, payment_method) in a single record column. To make these parameters usable, it’s essential to unnest them in the query used for creating an event schema.

Navigate to Settings → Schemas in Castled and create an event schema.

Here is the sample query for your convenience.

select
user_id, formatted_event_ts,

    (select value.string_value from unnest(event_params) where key = 'transaction_amount') as transaction_amount,
    (select value.string_value from unnest(event_params) where key = 'payment_method') as payment_method,

    from `analytics_326728785`.`payment_success_history`

Make sure to select all the attributes from the Firebase table necessary for effective segmentation and personalisation.

Now map the relevant columns(formatted_event_ts in this case and create a relationship to the user schema)

4. Create Segments Combining the Firebase History Tables + Realtime Tables Created by Castled

Once the schema is created and mapped to your core user schema, you can use it to create segments. For example, to create a segment of users who have made a payment of more than 100 dollars in the last six months, use the segment creation tool in Castled.

If you wish to include real-time tables generated by Castled in the segment as well, use the OR filter to combine the two event schemas.