Posthog CLI Queries

PostHog CLI Queries

Query this project's PostHog data from the terminal without guessing command syntax.

When To Use

• The user asks to inspect PostHog events, trends, or dashboard data
• The task needs real data from this project's PostHog environment
• The user wants a repeatable terminal command instead of clicking in the PostHog UI

Preconditions

• posthog-cli must be installed and authenticated
• Query commands need a personal API key with query:read
• Dashboard API helpers read ~/.posthog/credentials.json
• This project currently uses PostHog environment 292804 on https://us.posthog.com, but scripts read the active local credentials instead of hardcoding values

Default Workflow

1. Verify auth:

posthog-cli exp query run 'SELECT 1 AS ok'

2. For event data, use posthog-cli exp query run '<hogql>'
3. For dashboard metadata or cached dashboard insight results, use the scripts in this skill because the CLI has no dedicated dashboard command
4. Return:
   • the exact command used
   • the key rows or aggregates
   • any metric caveats such as partial-day data, test-account filtering, or missing properties

Shell quoting rule:

• Use single quotes around HogQL when the query contains properties like $app_version or $os, otherwise the shell may expand them before the CLI sees the query

Common Queries

Recent events:

posthog-cli exp query run 'SELECT event, timestamp FROM events ORDER BY timestamp DESC LIMIT 20'

Top events in the last 7 days:

posthog-cli exp query run 'SELECT event, count() AS c FROM events WHERE timestamp > now() - INTERVAL 7 DAY GROUP BY event ORDER BY c DESC LIMIT 15'

Top pageview pages in the last 7 days:

posthog-cli exp query run "SELECT properties.page_name AS page_name, count() AS c FROM events WHERE event = 'pageview' AND timestamp > now() - INTERVAL 7 DAY GROUP BY page_name ORDER BY c DESC LIMIT 15"

Top click targets in the last 7 days:

posthog-cli exp query run "SELECT properties.element_name AS element_name, count() AS c FROM events WHERE event = 'click' AND timestamp > now() - INTERVAL 7 DAY GROUP BY element_name ORDER BY c DESC LIMIT 15"

Recent app versions from events:

posthog-cli exp query run 'SELECT properties.$app_version AS app_version, count() AS c FROM events WHERE timestamp > now() - INTERVAL 7 DAY GROUP BY app_version ORDER BY c DESC LIMIT 20'

Recent OS breakdown:

posthog-cli exp query run 'SELECT properties.$os AS os, count(DISTINCT person_id) AS users FROM events WHERE timestamp > now() - INTERVAL 7 DAY GROUP BY os ORDER BY users DESC LIMIT 20'

Hourly volume in the last 24 hours:

posthog-cli exp query run 'SELECT toStartOfHour(timestamp) AS hour, count() AS c FROM events WHERE timestamp > now() - INTERVAL 24 HOUR GROUP BY hour ORDER BY hour DESC LIMIT 24'

Dashboard Commands

List dashboards:

bash .agents/skills/posthog-cli-queries/scripts/dashboard_list.sh

Fetch a dashboard as JSON:

bash .agents/skills/posthog-cli-queries/scripts/dashboard_fetch.sh 1075953

Fetch a dashboard summary:

bash .agents/skills/posthog-cli-queries/scripts/dashboard_fetch.sh 1075953 --summary

Current known dashboard:

bash .agents/skills/posthog-cli-queries/scripts/dashboard_fetch.sh 1075953 --summary

Dashboard Analysis Notes

• Dashboard results are usually cached insight payloads, so last_refresh matters
• Do not compare a partial current day against a full previous day without saying so explicitly
• Check filterTestAccounts before comparing tiles with each other
• If $os or $app_version has a large null bucket, call out that the property coverage is incomplete
• If event names overlap like app_open and Application Opened, mention that the taxonomy is split

Useful jq Snippets

Extract tile names from a fetched dashboard JSON:

jq -r '.tiles[] | select(.insight != null) | [.id, .insight.id, .insight.name] | @tsv'

Show top breakdown rows from one insight result:

jq -r '.tiles[] | select(.insight.id==6334935) | .insight.result[] | [.label, .count, (.data[-1] // 0)] | @tsv'

Failure Handling

• If posthog-cli exp query run says missing required scope 'query:read', fix the personal API key scopes first
• If a query times out, narrow the date range or aggregate more aggressively
• If dashboard scripts fail, confirm ~/.posthog/credentials.json exists and contains host, token, and env_id