---
name: zo-metrics
description: Query and maintain Zo company metrics from the production Neon Postgres database. Includes user growth, subscription analytics, tool usage, acquisition channels, and conversation patterns. Dashboard site at web/zodash.
compatibility: Zo Computer internal use only
metadata:
  author: rob.zo.computer
---
# Zo Metrics

Internal skill for querying Zo company metrics from the production database.

## Dashboard Site

**Location:** `file 'web/zodash'`

The zodash site is the production internal dashboard for Zo metrics. Architecture:

1. **SQL queries** live in `Skills/zo-metrics/queries/*.sql`
2. **Data pipeline**: Queries run → output to JSON files in `web/zodash/src/data/`
3. **React components**: Import JSON, render with Recharts/shadcn components
4. **Build**: Data is baked into the bundle at build time (no runtime DB access)

### Workflow for Adding a New Chart

1. Create/modify a query in `queries/`
2. Run the query and save output: `./scripts/run-query.sh queries/foo.sql > ../web/zodash/src/data/foo.json`
3. Import in your component: `import fooData from '@/data/foo.json'`
4. Render with Recharts

### Dashboard Queries (from Hex export)

| File | Purpose | Result Variable |
|------|---------|-----------------|
| `01-daily-tool-messages-per-host.sql` | Base activity data | `daily_host_activity` |
| `02-7-day-rolling-active-hosts.sql` | 7-day WAU rolling | `rolling_active_hosts` |
| `03-combined-7-day-rolling-1-day-active-hosts.sql` | DAU/WAU/MAU combined | `combined_active_hosts` |
| `04-unpivoted-active-hosts-for-charting.sql` | Unpivoted for multi-line chart | `unpivoted_hosts` |
| `05-active-hosts-by-conversation-type-dec-11.sql` | Activity by conv type | `active_hosts_by_type` |
| `06-summary-active-hosts-by-type.sql` | Type breakdown summary | `host_type_summary` |
| `07-distinct-hosts-in-type-breakdown.sql` | Distinct host check | `distinct_check` |
| `08-active-host-breakdown-split-vs-single-type.sql` | Split vs single-type hosts | `host_breakdown` |
| `09-active-subscriptions-by-type.sql` | Subscriptions by plan | `active_subscriptions` |
| `10-cumulative-subscriptions-by-type-over-time.sql` | Cumulative sub growth | `subscription_history` |
| `11-new-hosts-last-week-activity-plans.sql` | New host cohort | `new_hosts_last_week` |
| `12-new-host-summary-by-activity-plan.sql` | New host activity summary | `new_host_summary` |
| `13-mom-active-host-growth-since-nov-2023.sql` | MoM growth rates | `monthly_host_growth` |

Note: Some queries reference result variables from earlier queries (e.g., `daily_host_activity`). For standalone execution, you'll need to inline those CTEs or run them as a sequence.

---

## Database Connection

**Provider:** Neon Postgres  
**Connection string** (preferred):

```bash
psql 'postgresql://hostagentspg_owner:npg_rR7YyGD3AuVX@ep-muddy-cell-a524vfrg.us-east-2.aws.neon.tech/hostagentspg?sslmode=require&channel_binding=require'
```

Alternative env-var style:

```bash
export PGHOST=ep-muddy-cell-a524vfrg.us-east-2.aws.neon.tech
export PGDATABASE=hostagentspg
export PGUSER=hostagentspg_owner
export PGPASSWORD=npg_rR7YyGD3AuVX
export PGSSLMODE=require
export PGCHANNELBINDING=require
```

Quick inline usage:
```bash
PGHOST=ep-muddy-cell-a524vfrg.us-east-2.aws.neon.tech PGDATABASE=hostagentspg PGUSER=hostagentspg_owner PGPASSWORD=npg_rR7YyGD3AuVX PGSSLMODE=require PGCHANNELBINDING=require psql -c "SELECT COUNT(*) FROM users;"
```

