# Weekly Metrics

> Compute weekly community and reach metrics from platform analytics exports (Slack CSV, X/Twitter CSV, LinkedIn XLS, YouTube CSV) and the Reddit API. Use when the user wants to pull weekly numbers for Slack, X, LinkedIn, YouTube, or Reddit — including impressions, engagement rates, messages, posts, views, subscribers added, and watch time. Also use when the user asks to "update the metrics", "get this week's numbers", "fill in the sheet", or wants to aggregate daily analytics into weekly totals.

- Skill: `warpdotdev/weekly-metrics` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add warpdotdev/weekly-metrics`
- Raw SKILL.md: https://api.skillmd.com/api/skills/warpdotdev/weekly-metrics/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: warpdotdev (https://skillmd.com/u/warpdotdev)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/warpdotdev/weekly-metrics

---


# Weekly Metrics

Compute weekly metrics from platform analytics exports and the Reddit public API. At the start of every run, proactively check whether Google Workspace Sheets access is available. If it is, offer to update the metrics spreadsheet directly; otherwise return copy-paste-ready values grouped by data source.

Supported platforms: Slack, X/Twitter, LinkedIn, YouTube, Reddit, Docs (GitBook).

## Workflow

The normal flow is computing metrics for **the most recent completed week** (last Monday–Sunday). Backfilling multiple weeks is also supported.

### Step 1: Determine the target week and output mode

Ask: "Which week are we filling in?" Default to last completed Mon–Sun.
Represent weeks consistently with the destination sheet. The current metrics sheet labels columns by the week-ending Sunday date.

Before collecting inputs, proactively check whether Google Workspace Sheets tooling is available. If it is, ask the user: "It looks like I can update the sheet directly. Do you want me to update it, or would you prefer copy-paste values?" If Google Workspace Sheets tooling is not available, do not ask about direct updates; proceed with copy-paste output.

Default destinations:
- Direct spreadsheet update: your metrics spreadsheet (set `METRICS_SHEET_ID` in `.env`; e.g. `https://docs.google.com/spreadsheets/d/$METRICS_SHEET_ID/edit`)
- Copy-paste output: one tab-separated code block per metric row, grouped by data source.

### Step 2: Collect inputs

Ask for files and values. Present as a checklist so the user can provide what they have:

**Files needed:**
- Slack analytics CSV (export from Slack workspace analytics, date range covering the target week)
- X/Twitter analytics CSV (`account_overview_analytics.csv` from X Analytics/Premium)
- LinkedIn **content** analytics XLS (LinkedIn Page > Analytics > Content > Export) — this is for impressions/engagement. Do NOT confuse with the **visitors** export, which has page views/unique visitors instead.
- YouTube analytics CSV (YouTube Studio > Analytics > Advanced Mode > Export, include Subscribers, Views, Watch time columns). For content-level exports, include the video publish date.
- Docs/GitBook analytics CSV (GitBook Insights export — columns: `eventsCount`, `visitorsCount`, `datetime`)

**Fetched automatically (no user input):**
- Reddit posts and comments (via Reddit public JSON API)

Process each platform's data as it arrives — don't wait for everything.

### Step 3: Compute metrics

#### Slack (from CSV)
CSV has daily rows with date in YYYY-MM-DD format.
- **Total Members**: `Total Enabled Membership` on the Sunday
- **Active members/week**: `Weekly active people` on the Sunday
- **Members posted/week**: `Weekly people posting messages` on the Sunday
- **Messages/week**: For each day Mon–Sun, sum `Messages in public channels` + `Messages in private channels` + `Messages in shared channels` + `Messages in DMs`

#### Twitter/X (from CSV)
CSV has daily rows. Date format: `"Day, Mon DD, YYYY"`. Columns include Impressions and Engagements.
- **Impressions/week**: Sum daily `Impressions` for Mon–Sun
- **Engagement rate/week**: Sum daily `Engagements` / Sum daily `Impressions` for Mon–Sun. Express as percentage.

