Player retention (D1, D7, D30) and purchase revenue in ClickHouse

You want player retention by install day, and purchase revenue per install cohort, in SQL you control. In Jitsu's default layout every app event lands in one ClickHouse table, events, so retention, sessions and first-week in-app purchase revenue are three queries against that table. Each uses FINAL, because Jitsu deduplicates ClickHouse rows with ReplacingMergeTree and only FINAL makes counts exact.

TL;DR

• D1, D7 and D30 retention is the share of an install cohort that sends any event on the 1st, 7th and 30th day after its first one.
• In Jitsu's default layout every event lands in one events table.
• Backend purchases join to installs through the app's identify calls.
• Every Jitsu Cloud workspace includes ClickHouse with a query console. ClickHouse API access is Enterprise only.
• The queries were run against a table built to that layout, with duplicate rows inserted on purpose.

Who this is for

Data and analytics engineers at app and game companies who want retention and revenue in SQL they control. Every Jitsu Cloud workspace includes a ClickHouse warehouse with a query console, Free plan included.

The problem

A retention chart in a product analytics tool is limited to the events that tool received and its own definition of an active user. In the warehouse, revenue joins and the definition are both SQL, but the SQL has to know the table: which column identifies an install, how duplicates go away, and how a backend purchase, which carries only a user ID, finds the install that made it.

Events to track

The tracking plan CSV has the full property list. For the backend purchase call, see the tracking plan page, with messageId set to store:order_id so a retried call deduplicates to one row.

How events get in

Apps send through Segment's open-source libraries pointed at your Jitsu domain (iOS, Android, React Native). Purchases come from the backend through the HTTP API with a server-to-server write key. The destination is the included ClickHouse, or your own through the ClickHouse destination.

The queries assume Jitsu's default layout:

• One table, events. Keys become snake_case and nested objects flatten with _, so anonymousId is anonymous_id and context.library.name is context_library_name (schema docs).
• Track properties are root columns: properties.revenue is revenue.
• message_id is the primary key. On ClickHouse, Jitsu uses ReplacingMergeTree, which removes duplicate rows in the background some time after insertion, so a plain SELECT can still see them (ClickHouse destination docs).
• Tables are partitioned by month on the connection's timestamp column (ClickHouse destination docs).

The queries filter with anonymous_id != '', which excludes empty strings and NULLs alike.

The function

Jitsu picks a column type from the first value it sees, and if the first purchase has "revenue": 5, the column becomes an integer and a later 4.99 no longer fits (schema docs). This function pins the type, drops sandbox purchases and parks rows with an unreadable revenue value in their own table:

import { JitsuFunction } from "@jitsu/protocols/functions";
import { AnalyticsServerEvent } from "@jitsu/protocols/analytics";

export const config = {
  slug: "purchase-columns",
  name: "Purchase columns",
  description: "Drops sandbox purchases, makes revenue a Float64 column, parks purchases with a bad revenue value.",
};

const MONEY_EVENTS = new Set(["Order Completed", "subscription_started", "subscription_renewed"]);

const purchaseColumns: JitsuFunction<AnalyticsServerEvent> = async (event, { log }) => {
  if (event.type !== "track" || typeof event.event !== "string" || !MONEY_EVENTS.has(event.event)) {
    return event;
  }
  const properties = { ...(event.properties ?? {}) };

  // App Store sandbox and Google Play license-tester purchases are not revenue.
  if (String(properties.environment ?? "").toLowerCase() === "sandbox") {
    return "drop";
  }

  const raw = properties.revenue;
  const revenue = typeof raw === "number" ? raw : typeof raw === "string" && raw.trim() !== "" ? Number(raw) : NaN;
  if (!Number.isFinite(revenue)) {
    log.warn(`${event.event} ${event.messageId}: revenue ${JSON.stringify(raw)} is not a number`);
    return { ...event, JITSU_TABLE_NAME: "purchases_invalid" };
  }

  // Pin the column type. Without it, a first value of 5 creates an integer column and 4.99 no longer fits.
  return { ...event, properties: { ...properties, revenue, __sql_type_revenue: "Float64" } };
};

export default purchaseColumns;

