MENU

Search our knowledge base or contact us:  support@salesmanago.com,  +1 800 960 0640

Dynamic segments – text editor

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}]

  1. TIME – the time of the event in format ‘yyyy-MM-dd’ or  ‘yyyy-MM-dd HH:mm:ss’
  2. 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)
  3. 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}]

  1. TIME – a time of the transaction in format ‘yyyy-MM-dd’ or ‘yyyy-MM-dd HH:mm:ss’
  2. TRANSACTION_VALUE – transaction value
  3. PRODUCT – product ID 
  4. PRODUCT_PRICE – product price (during executing the query)
  5. PRODUCT_NAME – product name (during executing the query)
  6. PRODUCT_CATEGORY – the name of the product’s category (during executing the query)
  7. PRODUCT_BRAND – product brand (during executing the query)
  8. PRODUCT_MANUFACTURER – producer of the product (during executing the query)
  9. PRODUCT_COLOR – the colour of the product (during executing the query)
  10. 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}]

  1. TIME – a time of the transaction in format ‘yyyy-MM-dd’ or ‘yyyy-MM-dd HH:mm:ss’
  2. TRANSACTION_VALUE – transaction value
  3. PRODUCT – product ID 
  4. PRODUCT_PRICE – product price (during executing the query)
  5. PRODUCT_NAME – product name (during executing the query)
  6. PRODUCT_CATEGORY – the name of the product’s category (during executing the query)
  7. PRODUCT_BRAND –  product brand (during executing the query)
  8. PRODUCT_MANUFACTURER – producer of the product (during executing the query)
  9. PRODUCT_COLOR – the colour of the product (during executing the query)
  10. 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}]

  1. TOTAL – sum of the transaction values
  2. AVG – average transaction value
  3. MIN – minimal transaction value
  4. MAX – maximal transaction value
  5. COUNT – number of transactions
  6. 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}]

  1. TOTAL – sum of the transaction values
  2. AVG – average transaction value
  3. MIN – minimal transaction value
  4. MAX – maximal transaction value
  5. COUNT – number of transactions
  6. 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:

  1. * – intersection of sets
  2. + – union of sets
  3. – – 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’]

If you need more information about the topic mentioned above, please contact us: support@salesmanago.com +1 800 960 0640