Developer Mode - Example Queries

This section details various examples of queries viewed written in Developer Mode to demonstrate how to use IQL Syntax to define your Actions to capture user behavior.

In this topic:

Example Queries

Purchase Retargeting by Email

Description

The following query captures the purchase The following query will trigger immediately when a user makes a purchase and send the cart contents to the connection (for example, Oracle Responsys).

In this use case, Oracle Responsys could then immediately trigger a follow up email with the customer upon purchase.

Note: This query does not provide guidance on how to configure the use case in full - only the required IQL syntax.

Query Explained

  • Trigger when:

    • Closed Session is false (that is the session is open)

  • Select:

    • The Customer ID (data.wt.customer_id) in the current event

    • The Product SKUs (data.wt.pn_sku) in the current event

  • Where:

    • The Customer ID exists (data.wt.customer_id)

Tip: Selecting parameters

Parameters are selected so that you can send them to your connection so they can be used. In this example, the Customer ID will be used to find a matching customer record in Responsys and the Product SKUs will be sent in order to understand what products the user purchased.

Query

IQL Query

ON TRIGGER ( /* Ensure the session is not closed */ NOT behavior.[Close session] ) SELECT /* Select the customer ID and cart contents */ data.wt.customer_id as 'Customer ID', data.wt.pn_sku as 'Product SKUs' WHERE /* Ensure the customer ID is present and this is a purchase event */ data.wt.tx_e = 'p' AND Exists(data.wt.customer_id)

Tip: Why data.wt.customer_id instead of parameter.[Customer ID]?

This example assumes that you have not named your custom parameter data.wt.customer_id as a Customer ID within Settings and not made it available within the actions user interface. If you do this, you can then refer to the parameter as either data.wt.customer_id or parameter.[Customer ID].

Example Output

Event - Purchase Event

Below is a sample of the data the website sends to Infinity when the customer makes the purchase. This is evaluated by the query when determining whether to trigger the action:

Parameter Value Notes
session.closed false Closed Session (This denotes that the session has not closed)
data.wt.customer_id CUST123 The customer's ID
data.wt.pn_sku SKU2-PSD;SKU3-SDJ;SKU8-DAS The product SKUs the customer purchased
data.wt.es https://mywebsite.com/cart/thank-you The URL of the page
...other parameters... ... This not an exhaustive list of parameters available within the event

Action Selections

Based on the above event, the action will trigger and select the following data (which is then available to be sent on to the required destination - Oracle Responsys in this example):

Selection 1 - Customer ID

