Example Queries

Find change-state events

SELECT
    *
FROM session_events_view
WHERE timestamp BETWEEN '2025-08-10' AND '2025-08-11'
  AND organization_name = 'my organization'
  AND venue = 'my venue'
  AND action = 'change-state';

Aggregate product-sessions

Users start their journey on one device and continue on another. Such a session may contain two "product-sessions" (ie. two sessions with distinct context). For example, a customer might begin by using a directory to search for a store, then scan a QR code to get directions on their mobile phone. This query splits such sessions apart and counts the number of events that occurred in each product-session.

SELECT
    count(*) AS number_of_events_in_session,
    session_id,
    context
    -- other fields whose value remains constant throughout a product-session
FROM session_events_view
WHERE timestamp BETWEEN '2025-08-10' AND '2025-08-11'
  AND organization_name = 'my organization'
GROUP BY ALL;

Result

NUMBER_OF_EVENTS_IN_SESSION	SESSION_ID	CONTEXT
16	XaGW8ggviPjasUkrqgoOGvBxZ+YZfR/XyXwz7O+Dkp0=	Directory
14	HhkF9om5R6dd/3LsNpn3B+Q/iEa/5N7D2xIg43BCBKg=	Premium Directory
27	HdCYKU6c90WBmfYiO/uLwwxsd+mMzWNDw9IFLZyboFw=	Web
4	domPic+L2h+IqOrTrtJrs9o4NnZPpLoJwwYIN5XQkns=	Web

Compact Searches

This query simplifies your data by combining multiple consecutive searches into a single search event, then shows you exactly what the user selected afterward - whether it was a specific location or a category.

You can see the complete picture of search behavior - not just what people search for, but what they actually choose. This helps you understand:

• Which search results are most effective
• Whether users find what they're looking for quickly
• How to improve your search functionality to better match user intent

SELECT
    timestamp,
    context,
    action,
    query,
    next_action,
    location_id_found,
    category_id_found,
    is_completed_search
FROM (
    SELECT
        *,
        data:query::STRING AS query,
        LEAD(action) OVER (
            PARTITION BY session_id, context
            ORDER BY timestamp ASC
        ) AS next_action,
        LEAD(data) OVER (
            PARTITION BY session_id, context
            ORDER BY timestamp ASC
        ) AS next_data,
        action = 'search' AND COALESCE(next_action = 'search', FALSE) AS is_ignored,
        next_data:location::STRING AS location_id_found,
        next_data:category::STRING AS category_id_found,
        action = 'search' AND next_action IN ('select-location', 'select-category') AS is_completed_search
    FROM session_events_view
    WHERE timestamp BETWEEN '2025-08-10' AND '2025-08-11'
      AND organization_name = 'my organization'
)
WHERE action = 'search'
    AND is_ignored = FALSE;

Result

TIMESTAMP	CONTEXT	ACTION	QUERY	NEXT_ACTION	LOCATION_ID_FOUND	CATEGORY_ID_FOUND	IS_COMPLETED_SEARCH
2025-08-10 22:42:52.617 +0000	Web	search	Sushi jiro	change-state			FALSE
2025-08-10 22:42:52.617 +0000	Web	search	Bra	select-location	5b0f85d2e9b85720eb59729d		TRUE
2025-08-10 20:12:09.616 +0000	Web	search	Brands	change-state			FALSE
2025-08-10 20:12:09.616 +0000	Web	search	Cheese	select-category		6c0f85d2e9b85720eb5972de	TRUE