Order Analytics & Reporting

The Orders module provides real-time analytics via the OrderAnalyticsService with PostgreSQL materialized views for high-performance reporting at scale.

API Endpoints

All analytics endpoints accept from and to query parameters for date range filtering:

?from=2026-01-01&to=2026-03-31

Dashboard Metrics

GET /api/v1/orders/analytics/dashboard?from=2026-03-01&to=2026-03-31

Response:

{
  "data": {
    "total_orders": 1250,
    "total_revenue": 3750000.00,
    "avg_order_value": 3000.00,
    "unique_customers": 890,
    "by_status": {
      "pending": 45,
      "confirmed": 120,
      "processing": 80,
      "shipped": 200,
      "delivered": 750,
      "cancelled": 55
    },
    "by_payment_status": {
      "paid": 800,
      "cod": 350,
      "pending": 45,
      "partial": 10,
      "refunded": 45
    }
  }
}

Revenue Time Series

Daily revenue and order counts:

GET /api/v1/orders/analytics/revenue?from=2026-03-01&to=2026-03-31

Response:

{
  "data": [
    { "day": "2026-03-01", "orders": 42, "revenue": 126000.00 },
    { "day": "2026-03-02", "orders": 38, "revenue": 114000.00 },
    { "day": "2026-03-03", "orders": 55, "revenue": 165000.00 }
  ]
}

Fulfillment Metrics

Delivery, cancellation, and RTO rates:

GET /api/v1/orders/analytics/fulfillment?from=2026-03-01&to=2026-03-31

Response:

{
  "data": {
    "total": 1250,
    "delivered": 750,
    "cancelled": 55,
    "rto": 30,
    "delivery_rate": 60.0,
    "cancellation_rate": 4.4,
    "rto_rate": 2.4
  }
}

Source Breakdown

Which modules/channels orders come from:

GET /api/v1/orders/analytics/sources?from=2026-03-01&to=2026-03-31

Response:

{
  "data": [
    { "source_module": "store-shopify", "count": 800, "revenue": 2400000.00 },
    { "source_module": "manual", "count": 300, "revenue": 900000.00 },
    { "source_module": "csv-import", "count": 150, "revenue": 450000.00 }
  ]
}

Top Products

Products ranked by order volume:

GET /api/v1/orders/analytics/products?from=2026-03-01&to=2026-03-31&limit=10

Response:

{
  "data": [
    { "name": "Premium Widget", "sku": "PW-001", "total_quantity": 320, "total_revenue": 960000.00 },
    { "name": "Basic Gadget", "sku": "BG-001", "total_quantity": 250, "total_revenue": 500000.00 }
  ]
}

COD vs Prepaid Split

GET /api/v1/orders/analytics/cod-vs-prepaid?from=2026-03-01&to=2026-03-31

Response:

{
  "data": {
    "cod_count": 350,
    "prepaid_count": 800,
    "cod_revenue": 1050000.00,
    "prepaid_revenue": 2400000.00
  }
}

Service Usage

use Modules\Orders\App\Services\OrderAnalyticsService;

$analytics = app(OrderAnalyticsService::class);

$from = '2026-03-01';
$to = '2026-03-31';

// Dashboard summary
$metrics = $analytics->getDashboardMetrics($from, $to);

// Revenue time series (daily)
$series = $analytics->getRevenueTimeSeries($from, $to);

// Source breakdown
$sources = $analytics->getSourceBreakdown($from, $to);

// Fulfillment rates
$fulfillment = $analytics->getFulfillmentMetrics($from, $to);

// COD vs Prepaid
$split = $analytics->getCodVsPrepaid($from, $to);

// Top 10 products
$products = $analytics->getTopProducts($from, $to, 10);

PostgreSQL Materialized Views

For high-traffic tenants, the module uses materialized views for pre-computed analytics. These are refreshed automatically every 15 minutes via RefreshAnalyticsViewsJob.

mv_daily_order_stats

Pre-aggregated daily statistics:

Column	Type	Description
day	date	Date
total_orders	int	Orders placed
total_revenue	decimal	Total revenue
avg_order_value	decimal	Average order value
cod_count	int	COD orders
prepaid_count	int	Prepaid orders
unique_customers	int	Distinct customers

mv_order_source_stats

Per-source daily statistics:

Column	Type	Description
source_module	string	Order source
day	date	Date
total_orders	int	Orders from this source
total_revenue	decimal	Revenue from this source
avg_order_value	decimal	Average value from this source

Refresh Strategy

// In OrdersServiceProvider
$this->app->afterResolving(Schedule::class, function (Schedule $schedule) {
    $schedule->job(new RefreshAnalyticsViewsJob())
        ->everyFifteenMinutes()
        ->withoutOverlapping();
});

The views use REFRESH MATERIALIZED VIEW CONCURRENTLY which doesn't lock reads during refresh. This requires unique indexes on the views, which are created by the migration.

When to Use Views vs Live Queries

Scenario	Approach
Dashboard widgets	Use materialized views for instant load
Date-range reports	Live queries (small result sets)
Real-time monitoring	Live queries
Historical exports	Materialized views + live queries

The OrderAnalyticsService currently uses live queries by default. For tenants with 100K+ orders, switch the service to read from materialized views for dashboard metrics.

Database Indexes for Analytics

The following indexes optimize analytics queries:

Index	Columns	Purpose
Composite	(status, created_at)	Status-filtered date queries
Composite	(payment_status, created_at)	Payment status reports
Composite	(source_module, created_at)	Source breakdown queries
Composite	(customer_id, created_at)	Per-customer analytics
BRIN	created_at	Time-range scans (compact, efficient for ordered data)
Partial	WHERE status = 'pending'	Fast count of pending orders
Partial	WHERE status = 'ready_to_ship'	Fast count of ready-to-ship