How to Build a Product Usage Dashboard from Event Data

Every PM I've worked with eventually asks for a usage dashboard. Not revenue, not funnels — "who's using the product, when, and how much." The request sounds simple until you realize your company doesn't have Amplitude, your data team is two engineers who also write application code, and the budget for a $40k analytics platform isn't getting approved this quarter.

If you have a Postgres events table, you already have everything you need. Six queries, each mapping to a dashboard widget. A few hours of work and you'll have the same view of engagement that a product analytics platform would give you.

These queries assume two tables:

events (user_id, event_name, properties jsonb, session_id, created_at)
users  (id, created_at, plan)

If your schema calls the timestamp timestamp or the event column type, rename accordingly. The structure is the same.

1. DAU / WAU / MAU with proper deduplication

Widget type: three number tiles + a ratio

The most-requested metric in product analytics is just a distinct-user count over three time windows. The only way to mess it up is to count events instead of users, which inflates the number every time someone clicks around.

SELECT
  count(DISTINCT user_id) FILTER (
    WHERE created_at >= current_date
  ) AS dau,
  count(DISTINCT user_id) FILTER (
    WHERE created_at >= current_date - 6
  ) AS wau,
  count(DISTINCT user_id) FILTER (
    WHERE created_at >= current_date - 29
  ) AS mau,
  round(
    count(DISTINCT user_id) FILTER (WHERE created_at >= current_date)::numeric
    / NULLIF(count(DISTINCT user_id) FILTER (WHERE created_at >= current_date - 29), 0),
    3
  ) AS dau_mau_ratio
FROM events
WHERE created_at >= current_date - 29;

The FILTER (WHERE ...) syntax is Postgres 9.4+. On older versions, wrap the condition in a CASE WHEN ... THEN user_id END inside count(DISTINCT ...). The DAU/MAU ratio at the end is the one number worth watching on its own: Facebook cited 0.6+ as world class, most B2B products sit between 0.1 and 0.3, and the trend matters more than the absolute value.

2. Activity heatmap: hour of day by day of week

Widget type: heatmap (color intensity = event volume)

This tells you when users actually show up. It sounds like a nice-to-have until you look at the output and notice that nobody uses your product on Fridays, or that your European users are driving all the activity at 3 AM your time.

SELECT
  extract(dow FROM created_at)  AS day_of_week,   -- 0 = Sunday, 6 = Saturday
  extract(hour FROM created_at) AS hour_of_day,
  count(*)                      AS event_count,
  count(DISTINCT user_id)       AS unique_users
FROM events
WHERE created_at >= now() - interval '30 days'
GROUP BY 1, 2
ORDER BY 1, 2;

Two gotchas. First, extract(dow ...) in Postgres returns 0 for Sunday, not 1 — if you're rendering this in a grid with labeled rows, an off-by-one here shifts the entire visual. Second, this query uses your database server's timezone. If the server is UTC but your users are in US-Eastern, the heatmap is shifted 4-5 hours. Add AT TIME ZONE 'America/New_York' to created_at before extracting if you want the heatmap in your users' local time.

3. Session analysis from raw events

Widget type: stats summary or table

Your session_id column might be null, populated inconsistently, or defined in a way that doesn't match what you want. The standard definition: a session is a sequence of events from the same user with no gap longer than 30 minutes. Postgres window functions handle this in one query.

WITH event_gaps AS (
  SELECT
    user_id,
    created_at,
    event_name,
    CASE
      WHEN created_at - lag(created_at) OVER (
        PARTITION BY user_id ORDER BY created_at
      ) > interval '30 minutes'
      THEN 1
      ELSE 0
    END AS new_session
  FROM events
  WHERE created_at >= now() - interval '7 days'
),
sessions AS (
  SELECT
    user_id,
    created_at,
    event_name,
    sum(new_session) OVER (
      PARTITION BY user_id ORDER BY created_at
    ) AS session_num
  FROM event_gaps
)
SELECT
  user_id,
  session_num,
  min(created_at)                                       AS session_start,
  max(created_at)                                       AS session_end,
  extract(epoch FROM max(created_at) - min(created_at)) AS duration_seconds,
  count(*)                                              AS event_count
FROM sessions
GROUP BY user_id, session_num
HAVING count(*) > 1
ORDER BY min(created_at) DESC
LIMIT 100;

The HAVING count(*) > 1 filters single-event sessions (bounces). Whether to include those depends on the question — for "average session depth," keep them because bounces are real and you want to know about them. For "what does an engaged session look like," drop them.

