Usage Report V0

ARCHIVED, DOC-ONLY REFERENCE. Do not invoke this skill for production reports. Use /usage-report instead.

Comparison-mode usage: This skill can be run AFTER /usage-report has already populated .scratchpad/usage-reports/<DATE>/ with the bastion export, analyzer JSON/CSV files, and PNG charts. In comparison mode you skip Steps 1-6 (the bastion export and analyzer runs) and jump straight to Step 7 (LLM writes the report by reading the existing files). The output is a v0-style LLM-written report alongside the new deterministic + commentary report, useful for side-by-side prose voice / structure comparison.

This SKILL.md captures the report-generation approach that was on main at commit f1c0c14a (2026-06-07), before the deterministic-render refactor. At that point the LLM did the report writing: it ran the analyzer scripts, then wrote the entire markdown by reading their outputs and synthesizing prose. The hallucination bug class we hit (Azure 28 vs 47, etc.) was the motivation to move to the template + augment-with-commentary architecture used in the current /usage-report skill.

Kept here for:

• Side-by-side comparison runs against /usage-report output for the same date.
• Comparing prose voice and section structure between the old LLM-written reports and the new deterministic+commentary reports.
• Recovering specific narrative patterns (e.g., "the longevity tier is now an aws/ecs/entra phenomenon") that were good and worth porting forward.
• Auditing what the old skill's instructions told the LLM to do, to make sure no semantic content was lost in the migration.

The .py scripts referenced below (analyze_telemetry.py, analyze_liveness.py, the chart generators, etc.) live in .claude/skills/usage-report/. This v0 folder contains only this SKILL.md.

Comparison-mode workflow (recommended):

1. Run /usage-report first for date <DATE>. This produces the deterministic report and populates .scratchpad/usage-reports/<DATE>/ with all data files and charts.
2. Then invoke this archived skill in comparison mode: skip Steps 1-6 (data is already exported), and start at Step 7 to have the LLM write a v0-style report from the existing files.
3. Save the v0 output as ai-registry-usage-report-<DATE>-v0.md in the same dated folder, next to the deterministic ai-registry-usage-report-<DATE>.md.

Usage Report Skill (archived)

Export telemetry data from the MCP Gateway Registry's DocumentDB telemetry collector and generate a usage report showing deployment patterns, version adoption, and feature usage in the wild.

Visualization Guidelines

All charts in this skill follow Edward Tufte's principles documented in tufte-viz-guidelines.md: high data-ink ratio, no chartjunk, layered information, honest scales. The shared style module tufte_style.py provides apply_tufte_style() (rcParams) and tufte_axes(ax) (per-axes cleanup). When adding new chart generators, import from tufte_style and call apply_tufte_style() once before plotting and tufte_axes(ax) for each axes after plotting. Reference the Tufte checklist in tufte-viz-guidelines.md before merging any new chart.

Prerequisites

1. SSH key at ~/.ssh/id_ed25519 with access to the bastion host
2. Terraform state available in terraform/telemetry-collector/ (to read bastion IP)
3. Bastion host enabled (bastion_enabled = true in terraform/telemetry-collector/terraform.tfvars)
4. AWS credentials configured on the bastion host (for Secrets Manager access)
5. GitHub CLI (gh) authenticated with read access to the upstream repo (agentic-community/mcp-gateway-registry) for collecting stars, forks, and contributor counts

Input

The skill accepts optional parameters:

/usage-report [OUTPUT_DIR]

• OUTPUT_DIR - Base directory for reports (default: .scratchpad/usage-reports/)

If OUTPUT_DIR is not provided, save to .scratchpad/usage-reports/.

All artifacts for a given run are placed in a dated subfolder: OUTPUT_DIR/YYYY-MM-DD/. This keeps each report self-contained and avoids a flat directory of hundreds of files. Previous metrics and CSV files are discovered by scanning both the base directory and all dated subdirectories.

Workflow

Step 1: Get Bastion IP

cd terraform/telemetry-collector && terraform output -raw bastion_public_ip

