SQL query to filter visit having cart addition, & all pages viewed in a visit

Question

Hi Team,,  Looking for SQL query to filter visit having cart addition, & all pages viewed in a visit.. thanks in advance

SamuelPaulPeter · Answer

Hi @dineshrkumar

Use the below SQL to execute your requirement:

Please do not forget to replace your_experienceevents_table, visitorId, sessionId, eventTimestamp, pageName, eventType, and cartAction with the exact field names in your dataset.

SELECT
  visitorId,
  sessionId,
  MIN(eventTimestamp)                                   AS visit_start,
  MAX(eventTimestamp)                                   AS visit_end,
  array_join(                                           
    array_distinct(array_agg(pageName ORDER BY eventTimestamp)),
    ' | '
  )                                                     AS pages_viewed,
  SUM(CASE WHEN eventType = 'cart' AND cartAction = 'add' THEN 1 ELSE 0 END) AS cart_add_count
FROM your_experienceevents_table
WHERE eventTimestamp BETWEEN TIMESTAMP '2025-01-01 00:00:00' AND TIMESTAMP '2025-12-31 23:59:59'
GROUP BY visitorId, sessionId
HAVING SUM(CASE WHEN eventType = 'cart' AND cartAction = 'add' THEN 1 ELSE 0 END) > 0
ORDER BY visit_start DESC
LIMIT 1000;

If your site stores page path instead of pageName, use that field.
If cart-add is recorded as an event name ("productAddToCart"), adjust the CASE condition.
Remove or adjust the WHERE date window to control runtime.

If you still find it difficult, paste the exact table/field names (or the sample file) and I’ll adapt the query to your schema as much as I can.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded