Appearance
Metrics · Executive View
The tables in the metrics schema are pre-aggregated. Use them for dashboards, executive reports and quick answers — no need to scan millions of rows from the operational tables.
Why they exist
Tables in the lms schema are rich but heavy: answering "how many new learners this week?" would require count(*) over lms.users filtered by date. On large accounts that gets expensive. Tables in metrics solve this by exposing the number already computed per day, per tenant, with a primary key ready to join.
Granularities
| Grain | Tables |
|---|---|
| Per day · tenant | metrics.daily_counters, metrics.user_activity_snapshots |
| Per day · tenant · product | metrics.daily_product_certificates |
| Per day · tenant · lesson | metrics.daily_lesson_events |
| Per hour · tenant · day | metrics.hourly_events |
| Current · tenant · lesson | metrics.lesson_rating_aggregates, metrics.lesson_progress_aggregates |
| Current · tenant · track | metrics.track_access_aggregates |
KPI map
| Executive question | Table | Column |
|---|---|---|
| How many new learners today? | metrics.daily_counters | users |
| DAU / WAU / MAU? | metrics.user_activity_snapshots | active_yesterday, active_7d, active_30d |
| How many certificates issued today? | metrics.daily_counters | certificates_issued |
| Which products generate the most certificates? | metrics.daily_product_certificates | issues_total, issues_unique |
| Exam pass / fail counts? | metrics.daily_counters | exams_passed, exams_failed |
| Best-rated lessons? | metrics.lesson_rating_aggregates | likes, dislikes, total |
| Most-consumed lessons today? | metrics.daily_lesson_events | total |
| What time of day do learners study most? | metrics.hourly_events | total |
| Who watched each lesson? | metrics.lesson_progress_aggregates | user_ids (array) |
| Who has access to each track? | metrics.track_access_aggregates | user_ids, user_ids_paid, user_ids_manual |
| Volume of messages, questions, comments? | metrics.daily_counters | user_messages, questions_created, comments_created |
Latency
Metrics are refreshed once a day at 04:00 UTC. Yesterday's data lands in the morning. Don't use metrics for real-time decisions — use the operational lms.* tables for that.
Usage pattern
Every table in metrics has id_tenant in its PK. To get a customer-wide view, aggregate across all tenants:
sql
-- Last-30-days consolidated DAU
SELECT date, sum(active_yesterday) AS dau
FROM metrics.user_activity_snapshots
WHERE date >= now() - interval '30 days'
GROUP BY date
ORDER BY date;