Clickstream analytics: sessions, funnels and audience search in SQL

docker run -d --name serenedb -p 7890:7890 \
  -e POSTGRES_PASSWORD=<password> serenedb/serenedb:26.11.2

psql -h localhost -p 7890 -U postgres

CREATE TABLE events (
    event_id  BIGINT,
    ts        TIMESTAMP,
    event     VARCHAR,        -- impression, click, page_view, search, add_to_cart or purchase
    user_id   VARCHAR,        -- the visitor's anonymous id
    campaign  VARCHAR,        -- ad events: the campaign, the creative and the site that showed it
    creative  VARCHAR,
    publisher VARCHAR,
    cost      DECIMAL(10,6),  -- impression: the price paid, CPM / 1000
    page      VARCHAR,        -- site events: the page path, UTM tags on landing pages
    title     VARCHAR,        -- the page's title or the product
    query     VARCHAR,        -- search: what the visitor typed
    revenue   DECIMAL(10,2)   -- purchase: the order value
);

CREATE TABLE creatives (
    creative VARCHAR,
    campaign VARCHAR,
    status   VARCHAR,         -- live or archived
    headline VARCHAR,
    emb      FLOAT[4]         -- hand-written here; a model's embeddings in production
);

CREATE TEXT SEARCH DICTIONARY ad_text AS
    split_text(case := 'lower')
    WITH (frequency, position, norm);

CREATE INDEX events_idx ON events
    USING inverted (ts, event, user_id, campaign, publisher, title ad_text, query ad_text);

CREATE INDEX creatives_idx ON creatives
    USING inverted (creative, campaign, status, headline ad_text, emb ivf (metric = 'l2'));

-- One day of ad impressions, 2026-11-05: four publishers, more traffic in the
-- evening than at night, and four times the usual traffic on deals-feed from
-- 14:00 to 15:00. Each row gets a fixed pseudo-random number from its id, so
-- every run loads the same day.
INSERT INTO events (event_id, ts, event, user_id, campaign, creative, publisher, cost)
SELECT id * 10,
       TIMESTAMP '2026-11-05 00:00:00' + m * INTERVAL '1 minute' + (h % 60) * INTERVAL '1 second',
       'impression',
       'v' || lpad((h / 60 % 20000)::VARCHAR, 5, '0'),
       CASE WHEN c < 55 THEN 'trail_shoes' ELSE 'rain_jackets' END,
       CASE WHEN c < 25 THEN 'cr-101' WHEN c < 55 THEN 'cr-102'
            WHEN c < 80 THEN 'cr-201' ELSE 'cr-202' END,
       publisher,
       cpm / 1000
FROM (
    SELECT *, h / 1200000 % 100 AS c
    FROM (
        SELECT p.publisher, p.cpm, g.m,
               (p.n * 1440 + g.m) * 100 + k.k AS id,
               ((p.n * 1440 + g.m) * 100 + k.k) * 2654435761 % 4294967296 AS h
        FROM (VALUES (1, 'news-daily.example',  6.00, 70),
                     (2, 'run-club.example',    9.50, 30),
                     (3, 'weather-now.example', 4.00, 40),
                     (4, 'deals-feed.example',  2.20, 25)) AS p(n, publisher, cpm, peak),
             generate_series(0, 1439) AS g(m),
             generate_series(1, 100) AS k(k)
        WHERE k.k <= round(p.peak * (0.6 - 0.4 * cos(2 * pi() * (g.m - 240) / 1440))
                           * CASE WHEN p.n = 4 AND g.m >= 840 AND g.m < 900 THEN 4 ELSE 1 END)
    )
);

-- Clicks: a share of the impressions that depends on the publisher and the
-- creative. From 14:00 to 15:00, deals-feed's clicks come one second after
-- the impression, at ten times its usual rate.
INSERT INTO events (event_id, ts, event, user_id, campaign, creative, publisher)
SELECT event_id + 1,
       ts + CASE WHEN burst THEN 1 ELSE 4 + floor(r2 * 40) END * INTERVAL '1 second',
       'click', user_id, campaign, creative, publisher
