Factcat

The problem

Product analytics tools make your modelling decisions for you. The entity is a user (accounts or groups are a paid add-on), a period is a calendar bucket, and "active", "retained", or "converted" means an event occurred — never a predicate over your own columns:

entity   = a user
period   = a calendar bucket (day / week / month)
retained = did an event

It starts with the simplest chart: weekly active what? Users, accounts, subscriptions, bookings — that is a modelling decision, and Factcat's Uniques is COUNT DISTINCT of whatever you say the entity is.

It compounds in the definitions that actually get negotiated. Here is a retention definition from an actual room, for an actual subscription business:

1. Who is being measured: the subscription
2. Period: 35 days. Billing periods are 30 days with 5 extra days allotted for dunning payment collection
3. How is retention measured: considered churned if we didn't collect payment from dunning in 5 days

Not one line of that fits a product analytics tool. The entity is a subscription, not a user. The period is not a calendar bucket. "Retained" is a payment state machine, not an event occurrence. So it gets built by hand, in SQL, by a consultant, again.

The same definition, in Factcat

from factcat import RetentionSpec, retention_sql

spec = RetentionSpec(
    table="analytics.fct_subscription_payments",
    entity="subscription_id",       # not the user
    entity_time="sub_start",
    event_time="paid_at",
    period_days=35,                 # not a calendar bucket
    n_periods=12,
    retained="status = 'collected' AND within_period_offset <= 5",
)

print(retention_sql(spec, dialect="snowflake"))

retained is arbitrary SQL. It can reference any column in your table plus three derived columns: offset_days, period_index, and within_period_offset. A five-day dunning window is within_period_offset <= 5. That is the whole idea.

Funnels work the same way - ordered steps as arbitrary predicates, at whatever grain you choose, with the completion window measured from the first step:

from factcat import FunnelSpec, funnel_sql

spec = FunnelSpec(
    table="analytics.fct_events",
    entity="account_id",
    event_time="occurred_at",
    steps=("event = 'trial_start'", "event = 'invited_teammate'", "event = 'paid'"),
    step_labels=("trial", "invited a teammate", "converted"),
    within_days=30,
)

The everyday report is a time series. Total, Uniques, and Average (Total / Uniques: events per unique entity). Uniques is COUNT DISTINCT of your entity, not of users. On a numeric column: Sum, Average, Median, and Distinct (mean distinct values per entity). Not min/max.

from factcat import EventsSpec, events_sql

spec = EventsSpec(
    table="analytics.fct_events",
    entity="subscription_id",
    event_time="occurred_at",
    measure="uniques",
    where="event_name = 'paid'",
)
print(events_sql(spec, dialect="bigquery"))

On the Events chart, Break down by fills that expression (a column, or SQL). Add breakdown adds a second or third; top N ranks the combination (not nested top-N per property). As-of, top N, and Show (other) are sugar; they do not replace the tuple. The chart legend joins labels with a middle dot (US · Chrome); the table and CSV keep one column per breakdown.

Split a series by caller expressions. top_n (default 8) folds a long tail into (other); set include_other=False to drop the tail instead. breakdown_at is rows (the value on the event), first, or last (one non-null value per entity). It does not replace the expression.

print(events_sql(EventsSpec(
    table="analytics.fct_events",
    entity="subscription_id",
    event_time="occurred_at",
    measure="uniques",
    where="event_name = 'paid'",
    breakdowns=("country", "browser"),
    breakdown_at="rows",
    top_n=8,
    include_other=True,
), dialect="bigquery"))

Uniques, Distinct, and Median default to approx (exact=False). Break down by picks the top-N series the same way (labels only; measures on those series stay exact). The app Exact toggle (exact=True) turns every sketch off.

Postgres and other generated dialects without a sketch keep COUNT DISTINCT / GROUP BY LIMIT. Exact is COUNT DISTINCT / PERCENTILE_CONT or MEDIAN / LIMIT. Sketches are CPU and memory, not fewer bytes scanned.

The Events chart lists both families: Volume / Unique {entities} / Average per {entity}, then Sum / Average / Median / Distinct of a column (of=). Distinct is mean distinct values per mapped entity, not a global COUNT DISTINCT of the column.

Day/week/month buttons in the app fill a bucket expression (reporting timezone, then week start). There is no period: day|week|month field.

Why the entity matters

The same table, the same predicate, two grains, two different answers:

Neither is wrong. One user held two subscriptions and let one lapse. Which number you want depends on whether you are forecasting revenue or judging engagement - and that is a modelling decision, not something a vendor should have made for you.

What it is not

Factcat does not ship a tracking SDK, does not ingest, and never copies your data. There is no Factcat-hosted warehouse. You bring credentials to your own BigQuery or Snowflake. If you need event collection, keep using whatever you use.

Recommended warehouse shape

The library accepts any relation. A payments fact with status and subscription_id is a valid source. You do not have to have an events table.

The Events app expects one wide events table today (typed columns; unused values null). JSON property bags and one table per event type are not supported yet. Setup shows the matching guide (setup-bigquery.md or setup-snowflake.md). Reporting timezone and whether the timestamp is a UTC instant or civil DATETIME are set on Setup.

