Skip to main content
Version: v2

BigQuery

Sync Expectations

The following table describes the sync behavior of Fullstory events to BigQuery.

DestinationSync IntervalUpdate Window*Relevant TimestampsSetup Guide
BigQueryApproximately processed_time rounded to the next hour + 1 hour99% stable within 3 hoursevent_time
updated_time
processed_time
BigQuery Help Doc

* Update Window: These are observed aggregates and may vary based on the specifics of the data capture for your site or app.

Events Table Schema

The following table contains the schema for the Fullstory events in BigQuery. For information on the data types, see Google's documentation.

FieldTypeDescription
event_idSTRINGThe unique identifier for the event.
event_timeTIMESTAMPThe time in UTC that the event occurred.
processed_timeTIMESTAMPThe time in UTC that the event was processed by Fullstory's servers.
updated_timeTIMESTAMPThe time in UTC that event was updated in the database. Set via CURRENT_TIMESTAMP when merging.
device_idINTEGERThe device ID as defined in the base event model.
session_idINTEGERThe session ID as defined in the base event model.
view_idINTEGERThe view ID as defined in the base event model.
event_typeSTRINGThe type of this event.
event_propertiesSTRINGA json string containing the associated event properties.
source_typeSTRINGThe source type of the event.
source_propertiesSTRINGA json string containing the associated source properties.

The table will be partitioned by day on the event_time column, so you will almost always want to include a filter on event_time in your queries to avoid scanning the entire table. For more info, see the Google documentation on partitioned tables.

Additionally, you may want to consider enabling Physical Billing to reduce the storage costs for this table.

Querying the Events Table

When querying the JSON fields in BigQuery, you should favor parsing with PARSE_JSON in a subquery to avoid the overhead of running JSON_EXTRACT_VALUE which combines a parse and then access via path.

To demonstrate how to query the events table, the following example shows how to count the number of rage clicks broken down by browser and URL for a single day.

SELECT
JSON_VALUE(source_properties, '$.user_agent.browser') AS browser,
JSON_VALUE(source_properties, '$.url.path') AS path,
COUNT(1) AS rage_clicks
FROM (
SELECT
event_id,
event_time,
processed_time,
updated_time,
device_id,
session_id,
view_id,
event_type,
PARSE_JSON(event_properties, wide_number_mode=>'round') AS event_properties,
source_type,
PARSE_JSON(source_properties, wide_number_mode=>'round') AS source_properties
FROM
`project.dataset.fullstory_events_<org_id>` ) parsed
WHERE
DATE(event_time) = '2024-01-01'
AND event_type = 'click'
AND CAST(JSON_VALUE(event_properties, '$.fs_rage_count') AS INTEGER) > 0
GROUP BY
browser,
path
ORDER BY
rage_clicks DESC;

Analyze with DBT

To help manage the complexity of these queries, Fullstory provides a DBT package to help you get started with BigQuery. The package includes models for Fullstory events, sessions, and users.