FROM (
    SELECT *,
           (event_id * 2246822519 % 4294967296) / 4294967296.0 AS r,
           (event_id * 3266489917 % 4294967296) / 4294967296.0 AS r2,
           publisher = 'deals-feed.example' AND ts >= TIMESTAMP '2026-11-05 14:00:00'
               AND ts < TIMESTAMP '2026-11-05 15:00:00' AS burst
    FROM events
    WHERE event = 'impression'
)
WHERE r < CASE publisher WHEN 'news-daily.example' THEN 0.0030
                         WHEN 'run-club.example'   THEN 0.0110
                         WHEN 'weather-now.example' THEN 0.0045
                         ELSE CASE WHEN burst THEN 0.0500 ELSE 0.0050 END END
          * CASE creative WHEN 'cr-101' THEN 1.0 WHEN 'cr-102' THEN 1.45
                          WHEN 'cr-201' THEN 0.85 ELSE 1.1 END;

-- What visitors did on the store's site: every ad click lands on its
-- campaign's page with UTM tags, and 2,500 visitors come in from search on the
-- home page. A share views a product, searches, adds to the cart and buys;
-- some come back three hours later and buy then. deals-feed's 14:00 clicks
-- land and leave. The day ends at midnight.
INSERT INTO events (event_id, ts, event, user_id, page, title, query, revenue)
SELECT v.id + s.n,
       v.ts + CASE WHEN v.burst THEN INTERVAL '1 second' ELSE s.after END,
       s.event,
       v.user_id,
       CASE WHEN s.n = 2 AND v.campaign IS NULL THEN '/'
            WHEN s.n = 2 THEN '/' || replace(v.campaign, '_', '-')
                || '?utm_source=' || v.publisher || '&utm_medium=display'
                || '&utm_campaign=' || v.campaign || '&utm_content=' || v.creative
            WHEN s.event = 'search' THEN '/search'
            WHEN s.event = 'add_to_cart' THEN '/cart'
            WHEN s.event = 'purchase' THEN '/checkout/done'
            ELSE (['/p/ridge-3', '/p/scree', '/p/trail-socks',
                   '/p/stormline', '/p/drizzle-shell', '/p/brim-cap'])[v.k] END,
       CASE WHEN s.n = 2 THEN CASE v.campaign WHEN 'trail_shoes' THEN 'Trail running shoes'
                                              WHEN 'rain_jackets' THEN 'Running rain jackets'
                                              ELSE 'Running gear for trails and rain' END
            WHEN s.event = 'search' THEN NULL
            ELSE (['Ridge 3 waterproof trail running shoe', 'Scree trail running shoe',
                   'Trail running socks, 3-pack', 'Stormline waterproof running jacket',
                   'Drizzle 180 g packable rain jacket', 'Running cap with brim'])[v.k] END,
       CASE WHEN s.event = 'search' THEN
           (['trail running shoes', 'waterproof trail shoes', 'trial running shoes',
             'trail sheos', 'trail shoes wide fit', 'rain jacket', 'waterprof running jacket',
             'packable rain jacket', 'running cap', 'headlamp', 'running socks'])[v.q] END,
       CASE WHEN s.event = 'purchase' THEN ([139.00, 119.00, 24.00, 149.00, 89.00, 29.00])[v.k] END
