SaaS Usage Analytics: Event Tracking, Retention Cohorts, Feature Adoption, and Dashboards

Third-party analytics tools (Mixpanel, Amplitude, PostHog) are excellent but carry data residency concerns, per-event pricing that scales painfully, and limited ability to join product events with your billing and user data. Building usage analytics in-house — on top of PostgreSQL — gives you SQL joins across every dimension, zero marginal cost per event, and complete data ownership.

This guide covers the schema, the server-side event tracking API, and the key analytical queries every SaaS product needs.

Database Schema

-- Core event table: append-only, never updated
CREATE TABLE events (
  id           UUID DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  user_id      UUID REFERENCES users(id) ON DELETE SET NULL,
  name         TEXT NOT NULL,      -- e.g. 'project.created', 'invoice.sent'
  properties   JSONB NOT NULL DEFAULT '{}',
  session_id   TEXT,               -- Browser session identifier
  ip_address   INET,
  user_agent   TEXT,
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- Partition by month for query performance
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

-- Monthly partitions (create via script or pg_partman)
CREATE TABLE events_2027_05 PARTITION OF events
  FOR VALUES FROM ('2027-05-01') TO ('2027-06-01');

CREATE TABLE events_2027_06 PARTITION OF events
  FOR VALUES FROM ('2027-06-01') TO ('2027-07-01');

-- Indexes on each partition (automatically inherited)
CREATE INDEX idx_events_workspace_name ON events(workspace_id, name, occurred_at DESC);
CREATE INDEX idx_events_user           ON events(user_id, occurred_at DESC);
CREATE INDEX idx_events_name_date      ON events(name, occurred_at DESC);
CREATE INDEX idx_events_properties     ON events USING GIN(properties);

-- Daily active users pre-aggregation (run nightly)
CREATE TABLE dau_rollup (
  date         DATE NOT NULL,
  workspace_id UUID NOT NULL REFERENCES workspaces(id),
  dau          INTEGER NOT NULL,
  events_count INTEGER NOT NULL,
  PRIMARY KEY (date, workspace_id)
);

Server-Side Event Tracking

// lib/analytics/track.ts
import { prisma } from "@/lib/prisma";
import { headers } from "next/headers";

export interface TrackOptions {
  workspaceId: string;
  userId?:     string;
  event:       string;
  properties?: Record<string, unknown>;
  sessionId?:  string;
}

// Fire-and-forget: don't await in request handlers
export function track(options: TrackOptions): void {
  // Async but non-blocking
  void insertEvent(options).catch((err) => {
    console.error("[analytics] Failed to track event:", options.event, err);
  });
}

async function insertEvent(options: TrackOptions): Promise<void> {
  const reqHeaders = await headers().catch(() => null);
  const ipAddress  = reqHeaders?.get("x-forwarded-for")?.split(",")[0] ?? null;
  const userAgent  = reqHeaders?.get("user-agent") ?? null;

  await prisma.$executeRaw`
    INSERT INTO events (workspace_id, user_id, name, properties, session_id, ip_address, user_agent)
    VALUES (
      ${options.workspaceId}::uuid,
      ${options.userId ?? null}::uuid,
      ${options.event},
      ${JSON.stringify(options.properties ?? {})}::jsonb,
      ${options.sessionId ?? null},
      ${ipAddress}::inet,
      ${userAgent}
    )
  `;
}

// Typed event helpers — prevents typos in event names
export const analytics = {
  projectCreated: (workspaceId: string, userId: string, projectId: string) =>
    track({ workspaceId, userId, event: "project.created", properties: { projectId } }),

  invoiceSent: (workspaceId: string, userId: string, invoiceId: string, amountCents: number) =>
    track({ workspaceId, userId, event: "invoice.sent", properties: { invoiceId, amountCents } }),

  featureUsed: (workspaceId: string, userId: string, feature: string) =>
    track({ workspaceId, userId, event: "feature.used", properties: { feature } }),

  pageViewed: (workspaceId: string, userId: string, path: string) =>
    track({ workspaceId, userId, event: "page.viewed", properties: { path } }),
};

Key Analytical Queries

Daily/Weekly/Monthly Active Users

-- DAU for the last 30 days
SELECT
  DATE(occurred_at AT TIME ZONE 'UTC') AS date,
  COUNT(DISTINCT user_id)              AS dau,
  COUNT(*)                             AS events
FROM events
WHERE workspace_id = $1
  AND occurred_at  >= NOW() - INTERVAL '30 days'
  AND user_id IS NOT NULL
GROUP BY DATE(occurred_at AT TIME ZONE 'UTC')
ORDER BY date;

-- WAU and MAU as single values
SELECT
  COUNT(DISTINCT CASE WHEN occurred_at >= NOW() - INTERVAL '7 days'  THEN user_id END) AS wau,
  COUNT(DISTINCT CASE WHEN occurred_at >= NOW() - INTERVAL '30 days' THEN user_id END) AS mau
FROM events
WHERE workspace_id = $1;

-- Stickiness ratio: DAU/MAU (healthy SaaS: >15%)
WITH active AS (
  SELECT
    COUNT(DISTINCT CASE WHEN occurred_at::date = CURRENT_DATE              THEN user_id END) AS dau,
    COUNT(DISTINCT CASE WHEN occurred_at >= NOW() - INTERVAL '30 days'    THEN user_id END) AS mau
  FROM events
  WHERE workspace_id = $1
)
SELECT
  dau,
  mau,
  ROUND(dau::numeric / NULLIF(mau, 0) * 100, 1) AS stickiness_pct
FROM active;

Retention Cohort Analysis

-- Weekly retention cohort: % of users who return in weeks 1–8 after signup
WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('week', created_at) AS cohort_week
  FROM users
  WHERE workspace_id = $1
    AND created_at >= NOW() - INTERVAL '12 weeks'
),
activity AS (
  SELECT DISTINCT
    e.user_id,
    DATE_TRUNC('week', e.occurred_at) AS activity_week
  FROM events e
  WHERE e.workspace_id = $1
    AND e.user_id IS NOT NULL
    AND e.occurred_at >= NOW() - INTERVAL '12 weeks'
),
cohort_activity AS (
  SELECT
    c.cohort_week,
    a.activity_week,
    COUNT(DISTINCT c.user_id) AS active_users
  FROM cohorts c
  JOIN activity a ON a.user_id = c.user_id
  GROUP BY c.cohort_week, a.activity_week
),
cohort_sizes AS (
  SELECT cohort_week, COUNT(*) AS cohort_size
  FROM cohorts
  GROUP BY cohort_week
)
SELECT
  ca.cohort_week,
  cs.cohort_size,
  -- Week number since cohort signup (0 = signup week, 1 = week after, ...)
  EXTRACT(EPOCH FROM (ca.activity_week - ca.cohort_week)) / 604800 AS week_number,
  ca.active_users,
  ROUND(ca.active_users::numeric / cs.cohort_size * 100, 1) AS retention_pct
