Personalize your campaigns with your data warehouse

The idea of this campaign was to target existing customers that already have a data warehouse connected to Segment, and are already pulling in data from a cloud source into the warehouse. With SQL traits, they could now send that data, for e.g. Zendesk tickets data, to an email destination. You may want to exclude customers that have an open support ticket from marketing campaigns. To personalize this email, I needed to pull the following fields to personalize the content:

If you look at the template for this email, you'll see where these personalized fields get injected into the email content. Customer.io uses a powerful templating language called Liquid, developed initially at Shopify, that can help you with inserting dynamic variables, conditional branching etc.

Getting the data with a SQL trait

Now that we've seen the end-result, it's time to pull the underlying data with a SQL trait. This feature gives you the ability to specify a query in the Personas UI which will run over your data warehouse, and then feed the results back into Segment and your downstream Destinations.

The audience I was looking for roughly consisted of:

• Existing Segment customers

• Account Has a Cloud Source enabled AND a Redshift/Postgres warehouse

• Account/Users: Is one of the top 3 most active users in their account

After selecting one of your connected data warehouses, you can write a query and preview the results. The query needs to return a list of users with a user_id field, and the contextual fields mentioned earlier that will be used in the body of the email.

The next step is to select where you want to send the traits. Under the hood SQL traits will send an identify call or a group call, depending on whether you're importing a list of users or accounts, to our existing Destinations.

The last step is to give the SQL traits query a name and description. Once you create the SQL trait, we will run the query twice a day (configurable schedule for advanced plan), and start importing users. Note that Personas will only send identify calls for users where a trait value has changed. This is to prevent you from sending a large volume of users each time, and only send new information about users whose information has changed.

You can verify that data is being sent to properly by consulting the debugger in Segment (https://app.segment.com/your_workspace_slug/personas/sources/personas_default/debugger), and then searching for that user in Customer.io or your email tool of choice.

Crafting the personalized message

Finally, you'll want to draft the campaign in Customer.io. The fields that you just imported with the SQL traits can be accessed within the templating language in the format {{ customer.<trait_name> }}, for e.g. {{ customer.cloud_source_for_sql_trats }}

Here's the underlying liquid template code I used that leverages the imported SQL traits as well as some of the Liquid features like variables, if/else statements, and helper functions, to personalize the content of the email.

Once you've finished the content, you should always carefully QA and send test emails to ensure that the fields are rendering properly.

Final step is to launch, and witness your improved engagement rates thanks to your personalized approach. You can even leverage the Customer.io source events to build your own campaign performance dashboards.