Count PostHog weekly active users, new and returning

Every Monday: last week's active users from PostHog, split into new and returning, with the change against the week before.

Runs
Mondays at 8:00
Uses
PostHog
Posts
Stat tile

From your apps to your board

How the skill works: your agent reads last week from PostHog; posthog project id, posthog host from What you tell it, mondays at 8:00, and gives you weekly active users and the report in the chat.

Reads

  • PostHogLast week
  • What you tell itPostHog project ID, PostHog host
reads
Your agentClaude Code, Codex, Cursor or GeminiMondays at 8:00
posts

You get

  • Weekly active usersA stat tile on your board, with its trend
  • The report in the chatEvery run, with or without Briefkin

What you get

  • Weekly active users for the last full Monday to Sunday week, and the change.
  • New and returning, with the share of active users who came back.
  • Four weeks side by side for context.
  • What drove it: the initial source behind new users when they move by more than 15%.

The card on the right is a sample. Yours shows your own numbers and updates in place every run.

What lands on your board

acme-webWeekly active users8,412+6% vs previous period, bettervs last report
New1,936+21% vs previous period
Returning6,476+2.0% vs previous period
Returning share77%
An example with made-up numbers. Your agent posts your real ones.

What it does

WAU is the first number a founder looks at on Monday and the one most likely to be quoted wrong, because "active" quietly changes meaning between dashboards. One counts every pageview, another only signed-in users, a third includes your own team.

This skill pins the definition down in one HogQL query: distinct people with at least one of the events you choose, in the last full Monday to Sunday week, with internal users filtered out. It then splits them into people first seen that week and people who came back, so growth and retention show up as separate lines.

How it works

  1. 1

    1Define active

    Pick the events that mean someone used the product: $pageview on the app host, $screen for mobile, or a key event such as project opened. Exclude internal users with the project's test account filters, or with person.properties.email NOT LIKE '%@example.com'.

  2. 2Count last week

    POST /api/projects/:project_id/query/ with a HogQLQuery such as SELECT count(DISTINCT person_id) FROM events WHERE event IN ('$pageview', '$screen') AND timestamp >= toStartOfWeek(now(), 1) - INTERVAL 7 DAY AND timestamp < toStartOfWeek(now(), 1). Mode 1 of toStartOfWeek starts weeks on Monday.

  3. 3Split new from returning

    In the same query, count people whose person.created_at falls inside the week as new and everyone else as returning. Run it again with both dates moved back 7 days, so each split has a comparison.

  4. 4Explain a big jump

    If new users moved by more than 15%, break them down by person.properties.$initial_utm_source (or $initial_referring_domain) to name the campaign or site behind the change.

  5. 5

    5Post the count

    WAU against the week before, new and returning as stats with their own changes, a four-week table, and the source behind any big jump.

  6. Post it to the board

    With BRIEFKIN_TOKEN set, the agent sends the result as one card that updates in place every run. Without it, you get the report in the chat.

Install and run it

Pick your agent, where the data comes from and how to schedule it; the commands below follow.

Agent
Data
Schedule
  1. Install the skill

    One command. It puts the SKILL.md in ~/.claude/skills and in ~/.agents/skills, which Codex, Cursor, Gemini CLI, Copilot CLI and OpenCode read too.

    shellcurl -fsSL https://briefkin.dev/skills/posthog-weekly-active-users/install | sh
  2. Run it

    Ask Claude Code:

    promptRun the posthog-weekly-active-users skill

    Or paste the prompt instead of installing:

    promptUsing PostHog (HogQL through the query API), count distinct people with a $pageview or $screen event in the last full Monday to Sunday week, excluding internal and test users. Split them into new (person created that week) and returning, and do the same for the week before so I get the change in each. Show the last 4 weeks as a small table. If new users moved by more than 15%, break them down by initial UTM source or referring domain and tell me what drove it.
  3. Put it on a schedule

    Mondays at 8:00, from cron on any machine where Claude Code is signed in:

    crontab0 8 * * 1 claude -p "Run the posthog-weekly-active-users skill"

    Add it with crontab -e. A laptop asleep at that time runs it when it wakes if you use launchd or Task Scheduler instead; GitHub Actions works for the API route.

  4. Send it to your board

    Connect Briefkin once with curl -fsSL https://briefkin.dev/install | sh and every run lands on your board as a stat tile. How setup works

