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

# Syncing Custom Events from a Warehouse

> A guide for data administrators setting up a data warehouse sync that loads product usage and other activity into Conversion as custom events.

This guide is for data administrators who know their warehouse but are new to Conversion. It walks through planning and building a sync that loads activity rows, such as logins, feature usage, or purchases, into Conversion as custom events on contacts. For schedules and incremental syncing in general, see the [Data Warehouse Sync overview](https://docs.conversion.ai/product-docs/sync/data-warehouse/overview.md).

The short version: **write one `SELECT` that returns one row per event, with columns named `user_id` or `email`, `event_name`, `timestamp`, and `event_id`. Every other column becomes a property of the event.**

***

## How warehouse rows map to Conversion

A custom event records that a contact did something at a point in time. Unlike contacts and custom objects, events are never updated: each row adds one event to one contact.

| In your warehouse | In Conversion |
| :- | :- |
| An events table, or a table with one row per action | A custom event sync |
| A row | One event on one contact's [activity log](https://docs.conversion.ai/product-docs/records/contacts/activity.md#custom-events) |
| The user's ID or email | The contact the event belongs to |
| A column with the action's name, or a fixed string | The event name, such as `feature_activated` |
| The time the action happened | The event's timestamp |
| The row's primary key, prefixed with the table name | The event ID, used to skip duplicates |
| Any other column | An event property in the event's data |

Once synced, marketers can use the events in:

* [Workflow triggers](https://docs.conversion.ai/product-docs/workflows/triggers/event-based.md#custom-events), with **Custom event occurred**
* [Audience and query filters](https://docs.conversion.ai/product-docs/unified-query-engine/overview.md#custom-event)
* [Lead scoring rules](https://docs.conversion.ai/product-docs/scoring/rules.md)

***

## Column reference

Conversion reads the column names your query returns, ignoring case. It doesn't use field keys or a mapping screen for events.

| Column | Required | What it does |
| :- | :- | :- |
| `user_id` | One of `user_id` or `email` | Matches the contact by its user ID. `userId` also works. |
| `email` | One of `user_id` or `email` | Matches the contact by email. `email_address` also works. |
| `event_name` | Yes | The event's name. Must be named exactly `event_name`. |
| `timestamp` | Recommended | When the event happened. Without it, the event is stamped with the time it was synced. See [Timestamps](#timestamps). |
| `event_id` | Recommended | An ID that is unique across every event sync in your workspace, used to skip duplicates. Must be named exactly `event_id`. See [Duplicates and backfills](#duplicates-and-backfills). |
| Any other column | No | Stored as an event property, keyed by the column name in lowercase. Null values are left out. |

***

## Set up a custom event warehouse sync

### Before you begin

* A [connected warehouse](https://docs.conversion.ai/product-docs/sync/data-warehouse/overview.md#1-connect-your-warehouse) with a read-only user that can see the tables you plan to sync
* Admin access in Conversion to create syncs
* Your contacts already in Conversion, from a [contacts sync](https://docs.conversion.ai/product-docs/sync/data-warehouse/overview.md#contacts) or another source. An event sync only creates contacts if you turn on **Create contacts for unmatched emails**.

### 1. Agree on the events with your marketers

Before you write any SQL, agree with your marketers on which events to send and what they need from each one. Triggers, filters, and scoring rules match events by name and filter on their properties, so the names and properties you choose are what marketers will build with.

| Ask | Why it matters |
| :- | :- |
| Which actions should start a workflow or change a score? | Each becomes an event name. Send only the actions someone will use. |
| What do you want to filter or personalize on? | Each value, such as `plan` or `feature_name`, must be a column in the query. |
| How quickly should a workflow react? | Sets the [sync schedule](https://docs.conversion.ai/product-docs/sync/data-warehouse/overview.md#3-set-the-schedule). |
| How much history do you need? | Decides whether the first run backfills all history or starts from a recent date. |

Write down each event name and its properties before you start. Keep names stable: renaming an event later means updating every trigger and filter that uses it.

### 2. Write the query

Return one row per event. Name the columns as in the [column reference](#column-reference), and convert the timestamp to Unix seconds as text. This example uses Snowflake syntax:

```sql theme={null}
SELECT
  u.id AS user_id,
  u.email,
  'feature_activated' AS event_name,
  TO_VARCHAR(DATE_PART(EPOCH_SECOND, a.created_at)) AS timestamp,
  'feature_activation:' || a.id AS event_id,
  a.feature_name,
  a.plan
FROM analytics.feature_activations a
JOIN analytics.users u ON a.user_id = u.id
WHERE a.created_at > TO_TIMESTAMP({{last_sync_time}})
```

To sync several event types from one table, select the type column as `event_name` instead of a fixed string:

```sql theme={null}
SELECT
  e.user_id,
  e.event_type AS event_name,
  TO_VARCHAR(DATE_PART(EPOCH_SECOND, e.occurred_at)) AS timestamp,
  'product_event:' || e.id AS event_id,
  e.properties_plan AS plan
FROM analytics.product_events e
WHERE e.event_type IN ('feature_activated', 'invite_sent', 'report_exported')
  AND e.occurred_at > TO_TIMESTAMP({{last_sync_time}})
```

Filter to the event types your marketers agreed on. Every row becomes an event, so an unfiltered events table can send millions of events nobody uses.

The timestamp and `last_sync_time` syntax differ by warehouse. See [Timestamps](#timestamps) and your warehouse's guide: [BigQuery](https://docs.conversion.ai/product-docs/sync/data-warehouse/big-query.md), [Snowflake](https://docs.conversion.ai/product-docs/sync/data-warehouse/snowflake.md), [ClickHouse](https://docs.conversion.ai/product-docs/sync/data-warehouse/clickhouse.md), or [Databricks](https://docs.conversion.ai/product-docs/sync/data-warehouse/databricks.md).

### 3. Create the sync

1. Go to **Settings → CRM and syncing → Syncs**.
2. Click **Add sync**.
3. Enter a **Name** and **Description**, and pick the **Connection**.
4. For **Object**, choose **Custom events**.
5. Set the **Frequency**. Events always use the **Upsert** mode.
6. Turn on **Create contacts for unmatched emails** if events should create contacts that don't exist yet. This requires an `email` column.
7. Turn on **Subscribe new contacts** if the contacts this sync creates should be subscribed to email.
8. Click **Edit query**, paste your query, and click **Preview** to check the first 100 rows.
9. Click **Create sync**.
10. Open the sync and click **Activate**.

See [Data Warehouse Connections and Syncs](https://docs.conversion.ai/product-docs/workspace-settings/crm-and-syncing/data-warehouse.md#creating-a-sync) for more on each setting.

### 4. Check the results

1. Open the sync's **Logs** tab and check the rows processed and any skipped or failed rows. See [Common mistakes](#common-mistakes) for what each reason means.
2. Open a contact you expect to have events, go to its activity log, and filter to **Custom events**. Confirm the event name, time, and properties.
3. Share the result with your marketers and confirm they can build the triggers, filters, and scoring rules you agreed on.

Onboarding with Conversion? Let your Deployment Strategist know when this is done and we'll check it on our end.

***

## How contacts are matched

Each row must identify one contact with `user_id`, `email`, or both.

1. If the row has a `user_id`, Conversion looks for the contact with that user ID.
2. If none is found and the row has an `email`, Conversion looks for the contact with that email.
3. If the email matches a contact that has a different user ID, the row is skipped as **Conflicting identifier**.
4. If no contact matches, the row is skipped as **Contact not found**, unless **Create contacts for unmatched emails** is on. Then Conversion creates the contact from the row's email (and user ID, if present). Rows without a valid email are still skipped.

Use the same identifier your contacts sync uses. Prefer `user_id` when you have it, since it doesn't change when a user updates their email.

***

## Timestamps

Name the column `timestamp`. Conversion stores it in UTC.

* Send it as **text**. A numeric value fails with **Invalid timestamp**, so cast it to a string in your query.
* Unix seconds (`1730073600`) are the safest format. Unix milliseconds and ISO 8601 strings (`2026-10-28T00:00:00Z`) also parse.
* If the column is missing or the value is null, the event is stamped with the time it was synced.

| Warehouse | Expression |
| :- | :- |
| BigQuery | `CAST(UNIX_SECONDS(created_at) AS STRING) AS timestamp` |
| Snowflake | `TO_VARCHAR(DATE_PART(EPOCH_SECOND, created_at)) AS timestamp` |
| ClickHouse | `toString(toUnixTimestamp(created_at)) AS timestamp` |
| Databricks | `CAST(unix_timestamp(created_at) AS STRING) AS timestamp` |

`{{last_sync_time}}` filters which rows a run reads. The `timestamp` column sets when each event happened. They can come from different columns: if events can land in your warehouse late, filter on the time the row was loaded and send the time the action happened as `timestamp`.

***

## Duplicates and backfills

With an `event_id`, Conversion keeps one event per ID. If a later run sends the same `event_id` again, for example because of a [time buffer](https://docs.conversion.ai/product-docs/sync/data-warehouse/overview.md#time-buffered-queries) or a re-run, no second event is created. Repeats within three days show in the sync's logs as **Duplicate event**. Without an `event_id`, every row creates a new event, so overlapping runs create duplicates.

An `event_id` must be unique across every event sync in your workspace, and across events sent through the Events API. Conversion doesn't scope it by sync or event name, so if two tables both have a row with ID `123`, one event silently replaces the other. Prefix the table's primary key with the table or event name, such as `'order:' || o.id`. If the table has no single key, build one that is stable across runs, such as the user ID, event name, and timestamp joined together.

On the first run, `{{last_sync_time}}` is `0`, so the query returns all history. To start from a recent date instead, add a fixed lower bound to the `WHERE` clause.

> [!WARNING]
>
> Before the first run of a sync with a lot of history, check with your marketers which workflows and lead scoring rules use these event names, so a large backfill doesn't surprise them.

***

## Common mistakes

| Symptom | Likely cause | Fix |
| :- | :- | :- |
| **Activate** says the sync needs an event name | No column is named `event_name` | Alias the name column, or a fixed string, `AS event_name` |
| **Activate** says the query needs a userId or email column | No column is named `user_id`, `userId`, or `email` | Alias the user's ID or email to one of those names |
| Every event shows the time of the sync | No `timestamp` column, or it's named something else | Alias the event time `AS timestamp` |
| Rows fail with **Invalid timestamp** | The value is a number, or a format Conversion can't parse | Cast it to a string of Unix seconds, as in [Timestamps](#timestamps) |
| Rows skip with **Contact not found** | The contact doesn't exist in Conversion | Sync contacts first, or turn on **Create contacts for unmatched emails** |
| Rows skip with **Conflicting identifier** | The email belongs to a contact with a different user ID | Check that `user_id` uses the same IDs as your contacts sync |
| Rows skip with **Missing event name** | `event_name` is null or empty for those rows | Filter out those rows, or fill the name with `COALESCE` |
| Duplicate events after overlapping runs | No `event_id`, or it's named `eventId` | Alias a unique key `AS event_id` |
| Events from one sync are missing or replaced by another sync's events | Two syncs send the same `event_id` values, such as raw primary keys | Prefix `event_id` with the table or event name |
| An `event_id` or `created_at` value appears as a property | The column name isn't recognized | Rename it to `event_id` or `timestamp` |


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