#### LinkedIn content (from XLS)
Binary `.xls` — parse with xlrd. The file has a `Metrics` sheet. **Row 0 is a description row** (not headers) — headers are on **row 1**. Data starts at row 2.
Columns: Date (MM/DD/YYYY), `Impressions (organic)`, `Engagement rate (organic)` (as a decimal, e.g. 0.0163 = 1.63%).
- **Impressions/week**: Sum daily `Impressions (organic)` for Mon–Sun
- **Engagement rate/week**: Impression-weighted average: `Σ(daily_impressions × daily_rate) / Σ(daily_impressions)`. Do NOT use a simple average — it over-weights low-impression days.

#### YouTube (from CSV)
Use YouTube Studio exports. Daily exports may be sorted by a metric column (not date) — normalize by date before processing. Content-level exports may list one row per video with a publish date.

For weekly metrics, only include content published **after your content-tracking start date**. Ignore videos published on or before that date, and ignore rows with no publish date unless the user explicitly says to include them.

Date formats commonly seen:
- Daily export date: `YYYY-MM-DD`
- Content export publish date: `Mon DD, YYYY`

Columns: `Subscribers`, `Views`, `Watch time (hours)`. First row after header may be a `Total` summary row — skip it when filtering by content.
- **Subscribers added/week**: Sum `Subscribers`. Note: can be negative on individual days/videos (net unsubs).
- **Views/week**: Sum `Views`
- **Watch time (hours)/week**: Sum `Watch time (hours)`. Round to 1 decimal.
- When multiple channel exports are provided, aggregate matching metrics across all files after applying the publish-date filter.

#### Reddit (automatic)
No user input needed.
- **Posts/week**: `curl -s -H "User-Agent: buzz-metrics/1.0" "https://www.reddit.com/r/your-org/new.json?limit=100"` — count posts with `created_utc` in Mon–Sun range. For backfills, paginate using the `after` token to fetch up to 200 posts (2 pages). Older weeks may still be undercounted.
- **Comments/week**: Sum `num_comments` for those posts. Comments are attributed to the post's creation week.
- **Sentiment NPS**: Fetched from **Octolens** via the `analytics` MCP tool. This is an NPS-like score: `(Positive% − Negative%)`, rounded to nearest integer.
  - **Source filter**: `src IN ('reddit', 'reddit_comment')`
  - **Relevance filter**: `rel IN (0, 1)` (High + Medium relevance only)
  - **Sentiment values**: `Positive`, `Negative`, `Neutral` (exclude empty)
  - **Formula**: `round((positive_count / total_count) * 100 - (negative_count / total_count) * 100)`
  - **SQL query pattern** (replace date range as needed):
    ```sql
    SELECT toStartOfWeek(ts, 1) AS week_start, sentiment, count() AS cnt
    FROM (
      SELECT argMax(timestamp, updated_at) AS ts,
             argMax(sentiment_label, updated_at) AS sentiment,
             argMax(source, updated_at) AS src,
             argMax(relevance_score, updated_at) AS rel
      FROM mentions WHERE is_deleted = 0
      GROUP BY org_id, id
    )
    WHERE src IN ('reddit', 'reddit_comment')
      AND ts >= '<start_date>' AND ts < '<end_date>'
      AND sentiment != ''
      AND rel IN (0, 1)
    GROUP BY week_start, sentiment
    ORDER BY week_start, sentiment
    ```
  - Present as a single integer per week (e.g. +12, −30).
  - Note: Octolens may re-score sentiment on older posts over time. If prior weeks' scores shift from previously reported values, flag the discrepancy but report the current values.

#### Docs / GitBook (from CSV)
CSV has daily rows. Date format: `"Mon DD, HH:MM AM"` (e.g. `"Feb 25, 12:00 AM"`). Year is not included — infer from context. Columns: `eventsCount`, `visitorsCount`.
- **Events/week**: Sum daily `eventsCount` for Mon–Sun. Values use comma formatting (e.g. `"9,794"`) — strip commas before parsing.
- **Visitors/week**: Sum daily `visitorsCount` for Mon–Sun.

