From: Dana, Head of E-commerce
Subject: How did the web shop convert in June?
We changed the checkout page in early June and I need to know how the funnel looks. The clickstream is in RIDGELINE.WEB.EVENTS, one row per event with the raw JSON in PAYLOAD. A session is payload:session_id; the steps are page_view, add_to_cart, checkout and purchase. Purchase events carry order_value. Careful: the feed sends that as a number on some events and as a string on others.
I'd like the funnel as session counts, the conversion rate, revenue, and then all of it by device type.
This project builds one query up step by step. Each step starts from the query you wrote in the step before, so you can extend it rather than start again. If you didn't solve the previous step in this session, you get its model answer as the starting point.
The data is a month of clickstream from the web shop: about 160 events, with the device, user and cart inside each event's JSON. Count sessions, not events: a visitor who views five pages is one session at the top of the funnel.