Getting the right data for your personalized emails, push notifications, livechat, or website content is oftentimes the biggest pain-point when running a new campaign. This ranges from the standard traits about a user like a first name, email, or role, to more complex data based on behavior, like what the last 5 products that a user viewed, or what the billing status of the subscription is. This data is often sitting in a data warehouse like Redshift, Postgres, or BigQuery, disconnected from your marketing tools. In this lesson we'll show you how you can use the SQL Traits feature from Personas to solve that data problem for your marketing campaigns.
Anatomy of a personalized email
Let's reverse engineer a personalized email to see where the data comes from, and how to import that data with SQL traits. To do this I'll use an example of an email I sent with Customer.io to announce SQL traits to a subset of existing customers. Very meta, I know. Here's the end product:
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.
Grow using data