Queries for each sync type

Every reverse ETL sync runs a SQL query that you write. This page shows what each sync type expects, with examples for each database we support.

When you create a sync, you provide a query selecting the people or objects you want to import, and their properties. You build your queries using the same principles as our Pipelines API: each row your query returns becomes one API call.

Choose your database from the Database menu, and every example on the page switches to it.

Each row returned from your query represents an individual operation (like an identify call, a track event, etc). Columns represent the traits or properties that you want to apply to the person, group, or event that your sync imports.

While we support queries that return millions of rows and hundreds of columns, syncing large amounts of data more then once a day can impact your account’s performance—including delaying automations or messages. When you set up your query, consider how much data you want to send and how often; and make sure you limit your results using the last_sync_time.

Make sure you compare timestamps against last_sync_time

Our examples below include a last_sync_time value. You must compare a timestamp to this value to avoid sending duplicate traffic to Customer.io which could impact your workspace’s performance.

Column names and data types

Redshift lowercases unquoted identifiers. Customer.io recognizes identifiers like userId and groupId in any case, but every other column becomes an attribute named exactly as Redshift returns it, and attribute names are case-sensitive. An alias like firstName arrives as firstname. Double-quote the aliases you want to preserve:

SQL
SELECT id AS "userId", email_address AS "email"
FROM users
WHERE last_updated >= {{last_sync_time}}

Enable case-sensitive identifiers in your Redshift cluster

By default, Redshift ignores the casing of double-quoted identifiers. Set enable_case_sensitive_identifier to true for double-quoted aliases to preserve their casing.

Enable it for your cluster or workgroup in the parameter group settings, or set it per session:

SQL
SET enable_case_sensitive_identifier TO true;

Set it in your parameter group so it applies to every session, including the queries Customer.io runs on your behalf.

Databricks ARRAY, MAP, and STRUCT columns arrive in Customer.io as structured JSON. An ARRAY column becomes an array attribute, and a STRUCT or MAP column becomes an object attribute. You don’t need to serialize these types yourself. Select the column and we handle the conversion.

Databricks resolves unqualified table names against the catalog and schema on your connection. Fully qualify anything else as catalog.schema.table.

When you set up a sync, you’re modeling your data in the Pipelines API format. Some attributes in our API are nested, and you might want to nest others based on how you expect them in Customer.io or in the destinations you send data to.

To nest data, build a json_object from items in your query, alternating keys and values in order: key, value, key, value.

SQL
SELECT
   id AS groupId,
   name,
   json_object(
      'job_title', job_title,
      'primary_contact', primary_contact
   ) AS relationshipAttributes

You do the same for other nested objects, like device context. See the Pipelines API documentation for the structure of our payloads.

Map column names to attributes in Customer.io by aliasing each column to the name you want in Customer.io, and we use it as written.

Map column names to attributes in Customer.io by aliasing each column to the name you want in Customer.io, and we use it as written.

PostgreSQL lowercases unquoted aliases. Customer.io recognizes identifiers like userId and groupId in any case, but every other column becomes an attribute named exactly as PostgreSQL returns it. An alias like firstName arrives as firstname. Double-quote an alias to keep its case, like first_name AS "firstName".

Snowflake column names are in all caps, but Customer.io and the destinations you send Snowflake data to don’t typically expect property names formatted that way. You’ll need to convert your column names to lowercase, snake-cased formats, or whichever format fits your data model outside of Snowflake. If you have a column named EMAIL, convert it to email to use it as an identifier in Customer.io Journeys.

Queries aren’t case-sensitive, but results are. Snowflake returns the same rows whether you write EMAIL, email, or Email. The column name stays EMAIL unless you change it with AS, and that name is the trait we record in Customer.io.

SQL
SELECT ID AS "id", EMAIL AS "email", PRIMARY_PHONE AS "phone"
FROM people
WHERE LAST_UPDATED >= {{last_sync_time}}

If Snowflake returns uppercase attributes despite the AS clause, adjust the QUOTED_IDENTIFIERS_IGNORE_CASE setting for your Snowflake user or role. QUOTED_IDENTIFIERS_IGNORE_CASE controls whether Snowflake treats quoted identifiers as case-sensitive. Set it at the role or user level rather than the account level, so you don’t affect other consumers of your Snowflake data.

