In SALESmanago you can create dynamic segments using the text editor. However, you have to remember that the queries must be written in SQL language. There are some exemplary queries below.
IMPORTANT: If a segment was created in the text editor, there is no possibility to switch it to the wizard editor.
To open the text editor, go to
Segmentation → Dynamic Segments → Use text editor
Basic names of the SQL queries elements
SOURCES SEGMENT
Segment’s scheme: event[{conditions}]
- TIME – the time of the event in format ‘yyyy-MM-dd’ or ‘yyyy-MM-dd HH:mm:ss’
- TYPE – the type of the event. Currently supported:
- VR – a visit from referrer (SOURCE – referrer)
- VS – a visit from search
- VD – direct visit
- VA – a visit from Google Ads advert
- VU – visit from UTM (SOURCE – utm_source, SUB_SOURCE – utm_medium, SUB_SUB_SOURCE – utm_campaign)
- EMO – opened a mass email (SOURCE – conversation ID)
- ERO – opened an email from the rule (SOURCE – rule ID)
- EWO – opened an email from the workflow (SOURCE – workflow ID)
- EMC – clicked mass email (SOURCE – conversation ID)
- ERC – clicked an email from the rule (SOURCE – rule ID)
- EWC – clicked an email from the workflow (SOURCE – workflow ID)
- SOURCE, SUB_SOURCE, SUB_SUB_SOURCE – details of the event source, depending on its type
Exemplary queries:
- event[TIME >= ‚2018-01-01’ & TIME < ‚2019-01-01’ & TYPE = ‚EMC’] – people who clicked the mass email between dates
- event[TYPE = ‚VR’ & lower{SOURCE} ~ ‚.*facebook.*’] – people who visited the website from redirecting the website meeting the regular expression after reducing to the small letters.
- event[TYPE = ‚VU’ & SOURCE = ‚automails’ & SUB_SOURCE = ’email’ & SUB_SUB_SOURCE = ‚orderdispatch’] – people who visited website from the UTM phrase
TRANSACTION SEGMENT
Segment’s scheme: transaction[{conditions}]
- TIME – a time of the transaction in format ‘yyyy-MM-dd’ or ‘yyyy-MM-dd HH:mm:ss’
- TRANSACTION_VALUE – transaction value
- PRODUCT – product ID
- PRODUCT_PRICE – product price (during executing the query)
- PRODUCT_NAME – product name (during executing the query)
- PRODUCT_CATEGORY – the name of the product’s category (during executing the query)
- PRODUCT_BRAND – product brand (during executing the query)
- PRODUCT_MANUFACTURER – producer of the product (during executing the query)
- PRODUCT_COLOR – the colour of the product (during executing the query)
- PRODUCT_GENDER – gender of the product (during executing the query)
Exemplary query:
- transaction[lower{CATEGORY} ? ‚%leather%’ | lower{CATEGORY} ? ‚%shoe%’] – people who purchased the product from given categories
CART SEGMENT
Segment’s scheme: cart[{con}]
- TIME – a time of the transaction in format ‘yyyy-MM-dd’ or ‘yyyy-MM-dd HH:mm:ss’
- TRANSACTION_VALUE – transaction value
- PRODUCT – product ID
- PRODUCT_PRICE – product price (during executing the query)
- PRODUCT_NAME – product name (during executing the query)
- PRODUCT_CATEGORY – the name of the product’s category (during executing the query)
- PRODUCT_BRAND – product brand (during executing the query)
- PRODUCT_MANUFACTURER – producer of the product (during executing the query)
- PRODUCT_COLOR – the colour of the product (during executing the query)
- PRODUCT_GENDER – gender of the product (during executing the query)
Exemplary query:
- cart[lower{CATEGORY} ? ‚%leather%’ | lower{CATEGORY} ? ‚%shoe%’] – people who purchased the product from given categories
TRANSACTION STATISTICS SEGMENT
Segment’s scheme: transaction_stats[{conditions}; {start}; {end}]
- TOTAL – sum of the transaction values
- AVG – average transaction value
- MIN – minimal transaction value
- MAX – maximal transaction value
- COUNT – number of transactions
- SIZE – number of purchased products
Exemplary query:
- transaction_stats[TOTAL > 1000.0 & AVG > 50.0; ‚2018-01-01’; ‚2019-01-01’] – Contacts with the sum of Contact values in the period, greater than and average transaction value greater than
TRANSACTION STATISTICS SEGMENT IN THE PERIOD
Segment’s scheme: transaction_stats_in_period[{conditions}; {start}; {end}]
- TOTAL – sum of the transaction values
- AVG – average transaction value
- MIN – minimal transaction value
- MAX – maximal transaction value
- COUNT – number of transactions
- SIZE – number of purchased products
Exemplary query:
- transaction_stats_in_period[TOTAL > 1000.0 & AVG > 50.0; ‚2018-01-01’; ‚2019-01-01’] – Contacts with a total sum of transaction values greater than and an average sum of transaction values in the period greater than
CONNECTING SEGMENTS
It is possible to connect each segment in any number of repetitions as the union, quotient, and set difference (union, intersection, exclusion) and implementing this connection.
Segments can be connected using the following operators:
- * – intersection of sets
- + – union of sets
- – – set difference
Exemplary query:
- transaction[lower{CATEGORY} ? ‚%skin problems%’ | lower{CATEGORY} ? ‚%liver protection%’] – event[TYPE = ‚VR’ & lower{SOURCE} ~ ‚.*facebook.*’] – people who have ever bought a product from the given categories and never entered the website from facebook
- cart[lower{CATEGORY} ? ‚%skin problems%’ | lower{CATEGORY} ? ‚%liver protection%’] – transaction[lower{CATEGORY} ? ‚%skin problems%’ | lower{CATEGORY} ? ‚%liver protection%’] – people who have ever added products for the given category to the cart , but have never bought it
Simple queries
Example: Contact visited from the source [XXX] through the last 7 days
Query: event[TIME >= ‚2019-08-02′ & TYPE=’EMC’]
Example: in the range from [XX] to [XX] the clients spent more than XX
Query: transaction_stats[AVG > 50.0; ‚2018-01-01’; ‚2019-01-01’]
Example: people who have bought a product from category [XX] in the time range
Query: transaction[CATEGORY ? ‚%category name%’ & TIME >= ‚2018-01-01’ & TIME < ‚2019-01-01’]
Example: adding to the cart an item priced more than [XX], in the time from [YY] to [ZZ]
Query: cart[PRODUCT_PRICE > XX & TIME > ‚YY’ & TIME < ‚ZZ’]
Example: finalizing transaction from the source type [XX] name [YY] and value greater than [ZZ]
Query: transaction_from_source[TYPE = ‚XX’ & SOURCE ‚YY’ & TRANSACTION_VALUE > ZZ]
Example: Contacts with the sum of the transaction values in period greater than [XX], average transaction value greater than [YY] in period [ZZ] do [AA]
Query: transaction_stats[TOTAL > XX & AVG > YY; ‚ZZ’; ‚AA’]
Example: Contacts with total sum of transaction values greater than [XX], average transaction value in period greater than [YY], in the time from [AA] do [BB],weekly [CC] (WEEK)
Query: transaction_stats_in_period[TOTAL > XX & AVG > YY; ‚AA’; ‚BB’]
Example: people who during [XX] days from transaction from the rule [YY] added to the cart product named [ZZ]
Query: cart_from_source[TYPE = ‚XX’ & days{TRANSACTION_TIME, EVENT_TIME} <= YY & PRODUCT_NAME = ‚ZZ’]
Complex queries
Example: Contacts with the sum of transactions in the period greater than [XX], aaverage transaction value greater than [YY] in the period from [ZZ] to [AA] and also added to the cart product priced more than [BB] after the date [CC]
Query: transaction_stats[TOTAL > XX & AVG > YY; ‚ZZ’; ‚AA’] * cart[PRODUCT_PRICE > BB & TIME > ‚CC’]
Example: people who have ever added to the cart products from the category [XX], but have never bought it
Query: cart[CATEGORY = ‚XX’] – transaction[CATEGORY = ‚XX’]