PostHog Measurements for Goal Bindings
Reference guide for creating and debugging PostHog insights that bind to Hamster goal measures. Every line here saves 10 minutes of trial-and-error.
When to Invoke
- Creating a PostHog insight to bind to a Hamster goal measure
- Debugging why a measure binding shows 0 or null
- Converting a HogQL query to a TrendsQuery
- Setting up formula-based metrics (ratios, rates) for goal measures
- User says: "bind this PostHog metric to a goal" or "why is my measure showing 0"
The Measure Binding Contract (CRITICAL)
Hamster measure bindings read aggregated_value from TrendsQuery results.
| Query Type |
Result Shape |
Binding Can Read? |
| TrendsQuery |
aggregated_value: <number> |
YES |
| HogQLQuery |
[[value]] (table format) |
NO |
Rules:
- The insight MUST be a
TrendsQuery -- never HogQLQuery for measure bindings.
- Set
display: "BoldNumber" for single-value metrics. Other options ("Number", "TimeSeries") may not expose aggregated_value correctly.
- For ratio metrics (A/B), use the
formula field -- the formula result appears as aggregated_value.
- For non-formula queries, each series returns its own
count and aggregated_value.
TrendsQuery Structure Reference
{
"query": {
"kind": "TrendsQuery",
"series": [
{
"kind": "EventsNode",
"event": "Chat Message Sent",
"math": "hogql",
"math_hogql": "count(DISTINCT properties.thread_id)",
"properties": [
{
"key": "properties.source",
"value": "ai",
"operator": "exact",
"type": "event"
}
]
}
],
"formula": null,
"dateRange": { "date_to": null, "date_from": "-7d" },
"interval": "day",
"display": "BoldNumber"
}
}
Fields
series: Array of EventsNode objects. Each defines an event + math aggregation.
formula: String like "A/B" where A/B refer to series indices (0-based series, letter-based refs). null for single-series.
dateRange: { "date_from": "-7d", "date_to": null } for last 7 days. Supports relative (-30d, -1m) and absolute dates.
interval: "day", "week", "month". For BoldNumber, this affects aggregation granularity but the final value is the sum over the range.
display: "BoldNumber" for single-value metrics (what bindings need). "TimeSeries" for trend charts.
EventsNode Fields
kind: Always "EventsNode" for event-based series.
event: Event name (e.g., "Chat Message Sent").
math: Aggregation type (see table below).
math_hogql: Required when math: "hogql". Contains the HogQL expression.
math_property: Required for unique_group (must be a GROUP TYPE, not an event property).
properties: Array of property filters. Each: { "key": "...", "value": ..., "operator": "exact|gt|lt|...", "type": "event|person|..." }.
Math Types Reference
math value |
What it does |
Extra fields needed |
"total" (default) |
Total event count |
none |
"dau" |
Daily active users |
none |
"weekly_active" |
Weekly active users |
none |
"monthly_active" |
Monthly active users |
none |
"unique_session" |
Unique sessions |
none |
"first_time_for_user" |
First time event for user |
none |
"first_matching_event_for_user" |
First matching event for user |
none |
"unique_group" |
Unique group count |
math_property = GROUP TYPE (e.g., "account", "project") |
"hogql" |
Custom HogQL aggregation |
math_hogql = HogQL expression string |
Critical Gotchas
There is NO "unique" math type. To count unique values of an event property (e.g., distinct thread_id), use math: "hogql" with math_hogql: "count(DISTINCT properties.thread_id)".
unique_group is for GROUP ANALYTICS, not event properties. math: "unique_group" with math_property: "thread_id" does NOT count unique thread IDs -- it silently falls back to count(). math_property must be a group type name (like "account"), not an event property name.
Use math_hogql, NOT math_property, for custom HogQL expressions. math_property is only for unique_group. If you put a HogQL expression in math_property, it will be ignored.
Step-by-Step: Creating a Measure-Binding Insight
- Determine the metric formula. What are you counting? Is it a ratio (A/B)? A single count?
- Choose the right math types. Map your intent to the math types table above. When in doubt, use
math: "hogql" with an explicit expression.
- Build the TrendsQuery JSON. Use the structure reference above. Set
display: "BoldNumber".
- Create the insight via
POSTHOG_CREATE_CUSTOM_PROJECT_INSIGHTS with the query object.
- Fetch the result via
POSTHOG_CREATE_QUERY_IN_PROJECT_BY_ID with refresh: true to force computation.
- Verify
aggregated_value is non-null and matches expectations. If null or 0, run the debugging checklist.
- Update the goal measure binding to point to the new insight ID.
HogQL Pitfalls
These are the silent failures that cost hours:
| Pitfall |
Symptom |
Fix |
properties.$session_id |
Returns NULL silently |
Use properties['$session_id'] (bracket syntax for $-prefixed keys) |
properties.thread_id |
Works fine |
Dot syntax is OK for non-$ properties |
'none' sentinel |
Missing values appear as literal string 'none' |
Treat as absent; filter with IS NOT NULL AND != 'none' |
| Shell escaping |
Complex HogQL breaks when passed via CLI |
Write to a temp file, or use JSON body directly via Composio |
OPERATION_PROPERTY_NONE |
Literal string 'none' in results |
Same as above -- it is the missing-value sentinel |
Common Patterns
Unique count of a property
{ "kind": "EventsNode", "event": "Chat Message Sent", "math": "hogql", "math_hogql": "count(DISTINCT properties.thread_id)" }
Unique sessions
{ "kind": "EventsNode", "event": "Chat Message Sent", "math": "unique_session" }
Total events (default)
{ "kind": "EventsNode", "event": "Chat Message Sent", "math": "total" }
Ratio metric (e.g., unique threads / total sessions)
{
"query": {
"kind": "TrendsQuery",
"series": [
{ "kind": "EventsNode", "event": "Chat Message Sent", "math": "hogql", "math_hogql": "count(DISTINCT properties.thread_id)" },
{ "kind": "EventsNode", "event": "Chat Message Sent", "math": "unique_session" }
],
"formula": "A/B",
"dateRange": { "date_from": "-7d", "date_to": null },
"interval": "day",
"display": "BoldNumber"
}
}
Filtered series (only AI-sourced messages)
{
"kind": "EventsNode",
"event": "Chat Message Sent",
"math": "total",
"properties": [
{ "key": "properties.source", "value": "ai", "operator": "exact", "type": "event" }
]
}
Anti-Patterns
| Anti-pattern |
Why it fails |
Using HogQLQuery instead of TrendsQuery |
Returns [[value]] table format -- binding cannot read aggregated_value |
Using unique_group with an event property name |
Silently falls back to count() -- math_property must be a group TYPE |
Using math_property instead of math_hogql for custom HogQL |
math_property is ignored for math: "hogql" -- use math_hogql |
Forgetting refresh: true when fetching results |
Returns stale or null cached results |
Using properties.$session_id in HogQL |
Returns NULL silently -- use properties['$session_id'] |
Using display: "TimeSeries" for a binding metric |
May not expose aggregated_value -- use "BoldNumber" |
Debugging Checklist
When a measure binding shows 0, null, or wrong value, check in order:
- Is the insight a TrendsQuery? (not HogQLQuery) -- HogQL returns tables, not
aggregated_value.
- Does the result contain
aggregated_value? -- Fetch via POSTHOG_CREATE_QUERY_IN_PROJECT_BY_ID and inspect the response.
- Is
display set to "BoldNumber"? -- Other display types may not expose the value correctly.
- Are you using
math_hogql (not math_property) for custom HogQL? -- Check the series object.
- Are
$-prefixed properties accessed with bracket syntax? -- properties['$session_id'] not properties.$session_id.
- Did you fetch with
refresh: true? -- Without it, you may get stale/null cached results.
- Is the date range correct? -- Check
dateRange.date_from and dateRange.date_to. A too-narrow range returns 0.
- Are there events matching the filter? -- Verify the event exists and properties match in the PostHog UI.
- For formulas: does the denominator ever equal 0? -- Division by zero returns null, not an error.
PostHog Composio Actions
Key actions for measure binding work:
| Action |
Purpose |
POSTHOG_CREATE_CUSTOM_PROJECT_INSIGHTS |
Create a new insight with a custom TrendsQuery |
POSTHOG_CREATE_QUERY_IN_PROJECT_BY_ID |
Execute a query and get results (use refresh: true) |
POSTHOG_LIST_ORGANIZATION_PROJECTS |
List projects to get project IDs |
POSTHOG_LIST_PROJECT_GROUP_TYPES |
List group types (for unique_group math) |
For the full set of 200+ PostHog actions, call list_app_actions with app: "posthog".
PostHog Project IDs
- prod:
371628
- tux:
371881
- API host:
https://us.posthog.com
Cross-References
hamster-analytics -- Event instrumentation patterns (how to track events in code)
hamster-instrumentation -- Plan-time observability checklist (what to track before building)
hamster-posthog-dashboards -- Dashboard analysis and reporting workflows
hamster-posthog-user-audit -- Per-user event auditing (Q1-Q8 playbook)
- Hamster docs: Metrics | Goals
1---2name: posthog-measurements-for-goal-bindings3description: "Standardizes creating and debugging PostHog insights for Hamster goal measure bindings. Covers TrendsQuery vs HogQL, the measure binding contract (aggregated_value), math types, formulas, HogQL pitfalls, and the Composio API actions needed. Use when binding a PostHog metric to a goal, debugging a measure showing 0/null, or converting HogQL to TrendsQuery."4version: "1.0.0"5category: "Workflows"6metadata:7 source: https://skillmd.com/skills/gethamster/posthog-measurements-for-goal-bindings8---910# PostHog Measurements for Goal Bindings1112> Reference guide for creating and debugging PostHog insights that bind to Hamster goal measures. Every line here saves 10 minutes of trial-and-error.1314## When to Invoke1516- Creating a PostHog insight to bind to a Hamster goal measure17- Debugging why a measure binding shows 0 or null18- Converting a HogQL query to a TrendsQuery19- Setting up formula-based metrics (ratios, rates) for goal measures20- User says: "bind this PostHog metric to a goal" or "why is my measure showing 0"2122## The Measure Binding Contract (CRITICAL)2324Hamster measure bindings read `aggregated_value` from **TrendsQuery** results.2526| Query Type | Result Shape | Binding Can Read? |27| --- | --- | --- |28| TrendsQuery | `aggregated_value: <number>` | YES |29| HogQLQuery | `[[value]]` (table format) | NO |3031**Rules:**32331. The insight MUST be a `TrendsQuery` -- never `HogQLQuery` for measure bindings.342. Set `display: "BoldNumber"` for single-value metrics. Other options (`"Number"`, `"TimeSeries"`) may not expose `aggregated_value` correctly.353. For ratio metrics (A/B), use the `formula` field -- the formula result appears as `aggregated_value`.364. For non-formula queries, each series returns its own `count` and `aggregated_value`.3738## TrendsQuery Structure Reference3940```json41{42 "query": {43 "kind": "TrendsQuery",44 "series": [45 {46 "kind": "EventsNode",47 "event": "Chat Message Sent",48 "math": "hogql",49 "math_hogql": "count(DISTINCT properties.thread_id)",50 "properties": [51 {52 "key": "properties.source",53 "value": "ai",54 "operator": "exact",55 "type": "event"56 }57 ]58 }59 ],60 "formula": null,61 "dateRange": { "date_to": null, "date_from": "-7d" },62 "interval": "day",63 "display": "BoldNumber"64 }65}66```6768### Fields6970- **`series`**: Array of `EventsNode` objects. Each defines an event + math aggregation.71- **`formula`**: String like `"A/B"` where A/B refer to series indices (0-based series, letter-based refs). `null` for single-series.72- **`dateRange`**: `{ "date_from": "-7d", "date_to": null }` for last 7 days. Supports relative (`-30d`, `-1m`) and absolute dates.73- **`interval`**: `"day"`, `"week"`, `"month"`. For BoldNumber, this affects aggregation granularity but the final value is the sum over the range.74- **`display`**: `"BoldNumber"` for single-value metrics (what bindings need). `"TimeSeries"` for trend charts.7576### EventsNode Fields7778- **`kind`**: Always `"EventsNode"` for event-based series.79- **`event`**: Event name (e.g., `"Chat Message Sent"`).80- **`math`**: Aggregation type (see table below).81- **`math_hogql`**: Required when `math: "hogql"`. Contains the HogQL expression.82- **`math_property`**: Required for `unique_group` (must be a GROUP TYPE, not an event property).83- **`properties`**: Array of property filters. Each: `{ "key": "...", "value": ..., "operator": "exact|gt|lt|...", "type": "event|person|..." }`.8485## Math Types Reference8687| `math` value | What it does | Extra fields needed |88| --- | --- | --- |89| `"total"` (default) | Total event count | none |90| `"dau"` | Daily active users | none |91| `"weekly_active"` | Weekly active users | none |92| `"monthly_active"` | Monthly active users | none |93| `"unique_session"` | Unique sessions | none |94| `"first_time_for_user"` | First time event for user | none |95| `"first_matching_event_for_user"` | First matching event for user | none |96| `"unique_group"` | Unique group count | `math_property` = GROUP TYPE (e.g., `"account"`, `"project"`) |97| `"hogql"` | Custom HogQL aggregation | `math_hogql` = HogQL expression string |9899### Critical Gotchas1001011. **There is NO `"unique"` math type.** To count unique values of an event property (e.g., distinct `thread_id`), use `math: "hogql"` with `math_hogql: "count(DISTINCT properties.thread_id)"`.1021032. **`unique_group` is for GROUP ANALYTICS, not event properties.** `math: "unique_group"` with `math_property: "thread_id"` does NOT count unique thread IDs -- it silently falls back to `count()`. `math_property` must be a group type name (like `"account"`), not an event property name.1041053. **Use `math_hogql`, NOT `math_property`, for custom HogQL expressions.** `math_property` is only for `unique_group`. If you put a HogQL expression in `math_property`, it will be ignored.106107## Step-by-Step: Creating a Measure-Binding Insight1081091. **Determine the metric formula.** What are you counting? Is it a ratio (A/B)? A single count?1102. **Choose the right math types.** Map your intent to the math types table above. When in doubt, use `math: "hogql"` with an explicit expression.1113. **Build the TrendsQuery JSON.** Use the structure reference above. Set `display: "BoldNumber"`.1124. **Create the insight** via `POSTHOG_CREATE_CUSTOM_PROJECT_INSIGHTS` with the query object.1135. **Fetch the result** via `POSTHOG_CREATE_QUERY_IN_PROJECT_BY_ID` with `refresh: true` to force computation.1146. **Verify `aggregated_value`** is non-null and matches expectations. If null or 0, run the debugging checklist.1157. **Update the goal measure binding** to point to the new insight ID.116117## HogQL Pitfalls118119These are the silent failures that cost hours:120121| Pitfall | Symptom | Fix |122| --- | --- | --- |123| `properties.$session_id` | Returns NULL silently | Use `properties['$session_id']` (bracket syntax for `$`-prefixed keys) |124| `properties.thread_id` | Works fine | Dot syntax is OK for non-`$` properties |125| `'none'` sentinel | Missing values appear as literal string `'none'` | Treat as absent; filter with `IS NOT NULL AND != 'none'` |126| Shell escaping | Complex HogQL breaks when passed via CLI | Write to a temp file, or use JSON body directly via Composio |127| `OPERATION_PROPERTY_NONE` | Literal string `'none'` in results | Same as above -- it is the missing-value sentinel |128129## Common Patterns130131### Unique count of a property132133```json134{ "kind": "EventsNode", "event": "Chat Message Sent", "math": "hogql", "math_hogql": "count(DISTINCT properties.thread_id)" }135```136137### Unique sessions138139```json140{ "kind": "EventsNode", "event": "Chat Message Sent", "math": "unique_session" }141```142143### Total events (default)144145```json146{ "kind": "EventsNode", "event": "Chat Message Sent", "math": "total" }147```148149### Ratio metric (e.g., unique threads / total sessions)150151```json152{153 "query": {154 "kind": "TrendsQuery",155 "series": [156 { "kind": "EventsNode", "event": "Chat Message Sent", "math": "hogql", "math_hogql": "count(DISTINCT properties.thread_id)" },157 { "kind": "EventsNode", "event": "Chat Message Sent", "math": "unique_session" }158 ],159 "formula": "A/B",160 "dateRange": { "date_from": "-7d", "date_to": null },161 "interval": "day",162 "display": "BoldNumber"163 }164}165```166167### Filtered series (only AI-sourced messages)168169```json170{171 "kind": "EventsNode",172 "event": "Chat Message Sent",173 "math": "total",174 "properties": [175 { "key": "properties.source", "value": "ai", "operator": "exact", "type": "event" }176 ]177}178```179180## Anti-Patterns181182| Anti-pattern | Why it fails |183| --- | --- |184| Using `HogQLQuery` instead of `TrendsQuery` | Returns `[[value]]` table format -- binding cannot read `aggregated_value` |185| Using `unique_group` with an event property name | Silently falls back to `count()` -- `math_property` must be a group TYPE |186| Using `math_property` instead of `math_hogql` for custom HogQL | `math_property` is ignored for `math: "hogql"` -- use `math_hogql` |187| Forgetting `refresh: true` when fetching results | Returns stale or null cached results |188| Using `properties.$session_id` in HogQL | Returns NULL silently -- use `properties['$session_id']` |189| Using `display: "TimeSeries"` for a binding metric | May not expose `aggregated_value` -- use `"BoldNumber"` |190191## Debugging Checklist192193When a measure binding shows 0, null, or wrong value, check in order:1941951. **Is the insight a TrendsQuery?** (not HogQLQuery) -- HogQL returns tables, not `aggregated_value`.1962. **Does the result contain `aggregated_value`?** -- Fetch via `POSTHOG_CREATE_QUERY_IN_PROJECT_BY_ID` and inspect the response.1973. **Is `display` set to `"BoldNumber"`?** -- Other display types may not expose the value correctly.1984. **Are you using `math_hogql` (not `math_property`) for custom HogQL?** -- Check the series object.1995. **Are `$`-prefixed properties accessed with bracket syntax?** -- `properties['$session_id']` not `properties.$session_id`.2006. **Did you fetch with `refresh: true`?** -- Without it, you may get stale/null cached results.2017. **Is the date range correct?** -- Check `dateRange.date_from` and `dateRange.date_to`. A too-narrow range returns 0.2028. **Are there events matching the filter?** -- Verify the event exists and properties match in the PostHog UI.2039. **For formulas: does the denominator ever equal 0?** -- Division by zero returns null, not an error.204205## PostHog Composio Actions206207Key actions for measure binding work:208209| Action | Purpose |210| --- | --- |211| `POSTHOG_CREATE_CUSTOM_PROJECT_INSIGHTS` | Create a new insight with a custom TrendsQuery |212| `POSTHOG_CREATE_QUERY_IN_PROJECT_BY_ID` | Execute a query and get results (use `refresh: true`) |213| `POSTHOG_LIST_ORGANIZATION_PROJECTS` | List projects to get project IDs |214| `POSTHOG_LIST_PROJECT_GROUP_TYPES` | List group types (for `unique_group` math) |215216For the full set of 200+ PostHog actions, call `list_app_actions` with `app: "posthog"`.217218### PostHog Project IDs219220- **prod**: `371628`221- **tux**: `371881`222- API host: `https://us.posthog.com`223224## Cross-References225226- **`hamster-analytics`** -- Event instrumentation patterns (how to track events in code)227- **`hamster-instrumentation`** -- Plan-time observability checklist (what to track before building)228- **`hamster-posthog-dashboards`** -- Dashboard analysis and reporting workflows229- **`hamster-posthog-user-audit`** -- Per-user event auditing (Q1-Q8 playbook)230- Hamster docs: [Metrics](https://tryhamster.com/docs/hamster-studio/metrics) | [Goals](https://tryhamster.com/docs/hamster-studio/goals)