### Step 4: Deliver results

If the user chose direct spreadsheet updates, update the spreadsheet in this order:
1. Review the relevant sheet tabs and nearby historical cells before writing. Read formulas as formulas, not only formatted values, so you can distinguish raw weekly source rows from primary dashboard formulas.
2. Update `Secondary` first. Treat `Secondary` as the source-of-truth tab for raw weekly values. Write the computed weekly deltas/rates into the column whose header is the target week-ending Sunday date. Do not write cumulative month-to-date values into `Secondary` unless the row history clearly shows that row is cumulative.
3. Update `Primary` second. Preserve and mirror existing formulas where rows are formula-driven or cumulative. For example, primary YouTube views/subscribers may be cumulative formulas over secondary weekly rows, while secondary rows hold the weekly deltas. Views and impressions are often cumulative in the primary sheet, even when secondary rows hold weekly deltas.

If the user chose copy-paste output, group results by data source:

```
Slack                    Feb 24
  Total Members           4,515
  Active/week               450
  Posted/week                85
  Messages/week             350

Reddit                   Feb 24
  Posts                       8
  Comments                   42

X/Twitter                Feb 24
  Impressions/week       73,286
  Engagement Rate         2.66%

LinkedIn                 Feb 24
  Impressions/week        8,172
  Engagement Rate         4.26%

YouTube                  Feb 24
  Subscribers added          315
  Views                  260,744
  Watch time (hrs)       2,542.6
```

For copy-paste output, provide a **single-row tab-separated copyable code block** for each metric row. Never combine multiple metrics into a single code block. The user copies one block at a time and pastes into the corresponding row in Sheets.

Label each code block with the metric name, then the copyable block:

Total Members:
```
4515	4620	4669
```

Active/week:
```
450	395	412
```

No `printf` or `pbcopy` commands — just raw values separated by literal tabs.

### Step 5: Validate

Ask: "Do these numbers look right?"

Common issues:
- Slack CSV doesn't cover the full week
- X or LinkedIn exports missing days (downloaded mid-week)
- Reddit post count low (API returns max ~100 recent posts)
- YouTube CSV may not be date-sorted — always sort by date before slicing into weeks
- YouTube content exports may include old evergreen videos; filter to videos published after your content-tracking start date
- YouTube subscriber count can go negative on individual days
- Views and impressions may be cumulative in `Primary` but weekly deltas in `Secondary`; confirm the row pattern before writing

## Important notes
- Week boundaries: **Monday–Sunday**, matching sheet column headers. The user may label weeks by their Sunday (last day) rather than Monday — either convention is fine, just be consistent with what they provide.
- For Slack's `Weekly active people` / `Weekly people posting messages`: use the **Sunday** value (reflects the Mon–Sun window).
- Date formats differ across exports — normalize before matching:
  - Slack: `YYYY-MM-DD`
  - X: `"Day, Mon DD, YYYY"` (e.g. `"Fri, Feb 21, 2026"`)
  - LinkedIn: `MM/DD/YYYY`
  - YouTube daily exports: `YYYY-MM-DD`
  - YouTube content exports: `Mon DD, YYYY`
  - GitBook: `"Mon DD, HH:MM AM"` (no year)
- **Multi-week backfills**: When computing many weeks at once, use a single Python script that processes all platforms and weeks in one pass. This is faster and less error-prone than manual per-week computation.
- **LinkedIn export confusion**: The *Visitors* export (`your-org_visitors_*.xls`) has page views / unique visitors — NOT impressions or engagement rate. The *Content* export is a separate download from LinkedIn Page > Analytics > Content > Export. If the user provides only the visitors file, flag that impressions/engagement will be missing.