SQL
ALTER USER customerio_reverse_etl_user
  SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;

-- or, for the Customer.io role
ALTER ROLE customerio_reverse_etl
  SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;

Snowflake stores boolean values as binary (0 and 1) to reduce file size on disk, and its UI may display them as “True” or “False” even though the underlying data is binary. As Snowflake’s documentation puts it: “BINARY input/output can be confusing because what you see is not necessarily what you get.”

Convert binary booleans to strings with Snowflake’s IFF function, so your teammates can set branch and action conditions on true/false rather than 1/0:

SQL
SELECT
  ID AS "userId",
  IFF(IS_ACTIVE, 'true', 'false') AS "is_active"
FROM
  users
WHERE
  LAST_UPDATED >= {{last_sync_time}}

last_sync_time and limiting your results

We strongly recommend that you index a column in your database representing the date-time each row was last-updated. When you write your query, you should add a WHERE clause comparing your “last updated” column to the {{last_sync_time}}.

The last sync time is a Unix timestamp representing the date-time when the previous successful sync started. Comparing a “last-updated” column to this timestamp helps you limit your sync operations to the columns that changed since the previous sync.This helps you avoid syncing the same records repeatedly, which can make your syncs take longer and impact your workspace’s performance.

If you use ISO date-times, you can convert them to Unix timestamps in your query.

This value is 0 until at least one sync is Completed

If you’re just getting started, or if all of your previous syncs have a Some Rows Failed status, this value is 0. If your previous syncs show Some Rows Failed, you should download the error report and fix those errors so that an import finishes completely and the last_sync_time obtains a non-zero value.

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

We replace the placeholder with a plain number of Unix seconds. If your column stores a TIMESTAMP or a DATE, Databricks won’t compare it to a number, and the query fails with a type error. You need to convert the placeholder to match your column:

SQL
SELECT user_id AS userId, email
FROM users
WHERE updated_at >= timestamp_seconds({{last_sync_time}})

You can convert either side of the comparison. We recommend converting the placeholder, because Databricks applies timestamp_seconds() to a single number, while unix_timestamp(updated_at) runs against every row the query scans.

The Databricks examples on this page assume TIMESTAMP columns and use this conversion. If your column stores Unix seconds instead, compare it to {{last_sync_time}} directly.

The Databricks examples use >= rather than >. A strict > can permanently skip a row whose timestamp exactly matches the last sync time. With >=, the sync reads that row again on the next run, and we recognize it as a duplicate and don’t process it twice. Compare against a timestamp that changes per row, like a last-updated column, so the number of rows the sync reads again stays small.

When you preview a query, we set {{last_sync_time}} to 30 days ago. To use a different date, click the gear icon next to the last_sync_time date in the preview. If your table’s timestamps are older than the preview date, the preview can return no rows even though a real sync would.

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

Identify

The identify method tells us who someone is and lets you assign unique traits to a person. Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

You can identify people by anonymousId and/or userId.

  • anonymousId only: This assigns traits to a person before you know who they are.
  • userId only: Identifies a user and sets traits.
  • both userId and anonymousId: Associates the data from the anonymousId with the person you identify by userId.