The following selection will return CUST123 (the customer's ID):

data.wt.customer_id as 'Customer ID' /* returns 'CUST123' */


Selection 2 - Product SKUs

The following selection will return KU2-PSD;SKU3-SDJ;SKU8-DAS (the product SKUs the customer purchased):

data.wt.pn_sku as 'Product SKUs' /* returns 'SKU2-PSD;SKU3-SDJ;SKU8-DAS' */

Cancel Subscription Intervention

Description

The following query will trigger when a customer clicks on the Cancel Subscription button if the customer has a lifetime value exceeding $1000.00 and is based in the United States of America. It will immediately send the captured data to the connection to render a support agent chatbox on the page to try to save the customer.

Note: This query does not provide guidance on how to configure the use case in full - only the required IQL syntax.

Query Explained

  • Trigger when:

    • Closed Session is false (that is the session is open)

  • Select:

    • The parameter Event Name (data.wt.event_name - set to parameter.[Event Name]) in the current event

    • Their latest Customer ID (data.wt.customer_id - set to parameter.[Customer ID])

    • Their latest Customer Value (data.wt.customer_value - set to parameter.[Customer Value])

  • Where:

    • The Event Name parameter is cancel subscription click

    • The Customer Value is greater than 1000

    • Customer ID exists

    • Country is united states

Tip: Selecting parameters

Parameters are selected so that you can send them to your connection so they can be used. In this example, Event Name will be used for to understand that a support agent chat box should be rendered. The Customer ID and Customer Value will be used so that the support agent will understand who the customer is and how valuable they are (in order to offer perhaps a discount or offer if their value is high enough).

Query

IQL Query

ON TRIGGER ( /* Ensure the session is not closed */ NOT behavior.[Close session] SELECT /* Select the key event */ parameter.[Event Name] as 'Event Name', /* Select last seen Customer ID within the session */ Latest(parameter.[Customer ID]) as 'Customer ID' /* Select last seen Customer Value within the session */ Latest(parameter.[Customer Value]) as 'Customer Value' WHERE /* Ensure cancel subscription button is clicked */ parameter.[Event Name] = 'cancel subscription click' /* Ensure Customer ID has been seen during the session */ session(Exists(parameter.[Customer ID])) /* Ensure Country is 'united states' */ AND Latest(parameter.[Country]) = 'united states' /* Ensure Customer value is greater than 1000 USD */ AND Latest(parameter.[Customer Value]) > 1000

Example Output

Below are a series of events which are sent by the website to Oracle Infinity as the user browses through the website to cancel their subscription. All of these events will be evaluated in order to determine whether to trigger the above action (and what to send on to the desired connection):

Event 1 - User logs in

Parameter Value Notes
data.wt.customer_value 2199 Customer Value (Custom Parameter)
data.wt.customer_id 939833342 Customer ID (Custom Parameter)
...other parameters... ... This not an exhaustive list of parameters available within the event
session.closed false Closed Session (This denotes that the session has not closed)
data.wt.event_name login Event Name (Custom Parameter)
ext.geo.country united states Country
data.wt.es www.mywebsite.com/login Page URL

Event 2 - User clicks on cancel subscription

Parameter Value Notes
session.closed false This denotes that the session has not closed
ext.geo.country united states Country
data.wt.es www.mywebsite.com/account Page URL
data.wt.event_name cancel subscription click Event Name (Custom Parameter)
...other parameters... ... This not an exhaustive list of parameters available within the event

Action Selections

Based on the above session, the action will trigger on Event 2 (when the user clicks on the cancel subscription button) and will select the following data from the user's session:

Selection 1 - Event Name

The following selection will return cancel subscription click (the current event name):

data.wt.event_name as 'Event Name' /* returns 'cancel subscription click' */


Selection 2 - Customer value

The following selection will return 2199 (the last seen Customer Value):

Latest(parameter.[Customer Value]) as 'Customer Value' /* returns '2199' */


Selection 3 - Customer ID

The following selection will return 939833342 (the last seen Customer ID):

Latest(parameter.[Customer ID]) as 'Customer ID' /* returns '939833342' */

Trigger Cart Abandon Email upon Add to Cart or View Cart

Description

The following query expands upon the default Product Add Abandonment scenario and extends capture last products seen in the user's cart when they viewed it during the session (in addition to the products captured during the add to cart events).

It will select both the added SKUs and the SKUs seen in their last view cart as separate selections so that the receiving connection (for example, an email service provider such as Oracle Responsys) can decide what to do with the selections. It also selects both the Customer ID and SHA-256 Hashed Email Address identifiers (to provide the maximum chance of a match in the receiving connection in the event where the customer perhaps only identifies with the customer ID or the SHA-256 hashed email address).

Tip: Both selections are provided in the event that one selection is not populated for the session. For example, the user may add items to their cart and abandon it immediately without viewing the cart. Conversely, they may visit the site, open a previously populated cart, and then abandon it again.

Note: This query does not provide guidance on how to configure the use case in full - only the required IQL syntax.

Query Explained

  • Trigger when:

    • Closed Session is true (that is the session is closed)

    • AND the user has not purchased

    • AND :

      • they have either added a product to their cart

      • OR viewed their cart (visited the 'www.mywebsite.com/cart' page)

  • Select:

    • All of the Product SKUs added to their cart (if available)

    • The last Product SKUs they had in their cart when they viewed their cart

    • Their SHA-256 hashed email address (data.wt.e_id_s - set to parameter.[Email SHA256 Hash])

    • Their Customer ID (data.wt.customer_id -set to parameter.[Customer ID])

  • Where:

    • They have visited www.mywebsite.com

    • They have declared an identifier (either their customer ID or their SHA-256 hashed email address)

Tip: Selecting parameters

Parameters are selected so that you can send them to your connection so they can be used. In this example, all of the Product SKUs added to their cart within the session and the Product SKUs last seen in their cart be selected so that the email service provider (for example, Oracle Responsys). The SHA-256 hashed email address and Customer ID are selected so that a matching customer record can be found in the email service provider in order to allow a cart abandon email to be triggered automatically.

Query

IQL Query

ON TRIGGER ( /* On Session Close */ behavior.[Close session] /* Has NOT purchased */ AND NOT(behavior.[Purchase product]) /* Ensure they have added a product to their cart or have viewed their cart */ AND ( behavior.[Add product to cart] /* Has added a product to their cart */ OR session( parameter.[Page URL] = 'www.mywebsite.com/cart' /* Has viewed their cart */ ) ) ) SELECT /* Select the items added to cart */ behavior.[Add product to cart].[Product SKU] AS 'Added SKUs', /* Select the last products seen in their cart (but remove any duplicate Product SKUs using Distinct()) */ Latest(Distinct(parameter.[Product SKU])) FILTER( WHERE parameter.[Page URL] = 'www.mywebsite.com/cart' /* This is the URL where users view their cart */ AND Exists(parameter.[Product SKU]) /* Ensure that the view cart page contains the Product SKUs in their cart */ ) as 'SKUs in Cart', /* Select the SHA-256 hashed email address */ Latest(parameter.[Email SHA256 Hash]) AS 'Hashed Email', /* Select the customer ID */ Latest(parameter.[Customer ID]) AS 'Customer ID' WHERE /* Ensure they have visited www.mywebsite.com */ ( session(parameter.[Domain] like '%www.mywebsite.com%') ) /* Ensure they have an identifier */ AND ( session( Exists(parameter.[Email SHA256 Hash)]) /* They must have a SHA-256 hashed email */ OR Exists(parameter.[Customer ID]) /* OR a Customer ID */ ) )

Example Output

Below are a series of events which are sent by the website to Oracle Infinity as the user adds items to their cart, logs in and views their cart. All of these events will be evaluated in order to determine whether to trigger the above action (and what to send on to the desired connection):

Event 1 - Add Item to Cart

Parameter Value Notes
session.closed false Closed Session (This denotes that the session has not closed)
data.wt.es www.mywebsite.com/product/accessories/socks-blue Page URL
data.wt.tx_e a Transaction Event - this denotes that a product has been added to the cart
data.wt.pn_sku SKU-A52334 Product SKU - Added product SKU
data.wt.tx_u 2 Transaction Quantity - Quantity added
...other parameters... ... This not an exhaustive list of parameters available within the event

Event 2 - Add Item to Cart

Parameter Value Notes
session.closed false Closed Session (This denotes that the session has not closed)
data.wt.es www.mywebsite.com/product/jeans/blue-jeans Page URL
data.wt.tx_e a Transaction Event - this denotes that a product has been added to the cart
data.wt.tx_u 1 Transaction Quantity - Quantity added
data.wt.pn_sku SKU-C32412 Product SKU - Added product SKU
...other parameters... ... This not an exhaustive list of parameters available within the event

Event 3 - User logs in

Parameter Value Notes
session.closed false Closed Session (This denotes that the session has not closed)
data.wt.es www.mywebsite.com/login Page URL
data.wt.event_name login Custom parameter denoting a login
data.wt.tx_u 1 Transaction Quantity - Quantity added
data.wt.customer_id 939833342 Custom parameter the customer's ID
data.wt.e_id_s 7532291025ae0ba0b686113fdb01ac709fe47e0beeaa82d3259f100eafca3d9f Custom parameter denoting the SHA-256 hashed email address
...other parameters... ... This not an exhaustive list of parameters available within the event

Event 4 - View Cart

Parameter Value Notes
session.closed false Closed Session (This denotes that the session has not closed)
data.wt.es www.mywebsite.com/cart Page URL
data.wt.pn_sku SKU-A222130;SKU-A52334;SKU-C32412 Product SKU - List of SKUs in the user's cart
data.wt.tx_u 1;2;1 Transaction Quantity - Quantity of items in user's cart
...other parameters... ... This not an exhaustive list of parameters available within the event

Event 5 - Session Close Event (See Session Close Event)

Parameter Value Notes
session.closed true Closed Session (This denotes that the session has closed)
data.wt.es www.mywebsite.com/cart Page URL
data.wt.pn_sku SKU-A222130;SKU-A52334;SKU-C32412 Product SKU - List of SKUs in the user's cart
data.wt.tx_u 1;2;1 Transaction Quantity - Quantity of items in user's cart
...other parameters... ... This not an exhaustive list of parameters available within the event

Action Selections

Based on the above session, the action will trigger on Event 5 (when the session closes) and will select the following data from the user's session:

Selection 1 - Added Product SKUs

The following selection will return ["SKU-A52334","SKU-C32412"] (two item have been added to the cart so both are listed):

behavior.[Add product to cart].[Product SKU] /* returns ["SKU-A52334","SKU-C32412"] */


Selection 2 - SKUs in Cart

The following selection will return ["SKU-A222130","SKU-A52334","SKU-C32412"] (when they viewed their cart, it had not only the two SKUs added to their cart during this session but also an additional SKU from a previous session):

Latest(Distinct(parameter.[Product SKU])) FILTER( WHERE parameter.[Page URL] = 'www.mywebsite.com/cart' AND Exists(parameter.[Product SKU]) as 'SKUs in Cart' /* returns ["SKU-A222130","SKU-A52334","SKU-C32412"] */


Selection 3 - Hashed Email

The following selection will return 7532291025ae0ba0b686113fdb01ac709fe47e0beeaa82d3259f100eafca3d9f (the last seen SHA-256 email address):

Latest(parameter.[Email SHA256 Hash]) AS 'Hashed Email' /* returns '7532291025ae0ba0b686113fdb01ac709fe47e0beeaa82d3259f100eafca3d9f' */


Selection 4 - Customer ID

The following selection will return 939833342 (the last seen customer's ID):

data.wt.customer_id as 'Customer ID' /* returns 'CUST123' */

Learn more

Using Developer Mode

Developer Mode - IQL Reference

Actions