Analytics Database Sync

Overview

Reelevant can synchronise events collected through your Datasources into a dedicated analytics database. This database powers statistical dashboards, scoring models, and deduplication — giving you a reliable, query-ready store of all purchase and navigation activity. Synchronisation is configured per Datasource through destinations. When a destination is configured, every event processed by that Datasource is automatically written to the analytics database alongside its normal processing.

Supported Datasource Modes

Analytics database sync works across all ingestion modes:

Mode	Behaviour
Ingester (navigation tracking)	Events are written to the analytics database in real time as they arrive.
Pubsub (Kafka / real-time streams)	Events are written to the analytics database in real time as they are consumed.
Worker (batch imports)	Events are written in bulk using optimised load operations at the end of each import job.

Table Types

Two table types are supported, determined by the Datasource subtype:

Purchases

Stores transactional purchase events. Each row represents a single product purchased by a user. All columns are always present in the table; values are set to null when no matching Field Mapping type exists.

Column	Source Field Mapping Type	Description
user	`workflow_user` or `workflow_purchase_user`	The user identifier.
price	`price`	The item price.
reference_id	`reference_id` or `reference_ids`	The product reference purchased.
transaction_id	`transaction_id`	The transaction identifier grouping items in a single order.
transaction_source	`transaction_source`	The origin of the transaction (e.g., online, in-store).
transaction_metadata	`transaction_metadata`	Additional transaction metadata, stored as a structured string.
purchased_at	`datetime`	When the purchase occurred.

Website Events

Stores navigation and behavioural events. Each row represents a single tracked interaction. Mandatory columns — always present in the table, set to null when no matching field exists:

Column	Source Field Mapping Type	Description
clientId	`workflow_user`	The primary user identifier (e.g., CRM ID, logged-in user).
userId	`workflow_onsite_user`	A secondary user identifier (e.g., cookie-based or anonymous session ID).
name	`event_name`	The event name (e.g., page_view, add_to_cart, purchase).
ids	`reference_ids` or `reference_id`	Reference identifiers associated with the event (e.g., product IDs viewed). Always stored as an array.
timestamp	`datetime`	When the event occurred.

Optional columns — included only when the Datasource’s Field Mapping contains the corresponding type:

Column	Source Field Mapping Type	Description
tmpId	`user_tmp_id`	A temporary device or session identifier (e.g., a fingerprint or first-party cookie).
url	`url`	The page URL where the event occurred.
eventId	`event_id`	A unique identifier for this specific event instance.
value	`price`	A numeric value associated with the event (e.g., cart total, product price).
transId	`transaction_id`	A transaction identifier, if the event is transactional.
agent	`user_agent`	The browser user agent, stored as a structured record with browser, OS, and device details.

Multiple Datasources can write to the same analytics table. For example, a Google Tag Manager ingester and a Reelevant Analytics ingester can both feed into the same website events table. The platform maps each Datasource’s fields to the correct canonical columns based on their Field Mapping types.

How Field Mapping Drives the Schema

The analytics database schema is derived from your Datasource’s Field Mapping. You do not need to manually define columns — the platform maps your configured fields to the correct analytics columns based on their assigned semantic type. Each field in your Field Mapping has an assigned type (e.g., User, Event Name, Datetime, URL). The platform matches these types against the canonical column definitions shown in the tables above and writes each value to the corresponding canonical column. For example, if your Datasource has a field named event_time with type Datetime, its value is written to the timestamp column — regardless of the original field name.

Only fields with recognised semantic types (User, Event Name, Datetime, Reference ID, URL, Price, Transaction ID, User Agent, etc.) are written to canonical analytics columns. Fields assigned generic types like “Text” or “Number” are not synchronised to the analytics database.

Configuring a Destination

Destinations are configured in the Datasource’s storage options. Each destination targets a single table in the analytics database:

