Your app is collecting data. Lots of it. Every user action, every transaction, every event — it's all landing in your database. But when your customers log in, what do they actually see?
A table of rows. Maybe a count. If you're generous, a bar chart baked into a static page that you wrote six months ago and haven't touched since.
This is the analytics gap — and it's one of the most common ways SaaS products quietly lose customers to competitors who figured out that giving users insight into their own data is a product feature, not a nice-to-have.
In this article, we'll look at what customer-facing analytics actually means, why it's hard to get right, and what the SQL layer underneath it needs to look like. (If you're looking for a tool built specifically for this problem, Draxlr is worth a look.)
What "Customer-Facing Analytics" Actually Means
Customer-facing analytics (sometimes called user-facing or embedded analytics) is any reporting, chart, or data summary you expose to your end users — not your internal team, but your customers.
Examples:
- A project management tool showing a team their task completion rate by week
- An e-commerce platform giving merchants a revenue breakdown by product category
- A fintech app letting users track their spending trends over time
The key insight: your customers are making decisions based on the data that lives in your database. If you're not surfacing that data in a useful way, they're either exporting CSVs and building spreadsheets — or switching to a product that does it for them.
The SQL Foundation: Designing for Analytics Queries
The difference between a database designed for writes and one that performs well for analytics reads is significant. Most SaaS apps optimize for OLTP (transactional workloads), which means fast inserts and row lookups. Analytics queries are the opposite: they scan large ranges, aggregate across many rows, and join multiple tables.
Here's a typical transactional query:
SELECT * FROM orders WHERE id = 12345;
Here's what an analytics query looks like:
SELECT
DATE_TRUNC('month', created_at) AS month,
COUNT(*) AS total_orders,
SUM(amount_cents) / 100.0 AS revenue,
AVG(amount_cents) / 100.0 AS avg_order_value
FROM orders
WHERE
tenant_id = 'acme-corp'
AND created_at >= NOW() - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;
This query will scan potentially millions of rows. Without the right indexes, it'll be slow — and when hundreds of customers run queries like this simultaneously, your database will feel it.
What helps:
- Index on
(tenant_id, created_at)for time-range queries scoped to a tenant - Partial indexes for common filters (e.g.,
WHERE status = 'completed') - Materialized views for expensive aggregations you compute once and refresh periodically
-- Materialized view for monthly revenue per tenant
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
tenant_id,
DATE_TRUNC('month', created_at) AS month,
COUNT(*) AS order_count,
SUM(amount_cents) / 100.0 AS revenue
FROM orders
GROUP BY 1, 2;
-- Refresh nightly or on demand
REFRESH MATERIALIZED VIEW monthly_revenue;
Now your dashboard query becomes a fast lookup against a pre-aggregated table instead of a full scan.
The Multi-Tenancy Problem You Can't Ignore
Every customer-facing analytics feature lives under a hard constraint: Customer A must never see Customer B's data.
This sounds obvious, but it's surprisingly easy to get wrong when you're under pressure to ship. A missing WHERE tenant_id = ? clause in one query, and you have a data leak.
The safest pattern is to enforce tenant scoping at the query level, not the application level. In PostgreSQL, Row Level Security (RLS) is built for exactly this:
-- Enable RLS on the orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- Policy: users only see rows for their own tenant
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
Then, when your app opens a database connection for a given user session, it sets the tenant context:
SET app.current_tenant_id = 'acme-corp-uuid-here';
Every query after that automatically filters to that tenant's rows, no matter what SQL gets executed. It's a database-level safety net that protects you even if application code has a bug.
Common Analytics Queries Your Customers Actually Want
Here are the patterns that show up in almost every SaaS product's analytics layer.
Usage over time:
SELECT
DATE_TRUNC('week', created_at) AS week,
COUNT(*) AS events
FROM user_events
WHERE tenant_id = $1
AND event_type = 'page_view'
AND created_at >= NOW() - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;
Top items by a metric:
SELECT
p.name AS product_name,
SUM(oi.quantity) AS units_sold,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.tenant_id = $1
AND oi.created_at >= $2
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 10;
Cohort retention (month 0 vs. month 1 still active):
WITH cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', MIN(created_at)) AS cohort_month
FROM subscriptions
WHERE tenant_id = $1
GROUP BY user_id
)
SELECT
c.cohort_month,
COUNT(DISTINCT c.user_id) AS cohort_size,
COUNT(DISTINCT s.user_id) AS retained_month_1
FROM cohorts c
LEFT JOIN subscriptions s
ON s.user_id = c.user_id
AND DATE_TRUNC('month', s.created_at) = c.cohort_month + INTERVAL '1 month'
GROUP BY 1
ORDER BY 1;
These are the queries that turn raw transactional data into insight. The SQL is rarely the hard part — the hard part is building the infrastructure around it.
Common Mistakes (And How to Avoid Them)
1. Running analytics queries on your production OLTP database
Your primary database is handling writes, your API reads, and now heavy analytic scans too. Under load, these long-running queries block connection slots and degrade the entire app. Use a read replica for analytics traffic, or move to a separate analytics database for larger datasets.
2. Skipping query caching entirely
Most analytics queries are expensive and most users look at the same date ranges. Cache the results (Redis, Postgres materialized views, or your analytics tool's built-in cache) and refresh on a schedule. A customer viewing "last 30 days revenue" at 9am and again at 10am doesn't need two full scans.
3. Giving customers raw table data instead of aggregated insight
Customers don't want to read a 50,000-row table. They want answers. Build queries that aggregate, summarize, and rank — then build charts on top of those summaries.
4. Not indexing on tenant_id
If your tables have no index on tenant_id, every query scans the full table before filtering. A composite index like (tenant_id, created_at DESC) covers most analytics query patterns efficiently.
CREATE INDEX idx_orders_tenant_created
ON orders (tenant_id, created_at DESC);
5. Building custom reports for every request
Teams that skip building self-service analytics end up fielding a constant stream of "can you add a chart for X?" requests. Embedding a configurable dashboard — even a simple one — cuts this dramatically.
The Build vs. Buy Decision
At some point every engineering team faces this question: do we build our own analytics UI, or embed an existing tool?
Building in-house gives you full control over the look and feel, but it's expensive. Charts, filters, date pickers, export functionality, and access controls — each one is a non-trivial feature. Most teams underestimate the ongoing maintenance cost.
The alternative is an embedded analytics tool that sits on top of your existing SQL database, renders charts in an iframe or web component, and handles multi-tenancy and auth for you. You write SQL, the tool handles the UI.
The right choice depends on how central analytics is to your product. If "analytics" is a secondary feature that customers occasionally check, embedded tools are almost always the faster path. If analytics is your core product differentiation, building custom makes sense.
Key Takeaways
- Your customers want insight into their own data — if you're not providing it, you're leaving retention on the table
- Design your SQL queries and indexes for analytics workloads, not just transactional ones
- Enforce tenant isolation at the database level (Row Level Security) to prevent data leaks by design
- Use materialized views and read replicas to keep analytics fast without hammering your production database
- Cache aggressively — most analytics queries return the same results for many users within a short window
- Don't wait until you have a perfect analytics infrastructure; a few well-designed SQL queries exposed through a simple dashboard will get you further than you think
What does your analytics setup look like? Are you building custom charts, embedding a tool, or still at the "CSV export" stage? Drop a comment below — I'm curious how teams at different stages are tackling this.
