skills.rest
PostHog
PostHogOFFICIAL
Explore all skills by this author →
Website𝕏@PostHogGitHub

modeling-conversion-metrics

Build reusable funnel conversion models in PostHog HogQL or dbt.

713|118|Updated Aug 11, 2020
Search Tags:#conversion rate#funnel analysis#PostHog#drop-off#DBT#HogQL#windowfunnel
One-click install
npx skills add https://github.com/PostHog/posthog-foss --skill modeling-conversion-metrics
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: modeling-conversion-metrics
Source: https://github.com/PostHog/posthog-foss/tree/main/products/data_modeling/skills/modeling-conversion-metrics
Command: npx skills add https://github.com/PostHog/posthog-foss --skill modeling-conversion-metrics

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

Teams repeatedly need durable, reusable conversion metrics — funnel conversion rates, step drop-off, and time-to-convert — but ad-hoc funnel queries are hard to reuse, join to other models, or keep consistent across person- and account-level analysis.

Core Features & Use Cases

  • Funnel modeling in HogQL: Compute per-person step completion with ClickHouse windowFunnel, including conversion windows, ordered/strict/any-order modes, and first-touch breakdown attribution, then persist as PostHog warehouse views.
  • dbt conversion marts: Stage step events and build an fct_conversion table with ordered step logic, conversion windows, and schema tests for teams modeling outside PostHog.
  • Metric definitions: Precise definitions for overall vs step-to-step conversion, drop-off, time-to-convert, and attribution choices so numbers reconcile across models.
  • Use Case: A product team wants a reusable signup-to-paid conversion rate with a 30-day window, broken down by signup channel, that can be joined to revenue models — this Skill produces the HogQL view or dbt mart with the correct window, aggregation unit, and attribution.

Quick Start

Ask the AI to model a signup-to-paid conversion funnel with a 30-day window as a reusable PostHog warehouse view, broken down by signup channel.

Frequently Asked Questions about modeling-conversion-metrics

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I compute a funnel conversion rate in PostHog SQL?▼

Use ClickHouse windowFunnel in HogQL: windowFunnel(window_seconds)(toDateTime(timestamp), cond1, cond2, ...) returns steps completed in order per person. Aggregate with countIf to get overall and step-to-step conversion rates, then save as a warehouse view.

How do I build a funnel conversion model in dbt?▼

Stage the step events, then for each step select the earliest event after the previous step's timestamp and within the conversion window from step 1. Aggregate the per-person chains into an fct_conversion table with entry counts, step counts, and overall conversion, plus schema tests.

What is the difference between overall and step-to-step conversion?▼

Overall conversion is units reaching step k divided by units entering step 1 — the headline rate. Step-to-step conversion divides units reaching step k by units reaching step k-1, isolating where drop-off occurs. A model should expose both.

Why does windowFunnel fail with PostHog timestamps?▼

PostHog's timestamp column is DateTime64, which windowFunnel rejects. Wrap it in toDateTime() before passing it as the time argument, as shown in the funnel_conversion.sql recipe.

When should I use a saved funnel insight instead of a warehouse view?▼

Use a saved funnel insight for interactive analysis, native breakdowns, and dashboards. Choose a warehouse view when the conversion metric must be reused — joined to other models, exposed in SQL, or fed into revenue and activation models.

How do I handle breakdown attribution in funnel analysis?▼

Pick first-touch (value at the unit's first step), last-touch (value at conversion), or per-step attribution before modeling, since rates change with the choice. State the chosen method explicitly in the model's column annotations.

Related Skills

Explore More Data & Analytics Skills→
caobingshengcaobingsheng
1

analytics-product

Analyze product data across PostHog and Mixpanel for funnels, cohorts, and retention.

Community
Advanced
nicepkgnicepkg
276

analytics-metrics-kpi

Define metrics, track KPIs, and analyze A/B tests for product analytics.

Official
Advanced
pwv-vcpwv-vc
6

funnel-analysis

Compute SQL-based conversion rates and drop-offs across multi-step funnels.

Official
Intermediate
lightdashlightdash
6.1k

effective-dbt-sql

Writes semantically correct dbt model SQL using CTEs, explicit joins, and field reuse.

Official
Intermediate