Beginning August 18, 2023, new Unify Plus users can access SQL Traits in Unify.
Use SQL Traits to import user or account traits from your data warehouse back into Unify or Engage to build audiences or to enhance data that you send to other Destinations.
SQL Traits are only limited by the data in your warehouse. Because anything you can write a query for can become a SQL Trait, you can add detail to your user and account profiles, resulting in more nuanced personalization.
This unlocks some interesting possibilities to help you meet your business goals.
- To improve your support team’s customer satisfaction score (CSAT), create a SQL Trait of the most common ticket requests for a customer’s industry by joining data from cloud sources like Zendesk and Salesforce. The resulting SQL Trait helps you anticipate the user’s problems and accelerate potential solutions.
- To determine if a user resides in a specific area, query address data in your warehouse and send it as a
falseTrait to an Engage audience.
- To fill gaps in your customer profiles to include information before you implemented Segment, import historical Traits from your warehouse.
- To predict a customer’s lifetime value (LTV), generate a complex query based on demographic and customer data in your warehouse. You can then use that information in an Engage audience to send personalized offers or recommend specific products.
- To inform your outreach efforts, use complex queries to build churn or product adoption models.
Check out Segment’s SQL Traits blog post for more customer case studies.
To view SQL Traits in a user profile, you must have PII access. Without this access, Segment redacts all SQL traits in a profile.
Note that after you bring in data with SQL Traits, changing data types for fields may not be compatible with all destinations.
Example: cloud sources sync
SQL Traits allow you to import data from object cloud sources like Salesforce, Stripe, Zendesk, Hubspot, Marketo, Intercom, and more. For example, bring in Salesforce Leads or Accounts, Zendesk ticket behavior, or Stripe LTV calculations.
The two examples below show SQL queries you can use to retrieve cloud-source information from your warehouse.
Salesforce lead import
If you want to import data from the Salesforce leads and contacts table, you can use SQL similar to the following query:
select external_id_c as user_id,
-- …more properties
Has Open Ticket in Zendesk
This query computes whether a user has an open ticket:
select distinct u.external_id as user_id, true as has_open_ticket
from zendesk.tickets t
join zendesk.users u
on u.id = t.requester_id
where t.status in ('pending','open','hold','new')
Comparing trait types
View the table below to better understand how Segment collects custom, computed, and SQL traits.
You can use the Profile explorer (Unify > Profile explorer) to view traits attached to a profile.
|Traits created from source events you pass into Segment. From your sources, send custom traits as pieces of information that you know about a user in an Identify call.
|Traits collected from computations off of event and event property data from your sources. Create user or account-level calculations like
total_num_orders for a customer. Learn more by viewing types of computed traits.
|Traits created by running SQL queries on data in your warehouse. SQL traits are a type of computed trait. SQL traits help you import traits from your data warehouse back into Segment to build audiences or enhance data that you send to other destinations.
Configure SQL Traits
To use SQL Traits, you need the following:
- a warehouse connected to Segment
- a Segment workspace
- a user account with access to Unify in that workspace
Step 1. Set up a warehouse source
Segment supports Redshift, Postgres, Snowflake, Azure SQL, and BigQuery as data warehouse sources for SQL Traits. Note that the BigQuery setup process requires a service user.
Safeguard your data
For any warehouse, Segment recommends that you create a separate read-only user for building SQL Traits.
Redshift, Postgres, Snowflake, Azure SQL setup
If you don’t already have a data warehouse, use one of the following guides to get started:
- Redshift Getting Started
- Postgres Getting Started
- Snowflake Getting Started
- Azure SQL Getting Started
To connect BigQuery to Segment SQL Traits, follow these instructions to create a service account for Segment to use:
Navigate to the Google Developers Console.
Click the drop down to the left of the search bar and select the project that you want to connect.
Note: If you don’t see the project you want in the menu, click the account switcher in the upper right corner, and verify that you’re logged in to the right Google account for the project.
Click the menu in the upper left and select IAM & Admin, then Service accounts.
Click Create Service Account.
Give the service account a name like
Under Project Role, add only the
BigQuery Data Viewerand
BigQuery Job Userroles.
IMPORTANT: Do not add any other roles to the service account. Adding other roles can prevent Segment from connecting to the account.
Click Create Key.
JSONand click Create.
A file with the key is saved to your computer. Save this; you’ll need it to set up the warehouse source in the next step.
You’re now ready to create a new BigQuery warehouse source, upload the JSON key you just downloaded, and complete the BigQuery setup.
Step 2. Add the warehouse as a Source
Once your warehouse is up and running, follow these steps:
Navigate to the Engage settings (Engage > Engage Settings > Warehouse Sources), and click Add Warehouse Source.
Select the type of warehouse you’re connecting.
In the next screen, provide the connection credentials, and click Save.
If you’re connecting a BigQuery warehouse, use the JSON key file that you downloaded as the last step.
Create a SQL Trait
Before you create a SQL Trait, you must first preview it to validate your query. If you’re new to SQL, try out one of the templates Segment offers.
Preview the SQL Trait
From the Audiences viewer, go to the Computed Traits tab, and click New Computed Trait. Next, choose SQL, and click Configure. Select the data warehouse that contains the data you want to query.
If you’re sending data from object cloud sources to your warehouse, the SQL Traits UI has some pre-made templates you can try out.
When you’re building your query, keep the following requirements in mind for the data your query returns.
- The query must return a column with a
group_idfor account traits, if you have Engage for B2B enabled). The query cannot include values for both
- The query must return at least one trait in addition to
group_id, and no more than 25 total columns.
- The query must not return any
group_ids with a
- The query must not return any records with duplicate
- The query must not return more than 25 million rows.
- Each record must be less than 16KB in size to adhere to Segment’s maximum request size.
A successful preview returns a sample of users and their traits.
If Segment recognizes a user already in Engage, it displays a green checkmark on their profile. Clicking the checkmark displays the user’s profile. If a user has a question mark, Segment hasn’t detected this
user_id in Engage before.
Configure SQL Trait options
Once you’re ready to import the SQL Trait, select the Destinations to which you want to send the data. If you prefer to build Engage audiences directly from the data instead of sending it to a Destination, click Skip.
Give your SQL Trait a descriptive name. If you’re importing multiple Traits, use a name like “Zendesk Traits”. The Trait names you use in audience-building or in your downstream tools correspond to the column names from the query.
If you’re building Engage audiences from this data, select “Compute without enabled destinations”.
Click Create Computed Trait to save the Trait.
Check Compute without destinations if you only want to send to Engage.
When you create a SQL Trait, Segment runs the query on the warehouse twice a day by default. You can customize the time at which Segment queries the data warehouse and the frequency, up to once per hour, from the SQL Trait’s settings. (If you’re interested in a more frequent schedule, contact Segment Support.)
For each row (user or account) in the query result, Engage sends an identify or group call with all the columns that were returned as Traits. For example, if you write a query that returns
user_id, has_open_ticket, num_tickets_90_days, avg_zendesk_rating_90days Segment sends an identify call with the following payload:
Is there a limit to the result set that can be queried and imported?
Yes. The result set is capped at 25 million rows.
How often does Segment query the customer’s data warehouse?
For each SQL Trait you create, you can set a compute schedule to query the data warehouse up to once per hour. Your query may run at any given time during the hour you select.
What identifiers can I use to query a list?
You can query based on
anonymous_id. If Segment doesn’t locate a match based on the chosen identifier, it creates a new profile. See more below.
Can I use SQL Traits to create users in Segment? Or do SQL Traits only append Traits to existing users?
Yes. The Engage engine sends an identify call if there is no match between the identifier you chose and an existing record. When this happens, Segment creates a new user profile. This identify call takes place in the back-end and doesn’t show up in your Debugger.
Does Engage send identify/track/group calls on every run?
No. Engage only sends an identify/track/group call if the values in a row have changed from previous runs.
I have a large (1M+) query of users to import, should I be worried?
If you’re importing a large list of users and traits, you’ll need to consider your API call usage as well as volume among the partners receiving your data. These vary depending on Segment’s partners, contact support for more information.
Is there a limit on the size of a SQL Trait’s payload?
Yes, Segment limits request sizes to a maximum of 16KB. Records larger than this are discarded.
Do SQL Traits support arrays?
No, SQL Traits supports string and numeric data types. You can cast arrays as a comma-separated string. In this case, if you used this trait to build an audience, you could check if the array contains a certain value with the “contains” operator, but the value is sent to any connected destinations as a string.
I’m getting a permissions error.
You might encounter a
permission denied for schema error, like the following:
Segment usually displays this error because you’re querying a schema and table that the current user cannot access. To check the table privileges for a specific grantee (user), view the credentials of the stored warehouse user.
To grant access to a table, an admin usually needs to grant access to both a schema and table through the following similar commands:
GRANT USAGE ON SCHEMA ecommerce TO segment_user;
GRANT SELECT ON TABLE ecommerce.users TO segment_user;
Learn more about granting permissions using the following links:
I’m seeing a maximum columns error.
Segment supports returning only 25 columns. Contact Segment with a description of your use case if you need access to more than 25 columns.
I’m seeing a duplicate
Each query row must correspond to a unique user. Segment displays this error if it detects multiple rows with the same
user_id. Use a
group by statement to ensure that each row has a unique user_id.
I’m seeing some users/accounts in my preview with question marks. What does that mean?
Question marks in previews indicate one of two things:
1. Segment doesn’t recognize this
group_id in Engage.
In this case, for sources connected to Engage, Segment hasn’t received any event (for example, identify, track, or page) with this
user_id. This could still be a legitimate
user_id for a number of reasons, but before syncing, make sure you rule out option two (below), as sending a different identifier as the
user_id can corrupt your identity graph.
2. You have the wrong
You might be returning a value for
user_id that’s inconsistent with how you track
user_id elsewhere. Some customers want to return
user_id, or a partner’s tool ID as the
user_id. These conflict with Segment best practices and corrupt the identity graph if you then track
user_id differently elsewhere in your apps.
If you see only question marks in the preview, and have already tracked data historically with Segment, then you likely have the wrong column. If your cloud source doesn’t have the database
user_id, Segment recommends using a
JOIN clause with an internal users table before sending the results back to Segment.
Why do some SQL Trait settings not have the “Compute schedule” option?
Segment added the compute schedule feature on Feb 8, 2021, so traits created prior to this date will not have this option. If your trait lacks this feature, recreating it will make it available.
Why do the SQL traits value showing in preview is not reflecting over the profile even after a successful sync?
Why doesn’t the value of a SQL trait show in a user profile after a successful sync?
Check that you’ve configured the identifier that uniquely identifies users in a SQL query (
group_id for account traits) in Identity Resolution settings as an identifier. This ensures the trait is added to the user’s profile with the correct identifier. If you don’t configure the identifier in Identity Resolution settings, the trait’s value is not added to the user profile.
Why doesn’t the identifier updated by a SQL trait show the correct value found in the column?
Ensure that the name given to the SQL trait is not the same name as the identifier or column name from the query. To use SQL traits to update an identifier, the identifier will need to be a column in the query of your SQL trait. The column name in the query of the SQL trait should be the one that Identity Resolution uses to generate the identifier.
Are there any errors in the browser’s Network or Console tab?
If you experience issues saving the SQL Trait query or previewing the results of the SQL Trait query, open the browser’s Console and Network tabs to see if any errors occurred upon clicking the Save/Preview buttons. If you find any errors, please expand the error and take a screenshot of it. You can then share these details when creating a support ticket.
Why can’t I see error messages in SQL traits while other users can?
To see error messages in SQL traits, you will need to have PII Access.
This page was last modified: 07 Feb 2024
Questions? Problems? Need more info? Contact Segment Support for assistance!