SQL
SELECT id AS "userId", email_address AS "email", fname, lname, msisdn AS "phone"
FROM users
WHERE last_updated >= {{last_sync_time}}
SQL
SELECT user_id AS userId, email, phone, first_name, last_name
FROM users
WHERE updated_at >= timestamp_seconds({{last_sync_time}})
SQL
SELECT id AS userId, email_address AS email, fname, lname, msisdn AS phone
FROM users
WHERE last_updated >= {{last_sync_time}}
SQL
SELECT id AS userId, email_address AS email, fname, lname, msisdn AS phone
FROM users
WHERE last_updated >= {{last_sync_time}}
SQL
SELECT id AS userId, email_address AS email, fname, lname, msisdn AS phone
FROM users
WHERE last_updated >= {{last_sync_time}}
SQL
SELECT id AS userId, email_address AS email, fname, lname, msisdn AS phone
FROM users
WHERE last_updated >= {{last_sync_time}}
SQL
SELECT ID AS "userId", EMAIL AS "email", MSISDN AS "phone", FNAME AS "first_name", LNAME AS "last_name"
FROM users
WHERE TIMESTAMP >= {{last_sync_time}}
One of:
  • userIdstringrequired
    The unique identifier for a person. This value should be unique across systems, so you recognize the same person in your sources _and_ destinations.
  • anonymousIdstring
    A unique substitute for a User ID in cases when you don’t have an absolutely unique identifier. Our libraries generate this value automatically to help you track people before they sign up, log in, provide their email, etc.
  • A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.
  • Contains a list of booleans indicating the integrations that are enabled (true) or disabled (false). By default, all integrations are enabled (returning an empty object). Set "All": false to reverse this behavior.
  • messageIdstring
    A unique identifier for a Pipelines call, ensuring that each individual event is unique. This is set by Customer.io
  • originalTimestampstring(date-time)
    In general, you can use timestamp rather than this field if you want to back-date events. This is the timestamp on the client device you invoke a call or the timestamp value you manually passed in a server-side library call.
  • sentAtstring(date-time)
    The ISO-8601 timestamp when a library sends an event to Pipelines.
  • timestampstring(date-time)
    The ISO-8601 timestamp when the event originally took place. This is mostly useful when you backfill past events. If you're not backfilling data, you can leave this field empty and we'll use the current time or server time.
  • Additional properties that you know about a person. We've listed some common/reserved traits below, but you can add any traits that you might use in another system.

Identify people by email or ID

If you identify people by email and by a unique ID, use a CASE statement or the COALESCE function to set the userId to the customer ID when it’s available and fall back to email for people who don’t have one yet. This is common when you support both leads, identified by email, and customers, identified by a unique ID after they convert.

COALESCE returns the first value in the list that isn’t null:

SQL
SELECT
  COALESCE(CAST(user_id AS VARCHAR), email) AS "userId",
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

CASE checks whether user_id exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

SQL
SELECT
  CASE
    WHEN user_id IS NOT NULL THEN CAST(user_id AS VARCHAR)
    ELSE email
  END AS "userId",
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

COALESCE returns the first value in the list that isn’t null:

SQL
SELECT
  COALESCE(CAST(user_id AS STRING), email) AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE updated_at >= timestamp_seconds({{last_sync_time}})

CASE checks whether user_id exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

SQL
SELECT
  CASE
    WHEN user_id IS NOT NULL THEN CAST(user_id AS STRING)
    ELSE email
  END AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE updated_at >= timestamp_seconds({{last_sync_time}})

COALESCE returns the first value in the list that isn’t null:

SQL
SELECT
  COALESCE(CAST(user_id AS STRING), email) AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

CASE checks whether user_id exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

SQL
SELECT
  CASE
    WHEN user_id IS NOT NULL THEN CAST(user_id AS STRING)
    ELSE email
  END AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

COALESCE returns the first value in the list that isn’t null:

SQL
SELECT
  COALESCE(CAST(user_id AS NVARCHAR(255)), email) AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

CASE checks whether user_id exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

SQL
SELECT
  CASE
    WHEN user_id IS NOT NULL THEN CAST(user_id AS NVARCHAR(255))
    ELSE email
  END AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

COALESCE returns the first value in the list that isn’t null:

SQL
SELECT
  COALESCE(CAST(user_id AS CHAR), email) AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

CASE checks whether user_id exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

SQL
SELECT
  CASE
    WHEN user_id IS NOT NULL THEN CAST(user_id AS CHAR)
    ELSE email
  END AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

COALESCE returns the first value in the list that isn’t null:

SQL
SELECT
  COALESCE(user_id::TEXT, email) AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

CASE checks whether user_id exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

SQL
SELECT
  CASE
    WHEN user_id IS NOT NULL THEN user_id::TEXT
    ELSE email
  END AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

You need to use AS with quotes so the column name stays lowercase.

COALESCE returns the first value in the list that isn’t null:

SQL
SELECT
  COALESCE(CAST(USER_ID AS VARCHAR), EMAIL) AS "userId",
  EMAIL AS "email",
  FIRST_NAME AS "first_name",
  LAST_NAME AS "last_name"
FROM users
WHERE LAST_UPDATED >= {{last_sync_time}}

CASE checks whether USER_ID exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

