Bruin CLI Step 7 of 7

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.

AI Prompt

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, using signup_date to pick them
  • current_plan - carried from posthog_stage.accounts.plan
  • movement - upgraded, downgraded, unchanged, or unknown when either plan is missing, comparing the two on the ordinal free < pro < enterprise
  • latest_engagement_score - the account's engagement_score from the most recent activity_month in account_engagement_monthly, null if the account has no engagement history yet

Match the conventions already used in assets/posthog_reports/:

  • type: bq.sql with materialization: type: table, the same as the other reports
  • an @bruin header with a description, depends, tags, and full columns with types, descriptions, and checks
  • not_null and unique on company, and a custom check that movement is never unknown for an account whose initial_plan and current_plan are both set
  • follow the plan-ordinal pattern already used in assets/posthog_stage/accounts.sql rather 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.

Sign up to our newsletter

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.