The __sql_type_revenue hint takes effect when Jitsu creates the column. An integer column that already exists stays an integer until you alter it yourself.

Float64 is ClickHouse's type name, so attach this function to a ClickHouse connection only. Sandbox purchases are dropped on this connection, and the function makes no fetch call before the drop. The function is TypeScript in a jitsu-cli project, tested with string and integer revenue, sandbox values and malformed input. It is built and deployed with jitsu-cli; the function editor in the Jitsu UI takes JavaScript, which is what jitsu-cli build outputs.

The queries

Retention by install day

ClickHouse, one row per install day:

-- D1, D7 and D30 retention by install day (UTC). One install = one anonymous_id.
-- Retained on day N = the install sent any event on the Nth calendar day after its first event.
SELECT
    install_day,
    count() AS installs,
    if(install_day <= today() - 1, round(countIf(has(days_active, 1)) / count(), 3), NULL) AS d1,
    if(install_day <= today() - 7, round(countIf(has(days_active, 7)) / count(), 3), NULL) AS d7,
    if(install_day <= today() - 30, round(countIf(has(days_active, 30)) / count(), 3), NULL) AS d30
FROM
(
    SELECT
        anonymous_id,
        min(toDate(timestamp)) AS install_day,
        arrayMap(d -> dateDiff('day', install_day, d), groupUniqArray(toDate(timestamp))) AS days_active
    FROM events FINAL
    WHERE anonymous_id != ''
    GROUP BY anonymous_id
)
WHERE install_day >= today() - 60
GROUP BY install_day
ORDER BY install_day;

d7 and d30 stay NULL until a cohort is old enough to have them. Day boundaries come from toDate() in the server's time zone; pass a second argument, such as toDate(timestamp, 'America/New_York'), to cut them in yours. Backend events in this plan carry no anonymousId, so they never count as app activity.

Sessions

Jitsu leaves session definition to you (sessions docs). This ClickHouse query rebuilds sessions from event timestamps with a 30-minute gap:

