Event Selection Subqueries

What Are Event Selection Subqueries

Webtrends Action Center supports an advanced feature that allows you to “subquery” the events within the Streams you have selected in order to make sure that only the most complete and accurate data is being sent to a remarketing feed.

To describe the problem: in a session Stream query in the Streams Lab, if you select a “Product SKU” for example, you can’t tell the Stream to only output Product SKU events of a certain action type (like “add to cart”), you get all events from the session that meets the criteria of the Stream query, meaning you may get all product SKUs for the product view events AND the add to cart because all it requires is that the session meet the abandonment criteria. Because we are collecting all the events of the session, it can happen that you may not be sending accurate data about abandonment, purchases or any product sku events due to the way that we are looking for the values for any given parameter and sending them along in Action Center.

The Streams query is only for the use of figuring out which Streams meet the criteria and then those sessions instantly become available.  But it requires an “Event Selection Query” or subquery to specify which events within that session you want to send along in your remarketing output.

Introduction to Subqueries


When is a subquery needed

Whenever there are multiple events that contain the parameter you are trying to remarket then a subquery may be needed.subquery1



How Subqueries Work

 Example query

SELECT ANY(dcsvid), CONCAT(pn_sku,";"), CONCAT(departuredate,";"), CONCAT(returndate,";") WHERE tx_e=’a’ AND EXISTS(pn_sku)

Why is the WHERE in this sub-query not the same as the WHERE in the Labs query?

The labs query are session “selectors” not event criteria.  For example, you may have a WHERE clause in the Streams Lab that says you only want sessions WHERE mc_id=’SpecialOffer123′ but that sure doesn’t mean you only want the events with that mc_id on them or that would be one very limited output.

The WHERE in the subquery is helping you identify which events in the session you want to grab the SELECT parameters from. It filters down the number of events in the session summary.  Incidentally, this is why the ANY option is important.  If you have filtered the session SELECT down to just the tx_e=’a’ events (cart add events) then you may (and likely will) lose the customer ID that only appears on the cart events or the mc_id which only appears on the first hit of the session or…

Labs query WHERE:
WHERE referrer='facebook.com' AND mc_id='SummerSale' AND ANY(tx_e!='p') AND ANY(tx_e='a')

Subquery WHERE:
WHERE tx_e='a' AND EXISTS(pn_sku)

The labs WHERE said:  “Give me all the sessions that came from Facebook from my new SummerSale campaign where the user did add something to cart but did not have a purchase event.” These sessions will have all events in them though, including product views, page views, cart adds, ad clicks, etc. The subquery said: “Filter those sessions to only show the cart add events and the product SKU is there”

What is FIRST and LAST?

Without a Subquery the Action Center will always assume the “last” event.  This means it will be overwritten if any new values show up.  Since there are a lot of times where the FIRST is significant (referring params, campaign IDs, etc.) we are adding FIRST.  LAST is being added because we are also adding a concept of ALL (see below).

Why Distinct?

In many cases, the remarketing effort is simply targeting the unique product SKUs and isn’t interested in the price of the cart or contents with units/price.  In those cases, it’s not convenient to see the same product appearing multiple times in a Concatenated list.  So using DISTINCT will give the unique values for the parameter.

Please note however that if you use DISTINCT you really can’t use this in conjunction with other custom paired params.  Like if in the travel use case you want the DISTINCT destination cities and you want it paired with the departure date… well the parameter matches may not match up anymore (DISTINCT cities may be fewer than the distinct dates naturally).

What is ANY?

ANY is special because it essentially says to ignore the WHERE filter and find any instance of the parameter and save the value (like collecting the external visitor ID for example which may not occur on a cart event). This is different than the LAST, FIRST or ALL because those key words indicate that the parameter _is_ subject to the WHERE.  ANY allows you to grab a value for a parameter that happened on an event that simply may not meet the criteria of the WHERE filter

This query filters the events in the session to contain only the cart add events.  Unfortunately the external visitor ID (dcsvid) may only appear once in the session and chances are it isn’t on the tx_e=a events.

SELECT ANY(dcsvid), pn_sku, custom.departuredate, custom.returndate WHERE tx_e=’a’


Action Center must decide what to send to the email vendor.  Do we send individual events and ask them to do some analysis to figure out the actual cart contents?  Or do we send a single INSERT that concatenates the cart contents?

If this is a triggered campaign send, they need them all in a single event obviously.  If this is in a table saved for some kind of scheduled batch send then each vendor might be able to support either or both.

The following examples are two ways to express the same cart – two products purchased with one unit of ABC123 and 3 units of XYZ890.

We could send one INSERT with multiple products in the cart concatenated:

Product SKU Units Purchased
ABC123;XYZ890 1;3

Or we can send multiple events:

Product SKU Units Purchased
ABC123 1
XYZ890 3

Why do we need to support AND and EXISTS in the WHERE?

Generally all Actions created in the Action Center require custom parameters to their product events.  These extra parameters are essential to understand what was actually added to cart.  For retail, the color and style parameters are as important as the fact that they were looking at a certain brand of sunglasses frames.  For travel the destination city and dates are critical.  Adding these to the WHERE allows us to make sure the quality of data is there.The latter is undoubtedly cleaner but requires some kind of processing on the other end to make it a complete view of the cart.  It will not be supported in a Campaign Trigger naturally. Since vendors will differ on their delimiter, the CONCAT allows to specify the delimiter.