AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

querying-posthog-data

Explains how to query PostHog data, defaulting to typed runners for supported product analytics. Read it before you write HogQL/SQL. Also read it before you call execute-sql against PostHog. Use it to find or aggregate PostHog entities. These entities include insights, dashboards, cohorts, feature flags, experiments, surveys, hog flows, warehouse data, and persons. Use it for trends, funnels, retention, lifecycle, paths, stickiness, web analytics, error tracking, logs, sessions, and LLM traces. Before you calculate a governed business or telemetry measure, check system.information_schema.metrics for an approved definition. Examples include MRR, activation, billable usage, active organizations, and failure rates. Use the approved definition before you derive a measure from raw events or use a typed domain tool. It also covers HogQL differences, system table schemas, functions, query examples, and schema discovery.

Original source
posthog
License
MIT
Pinned revision
cac7c80a9e5b73484d18eda1bffb47297c938e0c

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read posthog--querying-posthog-data at revision cac7c80a9e5b73484d18eda1bffb47297c938e0c, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 65

references/models-messaging-opt-outs.md

Messaging opt-outs

Message recipient preferences (system.message_recipient_preferences)

Messaging preferences per recipient, one row per recipient. The preferences map records opt-outs and opt-ins per message category.

Columns

Column | Type | Nullable | Description --- | --- | --- | --- id | String | NOT NULL | Preference row UUID. team_id | Integer | NOT NULL | identifier | String | NOT NULL | Recipient identifier, usually an email address. preferences | JSON | NOT NULL | JSON map of message category ID to 'OPTED_OUT' or 'OPTED_IN'. The key '$all' covers all marketing messages; other keys are message_categories ids. deleted | Integer | NOT NULL | 1 if the row has been deleted, 0 otherwise. created_at | DateTime | NOT NULL | When the recipient was first recorded. updated_at | DateTime | NOT NULL | When the recipient's preferences last changed.

Message categories (system.message_categories)

Message categories recipients can opt out of, one row per category. Category IDs are the keys in message_recipient_preferences.preferences.

Columns

Column | Type | Nullable | Description --- | --- | --- | --- id | String | NOT NULL | Category UUID, used as the key in recipient preferences. team_id | Integer | NOT NULL | key | String | NOT NULL | Stable category key used in the API, e.g. 'newsletter'. name | String | NOT NULL | Display name of the category. description | String | NOT NULL | Internal description of the category. public_description | String | NOT NULL | Description shown to recipients on the preferences page. category_type | String | NOT NULL | 'marketing' (opt-out applies) or 'transactional'. deleted | Integer | NOT NULL | 1 if the category has been deleted, 0 otherwise. created_at | DateTime | NOT NULL | When the category was created. updated_at | DateTime | NOT NULL | When the category was last updated.

Query Examples

-- Recipients opted out of all marketing messages
SELECT identifier, updated_at
FROM system.message_recipient_preferences
WHERE deleted = 0 AND JSONExtractString(preferences, '$all') = 'OPTED_OUT'
ORDER BY updated_at DESC

-- Opt-out counts per category
SELECT c.key, c.name, count() AS opted_out
FROM system.message_recipient_preferences AS p
JOIN system.message_categories AS c ON JSONExtractString(p.preferences, toString(c.id)) = 'OPTED_OUT'
WHERE p.deleted = 0 AND c.deleted = 0
GROUP BY c.key, c.name
ORDER BY opted_out DESC

-- Is a specific recipient opted out of a category (falling back to the all-marketing flag)?
SELECT identifier,
       JSONExtractString(preferences, (SELECT toString(id) FROM system.message_categories WHERE key = 'newsletter')) AS category_status,
       JSONExtractString(preferences, '$all') AS all_marketing_status
FROM system.message_recipient_preferences
WHERE deleted = 0 AND identifier = '[email protected]'