Using A Sql Data Source To Create Your Audience

What is SQL?

SQL (Structured Query Language) is a language used to create, update and read data from a database. In the context of Vero Connect, SQL allows you to specify which endpoints (recipients) in your database that you want to include in your message's audience, and what data related to that endpoint you would like to access in order to personalize the content of your message.

The Structure of a Query

While SQL is a powerful query language with a complex syntax, at it's heart there are three basic components of a query you would use in Vero Connect:

The SELECT statement: which properties in the data queried you want to have access to.
The FROM statement: which table you want to pull this data from.
The WHERE statement: narrows down which specific endpoints (recipients) you want the database to return.

Let's look at a simple example:

SELECT
  addressable_email AS email,
  first_name,
  last_name,
  language
FROM my_users_table
WHERE subscribed = TRUE

SELECT

What the SELECT statement says is that a row in my_users_table may have multiple columns but the columns we are interested in are the addressable_email, first_name, last_name and language columns. It's always better to only pull down the data we need, which is why we carefully specify just those columns we'll use to tell Vero where to send your message and the properties needed to personalize that content.

You may have noticed the line addressable_email AS email. The AS keyword in SQL allows us to label the column something differently in the result that is returned. In this case, Vero is expecting the query results to include an email column. As this column is called addressable_email in my_users_table, we ensure the results return that column as email so Vero knows that it can use that information as an endpoint to send the message to.

FROM

The FROM keyword is used to tell the database which table we are querying. In our query, we'll be looking at the table my_users_table. Depending on the source of your data, the FROM statement may be more complex than this.

WHERE

The WHERE statement is the part of the query that allows you to specify exactly which rows of data the database should return. This will usually involve a comparison against one or more of the columns in the database to some value or range of values.

In our specific case, we are asking the database to return only those users who meet the condition of having the subscribed column equal to TRUE. Let's look at a few other common types of WHERE statements below.

SELECT
  email
FROM my_users_table
WHERE created_at >= '2023-01-01 00:00:00'

In this case, we are telling the database to return users whose record was created sometime on or after January 1, 2023.

SELECT
  email
FROM my_users_table
WHERE account_id IN (1,2,3,4)

In this example, we are asking the database to return the users who have an account_id with any of the values 1,2,3, or 4. Imagine this scenario: users in our database can belong to many different organizations. We want to specifically contact users belonging to four of those organizations. So in our query, we can specify only those four account_ids using the IN keyword. When using IN, always be sure to include the values you are querying for in parentheses.

SELECT
  email
FROM my_users_table
WHERE email LIKE '%@example.com'

Let's say we are looking for any users with an email address from the example.com domain. The LIKE keyword let's us do just that. We provide the LIKE keyword with a basic pattern. In this case, that pattern is %@example.com. The % is called a wildcard operator. What this is saying is that we want all records where the email column matches the pattern of any character or set of characters followed by @example.com.

What if we wanted to match emails with from both example.com and example.net? We could include another wildcard:

WHERE email LIKE '%@example%'

But be careful. While this would return results that included both @example.com and @example.net. It would also match with many email addresses we didn't intend, like example-rama.com or examplecalifragilisticexpialidocious.gov. Instead, we can use the OR keyword to specify just these two domains.

WHERE (email LIKE '%@example.com' OR email LIKE '%@example.net')

What if we want to check multiple properties? That's easy. We use the AND keyword.

SELECT
  email
FROM my_users_table
WHERE subscribed = TRUE
AND favorite_color = 'red'

In this case, the database will only return those users who have a value of subscribed equal to true and also have a favorite_color with the value equal to red.

Using Your Data in Your Content

Let's return to our original example:

SELECT
  addressable_email AS email,
  first_name,
  last_name,
  language
FROM my_users_table
WHERE subscribed = TRUE

We've pulled down the email, first_name, last_name and language fields, so how do we use them?

In our content, we can access those as user properties using the Liquid templating language.

Let's say we want to look at the language and use a different greeting in our email depending on what that language is. In the greeting (in our email's content), we'll use the user's first_name property to personalize the content.

That could look something like the following:

{%if user.language == ‘en-US’ %}
  Hello {{user.first_name}}
{% elsif user.language == ‘ja-JP’%}
  こんにちは {{user.first_name}}
{% else %}
  Guten Tag {{user.first_name}}
{% endif %}

In Liquid, these properties will be accessed from the user object and the property names will match the fields from our SELECT statement in our SQL query.

We're using Liquid's IF statement to check the value of the language property, and then personalizing the content based on that result. Further, we access the user's first_name using {{ user.first_name }} and print that in the email.

Further Recommendations

SQL is very powerful, and there's much more you can do with it to really take advantage of your data and create highly selective audiences for your emails, or access a wide array of data available to you about your customers for use in the content of your emails.

To learn more, there are a number of courses available online. These are a just few that we recommend:

Beyond that, each type of database Vero supports (PostGres, RedShift, Snowflake, etc) have their own unique flavor of SQL, so be sure to review the documentation for your particular database.

Using a SQL Data Source to create your audience