SQL
SELECT
  CASE
    WHEN USER_ID IS NOT NULL THEN CAST(USER_ID AS VARCHAR)
    ELSE EMAIL
  END AS "userId",
  EMAIL AS "email",
  FIRST_NAME AS "first_name",
  LAST_NAME AS "last_name"
FROM users
WHERE LAST_UPDATED >= {{last_sync_time}}

Track

The track method records things people do. Every track call represents an event.

You should track your audience’s activities with events both as performance indicators and so you can respond to your audience’s activities with automations in Journeys. For example, if your audience performs a Video Viewed or Item Purchased event, you might respond with other videos or products the person might enjoy.

Track calls require an event name describing what a person did. They must also include an anonymousId or a userId. Calls that you make with an anonymousId are associated with a userId when you identify someone by their userId.

In most cases, your query should compare a timestamp to the last_sync_time to ensure that you only import new events.

SQL
SELECT id AS "userId", event_name AS event, products, total_price AS value
FROM events
WHERE timestamp > {{last_sync_time}}
SQL
SELECT user_id AS userId, event_name AS event, products, total_price AS value
FROM events
WHERE created_at >= timestamp_seconds({{last_sync_time}})
SQL
SELECT id AS userId, event_name AS event, products, total_price AS value
FROM events
WHERE timestamp > {{last_sync_time}}
SQL
SELECT id AS userId, event_name AS event, products, total_price AS value
FROM events
WHERE timestamp > {{last_sync_time}}
SQL
SELECT id AS userId, event_name AS event, products, total_price AS value
FROM events
WHERE timestamp > {{last_sync_time}}
SQL
SELECT id AS userId, event_name AS event, products, total_price AS value
FROM events
WHERE timestamp > {{last_sync_time}}
SQL
SELECT ID AS "userId", EVENT_NAME AS "event", PRODUCTS AS "products", TOTAL_PRICE AS "value"
FROM events
WHERE TIMESTAMP > {{last_sync_time}}
One of:
  • eventstringrequired
    The name of the event
  • userIdstringrequired
    The unique identifier for a person. This value should be unique across systems, so you recognize the same person in your sources _and_ destinations.
  • contextone of
    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.
    One of:
    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.
    • activeboolean
      Whether a user is active.

      This is usually used when you send an .identify() call to update the traits independently of when you've “last seen” a user.

    • Contains information about the campaign that resulted in the API call, gathered from, or mapping to, UTM parameters (e.g. utm_source).
    • channelstring
      The channel the event originated from.
      Accepted values: browser, server, mobile
    • ipstring
      The user's IP address. This isn't captured by our libraries, but by our servers when we receive client-side events (like from our JavaScript source).
    • localestring
      The locale string for the current user, e.g. en-US.
    • Contains information about the current page in the browser. This is automatically collected by our JavaScript source.
    • userAgentstring
      The user agent of the device making the request
  • integrationsobject
    Contains a list of booleans indicating the integrations that are enabled (true) or disabled (false). By default, all integrations are enabled (returning an empty object). Set "All": false to reverse this behavior.
    • Enabled/Disabled integrations *boolean
  • messageIdstring
    A unique identifier for a Pipelines call, ensuring that each individual event is unique. This is set by Customer.io
  • originalTimestampstring(date-time)
    In general, you can use timestamp rather than this field if you want to back-date events. This is the timestamp on the client device you invoke a call or the timestamp value you manually passed in a server-side library call.
  • propertiesobject
    Additional properties for your event.
    • Event Properties *any type
      Additional properties that you want to capture in the event. These can take any JSON shape.
  • sentAtstring(date-time)
    The ISO-8601 timestamp when a library sends an event to Pipelines.
  • timestampstring(date-time)
    The ISO-8601 timestamp when the event originally took place. This is mostly useful when you backfill past events. If you're not backfilling data, you can leave this field empty and we'll use the current time or server time.

Backfilling events

In your initial sync, the last_sync_time is 0, and we’ll capture all events that otherwise match your query. After that, we only capture events that occur after the last_sync_time—events that occurred since the previous sync. This prevents you from importing the same events multiple times, but also means that you can’t backfill event history.

