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

# Analytics Database Sync

> How Datasources synchronise events to the analytics database used for statistics and scoring

## 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](/advanced-guide/datahub/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. |

<Info>
  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.
</Info>

## How Field Mapping Drives the Schema

The analytics database schema is derived from your Datasource's [Field Mapping](/advanced-guide/datahub/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.

<Info>
  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.
</Info>

## 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](#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.

<Info>
  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.
</Info>

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](/developer-docs/api-reference/authentication)):

```bash theme={"theme":{"light":"github-light","dark":"github-dark"}}
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. |

<Info>
  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.
</Info>

## Prerequisites

Before enabling analytics database sync for a Datasource:

1. **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.
2. **The Datasource must be published** — only published (active) Datasources write to the analytics database.
3. **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.