PostHog is excellent at what it does and silent outside its own boundary: no retroactive account rollup, no join to billing or CRM, and an events API with a backfill trap most teams find the hard way. Here is where PostHog's own reporting stops, how a three-layer pipeline fixes it, and why starting from a free local template beats assembling a stack.
Arsalan Noorafkan
Developer Advocate
Quick answer: PostHog's product analytics UI, Group Analytics, and HogQL each answer a slice of the account question and stop. They stop at PostHog's own data, at an account entity that only exists if you instrumented $groups before the fact, and at person profiles that carry no history of what they used to be. A PostHog analytics pipeline fixes all three by landing PostHog's raw data in your warehouse, resolving identity and rolling accounts up in SQL you can read, and scoring engagement and product-qualified accounts against definitions your team owns. You do not need to buy anything to start: bruin init posthog-bigquery gives you 13 assets, four report tables, and a dashboard that run locally for free.
The usual trigger is a sales or CS conversation. Someone asks which accounts are ready for an upsell, and the honest answer is "check three tools and guess" - PostHog for usage, the CRM for plan and revenue, a spreadsheet for the two joined together by hand, three days out of date by the time anyone reads it.
That is not a PostHog problem. PostHog is doing exactly what a product analytics tool should do. It is a scope problem, and it shows up in every company whose product usage data and revenue data live in two different systems that were never meant to meet.
Worth being precise, because PostHog's tooling is genuinely good within its boundary.
| Surface | What it does well | Where it stops |
|---|---|---|
| Trends, funnels, retention, paths | Fast ad hoc exploration with no setup, over any event you send | PostHog data only, capped history on lower tiers, no join to revenue or CRM |
| Group Analytics | Rolls events up to a company, team, or workspace | Requires $groups sent at capture time; cannot be applied retroactively; a paid add-on |
| HogQL | Read-only SQL over your own project data, inside the PostHog UI | Still bounded by what PostHog holds; no external BI tool can point at it directly |
| Batch export / Data Pipeline | A managed way to land the raw event stream in BigQuery, Snowflake, or S3 | Gives you raw tables; identity resolution, account rollup, scoring, and reports are still yours to build |
| Experiments | Built-in significance testing per flag variant | The same exposure data, but never crossed with plan, industry, or MRR |
Every one of these is excellent inside PostHog's boundary and silent outside it. Batch export is the closest thing to a solution, and even it hands you raw tables and wishes you luck - which is the honest place to start the real conversation.
Six specific limits, in the order teams usually hit them.
The questions that actually decide a renewal or an upsell are almost never about product usage alone:
Every one needs PostHog joined to something else - billing, the CRM, the support desk. No PostHog surface can do that join, because the other side of it is not in PostHog.
This is the limit that surprises people, because it sounds like a feature gap rather than an architectural one. PostHog is person-centric. Rolling events up to a company requires sending a $groups property on every event at the moment it happens - Group Analytics cannot reach back into events you already collected and retroactively assign them to an account. If you did not instrument it a year ago, you do not have a year of account-level history today, and you never will.
A pipeline sidesteps this because it derives the account after the fact, in SQL, from ordinary person properties like company and plan that your identify calls already send. It is a weaker signal than true event-time grouping in one respect - it cannot see which specific session belonged to which specific account seat if a person switches companies - but it works on data you already have, which the paid add-on fundamentally cannot.
PostHog's persons table is a current-state snapshot. When someone's plan changes from free to pro, the old value is gone - it never existed as a separate historical fact, the same way a spreadsheet cell simply becomes its new value. Query PostHog today and you get today's plan, correctly, with no trace of what it was three months ago.
Which means: a report built directly on PostHog's person data cannot say what plan an account was on when it churned, or trend engagement against a plan history that does not exist. The event stream itself is different - PostHog does keep events, and they are immutable once captured - but any account attribute layered on top of the event data is only ever as accurate as the account's current profile, applied retroactively to every historical month. Every pipeline built on person properties has to say this plainly rather than let a chart imply otherwise.
This one is purely technical, and it is worth its own warning because it fails silently. PostHog's /events REST API does not return a complete history for a multi-day window, and gives no indication that anything is missing.
Measured against one project, --full-refresh each time, three repeats per window, with deterministic results:
| Window | Events in PostHog | Events actually loaded |
|---|---|---|
| 3 days | 1,679 | 545 (one day's worth) |
| 7 days | 3,090 | 2,619 (all seven days, but 15% short) |
| 31 days | 16,925 | 673 (one day's worth) |
| 95 days | 42,354 | 1,551 (four days) |
| 1 day | exact | exact, across 93 days tested |
The loss tracks the volume of events in the window, not its width - a three-day window returned a single day while a seven-day window spanned all seven and still came up short. There is no safe wide window to reach for, because the seven-day case looks complete and is not. The only reliable approach is a day-by-day backfill loop, and any pipeline or script that skips this will produce a confidently wrong history with no error in the logs.
An engagement score or a PQL threshold defined in a dashboard's query builder has no diff, no reviewer, and no blame. When the score changes, "did usage actually change or did someone edit the weights" becomes an archaeology project.
The same score as a SQL file with its weights spelled out in a comment has an author, a pull request, and a reason. The formula is the same either way. The difference is entirely in whether you can defend it in front of the product team six months later.
PostHog's own pricing tracks events ingested. Your reporting needs do not grow at that rate - an account processing ten times the events does not need ten times the reports, it needs the same handful of reports computed correctly at whatever scale the product reaches. That mismatch is fine when the reports are worth it. It stings when you are still finding out whether they are.
Three layers, and the middle one does more work here than in most warehousing setups, because PostHog's identity model is more involved than most sources.
Raw. Events, persons, and feature flags, loaded as PostHog returned them. Events are immutable and append; persons and feature flags are mutable and merge on their current state. This layer exists so a wrong number downstream can be traced back to exactly what PostHog sent. Nobody reports off it.
Staging. This is where the real modeling happens. Events are deduplicated against an append-only raw table. Every distinct ID PostHog has ever merged into a person is resolved down to a single lookup, so no downstream join can accidentally fan an event out across two people. Persons with a company property are rolled up into accounts - plan, seats, MRR, industry - the entity PostHog itself has no place to put unless you paid for it and set it up in advance. Sessions are summarized from PostHog's own session boundaries. Feature flag evaluations are separated into exposure and actual adoption, because a boolean flag answers false for everyone held out of a rollout, and counting that as exposure would read as adoption by people who never saw the feature.
Reports. Business-ready tables: monthly account engagement scored against licensed seats, product-qualified accounts with expansion and churn-risk flags, feature adoption crossed with plan and industry, and retention cohorts sliced by acquisition source. One row per grain, documented, tested, and stable enough for a dashboard or an AI agent to sit on top.
The layering is what lets a product team change what counts as a "product action" without touching the identity resolution, and lets a data engineer fix a backfill without anyone's score definition moving underneath them.
| Approach | Good fit | Trade-off |
|---|---|---|
| Managed ELT (Fivetran, Airbyte, Stitch, Hevo) | You want the PostHog sync operated by someone else | Replication only - identity resolution, scoring, and orchestration live elsewhere |
| PostHog batch export / Data Pipeline | You already use BigQuery, Snowflake, or S3 and want the lowest-effort load | Raw tables only, and it is a PostHog add-on rather than a general ingestion path |
| dbt + a connector + an orchestrator | You already run this stack and have the team for it | Three tools, three configs, three failure modes, and ingestion is somebody else's problem |
| Product-led growth SaaS (Amplitude, Pendo, and similar) | You want PQL-style scoring tomorrow with no engineering | Their definitions, their surface, and the data does not land somewhere you can join it to billing |
| Custom PostHog API scripts | Genuinely unusual extraction logic | You own pagination, the day-by-day backfill discipline, retries, and schema drift forever |
| Bruin | You want ingestion, SQL models, quality checks, lineage, and scheduling as files in one repo | Code-first, so it suits teams comfortable with Git and SQL |
If you already have Fivetran and dbt working and your team is happy, the marginal gain from switching is small. The argument is for the team that does not have that stack yet and is deciding what to assemble - one CLI that does ingestion, transformation, checks, and lineage in a single project means one thing to learn, one thing to run in CI, and one place a coding agent has to look to understand the pipeline. ingestr and the Bruin CLI are both open source, and running the whole thing locally costs nothing, so you can find out whether the reports are worth having before you pay anyone.
The failure mode for a first PostHog pipeline is almost never "not sophisticated enough." It is "took a month, mostly on the backfill, and nobody trusts the score."
That happens because the first version tries to model every event PostHog has ever captured, tune five scoring weights nobody has checked against real data yet, and ship a semantic layer before anyone outside the data team has looked at a single number. Meanwhile the CS team still cannot tell which of their accounts to call this week.
The version that works looks like this:
bruin run on your laptop against your own warehouse.APPROX_QUANTILES query against the scores you actually produced, tells you what to tune next - and it is never what you guessed going in.bruin init posthog-bigquery writes 13 assets across the three layers, plus a dashboard.
posthog_raw - three ingestr assets: events, persons, feature flags. Events append and are immutable; persons and feature flags merge on their current state.
posthog_stage - six models. Deduplicated events, identity resolution down to one person per distinct ID, an account rollup derived from person properties, sessions, and feature-flag exposure separated from actual adoption.
posthog_reports - four tables:
| Report | Answers |
|---|---|
account_engagement_monthly | Monthly engagement scored against licensed seats, with month-over-month trend |
product_qualified_accounts | PQL scoring over a trailing window, with expansion and churn-risk signals |
feature_adoption_by_segment | Flag adoption and post-exposure conversion, crossed with plan and industry |
weekly_retention_cohorts | Retention by signup week, source, and plan, guarded against incomplete weeks |
dashboards/posthog-product-analytics.yml - a Dashboards as Code file you serve on localhost:8321. Headline KPIs, a PQL leaderboard with conditional formatting, an expansion-and-churn-risk table, feature adoption, and retention.
Two things the template does that are worth stealing even if you build your own. First, every scoring component is clamped to a 0-1 range before it is weighted, so a two-person startup and a four-hundred-seat enterprise land on a comparable scale instead of the larger account automatically winning on raw counts. Second, the seat-activation denominator is a named, documented choice - licensed_seats bounded by who PostHog has actually seen, rather than by the raw seat count on the contract - because scoring activation against a number nobody has identified into yet quietly ranks your biggest, least-rolled-out accounts as your worst.
The step-by-step guide walks the whole thing, including the backfill loop, in detail.
The template targets BigQuery, but only three things are actually BigQuery-specific: the asset type (bq.sql), the ingestr destination in pipeline.yml, and a handful of dialect functions - JSON_VALUE, SAFE_DIVIDE, SAFE_CAST, APPROX_TOP_COUNT, ARRAY_AGG.
The layering, the identity resolution, the account rollup, the scoring formulas, and the checks are all portable SQL thinking. ingestr already supports Snowflake, Databricks, ClickHouse, Redshift, Postgres, DuckDB, and more as destinations, so the raw layer is a one-line change.
Which makes the port a good agent task. Hand your coding agent the repo and something like:
Refactor this pipeline from BigQuery to Snowflake. Keep the three-layer structure, asset names, column names, materialization strategies, dependencies, checks, and scoring formulas exactly as they are - only the dialect and platform config change. Change
bq.sqltosf.sql, the ingestr destination tosnowflake, and translateJSON_VALUE,SAFE_DIVIDE,SAFE_CAST,APPROX_TOP_COUNT, andARRAY_AGGto their Snowflake equivalents. Work one directory at a time and runbruin validate --fastafter each.
That works because the agent has the whole thing in front of it as files. A scoring model defined in a vendor's dashboard UI cannot be refactored by anyone, human or otherwise.
Local is not a permanent home. But it is the right first home, and moving off it later is a deployment change rather than a rewrite - same repo, same bruin run, credentials injected from wherever you keep secrets.
| Option | Good when | You operate |
|---|---|---|
| GitHub Actions | You already use GitHub and a daily cron is enough | Secrets, the workflow, and its logs |
| Self-hosted (cron, Airflow, ECS, Cloud Run) | You need a VPC, private connectivity, or an existing scheduler | The host, the scheduler, and alerting |
| Bruin Cloud | You want scheduling, lineage, run history, alerting, and SSO without building them | Nothing |
Pick based on what you want to own, not on what sounds most serious.
A pipeline that lies confidently is worse than a dashboard that admits its limits.
Engagement and PQL scores are configured, not measured. They encode assumptions about what "using the product well" looks like at your company, set through the same handful of variables you tuned before trusting them. A score of 72 is not an objective fact about an account; it is what your weights say about that account's behavior.
Account attributes are today's values, applied to every historical month. plan, seats, and mrr come from a current-state person snapshot. A report that shows last quarter's engagement next to today's plan is showing you today's plan, not the plan that was actually in force then.
A backfilled history is only as complete as the day-by-day discipline that built it. A wide-window load that "succeeded" is the one number in this whole pipeline that is worth re-checking before anyone trusts a trend line.
A PostHog analytics pipeline loads PostHog's raw data - events, persons, and feature flags - into a data warehouse, resolves PostHog's mutable identity graph down to one person per distinct ID, rolls persons up into accounts, and publishes report tables such as monthly account engagement, product-qualified accounts, feature adoption by segment, and retention cohorts.
Group Analytics requires a $groups property sent at capture time and cannot be applied retroactively to events you already collected, and it is a paid add-on. A pipeline derives the account rollup in SQL from ordinary person properties instead, which works on data you already have.
No. PostHog's public API exposes the same data, and open-source tools such as ingestr can load it into BigQuery, Snowflake, ClickHouse, Databricks, Redshift, Postgres, or DuckDB. Batch export is a reasonable managed load; either way, the modeling and reports are yours to build.
Resolve events to people through their merged distinct IDs, then to accounts through a company property, and score a trailing window against the prior one on breadth, depth, seat activation, power-user share, and momentum - each clamped to 0-1 before weighting so account size does not dominate the ranking.
PostHog's events API silently returns partial data over any window wider than a day, with no error. The loss tracks event volume in the window rather than its width, so there is no safe wide window - only a day-by-day loop is reliably exact.
Yes. Only the asset type, the ingestr destination, and the SQL dialect are BigQuery-specific. Everything structural is portable, and translating the dialect is a mechanical refactor a coding agent can do in one pass.
The Bruin CLI, ingestr, and DAC are open source and run locally at no cost. The only spend is warehouse storage and query usage. Deployment costs appear later, once the reports are actually being used.
Start with the PostHog Product Analytics guide if you want the commands, or the template README if you would rather read the assets first.
Stripe's dashboard, Sigma, and Data Pipeline each stop somewhere. Here is where they stop, how a three-layer Stripe analytics pipeline fixes it, how the tooling options compare, and why starting from a free local template beats assembling a stack.
Learn how to load Shopify data into ClickHouse, choose an ingestion method, model a bronze-silver-gold ecommerce warehouse, and use Bruin to spend less time maintaining data tooling.
What is CDC in data engineering? Learn how change data capture records database changes, differs from ETL and incremental loading, and when to use it.
Practical updates on open-source data pipelines, AI analysts, governance, and what we are shipping at Bruin.
The signup form is hosted by Brevo. Accept cookies to load it.