If the output is "Bastion not enabled", tell the user to set bastion_enabled = true in terraform/telemetry-collector/terraform.tfvars and run terraform apply.

Step 2: Copy Export Script to Bastion

scp -o StrictHostKeyChecking=no -i ~/.ssh/id_ed25519 \
  terraform/telemetry-collector/bastion-scripts/telemetry_db.py \
  ec2-user@$BASTION_IP:~/telemetry_db.py

Step 3: Run Export on Bastion

ssh -o StrictHostKeyChecking=no -i ~/.ssh/id_ed25519 \
  ec2-user@$BASTION_IP \
  'python3 telemetry_db.py export --output /tmp/registry_metrics.csv 2>&1'

Capture the full output -- it contains the summary statistics printed by telemetry_db.py.

Step 4: Create Dated Subfolder and Download the CSV

Create a dated subfolder for this run's artifacts, then download the CSV into it:

DATE_DIR=OUTPUT_DIR/YYYY-MM-DD
mkdir -p $DATE_DIR

scp -o StrictHostKeyChecking=no -i ~/.ssh/id_ed25519 \
  ec2-user@$BASTION_IP:/tmp/registry_metrics.csv \
  $DATE_DIR/registry_metrics.csv

Step 5: Install Python Dependencies and Generate Charts

First, ensure matplotlib and seaborn are available on the system Python:

/usr/bin/python3 -c "import matplotlib, seaborn" 2>/dev/null || pip install --break-system-packages matplotlib seaborn

Then generate the instance-based deployment distribution chart (counts unique registry instances, not events). Run it twice -- once for the cumulative install base, once filtered to the previous complete day -- so the report can show "everyone who ever installed" alongside "who is running it right now":

# Cumulative -- all customers ever
/usr/bin/python3 .claude/skills/usage-report/generate_instance_distribution_chart.py \
  --csv $DATE_DIR/registry_metrics.csv \
  --output $DATE_DIR/instance-distribution-YYYY-MM-DD.png

# Active-yesterday -- only customers that reported on the last complete day.
# Pass YYYY-MM-DD - 1 (the previous day relative to report date) so today's
# partial-day undercount doesn't bias the picture.
/usr/bin/python3 .claude/skills/usage-report/generate_instance_distribution_chart.py \
  --csv $DATE_DIR/registry_metrics.csv \
  --output $DATE_DIR/instance-distribution-active-PREVIOUS-YYYY-MM-DD.png \
  --active-on-date PREVIOUS-YYYY-MM-DD

Each invocation produces a faceted PNG with 6 subplots: Cloud Provider, Compute Platform, Storage Backend, Auth Provider, Architecture, and Deployment Mode. Each subplot shows unique instance counts and percentages. The --active-on-date form filters the row set to instances that had at least one event on the given date (heartbeat or startup), then runs the same six-panel breakdown on that subset; the chart title is annotated to make the filter explicit.

In the report, embed both PNGs in the "Deployment Distribution (by Unique Instances)" section and add a short narrative pointing out where the two views diverge -- typically the active-yesterday view shifts toward Kubernetes (vs Docker), enterprise IdP (vs the long-tail), and AWS dominance.

Step 5b: Generate Timeseries Chart

Generate a timeseries chart showing unique registry installs per cloud provider over time. This reads ALL CSV files in the base output directory and dated subdirectories to build a complete historical view:

/usr/bin/python3 .claude/skills/usage-report/generate_timeseries_chart.py \
  --csv-dir OUTPUT_DIR \
  --output $DATE_DIR/registry-installs-timeseries-YYYY-MM-DD.png \
  --exclude-incomplete-day YYYY-MM-DD

This produces a PNG with three subplots:

• Cumulative Unique Registry Installs -- running total of unique registry_ids per cloud provider
• Daily Active Registry Installs -- unique registry_ids seen each day per cloud provider (returning instances are re-counted)
• Daily NEW Registry Installs (first-seen) -- unique registry_ids whose earliest-ever event lands on each day, per cloud provider. Each instance is counted exactly once across the entire history. Use this to track raw acquisition velocity per cloud, isolated from churn and re-engagement

