> ## Documentation Index
> Fetch the complete documentation index at: https://docs.castled.io/llms.txt
> Use this file to discover all available pages before exploring further.

# 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:

```sql
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.

![firebase segment 1](https://mintlify.s3-us-west-1.amazonaws.com/castled/images/firebase-segment/firebase_seg_1.png)

![firebase segment 2](https://mintlify.s3-us-west-1.amazonaws.com/castled/images/firebase-segment/firebase_seg_2.png)

Here is the sample query for your convenience.

```sql
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)
![firebase segment 3](https://mintlify.s3-us-west-1.amazonaws.com/castled/images/firebase-segment/firebase_seg_3.png)

### 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.
![firebase segment 4](https://mintlify.s3-us-west-1.amazonaws.com/castled/images/firebase-segment/firebase_seg_4.png)

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.
![firebase segment 5](https://mintlify.s3-us-west-1.amazonaws.com/castled/images/firebase-segment/firebase_seg_5.png)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.