If you need to backfill event history after your initial sync, you’ll need to set up a new sync to import the events you want to backfill. In general, you’ll:

  1. Create a new sync with a new query that captures the events you want to backfill.
  2. Run the sync to backfill events.
  3. Disable the backfilling sync so that you don’t capture events that your normal event query would otherwise import.

Group

The Group method associates a person with a group—like a company, organization, project, online class or any other collective noun you come up with for the same concept. In Customer.io Journeys, we call groups objects . If the group/object or person in your group call don’t exist, this operation creates them.

Group calls require a groupId to represent the group. In almost every case, a group call should also include a userId to associate the person with the group. You can also include traits to provide additional information about the group (or the relationship between the person and the group). Find more details about the group method in our API specifications.

Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

SQL
SELECT companyId AS "groupId", objectTypeId AS "objectTypeId", companyname, employees, personId AS "userId"
FROM companies
WHERE last_updated >= {{last_sync_time}}
SQL
SELECT company_id AS groupId, object_type_id AS objectTypeId, company_name, employees, user_id AS userId
FROM companies
WHERE updated_at >= timestamp_seconds({{last_sync_time}})

Remember, group calls represent both an organization/group and relationships with users (by userId). Your query should include not only the groupId, but the userId so that you can capture relationships between users and groups.

If the userId doesn’t exist in Customer.io, we’ll create a new person to represent the new userId and their relationship to the group.

SQL
SELECT companyId AS groupId, objectTypeId, companyname, employees, personId AS userId
FROM companies
WHERE last_updated >= {{last_sync_time}}

Remember, group calls represent both an organization/group and relationships with users (by userId). Your query should include not only the groupId, but the userId so that you can capture relationships between users and groups.

If the userId doesn’t exist in Customer.io, we’ll create a new person to represent the new userId and their relationship to the group.

SQL
SELECT companyId AS groupId, objectTypeId, companyname, employees, personId AS userId
FROM companies
WHERE last_updated >= {{last_sync_time}}

Remember, group calls represent both an organization/group and relationships with users (by userId). Your query should include not only the groupId, but the userId so that you can capture relationships between users and groups.

If the userId doesn’t exist in Customer.io, we’ll create a new person to represent the new userId and their relationship to the group.

SQL
SELECT companyId AS groupId, objectTypeId, companyname, employees, personId AS userId
FROM companies
WHERE last_updated >= {{last_sync_time}}

Remember, group calls represent both an organization/group and relationships with users (by userId). Your query should include not only the groupId, but the userId so that you can capture relationships between users and groups.

If the userId doesn’t exist in Customer.io, we’ll create a new person to represent the new userId and their relationship to the group.

SQL
SELECT companyId AS groupId, objectTypeId, companyname, employees, personId AS userId
FROM companies
WHERE last_updated >= {{last_sync_time}}
SQL
SELECT COMPANYID AS "groupId", OBJECT_TYPE_ID AS "objectTypeId", COMPANYNAME AS "company_name", EMPLOYEES AS "employees", PERSONID AS "userId"
FROM companies
WHERE LAST_UPDATED >= {{last_sync_time}}

Include objectTypeId when you send data to Customer.io

Customer.io supports different kinds of groups (called objects ) where each object has an object type represented by an incrementing integer beginning at 1. If you send group calls to Customer.io, include traits.objectTypeId or we’ll assume that the object type is 1.