View the SKILL.mdRaw file
SKILL.md---
name: posthog-weekly-active-users
description: "Every Monday: last week's active users from PostHog, split into new and returning, with the change against the week before. Use when the user asks for this, or when it runs on a schedule."
---

# PostHog weekly active users

Every Monday: last week's active users from PostHog, split into new and returning, with the change against the week before.

WAU is the first number a founder looks at on Monday and the one most likely to be quoted wrong, because "active" quietly changes meaning between dashboards. One counts every pageview, another only signed-in users, a third includes your own team.

This skill pins the definition down in one HogQL query: distinct people with at least one of the events you choose, in the last full Monday to Sunday week, with internal users filtered out. It then splits them into people first seen that week and people who came back, so growth and retention show up as separate lines.

## Two ways to get the data

Use the API or MCP route when you have an MCP server, a CLI or an API token for the app. Otherwise, if you can drive a browser the user is signed in to (Claude in Chrome, a Playwright or Chrome DevTools MCP server), use the browser route. Say which one you used.

## Route A: API or MCP server

### What you need

- A PostHog personal API key with the `query:read` scope in `POSTHOG_API_KEY`, the project ID, and your host (`us.posthog.com`, `eu.posthog.com` or your own). A PostHog MCP server or connector works too.

If something is missing, say what and stop; do not invent numbers.

### Steps

1. **Define active**: Pick the events that mean someone used the product: `$pageview` on the app host, `$screen` for mobile, or a key event such as `project opened`. Exclude internal users with the project's test account filters, or with `person.properties.email NOT LIKE '%@example.com'`.
2. **Count last week** (PostHog): `POST /api/projects/:project_id/query/` with a `HogQLQuery` such as `SELECT count(DISTINCT person_id) FROM events WHERE event IN ('$pageview', '$screen') AND timestamp >= toStartOfWeek(now(), 1) - INTERVAL 7 DAY AND timestamp < toStartOfWeek(now(), 1)`. Mode 1 of `toStartOfWeek` starts weeks on Monday.
3. **Split new from returning** (PostHog): In the same query, count people whose `person.created_at` falls inside the week as new and everyone else as returning. Run it again with both dates moved back 7 days, so each split has a comparison.
4. **Explain a big jump** (PostHog): If new users moved by more than 15%, break them down by `person.properties.$initial_utm_source` (or `$initial_referring_domain`) to name the campaign or site behind the change.
5. **Post the count**: WAU against the week before, new and returning as stats with their own changes, a four-week table, and the source behind any big jump.

## Route B: the browser

### What you need

- Signed in to PostHog as a member of the organization with access to the project (any project role can run SQL insights).

If something is missing, say what and stop; do not invent numbers.

### Steps

Start at https://us.posthog.com/. The user is signed in already; never type passwords or codes, and ask if a sign-in page appears.

1. **Open the project and a SQL insight**: PostHog URLs include the project ID, so start at the home page (`eu.posthog.com` for EU projects), pick the project in the top-left project switcher, then open **Product analytics**, click **New**, and choose the **SQL** tab.
2. **Run last week's count**: Paste `SELECT count(DISTINCT person_id) FROM events WHERE event IN ('$pageview', '$screen') AND timestamp >= toStartOfWeek(now(), 1) - INTERVAL 7 DAY AND timestamp < toStartOfWeek(now(), 1)` and run it. Turn on **Filter out internal and test users** above the query, or add your own `person.properties.email NOT LIKE '%@example.com'` clause.
3. **Split new from returning**: Add `countIf(person.created_at >= toStartOfWeek(now(), 1) - INTERVAL 7 DAY)` as new and the remainder as returning, then move both dates back 7 days and run again for the week before. Repeat twice more for a four-week table.
4. **Explain a jump**: If new users moved by more than **15%**, run the new-user count grouped by `person.properties.$initial_utm_source` (or `$initial_referring_domain`) for last week, ordered by count, and name the top sources.
5. **Write the count up**: WAU against the week before, new and returning with their own changes, the four-week table and the source behind any jump.