FROM (
    SELECT *,
           CASE campaign WHEN 'trail_shoes' THEN 1 + floor(r2 * 3)
                         WHEN 'rain_jackets' THEN 4 + floor(r2 * 3)
                         ELSE 1 + floor(r2 * 6) END::INTEGER AS k,
           CASE campaign WHEN 'trail_shoes' THEN 1 + floor(r3 * 5)
                         WHEN 'rain_jackets' THEN 6 + floor(r3 * 4)
                         ELSE 1 + floor(r3 * 11) END::INTEGER AS q
    FROM (
        SELECT event_id AS id, ts, user_id, campaign, creative, publisher,
               publisher = 'deals-feed.example' AND ts >= TIMESTAMP '2026-11-05 14:00:00'
                   AND ts < TIMESTAMP '2026-11-05 15:00:00' AS burst,
               (event_id * 2654435761 % 4294967296) / 4294967296.0 AS r,
               (event_id * 2246822519 % 4294967296) / 4294967296.0 AS r2,
               (event_id * 3266489917 % 4294967296) / 4294967296.0 AS r3
        FROM events
        WHERE event = 'click'
        UNION ALL
        SELECT 10000000 + i * 10,
               TIMESTAMP '2026-11-05 07:00:00'
                   + floor((i * 2971215073 % 4294967296) / 4294967296.0 * 57600) * INTERVAL '1 second',
               'v' || lpad((20000 + i)::VARCHAR, 5, '0'), NULL, NULL, NULL, false,
               (i * 2246822519 % 4294967296) / 4294967296.0,
               (i * 3266489917 % 4294967296) / 4294967296.0,
               (i * 2654435761 % 4294967296) / 4294967296.0
        FROM generate_series(1, 2500) AS g(i)
    )
) AS v
JOIN (VALUES (2, 'page_view',   INTERVAL '3 seconds',  0.0,   1.0),
             (3, 'page_view',   INTERVAL '40 seconds', 0.0,   0.55),
             (4, 'search',      INTERVAL '75 seconds', 0.0,   0.30),
             (5, 'add_to_cart', INTERVAL '150 seconds', 0.0,  0.12),
             (6, 'purchase',    INTERVAL '260 seconds', 0.0,  0.03),
             (7, 'page_view',   INTERVAL '3 hours',    0.03, 0.05),
             (8, 'purchase',    INTERVAL '3 hours 4 minutes', 0.03, 0.05)) AS s(n, event, after, lo, hi)
  ON v.r >= s.lo AND v.r < s.hi AND (NOT v.burst OR s.n = 2)
WHERE v.ts + s.after < TIMESTAMP '2026-11-06 00:00:00';

-- The creative library: four creatives live today, four from past flights.
-- emb has four hand-written dimensions (trail shoes, rain, price offer,
-- product detail) so the distances stay readable.
INSERT INTO creatives VALUES
('cr-101', 'trail_shoes',  'live',     'Grip on wet rock: trail shoes for the season',         [0.90, 0.30, 0.05, 0.40]),
('cr-102', 'trail_shoes',  'live',     'Waterproof trail runners with free shipping',          [0.80, 0.60, 0.50, 0.10]),
('cr-201', 'rain_jackets', 'live',     'A 180 g rain shell that packs into its own pocket',    [0.05, 0.90, 0.05, 0.50]),
('cr-202', 'rain_jackets', 'live',     'Run dry: waterproof running jackets from $89',         [0.10, 0.90, 0.60, 0.10]),
('cr-091', 'trail_shoes',  'archived', 'Trail shoes that drain fast after river crossings',    [0.90, 0.50, 0.00, 0.30]),
('cr-092', 'trail_shoes',  'archived', 'Free returns on every trail shoe, all season',         [0.80, 0.05, 0.70, 0.05]),
('cr-181', 'rain_jackets', 'archived', 'Reflective running jacket for dark, wet mornings',     [0.05, 0.60, 0.05, 0.70]),
('cr-182', 'rain_jackets', 'archived', 'Waterproof gear for wet trail runs, 15% off this week', [0.60, 0.80, 0.60, 0.10]);

VACUUM (REFRESH_TABLE) events;
VACUUM (REFRESH_TABLE) creatives;

SELECT event, count(*) AS events
FROM events
GROUP BY event
ORDER BY events DESC;

SELECT publisher,
       sum(impressions) AS impressions,
       sum(clicks) AS clicks,
       round(100.0 * sum(clicks) / sum(impressions), 2) AS ctr_pct,
       round(max(100.0 * clicks / impressions), 2) AS top_hour_ctr_pct,
       round(sum(spend), 2) AS spend_usd
FROM (
    SELECT publisher, time_bucket(INTERVAL '1 hour', ts) AS hour,
           count(*) FILTER (WHERE event = 'impression') AS impressions,
           count(*) FILTER (WHERE event = 'click') AS clicks,
           sum(cost) AS spend
    FROM events
    WHERE event IN ('impression', 'click')
    GROUP BY publisher, hour
)
GROUP BY publisher
ORDER BY spend_usd DESC;

