How to Build a Customer Health Dashboard with SaaS Data

https://peaka.studio/studio/{project_id}/...

SELECT 
  u.email AS requester_email, -- Requester's email address
  COUNT(*) AS active_ticket_count -- Count of active tickets per user
FROM "zendesk"."public"."tickets" t
JOIN "zendesk"."public"."users" u ON t.requester_id = u.id
WHERE t.status IN ('open', 'pending') -- Only tickets that are still open or pending
  AND t.created_at >= CURRENT_DATE - INTERVAL '30' DAY -- Tickets created in the last 30 days
GROUP BY u.email; -- Group results by user email

SELECT 
  c.email AS customer_email, -- Customer's email address
  SUM(p.unit_amount / 100) AS mrr_usd -- Sum of subscription amounts (converted from cents to dollars)
FROM "stripe"."payment"."subscriptions" s
JOIN "stripe"."payment"."customers" c ON s.customer_id = c.id
JOIN "stripe"."payment"."prices" p ON s.plan_id = p.id
WHERE s.status IN ('active', 'incomplete_expired', 'incomplete') -- Active or recently incomplete subscriptions
GROUP BY c.email; -- Group results by customer email

WITH active_tickets AS (
  SELECT 
    u.email AS customer_email, -- Email of the customer who submitted tickets
    COUNT(*) AS ticket_count -- Number of tickets created in the last 30 days
  FROM "zendesk"."public"."tickets" t
  JOIN "zendesk"."public"."users" u ON t.requester_id = u.id
  WHERE t.created_at >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY u.email
),
active_subscriptions AS (
  SELECT 
    c.email AS customer_email, -- Customer email from Stripe
    MAX(s.ended_at) AS last_payment_date -- Last subscription end date
  FROM "stripe"."payment"."subscriptions" s
  JOIN "stripe"."payment"."customers" c ON s.customer_id = c.id
  WHERE s.status = 'active'
  GROUP BY c.email
)

SELECT 
  at.customer_email, -- Email from active tickets
  at.ticket_count, -- Number of tickets submitted
  CASE 
    WHEN at.ticket_count > 5 AND (asub.last_payment_date IS NULL OR asub.last_payment_date < CURRENT_DATE - INTERVAL '30' DAY) THEN 'High' -- High risk if many tickets and no recent payment
    WHEN at.ticket_count > 3 THEN 'Medium' -- Medium risk if moderate ticket count
    ELSE 'Low' -- Otherwise low risk
  END AS churn_risk
FROM active_tickets at
LEFT JOIN active_subscriptions asub ON at.customer_email = asub.customer_email;

WITH ticket_stats AS (
  SELECT 
    u.email AS customer_email, -- Customer email from Zendesk users
    COUNT(*) FILTER (WHERE t.status IN ('open', 'pending') AND t.created_at >= CURRENT_DATE - INTERVAL '30' DAY) AS open_tickets_last_30d, -- Number of open tickets in last 30 days
    COUNT(*) AS total_tickets -- Total number of tickets
  FROM "zendesk"."public"."tickets" t
  JOIN "zendesk"."public"."users" u ON t.requester_id = u.id
  GROUP BY u.email
),
active_subscriptions AS (
  SELECT 
    c.email AS customer_email, -- Customer email from Stripe
    COUNT(*) > 0 AS has_active_subscription -- Boolean flag for active subscription
  FROM "stripe"."payment"."subscriptions" s
  JOIN "stripe"."payment"."customers" c ON s.customer_id = c.id
  WHERE s.status = 'active'
  GROUP BY c.email
),
churn_risk_calc AS (
  SELECT 
    ts.customer_email,
    CASE 
      WHEN ts.open_tickets_last_30d > 5 AND (asub.has_active_subscription = false OR asub.has_active_subscription IS NULL) THEN 'High' -- High churn risk if many open tickets and no active subscription
      WHEN ts.open_tickets_last_30d > 3 THEN 'Medium' -- Medium churn risk for moderate open tickets
      ELSE 'Low' -- Otherwise low risk
    END AS churn_risk
  FROM ticket_stats ts
  LEFT JOIN active_subscriptions asub ON ts.customer_email = asub.customer_email
)

SELECT
  ts.customer_email,
  COALESCE(asub.has_active_subscription, false) AS active_subscription, -- Whether customer has active subscription
  ts.open_tickets_last_30d, -- Number of open tickets in last 30 days
  crc.churn_risk, -- Churn risk level
  (
    CASE WHEN COALESCE(asub.has_active_subscription, false) THEN 50 ELSE 0 END + -- Add 50 points if active subscription
    CASE WHEN ts.open_tickets_last_30d = 0 THEN 20 ELSE 0 END + -- Add 20 points if no open tickets
    CASE WHEN ts.open_tickets_last_30d > 5 THEN -20 ELSE 0 END + -- Subtract 20 points if more than 5 open tickets
    CASE WHEN crc.churn_risk = 'High' THEN -30 ELSE 0 END -- Subtract 30 points if high churn risk
  ) AS account_health_score -- Final calculated health score
FROM ticket_stats ts
LEFT JOIN active_subscriptions asub ON ts.customer_email = asub.customer_email
LEFT JOIN churn_risk_calc crc ON ts.customer_email = crc.customer_email;

I have a Peaka account and a Peaka project that contains four saved queries:
- active_tickets: Returns number of active support tickets per customer
- mrr: Returns monthly recurring revenue (MRR) per customer
- account_health_score: Returns a numeric health score between 0 and 100 for each customer
- churn_risk: Returns churn risk category ("Low", "Medium", or "High")

I want to build a full-stack customer health dashboard in Next.js, using React and Tailwind CSS.

The dashboard layout should include:

Top section (4 metric cards):
- Total active tickets
- Average MRR
- Average account health score
- High churn risk percentage

Bottom section (charts):
- Bar chart showing MRR by customer
- Pie chart showing churn risk distribution
- Histogram (bar chart) of health score distribution in buckets
- Scatter plot of active tickets vs MRR, with churn risk as point color

All data should be fetched directly from Peaka's data API using fetch.

To get data from a Peaka query, I will use the following API pattern:

POST https://partner.peaka.studio/api/v1/data/projects/{projectId}/queries/execute
Authorization: Bearer <token>
Content-Type: application/json

Body:
{
  "from": [
    {
      "catalogName": "peaka",
      "schemaName": "query",
      "tableName": "mrr" // or "active_tickets", etc.
    }
  ]
}

Each query will be executed this way from the frontend. You can assume the token is stored in an .env file or injected via a helper function.

Please generate the full dashboard code as a single `dashboard.tsx` page, using `fetch` to call these APIs from the browser. Use Tailwind CSS for layout. For charts, you can use Recharts or another modern charting library.

You can check Peaka's API reference here: https://docs.peaka.com/api-reference/introduction

Make the code developer-friendly and easy to customize.

PEAKA_API_KEY=your_api_key_here
PEAKA_PROJECT_ID=your_project_id_here