--- name: google-sheets-kpi-tracker description: "Fridays: each KPI in your tracking sheet against its target, which ones are behind, which moved since last week, and which rows nobody updated. Use when the user asks for this, or when it runs on a schedule." --- # Google Sheets KPI tracker report Fridays: each KPI in your tracking sheet against its target, which ones are behind, which moved since last week, and which rows nobody updated. Plenty of companies run on a KPI sheet: one row per metric, an owner, a target and this week's actual, typed in by whoever owns it. The sheet is the source of truth, and reading it properly every week (which rows are green, which slipped, which nobody touched) is the part that slides. The agent reads the sheet as it is, judges each KPI against its own target and direction, and posts the count on target with the misses spelled out. Stale rows get called out instead of being judged on last month's number. ## 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 - Read access to the spreadsheet: share it with the agent's Google account or service account (Viewer is enough), and give the agent the spreadsheet ID from its URL. - A Google Sheets connector or MCP server, or the Sheets API with `GOOGLE_APPLICATION_CREDENTIALS` pointing at the service account key. If something is missing, say what and stop; do not invent numbers. ### Steps 1. **Read the KPI tab** (Google Sheets): Call `spreadsheets.values.get` on `KPIs!A1:G50` with `valueRenderOption=UNFORMATTED_VALUE`, so 31% comes back as `0.31`. Use the first row as headers: KPI, Owner, Target, Direction, Actual, Last week, Updated. 2. **Check each row is usable**: Skip rows with an empty Actual. If Updated is more than 8 days old, mark the KPI as stale rather than judging it on an old number. 3. **Judge against target**: With Direction `higher`, on target means Actual at or above Target; with `lower`, at or below. A miss within 10% of target is close, anything further is behind. Compare Actual with Last week for the trend. 4. **Write the report**: KPIs on target out of the total, then each miss with owner, target, actual and the change since last week, then stale rows grouped by owner. ## Route B: the browser ### What you need - Signed in to Google with an account that has at least Viewer access to the spreadsheet. If something is missing, say what and stop; do not invent numbers. ### Steps Start at https://docs.google.com/spreadsheets/. The user is signed in already; never type passwords or codes, and ask if a sign-in page appears. 1. **Open the KPI sheet**: Open `https://docs.google.com/spreadsheets/`, open the KPI tracker (or paste its URL), and click the **KPIs** tab at the bottom. Read rows 1 to 50 of columns A to G: KPI, Owner, Target, Direction, Actual, Last week, Updated. 2. **Check each row is usable**: Skip rows with an empty Actual. Compare the Updated date with today; more than 8 days old is stale and gets reported rather than judged. Cells show formatted values, so `31%` reads as a percentage; click a cell and read the formula bar when a number is ambiguous. 3. **Judge against target**: With Direction `higher`, on target means Actual at or above Target; with `lower`, at or below. A miss within 10% of target is close, further is behind. Compare Actual with Last week for the trend. 4. **Write the report**: KPIs on target out of the total, each miss with owner, target, actual and trend, the stale rows grouped by owner, and the one KPI to raise first. Do not type into the sheet. Note: The sheet shows formatted values, so a target of `≥ 40%` written as text and an actual of `31%` need reading as numbers. On a very wide sheet, File > Download > Comma-separated values gives the agent the whole tab at once. ## The report - **KPIs on target** out of the total, and the change since last week. - Each KPI that is behind or close: owner, target, actual, trend. - KPIs whose row hasn't been updated in over a week, by owner. - The one KPI to discuss first, usually the biggest miss that is also getting worse. ## 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": "google-sheets-kpi-tracker/", "item_key": "google-sheets-kpi-tracker", "project": "Acme", "source": "Google Sheets KPI tracker report skill", "kind": "metric", "title": "7 of 10 KPIs on target, activation furthest behind", "metric": { "label": "KPIs on target", "value": 7, "unit": "of 10", "better": "higher", "period_start": "", "period_end": "" }, "glance": { "type": "Badges", "props": { "items": [ { "label": "Activation 31% vs 40%", "tone": "bad" }, { "label": "NPS 34 vs 40", "tone": "bad" }, { "label": "Weekly signups 412 vs 450", "tone": "warn" } ] } }, "widget": [ { "type": "Table", "props": { "columns": [ "KPI", "Owner", "Target", "Actual", "Status" ], "rows": [ [ "Activation rate", "Priya", "≥ 40%", "31%", "Behind" ], [ "NPS", "Sam", "≥ 40", "34", "Behind" ], [ "Weekly signups", "Maya", "≥ 450", "412", "Close" ], [ "MRR", "Alex", "≥ $45,000", "$48,210", "On target" ], [ "Trial conversion", "Maya", "≥ 35%", "42%", "On target" ], [ "Logo churn", "Alex", "≤ 3%", "2.4%", "On target" ], [ "First response time", "Sam", "≤ 4 h", "2.6 h", "On target" ], [ "Uptime", "Jordan", "≥ 99.9%", "99.95%", "On target" ], [ "CAC payback", "Alex", "≤ 12 mo", "9 mo", "On target" ], [ "Burn multiple", "Alex", "≤ 2.0", "1.7", "On target" ] ] } }, { "type": "Callout", "props": { "text": "Activation fell from 34% to 31% this week and is the biggest miss. Start Monday's meeting there.", "tone": "warn" } } ], "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: Fridays at 15:00 (`0 15 * * 5`). From cron, with the agent signed in on that machine: ```bash 0 15 * * 5 claude -p "Run the google-sheets-kpi-tracker 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/google-sheets-kpi-tracker#schedule Source: https://briefkin.dev/skills/google-sheets-kpi-tracker