Metadata-Version: 2.5
Name: factcat
Version: 0.5.0
Summary: Open-source alternative to Amplitude and Mixpanel that runs in your own data warehouse
Project-URL: Homepage, https://github.com/gordonkjlee/factcat
Project-URL: Source, https://github.com/gordonkjlee/factcat
Author: Gordon Lee
License: MIT
Keywords: analytics,dbt,funnel,retention,sql,warehouse
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Classifier: Topic :: Scientific/Engineering :: Information Analysis
Requires-Python: >=3.10
Requires-Dist: fastapi>=0.110.0
Requires-Dist: jinja2>=3.1.0
Requires-Dist: markdown>=3.6.0
Requires-Dist: sqlglot>=25.0.0
Requires-Dist: tzdata>=2024.1
Requires-Dist: uvicorn>=0.27.0
Provides-Extra: all
Requires-Dist: google-cloud-bigquery>=3.0.0; extra == 'all'
Requires-Dist: snowflake-connector-python[secure-local-storage]>=3.12.0; extra == 'all'
Provides-Extra: bigquery
Requires-Dist: google-cloud-bigquery>=3.0.0; extra == 'bigquery'
Provides-Extra: dev
Requires-Dist: duckdb>=1.0.0; extra == 'dev'
Requires-Dist: httpx>=0.27.0; extra == 'dev'
Requires-Dist: pytest>=8.0.0; extra == 'dev'
Provides-Extra: snowflake
Requires-Dist: snowflake-connector-python[secure-local-storage]>=3.12.0; extra == 'snowflake'
Description-Content-Type: text/markdown

# factcat

An open-source alternative to Amplitude and Mixpanel that runs in your own data
warehouse. Factcat generates SQL and runs it in your BigQuery or Snowflake —
no SDK, no ingestion, nothing hosted.

Product analytics tools make your modelling decisions for you: `entity = a user`,
`period = a calendar bucket`, `retained = did an event`. Real definitions violate all
three:

```python
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,             # a billing cycle plus dunning, not a calendar bucket
    n_periods=12,
    retained="status = 'collected' AND within_period_offset <= 5",
)

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

```python
from factcat import EventsSpec, events_sql

print(events_sql(EventsSpec(
    table="analytics.fct_events",
    entity="subscription_id",
    event_time="occurred_at",
    measure="uniques",
)))
```

Breakdowns are caller SQL plus optional ``top_n`` (default 8) and
``include_other`` (default True). Value semantics ride per column: a plain
string means ``breakdown_at`` (``rows`` / ``first`` / ``last`` /
``carried``); a ``Breakdown`` entry adds ``fill_from``, ``since`` /
``until`` / strict ``before`` bounds, ``backfill``, and
``own_value_first`` (carried: the row's own value outranks a narrowed
``fill_from`` stream). ``carried`` is the last non-null value at or
before each row's instant over the entity's unfiltered history.
None of it replaces the expression.

```python
print(events_sql(EventsSpec(
    table="analytics.fct_events",
    entity="subscription_id",
    event_time="occurred_at",
    measure="uniques",
    breakdowns=("country", "browser"),
    top_n=8,
)))
```

Event measures: `total`, `uniques`, `average` (Total / Uniques). Property
measures (`on="property"`, `of=` a column): `sum`, `average`, `median`,
`distinct` (mean distinct values per entity). Uniques is `COUNT DISTINCT` of
`entity` when `exact=True`; default `exact=False` is approx NDV, approx
median, and approx top-N breakdown labels (BigQuery and Snowflake included).
The same `exact` field turns every sketch off.

`retained` is arbitrary SQL over any column in your table, plus the derived columns
`offset_days`, `period_index` and `within_period_offset`.

Generates SQL and queries in place. No SDK, no ingestion, no copy of your data.

SQL generation supports DuckDB, Postgres, BigQuery, Snowflake, Databricks, Spark, Trino,
Presto, ClickHouse and Redshift. Execute adapters push that SQL into the caller's
warehouse. Factcat has no warehouse of its own. ``pip install factcat`` is the
product (SQL + chart) and includes no warehouse SDK. Run queries with
``pip install factcat[bigquery]`` or ``factcat[snowflake]``. ``factcat[all]`` is
every shipped driver. The adapter contract is ``dialect`` plus ``run(sql)``.

```python
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)
```

Snowflake is the same shape with that warehouse's fields (`account`, `user`,
`warehouse`, `database`, `schema`, `private_key_path`). It has no scan-cap dry-run.

Application-default credentials by default (`gcloud auth application-default login`), or
pass a service-account JSON path as `credentials`. BigQuery queries are capped at 10 GiB
scanned unless you raise `maximum_bytes_billed` or pass `None` for unlimited. `project` and
`location` are required so an EU dataset is not sent to US.

Full documentation: https://github.com/gordonkjlee/factcat
