Our engine never sees your warehouse password
datahashi

BlogModeling & metrics

SaaS activation and retention without 14 CTEs

How product managers analyze SaaS user activation and weekly retention across entity join graphs in a thin semantic layer.

The datahashi team8 min read

The problem

Product managers tracking user onboarding funnels need to know whether enterprise accounts activate faster than self-serve teams. To answer this question in raw SQL, an analyst writes nested subqueries joining telemetry events to user identities and workspace subscriptions.

Before long, a simple cohort retention question turns into a monstrous query spanning fourteen common table expressions. Each CTE reconstructs windowed distinct counts, division-by-zero guards, and ISO week calendar truncations from scratch. A single user belonging to multiple workspaces inflates event totals through accidental cartesian multiplication. When product marketing runs a slightly different SQL notebook, their activation cohort numbers disagree with product analytics. Growth initiatives stall while engineers audit competing queries line by line.

The root cause is the lack of a formal graph definition. Without central entity relationships, every analyst must manually reconstruct the path between workspaces, users, and event streams. One notebook joins events directly to workspaces, while another routes through users. These small discrepancies produce conflicting cohort retention curves across executive presentations.

You need a central semantic layer that compiles clean Join Graph paths and calendar time grains directly from declarative YAML models. With datahashi, product managers query certified activation rates across workspace tiers without authoring recursive SQL statements.

+--------------------------------------------------------------+
| Product Team asks: "Do Enterprise workspaces activate faster?"|
+--------------------------------------------------------------+
                               |
               Fourteen Nested SQL CTEs ✗
                               |
     Accidental cartesian joins inflate distinct user totals!
           Marketing and product numbers diverge ✗
                               |
            Central Join Graph Semantic Model ✓
                               |
        Clean entity traversal: Workspace -> User -> Events
            Accurate, reproducible weekly cohort rates ✓

The data

The product analytics schema models three physical warehouse tables. The workspaces table tracks subscription account tiers and creation dates. The users table maps team members to their parent workspaces. The product_events table streams telemetry actions with UTC timestamps.

