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

A retention chart in Mixpanel or Amplitude shows who came back, while the validated purchases that say what each install cohort paid sit in your backend. In the warehouse you write the definition of an active player and join revenue to it yourself.

Jitsu Cloud includes a ClickHouse warehouse. App events and backend purchases share one table there, and the revenue query below joins a purchase that arrives with only a user_id to its install through the app's identify calls. Each query below pastes straight into its console.

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.
• Sessions come from event timestamps, so you choose the inactivity gap.
• First-week revenue per install cohort comes from the same table, split by currency, with paying installs per cohort.

What the queries read

The tracking plan page has the app setup and the backend purchase call, its CSV lists every property, and the mobile apps and games overview shows the rest of the pipeline.

Every query assumes 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 (ClickHouse destination docs).

Retention by install day

The query returns 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;

installs counts every install in the cohort, paid or organic, so a day with a store feature or a paid campaign shows up as a spike there before it shows in D1.

d7 and d30 stay NULL until a cohort is old enough to have them, and avg() over those columns skips the young cohorts. For 10,000 installs on 5 September and 2,000 on 12 October, the query run on 15 October returns these two rows among the others:

The inner query groups every install in events, so its cost grows with the table rather than with the number of cohorts; on a large table, run it from a daily rollup table of installs and active days instead, since FINAL merges duplicate rows each time the query runs (ClickHouse docs on FINAL). Backend events in this plan carry no anonymousId, so they never count as app activity. An install is one anonymous_id, so a player on two devices, such as a phone and a tablet, counts as two installs here. To count players instead, group by the user_id that the revenue query's links step resolves, or turn on identity stitching, covered below. Filtering install_day to the weeks before and after a release compares cohorts on either side of it, and D1 is the column that moves first after an onboarding change.

Sessions

This ClickHouse query rebuilds sessions from event timestamps with a 30-minute gap, from the same events table, so no extra session event is needed. Jitsu can also assign sessions in a function as events arrive (sessions docs):

-- 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;

active_installs counts installs, as in the retention query. Read by day, sessions_per_install and median_session_s show whether a release changed the number and length of sessions per install, which a retention curve alone does not. If your game sits open between turns, raise the 1800-second gap in the query, so a long pause stays inside one session. Sessions with a single event have a length of 0 seconds, which pulls the median down; add WHERE length_s > 0 to the final query to leave them out. Behind a heartbeat filter like the one on the noise filtering page, sessions cover only the events that remain.

First-week revenue per install cohort

Keeping revenue a number

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, so deploy the function before the first purchase reaches a new warehouse. 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, marked environment: sandbox by the backend call, are dropped on that connection only, so another connection that should skip them needs its own check.

The query

Each currency gets its own row per install day:

-- 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.

A subscription bought in the first week counts alongside one-off purchases, since subscription_started and subscription_renewed rows join the same sum. The query sums whatever the backend sends in revenue, so send the net amount if finance reports revenue net of store fees, and convert currencies in the backend or join a rates table here.

revenue_d7_per_install divides by every install in the cohort, payers or not, so cohorts of different sizes compare on one scale, and paying_installs divided by installs gives the payer rate in that currency. Cohorts with no purchase in their first week are absent from the result, because the joins are inner joins, so read their install counts from the retention query instead.

This join works on any plan. Identity stitching does the linking in the warehouse instead: when a player identifies, it rewrites their earlier anonymous events with the user ID in the warehouse, up to 30 days back, in ClickHouse, BigQuery, PostgreSQL, Redshift, Snowflake or MySQL. It is part of the Enterprise plan on Jitsu Cloud and has no plan restriction self-hosted (identity stitching).

Questions people ask

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.

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 duplicate rows are removed before counting.

Do the queries need the Enterprise plan? No. They run in the query console of the included ClickHouse on every plan, Free included. For a BI tool on any plan, send events to your own ClickHouse through the ClickHouse destination, where the queries run unchanged; API access to the included ClickHouse is on the Enterprise plan.

Get started with Jitsu

Open the query console in a Jitsu Cloud workspace and paste the retention query once a week of app events has arrived.