PostHog

Query PostHog analytics via HogQL.

Setup

Set environment variables (add to ~/.zshenv or similar):

export POSTHOG_API_KEY="phx_..."        # Personal API key from PostHog settings
export POSTHOG_PROJECT_ID="12345"       # Project ID from PostHog URL
export POSTHOG_HOST="https://us.posthog.com"  # or https://eu.posthog.com

Get your API key: PostHog → Settings → Personal API Keys → Create

Usage

Run a HogQL query

node scripts/query.js "SELECT event, count() as cnt FROM events WHERE timestamp > now() - interval 1 day GROUP BY event ORDER BY cnt DESC LIMIT 20"

Common queries

Events today:

node scripts/query.js "SELECT event, count() as cnt FROM events WHERE timestamp > now() - interval 1 day GROUP BY event ORDER BY cnt DESC LIMIT 20"

Unique users (last 7 days):

node scripts/query.js "SELECT count(DISTINCT person_id) as unique_users FROM events WHERE timestamp > now() - interval 7 day"

Page views by URL:

node scripts/query.js "SELECT properties.\$current_url as url, count() as views FROM events WHERE event = '\$pageview' AND timestamp > now() - interval 7 day GROUP BY url ORDER BY views DESC LIMIT 20"

Events for a specific user:

node scripts/query.js "SELECT event, timestamp, properties FROM events WHERE person_id = 'xxx' AND timestamp > now() - interval 1 day ORDER BY timestamp DESC LIMIT 50"

Funnel-style sequential query:

node scripts/query.js "SELECT event, count() as cnt FROM events WHERE event IN ('\$pageview', 'sign_up', 'purchase') AND timestamp > now() - interval 30 day GROUP BY event ORDER BY cnt DESC"

Event properties breakdown:

node scripts/query.js "SELECT properties.\$browser as browser, count() as cnt FROM events WHERE event = '\$pageview' AND timestamp > now() - interval 7 day GROUP BY browser ORDER BY cnt DESC LIMIT 10"

Options

• --limit N — Override result limit (default: 100)
• --json — Output raw JSON instead of ASCII table
• --csv — Output CSV format

HogQL Reference

HogQL is PostHog's SQL dialect over ClickHouse. Key tables:

• events — all tracked events (event, timestamp, person_id, properties.*)
• persons — person profiles (id, properties.*)
• sessions — session data

Common properties accessed via properties.$prop_name:

• properties.$current_url, properties.$browser, properties.$os
• properties.$referring_domain, properties.$screen_height
• Custom properties: properties.my_custom_prop

Time functions: now(), interval N day/hour/minute, toDate(), toStartOfDay()