For clickstream-style product analytics, this shape works well:

1. Events - one table. At least an event name, a timestamp, and an entity id. Other fields are real columns, not a JSON blob. Different event types may share the table; unused columns are null, which is fine.
2. Identity mapping - source system id plus a type, resolved to one canonical entity id. Do that in the warehouse (dbt). Factcat does not merge anonymous and logged-in ids.
3. Entity dimension - current attributes (country, plan) as columns, joined when you want a breakdown.

Extra grains (booking, account, subscription) are extra id columns on the events table, not "any property". A report that counts bookings simply ignores rows where that id is null.

A PostHog or Amplitude export with JSON properties should be flattened into columns in dbt before you point Factcat at it.

Supported warehouses

SQL generation: DuckDB, Postgres, BigQuery, Snowflake, Databricks, Spark, Trino, Presto, ClickHouse and Redshift.

Portability comes from sqlglot rather than from ten hand-written backends. Exactly one construct needs per-dialect SQL - generating a series of integers for the period grid - and it lives in dialects.py.

Execute adapters push that SQL into the caller's warehouse through its official client. Factcat has no warehouse of its own. BigQuery and Snowflake ship today. The contract is dialect plus run(sql) — identity, auth, and cost knobs stay on the concrete class so Snowflake does not inherit project / location / maximum_bytes_billed. A later warehouse is a module and one line in the registry; see the docstring on factcat.warehouses.

from factcat import RetentionSpec, retention_sql
from factcat.warehouses import connect

sql = retention_sql(spec, dialect="bigquery")
bq = connect("bigquery", project="my-proj", location="EU")
result = bq.run(sql)

Application-default credentials by default (gcloud auth application-default login), or pass a service-account JSON path as credentials. Queries are capped at 10 GiB scanned unless you raise maximum_bytes_billed or pass None for unlimited. project and location are required.

Install

One project: factcat.

pip install factcat              # SQL generation + the local chart
pip install factcat[bigquery]    # run queries in BigQuery
pip install factcat[snowflake]   # run queries in Snowflake
pip install factcat[all]         # every execute adapter we ship

Each extra is named after connect(kind=) and installs that warehouse's official driver. The default has no warehouse SDK. Do not install a second PyPI project per warehouse. Setup guides ship in the app (setup-bigquery.md, setup-snowflake.md).

To hack on the library:

pip install -e "packages/engine[dev,all]"

Run the app

The app is a local web page. It does not ingest your data. It generates SQL and runs it in your warehouse (BigQuery or Snowflake). No Docker. Start it from your warehouse repo (or any project directory); that is where .factcat.json is written.

You need: Python 3.10+. For BigQuery, the Google Cloud SDK, a GCP project with the BigQuery API enabled, and a table you can query (BigQuery Job User on the project, Data Viewer on the dataset). For Snowflake, an account, a user with a key-pair, a compute warehouse, and a table you can query.

python -m venv .venv
# Windows: .venv\Scripts\activate
# macOS/Linux:
source .venv/bin/activate

pip install factcat

cd /path/to/your/warehouse   # mapping is saved here
factcat

Open http://127.0.0.1:8000. First run opens Setup (/setup): pick BigQuery or Snowflake. If that warehouse extra is not installed, Setup shows the command and Install (into this environment; it does not pip on its own). Then that warehouse's connection and catalog, then entity id and timestamp. Fields persist as you pick them (no Save button). Event names load on Events Refresh list. Optional: allow Factcat to create and maintain tables in your warehouse for better performance (BigQuery: project and dataset; Snowflake: database and schema). Setup is a separate control at the bottom of the left rail, not an analysis. Events stays reachable; until a mapping is ready it shows a prompt, Run is disabled, and Setup has a marker. Preferences sits above Setup (wording, thousand/decimal separators, weekday/month display, time of day). Those follow the person in ~/.factcat/preferences.json, not the project file.

BigQuery. After the extra is present: gcloud auth application-default login and gcloud config set project YOUR_GCP_PROJECT. Billing project from ADC, then GOOGLE_CLOUD_PROJECT, then gcloud config get-value project. Dataset → table (lists; greyed until the previous step is set). Lists are cached in the mapping file so returning to Setup does not re-query; each field has Refresh. Location is taken from the dataset (do not guess US). Advanced is only if you use a key file instead of ADC.

