SAPI Query Statements, Clauses, and Operators

A good query selects only the necessary data to be streamed and kept in memory for processing.

For Streams Lab and Session Stream

select
Specifies the part of the stream to return. For example, content group (wt.cg_n) and sub-content group (wt.cg_s).
*
Specifies a wildcard search in select. For example, to return all parameters that start with data., use select data.*.
Important: The wildcard must follow a dot and cannot be followed any additional characters:

  • Correct form:
    • select data.*
    • select data.wt.*
  • Incorrect form:
    • select data.*.x
    • select data*
where
Specifies how to filter the stream (for example, include only events from the United Kingdom with a certain campaign ID).
=
is equal to
!=
is not equal to
like
is like (use with * wildcard)
notlike
is not like (use with * wildcard)
EXISTS()
This Boolean function checks for the existence of a parameter. For example, if you want to see a stream of events only where the user is in the shopping funnel:

select * where EXISTS(data.wt.tx_e)
NOT_EXISTS()
This Boolean function checks for the absence of a parameter. For example, if you want to identify pages that do not have a Title and fix them:

select * where NOT_EXISTS(data.wt.ti)

For Session Streams Only

ANY
Example (simple): You want to view only sessions that have at least one product view:

select * where ANY(data.wt.tx_e='v')
Example (more complex): You want to view only sessions that have at least one product view event and the content group for that particular view is “Shoes”:

select * where ANY(data.wt.tx_e='v' and data.wt.cg_n='Shoes')
ALL
Example: You want to view only sessions that have no product view event:

select * where ALL(data.wt.tx_e!='v')