Building a Useful Analytics Dashboard

You shipped a dashboard full of colorful charts, yet the business owner still messages you to ask whether sales went up this month. That means the dashboard shows numbers, not answers. The second problem shows up later: the page gets slower as order data grows.

This guide covers both sides. First, how to choose metrics that drive decisions. Second, how to build them in Laravel 11 with efficient aggregate queries, Chart.js, caching, and a daily summary table for large datasets.

1. Start with Questions, Not Charts

Before writing a query, sit with the people who will use the dashboard and list the questions they ask every week. For a shop or café, the list often looks like this:

• Is revenue for the last 30 days higher than the 30 days before?
• Which products sell best, and which ones do not move?
• Which hours and days are busiest, so staff schedules make sense?
• What is the average order value (AOV)?

A simple rule: a metric earns a place if some decision changes when the number changes. All-time visitors fails that test because it only goes up.

Keep the main view to four to six numbers and move the rest to drill-down pages.

2. Summary Cards with Comparison

$12,000 in sales is meaningless until you know last month's figure. Let the database do the math instead of loading thousands of rows into PHP.

use App\Models\Order;

$now   = now();
$start = $now->copy()->subDays(30);
$prev  = $now->copy()->subDays(60);

$current = Order::where('created_at', '>=', $start)
    ->selectRaw('COUNT(*) as orders, COALESCE(SUM(total), 0) as revenue')
    ->first();

$previousRevenue = Order::whereBetween('created_at', [$prev, $start])->sum('total');

$aov    = $current->orders > 0 ? $current->revenue / $current->orders : 0;
$growth = $previousRevenue > 0
    ? round(($current->revenue - $previousRevenue) / $previousRevenue * 100, 1)
    : null; // null = no baseline yet

Order count and revenue come from one query, and growth is null when there is no previous data, so the UI can say "no baseline yet" instead of a misleading 0%.

3. Time Series Without Gaps

$daily = Order::where('created_at', '>=', $start)
    ->selectRaw('DATE(created_at) as day, SUM(total) as revenue')
    ->groupBy('day')
    ->orderBy('day')
    ->pluck('revenue', 'day');

$labels = [];
$values = [];
for ($i = 29; $i >= 0; $i--) {
    $date     = now()->subDays($i)->toDateString();
    $labels[] = $date;
    $values[] = (int) ($daily[$date] ?? 0);
}

Days with no orders are missing from a GROUP BY result. Without the zero-fill loop, the line jumps straight from Monday to Thursday. Also set the app timezone in config/app.php to the business location so an 11:30 pm sale lands on the right day.

4. Top Products and Busy Hours

$topProducts = OrderItem::query()
    ->join('orders', 'orders.id', '=', 'order_items.order_id')
    ->where('orders.created_at', '>=', $start)
    ->selectRaw('order_items.product_name, SUM(order_items.qty) as sold')
    ->groupBy('order_items.product_name')
    ->orderByDesc('sold')
    ->limit(5)
    ->get();

$busyHours = Order::where('created_at', '>=', $start)
    ->selectRaw('HOUR(created_at) as hour, COUNT(*) as orders')
    ->groupBy('hour')
    ->pluck('orders', 'hour');

HOUR() and DATE() work in MySQL and MariaDB. In PostgreSQL use EXTRACT(HOUR FROM created_at) and created_at::date.

5. Feeding Chart.js

<canvas id="revenueChart"></canvas>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
new Chart(document.getElementById('revenueChart'), {
    type: 'line',
    data: {
        labels: @json($labels),
        datasets: [{ label: 'Daily revenue', data: @json($values), tension: 0.3 }]
    }
});
</script>

@json encodes arrays safely, so a quote in a product name will not break your script.

6. Keeping It Fast

The thresholds below are rough estimates for a small VPS. Measure with your own data.

An index is the cheapest first step; more techniques are in optimizing Eloquent query performance. For large tables, store one row per day in daily_sales (unique date, orders, revenue):

// routes/console.php
use App\Models\Order;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schedule;

Schedule::call(function () {
    $day = now()->subDay()->toDateString();

    $row = Order::whereDate('created_at', $day)
        ->selectRaw('COUNT(*) as orders, COALESCE(SUM(total), 0) as revenue')
        ->first();

    DB::table('daily_sales')->updateOrInsert(
        ['date' => $day],
        ['orders' => $row->orders, 'revenue' => $row->revenue, 'updated_at' => now()]
    );
})->dailyAt('00:15');

The dashboard then reads 30 summary rows plus today's live figures. If aggregation gets heavy, push it to a queued job as described in Laravel queues and jobs.

7. A Layout That Reads Naturally

Eyes scan from the top left to the bottom right, so use that order:

1. Top row: main KPI cards, each with an up or down arrow and the percentage change.
2. Second row: the 30-day revenue trend, one line only.
3. Third row: top products table and a busy-hours bar chart side by side.
4. Bottom: links to detailed reports, a date range filter, and an export button.

Use color sparingly. Green and red are enough for up and down. On phones, stack cards in one column, because owners often check numbers between other tasks.

8. Restrict Who Can See It

Revenue and margin are not for every employee. Define a Gate and attach it to the route:

// app/Providers/AppServiceProvider.php, inside boot()
Gate::define('view-dashboard', fn (User $user) => in_array($user->role, ['owner', 'manager']));

// routes/web.php
Route::get('/dashboard', [DashboardController::class, 'index'])
    ->middleware(['auth', 'can:view-dashboard']);

The role column is an assumption; adapt it to your users table.

Common Mistakes and Fixes

• Too many charts on one screen. Cut to four to six KPIs and move the rest to detail pages.
• Wrong chart type. Use lines for trends over time and horizontal bars for comparing products. A ten-slice pie chart is unreadable.
• Counting cancelled orders. Filter by status, for example only paid, so revenue is not inflated.
• Stale cache. Call Cache::forget() on order creation when numbers must be fresh.
• Numbers never reconciled. One small mismatch with register reports and the owner stops trusting the whole dashboard. Check a sample before hand-off.

Hand-off Checklist

• Every KPI shows a previous-period comparison or a target.
• Date range and timezone are visible on screen.
• Days without sales appear as zero.
• The page loads in under two seconds with real data.
• Figures were checked by hand against register reports for one sample day.
• The owner can answer their weekly questions without asking a developer.

A useful dashboard is not the one with the most charts. It is the one that shortens the path from question to decision.