Map the event-name column on Setup (STRING). Names are not fetched while mapping. Look back for event names on Setup (90 days; 0 is all time) is the window for the event picker; that job does not use the scan cap. Refresh event names on Events re-runs that window. The chevron next to it looks further back (6 months / 12 months / all time, only steps at least twice the current lookback). The time filter isolates the timestamp column so a date-partitioned table can prune. Optional Factcat-managed tables: allow Factcat to create and maintain tables in your warehouse for better performance. BigQuery is project and dataset; Snowflake is database and schema. First fetch creates fc_event_names if missing; later Refresh reads it. The object is stamped with a fingerprint of the mapped table and event-name column (JSON comment on the relation, plus .factcat.json) so a remapping rebuilds it. A table fallback is a snapshot — Refresh rebuilds it. Lookback and the Refresh chevron are hidden while that dest is set. Catalog jobs do not use the scan cap. On Events, each event series is a card: event name, measure (and Of when the measure is a property), and filters. Filter operators follow the column type (boolean, number, date, time, timestamp, string). String rows can contain / start with / end with several patterns (each value a pill), with a case-sensitive option. On a date or timestamp, a part dropdown is either start of (hour / day / week / month / quarter — month and quarter pickers, not a day calendar) or extract (hour of day, day of week, month of year, year as four digits, …). The mapped timestamp may be filtered on a series (intersects the chart date range). Combine nests another event into that series (OR); Split undoes it. Ungrouped series overlay as separate lines. Break down by is chart-wide unless Break down each series is on, in which case each series has its own split. Refresh event names reloads names for the current lookback (or from fc_event_names if you gave Factcat a write project and dataset). Time grain and date range sit above the chart with Run (warehouse cost is explicit). Grain is day / week / month / hour / day of week / hour of day; sugar fills EventsSpec.bucket, not a period enum. Date range is then in that grain (Last 30 days, Last 8 weeks, Last 6 months). Hour reuses the day list plus Last 24 hours. Day of week and hour of day use the range as a calendar filter — last N days, weeks, months, or quarters, not locked to the chart grain (default last 8 weeks / last 14 days). Include the current period follows the window (this month, this quarter, today) on those last-N filters. Day grain also offers This week / Last week / This month as windows of days. Week and month last-N default to complete periods so the first bar is a full week or month. Include this week/month is opt-in and the current bar is marked incomplete. Custom is specific dates (snapped to the grain) or relative (from 12 to 3 weeks ago; 0 = this period). Sugar on event_time, not a period enum. If a write destination is set, Refresh event names reads fc_event_names (created on first miss as a materialized view, or a table if the source cannot back a view). Lookback does not apply while that cache is in use. Week start and reporting timezone stay on Setup (they change SQL). Thousand/decimal separators, wording (business user / SQL analyst, with uppercase or lowercase SQL and <> or != for the analyst), weekday/month display, day-of-month pad, hour style (12-hour or 24-hour first, then a short list of complete formats) are Preferences; number filters use those separators, and the SQL pane stays warehouse SQL (period decimal, no grouping). Catalog dropdowns are alphabetical. Entity lists string and integer columns; timestamp lists TIMESTAMP / DATETIME.

If the BigQuery table lives in another GCP project (billing in dev, data in prod), set Project that holds the table before loading datasets.

Snowflake. Account identifier, user, and sign-in (key-pair path or browser SSO). Then Role (optional) and compute warehouse from lists, then database → schema → table. Catalog fields stay visible and greyed until the previous step is set, then loaded so the first click has options. An encrypted key's passphrase is SNOWFLAKE_PRIVATE_KEY_PASSPHRASE in the environment, not in .factcat.json.

There is no user_id default. Entity name on Setup is a display label (default User; Other is free text). It does not pick the id column. Volume is row count, Unique Users (or Unique Customers, …) is distinct of the mapped id, Average per User is Volume / that unique count. Entity singular and plural are set on Setup. Day/week/month buckets are dates, not timestamps. Chart type is Auto (bar when there are one or two points, else line), Line, Area, or Bar. Format on the chart sets value format, data labels, axis labels, and grid (major / major+minor). Copy and PNG sit beside it. The title updates only after a successful Run (unless you have edited it). Next to Run, a dry-run bytes scanned estimate (free; not billed) updates when the scan would change. The 10 GB cap is Factcat's maximum_bytes_billed on the job, not a GCP project default. Exact unique counts do not change bytes scanned (same columns), so they do not re-estimate. Result row limit is a Setup crash fuse (default 1,000,000) in SQL: most recent aggregated rows, ORDER BY bucket DESC LIMIT n. A time series never hits it; a slice by a high-cardinality property might. If it does, a warning offers Load more, which doubles the cap for that run rather than removing LIMIT. There is no hard max. Sort is among loaded rows and does not re-query. Job scan cap is set in Setup for BigQuery (default 10 GB on the job). Snowflake has no byte estimate; that chrome is hidden. If a BigQuery estimate exceeds the cap, the report can override it for that run. The filter pane sits beside Chart, Table, and SQL result panes.

Click Run. The mapping is written to .factcat.json in the directory where you started factcat, so the next start is already filled in. Add .factcat.json to that repo’s .gitignore. ADC lives in your user profile; you do not log in to Google every time you start the app.

Stop the server with Ctrl+C. To use a key file instead of ADC, paste the JSON path in the form. The app never copies your events out of your warehouse.

Tests

cd packages/engine && python -m pytest

The suite runs against DuckDB with hand-computed ground truth, and every expected number in tests/test_retention_subscription_dunning.py was worked out on paper from the fixture. It includes mutation guards: disable the retained predicate and February's period 1 reports 100% retention on a payment that failed, which is what the naive "any event retains" model tells you.

Licence

MIT.