Redshift
Sync Expectations
The following table describes the sync behavior of Fullstory events to Redshift.
| Destination | Sync Interval | Update Window* | Relevant Timestamps | Setup Guide |
|---|---|---|---|---|
| Redshift | Approximately processed_time rounded to the next hour + 1 hour | 99% stable within 3 hours | event_time updated_time processed_time | Redshift Help Doc |
* Update Window: These are observed aggregates and may vary based on the specifics of the data capture for your site or app.
Data Schema
Events Table Schema
The following table contains the schema for the Fullstory events in Redshift. For information on the data types, see the Redshift documentation.
| Field | Type | Description |
|---|---|---|
event_id | VARCHAR(MAX) | The unique identifier for the event. |
event_time | TIMESTAMPTZ | The time in UTC that the event occurred. |
processed_time | TIMESTAMPTZ | The time in UTC that the event was processed by Fullstory's servers. |
updated_time | TIMESTAMPTZ | The time in UTC that event was updated in the database. Set via SYSDATE when merging. |
device_id | BIGINT | The device ID as defined in the base event model. |
session_id | BIGINT | The session ID as defined in the base event model. |
view_id | BIGINT | The view ID as defined in the base event model. |
event_type | VARCHAR(MAX) | The type of this event. |
event_properties | SUPER | A json object containing the associated event properties. |
source_type | VARCHAR(MAX) | The source type of the event. |
source_properties | SUPER | A json object containing the associated source properties. |
Element Definitions Table Schema
The following table contains the schema for Fullstory named element definitions.
| Property | Type | Description |
|---|---|---|
id | VARCHAR(MAX) | The named element's ID. Use the ID to join on the element_definition_id. |
name | VARCHAR(MAX) | The name of the named element. |
description | VARCHAR(MAX) | The description of the named element. |
state | VARCHAR(MAX) | The state of the named element (e.g., active, archived). |
created_time | TIMESTAMPTZ | Timestamp of when the named element was created. |
created_by | VARCHAR(MAX) | The Fullstory user that created the named element. |
modified_time | TIMESTAMPTZ | Timestamp of when the named element was last modified. |
modified_by | VARCHAR(MAX) | The Fullstory user that last modified the named element. |
updated_time | TIMESTAMPTZ | Timestamp of when this record was last synced. |
Event Definitions Table Schema
The following table contains the schema for Fullstory defined event definitions.
| Property | Type | Description |
|---|---|---|
id | VARCHAR(MAX) | The defined event's ID. Use the ID to join on the event_definition_id. |
name | VARCHAR(MAX) | The name of the defined event. |
description | VARCHAR(MAX) | The description of the defined event. |
state | VARCHAR(MAX) | The state of the defined event (e.g., active, archived). |
created_time | TIMESTAMPTZ | Timestamp of when the defined event was created. |
created_by | VARCHAR(MAX) | The Fullstory user that created the defined event. |
modified_time | TIMESTAMPTZ | Timestamp of when the defined event was last modified. |
modified_by | VARCHAR(MAX) | The Fullstory user that modified the defined event. |
updated_time | TIMESTAMPTZ | Timestamp of when this record was last synced. |
Page Definitions Table Schema
The following table contains the schema for Fullstory page definitions.
| Property | Type | Description |
|---|---|---|
id | VARCHAR(MAX) | The page ID. Use the ID to join on the page_definition_id. |
fs_link_id | VARCHAR(MAX) | The ID that can be used to construct a url in the Fullstory app, e.g. https://app.fullstory.com/ui/<org_id>/settings/pages/<link_id> |
name | VARCHAR(MAX) | The name of the page. |
description | VARCHAR(MAX) | The customer-entered description of the page. |
is_user_defined | BOOLEAN | True if the page was defined by the user, False if defined by a Fullstory algorithm. |
state | VARCHAR(MAX) | The state of the page (e.g., active, archived). |
created_time | TIMESTAMPTZ | Timestamp of when the page was created. |
created_by | VARCHAR(MAX) | The user that created the page. |
modified_time | TIMESTAMPTZ | Timestamp of when the page was last modified. |
modified_by | VARCHAR(MAX) | The user that modified the page. |
updated_time | TIMESTAMPTZ | Timestamp of when this record was synced. |
Querying the Events Table
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
source_properties.user_agent.browser::varchar as browser,
source_properties.url.path::varchar as path,
count(1) as rage_clicks
FROM
FULLSTORY_DB.FULLSTORY_ORG_ID.events
WHERE
date(event_time) = '2024-01-01'
AND event_type = 'click'
AND event_properties.fs_rage_count > 0
GROUP BY
browser,
path
ORDER BY
rage_clicks DESC;
Analyze with DBT
Fullstory provides a DBT package to help you get started with Redshift. The package includes models for Fullstory events, sessions, and users.