Hero decoration
Hero decoration
Analytics Academy Grow using data: Lesson 5
Lesson logo

Personalize your campaigns with your data warehouse

Make your marketing campaigns even more powerful by importing data from your data warehouse using Personas SQL Traits.

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:

email1-screenshot.jpg

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.

liquid-screenshot-1.jpg

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.

sqltraitspreview_obfuscated.png

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.

image7.png

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.

image8.png

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.

debugger_obfuscated.png

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.

customerio_obfuscated.png

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.

Get every lesson Get every lesson

Get every lesson
delivered to your inbox

Enter your email below and we’ll send Analytics Academy lessons directly to you so you can learn at your own pace.
Thanks! You'll be hearing from us soon.

Get every lesson
delivered to your inbox

Enter your email below and we’ll send Analytics Academy lessons directly to you so you can learn at your own pace.
Thanks! You'll be hearing from us soon.
Get every lesson