CREATE TABLE workspaces (
    workspace_id VARCHAR PRIMARY KEY,
    plan_tier VARCHAR NOT NULL,
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE users (
    user_id VARCHAR PRIMARY KEY,
    workspace_id VARCHAR NOT NULL REFERENCES workspaces(workspace_id),
    role VARCHAR NOT NULL,
    signup_at TIMESTAMP NOT NULL
);

CREATE TABLE product_events (
    event_id VARCHAR PRIMARY KEY,
    user_id VARCHAR NOT NULL REFERENCES users(user_id),
    event_name VARCHAR NOT NULL,
    occurred_at TIMESTAMP NOT NULL
);

INSERT INTO workspaces VALUES
    ('ws_1', 'pro', '2026-09-01 10:00:00'),
    ('ws_2', 'enterprise', '2026-09-05 14:00:00'),
    ('ws_3', 'starter', '2026-09-15 09:30:00'),
    ('ws_4', 'pro', '2026-09-20 16:00:00');

INSERT INTO users VALUES
    ('u_1', 'ws_1', 'admin', '2026-09-01 10:05:00'),
    ('u_2', 'ws_1', 'member', '2026-09-02 11:00:00'),
    ('u_3', 'ws_2', 'admin', '2026-09-05 14:15:00'),
    ('u_4', 'ws_2', 'member', '2026-09-06 08:30:00'),
    ('u_5', 'ws_3', 'admin', '2026-09-15 09:45:00'),
    ('u_6', 'ws_4', 'admin', '2026-09-20 16:10:00');

INSERT INTO product_events VALUES
    ('ev_1', 'u_1', 'activated', '2026-09-01 10:30:00'),
    ('ev_2', 'u_1', 'invite_sent', '2026-09-02 10:00:00'),
    ('ev_3', 'u_2', 'dashboard_viewed', '2026-09-03 14:00:00'),
    ('ev_4', 'u_3', 'activated', '2026-09-05 15:00:00'),
    ('ev_5', 'u_3', 'integration_connected', '2026-09-06 09:00:00'),
    ('ev_6', 'u_4', 'dashboard_viewed', '2026-09-06 11:00:00'),
    ('ev_7', 'u_5', 'dashboard_viewed', '2026-09-15 10:00:00'),
    ('ev_8', 'u_6', 'dashboard_viewed', '2026-09-20 16:30:00');

The relationship graph links event telemetry to users and workspaces:

+----------------+            +------------+            +----------------+
| product_events |            |   users    |            |   workspaces   |
+----------------+            +------------+            +----------------+
| event_id (PK)  |            | user_id PK |            | workspace_id PK|
| user_id (FK) --+----------->| workspc_id +----------->| plan_tier      |
| event_name     |  many_to_1 | role       |  many_to_1 | created_at     |
| occurred_at    |            | signup_at  |            +----------------+
+----------------+            +------------+

The model

The semantic model captures three entities, two many-to-one relationships, and four metrics.

The ProductEvent entity defines measures for event counts and distinct active users. The events_per_active_user metric divides event_count by active_user_count post-aggregation, ensuring valid ratio calculations across arbitrary dimensional slices.

source: warehouse_main

entities:
  - name: Workspace
    table: workspaces
    primary_key: workspace_id
    dimensions:
      - name: workspace_id
        column: workspace_id
        type: string
      - name: plan_tier
        column: plan_tier
        type: string
      - name: created_at
        column: created_at
        type: timestamp
        grains: [day, week, month]
    measures:
      - name: workspace_count
        expr: workspace_id
        agg: count

  - name: User
    table: users
    primary_key: user_id
    dimensions:
      - name: user_id
        column: user_id
        type: string
      - name: workspace_id
        column: workspace_id
        type: string
      - name: role
        column: role
        type: string
      - name: signup_at
        column: signup_at
        type: timestamp
        grains: [day, week, month]
    measures:
      - name: user_count
        expr: user_id
        agg: count

  - name: ProductEvent
    table: product_events
    primary_key: event_id
    dimensions:
      - name: event_id
        column: event_id
        type: string
      - name: user_id
        column: user_id
        type: string
      - name: event_name
        column: event_name
        type: string
      - name: occurred_at
        column: occurred_at
        type: timestamp
        grains: [day, week, month]
    measures:
      - name: event_count
        expr: event_id
        agg: count
      - name: active_user_count
        expr: user_id
        agg: count_distinct

relationships:
  - name: event_user
    from: ProductEvent
    to: User
    cardinality: many_to_one
    keys:
      - [user_id, user_id]

  - name: user_workspace
    from: User
    to: Workspace
    cardinality: many_to_one
    keys:
      - [workspace_id, workspace_id]

metrics:
  - name: active_users
    expr: active_user_count
    description: "Distinct users triggering product events"
  - name: events_per_active_user
    expr: event_count / active_user_count
    description: "Average engagement events per active user"
  - name: total_workspaces
    expr: workspace_count
    description: "Count of registered workspaces"

Notice how events_per_active_user is defined. Instead of forcing authors to write complex window logic in raw SQL, the model performs post-aggregation division. The engine automatically handles NULLIF guards to prevent division-by-zero crashes when no active users are recorded.

The relationship configuration informs the Join Graph resolver how to connect entities. When a query pairs metrics from ProductEvent with dimensions from Workspace, the compiler identifies the shortest path through User. It writes inner joins or left joins matching the configured cardinalities. You never write manual join conditions across intermediate tables.

Consult the model specification for complete configuration syntax.

The queries

Clients submit structured JSON payloads adhering to the query specification. The engine traverses the Join Graph from ProductEvent through User to Workspace automatically. Where a request sets include_sql, the engine returns compiled SQL only if it was started with ENGINE_ALLOW_SQL=true. MCP never returns SQL.

Query 1: Hero engagement and active users by plan tier and week

The hero query calculates engagement broken down by plan_tier and weekly time grains:

{
  "metrics": ["active_users", "events_per_active_user"],
  "dimensions": [
    {
      "name": "occurred_at",
      "entity": "ProductEvent",
      "grain": "week"
    },
    {
      "name": "plan_tier",
      "entity": "Workspace"
    }
  ],
  "order": [
    { "ref": "occurred_at", "desc": false },
    { "ref": "plan_tier", "desc": false }
  ],
  "include_sql": true
}

The gateway returns the compiled cohort metrics:

occurred_at plan_tier active_users events_per_active_user
2026-08-31T00:00:00Z enterprise 1 2
2026-08-31T00:00:00Z pro 2 1.5
2026-09-07T00:00:00Z enterprise 1 1
2026-09-14T00:00:00Z pro 1 1
2026-09-21T00:00:00Z enterprise 1 1

Here is the compiled SQL generated by the engine:

SELECT
  q0."occurred_at" AS "occurred_at",
  q0."plan_tier" AS "plan_tier",
  CAST(q0."active_user_count" AS DOUBLE) AS "active_users",
  (CAST(q0."event_count" AS DOUBLE) / NULLIF(CAST(q0."active_user_count" AS DOUBLE), 0)) AS "events_per_active_user"
FROM (
  SELECT
    date_trunc('week', "ProductEvent"."occurred_at") AS "occurred_at",
    "Workspace"."plan_tier" AS "plan_tier",
    COUNT(DISTINCT "ProductEvent"."user_id") AS "active_user_count",
    COUNT("ProductEvent"."event_id") AS "event_count"
  FROM "product_events" AS "ProductEvent"
  JOIN "users" AS "User"
    ON "ProductEvent"."user_id" = "User"."user_id"
  JOIN "workspaces" AS "Workspace"
    ON "User"."workspace_id" = "Workspace"."workspace_id"
  GROUP BY
    date_trunc('week', "ProductEvent"."occurred_at"),
    "Workspace"."plan_tier"
) AS q0
ORDER BY "occurred_at", "plan_tier"

The compiler traverses from product_events through users to workspaces along foreign keys. It evaluates conditional distinct counts inside a single grouped subquery.

Query 2: User engagement ranking by event type

Product managers also inspect feature adoption by ranking actions by distinct active users:

{
  "metrics": ["active_users"],
  "dimensions": [
    {
      "name": "event_name",
      "entity": "ProductEvent"
    }
  ],
  "order": [
    { "ref": "active_users", "desc": true }
  ],
  "include_sql": true
}

The result table ranks engagement across product lifecycle actions:

event_name active_users
dashboard_viewed 4
activated 2
invite_sent 1
integration_connected 1

Query 3: Refusal on uncertified retention metric

When an ad-hoc dashboard or bot requests an undeclared metric like churn_rate, the semantic engine halts execution immediately:

{
  "metrics": ["churn_rate"]
}

The engine returns HTTP 404:

{
  "error": "model: resolve: metric \"churn_rate\" is not exposed by the named view",
  "code": "not_found",
  "reason": "not_in_view",
  "ref": "churn_rate"
}

The query fails before hitting the database. Uncertified metrics cannot introduce conflicting definitions into executive reviews.

What to notice

  • Automated Join Graph navigation. The compiler traversed from ProductEvent to Workspace through User without duplicate joins or ambiguous column references. Authors define relationships once in declarative YAML; the engine handles foreign key resolution on demand.
  • Post-aggregation ratio safety. The compiled formula applies NULLIF to prevent division-by-zero crashes when no active users are found in a cohort. In raw SQL, calculating ratios without guards frequently crashes production dashboard views.
  • Mathematical distinct count integrity. The metric COUNT(DISTINCT user_id) calculates distinct users per group without inflating counts across joined rows. The engine preserves correct cardinality across complex multi-table graphs.
  • Dialect-aware time truncation. Time grains apply database-specific date truncation functions, ensuring calendar weeks group accurately across warehouse backends.
  • Governance over ad-hoc metric drift. Demanding undeclared metrics like churn_rate returns a clean refusal rather than an erroneous guess. Product managers and marketing leads align on the exact same metric definitions.

For foundational modeling techniques, see the guide on building your first metric in DuckDB and the post on fan-out safe metrics.

Limits

  • Empty time buckets. Set fill_gaps to emit a row for every empty calendar bucket. The query needs exactly one grouped calendar-grain dimension, no limit or offset, and an order on that dimension only. Funnels that are not a single time series still need a date spine upstream.
  • Multi-state transition cohorts. Complex behavioral retention funnels that require state machines across arbitrary moving windows remain transformed in the warehouse.
  • Distinct count rollup restrictions. Exact distinct counts cannot freely re-aggregate across coarse rollups without HyperLogLog sketches.
  • Database index dependencies. The engine pushes joins down to the warehouse. High-volume telemetry tables must maintain proper foreign key indexes to prevent slow scans.
  • Upstream sessionization responsibility. The semantic layer aggregates existing event logs. Grouping raw clickstream hits into session objects or device graphs remains an upstream warehouse transformation responsibility.

Product notes

An occasional email when we ship something or write something worth reading.