task Funnels & conversion · hard

Attribute session conversion to first touch

For each first-touch channel, count sessions and converted_sessions, then return conversion_pct rounded to two decimals. A converted session contains an ordered view → checkout → purchase of the same cart within that session.

what to return

Columns

channelsessionsconverted_sessionsconversion_pct in this order

Rows

4 rows · in the order asked

Watch for

Deduplicate globally by event_key, keeping the smallest id; a retry may have a conflicting payload.

Use (occurred_at, id) as the total event order; equal timestamps follow id order. All times are UTC.

A shopper starts a session at the first canonical event or after a gap strictly greater than 30 minutes from their previous canonical event. Exactly 30 minutes stays in the same session; midnight alone does not split it.

Session numbers start at 1 per shopper and include every event type, including heartbeat and purchase-only sessions.

Attribute the whole session to the channel of its first event. Conversion is binary per session, regardless of how many carts convert. There is no additional view-to-purchase time limit. Include zero-conversion channels. Order by channel.

Skill

Measure session-level conversion using a deterministic acquisition channel. Refresher: Sessionize an event stream →

expected result

Your query should return exactly these columns and values. Run it and compare.

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.