- As its name implies, “SQL Query” will execute any custom SQL statements that the marketer has written in order to achieve the desired results.
- In order to understand the significance and functionality of a "SQL Query", let us understand the working of segmentation when it comes to using segment rules for relational tables:
- Consider the following data in a table called museum_visits:
Let us suppose you want to select contacts who visited the museum in "October" with the access code “nightlife”.
Using segment rules, we ASSUME that creating this segment will achieve the desired result:
[table] [museum_visits] WHERE [visit_month] [is equal] “Oct”
[table] [museum_visits] WHERE [access_code] [is equal] “nightlife”
However, this is not how segmentation works, since each segment rule is its own result set of contacts.
- The first segment rule will pull “firstname.lastname@example.org” and “email@example.com” since both contacts visited the Museum in October.
- The second segment rule will pull “firstname.lastname@example.org” and “email@example.com” since both contacts have the access_code = “nightlife”.
- The “AND” condition between the two segment rules compares the contacts within both result sets and pulls those contacts that are in both. Hence, the result of the segment is 2 contacts: firstname.lastname@example.org and email@example.com. (However, the result should have been only "firstname.lastname@example.org")
The solution to this limitation is the new segment rule called "SQL Query".
- The marketer creates a SQL Query (Email >> Contacts >> SQL Queries) called “October nightlife visitors”
- The syntax of the query will be something like
visit_month = ‘Oct’
access_code = ‘nightlife’
- The marketer creates his segment using the new segment rule: [SQL Query] >> [October nightlife visitors]
- The result of this segment would be accurate and as desired.
Key usage points:
- The new segment rule simply executes at runtime a SQL Query, that the marketer has created ahead of time.
- By definition therefore, the new segment rule *only* executes on relational tables.
- The way SQL Queries work is that the SQL statement executes and then stores the resulting data in a target table (also a “relational table”).
- Unlike a SQL Query, a segment will only execute the SQL statement; it will *not* store the resulting data in the target table. It will only select the contact for segmentation.