-- Sessions rebuilt from event timestamps: 30 minutes without an event from an install ends its session.
-- Ordering by event time, not arrival time, keeps events flushed late from an offline queue in the right session.
WITH
    gaps AS
    (
        SELECT
            anonymous_id,
            timestamp,
            dateDiff('second', lagInFrame(timestamp, 1, timestamp) OVER w, timestamp) AS gap_s
        FROM events FINAL
        WHERE anonymous_id != '' AND timestamp >= today() - 14
        WINDOW w AS (PARTITION BY anonymous_id ORDER BY timestamp ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    ),
    numbered AS
    (
        SELECT
            anonymous_id,
            timestamp,
            sum(gap_s > 1800) OVER (PARTITION BY anonymous_id ORDER BY timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_index
        FROM gaps
    ),
    sessions AS
    (
        SELECT
            anonymous_id,
            session_index,
            min(timestamp) AS started_at,
            dateDiff('second', min(timestamp), max(timestamp)) AS length_s
        FROM numbered
        GROUP BY anonymous_id, session_index
    )
SELECT
    toDate(started_at) AS day,
    uniqExact(anonymous_id) AS active_installs,
    count() AS sessions,
    round(count() / uniqExact(anonymous_id), 2) AS sessions_per_install,
    quantileExact(0.5)(length_s) AS median_session_s
FROM sessions
GROUP BY day
ORDER BY day;

A session with one event has a length of 0 seconds. If a function drops heartbeats, session length runs from the first to the last remaining event.

First-week revenue per install cohort

ClickHouse, one row per install day and currency:

-- Purchase revenue in each install cohort's first 7 days, per currency.
-- Purchases arrive from the backend with user_id only. The app's identify calls link user_id to the install.
WITH
    installs AS
    (
        SELECT anonymous_id, min(toDate(timestamp)) AS install_day
        FROM events FINAL
        WHERE anonymous_id != ''
        GROUP BY anonymous_id
    ),
    cohorts AS
    (
        SELECT install_day, count() AS installs
        FROM installs
        GROUP BY install_day
    ),
    links AS
    (
        SELECT user_id, argMin(anonymous_id, timestamp) AS first_install
        FROM events FINAL
        WHERE type = 'identify' AND user_id != '' AND anonymous_id != ''
        GROUP BY user_id
    ),
    purchases AS
    (
        SELECT user_id, toDate(timestamp) AS day, revenue, currency
        FROM events FINAL
        WHERE type = 'track' AND event IN ('Order Completed', 'subscription_started', 'subscription_renewed')
    )
SELECT
    i.install_day AS install_day,
    p.currency AS currency,
    any(c.installs) AS installs,
    uniqExact(i.anonymous_id) AS paying_installs,
    round(sum(p.revenue), 2) AS revenue_d7,
    round(sum(p.revenue) / any(c.installs), 4) AS revenue_d7_per_install
FROM purchases AS p
INNER JOIN links AS l ON l.user_id = p.user_id
INNER JOIN installs AS i ON i.anonymous_id = l.first_install
INNER JOIN cohorts AS c ON c.install_day = i.install_day
WHERE p.day >= i.install_day AND p.day < i.install_day + 7
GROUP BY install_day, currency
ORDER BY install_day, currency;

The links step takes the first install each user_id identified on, so a player who signs in on a second device stays in the cohort of the first. Revenue stays split by currency; convert it in the backend before sending, or join a rates table here.

ClickHouse is why the alias reads first_install: naming the argMin(...) result anonymous_id makes the WHERE clause read the aggregate instead of the column, and the query fails.

ClickHouse's docs say queries with FINAL run slightly slower than without. For dashboards over long ranges, write daily rollups to their own table.

Destinations

The included ClickHouse comes with a query console on every Jitsu Cloud plan. Jitsu's pricing page lists ClickHouse API access on Enterprise only. BigQuery, Snowflake, Redshift, Postgres and MySQL are live destinations too, but these queries are ClickHouse SQL.

Example implementation (illustrative): a word game's retention table

Illustrative example: not a Jitsu customer. A word game sends app events through Analytics-Swift and Analytics-Kotlin to Jitsu Cloud's included ClickHouse, and purchases from its backend.

Take two hypothetical cohorts: 10 installs on 5 September, of which 5 open the app the next day, 3 on day 7 and 1 on day 30; and 2 installs on 12 October, of which 1 opens the app the next day. Run on 15 October, the retention query returns these two rows among the others. This is hypothetical output, from the test table the query was checked against:

The October cohort shows NULL for D7 and D30 because it is not 7 days old yet, and avg() over those columns skips it.

Where Jitsu does not fit

• Installs, not people. These queries count installs by anonymous_id. A player on two devices counts twice in retention, and only signed-in players can be linked across them.
• Identity stitching tier. Identity stitching rewrites a user's earlier anonymous events with their user ID in the warehouse, up to 30 days back, in six warehouses only: ClickHouse, BigQuery, PostgreSQL, Redshift, Snowflake and MySQL. It is part of Jitsu Cloud Enterprise and of self-hosted Jitsu (identity stitching); the identify join above needs neither.
• Computed metrics. Jitsu computes neither retention nor sessions. They are SQL you own.

Questions people ask

Why do the queries use FINAL? Jitsu writes ClickHouse tables with the ReplacingMergeTree engine keyed on message_id, which removes duplicate rows in the background some time after insertion. FINAL applies that deduplication at query time, so counts and revenue sums are exact.

Does the same SQL work in BigQuery or Snowflake? The approach carries over, while the syntax needs rewriting: FINAL, arrayMap, groupUniqArray, lagInFrame and quantileExact are ClickHouse features.

How are backend purchases tied to app installs? Through the app's identify calls, which carry both the install's anonymous_id and the account's user_id. The revenue query takes the first install each user_id identified on and joins purchases to it.

Does Jitsu compute sessions? No. Jitsu leaves session definition to you. The sessions query here rebuilds them from event timestamps with a 30-minute gap.

Get started with Jitsu

Get started on Jitsu Cloud; every workspace includes ClickHouse, and the Free plan includes 200k active events a month.

Download the tracking plan, no form. To talk through volume, Enterprise API access or self-hosting, book a demo. The overview covers the whole pipeline.