Temporal & experiments

Report Q1 revenue by customer segment at the time of each transaction, then again with today’s segment — same rows, different answers. Add a knowledge-date filter for late adjustments, and close with a small A/B activation rate (including a wide confidence interval).

This advanced tutorial assumes entity bindings and EXTEND() from E-commerce . Keep two clocks separate: valid time says when a fact applies; recorded time says when the system knew it. Run Data before every section after a notebook restart.

Data

Two customers with SCD2 segments. Acme upgrades smb → enterprise on Feb 15. Beta is still smb in Q1 (becomes mid_market on Apr 1 — only matters for “current segment”). Four Q1 transactions, one of which is a late −1000 adjustment recorded in April. Four experiment users for a toy onboarding test.

FeatureQL

/* SQL */
CREATE SCHEMA IF NOT EXISTS tutorial_te;
--
DROP TABLE IF EXISTS tutorial_te.experiment_events;
--
DROP TABLE IF EXISTS tutorial_te.experiment_assignments;
--
DROP TABLE IF EXISTS tutorial_te.transactions;
--
DROP TABLE IF EXISTS tutorial_te.customers_scd;
--
CREATE TABLE tutorial_te.customers_scd (
  id BIGINT,
  customer_id BIGINT,
  name VARCHAR,
  segment VARCHAR,
  effective_from DATE,
  effective_to DATE
);
--
INSERT INTO tutorial_te.customers_scd VALUES
  (1, 100, 'Acme', 'smb',        DATE '2023-01-01', DATE '2024-02-15'),
  (2, 100, 'Acme', 'enterprise', DATE '2024-02-15', NULL),
  (3, 200, 'Beta', 'smb',        DATE '2023-06-01', DATE '2024-04-01'),
  (4, 200, 'Beta', 'mid_market', DATE '2024-04-01', NULL);
--
CREATE TABLE tutorial_te.transactions (
  id BIGINT,
  customer_id BIGINT,
  amount BIGINT,
  tx_date DATE,
  recorded_at TIMESTAMP
);
--
INSERT INTO tutorial_te.transactions VALUES
  (1, 100,  5000, DATE '2024-01-15', TIMESTAMP '2024-01-15 10:00:00'),
  (2, 100,  8000, DATE '2024-03-01', TIMESTAMP '2024-03-01 09:00:00'),
  (3, 100, -1000, DATE '2024-02-28', TIMESTAMP '2024-04-02 15:00:00'),
  (4, 200,  3000, DATE '2024-02-20', TIMESTAMP '2024-02-20 11:00:00');
--
CREATE TABLE tutorial_te.experiment_assignments (
  user_id BIGINT,
  variant VARCHAR
);
--
INSERT INTO tutorial_te.experiment_assignments VALUES
  (1001, 'control'),
  (1002, 'treatment'),
  (1003, 'control'),
  (1004, 'treatment');
--
CREATE TABLE tutorial_te.experiment_events (
  user_id BIGINT,
  event_type VARCHAR
);
--
INSERT INTO tutorial_te.experiment_events VALUES
  (1001, 'activate'),
  (1002, 'activate'),
  (1004, 'activate');
--
SELECT CAST(COUNT(*) AS INTEGER) AS cnt FROM tutorial_te.transactions;

Result

Count BIGINT
4

Revenue by segment at transaction time

Aggregate each customer’s segment history into ARRAY(ROW(time_update, value)), then SCD_AT_TIME() + EXTEND() so every transaction picks the segment that was live on tx_date.

FeatureQL

WITH
    transactions := ENTITY(),
    tx_id := INPUT(BIGINT#transactions),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT#transactions BIND TO tx_id,
        customer_id BIGINT,
        amount BIGINT,
        tx_date DATE
        FROM SQL(SELECT id AS tx_id, customer_id, amount, tx_date FROM tutorial_te.transactions)
    ),
    tx_customer_id := tx_source[customer_id],
    tx_amount := tx_source[amount],
    tx_date := tx_source[tx_date],
    customers := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    tx_timestamp := INPUT(TIMESTAMP),
    scd_source := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        segment_scd ARRAY(ROW(time_update TIMESTAMP, value VARCHAR))
        FROM SQL(
            SELECT customer_id,
                ARRAY_AGG(ROW(effective_from::TIMESTAMP, segment) ORDER BY effective_from) AS segment_scd
            FROM tutorial_te.customers_scd GROUP BY customer_id
        )
    ),
    seg_scd := scd_source[segment_scd],
    seg_at_time := SCD_AT_TIME(seg_scd, tx_timestamp)[value],
    segment := EXTEND(
        ROW(tx_customer_id AS cid, tx_date::TIMESTAMP AS ts)
        WITH seg_at_time AS seg
        VIA cid,
        ts BIND TO customer_id,
        tx_timestamp
    )[seg]
SELECT
    segment,
    revenue := SUM(tx_amount) GROUP BY segment
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(tutorial_te.transactions)
    )
ORDER BY segment
;

Result

SEGMENT VARCHAR	REVENUE BIGINT
enterprise	7000
smb	8000

Acme’s Jan 5000 stays smb; the Mar 8000 and Feb −1000 are enterprise. Beta’s Feb 3000 is smb.

→ smb 8000, enterprise 7000.

Same Q1, current segment

Drop the as-of lookup; join today’s row (effective_to IS NULL). Acme’s January revenue moves with them to enterprise; Beta is mid_market now.

FeatureQL

