Data Modeling

Build reusable revenue models — MRR, ARR, gross revenue, new/expansion/contraction/churn, ARPU, LTV, and per-customer/per-account revenue — on either PostHog data-warehouse views (HogQL) or an external dbt project. Use when the user wants to model, define, or compute recurring revenue, monthly/annual recurring revenue, churn or retention of revenue, lifetime value, average revenue per user, or revenue by customer, cohort, product, or currency. On PostHog, build on the managed revenue_analytics_* views (revenue_item, mrr, customer, subscription, charge, product) fed by Stripe or custom revenue events — not raw Stripe tables — and normalize money with convertCurrency(). In dbt, stage the payment source and compute fct_mrr / fct_revenue_item / dim_customer marts with tests. Covers picking the right source, the subscription-config gotcha that leaves MRR empty, currency handling, and linking revenue to persons/groups. Read modeling-warehouse-foundations first for the view-vs-dbt mechanics.

Build reusable dimension / lookup tables for a star schema — country/region, timezone, currency, date, plan/product, and other descriptive attributes — on either PostHog data-warehouse views (HogQL) or an external dbt project. Use when the user wants to model dimension tables, lookup tables, a star schema, conformed dimensions, or wants to enrich events/revenue/usage with country, region, timezone, plan, or currency attributes without repeating JOINs. Covers sourcing the dimension data (upload, warehouse source, or derive from events), shaping it into an aliased one-row-per-entity view (optionally materialized on a slow schedule since dimensions change rarely), and attaching it to facts via a saved or person join so its columns read as native fields. Key rule: for currency use the built-in convertCurrency() instead of a hand-rolled rate table. Read modeling-warehouse-foundations first; dimensions here are reused by the revenue, conversion, activation, and product-usage modeling skills.

Build reusable activation models — an activation-rate metric and a per-user/per-account activated flag — on either PostHog data-warehouse views (HogQL) or an external dbt project. Use when the user wants to define, model, or measure activation, the "aha moment", onboarding success, or which early actions predict a user sticking around. The core idea this skill enforces: activation is NOT a single assumed event — it is a retention-validated combination of early actions, chosen by balancing reach (enough users hit it) against predictive power (those who hit it retain much better). Covers finding candidate actions, validating them against retention lift, count thresholds and action combinations, per-product and B2B group-level activation, and modeling the winning definition as a durable activated-flag + activation-rate model. Read modeling-warehouse-foundations first; composes modeling-product-usage-metrics for the retention validation.

Build reusable conversion models — funnel/step conversion rates, drop-off, and time-to-convert — on either PostHog data-warehouse views (HogQL) or an external dbt project. Use when the user wants to model, define, or compute a conversion rate, funnel, step completion, drop-off, activation-funnel, signup-to-paid, or any "what % of users who did A went on to do B (within N days)" metric. Covers the funnel model (ordered steps, the conversion-window time-box, strict vs any-order), the person-vs-group aggregation unit, overall vs step-to-step conversion (two different numbers), breakdown attribution, and when a saved funnel insight beats a warehouse view. On PostHog, model funnels in HogQL with windowFunnel; in dbt, stage the event stream and compute an fct_conversion mart with tests. Read modeling-warehouse-foundations first for the view-vs-dbt mechanics; pairs with query-funnel for interactive analysis.

Build reusable product-usage and engagement models — retention, stickiness, and lifecycle — on either PostHog data-warehouse views (HogQL) or an external dbt project. Use when the user wants to model, define, or compute whether users come back (retention / churn), how frequently they engage (stickiness / power users / DAU-WAU-MAU ratio), or the composition of the active base (new / returning / resurrecting / dormant lifecycle). These three are one engagement family sharing a start-event/return-event vocabulary and an interval granularity; this skill treats them together and helps pick the right lens: retention for the return-rate cohort matrix, stickiness for the frequency distribution, lifecycle for growth quality. On PostHog, model them in HogQL (mirroring query-retention / query-stickiness / query-lifecycle); in dbt, build fct_retention / fct_stickiness / fct_lifecycle marts with tests. Read modeling-warehouse-foundations first; feeds the retention validation used by modeling-activation-metrics.

Shared foundations for building reusable data models in PostHog, on either of two stacks: PostHog-native data-warehouse views / materialized views (HogQL, via the view-* MCP tools), or an external dbt project (sources.yml + staging/marts + schema tests) run against your own or PostHog's managed warehouse. Read before authoring any specific business model — covers the PostHog-vs-dbt decision, the view-create → view-materialize → sync_frequency workflow and the HogQL column-aliasing rule, the dbt project skeleton and the honest "no native dbt integration" picture, warehouse joins and star-schema dimensions, currency conversion with convertCurrency(), and checking/registering models in the data catalog for reuse. Companion to the domain skills modeling-revenue-metrics, modeling-conversion-metrics, modeling-activation-metrics, modeling-product-usage-metrics, and modeling-dimension-tables. Use when the user asks how to build a view, materialized view, or dbt model in PostHog, or which of the two stacks to use.