SQL Queries are used for moving data out from relational tables and into other relational tables. A good example of this is when you want to select contacts based on any type of aggregation of data — 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 when using Tables in segmentation that selection is based on individual records. But what happens if you want to segment your contacts based on the average purchase amount? This scenario is an example of data aggregation that you would use SQL Queries for. In this case, you’d write a SQL statement that pulls data out of your 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 you want to select your 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 in 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 comes into place. You’d simply write a 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 on the SQL Queries index page. In Navigation, go to AUDIENCE, and select SQL Queries.
Creating a SQL Query
To create a new SQL Query, follow these steps:
- In Navigation, go to AUDIENCE, and select SQL Queries.
- On the SQL Queries index page, click New Query.
- On the New Relational Query page, enter your query details.
- Name – A name for the SQL Query.
- Query – The SQL statement that pulls the data out of your relational tables.
- Targets – The names of the relational tables that will store the data resulting from the SQL statement.
- Update Type – The type of update indicates whether the update is an Overwrite (overwrites the existing data present in the target table) or Append (appends to the existing data present in the target table).
Executing a SQL Query
There are two ways to execute a SQL Query: