Turning Engineering Analytics Into Insights

PostHogOfficialPublished by PostHog

Skill
Tool integration
Data and AI
MIT
No issues found

From the author

Converts engineering analytics (PR / CI) data into saved PostHog insights, dashboards, and subscriptions, and explains how to query the product data directly with SQL. Covers discovering per-team GitHub warehouse tables via engineering-analytics-sources, replicating curated column semantics in HogQL, reading exposed engineering_analytics_* warehouse views where product logic is involved (CI cost, fingerprinted failure lines, commit attribution), saving queries with insight-create, and scheduling delivery with subscriptions-create. Use when asked to "save this as an insight", "put CI health / merge times on a dashboard", "email me PR throughput weekly", "chart CI cost", "track time to first review", "subscribe to these numbers", "alert on CI success rate", or "what data/tables/views does engineering analytics read". For ad-hoc CI and merge questions use diagnosing-ci-and-merge-bottlenecks; to investigate one specific CI failure use investigating-ci-failures.

— the description by PostHog

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

This skill provides a structured approach to transforming raw engineering analytics data (PRs, CI workflows, and team metrics) into persistent, shareable insights within PostHog. It guides users through discovering team-specific warehouse tables, applying curated HogQL logic to ensure data accuracy, and utilizing specialized warehouse views for complex metrics like CI costs and failure attribution. By moving beyond ad-hoc tool outputs, users can create custom dashboards and recurring subscriptions for long-term tracking of engineering health.

What it does

Identifies team-specific GitHub warehouse tables using engineering-analytics-sources.
Provides HogQL recipes that mirror product-curated logic for PR and workflow metrics.
Leverages pre-built warehouse views for CI job costs, history, and failure fingerprinting.
Creates SQL-based insights and bundles them into dashboards.
Configures recurring email or Slack subscriptions for automated reporting.
Ensures consistent metrics by avoiding manual re-derivation of complex product logic.

When to use it

When you need to save a specific engineering metric as a permanent insight or dashboard tile.
When you want to schedule weekly reports on PR throughput or CI health.
When you need to track CI costs or merge times over extended periods.
When you need to alert stakeholders on specific CI success rate thresholds.
When you need to understand which warehouse tables or views contain specific engineering data.

Advantages

Ensures data consistency by using the same logic as the core engineering analytics product.
Enables long-term trend analysis that ad-hoc tool outputs cannot provide.
Automates stakeholder communication through scheduled subscriptions.
Provides a clear separation between raw data querying and product-logic-backed views.
Prevents common pitfalls like incorrect timestamp parsing or bot-data skew.

Usage examples

“Save the weekly PR open-to-merge time as an insight.”: The agent identifies the relevant PR tables, applies the standard open-to-merge HogQL recipe, and creates a new SQL insight in PostHog.
“Put CI health and merge times on a new dashboard.”: The agent generates the necessary SQL queries for both metrics, creates individual insights, and bundles them into a single dashboard.
“Email me PR throughput weekly.”: The agent creates a SQL insight for PR throughput and configures a subscription to deliver the data on a weekly schedule.
“What tables does engineering analytics read for CI costs?”: The agent explains the use of the engineering_analytics_job_costs view and how it aggregates data from the underlying GitHub warehouse tables.

Install

Download the skill as a ZIP:
https://autogineer.com/agent-skills/posthog/turning-engineering-analytics-into-insights/download

Unzip it into the cross-client skills folder of your project:

mkdir -p .agents/skills && unzip turning-engineering-analytics-into-insights.zip -d .agents/skills

Documentation by PostHog

Turning engineering analytics into insights and subscriptions

The engineering analytics dashboard and MCP tools (pull-requests, workflow-health, pr-lifecycle, engineering-analytics-broken-tests, …) run curated HogQL privately: nothing in the UI or the tool output names the underlying tables, and the endpoints cannot themselves be saved as insights or subscribed to. The data, however, is queryable directly, through two substrates:

Raw warehouse tables — <prefix>github_pull_requests, <prefix>github_workflow_runs, <prefix>github_workflow_jobs, <prefix>github_reviews, and <prefix>github_teams / <prefix>github_team_members (org team membership — the author→team map) — ordinary team-scoped tables you query with HogQL.
Three curated warehouse views with fixed names — engineering_analytics_job_costs, engineering_analytics_ci_job_history, engineering_analytics_ci_failures — provisioned per team from the connected GitHub source(s). Non-materialized: computed at query time, always current, and they back insights and subscriptions like any table.

The views exist for exactly one reason: they render product code into SQL — the runner-tier cost model, the failure-fingerprint recipe, the jobs↔runs commit-attribution rules — logic that would silently drift if hand-rolled, re-rendered into the team's view whenever the code changes. Everything else is just table data, and pure HogQL over the raw tables is always enough: never create additional warehouse views for engineering analytics data, and never re-derive in SQL what the three views already encode.

What the product shows	Where the data actually lives	Can it back an insight?
PR list, merge times, CI status, workflow health	Data warehouse tables `<prefix>github_pull_requests`, `<prefix>github_workflow_runs`	Yes (SQL insight over the tables)
Reviews and approvals	`<prefix>github_reviews`	Yes
Team-level PR metrics (author→team attribution)	`<prefix>github_team_members` semi-joined against the PR authors	Yes (team aggregates only)
Job durations, queue times, runner tiers	`<prefix>github_workflow_jobs`	Yes
CI cost (runner-tier price ladder)	`engineering_analytics_job_costs` view	Yes (query the view, never recompute cost)
Per-job CI history with commit attribution	`engineering_analytics_ci_job_history` view	Yes
Grouped (fingerprinted) CI failure lines	`engineering_analytics_ci_failures` view (reads the Logs product, short retention)	Yes, for short recent windows
Thinned CI failure logs for a PR or run	Logs product (`service_name = 'github-ci-logs'`) + thinning logic	No (use the MCP tools ad hoc)
Flaky-test leaderboard, broken-tests triage, team CI health	CI trace spans + ranking/classification logic in product code	No (use the MCP tools ad hoc)

So the job splits cleanly: warehouse-backed metrics (raw tables or the three views) become SQL insights (then dashboards, then subscriptions); everything computed by product logic at request time stays on the MCP tools, delivered recurringly via an AI subscription if needed.

Step 1: discover the team's tables

Warehouse table names carry a user-chosen prefix, so never hardcode them. Call the engineering-analytics-sources MCP tool: each connected GitHub source returns its id, repo, and prefix. The tables are <prefix>github_pull_requests, <prefix>github_workflow_runs, <prefix>github_workflow_jobs, <prefix>github_reviews, and <prefix>github_teams / <prefix>github_team_members; an empty prefix means the plain github_* names. With multiple sources, ask which repo the user means; each source is one repo.

The three engineering_analytics_* views need no discovery: their names are fixed (no prefix), and they cover all of the team's GitHub sources at once — filter on repo_owner / repo_name (job_costs, ci_job_history) or repo (ci_failures, which reads the Logs product rather than the per-source tables) to scope down to one repo.

Step 2: write HogQL that carries the curated semantics

First check whether one of the three views already answers the question — cost, per-job history and commit attribution, fingerprinted failure lines — and query it directly; the rules below are for the raw tables.

The raw tables land GitHub's JSON verbatim, so a naive SELECT gets the domain rules wrong. Copy the base subqueries from references/hogql-recipes.md (they mirror the product's own curated builders) and follow these rules:

Timestamps are strings. Always parseDateTimeBestEffort(created_at) etc. before comparing or diffing.
Nested JSON is Nullable. ifNull(...)-unwrap before JSONExtractArrayRaw / splitByChar: ClickHouse rejects an Array inside a Nullable.
CI ↔ PR attribution is by PR number (the run's pull_requests association), never by head SHA: the PR snapshot keeps only the current head, so a SHA join silently drops every push but the latest. head SHA is only for a PR's current CI status (latest run per (head_sha, workflow_name)).
Bot detection: author_handle LIKE '%[bot]' OR author_handle IN ('dependabot', 'github-actions', 'posthog-bot', 'renovate'). Exclude bots and drafts from throughput / merge-time metrics by default.
Honest names. merged_at - created_at is open_to_merge_seconds (it fuses draft and review time); never label an insight "cycle time" or "review time".
Conclusions can be stale. The runs sync watermarks on created_at; a run that completes late can show a stale conclusion. Compute rates over status = 'completed' rows only.
Reviews join by pr_number. In github_reviews, state is APPROVED / CHANGES_REQUESTED / COMMENTED / DISMISSED (pending drafts are dropped at sync), and the injected pr_number joins to the PR table's number.
Author→team attribution is a membership semi-join. Filter author_handle IN (SELECT member_handle FROM …) against github_team_members (see the team recipe) — the shape the product's own team merge trend uses. A person can belong to several GitHub teams, so a plain JOIN would double-count their PRs across teams; and only team-level aggregates leave the query, never per-member figures.

Test the query with the execute-sql MCP tool (or the SQL editor) before saving anything.

Step 3: save it as an insight

Use insight-create with a SQL insight:

{
  "name": "Weekly PR open→merge time (hours)",
  "query": {
    "kind": "DataVisualizationNode",
    "source": { "kind": "HogQLQuery", "query": "<tested SQL>" }
  }
}

display (a ChartDisplayType, e.g. ActionsLineGraph) and chartSettings (xAxis / yAxis columns) turn the table into a chart; when unsure, save it and let the user pick the visualization in the UI, linking the returned insight URL. Bake a relative window into the SQL (>= now() - INTERVAL 90 DAY); note that a hard-coded date filter means dashboard date overrides won't apply to this tile. Bundle several insights with dashboard-create.

With the insight (or dashboard) saved, this is standard subscription territory: follow the managing-subscriptions skill for the subscriptions-create payload, channels, frequency, and AI-summary options.

For "notify me when X" (a condition, not a schedule): alerts require a trends insight, so a SQL insight can't be alerted today. Offer a scheduled subscription instead, or a threshold check inside a prompt-kind AI subscription.

What NOT to rebuild in SQL

The flaky-test ranking, broken-tests classification, team CI health rollup, and failure-log thinning are product logic over data an insight can't reach (CI trace spans, raw log bodies); hand-rolled SQL versions will silently drift from what the dashboard shows. For a recurring report on those, create a prompt-kind AI subscription (see the creating-ai-subscription skill) whose prompt asks for the relevant engineering analytics reading each period, or just call the MCP tools (engineering-analytics-flaky-tests, engineering-analytics-broken-tests, engineering-analytics-team-ci-health, engineering-analytics-ci-failure-logs, engineering-analytics-run-failure-logs) ad hoc.

CI cost is not on that list because its product logic is rendered into the engineering_analytics_job_costs view (parity-tested against the product's own model, re-rendered when the model changes), so cost insights are plain SQL over the view — and the cost MCP tools (engineering-analytics-pr-cost, engineering-analytics-workflow-runner-costs) read that same rendered SELECT, so the numbers agree. Still: never recompute dollar cost from runner labels yourself.

Caveats to carry into every insight

Name these in the insight description so future readers inherit them: open_to_merge_seconds is coarse (draft + review fused); CI conclusions can lag until the run's webhook settles; estimated_cost_usd NULL means non-billable or still running, never zero — disambiguate via provider vs completed_at; ci_failures is pytest-only and failure-only, so its counts are absolute signal, never rates; bots and drafts are excluded (or not: say which). And never build per-author leaderboards or cross-author rankings; per-developer surveillance is an explicit product non-goal. When a team-level split is wanted, group through the github_team_members membership semi-join instead.

Files

ATTRIBUTION.mdAdded by AutoGineer905 B
LICENSELicence file added by AutoGineer1.0 KiB
SKILL.md11.3 KiB
references/hogql-recipes.md10.7 KiB

references/hogql-recipes.md

HogQL recipes over the GitHub warehouse tables

These base subqueries mirror the product's curated builders (products/engineering_analytics/backend/logic/views/), so an insight built on them matches what the dashboard and MCP tools report. Replace every github_* table name (github_pull_requests, github_workflow_runs, github_workflow_jobs, github_reviews, github_team_members) with the team's real table name from engineering-analytics-sources (prefix + github_<endpoint>). The engineering_analytics_* views used below have fixed names — no prefix, no discovery.

The PR base

SELECT
    number,
    title,
    author_handle,
    (author_handle LIKE '%[bot]'
        OR author_handle IN ('dependabot', 'github-actions', 'posthog-bot', 'renovate')) AS is_bot,
    arrayElement(repo_parts, 1) AS repo_owner,
    arrayElement(repo_parts, 2) AS repo_name,
    if(merged_at IS NOT NULL, 'merged', raw_state) AS state,
    is_draft,
    created_at,
    merged_at,
    closed_at,
    head_sha,
    if(merged_at IS NOT NULL, dateDiff('second', created_at, merged_at), NULL) AS open_to_merge_seconds
FROM (
    SELECT
        number,
        title,
        state AS raw_state,
        coalesce(draft, false) AS is_draft,
        ifNull(JSONExtractString(user, 'login'), '') AS author_handle,
        splitByChar('/', ifNull(JSONExtractString(base, 'repo', 'full_name'), '')) AS repo_parts,
        JSONExtractString(head, 'sha') AS head_sha,
        parseDateTimeBestEffort(created_at) AS created_at,
        parseDateTimeBestEffort(merged_at) AS merged_at,
        parseDateTimeBestEffort(closed_at) AS closed_at
    FROM github_pull_requests
)

The two-layer shape is required: the inner SELECT parses string timestamps and ifNull-unwraps Nullable JSON; the outer derives state, repo identity, and durations. Collapsing the layers hits ClickHouse's Array-inside-Nullable rejection and same-SELECT alias limits.

The workflow-runs base

SELECT
    workflow_name,
    head_sha,
    head_branch,
    status,
    conclusion,
    run_started_at,
    run_attempt,
    pr_number,
    if(status = 'completed', dateDiff('second', run_started_at, updated_at), NULL) AS duration_seconds,
    arrayElement(repo_parts, 1) AS repo_owner,
    arrayElement(repo_parts, 2) AS repo_name
FROM (
    SELECT
        name AS workflow_name,
        head_sha,
        head_branch,
        status,
        conclusion,
        run_attempt,
        JSONExtractInt(arrayElement(JSONExtractArrayRaw(ifNull(pull_requests, '[]')), 1), 'number') AS pr_number,
        splitByChar('/', ifNull(JSONExtractString(repository, 'full_name'), '')) AS repo_parts,
        parseDateTimeBestEffort(run_started_at) AS run_started_at,
        parseDateTimeBestEffort(updated_at) AS updated_at
    FROM github_workflow_runs
)

pr_number is the first entry of the run's pull_requests association, or 0 when there is none (fork PRs, pushes with no open PR); filter pr_number > 0 before attributing runs to PRs. This association, not head_sha, is how the product links CI to a PR across all its pushes.

Recipe: weekly open→merge time trend

WITH prs AS (<PR base>)
SELECT
    toStartOfWeek(merged_at) AS week,
    median(open_to_merge_seconds) / 3600 AS p50_hours,
    quantile(0.95)(open_to_merge_seconds) / 3600 AS p95_hours,
    count() AS merged_prs
FROM prs
WHERE merged_at >= now() - INTERVAL 90 DAY
  AND NOT is_bot
  AND NOT is_draft
GROUP BY week
ORDER BY week

Report p50 and p95 side by side; they move independently. This is "open to merge", not cycle time.

Recipe: weekly CI success rate and p95 duration per workflow

WITH runs AS (<workflow-runs base>)
SELECT
    toStartOfWeek(run_started_at) AS week,
    workflow_name,
    count() AS runs,
    countIf(conclusion = 'success')
        / nullIf(countIf(conclusion IN ('success', 'failure', 'timed_out', 'startup_failure', 'stale')), 0)
            AS success_rate,
    quantileIf(0.95)(duration_seconds, conclusion = 'success') AS p95_seconds
FROM runs
WHERE status = 'completed'
  AND run_started_at >= now() - INTERVAL 60 DAY
GROUP BY week, workflow_name
HAVING runs >= 5
ORDER BY week, runs DESC

The success rate counts failure, timed_out, startup_failure, and stale as failures. It excludes skipped, cancelled, neutral, and action-required runs because they did not reach a pass-or-fail verdict. The duration percentile uses successful runs because cancelled and failed runs end early. For a single-workflow tile, add AND workflow_name = 'CI' and drop the group.

Recipe: PR throughput per week

WITH prs AS (<PR base>)
SELECT
    toStartOfWeek(created_at) AS week,
    countIf(state = 'merged') AS merged,
    countIf(state = 'closed') AS closed_unmerged
FROM prs
WHERE created_at >= now() - INTERVAL 90 DAY
  AND NOT is_bot
  AND NOT is_draft
GROUP BY week
ORDER BY week

Recipe: open PRs with failing CI right now

A PR's current CI status is the latest completed run per (head_sha, workflow_name); this is the one place head SHA is the correct key:

WITH prs AS (<PR base>),
runs AS (<workflow-runs base>),
ci AS (
    SELECT
        head_sha,
        countIf(s = 'completed' AND c IN ('failure', 'timed_out', 'startup_failure', 'stale')) AS failing
    FROM (
        SELECT head_sha, workflow_name,
            argMax(status, run_started_at) AS s,
            argMax(conclusion, run_started_at) AS c
        FROM runs
        GROUP BY head_sha, workflow_name
    )
    GROUP BY head_sha
)
SELECT count() AS open_prs_failing_ci
FROM prs
INNER JOIN ci ON prs.head_sha = ci.head_sha
WHERE prs.state = 'open' AND ci.failing > 0

Drafts and bots stay included here, matching the product's failing-CI card (ci_cards only excludes them from the stuck count); add AND NOT prs.is_draft only if the user explicitly wants non-draft PRs, and say the number then diverges from the dashboard. A pending-heavy result can be sync staleness, not real in-flight CI; treat pending as unsettled, never as failure.

Job-level recipes

Durations and queue times are honest SQL over <prefix>github_workflow_jobs (started_at - created_at is queue wait, completed_at - started_at is run time; all string timestamps, parse them). Do not recompute dollar cost from labels: the runner-tier price ladder lives in product code and drifts; query the engineering_analytics_job_costs view instead (below), which renders that model into SQL and is parity-tested against it.

Recipe: weekly CI cost by workflow (job_costs view)

SELECT
    toStartOfWeek(created_at) AS week,
    workflow_name,
    round(sum(estimated_cost_usd), 2) AS estimated_cost_usd
FROM engineering_analytics_job_costs
WHERE created_at >= now() - INTERVAL 60 DAY
GROUP BY week, workflow_name
ORDER BY week, estimated_cost_usd DESC

Grain is one row per job attempt, so sum is correct across retries. estimated_cost_usd is NULL (skipped by sum) for non-billable jobs (github-hosted, non-Linux, unclassifiable labels) and for jobs still running; disambiguate a NULL via provider (non-billable) vs completed_at (unsettled). Add WHERE pr_number = <n> for one PR's cost — it matches engineering-analytics-pr-cost, since the tool reads the same rendered cost SELECT. With several connected sources, filter repo_owner / repo_name.

Review recipes

In <prefix>github_reviews, pending drafts are dropped at sync; state is APPROVED / CHANGES_REQUESTED / COMMENTED / DISMISSED, and the injected pr_number joins to the PR base's number.

Weekly time to first review:

WITH prs AS (<PR base>),
first_review AS (
    SELECT pr_number, min(parseDateTimeBestEffort(submitted_at)) AS first_review_at
    FROM github_reviews
    GROUP BY pr_number
)
SELECT
    toStartOfWeek(prs.created_at) AS week,
    median(dateDiff('second', prs.created_at, fr.first_review_at)) / 3600 AS p50_hours,
    quantile(0.95)(dateDiff('second', prs.created_at, fr.first_review_at)) / 3600 AS p95_hours,
    count() AS reviewed_prs
FROM prs
INNER JOIN first_review fr ON prs.number = fr.pr_number
WHERE prs.created_at >= now() - INTERVAL 90 DAY
  AND NOT prs.is_bot
GROUP BY week
ORDER BY week

Name it "open to first review", not "time in review": this recipe reads the PR snapshot only, so draft time is fused in, same caveat as open_to_merge_seconds (ready-for-review timestamps live in <prefix>github_issue_events, when synced). The reviewer handle is ifNull(JSONExtractString(user, 'login'), '') when needed (e.g. to exclude self-reviews by comparing against author_handle) — but never build per-reviewer leaderboards.

Recipe: a team's weekly merge time (team_members table)

<prefix>github_team_members maps PR authors to GitHub org teams (every column lands Nullable; drop empty logins — they would match deleted-account authors). Attribute PRs to a team with a membership semi-join, the shape the product's own team merge trend uses — a plain JOIN would double-count authors who belong to several teams:

WITH prs AS (<PR base>),
members AS (
    SELECT ifNull(login, '') AS member_handle
    FROM github_team_members
    WHERE ifNull(team_slug, '') = 'my-team'
      AND ifNull(login, '') != ''
)
SELECT
    toStartOfWeek(merged_at) AS week,
    median(open_to_merge_seconds) / 3600 AS p50_hours,
    count() AS merged_prs
FROM prs
WHERE merged_at >= now() - INTERVAL 90 DAY
  AND NOT is_bot
  AND author_handle IN (SELECT member_handle FROM members)
GROUP BY week
ORDER BY week

Only team-level aggregates leave the query — no per-member figures, no cross-team rankings. Note the namespace: these are GitHub org team slugs, while the engineering-analytics-team-ci-health tool groups by the repo's ownership map (products/*/product.yaml + CODEOWNERS); a team whose slugs differ across the two won't line up between such insights and that tool.

Recipe: distinct CI failures per day (ci_failures view)

SELECT
    toStartOfDay(timestamp) AS day,
    uniq(fingerprint) AS distinct_failures,
    count() AS failed_test_lines
FROM engineering_analytics_ci_failures
WHERE timestamp >= now() - INTERVAL 14 DAY
GROUP BY day
ORDER BY day

The view reads the Logs product, so its reach is bounded by the short logs retention — fine for a recent-window tile, wrong for long trends. Fingerprinting is pytest-only, and the data is failure-only (passing runs are absent), so report absolute counts, never rates. To chase a specific failure to a culprit, hand off to the investigating-ci-failures skill, which works from this view plus engineering_analytics_ci_job_history. When windowing engineering_analytics_ci_job_history, pair the precise created_at >= … filter with a coarse raw floor (created_at_raw >= '<YYYY-MM-DD>', a day below the window) so the parquet scan can prune; a computed-column predicate alone forces a full scan.

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Published by

PostHog

Publisher

PostHog (Official)

Source

PostHog/skills · skills/omnibus/turning-engineering-analytics-into-insights · commit a5d30e7

Licence

MIT License (redistribution allowed)

LICENSE (added by AutoGineer from LICENSE)
ATTRIBUTION.md (written by AutoGineer)

Synced

2026-11-09

AutoGineer is not affiliated with or endorsed by PostHog or PostHog.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download turning-engineering-analytics-into-insights.zip