error-tracking-errors-by-revenue

# Rank errors by what they put at risk Read the current docs first. Every PostHog docs URL returns Markdown if you append `.md`: - https://posthog.com/docs/data-warehouse/sql.md - https://posthog.com/docs/product-analytics/group-analytics.md - https://posthog.com/docs/data-warehouse/warehouse-properties.md If the user hasn't said what to rank by, ask: MRR, ARR, plan, or seats. Then work through these steps in order. ## 1. Find the events and properties You need three things. Check that each one exists in this project before you write a query. - **Exceptions.** `$exception` events. Each one has an `issue_id`, and `properties.$exception_list[1]` holds the error's type and message. - **The account each exception came from.** Group types are defined by each project, so list this project's group types and find the one that represents an account, such as `company`. Note its index. If the project has no groups, look for an account property on persons, such as a company ID or email domain. - **What each account is worth.** A property on that group, such as `mrr`, `arr`, or `plan`. Check that most accounts have a value. If no account has a value, stop. Tell the user that warehouse properties can sync one from Stripe or their database. Don't guess one. ## 2. Find the key that joins them Each event's `$groups` property maps the project's group types to keys, such as `{"company": "acme"}`. In HogQL, the same keys are the columns `$group_0` to `$group_4`, and the index from step 1 picks the column. - Check that most `$exception` events from the last 7 days have that key. If they don't, the SDK doesn't set the group when it captures exceptions. Tell the user, and point them to group analytics for their SDK. - Join exceptions to the `groups` table on `groups.key` and `groups.index`. - Count each account once per error. An account that hits an error 500 times is still one account. ## 3. Pick the shape - One value per error reads best as a bar chart. In the SQL editor, open **Visualization**, pick **Horizontal bar chart**, and put the error label on the X-axis and the value on the Y-axis. - Label each bar with the exception type and the start of its message. Issue IDs are unreadable on an axis. - Show the top 10. - Match the time window to the question. "New errors" means issues created in the last 7 days, from `system.error_tracking_issues`. To rank every error, drop that filter. - Group by the issue ID as well as the label. Two issues can share a label. ## 4. Adapt the example This query ranks the errors first seen in the last 7 days by the MRR of the accounts they hit. It assumes the account group type has index 0 and an `mrr` property. Change the group index, the property, and the time windows to match what you found in steps 1 and 2. Don't run it unchanged. ```sql WITH issues AS ( SELECT id AS issue_id, concat(name, ': ', substring(description, 1, 45)) AS error FROM system.error_tracking_issues WHERE created_at >= now() - INTERVAL 7 DAY ), affected AS ( SELECT DISTINCT issue_id, $group_0 AS account FROM events WHERE event = '$exception' AND timestamp >= now() - INTERVAL 7 DAY AND notEmpty($group_0) AND issue_id IN (SELECT issue_id FROM issues) ) SELECT i.error AS error, round(sum(toFloat(g.properties.mrr))) AS mrr_at_risk FROM affected a JOIN issues i ON i.issue_id = a.issue_id LEFT JOIN groups g ON g.key = a.account AND g.index = 0 GROUP BY i.issue_id, i.error ORDER BY mrr_at_risk DESC LIMIT 10 ``` Run it and check the result. The values shouldn't all be zero, and the labels should be readable. Then save it as an insight. ## Finally Report the top three errors and what they put at risk. Say that the value is what those accounts pay, not revenue the error lost.