One of:
  • groupIdstringrequired
    ID of the group
  • contextone of
    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.
    One of:
    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.
    • activeboolean
      Whether a user is active.

      This is usually used when you send an .identify() call to update the traits independently of when you've “last seen” a user.

    • Contains information about the campaign that resulted in the API call, gathered from, or mapping to, UTM parameters (e.g. utm_source).
    • channelstring
      The channel the event originated from.
      Accepted values: browser, server, mobile
    • ipstring
      The user's IP address. This isn't captured by our libraries, but by our servers when we receive client-side events (like from our JavaScript source).
    • localestring
      The locale string for the current user, e.g. en-US.
    • Contains information about the current page in the browser. This is automatically collected by our JavaScript source.
    • userAgentstring
      The user agent of the device making the request
  • integrationsobject
    Contains a list of booleans indicating the integrations that are enabled (true) or disabled (false). By default, all integrations are enabled (returning an empty object). Set "All": false to reverse this behavior.
    • Enabled/Disabled integrations *boolean
  • messageIdstring
    A unique identifier for a Pipelines call, ensuring that each individual event is unique. This is set by Customer.io
  • originalTimestampstring(date-time)
    In general, you can use timestamp rather than this field if you want to back-date events. This is the timestamp on the client device you invoke a call or the timestamp value you manually passed in a server-side library call.
  • sentAtstring(date-time)
    The ISO-8601 timestamp when a library sends an event to Pipelines.
  • timestampstring(date-time)
    The ISO-8601 timestamp when the event originally took place. This is mostly useful when you backfill past events. If you're not backfilling data, you can leave this field empty and we'll use the current time or server time.
  • traitsobject
    Additional information about the group.
    • objectTypeIdstring
      If you use Customer.io Journeys as a destination, this value is the type of group/object your group belongs to; object type IDs are stringified integers. If you don't include this value, we assume the object type ID is 1. See objects in Customer.io Journeys for more information.
    • relationshipAttributesobject
      Attributes that you want to set for the relationship between the person and the group.
    • Group Traits *any type
  • userIdstring
    The unique identifier for a person. This value should be unique across systems, so you recognize the same person in your sources _and_ destinations.

Relationship attributes

In Customer.io, you can assign attributes to both the group (called a custom object in Customer.io) and to the relationship between the object and the person. By default, attributes are stored on the custom object itself, but you can assign relationship attributes using the relationshipAttributes JSON object.

Build the object with OBJECT and serialize it with JSON_SERIALIZE:

SQL
SELECT companyId AS "groupId", objectTypeId AS "objectTypeId", companyname, employees, personId AS "userId",
    JSON_SERIALIZE(
        OBJECT(
            'is_manager', is_manager,
            'role', role,
            'start_date', start_date,
            'department', department
        )
    ) AS "relationshipAttributes"
FROM companies
WHERE last_updated >= {{last_sync_time}}

Build the object with named_struct:

SQL
SELECT company_id AS groupId, object_type_id AS objectTypeId,
    company_name, employees,
    user_id AS userId,
    named_struct(
        'is_manager', is_manager,
        'role', role,
        'start_date', start_date
    ) AS relationshipAttributes
FROM companies
WHERE updated_at >= timestamp_seconds({{last_sync_time}})

Build the object with JSON_OBJECT, alternating keys and values in order:

SQL
SELECT companyId AS groupId, objectTypeId, companyname, employees, personId AS userId,
    JSON_OBJECT(
        'is_manager', is_manager,
        'role', role,
        'start_date', start_date,
        'department', department
    ) AS relationshipAttributes
FROM companies
WHERE last_updated >= {{last_sync_time}}

Build the object with a FOR JSON PATH subquery:

