metric-quarterly-rocks

name: metric-quarterly-rocks description: Use when computing or validating Jay's Q2 2026 Rocks metrics (Home WAU and Board Member Weekly Login Rate) - defines exact, reproducible Pendo query specifications and aggregation rules so weekly runs are byte-for-byte comparable across time allowed-tools: Read, Bash

Quarterly Rocks — Metric Definitions

Purpose

Lock the calculation methodology for Jay's two Q2 2026 EOS Rocks so weekly reporting is reproducible and trend lines remain comparable across time. This skill is the single source of truth for how these metrics are computed; it does not handle persistence or reporting (the /update-rocks command does that).

When to Use This Skill

Activate automatically when:

• Computing Home WAU or Board Member Weekly Login Rate for any reporting period
• Validating numbers in the L10 Rocks tab against fresh Pendo data
• Backfilling a missed week
• Spot-checking historical values during methodology audits
• Called by the /update-rocks command

When NOT to use:

• Defining metrics for a new feature or product (use metrics-definition workflow instead)
• Generating a metric target — targets are set out-of-band; this skill only computes current values
• Pulling Pendo data for non-Rocks analysis (use pendo-analytics directly)

Conventions (Locked)

These conventions are non-negotiable. Changing any of them invalidates trend comparisons and must be a deliberate methodology decision documented in this file.

Metric 1 — Home WAU (4-week rolling average)

Definition

Mean unique identified visitors per Sun-Sat week, averaged across the 4 most recent complete weeks where the week's Saturday end-date is on or before as_of_date. No segment filter — counts all identified visitors in Home (board members and non-board users alike).

Formula

WAU = mean(week_uniques[w-3], week_uniques[w-2], week_uniques[w-1], week_uniques[w])

where w is the Sun-Sat week ending on as_of_date, and each week_uniques is unique visitor count for that bucket.

Pendo query (literal call)

Single query with weekly bucketing:

mcp__claude_ai_Pendo__activityQuery(
  subId="4818486697721856",
  appId="5961191088521216",
  entityType="visitor",
  dateRange={
    range: "custom",
    startDate: <as_of_date - 27 days>,   # Sunday 4 weeks before
    endDate:   <as_of_date>              # Saturday
  },
  period="weekly",
  group=["visitorId"],
  count=true
)

The response returns 4 weekly buckets in results[].rows[].count. Sum all 4 counts and divide by 4. Round to nearest integer.

Spreadsheet output format

Text string in the WAU row cell. Format: "{wau/1000:.1f}k" with a trailing .0 stripped:

• 504,000 → "504k"
• 504,600 → "504.6k"
• 502,000 → "502k"

Lowercase k, no commas. Existing data in the workbook has minor case inconsistency ("505K" in col H) — going forward we standardize on lowercase k.

Q2 target

540k (+13% vs March 2026 baseline of 478k).

Metric 2 — Board Member Weekly Login Rate

Definition

Unique board members active in trailing 7 days inclusive of as_of_date, divided by unique board members active in trailing 90 days inclusive of as_of_date. A measure of stickiness within the board-member population.

Formula

rate = unique_board_members_7d / unique_board_members_90d

Filter

Pendo segment Home | Board Members (segmentId ruTWzJHTx86HCgbOfdAP80T85UQ). Functionally equivalent to metadata.agent.isboardmember == true but always use the segment id — the segment is the canonical definition managed in Pendo and may evolve.

Pendo queries (two calls)

# Numerator — 7-day window
mcp__claude_ai_Pendo__activityQuery(
  subId="4818486697721856",
  appId="5961191088521216",
  entityType="visitor",
  segmentId="ruTWzJHTx86HCgbOfdAP80T85UQ",
  dateRange={ range:"custom", startDate:<as_of_date - 6 days>, endDate:<as_of_date> },
  group=["visitorId"],
  count=true
)

# Denominator — 90-day window
mcp__claude_ai_Pendo__activityQuery(
  subId="4818486697721856",
  appId="5961191088521216",
  entityType="visitor",
  segmentId="ruTWzJHTx86HCgbOfdAP80T85UQ",
  dateRange={ range:"custom", startDate:<as_of_date - 89 days>, endDate:<as_of_date> },
  group=["visitorId"],
  count=true
)

Computation

rate = numerator_count / denominator_count. Keep 4 decimal places of precision (e.g., 0.3201).

Spreadsheet output format

Float decimal in the Stickiness row cell. Existing cell style applies a percent format, so 0.3201 renders as 32.01%. Do not multiply by 100; do not write a string with %. Always write the raw decimal.

Q2 target

40% (i.e., 0.40).

Validation Reference Points

These points were validated this session against existing L10 column data. Future runs should sanity-check against these for methodology integrity. Differences of >1pp indicate the calculation drifted.

Variations of 0.04–0.5pp across these checkpoints are expected — caused by Pendo's late-arriving identified-visitor backfill, not methodology drift.

Common Mistakes

Anti-Rationalization

Success Criteria

A run of either metric is correct when:

• as_of_date resolves to a Saturday on or before today
• WAU query returns exactly 4 weekly buckets (not 3, not 5)
• Stickiness numerator and denominator both come from segmentId="ruTWzJHTx86HCgbOfdAP80T85UQ"
• All windows are inclusive of as_of_date
• All outputs reference the validation table above and are within reasonable distance (no order-of-magnitude jumps without explanation)
• Output formats match the spreadsheet conventions (text "504k" for WAU, decimal 0.3201 for stickiness)

Related Skills

• pendo-analytics (context-assembly) — Defines MCP connection, available tools, and query patterns. This skill specializes those patterns for the two Rocks metrics.
• metrics-definition (workflow) — Used when defining new metrics; not used for the established Rocks metrics.

Integration Points

Called by:

• /update-rocks command — runs both metrics for the active reporting week and writes results to the L10 Rocks tab.

Calls these skills:

• pendo-analytics for MCP connection conventions (subId, appId references).