Product analytics

From a small music-app event log, compute time-to-value, activation, day-7 retention, engagement tiers, a signup→subscribe funnel, and rage-click friction — persisting shared building blocks so each step stays short.

This intermediate tutorial assumes RELATED() from E-commerce and persisted features from SaaS metrics . Run Data, Model, and both building-block cells before the metric queries.

Data

Four users, one event stream, a couple of subscriptions. Ava activates fast and returns on day 7. Ben onboards too late (>24h). Cara is a heavy player who converts. Drew rage-clicks play_button and never completes onboarding.

FeatureQL

/* SQL */
CREATE SCHEMA IF NOT EXISTS tutorial_pa;
--
DROP TABLE IF EXISTS tutorial_pa.subscriptions;
--
DROP TABLE IF EXISTS tutorial_pa.events;
--
DROP TABLE IF EXISTS tutorial_pa.users;
--
CREATE TABLE tutorial_pa.users (
  id BIGINT,
  name VARCHAR,
  signup_date DATE
);
--
INSERT INTO tutorial_pa.users VALUES
  (1, 'Ava',  DATE '2024-01-01'),
  (2, 'Ben',  DATE '2024-01-01'),
  (3, 'Cara', DATE '2024-01-05'),
  (4, 'Drew', DATE '2024-01-08');
--
CREATE TABLE tutorial_pa.events (
  id BIGINT,
  user_id BIGINT,
  event_type VARCHAR,
  element_id VARCHAR,
  session_id VARCHAR,
  ts TIMESTAMP
);
--
INSERT INTO tutorial_pa.events VALUES
  -- Ava: fast activate, 3 plays in 14d, day-7 return
  (1,  1, 'signup',              NULL,          's1', TIMESTAMP '2024-01-01 09:00:00'),
  (2,  1, 'complete_onboarding', NULL,          's1', TIMESTAMP '2024-01-01 09:10:00'),
  (3,  1, 'play_song',           NULL,          's1', TIMESTAMP '2024-01-01 09:15:00'),
  (4,  1, 'play_song',           NULL,          's2', TIMESTAMP '2024-01-03 10:00:00'),
  (5,  1, 'play_song',           NULL,          's3', TIMESTAMP '2024-01-08 20:00:00'),
  -- Ben: late onboard (>24h), never plays
  (6,  2, 'signup',              NULL,          's4', TIMESTAMP '2024-01-01 10:00:00'),
  (7,  2, 'complete_onboarding', NULL,          's5', TIMESTAMP '2024-01-02 14:30:00'),
  -- Cara: activated, heavy (4 plays), day-7 return
  (8,  3, 'signup',              NULL,          's6', TIMESTAMP '2024-01-05 11:00:00'),
  (9,  3, 'complete_onboarding', NULL,          's6', TIMESTAMP '2024-01-05 11:20:00'),
  (10, 3, 'play_song',           NULL,          's6', TIMESTAMP '2024-01-05 11:35:00'),
  (11, 3, 'play_song',           NULL,          's6', TIMESTAMP '2024-01-05 11:40:00'),
  (12, 3, 'play_song',           NULL,          's6', TIMESTAMP '2024-01-05 11:50:00'),
  (13, 3, 'play_song',           NULL,          's7', TIMESTAMP '2024-01-12 09:00:00'),
  -- Drew: rage clicks, never onboards
  (14, 4, 'signup',              NULL,          's8', TIMESTAMP '2024-01-08 12:00:00'),
  (15, 4, 'page_view',           'home',        's8', TIMESTAMP '2024-01-08 12:01:00'),
  (16, 4, 'error',               'play_button', 's8', TIMESTAMP '2024-01-08 12:05:00'),
  (17, 4, 'click',               'play_button', 's8', TIMESTAMP '2024-01-08 12:05:01'),
  (18, 4, 'click',               'play_button', 's8', TIMESTAMP '2024-01-08 12:05:02'),
  (19, 4, 'click',               'play_button', 's8', TIMESTAMP '2024-01-08 12:05:02'),
  (20, 4, 'click',               'play_button', 's8', TIMESTAMP '2024-01-08 12:05:03'),
  (21, 4, 'click',               'play_button', 's8', TIMESTAMP '2024-01-08 12:05:03'),
  (22, 4, 'click',               'play_button', 's8', TIMESTAMP '2024-01-08 12:05:04');
