Product analytics

This tutorial follows four music-app users from signup through activation, retention, engagement, conversion, and possible frustration.

Rather than write six unrelated event queries, you will persist a small set of user-level building blocks and reuse them across every metric.

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

Meet the four user journeys

The dataset is small enough to remember:

Ava onboards quickly, plays music, and returns on day 7.
Ben completes onboarding more than 24 hours after signup and never plays.
Cara becomes a heavy listener and subscribes.
Drew never completes onboarding and repeatedly clicks play_button.

FeatureQL

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

Build the user event model

Declare users, events, and subscriptions as distinct entities:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.PA AS
PERSIST
    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

Then map event and subscription rows back to their user:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.PA AS
PERSIST
    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

Users define the output grain for most of this tutorial. Events remain a child sequence that each metric can filter, order, or count differently.

Persist shared event landmarks

First persist the timestamps and counts used by several metrics: signup time, first onboarding, first play, the day-7 calendar date, and plays during the first 14 days.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.PA AS
PERSIST
    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, 7, 'day'), 'day')::DATE,
    day14_ts := DATE_ADD(signup_ts, 14, 'day'),
    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

Now derive the reusable labels:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.PA AS
PERSIST
    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 means onboarding occurred within 24 hours of signup.
RETAINED_DAY7 means the user produced any event on the calendar date exactly seven days after signup.
Engagement is heavy for at least 4 plays in 14 days, medium for 2–3, and light for 0–1.

Persisting these definitions keeps the following queries short and ensures the funnel and standalone metrics use the same rules.

Time to value

For this app, value begins when a user first plays a song. Measure the minutes from signup to the first play_song; return null when that event never occurs.

FeatureQL

DEFINE
    ttv_minutes := DATE_SUBTRACT(first_play_ts, signup_event_ts, 'minute')
RETURN
    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 reaches value in 15 minutes and Cara in 35. Ben and Drew never play, so their result is null.

Activation rate for the January 1 cohort

Restrict the population to users who signed up on January 1, then divide activated users by cohort size:

FeatureQL

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

Result

COHORT_DATE DATE	ACTIVATED_COUNT BIGINT	COHORT_SIZE BIGINT	ACTIVATION_RATE DOUBLE
2024-01-01T00:00:00	1	2	50

The cohort contains Ava and Ben. Ava activates and Ben does not, producing a 50% activation rate.

The cohort filter is part of the denominator. Filtering only the successful users would turn the metric into 100%.

Day-7 retention

Evaluate the persisted day-7 flag for every user:

FeatureQL

RETURN
    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 return on January 8 and January 12 respectively, exactly seven calendar days after their signups. Ben and Drew do not, so their flags are false.

This is calendar-day retention, not a rolling 168-hour window.

Engagement tiers

Classify users by their first 14 days of play activity:

FeatureQL

RETURN
    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 is heavy with 4 plays, Ava is medium with 3, and Ben and Drew are light.

Build a signup-to-subscription funnel

The funnel requires four events in order: signup → onboarding → first play → subscription. Start by exposing each user's step flags:

FeatureQL

DEFINE
    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)
RETURN
    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

Then roll those flags up to funnel counts:

FeatureQL

DEFINE
    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)
RETURN
    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

The funnel is 4 → 3 → 2 → 2. Drew drops before onboarding, and Ben drops before first play. Ava and Cara reach the subscription step.

Keeping user-level flags visible before aggregation makes a surprising funnel count easier to debug.

Detect rage-click friction

Define a rage click as at least five clicks on the same element within a 3-second window. The query uses a time-range window over each user's click stream.

FeatureQL

DEFINE
    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(
        RETURN
            element_id,
            session_id,
            ts_sec := TO_UNIXTIME(ts)
        WHERE event_type = 'click'
    ),
    click_windows := clicks.TRANSFORM(
        RETURN
            element_id,
            session_id,
            ts_sec,
            clicks_in_3s := COUNT() OVER (
                PARTITION BY element_id,
                session_id
                ORDER BY ts_sec
                RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
            )
    ),
    rage_results := click_windows.TRANSFORM(
        RETURN
            element_id,
            max_clicks := MAX(clicks_in_3s) GROUP BY (element_id)
    ).TRANSFORM(
        RETURN
            element_id,
            max_clicks
        WHERE max_clicks >= 5
    )
RETURN
    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 ARRAY<ROW<element_id VARCHAR, max_clicks BIGINT>>
Ava	[]
Ben	[]
Cara	[]
Drew	[{element_id: play_button, max_clicks: 6}]

Only Drew qualifies: 6 clicks on play_button within three seconds.

This metric is not a lifetime click count. The short window is what distinguishes a burst of frustration from ordinary repeated use.

What's next

You have reused one event model for duration, cohort, retention, classification, funnel, and friction metrics while keeping the user grain explicit.

Continue with Temporal & experiments for historical truth and confidence intervals, or Healthcare episodes for interval-based event analysis.

Last update at: 2026/10/13 08:32:21

On this page

Meet the four user journeys Build the user event model Persist shared event landmarks Time to value Activation rate for the January 1 cohort Day-7 retention Engagement tiers Build a signup-to-subscription funnel Detect rage-click friction What's next