OpenParable
Skip to content
← Back to skills

Posthog CLI Queries

Apps & Automation
posthog-cli-queries

Posthog CLI Queries is an Agent Skill for apps & automation published in the debugtheworldbot/keyStats repository on GitHub: a SKILL.md instruction file an AI agent can load to do this task.

Use when querying this project's PostHog data from the terminal, including recent events, event breakdowns, active-user metrics, and dashboard metadata or cached insight results.

View on GitHub ↗
QUICK START

How to use this skill

Bring this guide into your coding agent with a prompt tailored to the tool you use.

  1. Open your project in Codex.
  2. Copy and paste the prompt below.
  3. Review files and risks before approving any action.
Prompt to paste
Inspect the Agent Skill at https://github.com/debugtheworldbot/keyStats/blob/HEAD/.agents/skills/posthog-cli-queries/SKILL.md and all supporting files. Report provenance, permissions, scripts, dependencies, and security risks. Propose an exact installation into .agents/skills/posthog-cli-queries, but wait for my approval before writing files or running scripts.

Copying this prompt does not install or run the skill. Review third-party files before use.

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'
  1. For event data, use posthog-cli exp query run '<hogql>'
  2. For dashboard metadata or cached dashboard insight results, use the scripts in this skill because the CLI has no dedicated dashboard command
  3. 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