WITH site AS (
    SELECT user_id, ts, page,
           CASE WHEN ts - lag(ts) OVER (PARTITION BY user_id ORDER BY ts)
                     <= INTERVAL '30 minutes' THEN 0 ELSE 1 END AS starts_session
    FROM events
    WHERE event IN ('page_view', 'search', 'add_to_cart', 'purchase')
), numbered AS (
    SELECT *, sum(starts_session) OVER (PARTITION BY user_id ORDER BY ts) AS session
    FROM site
), sessions AS (
    SELECT user_id, session,
           coalesce(nullif(regexp_extract(arg_min(page, ts), 'utm_source=([^&]+)', 1), ''),
                    'no UTM tags') AS source,
           count(*) AS events,
           date_diff('second', min(ts), max(ts)) AS seconds
    FROM numbered
    GROUP BY user_id, session
)
SELECT source, count(*) AS sessions,
       round(avg(events), 2) AS events_per_session,
       round(100.0 * count(*) FILTER (WHERE events = 1) / count(*), 1) AS bounce_pct,
       round(avg(seconds)) AS avg_seconds
FROM sessions
GROUP BY source
ORDER BY sessions DESC;

WITH firsts AS (
    SELECT user_id,
           arg_min(campaign, ts) FILTER (WHERE event = 'click') AS campaign,
           min(ts) FILTER (WHERE event = 'click') AS clicked,
           min(ts) FILTER (WHERE event = 'page_view' AND page LIKE '/p/%') AS viewed,
           min(ts) FILTER (WHERE event = 'add_to_cart') AS carted,
           min(ts) FILTER (WHERE event = 'purchase') AS bought
    FROM events
    WHERE event IN ('click', 'page_view', 'add_to_cart', 'purchase')
    GROUP BY user_id
)
SELECT campaign,
       count(*) AS clicked,
       count(*) FILTER (WHERE viewed > clicked) AS viewed_product,
       count(*) FILTER (WHERE carted > viewed AND viewed > clicked) AS added_to_cart,
       count(*) FILTER (WHERE bought > carted AND carted > viewed
                          AND viewed > clicked) AS purchased
FROM firsts
WHERE campaign IS NOT NULL
GROUP BY campaign
ORDER BY clicked DESC;

WITH clicks AS (
    SELECT user_id, ts, publisher FROM events WHERE event = 'click'
), purchases AS (
    SELECT user_id, ts, revenue FROM events WHERE event = 'purchase'
), spend AS (
    SELECT publisher, sum(cost) AS spend FROM events
    WHERE event = 'impression' GROUP BY publisher
)
SELECT c.publisher,
       round(any_value(s.spend), 2) AS spend_usd,
       count(*) AS purchases,
       sum(p.revenue) AS revenue_usd,
       round(sum(p.revenue) / any_value(s.spend), 1) AS roas
FROM purchases p
ASOF JOIN clicks c ON c.user_id = p.user_id AND p.ts >= c.ts
JOIN spend s ON s.publisher = c.publisher
GROUP BY c.publisher
ORDER BY roas DESC;

COPY (
    SELECT DISTINCT user_id
    FROM events_idx
    WHERE event = 'search'
      AND query @@ (ts_levenshtein('trail', 1) && ts_levenshtein('shoes', 1))
      AND user_id NOT IN (SELECT user_id FROM events WHERE event = 'purchase')
    ORDER BY user_id
) TO '/tmp/trail_shoes_segment.csv' (HEADER);

CREATE JOB clickstream_history EVERY 1 DAY OFFSET 2 HOURS AS $$
    COPY (SELECT *, ts::DATE AS day FROM events
          WHERE ts < current_date - INTERVAL '30 days')
    TO '/tmp/clickstream' (FORMAT parquet, PARTITION_BY (day), COMPRESSION zstd, APPEND);
    DELETE FROM events WHERE ts < current_date - INTERVAL '30 days';
$$;