--
CREATE TABLE tutorial_pa.subscriptions (
  id BIGINT,
  user_id BIGINT,
  started_at DATE
);
--
INSERT INTO tutorial_pa.subscriptions VALUES
  (1, 1, DATE '2024-01-14'),
  (2, 3, DATE '2024-01-15');
--
SELECT CAST(COUNT(*) AS INTEGER) AS cnt FROM tutorial_pa.users;

Result

Count BIGINT
4

Model

Users are the binding entity. Events and subscriptions hang off user_id.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.PA AS
SELECT
    users := ENTITY(),
    user_id := INPUT(BIGINT#users)
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.PA.USERS	CREATED	Feature created as not exists
FM.PA.USER_ID	CREATED	Feature created as not exists

FeatureQL

CREATE OR REPLACE FEATURES IN FM.PA AS
SELECT
    tables.users := EXTERNAL_COLUMNS(
        id BIGINT#users BIND TO user_id,
        name VARCHAR,
        signup_date DATE
        FROM TABLE(tutorial_pa.users)
    ),
    tables.events := EXTERNAL_COLUMNS(
        user_id BIGINT#users BIND TO user_id,
        id BIGINT,
        event_type VARCHAR,
        element_id VARCHAR,
        session_id VARCHAR,
        ts TIMESTAMP
        FROM TABLE(tutorial_pa.events)
    ),
    tables.subscriptions := EXTERNAL_COLUMNS(
        user_id BIGINT#users BIND TO user_id,
        started_at DATE
        FROM TABLE(tutorial_pa.subscriptions)
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.PA.TABLES.USERS	CREATED	Feature created as not exists
FM.PA.TABLES.EVENTS	CREATED	Feature created as not exists
FM.PA.TABLES.SUBSCRIPTIONS	CREATED	Feature created as not exists

Persist the building blocks

First-touch timestamps, day-N calendar dates, and play counts in the first 14 days — then the flags and tiers that every later question reuses.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.PA AS
SELECT
    user_name := tables.users[name],
    signup_date := tables.users[signup_date],
    signup_ts := signup_date::TIMESTAMP,
    signup_event_ts := user_id.RELATED(
        MIN(
            IF(
                tables.events[event_type] = 'signup',
                tables.events[ts],
                NULL(TIMESTAMP)
            )
        )
        GROUP BY tables.events[user_id]
    ),
    first_onboard_ts := user_id.RELATED(
        MIN(
            IF(
                tables.events[event_type] = 'complete_onboarding',
                tables.events[ts],
                NULL(TIMESTAMP)
            )
        )
        GROUP BY tables.events[user_id]
    ),
    first_play_ts := user_id.RELATED(
        MIN(
            IF(
                tables.events[event_type] = 'play_song',
                tables.events[ts],
                NULL(TIMESTAMP)
            )
        )
        GROUP BY tables.events[user_id]
    ),
    sub_start := user_id.RELATED(
        MIN(tables.subscriptions[started_at])
        GROUP BY tables.subscriptions[user_id]
    ),
    onboard_minutes := DATE_SUBTRACT(
        first_onboard_ts,
        signup_event_ts,
        'minute'
    ),
    day7_date := DATE_TRUNC(DATE_ADD(signup_ts, 'day', 7), 'day')::DATE,
    day14_ts := DATE_ADD(signup_ts, 'day', 14),
    day7_events_count := user_id.RELATED(
        COUNT_IF(DATE_TRUNC(tables.events[ts], 'day')::DATE = day7_date)
        GROUP BY tables.events[user_id]
    ),
    play_count_14d := user_id.RELATED(
        COUNT_IF(
            tables.events[event_type] = 'play_song'
            AND tables.events[ts] >= signup_ts
            AND tables.events[ts] < day14_ts
        )
        GROUP BY tables.events[user_id]
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.PA.USER_NAME	CREATED	Feature created as not exists
FM.PA.SIGNUP_DATE	CREATED	Feature created as not exists
FM.PA.SIGNUP_TS	CREATED	Feature created as not exists
FM.PA.SIGNUP_EVENT_TS	CREATED	Feature created as not exists
FM.PA.FIRST_ONBOARD_TS	CREATED	Feature created as not exists
FM.PA.FIRST_PLAY_TS	CREATED	Feature created as not exists
FM.PA.SUB_START	CREATED	Feature created as not exists
FM.PA.ONBOARD_MINUTES	CREATED	Feature created as not exists
FM.PA.DAY7_DATE	CREATED	Feature created as not exists
FM.PA.DAY14_TS	CREATED	Feature created as not exists
FM.PA.DAY7_EVENTS_COUNT	CREATED	Feature created as not exists
FM.PA.PLAY_COUNT_14D	CREATED	Feature created as not exists

FeatureQL

CREATE OR REPLACE FEATURES IN FM.PA AS
SELECT
    activated := COALESCE(onboard_minutes, 9999) <= 1440,
    retained_day7 := day7_events_count > 0,
    engagement_tier := CASE
        WHEN play_count_14d >= 4 THEN 'heavy'
        WHEN play_count_14d >= 2 THEN 'medium'
        ELSE 'light'
    END,
    converted := COALESCE(sub_start > DATE '1900-01-01', FALSE)
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.PA.ACTIVATED	CREATED	Feature created as not exists
FM.PA.RETAINED_DAY7	CREATED	Feature created as not exists
FM.PA.ENGAGEMENT_TIER	CREATED	Feature created as not exists
FM.PA.CONVERTED	CREATED	Feature created as not exists

ACTIVATED = onboarding within 24 hours of signup. RETAINED_DAY7 = any event on the calendar day that is exactly signup + 7 days. Engagement: heavy ≥4 plays in 14 days, medium 2–3, light 0–1.

Time to value

Minutes from signup event to first play_song. Null if they never play.

FeatureQL

WITH
    TTV_MINUTES := DATE_SUBTRACT(FIRST_PLAY_TS, SIGNUP_EVENT_TS, 'minute'),
SELECT
    USER_NAME,
    TTV_MINUTES
FROM FM.PA
FOR
    USER_ID := BIND_VALUES(ARRAY[1, 2, 3, 4])
ORDER BY USER_NAME;

Result

USER_NAME VARCHAR	TTV_MINUTES BIGINT
Ava	15
Ben	NULL
Cara	35
Drew	NULL

Ava 15, Cara 35, Ben and Drew null.

Activation rate (Jan 1 cohort)

Bind only the Jan 1 signups. Rate = activated / cohort size.

FeatureQL

WITH
    cohort_date := signup_date,
    activated_count := SUM(IF(activated, 1, 0)) GROUP BY cohort_date,
    cohort_size := COUNT(1) GROUP BY cohort_date,
    activation_rate := ROUND(
        100e0 * activated_count::DOUBLE / cohort_size::DOUBLE,
        1
    )
SELECT
    cohort_date,
    activated_count,
    cohort_size,
    activation_rate
FROM FM.PA
FOR
    user_id := BIND_VALUES(ARRAY(1, 2))
;

Result

COHORT_DATE TIMESTAMP	ACTIVATED_COUNT BIGINT	COHORT_SIZE BIGINT	ACTIVATION_RATE DECIMAL
2024-01-01T00:00:00	1	2	50

Ava yes, Ben no → 50%.

Day-7 retention

FeatureQL

SELECT
    USER_NAME,
    RETAINED_DAY7
FROM FM.PA
FOR
    USER_ID := BIND_VALUES(ARRAY[1, 2, 3, 4])
ORDER BY USER_NAME;

Result

USER_NAME VARCHAR	RETAINED_DAY7 BOOLEAN
Ava	true
Ben	false
Cara	true
Drew	false

Ava and Cara true (events on Jan 8 and Jan 12). Ben and Drew false.

Engagement tiers

FeatureQL

SELECT
    USER_NAME,
    PLAY_COUNT_14D,
    ENGAGEMENT_TIER
FROM FM.PA
FOR
    USER_ID := BIND_VALUES(ARRAY[1, 2, 3, 4])
ORDER BY USER_NAME;

Result

USER_NAME VARCHAR	PLAY_COUNT_14D BIGINT	ENGAGEMENT_TIER VARCHAR
Ava	3	medium
Ben	0	light
Cara	4	heavy
Drew	0	light

Cara heavy (4 plays). Ava medium (3). Ben and Drew light.

Funnel

Per-user step flags, then roll up to counts. Steps must happen in order: signup → onboard → play → subscribe.

FeatureQL

WITH
    DID_SIGNUP := COALESCE(SIGNUP_EVENT_TS > TIMESTAMP '1900-01-01', FALSE),
    DID_ONBOARD := COALESCE(FIRST_ONBOARD_TS > SIGNUP_EVENT_TS, FALSE),
    DID_PLAY := COALESCE(FIRST_PLAY_TS > COALESCE(FIRST_ONBOARD_TS, TIMESTAMP '1900-01-01'), FALSE),
    DID_SUBSCRIBE := COALESCE(SUB_START > DATE '1900-01-01', FALSE),
SELECT
    USER_NAME,
    DID_SIGNUP,
    DID_ONBOARD,
    DID_PLAY,
    DID_SUBSCRIBE
FROM FM.PA
FOR
    USER_ID := BIND_VALUES(ARRAY[1, 2, 3, 4])
ORDER BY USER_NAME;

Result

USER_NAME VARCHAR	DID_SIGNUP BOOLEAN	DID_ONBOARD BOOLEAN	DID_PLAY BOOLEAN	DID_SUBSCRIBE BOOLEAN
Ava	true	true	true	true
Ben	true	true	false	false
Cara	true	true	true	true
Drew	true	false	false	false

FeatureQL

WITH
    did_signup := COALESCE(signup_event_ts > TIMESTAMP '1900-01-01', FALSE),
    did_onboard := COALESCE(first_onboard_ts > signup_event_ts, FALSE),
    did_play := COALESCE(
        first_play_ts > COALESCE(first_onboard_ts, TIMESTAMP '1900-01-01'),
        FALSE
    ),
    did_subscribe := COALESCE(sub_start > DATE '1900-01-01', FALSE),
    n_signup := COUNT_IF(did_signup),
    n_onboard := COUNT_IF(did_onboard),
    n_play := COUNT_IF(did_play),
    n_subscribe := COUNT_IF(did_subscribe)
SELECT
    n_signup,
    n_onboard,
    n_play,
    n_subscribe
FROM FM.PA
FOR
    user_id := BIND_VALUES(ARRAY(1, 2, 3, 4))
;

Result

N_SIGNUP BIGINT	N_ONBOARD BIGINT	N_PLAY BIGINT	N_SUBSCRIBE BIGINT
4	3	2	2

4 → 3 → 2 → 2 (Drew drops at onboarding; Ben drops at play).

Rage clicks

Five or more clicks on the same element in a 3-second window (unix-second RANGE window over the click stream).

FeatureQL

WITH
    ALL_EVENTS := USER_ID.RELATED(
        ARRAY_AGG(ROW(
            TABLES.EVENTS[event_type] AS event_type,
            TABLES.EVENTS[element_id] AS element_id,
            TABLES.EVENTS[session_id] AS session_id,
            TABLES.EVENTS[ts] AS ts
        )) GROUP BY TABLES.EVENTS[user_id]
    ),
    CLICKS := ALL_EVENTS.TRANSFORM(
        SELECT element_id, session_id, TO_UNIXTIME(ts) AS ts_sec
        WHERE event_type = 'click'
    ),
    CLICK_WINDOWS := CLICKS.TRANSFORM(
        SELECT element_id, session_id, ts_sec,
            COUNT(1) OVER (
                PARTITION BY element_id, session_id
                ORDER BY ts_sec
                RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
            ) AS clicks_in_3s
    ),
    RAGE_RESULTS := CLICK_WINDOWS.TRANSFORM(
        SELECT element_id, MAX(clicks_in_3s) GROUP BY element_id AS max_clicks
    ).TRANSFORM(
        SELECT element_id, max_clicks WHERE max_clicks >= 5
    ),
SELECT
    USER_NAME,
    RAGE_RESULTS
FROM FM.PA
FOR
    USER_ID := BIND_VALUES(ARRAY[1, 2, 3, 4])
ORDER BY USER_NAME;

Result

USER_NAME VARCHAR	RAGE_RESULTS VARBINARY
Ava	[]
Ben	[]
Cara	[]
Drew	[{element_id: play_button, max_clicks: 6}]

Only Drew: play_button with 6 clicks in 3s.

What's next

Analytics overview — concept map for this series
Temporal & experiments — SCD as-of, bi-temporal cutoffs, A/B CI
Healthcare episodes — interval merge and readmissions
Marketing attribution — credit models over sequences
SaaS metrics — another persisted health library

Last update at: 2026/08/04 11:25:38

On this page

Data Model Persist the building blocks Time to value Activation rate (Jan 1 cohort)Day-7 retention Engagement tiers Funnel Rage clicks What's next