FROM cohort_activity ca
JOIN cohort_sizes cs ON cs.cohort_week = ca.cohort_week
ORDER BY ca.cohort_week, week_number;

Feature Adoption Funnel

-- Which % of workspaces have used each key feature (last 30 days)?
WITH active_workspaces AS (
  SELECT DISTINCT workspace_id
  FROM events
  WHERE occurred_at >= NOW() - INTERVAL '30 days'
),
feature_usage AS (
  SELECT
    workspace_id,
    MAX(CASE WHEN name = 'project.created'  THEN 1 ELSE 0 END) AS used_projects,
    MAX(CASE WHEN name = 'invoice.sent'     THEN 1 ELSE 0 END) AS used_invoices,
    MAX(CASE WHEN name = 'team.invite_sent' THEN 1 ELSE 0 END) AS used_team_invite,
    MAX(CASE WHEN name = 'api.key_created'  THEN 1 ELSE 0 END) AS used_api
  FROM events
  WHERE occurred_at >= NOW() - INTERVAL '30 days'
  GROUP BY workspace_id
)
SELECT
  COUNT(*)                                              AS active_workspaces,
  ROUND(SUM(used_projects)::numeric   / COUNT(*) * 100, 1) AS projects_pct,
  ROUND(SUM(used_invoices)::numeric   / COUNT(*) * 100, 1) AS invoices_pct,
  ROUND(SUM(used_team_invite)::numeric/ COUNT(*) * 100, 1) AS team_invite_pct,
  ROUND(SUM(used_api)::numeric        / COUNT(*) * 100, 1) AS api_pct
FROM feature_usage;

Top Events

-- Most common events (last 7 days)
SELECT
  name,
  COUNT(*)                 AS event_count,
  COUNT(DISTINCT user_id)  AS unique_users,
  COUNT(DISTINCT workspace_id) AS unique_workspaces,
  ROUND(COUNT(*)::numeric / COUNT(DISTINCT user_id), 1) AS events_per_user
FROM events
WHERE occurred_at >= NOW() - INTERVAL '7 days'
GROUP BY name
ORDER BY event_count DESC
LIMIT 20;

TypeScript Analytics API Route

// app/api/internal/analytics/summary/route.ts
import { NextRequest, NextResponse } from "next/server";
import { auth } from "@/auth";
import { prisma } from "@/lib/prisma";