--exclude-incomplete-day drops events on the given date (today's date, the in-progress day) before charting so the trailing data point doesn't show a misleading dip. Always pass today's YYYY-MM-DD. Snapshot tables and headline tallies still see the full data; only the chart series are trimmed.

Step 5b2: Generate Compute Platform Timeseries Chart

Generate a second timeseries chart, parallel to the cloud-provider one, showing unique registry installs per compute platform (docker, kubernetes, ecs, ec2, etc.) over time. Same data-sourcing behavior (scans all CSV files across dated subdirectories). Pass --snapshots-table to also emit a markdown per-snapshot table ready to embed in the report:

/usr/bin/python3 .claude/skills/usage-report/generate_compute_timeseries_chart.py \
  --csv-dir OUTPUT_DIR \
  --output $DATE_DIR/compute-installs-timeseries-YYYY-MM-DD.png \
  --snapshots-table $DATE_DIR/compute-platform-snapshots-YYYY-MM-DD.md \
  --exclude-incomplete-day YYYY-MM-DD

This produces:

• A PNG with two subplots:
  • Cumulative Unique Registry Installs per Compute Platform -- running total of unique registry_ids per platform
  • Daily Active Registry Installs per Compute Platform -- unique registry_ids seen each day per platform
• A markdown file with the Per-Platform Growth (Unique Installs) table, one row per dated CSV snapshot, sorted descending by date (newest first, bolded). The column order is docker | kubernetes | ecs | ec2 | unknown when present, plus any other platforms alphabetically. Unique-instance counts per snapshot are computed directly from each dated CSV using the compute column (not compute_platform -- that's the schema key but not the CSV column name).

Embed the chart in the report's "Compute Platform Growth" section and drop the contents of the snapshots-table markdown file in under the "Per-Platform Growth (Unique Installs)" subheading. Add a short narrative on which platforms are growing fastest in absolute and percentage terms; the newest (bolded) row is the current total for the report.

Step 5c: Generate Instance Lifetime Chart

Generate a density plot showing the distribution of instance lifetimes (age in days). This reads the metrics JSON produced by the analysis step, so it must run after Step 6. However, the SKILL.md lists it here for logical grouping with other charts:

/usr/bin/python3 .claude/skills/usage-report/generate_lifetime_chart.py \
  --metrics $DATE_DIR/metrics-YYYY-MM-DD.json \
  --output $DATE_DIR/instance-lifetime-YYYY-MM-DD.png

This produces a PNG with three panels:

• Age Distribution -- histogram with KDE density overlay showing instance ages in days, with stats annotation (mean, max, multi-day vs single-day counts)
• Age Spread -- boxplot with Q1/median/Q3/max annotated and individual points overlaid, useful for spotting outliers and the long-tail of long-lived deployments
• Age Buckets -- horizontal bar chart grouping instances into age ranges (0 days, 1-2 days, 3-5 days, etc.) with counts and percentages

Note: Run this after Step 6 (telemetry analysis) since it reads the metrics JSON.

Step 5c-ts: Generate Lifetime-Bucket Retention Chart

Plot per-snapshot lifetime retention percentages (one-day wonders vs >=3 / >=7 / >=14 / >=30 day cohorts) over time. Reads every metrics-*.json file under the base output directory and recomputes the buckets retroactively, so it works on snapshots that predate the lifetime_bucket_pct field. Produces a PNG plus a per-snapshot CSV sidecar that future reports can diff against.

/usr/bin/python3 .claude/skills/usage-report/generate_lifetime_buckets_chart.py \
  --csv-dir OUTPUT_DIR \
  --output $DATE_DIR/lifetime-buckets-YYYY-MM-DD.png \
  --csv-out $DATE_DIR/lifetime-buckets-YYYY-MM-DD.csv

Embed the chart in the report directly below the Registry Instance Lifetime section, alongside a narrative that quotes the latest CSV row (the report-date snapshot) and contrasts it with the earliest snapshot to show whether the customer-retention curve is improving over time.

Note: Run this after Step 6 since it depends on instance_lifetime + internal_instance_ids keys in metrics-*.json.

Step 5c2: Generate Customer-Active-Instances Chart

Generate a chart of customer engagement over time, with three overlaid series:

• Daily Active Instances (DAI) -- unique registry_ids that sent at least one event (startup OR heartbeat) on that day.
• 7-day moving average (MA7) -- trailing 7-day average of DAI.
• 7-day consistency streak (S7) -- unique registry_ids that sent at least one event on EACH of the 7 days in the window [D-6..D].

Customer-only: internal instances loaded from known-internal-instances.md are excluded so the numbers align with the Liveness section (which is also customer-only). A CSV sidecar of the per-day values is written alongside the PNG so the report narrative can quote exact numbers and future reports can diff against it.

The CSV also includes two DAI percentage columns derived from the same per-day registry-id sets:

• cumulative_installs, dai_pct_of_total -- DAI / cumulative_installs through that day; the engagement rate of the full install funnel ever recorded
• likely_alive_7d, dai_pct_of_likely_alive -- DAI / unique-active-in-trailing-7d; the engagement rate of the currently-active fleet (analog of a DAU/WAU ratio)

Both percentages should be quoted side-by-side in the report's "DAI as a percentage of total installs" subsection so the reader sees the funnel-engagement number (low, because of one-day wonders) and the active-fleet engagement number (the healthier B2B-style read) together.

/usr/bin/python3 .claude/skills/usage-report/generate_active_instances_chart.py \
  --csv-dir OUTPUT_DIR \
  --output $DATE_DIR/active-instances-YYYY-MM-DD.png \
  --internal-instances .claude/skills/usage-report/known-internal-instances.md \
  --csv-out $DATE_DIR/active-instances-YYYY-MM-DD.csv \
  --exclude-incomplete-day YYYY-MM-DD

Same data-sourcing behavior as the other historical charts (scans all CSVs across dated subdirectories). Embed in the Liveness section under an "Engagement over Time" subheading.

Step 5c3: Generate LTV Infra-Spend Chart and Summary

Compute daily and cumulative AWS customer infra spend (EC2 compute + Bedrock Titan embeddings). The script emits two cost numbers framed as a range so the report can be conservative about not over-estimating spend:

• All-days (upper bound): charges every distinct (AWS customer instance, day) pair, including 1-day trial installs. Matches "every real AWS billing day this solution caused".
• Proven-persistence (lower bound): charges an instance on day D only if it had events on D AND on any prior day. This excludes every instance's first-ever active day, so instances that phone home once and never again contribute $0. Gap-tolerant: if an instance is silent on day D-1 and comes back on D, it's still charged on D (because it had prior events on earlier days).

Customer-only (internal UUIDs excluded), AWS-only (GCP/Azure/unknown excluded because we can't attribute their AWS-side usage). On the current fleet ~59% of AWS customer instances are "one-day wonders" — they show up once and never return — so the proven-persistence number is typically ~30% lower than all-days.

Cost model (per-compute-platform, grounded in deployment artefacts):

Platform for a given instance is resolved via its most-recent non-empty compute field. If an instance migrates across platforms mid-window, it's billed at the latest platform's rate for the whole window.

Bedrock Titan embeddings: only for instances whose latest embeddings_backend_kind == "bedrock". Cost = delta(search_queries_total) on that day × 100 tokens/query × $0.00002 / 1K tokens. The delta is computed from the instance's own search_queries_total timeseries (monotonic counter), so we never double-count queries that were already charged on a previous day.

/usr/bin/python3 .claude/skills/usage-report/generate_ltv_spend.py \
  --csv-dir OUTPUT_DIR \
  --output $DATE_DIR/ltv-spend-YYYY-MM-DD.png \
  --internal-instances .claude/skills/usage-report/known-internal-instances.md \
  --csv-out $DATE_DIR/ltv-spend-YYYY-MM-DD.csv \
  --summary-json $DATE_DIR/ltv-spend-YYYY-MM-DD.json \
  --exclude-incomplete-day YYYY-MM-DD

When --exclude-incomplete-day is passed, the JSON summary's yesterday block refers to the last complete day (typically YYYY-MM-DD - 1), not today. Headline tables in the report should label this clearly (e.g. "Yesterday (2026-05-16, last complete day)").

Outputs:

• PNG chart with three panels: daily EC2 compute USD (all-days + proven overlay), daily Bedrock USD, cumulative LTV USD (both lines with shaded range between them).
• CSV sidecar per day: date, aws_instances, aws_instances_persistent, <platform>_instances[_persistent], bedrock_queries[_persistent], compute_usd[_persistent], bedrock_usd[_persistent], total_usd[_persistent], cum_total_usd[_persistent].
• JSON summary with headline numbers under yesterday.all_days vs yesterday.proven, last_7_days.{all_days_total_usd, proven_total_usd}, ltv.{all_days, proven}, and per-platform LTV breakdown for both models.

Embed the chart in the report's Customer Infra Spend (AWS) section. Include a single summary table that shows both numbers as a range (e.g. "yesterday: $292.67 – $346.01"), one short paragraph explaining the two counting rules, and the per-platform LTV breakdown (both models side by side). Flag clearly that the cost model is hypothetical (we don't actually bill these customers; these are "what it would cost them at list price").

ARR projection (mandatory): below the cumulative LTV table, add a short subsection titled "Annualized Run Rate (ARR) Projection" that takes the 7-day daily-average spend and projects it forward 365 days. Compute as last_7_days.<model>_total_usd / 7 * 365, divided by 1,000,000, formatted to 2 decimal places in millions. Render BOTH models (proven and all-days) as a small two-row table:

| Model | 7-day daily avg | x 365 = ARR |
|-------|----------------:|------------:|
| Proven | $X | $Y.YYM |
| All-days | $X | $Y.YYM |

Frame the ARR as "what the active customer fleet would cost AWS customers per year at list price if today's run rate held constant." Include the same hypothetical disclaimer (we do not bill these customers). The ARR is a useful complement to install-count growth: it tracks the real economic footprint of the customer fleet, not just the headcount of registry instances.

Step 5d: Generate Install Forecast Chart

Project when the registry will reach 1,000 installs using two models: a 14-day OLS linear regression and a 7-day recent-pace extrapolation. Produces a PNG chart (cumulative installs with forecast line and confidence bands) and a JSON summary with ETAs.

/usr/bin/python3 .claude/skills/usage-report/generate_install_forecast.py \
  --csv-dir OUTPUT_DIR \
  --output $DATE_DIR/install-forecast-YYYY-MM-DD.png \
  --summary-json $DATE_DIR/install-forecast-YYYY-MM-DD.json

Outputs:

• PNG chart showing cumulative installs, linear fit, and projected crossing of the 1,000-install target
• JSON summary with today.installs, linear.eta (with 95% CI bounds), recent_pace.eta, and model parameters

Embed the chart in the report's Install Forecast section. Include a table showing both model ETAs and daily rates. This section should come after Version Adoption and before Customer Infra Spend.

Step 5e: Generate Adoption Funnel Chart

Visualize the conversion funnel from total installs through retention stages to confirmed-alive. Reads the metrics JSON (for lifetime buckets) and optionally the liveness JSON (for confirmed-alive count).

/usr/bin/python3 .claude/skills/usage-report/generate_adoption_funnel_chart.py \
  --metrics $DATE_DIR/metrics-YYYY-MM-DD.json \
  --liveness $DATE_DIR/liveness-YYYY-MM-DD.json \
  --output $DATE_DIR/adoption-funnel-YYYY-MM-DD.png

Note: Run after Step 6 and Step 6c since it reads both metrics-*.json and liveness-*.json.

Embed the chart in the report's Adoption Funnel section (placed after Most Engaged Operators, before Recommendations). Include a table showing each funnel stage, count, and percentage of the previous stage.