Parameter	Description
Dataset	The target dataset (database schema). Destinations can only target Reelevant’s tracking events dataset — any other dataset is rejected.
Table name	The table name. Supports the `{COMPANY_ID}` and `{DATASOURCE_ID}` placeholders, which are substituted per company and Datasource. Otherwise limited to letters, numbers, and underscores.
Deduplication	Optional. Enables periodic deduplication for this destination (see Deduplication).

Only the sub-options you provide are updated; the rest of the storage options are left unchanged. Two destinations cannot point at the same dataset and table name.

Destinations are only supported for the two tracking table types — purchases and website events. While the storage options technically accept a destination on any Datasource, synchronisation is only wired up for tracking Datasources.

Destinations are set through the Datasource’s patch step. The example below configures a single BigQuery destination for the Datasource 6718ad69056acb425fbecf1a (replace the bearer token with your access token — see Authentication):

curl -XPOST https://api.reelevant.com/v2/datasources/6718ad69056acb425fbecf1a/steps \
  -H "Authorization: Bearer ${access_token}" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "patch",
    "payload": {
      "storageOptions": {
        "destinations": [
          {
            "type": "bigquery",
            "dataset": "tracking_events_production_eu",
            "tableName": "tracking_events_{COMPANY_ID}",
            "deduplicateOptions": { "enabled": true }
          }
        ]
      }
    }
  }'

Once a destination is configured, synchronisation begins automatically on the next Datasource execution or event ingestion.

Deduplication

Duplicate events can occur due to retries, reprocessing, or upstream data issues. Reelevant provides an optional deduplication mechanism that runs periodically to remove duplicates from the analytics database.

How It Works

A scheduled job inspects all Datasources with deduplication enabled and removes duplicate rows based on a fixed deduplication key per table type:

Table type	Deduplication key
Purchases	`user` + `transaction_id` + `reference_id` — only one row per user/transaction/product combination is kept.
Website Events	`transId` + `ids` when a transaction is present, otherwise `eventId` — ensures each unique event is stored once.

When duplicates are found, the most recent row (by partition time) is kept and older duplicates are removed.

Enabling Deduplication

Deduplication is enabled per destination using the deduplication option:

Parameter	Description
Enabled	Toggle deduplication on or off for this destination.

Deduplication runs on a fixed schedule (every 12 hours). It is not triggered in real time — brief periods of duplicate data may exist between runs.

Prerequisites

Before enabling analytics database sync for a Datasource:

Field Mapping must use semantic types — the Datasource must have fields mapped to the correct semantic types (User, Datetime, Reference ID, Event Name, etc.) so the platform can map them to canonical analytics columns. Fields with generic types (Text, Number) are not synchronised.
The Datasource must be published — only published (active) Datasources write to the analytics database.
A destination must be configured — your Technical Account Manager or administrator will configure the destination dataset and table name for your Datasource.

Migration from Legacy Sync

If your Datasources previously synchronised to the analytics database through an intermediary relay system, the new direct-sync approach replaces it entirely. The behaviour is the same — events are written to the same tables with the same schema — but with lower latency and simpler architecture. No action is required on your side. Your administrator will update the Datasource configuration to use the new destination-based approach, and synchronisation continues seamlessly.

Getting Started

DataHub Deep Dive

Advanced Workflows

Email Rendering

Transformations

Advanced Analytics

Overview

Supported Datasource Modes

Table Types

Purchases

Website Events

How Field Mapping Drives the Schema

Configuring a Destination

Deduplication

How It Works

Enabling Deduplication

Prerequisites

Migration from Legacy Sync

​Overview

​Supported Datasource Modes

​Table Types

​Purchases

​Website Events

​How Field Mapping Drives the Schema

​Configuring a Destination

​Deduplication

​How It Works

​Enabling Deduplication

​Prerequisites

​Migration from Legacy Sync

Overview

Supported Datasource Modes

Table Types

Purchases

Website Events

How Field Mapping Drives the Schema

Configuring a Destination

Deduplication

How It Works

Enabling Deduplication

Prerequisites

Migration from Legacy Sync