Watch the performance on this one. The lag() window function scans every row in the time window, ordered per user. On a table with millions of rows, you need an index on (user_id, created_at) or you'll be waiting.

4. Power user curve (L28 histogram)

Widget type: bar chart

This is Facebook's L28 metric. Instead of an average that hides everything, you get the distribution: how many days was each user active in the last 28 days? The shape of this histogram tells you more than any single engagement number.

WITH daily_activity AS (
  SELECT
    user_id,
    count(DISTINCT created_at::date) AS days_active
  FROM events
  WHERE created_at >= current_date - 27
  GROUP BY user_id
)
SELECT
  days_active,
  count(*)       AS num_users,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct_of_users
FROM daily_activity
GROUP BY days_active
ORDER BY days_active;

What you want to see: a smile curve. Some users at 1-2 days (they tried it), a dip in the middle, then a spike at 20+ days (daily power users). What you don't want: everything piled at 1. If 80% of your users were active exactly one day out of 28, that's not an engagement problem — it's a retention problem, and no dashboard redesign fixes it.

If you want to go deeper on the analysis side of this — funnels, retention cohorts, activation rates — we covered that in How to Do Product Analytics from Your Postgres Database. The L28 curve here and the power user quartile analysis there complement each other well.

5. Feature usage breakdown: top 10

Widget type: horizontal bar chart

Which features are people actually using? Two ways to rank: by unique users (reach) and by total events (intensity). These rankings are often different. A feature with 10 users generating 500 events each might be critical for a small power-user segment; a feature with 200 users averaging 1.1 events each got tried and abandoned.

SELECT
  event_name,
  count(DISTINCT user_id)  AS unique_users,
  count(*)                 AS total_events,
  round(count(*)::numeric / count(DISTINCT user_id), 1) AS events_per_user
FROM events
WHERE created_at >= now() - interval '30 days'
  AND event_name NOT IN ('page_view', 'session_start', 'heartbeat')
GROUP BY event_name
ORDER BY unique_users DESC
LIMIT 10;

Customize the NOT IN clause for your product. Every app has a few high-volume noise events (page views, keep-alives, navigation pings) that swamp everything else. The events_per_user column is the one to pay attention to: a feature with high intensity per user is sticky, not just popular.

6. New vs. returning users and engagement trend

Widget type: stacked area chart (new/returning) + line overlay (events per user)

Two questions in one query. First: is your active user count growing because you're adding new people, or because existing users keep coming back? Second: are active users doing more or less over time?

WITH weekly AS (
  SELECT
    date_trunc('week', e.created_at)::date AS week,
    e.user_id,
    count(*)                               AS events,
    CASE
      WHEN u.created_at >= date_trunc('week', e.created_at)
       AND u.created_at <  date_trunc('week', e.created_at) + interval '7 days'
      THEN 'new'
      ELSE 'returning'
    END AS user_type
  FROM events e
  JOIN users u ON u.id = e.user_id
  WHERE e.created_at >= now() - interval '12 weeks'
  GROUP BY 1, 2, 4
)
SELECT
  week,
  count(DISTINCT user_id) FILTER (WHERE user_type = 'new')       AS new_users,
  count(DISTINCT user_id) FILTER (WHERE user_type = 'returning') AS returning_users,
  count(DISTINCT user_id)                                         AS total_active,
  round(sum(events)::numeric / count(DISTINCT user_id), 1)       AS events_per_user
FROM weekly
GROUP BY week
ORDER BY week;

The events_per_user column is your engagement trend. If it's climbing while user count grows, the product is getting stickier. If it's flat or declining as you add users, your newer cohorts are less engaged than the people who found you early. That's worth catching before it becomes a board-level conversation.

Swap date_trunc('week', ...) for date_trunc('day', ...) if you want daily granularity, but weekly is usually more readable and less noisy for trend analysis.

From queries to a live dashboard

Six queries, six widgets:

You can run all of these in psql and paste results into a spreadsheet. That works right up until someone asks "can I see this without asking you?" or "does this update on its own?" — at which point you need something that takes SQL and turns it into a shareable, auto-refreshing dashboard.

That's what Fastero is built for. Connect your Postgres, paste these queries, and each one becomes a live widget. Schedule refreshes, share with your team, set alerts when a metric moves. No data pipeline to build, no dbt models, no Amplitude contract.

Try Fastero free — connect your Postgres and turn SQL into a live product dashboard. No credit card required.