Note: The SQL insight shows up to a few thousand rows in the UI, which is plenty for aggregated counts. The weekly active users line in a Trends insight uses a rolling 7 days, so it will not match this fixed-week count.

## The report

- **Weekly active users** for the last full Monday to Sunday week, and the change.
- **New and returning**, with the share of active users who came back.
- **Four weeks** side by side for context.
- **What drove it**: the initial source behind new users when they move by more than 15%.

## Post it to Briefkin

When `BRIEFKIN_TOKEN` is set, or `~/.config/briefkin/env` exists (the installer from https://briefkin.dev/docs writes it), also send the result to the user's Briefkin board as one card. Keep the `item_key` the same on every run so the card updates in place, use a new `event_id` per run, and put today's real numbers in place of the example ones. If the request fails, say so once and carry on.

```bash
[ -n "${BRIEFKIN_TOKEN:-}" ] || . ~/.config/briefkin/env
curl -sS -X POST "${BRIEFKIN_URL:-https://briefkin.dev}/v1/events" \
  -H "authorization: Bearer $BRIEFKIN_TOKEN" \
  -H "content-type: application/json" \
  -d @- <<'JSON'
{
  "v": 1,
  "event_id": "posthog-weekly-active-users/<date of this run, e.g. 2026-10-16>",
  "item_key": "posthog-weekly-active-users",
  "project": "acme-web",
  "source": "PostHog weekly active users skill",
  "kind": "metric",
  "title": "8,412 weekly active users, up 6%",
  "metric": {
    "label": "Weekly active users",
    "value": 8412,
    "better": "higher",
    "period_start": "<start of the period, ISO 8601>",
    "period_end": "<end of the period, ISO 8601>"
  },
  "glance": {
    "type": "Grid",
    "props": {
      "columns": 3
    },
    "children": [
      {
        "type": "Stat",
        "props": {
          "label": "New",
          "value": 1936,
          "delta": 21
        }
      },
      {
        "type": "Stat",
        "props": {
          "label": "Returning",
          "value": 6476,
          "delta": 2
        }
      },
      {
        "type": "Stat",
        "props": {
          "label": "Returning share",
          "value": 77,
          "unit": "%"
        }
      }
    ]
  },
  "widget": [
    {
      "type": "Table",
      "props": {
        "columns": [
          "Week of",
          "Active",
          "New",
          "Returning"
        ],
        "align": [
          "left",
          "right",
          "right",
          "right"
        ],
        "rows": [
          [
            "Sep 14",
            7801,
            1512,
            6289
          ],
          [
            "Sep 21",
            7935,
            1580,
            6355
          ],
          [
            "Sep 28",
            7944,
            1600,
            6344
          ],
          [
            "Oct 5",
            8412,
            1936,
            6476
          ]
        ]
      }
    },
    {
      "type": "Bars",
      "props": {
        "items": [
          {
            "label": "Organic search",
            "value": 702
          },
          {
            "label": "Direct",
            "value": 488
          },
          {
            "label": "Partner webinar",
            "value": 336
          },
          {
            "label": "Newsletter",
            "value": 214
          },
          {
            "label": "LinkedIn",
            "value": 196
          }
        ],
        "unit": "new users"
      }
    },
    {
      "type": "Callout",
      "props": {
        "text": "New users up 21%: the partner webinar on Oct 7 brought 336 of them.",
        "tone": "info"
      }
    }
  ],
  "size": "md"
}
JSON
```

Widgets come from the Briefkin catalog (https://briefkin.dev/v1/catalog). Without a token, just give the user the report.

Set `period_start` and `period_end` to the window the number covers: the tile names it from them ("Last 7 days", "Today", "This month"). For a count right now (open items, a balance), send `"period_label": ""` so no period shows; for another name, send it as `period_label` (at most 24 characters).

## Run it on a schedule

Suggested: Mondays at 8:00 (`0 8 * * 1`). From cron, with the agent signed in on that machine:

```bash
0 8 * * 1 claude -p "Run the posthog-weekly-active-users skill"
# Codex: codex exec, Gemini CLI: gemini -p, Copilot CLI: copilot -p, OpenCode: opencode run
```

Or use the agent's own scheduler (Claude Code routines, Codex automations, Claude or ChatGPT scheduled tasks): https://briefkin.dev/skills/posthog-weekly-active-users#schedule

Source: https://briefkin.dev/skills/posthog-weekly-active-users

What your agent needs

  • A PostHog personal API key with the query:read scope in POSTHOG_API_KEY, the project ID, and your host (us.posthog.com, eu.posthog.com or your own). A PostHog MCP server or connector works too.

Connecting PostHog

  1. PostHog MCP server, PostHog's official remote server at https://mcp.posthog.com/mcp. Sign in with OAuth, or let the PostHog Wizard install it in your client; it routes to the US or EU cloud based on your account. Clients without OAuth can send a personal API key made with the MCP Server preset as a Bearer token (docs).
  2. Query API with a personal API key. Create one in your PostHog account settings, store it in POSTHOG_API_KEY with POSTHOG_PROJECT_ID and POSTHOG_HOST (https://us.posthog.com or https://eu.posthog.com), and give it the query:read scope, plus insight:read or feature_flag:read if the report uses them.

Add ?readonly=true to the MCP URL (or send x-posthog-read-only: true) to drop every tool that creates, updates or deletes; on the API, read scopes alone do the same.

Questions

Why does this differ from the weekly active users line in PostHog trends?

Trends with weekly active users math counts a rolling 7 days ending on each day. This skill counts a fixed Monday to Sunday week, so the numbers line up week over week. Both are right; they answer slightly different questions.

Are anonymous visitors counted?

Only if their events match the definition. If the app sits behind a login, $pageview on the app host already means signed-in users. When a marketing site and the app share one project, filter on properties.$host so homepage visitors are not counted as product users.

Do I need Briefkin to use the PostHog weekly active users skill?

No. The skill runs with your own agent and gives you the report in the chat. With a Briefkin token set, each run also posts it to your board as a stat tile, so you can glance at it instead of asking again.

Which agents can run it?

Claude Code, Codex, Cursor, Gemini CLI, GitHub Copilot CLI, OpenCode, Windsurf and Amp all read SKILL.md files. The install command puts it in ~/.claude/skills and ~/.agents/skills, where they look. ChatGPT, the Claude app and Grok take the prompt instead. Pick your agent under "Install and run it" and the commands change to match.

Can it work without an API or MCP server, from my browser?

Yes. Switch the data source to "Your browser" and the steps change to the PostHog pages themselves: the agent opens us.posthog.com in a browser you are signed in to and reads the numbers off the page. Claude Code, Codex, Cursor, Claude app and ChatGPT can drive a browser out of the box; Gemini CLI, Copilot CLI and OpenCode need a Playwright or Chrome DevTools MCP server first. It is slower than the API and needs you signed in, so it suits a report you run by hand or from a scheduler on your own computer.

Watch weekly active users on your board, run after run

Skills are free to use with any agent. Briefkin is where their results land: one board for every report, number and question your agents send, with a ping when something needs you.

Free during the open beta. No card.