Usage of SQL Queries
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.
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 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 in to 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.
Creating a SQL Query
To create a new table, please follow these steps:
- Hover your cursor over ‘Email’. Now, hover your cursor over ‘Contacts’
- Click ‘New SQL Queries’ on the bottom-right. This will open a new SQL Query Request
- Enter the name of the SQL query, the SQL statement that pulls the data out of your relational tables, and the name(s) of the relational table(s) that will store the data resulting from the SQL statement.
- Select the option whether you want to replace the data in the target table(s) by overwriting the data, or if you just want to append the data to the table(s).
- Click [Save] to save your edits.
Executing a SQL Query
There are two ways to execute a SQL Query. You can either execute it by selecting the “execute” link in the SQL Query index page, or by using a SQL Query action widget in Data Workflows.