---
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