export async function GET(req: NextRequest) {
  const session = await auth();
  if (session?.user?.role !== "admin") {
    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  }

  const [dau, retention, featureAdoption] = await Promise.all([
    // DAU last 14 days
    prisma.$queryRaw<{ date: Date; dau: bigint; events: bigint }[]>`
      SELECT
        DATE(occurred_at AT TIME ZONE 'UTC') AS date,
        COUNT(DISTINCT user_id)              AS dau,
        COUNT(*)                             AS events
      FROM events
      WHERE occurred_at >= NOW() - INTERVAL '14 days'
        AND user_id IS NOT NULL
      GROUP BY DATE(occurred_at AT TIME ZONE 'UTC')
      ORDER BY date
    `,

    // Week-1 retention rate
    prisma.$queryRaw<{ retention_pct: number }[]>`
      WITH week1_users AS (
        SELECT DISTINCT e.user_id
        FROM users u
        JOIN events e ON e.user_id = u.id
        WHERE u.created_at BETWEEN NOW() - INTERVAL '2 weeks' AND NOW() - INTERVAL '1 week'
          AND e.occurred_at BETWEEN u.created_at + INTERVAL '7 days'
                                AND u.created_at + INTERVAL '14 days'
      ),
      cohort AS (
        SELECT COUNT(*) AS total
        FROM users
        WHERE created_at BETWEEN NOW() - INTERVAL '2 weeks' AND NOW() - INTERVAL '1 week'
      )
      SELECT ROUND(COUNT(w.user_id)::numeric / NULLIF(c.total, 0) * 100, 1) AS retention_pct
      FROM cohort c, week1_users w
      GROUP BY c.total
    `,

    // Feature adoption rates
    prisma.$queryRaw<Record<string, number>[]>`
      SELECT
        ROUND(SUM(CASE WHEN name = 'project.created'  THEN 1 ELSE 0 END)::numeric / COUNT(DISTINCT workspace_id) * 100, 1) AS projects_pct,
        ROUND(SUM(CASE WHEN name = 'invoice.sent'     THEN 1 ELSE 0 END)::numeric / COUNT(DISTINCT workspace_id) * 100, 1) AS invoices_pct
      FROM events
      WHERE occurred_at >= NOW() - INTERVAL '30 days'
    `,
  ]);

  return NextResponse.json({
    dau:             dau.map((r) => ({ date: r.date, dau: Number(r.dau), events: Number(r.events) })),
    week1Retention:  retention[0]?.retention_pct ?? 0,
    featureAdoption: featureAdoption[0] ?? {},
  });
}

Nightly DAU Rollup Job

// scripts/rollup-dau.ts — run via cron at 00:05 UTC
import { prisma } from "@/lib/prisma";

async function rollupDAU() {
  const yesterday = new Date();
  yesterday.setUTCDate(yesterday.getUTCDate() - 1);
  const dateStr = yesterday.toISOString().split("T")[0];

  await prisma.$executeRaw`
    INSERT INTO dau_rollup (date, workspace_id, dau, events_count)
    SELECT
      ${dateStr}::date                  AS date,
      workspace_id,
      COUNT(DISTINCT user_id)           AS dau,
      COUNT(*)                          AS events_count
    FROM events
    WHERE occurred_at::date = ${dateStr}::date
      AND user_id IS NOT NULL
    GROUP BY workspace_id
    ON CONFLICT (date, workspace_id) DO UPDATE
      SET dau          = EXCLUDED.dau,
          events_count = EXCLUDED.events_count
  `;

  console.log(`[rollup] DAU rollup complete for ${dateStr}`);
}

rollupDAU().catch(console.error);

Cost and Timeline Estimates

See Also

• PostgreSQL Window Functions
• PostgreSQL Materialized Views
• SaaS Audit Logging
• PostgreSQL Partitioning Advanced Patterns
• SaaS Referral System

---

Working With Viprasol

Your product analytics should live where your business data lives — not in a third-party silo at $0.001 per event. Our team builds self-hosted usage analytics on PostgreSQL: partitioned event tables, typed server-side tracking helpers, DAU/WAU/MAU rollup queries, weekly retention cohort analysis, and feature adoption funnels — all joinable with your subscriptions, users, and invoices.

What we deliver:

• Partitioned events table by month with GIN index on properties JSONB
• track() fire-and-forget helper + typed analytics object with named event functions
• DAU/WAU/MAU SQL with stickiness ratio
• Weekly retention cohort CTE (cohort_week + week_number + retention_pct)
• Feature adoption MAX(CASE WHEN) aggregation per workspace
• Nightly DAU rollup job with ON CONFLICT DO UPDATE

Talk to our team about your product analytics architecture →

Or explore our SaaS development services.