weekly-metrics

name

weekly-metrics

description

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.

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

    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.