SQL Queries enable advanced audience segmentation by analyzing complex data relationships that simple list selection cannot achieve. You can segment contacts based on aggregated behaviors like average purchase amounts, first interactions, or most recent activities, which helps create more targeted email campaigns that drive better engagement than basic demographic segmentation alone.
This advanced segmentation capability enables you to deliver highly relevant messaging based on behavioral patterns and calculated metrics, helping improve campaign performance through more precise audience targeting and personalized content delivery.
They are also used for moving data from one relational table to another. A good example is when you want to select contacts based on an aggregation of data types, particularly with numerical values.
Usage of SQL Queries
Suppose you are storing your customers' purchase history data in relational tables to augment the data stored in the Product/Revenue database. You'll notice that when using tables in segmentation, the selection is based on individual records. But what happens if you want to segment your contacts based on their average purchase amount?
This scenario is an example of data aggregation that you would use SQL queries for. In this case, you'd write an SQL statement that pulls data from relational tables, calculates the average purchase amount per contact, and then inserts the data into another relational table.
Another good use for SQL queries is when selecting contacts based on date/time. Suppose you are using relational tables to store data each time a contact applies for a job on your site. Using tables in segmentation will enable you to select contacts who applied for a job on any given date range. But what about when you want to select your contacts based on the very first job application? Or based on the most recent job application?
Again, that's where SQL queries come into play. You'd simply write an SQL statement that selects contacts based on their very first job application and then insert it into another relational table.
Managing SQL Queries
In Maropost, you can create and manage your SQL queries from its index page. You can access this feature via the navigation panel under CDP > SQL Queries.
The SQL Queries index page displays information such as a list of SQL queries created beforehand, their records, creation and modification details, etc. Currently, the options present under the Actions drop-down menu are as follows:
- Execute Query: It enables you to run an SQL query script. While the script is being processed, it pulls data from one relational table and stores it into another relational table as per the script's command.
- Export Query: It allows you to export the data as a .csv file.
- Edit Query: It lets you make changes to an existing SQL query.
- Delete Query: It removes the SQL query permanently from your system.
Creating a SQL Query
To create a new SQL query, follow these steps:
- On the SQL Queries index page, click on the New Query button.
- On the New Query pop-up, enter your query details and click on Create to generate a new SQL query:
Name: A name for the SQL Query.
Targets: The names of the relational tables that will store the data resulting from the SQL statement.
Update Type: You can choose whether an update should be an "Overwrite" (overwrites the existing data present in the target table) or an "Append" (appends to the existing data present in the target table).
Query: The SQL statement that pulls the data out of your relational tables.
Executing a SQL Query
There are two ways to execute an SQL query:
- You can select the Execute Query option from the Actions drop-down menu on the SQL Query index page.
- You can use an SQL Query "Action" widget in Data Journeys.