## Database Schema Overview

### Core Entity Hierarchy

```
users (auth identity)
  └── hosts (workspace instance, identified by domain_prefix/handle)
        ├── conversations
        │     ├── messages
        │     └── tool_messages (8.7GB - largest table)
        ├── subscriptions
        ├── images
        ├── services
        └── space_routes
```

### Key Tables

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `users` | Auth records | `id`, `email`, `canonical_email`, `created_at`, `stripe_customer_id` |
| `hosts` | Workspace instances | `id`, `domain_prefix` (handle), `user_id`, `keepalive` |
| `conversations` | Chat sessions | `id`, `host_id`, `type`, `created_at`, `deleted_at` |
| `messages` | Conversation events | `id`, `conversation_id`, `event_type`, `role`, `data` (jsonb) |
| `tool_messages` | Tool invocations | `conversation_id`, `tool_name`, `tool_args`, `tool_result`, `tool_timestamp` |
| `subscriptions` | Stripe subs | `host_id`, `pricing_plan_slug`, `status`, `stripe_subscription_id` |
| `connections` | App integrations | `user_id`, `app`, `permission`, `pd_conn_id` |
| `user_profiles` | Onboarding/UTM data | `user_id`, `utm_source_first`, `persona_*` timestamps |

### Conversation Types

- `schedule` - Scheduled agent runs (most common)
- `api` - API-initiated conversations
- `sms` - SMS channel
- `email` - Email channel
- `zoroutine` - Zoroutine executions
- `NULL` - Interactive chat sessions

### Subscription Plans

| Slug | Tier | Price |
|------|------|-------|
| `zo_free_v8` | Free | $0 |
| `zo_plus_v8` | Basic | $18/mo |
| `zo_pro_v8` | Pro | $64/mo |
| `zo_ultra_v8` | Ultra | $200/mo |

---

## Stripe API (Accurate MRR)

For accurate MRR including usage-based revenue, use the Stripe API script:

```bash
STRIPE_SECRET_KEY="sk_live_..." bun Skills/zo-metrics/scripts/stripe-mrr.ts
```

Outputs JSON with:
- `mrr_net` - Last month's net revenue (after Stripe fees)
- `current_month_net` - Current month partial revenue
- `paid_customers` - Unique paying customers
- `top_customers` - Highest spenders
- `monthly_net_revenue` - Month-over-month breakdown

The script uses `/v1/balance_transactions` which is much faster than paginating invoices (~10s vs 5+ min).

To regenerate dashboard data:
```bash
STRIPE_SECRET_KEY="sk_live_..." bun Skills/zo-metrics/scripts/stripe-mrr.ts > web/zodash/src/data/stripe-mrr.json
```

Note: The Stripe secret key is stored in `/home/.z/secrets.json`.

## Common Metrics Queries

### User Growth

```sql
-- Weekly signups (last 8 weeks)
SELECT 
  DATE_TRUNC('week', created_at) as week,
  COUNT(*) as signups
FROM users
WHERE created_at > NOW() - INTERVAL '8 weeks'
GROUP BY 1 ORDER BY 1 DESC;

-- Daily active users (by conversation activity)
SELECT 
  DATE_TRUNC('day', c.created_at) as day,
  COUNT(DISTINCT c.host_id) as active_hosts
FROM conversations c
WHERE c.created_at > NOW() - INTERVAL '30 days'
  AND c.deleted_at IS NULL
GROUP BY 1 ORDER BY 1 DESC;
```

### Subscription Metrics

