Web Traffic Funnel

Project roadmap

1. 01

   Define the funnel

   ~1h

   Write README.md defining: the events table (event id, user or anonymous id, event name, page, occurred at, traffic source, device and user agent), the funnel steps and which events count for each, the session rule (30 minutes of inactivity), that steps must happen in order within one session, and what counts as a bot. Done when: README.md defines each step and rule precisely.

2. 02

   Create and fill the table

   ~1.5h

   Write schema.sql with the events table and indexes, and generate a few weeks of realistic events with generate_series and random(): most visitors leave early, fewer reach each later step, with different rates per source and device. Add messy data: duplicate events, bot-like visitors (hundreds of events per minute or bot user agents) and steps out of order. Done when: the data loads with all these cases present.

3. 03

   Clean the events

   ~1.5h

   Create a view clean_events that removes exact duplicates (by event id, or same visitor, event and time), removes visitors identified as bots by user agent or event rate, and keeps only known event names. Count how many events each rule removed. Done when: the counts of removed events match what you planted.

4. 04

   Group events into sessions

   ~2h

   Using LAG over each visitor's events ordered by time, mark a new session when the gap is over 30 minutes, then number sessions with a running SUM of those marks. Create a sessions view with the session id, visitor, start, end, source, device and number of events. Done when: a visitor with a 2-hour gap has two sessions.

5. 05

   Build the funnel

   ~2.5h

   For each session, find the first time of each step, only counting a step if it happened after the previous one (with MIN ... FILTER and conditions on earlier steps, or a recursive approach). Then count sessions reaching each step, with the conversion rate from the previous step and from the start, and the median time between steps. Done when: the funnel shows sensible, decreasing numbers, and out-of-order steps don't count.

6. 06

   Compare segments

   ~1.5h

   Break the funnel down by traffic source, device and week, and find the step with the biggest drop-off in each segment. Save the funnel and the breakdowns as views. Done when: the breakdown shows different conversion rates per segment, matching how the data was generated.

7. 07

   Test with psql

   ~1.5h

   Write test.sql that, in a transaction, inserts a few hand-made visitors with known outcomes (a full purchase, a drop-off at the cart, steps out of order, two sessions, and a duplicate event), checks the sessions and funnel views against the expected numbers, raising an error on any difference, then rolls back. Done when: psql -f test.sql passes.

Resources

• DocsPostgreSQL: window functions
• DocsPostgreSQL: aggregate FILTER clause
• DocsPostgreSQL: generate_series
• Docspsql documentation
• ArticleConversion funnel (Wikipedia)