SQL Template Examples

 Customer Profile Schema Show Schema Hide Schema

Customer Events Schema Show Schema Hide Schema

How to use templates

When creating an sql template we can use 1 dynamic value so called placeholder which user is able to change. We have 2 types of placeholders:

• Condition - allows user to change condition and value for given placeholder
• Value - user can change only the value while the condition is predefined in the template

You should be using conditions when given templates can be used in different scenarios where values can be compared differently. For example recency, could be used when its equal for example 1 - first time buyers, or when > 1 when segmenting all repeat buyers.

Using value placeholder requires setting condition in the template for example days = {X}, so template user can change only the days value. See below for all available placeholders and examples.

Condition Placeholders

• {condition:number} - any number column condition, like recency = 10 or recency > 10, where = and 10 are defined by the template user. 
• {condition:datetime_interval} - for comparing timestamp columns with interval starting from now; for example ts_event > current_timestamp - interval 2 hours; here "2 hours" is selected by the user
• {condition:date} - any date column condition, like date(ts_event) = '2020-01-01' or date(ts_event) > '2020-01-01', where = and '2020-01-01' are select by the template user. 
• {condition:date_range} - comparing a date value in a range, for example date(ts_event) between '2020-01-01' and '2021-01-01'; template user has to select exact from and to dates in this case.
• {condition:text} - any text column condition, where we can have exact matches event = 'purchase' and also partial matches like url like '%birthday%'
• {condition:number[]} - when user can define more than 1 number value for exact matches, for example recency IN (1,2,3) - user can enter 1,2,3 values
• {condition:text[]} - similar to number[] but for multiple text exact 

Value Placeholders

• {value:number} - when condition should be fixed, for example event = 'purchase' - user can only specify event value in this case but not the condition.
• {value:date} - allows to specify date value only
• {value:text} - allows to specify text value only

SQL Template Examples

Visited page reminder template

event IN ('page_view', 'page_landing') AND url_path = {value:text} AND date(ts_event) = current_date - interval 370 days
AND event_type = 'purchase'