PostHog Product Analytics Pipeline with BigQuery
1) Add a plan-change report
initial_plan is on posthog_stage.persons and unused by every shipped report - the template reads it in as a courtesy and leaves the upgrade path to you. It is the natural next report.
Add a new report asset to this Bruin pipeline: assets/posthog_reports/account_plan_changes.sql.
Grain: one row per company.
Source it from posthog_stage.persons and the latest month in posthog_reports.account_engagement_monthly. For each account:
initial_plan- the plan held by the account's earliest-signed-up person, usingsignup_dateto pick themcurrent_plan- carried fromposthog_stage.accounts.planmovement-upgraded,downgraded,unchanged, orunknownwhen either plan is missing, comparing the two on the ordinal free < pro < enterpriselatest_engagement_score- the account'sengagement_scorefrom the most recentactivity_monthinaccount_engagement_monthly, null if the account has no engagement history yet
Match the conventions already used in assets/posthog_reports/:
type: bq.sqlwithmaterialization: type: table, the same as the other reports- an
@bruinheader with adescription,depends,tags, and fullcolumnswith types, descriptions, and checks not_nullanduniqueoncompany, and a custom check thatmovementis neverunknownfor an account whoseinitial_planandcurrent_planare both set- follow the plan-ordinal pattern already used in
assets/posthog_stage/accounts.sqlrather than inventing a new one
Show me the SQL before writing the file. After I approve, write it, run bruin validate, then bruin run assets/posthog_reports/account_plan_changes.sql and show me the movement counts.
The parts that matter in that prompt are the grain, reusing the existing plan-ordinal pattern instead of a new one, and "show me before you write." An agent that follows the existing column and check conventions produces a model your team can review; one that invents its own produces a model nobody trusts.
Other reports that fit the same shape: feature adoption trended by month instead of as a single segment snapshot, an account health score that blends engagement_score and pql_score into one number for a CS team, and a version of product_qualified_accounts scoped to a single industry with its own tuned thresholds.