Powered by AppSignal & Oban Pro
Notesclub
Sign in with GitHub
created by hec & contributors
terms privacy

Metric Definitions and Data Quality

09-metric-definitions-and-data-quality.livemd

avatar
Andrew Dryga
avatar
avatar
Share to X
Share to Bluesky
More notebooks

Metric Definitions and Data Quality

Definitions

Metric Definition
Account Non-deleted row in accounts
User Non-deleted global user; account membership is counted separately
Active MCP agent Operator-visible, unrevoked, unexpired, membership-bound API key with kind = 'mcp'
Active account Account with at least one action run in the stated period
Activated account Account that dispatched its first action
Enabled runner Non-deleted runner without disabled_at
Recently connected runner Runner whose durable last_connected_at is in the stated period; not exact Presence state
Terminal success rate Successful runs divided by all terminal runs; unsettled states are excluded
MRR Active charged unit amount times quantity, normalized by Paddle interval and frequency, grouped by currency
MCP client family Self-reported clientInfo.name; not an upstream LLM provider or model

Setup

Mix.install([
  {:postgrex, "~> 0.22.0"},
  {:kino, "~> 0.19.0"},
  {:kino_vega_lite, "~> 0.1.13"}
])

Code.require_file("/opt/emisar/product_analytics.exs")
alias EmisarProductAnalytics, as: Analytics
db = Analytics.connect!()

Quality checks

quality =
  Analytics.query(db, """
  SELECT check_name, failing_rows
  FROM (
    SELECT 1 AS position, 'active subscriptions missing exact revenue facts' AS check_name,
      count(*) FILTER (WHERE status = 'active' AND (unit_price_amount IS NULL OR currency_code IS NULL OR billing_frequency IS NULL OR billing_interval IS NULL)) AS failing_rows
    FROM subscriptions
    UNION ALL
    SELECT 2, 'active subscriptions with unsupported cadence',
      count(*) FILTER (WHERE status = 'active' AND (billing_interval IS NULL OR billing_interval NOT IN ('month', 'year')))
    FROM subscriptions
    UNION ALL
    SELECT 3, 'memberships referencing deleted accounts', count(*)
    FROM memberships m JOIN accounts a ON a.id = m.account_id
    WHERE m.deleted_at IS NULL AND a.deleted_at IS NOT NULL
    UNION ALL
    SELECT 4, 'enabled runners never connected', count(*)
    FROM runners WHERE deleted_at IS NULL AND disabled_at IS NULL AND last_connected_at IS NULL
    UNION ALL
    SELECT 5, 'active MCP agents never used', count(*)
    FROM api_keys WHERE kind = 'mcp' AND deleted_at IS NULL AND revoked_at IS NULL AND expires_at > now() AND created_by_membership_id IS NOT NULL AND auto_generated_at IS NULL AND last_used_at IS NULL
    UNION ALL
    SELECT 6, 'pending approvals past expiry', count(*)
    FROM approval_requests WHERE status = 'pending' AND expires_at < now()
    UNION ALL
    SELECT 7, 'terminal runs without finished_at', count(*)
    FROM action_runs
    WHERE status IN ('success', 'failed', 'error', 'validation_failed', 'unknown_action', 'cancelled', 'timed_out', 'refused')
      AND finished_at IS NULL
  ) checks
  ORDER BY position
  """)

Analytics.table(quality)

Known value spaces

values =
  Analytics.query(db, """
  SELECT 'subscription.status' AS field, status AS value, count(*) AS rows FROM subscriptions GROUP BY status
  UNION ALL
  SELECT 'subscription.billing_interval', coalesce(billing_interval, '<null>'), count(*) FROM subscriptions GROUP BY billing_interval
  UNION ALL
  SELECT 'action_run.status', status, count(*) FROM action_runs GROUP BY status
  UNION ALL
  SELECT 'action_run.source', source, count(*) FROM action_runs GROUP BY source
  UNION ALL
  SELECT 'approval.status', status, count(*) FROM approval_requests GROUP BY status
  ORDER BY field, rows DESC
  """)

Analytics.table(values)

Dataset freshness and volume

datasets =
  Analytics.query(db, """
  SELECT 'accounts' AS dataset, count(*) AS rows, max(inserted_at) AS latest_record FROM accounts
  UNION ALL SELECT 'users', count(*), max(inserted_at) FROM users
  UNION ALL SELECT 'runners', count(*), max(inserted_at) FROM runners
  UNION ALL SELECT 'api_keys', count(*), max(inserted_at) FROM api_keys
  UNION ALL SELECT 'action_runs', count(*), max(inserted_at) FROM action_runs
  UNION ALL SELECT 'approval_requests', count(*), max(inserted_at) FROM approval_requests
  UNION ALL SELECT 'audit_events', count(*), max(inserted_at) FROM audit_events
  UNION ALL SELECT 'subscriptions', count(*), max(updated_at) FROM subscriptions
  ORDER BY dataset
  """)

Analytics.table(datasets)

Other notebooks:

Back