Analytics & Summary Endpoints Overview

Most tracking APIs focus on individual parcel operations: create, read, update, track. But operations teams need a higher-level view. How many parcels were delivered on time last week? What are the most common exception reasons? How does Express service compare to Economy in average delivery time? Analytics endpoints answer these questions by aggregating data across many parcels into concise summaries.

Why Analytics Endpoints?

Consider a logistics dashboard for a warehouse manager. They do not want to page through thousands of individual parcels. They need answers like:

• Delivery performance: Out of 5,000 parcels shipped this month, 92% arrived on time
• Exception patterns: "RecipientUnavailable" accounts for 45% of all exceptions
• Service comparison: Overnight averages 18 hours; Express averages 38 hours
• Current pipeline: 120 parcels in transit, 30 out for delivery, 8 in exception status

These are aggregation queries. The database groups rows, counts them, averages values, and returns summary numbers instead of individual records.

Endpoint Design

Our analytics API exposes four GET endpoints. All are read-only and return structured summary objects.

Date Range Parameters

The delivery stats and exception reasons endpoints accept from and to query parameters to scope the data to a specific date range:

GET /api/analytics/delivery-stats?from=2025-01-01&to=2025-01-31

If no date range is provided, the endpoint defaults to the last 30 days. This prevents accidentally scanning the entire database when a caller forgets to pass dates.

The Pipeline Endpoint

The pipeline endpoint is different. It returns a real-time snapshot of how many parcels are in each status right now. There is no date range because the question is about the current state, not historical data.

GET /api/analytics/pipeline

Aggregation Patterns in EF Core

EF Core translates LINQ grouping and aggregation methods into SQL GROUP BY, COUNT, AVG, and SUM queries. The key methods are:

• GroupBy - groups rows by a key (status, service type, exception reason)
• Count - counts rows in each group
• Average - computes the mean of a numeric column
• Sum - totals a numeric column across the group

A typical pattern looks like this:

csharp
1var results = await context.Parcels
2    .Where(p => p.CreatedAt >= from && p.CreatedAt <= to)
3    .GroupBy(p => p.Status)
4    .Select(g => new
5    {
6        Status = g.Key,
7        Count = g.Count()
8    })
9    .ToListAsync();

EF Core generates a single SQL query with GROUP BY and COUNT(*). The database engine performs the aggregation, and only the summary rows come back over the wire.

Calculating Delivery Time

Average delivery time requires a calculation: the difference between ActualDeliveryDate and CreatedAt for parcels that have been delivered. In EF Core, you compute this with EF.Functions.DateDiffHour or by computing the difference in your projection and averaging it.

On-Time Percentage

On-time percentage compares ActualDeliveryDate to EstimatedDeliveryDate. A parcel is on time if it was delivered on or before its estimated date. The calculation is:

OnTimePercentage = (count of on-time deliveries / total deliveries) * 100

This requires a conditional count, which you can express with Count(p => condition) or a combination of Where and Count.

Summary DTOs

Analytics endpoints return structured DTO objects, not raw entities. Each endpoint has its own response shape.

DeliveryStatsDto

csharp
1public class DeliveryStatsDto
2{
3    public DateTime From { get; set; }
4    public DateTime To { get; set; }
5    public int TotalParcels { get; set; }
6    public int Delivered { get; set; }
7    public int InTransit { get; set; }
8    public int Exceptions { get; set; }
9    public double AverageDeliveryTimeHours { get; set; }
10    public double OnTimePercentage { get; set; }
11}

ExceptionReasonDto

csharp
1public class ExceptionReasonDto
2{
3    public string Reason { get; set; }
4    public int Count { get; set; }
5    public double Percentage { get; set; }
6}

ServiceBreakdownDto

csharp
1public class ServiceBreakdownDto
2{
3    public string ServiceType { get; set; }
4    public int Count { get; set; }
5    public double AverageDeliveryTimeHours { get; set; }
6}

PipelineStatusDto

csharp
1public class PipelineStatusDto
2{
3    public string Status { get; set; }
4    public int Count { get; set; }
5}

These DTOs are simple, flat objects. They contain only the data the client needs, with no navigation properties or database concerns.

Response Caching

Analytics queries scan and aggregate many rows. Running them on every request wastes database resources when the data does not change frequently. Response caching tells the server (and optionally the client) to reuse a previous response for a specified duration.

ASP.NET Core provides the [ResponseCache] attribute for controller actions:

csharp
1[ResponseCache(Duration = 300)]
2public async Task<ActionResult<DeliveryStatsDto>> GetDeliveryStats(...)

This sets a Cache-Control: public, max-age=300 header, telling clients and proxies to cache the response for 5 minutes.

Server-Side vs. Client-Side Caching

The [ResponseCache] attribute sets HTTP cache headers. For server-side caching, you also need to add the response caching middleware:

csharp
1builder.Services.AddResponseCaching();
2// ...
3app.UseResponseCaching();

With both pieces in place, the middleware caches the response in memory and serves it directly for subsequent requests within the cache duration, without hitting your controller action or the database.

Cache Duration Considerations

Choose cache duration based on how stale the data can be:

• Pipeline status: 30-60 seconds (operations teams want near-real-time visibility)
• Delivery stats: 5-10 minutes (metrics do not change drastically minute to minute)
• Exception reasons: 10-15 minutes (pattern analysis tolerates some delay)
• Service breakdown: 10-15 minutes (similar tolerance)

The exact durations depend on business requirements. The key point is that analytics data does not need real-time accuracy, and caching dramatically reduces database load.

Summary

In this lesson, you learned:

• Analytics endpoints aggregate data across many parcels to produce summary statistics
• Four endpoints cover delivery stats, exception reasons, service breakdown, and pipeline status
• EF Core translates LINQ GroupBy, Count, and Average into efficient SQL aggregation queries
• Response DTOs are flat, purpose-built objects for each endpoint
• Response caching reduces database load for expensive aggregation queries
• Cache duration should match the freshness requirements of each endpoint

Next, we will implement the EF Core aggregation queries that power these endpoints.