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: Creating a segment in the text editor, there is no possibility to switch it to the wizard editor.

To open the text editor, go to

DYNAMIC SEGMENTS → SETTINGS → 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’ lub ‘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’ lub ‘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

SEGMENT STATYSTYK Z TRANSAKCJI

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 

SEGMENT STATYSTYK TRANSAKCJI W OKRESIE

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 the total sum of the transaction values greater than and average sum of the transactions’ in period value greater than 

SEGMENTS CONNECTING

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.

Segmenty można łączyć za pomocą operatorów:

  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,które kupiły XX kategorię w przedziale czasu

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], average 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