loading customer data

Use SQL to define a newsletter audience

In Vero Newsletters you can connect to your external database or data warehouse, write and run SQL queries against them, and then use the results of the query to generate an audience (list of users) to send your newsletter to.

Make sure you have connected your first data source before continuing with the steps in this help doc by reading Connecting to an External Database first.

This feature is recommended for users who are familiar with writing SQL or PostgreSQL queries.

First: Adding a data source

Before you can write SQL you must first connect a supported database or data warehouse to Vero. You can have multiple database connections, each connection is referred to as a Data Source, which can then be selected when you are defining the audience for a newsletter.

Read more about Connecting to an External Database before going any further.

Selecting an SQL data source

Once you have added a data source to Vero you can now select the Query External Database option from the audience panel of your newsletter.

select-query-database

After choosing the Query External Database option the SQL editor will open. If you have multiple data sources the SQL editor will open with the data source you last used selected. To change data sources, select the dropdown and choose a different source. Only the data sources that support SQL and PostgreSQL will be shown in this menu.

choose-data-source

Writing and running an SQL query

Inside the query editor, you can write standard SQL and PosgreSQL just like in any other SQL tool. Vero’s query editor is designed to be familiar to any other standard SQL editor, with syntax highlighting and a straightforward layout with code editor, table schema viewer and results table below.

query-editor

Vero supports standard SQL and POSTSQL statements and does very little transformating to your code before running it against your database, so most valid SQL will run. The only limitation to the Vero’s query editor is there to encourage safe and secure use of your databases and important user data. As such, only SELECT statements are supported.

To make it harder to make unintended data transformations, write statements such as DROP, CREATE or UPDATE are not supported.

Running a query and viewing results

Once you’ve composed your SQL and are ready to query the database to view the results, simply hit the Run Query button.

run-query

Your code will be run on the selected database in real time and if the query is successful, the results will be shown in the table below. The results table is where you can preview your data before selecting it as the audience of your newsletter. The results are shown in pages of 100 rows.

results-panel

The data shown in the results panel is always representative of the columns selected in your query, for example:

```SELECT * FROM users_data```

This would return and show all columns in the results view. This may take a long time for the query to run and show results depending on the size of your database.

SELECT email, first_name, last_name FROM users_data

This would return 3 columns and display in the order shown in the query.

Importing query results into the audience of a newsletter

After running a valid query, and once you are happy with the results of the query, you can then select Import Audience. This will copy the data from the results of your query and import it as the list of recipients to send the newsletter to. Data imported to the newsletter can also be used to personalize the content of the email.

Note: If you are using the LIMIT statement in your query while experimenting with your results, make sure that you remove this statement before selecting ‘Import Audience’ as only the results returned in the query will be imported.

import-audience

After selecting ‘Import Audience’ the data from your query will be validated and imported to the newsletter. If you are working with large volumes of data, you may edit the rest of the newsletter while you’re waiting for the data to be processed.

importing

Once the data has finished imported and is visible in the audience panel you’re good to go, this is the list of users your newsletter will be sent to.

audience-panel

To update or change the audience data, open the query editor by hitting the Edit Query button. After you have edited and run your query you must select Import Audience again to re-import the data in your audience.

Validation Errors

When importing a large data set, some of your data may be incomplete. Vero checks your data for a valid email column. If no email is found on a row, or if the email is not valid, the row will be rejected and will not be included in the final audience.

rejected-rows

Next steps

Before sending your newsletter, add the content of your email using our drag and drop or HTML editor. Learn more about adding content to your newsletter

FAQs

Can I use the audience data in the email content?

Yes, every column imported to the audience is available to use in the content of your email using the `` liquid syntax. Learn more about using liquid in Vero

Didn't find what you were looking for?

Get in touch, we're always happy to help. Email us at support@getvero.com.