WITH
    transactions := ENTITY(),
    tx_id := INPUT(BIGINT#transactions),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT#transactions BIND TO tx_id,
        customer_id BIGINT,
        amount BIGINT,
        tx_date DATE
        FROM SQL(SELECT id AS tx_id, customer_id, amount, tx_date FROM tutorial_te.transactions)
    ),
    tx_customer_id := tx_source[customer_id],
    tx_amount := tx_source[amount],
    customers := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    current_seg_source := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        current_segment VARCHAR
        FROM SQL(
            SELECT customer_id, segment AS current_segment
            FROM tutorial_te.customers_scd
            WHERE effective_to IS NULL
        )
    ),
    current_segment := current_seg_source[current_segment],
    segment := EXTEND(
        ROW(tx_customer_id AS cid)
        WITH current_segment AS seg
        VIA cid BIND TO customer_id
    )[seg]
SELECT
    segment,
    revenue := SUM(tx_amount) GROUP BY segment
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(tutorial_te.transactions)
    )
ORDER BY segment
;

Result

SEGMENT VARCHAR	REVENUE BIGINT
enterprise	12000
mid_market	3000

→ enterprise 12000, mid_market 3000. Same facts; different temporal reference.

Bi-temporal Q1 revenue

Valid time scopes the quarter (tx_date in Q1). Transaction time scopes knowledge (recorded_at ≤ cutoff). The late adjustment is excluded on March 31 and included on April 30.

FeatureQL

WITH
    tx_id := INPUT(BIGINT),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT BIND TO tx_id,
        amount BIGINT,
        tx_date DATE,
        recorded_at TIMESTAMP
        FROM SQL(SELECT id AS tx_id, amount, tx_date, recorded_at FROM tutorial_te.transactions)
    ),
    tx_amount := tx_source[amount],
    tx_date := tx_source[tx_date],
    tx_recorded_at := tx_source[recorded_at],
    is_q1 := tx_date >= DATE '2024-01-01' AND tx_date < DATE '2024-04-01',
    known_as_of := tx_recorded_at <= TIMESTAMP '2024-03-31 23:59:59'
SELECT
    total_revenue_as_of_mar31 := SUM(tx_amount)
    FILTER (WHERE is_q1 AND known_as_of)
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(tutorial_te.transactions)
    )
;

Result

TOTAL_REVENUE_AS_OF_MAR31 BIGINT
16000

FeatureQL

WITH
    tx_id := INPUT(BIGINT),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT BIND TO tx_id,
        amount BIGINT,
        tx_date DATE,
        recorded_at TIMESTAMP
        FROM SQL(SELECT id AS tx_id, amount, tx_date, recorded_at FROM tutorial_te.transactions)
    ),
    tx_amount := tx_source[amount],
    tx_date := tx_source[tx_date],
    tx_recorded_at := tx_source[recorded_at],
    is_q1 := tx_date >= DATE '2024-01-01' AND tx_date < DATE '2024-04-01',
    known_as_of := tx_recorded_at <= TIMESTAMP '2024-04-30 23:59:59'
SELECT
    total_revenue_as_of_apr30 := SUM(tx_amount)
    FILTER (WHERE is_q1 AND known_as_of)
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(tutorial_te.transactions)
    )
;

Result

TOTAL_REVENUE_AS_OF_APR30 BIGINT
15000

16000 as of Mar 31 → 15000 as of Apr 30. The delta is exactly that late −1000.

Experiment: activation rate + CI

Control vs treatment activation for a four-user toy experiment. Normal-approximation 95% CI — with n = 2 per arm the control interval spans nearly [0, 1].

FeatureQL

WITH
    exp_users := ENTITY(),
    user_id := INPUT(BIGINT#exp_users),
    assign_source := EXTERNAL_COLUMNS(
        user_id BIGINT#exp_users BIND TO user_id,
        variant VARCHAR
        FROM TABLE(tutorial_te.experiment_assignments)
    ),
    event_source := EXTERNAL_COLUMNS(
        user_id BIGINT#exp_users BIND TO user_id,
        event_type VARCHAR
        FROM TABLE(tutorial_te.experiment_events)
    ),
    variant := assign_source[variant],
    did_activate := user_id.RELATED(
        COUNT_IF(event_source[event_type] = 'activate')
        GROUP BY event_source[user_id]
    )
    > 0,
    assigned := COUNT(1) GROUP BY variant,
    activated := COUNT_IF(did_activate) GROUP BY variant,
    p := activated::DOUBLE / assigned::DOUBLE,
    se := SQRT(p * (1e0 - p) / assigned::DOUBLE),
    activation_rate := ROUND(p, 4),
    ci_low := ROUND(GREATEST(0e0, p - 1.96e0 * se), 4),
    ci_high := ROUND(LEAST(1e0, p + 1.96e0 * se), 4)
SELECT
    variant,
    assigned,
    activated,
    activation_rate,
    ci_low,
    ci_high
FOR
    user_id := BIND_COLUMNS(
        user_id
        FROM TABLE(tutorial_te.experiment_assignments)
    )
ORDER BY variant
;

Result

VARIANT VARCHAR	ASSIGNED BIGINT	ACTIVATED BIGINT	ACTIVATION_RATE DECIMAL	CI_LOW DECIMAL	CI_HIGH DECIMAL
control	2	1	0.5	0	1
treatment	2	2	1	1	1

Control 50% (wide CI). Treatment 100% (degenerate CI at 1). Point estimates look different; the sample is too small to trust.

What's next

Analytics overview — concept map for this series
Graphs & referrals — RECURSE(), subtree revenue, components
Product analytics — funnels and retention on event logs
SaaS metrics — as-of MRR without SCD2
Financial consolidation — rule-driven rollups

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

On this page

Data Revenue by segment at transaction time Same Q1, current segment Bi-temporal Q1 revenue Experiment: activation rate + CI What's next