SQL
SELECT companyId AS groupId, objectTypeId, companyname, employees, personId AS userId,
    (SELECT is_manager, role, start_date FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS relationshipAttributes
FROM companies
WHERE last_updated >= {{last_sync_time}}

Build the object with JSON_OBJECT:

SQL
SELECT companyId AS groupId, objectTypeId, companyname, employees, personId AS userId,
    JSON_OBJECT(
        'is_manager', is_manager,
        'role', role,
        'start_date', start_date,
        'department', department
    ) AS relationshipAttributes
FROM companies
WHERE last_updated >= {{last_sync_time}}

Build the object with JSON_BUILD_OBJECT:

SQL
SELECT companyId AS groupId, objectTypeId, companyname, employees, personId AS userId,
    JSON_BUILD_OBJECT(
        'is_manager', is_manager,
        'role', role,
        'start_date', start_date,
        'department', department
    ) AS relationshipAttributes
FROM companies
WHERE last_updated >= {{last_sync_time}}

Build the object with OBJECT_CONSTRUCT:

SQL
SELECT COMPANYID AS "groupId", OBJECTTYPEID AS "objectTypeId",
    COMPANYNAME AS "companyname", EMPLOYEES AS "employees",
    PERSONID AS "userId",
    OBJECT_CONSTRUCT(
        'is_manager', IS_MANAGER,
        'role', ROLE,
        'start_date', START_DATE
    ) AS "relationshipAttributes"
FROM companies
WHERE LAST_UPDATED >= {{last_sync_time}}

Page

The Page method records page views on your website, along with optional extra information about the page a person visited.

Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

SQL
SELECT id AS "userId", metatitle AS name, url, time_on_page
FROM pages
WHERE timestamp > {{last_sync_time}}
SQL
SELECT user_id AS userId, meta_title AS name, url, time_on_page
FROM pages
WHERE created_at >= timestamp_seconds({{last_sync_time}})
SQL
SELECT id AS userId, metatitle as name, url, time_on_page
FROM pages
WHERE timestamp > {{last_sync_time}}
SQL
SELECT id AS userId, metatitle as name, url, time_on_page
FROM pages
WHERE timestamp > {{last_sync_time}}
SQL
SELECT id AS userId, metatitle as name, url, time_on_page
FROM pages
WHERE timestamp > {{last_sync_time}}
SQL
SELECT id AS userId, metatitle as name, url, time_on_page
FROM pages
WHERE timestamp > {{last_sync_time}}
SQL
SELECT ID AS "userId", METATITLE AS "name", URL AS "url", TIME_ON_PAGE AS "time_on_page"
FROM pages
WHERE TIMESTAMP > {{last_sync_time}}
One of:
  • userIdstringrequired
    The unique identifier for a person. This value should be unique across systems, so you recognize the same person in your sources _and_ destinations.
  • contextobject
    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.
    • activeboolean
      Whether a user is active.

      This is usually used when you send an .identify() call to update the traits independently of when you've “last seen” a user.

    • Contains information about the campaign that resulted in the API call, gathered from, or mapping to, UTM parameters (e.g. utm_source).
    • channelstring
      The channel the event originated from.
      Accepted values: browser, server, mobile
    • ipstring
      The user's IP address. This isn't captured by our libraries, but by our servers when we receive client-side events (like from our JavaScript source).
    • localestring
      The locale string for the current user, e.g. en-US.
    • Contains information about the current page in the browser. This is automatically collected by our JavaScript source.
    • userAgentstring
      The user agent of the device making the request
  • integrationsobject
    Contains a list of booleans indicating the integrations that are enabled (true) or disabled (false). By default, all integrations are enabled (returning an empty object). Set "All": false to reverse this behavior.
    • Enabled/Disabled integrations *boolean
  • messageIdstring
    A unique identifier for a Pipelines call, ensuring that each individual event is unique. This is set by Customer.io
  • namestring
    The name of the page.
  • originalTimestampstring(date-time)
    In general, you can use timestamp rather than this field if you want to back-date events. This is the timestamp on the client device you invoke a call or the timestamp value you manually passed in a server-side library call.
  • propertiesobject
    Additional page properties. Analytics.js automatically collects url, title, referrer, path, and search properties. But, if you use our other sources or you write your own integration, you should consider sending these properties yourself. Destination actions that take page events often rely on the url and title properties.
    • categorystring
      The category of the page. This might be useful if you have a single page routes or have a flattened URL structure.
    • pathstring
      The path of the page. This defaults to location.pathname, but can be overridden.
    • referrerstring
      The referrer of the page, if applicable. This defaults to document.referrer, but can be overridden.
    • searchstring
      The search query in the URL, if present. This defaults to location.search, but can be overridden.
    • titlestring
      The title of the page. This defaults to document.title, but can be overridden.
    • urlstring
      The URL of the page. This defaults to a canonical url if available, and falls back to document.location.href.
    • Page Properties *any type
  • sentAtstring(date-time)
    The ISO-8601 timestamp when a library sends an event to Pipelines.
  • timestampstring(date-time)
    The ISO-8601 timestamp when the event originally took place. This is mostly useful when you backfill past events. If you're not backfilling data, you can leave this field empty and we'll use the current time or server time.

Screen

The Screen method sends screen view events for mobile devices. These help you understand the screens that people use in your app.

Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

SQL
SELECT id AS "userId", screen_name AS name, session_started
FROM screens
WHERE timestamp > {{last_sync_time}}
SQL
SELECT user_id AS userId, screen_name AS name, session_started
FROM screens
WHERE created_at >= timestamp_seconds({{last_sync_time}})
SQL
SELECT id AS userId, screen_name as name, session_started
FROM screens
WHERE timestamp > {{last_sync_time}}
SQL
SELECT id AS userId, screen_name as name, session_started
FROM screens
WHERE timestamp > {{last_sync_time}}
SQL
SELECT id AS userId, screen_name as name, session_started
FROM screens
WHERE timestamp > {{last_sync_time}}
SQL
SELECT id AS userId, screen_name as name, session_started
FROM screens
WHERE timestamp > {{last_sync_time}}
SQL
SELECT ID AS "userId", SCREEN_NAME AS "name", SESSION_STARTED AS "session_started"
FROM screens
WHERE TIMESTAMP > {{last_sync_time}}
One of:
  • userIdstringrequired
    The unique identifier for a person. This value should be unique across systems, so you recognize the same person in your sources _and_ destinations.
  • contextobject
    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.
    • activeboolean
      Whether a user is active.

      This is usually used when you send an .identify() call to update the traits independently of when you've “last seen” a user.

    • Contains information about the mobile app the event originated from, automatically collected by our mobile libraries when possible.
    • channelstring
      The channel the event originated from.
      Accepted values: browser, server, mobile
    • Contains information about the device the event originated from.
    • ipstring
      The user's IP address. This isn't captured by our libraries, but by our servers when we receive client-side events (like from our JavaScript source).
    • localestring
      The locale string for the current user, e.g. en-US.
    • Information about the current network connection, containing bluetooth, carrier, cellular, and wifi. If the context.network.cellular and context.network.wifi fields are empty, then the user is offline.
    • Dictionary of information about the operating system, containing name and version.
    • userAgentstring
      The user agent of the device making the request
  • integrationsobject
    Contains a list of booleans indicating the integrations that are enabled (true) or disabled (false). By default, all integrations are enabled (returning an empty object). Set "All": false to reverse this behavior.
    • Enabled/Disabled integrations *boolean
  • messageIdstring
    A unique identifier for a Pipelines call, ensuring that each individual event is unique. This is set by Customer.io
  • namestring
    The name of the screen the person visited.
  • originalTimestampstring(date-time)
    In general, you can use timestamp rather than this field if you want to back-date events. This is the timestamp on the client device you invoke a call or the timestamp value you manually passed in a server-side library call.
  • propertiesobject
    Additional properties for your screen.
    • Screen Properties *any type
  • sentAtstring(date-time)
    The ISO-8601 timestamp when a library sends an event to Pipelines.
  • timestampstring(date-time)
    The ISO-8601 timestamp when the event originally took place. This is mostly useful when you backfill past events. If you're not backfilling data, you can leave this field empty and we'll use the current time or server time.

Alias

The Alias method combines two previously unassociated user identities. Some integrations automatically reconcile people with different identifiers based on whether you send anonymousId, userId, or another trait that the integration expects to be unique. But for integrations that don’t, you may need to send alias requests to do this.

In general, you won’t need to use the alias call; we try to handle user identification gracefully so you don’t need to merge people. But you may need to send alias calls to manage user identities in some data-out integrations.

For example, in Mixpanel it’s used to associate an anonymous user with an identified user once they sign up.

SQL
SELECT id AS "userId", old_id AS "previousId"
FROM user_resolution
WHERE timestamp >= {{last_sync_time}}
SQL
SELECT user_id AS userId, old_id AS previousId
FROM user_resolution
WHERE updated_at >= timestamp_seconds({{last_sync_time}})
SQL
SELECT id AS userId, old_id as previousId
FROM user_resolution
WHERE timestamp >= {{last_sync_time}}
SQL
SELECT id AS userId, old_id as previousId
FROM user_resolution
WHERE timestamp >= {{last_sync_time}}
SQL
SELECT id AS userId, old_id as previousId
FROM user_resolution
WHERE timestamp >= {{last_sync_time}}
SQL
SELECT id AS userId, old_id as previousId
FROM user_resolution
WHERE timestamp >= {{last_sync_time}}
SQL
SELECT ID AS "userId", OLD_ID AS "previousId"
FROM user_resolution
WHERE TIMESTAMP >= {{last_sync_time}}
  • previousIdstringrequired
    The anonymousId or userId value that you want to merge into the canonical profile.
  • userIdstringrequired
    The userId that you want to keep. This is required if you haven't already identified someone with one of our web or server-side libraries.
Updated November 20, 2026