```sql
-- Current subscription breakdown
SELECT pricing_plan_slug, status, COUNT(*) 
FROM subscriptions 
GROUP BY 1, 2 ORDER BY COUNT(*) DESC;

-- Paid subscriber count
SELECT COUNT(*) as paid_subscribers
FROM subscriptions 
WHERE status = 'active' 
  AND pricing_plan_slug != 'zo_free_v8';

-- MRR estimate (rough)
SELECT 
  SUM(CASE 
    WHEN pricing_plan_slug = 'zo_plus_v8' THEN 12
    WHEN pricing_plan_slug = 'zo_pro_v8' THEN 35
    WHEN pricing_plan_slug = 'zo_ultra_v8' THEN 100
    ELSE 0
  END) as estimated_mrr
FROM subscriptions 
WHERE status = 'active';
```

### Tool Usage

```sql
-- Top tools (all time)
SELECT tool_name, COUNT(*) as usage
FROM tool_messages
GROUP BY 1 ORDER BY 2 DESC LIMIT 20;

-- Tool usage trend (last 7 days)
SELECT 
  DATE_TRUNC('day', tool_timestamp) as day,
  tool_name,
  COUNT(*) as calls
FROM tool_messages
WHERE tool_timestamp > NOW() - INTERVAL '7 days'
GROUP BY 1, 2 ORDER BY 1 DESC, 3 DESC;
```

### Acquisition Channels

```sql
-- UTM sources
SELECT utm_source_first, COUNT(*) 
FROM user_profiles 
WHERE utm_source_first IS NOT NULL
GROUP BY 1 ORDER BY 2 DESC LIMIT 20;

-- Referrer handles
SELECT referrer_handle, COUNT(*) 
FROM users 
WHERE referrer_handle IS NOT NULL
GROUP BY 1 ORDER BY 2 DESC LIMIT 20;
```

### App Connections

```sql
-- Connected apps by usage
SELECT app, permission, COUNT(*) 
FROM connections 
GROUP BY 1, 2 ORDER BY 3 DESC;
```

### Conversation Activity

```sql
-- Conversations by type (last 30 days)
SELECT type, COUNT(*) 
FROM conversations 
WHERE created_at > NOW() - INTERVAL '30 days'
  AND deleted_at IS NULL
GROUP BY 1 ORDER BY 2 DESC;

-- Heaviest users by conversation count
SELECT h.domain_prefix, COUNT(*) as convos
FROM conversations c
JOIN hosts h ON c.host_id = h.id
WHERE c.created_at > NOW() - INTERVAL '7 days'
  AND c.deleted_at IS NULL
GROUP BY 1 ORDER BY 2 DESC LIMIT 20;
```

## Schema Details

Full schema is defined in Drizzle ORM at `file 'code/hostagent/ts-packages/db/src/schema/'`.

### Foreign Key Relationships

- `conversations.host_id` → `hosts.id`
- `conversations.user_id` → `users.id`
- `messages.conversation_id` → `conversations.id`
- `tool_messages.conversation_id` → `conversations.id`
- `subscriptions.host_id` → `hosts.id`
- `connections.user_id` → `users.id`
- `hosts.user_id` → `users.id`

### Important Indexes

- `idx_conversations_not_deleted` - Partial index for active conversations
- `idx_tool_messages_tool_name` - For tool usage queries
- `idx_tool_messages_timestamp` - For time-range queries
- `idx_messages_conversation_sequence` - For ordered message retrieval

## Scripts

| Script | Purpose |
|--------|---------|
| `scripts/query.sh` | Quick ad-hoc query |
| `scripts/snapshot.sh` | Full metrics snapshot |
| `scripts/metrics.py` | Python CLI with subcommands |

Example usage:
```bash
./Skills/zo-metrics/scripts/query.sh "SELECT COUNT(*) FROM users"
python Skills/zo-metrics/scripts/metrics.py snapshot
```

## Storage Notes

- **tool_messages** is 8.7GB (bulk of storage) - be careful with full scans
- **images** stores references, not blobs
- **conversations.metadata** and **messages.data** are JSONB - can contain large payloads

## Related Files

- `queries/` - Dashboard SQL queries (13 queries from Hex export)
- `scripts/` - CLI tools for querying
- `references/schema-diagram.md` - Visual entity relationships