Metric Architecture
PlaneConnection analytics are built on two data layers:| Layer | Storage | Purpose |
|---|---|---|
| Pre-computed aggregates | Daily rollup tables | Fast dashboard loading with daily rollups. |
| Live queries | Trip, crew, aircraft data | Real-time calculations for detail views. |
Fleet Metrics
| Metric | Calculation | Unit | Source Data |
|---|---|---|---|
| Total Hours | Sum of block time from trip legs in the period | Hours | Trip legs |
| Total Flights | Count of trip legs in the period | Count | Trip legs |
| Fleet Utilization | Total hours / (aircraft count x days in period) | Hours/ac/day | Trip legs, aircraft |
| Revenue / Block Hour | Total revenue / total block hours | Currency/hour | Trip estimates |
| Deadhead Ratio | Non-revenue legs / total legs (per aircraft) | Percentage | Trip legs |
| Aircraft Hours | Sum of block time grouped by aircraft | Hours | Trip legs |
| Daily Fleet Hours | Sum of block time grouped by date | Hours | Trip legs |
Fleet Dimensions
| Dimension | Allows grouping by |
|---|---|
| Aircraft | Individual tail number |
| Aircraft Type | Make and model grouping |
| Date | Daily, weekly, monthly trends |
Crew Metrics
| Metric | Calculation | Unit | Source Data |
|---|---|---|---|
| Total Crew Members | Count of active crew members | Count | Crew roster |
| Avg Weekly Hours | Mean of rolling 7-day flight hours across active crew | Hours | Duty records |
| Currency Compliance | (Crew with all items current) / total active crew | Percentage | Currency data |
| Hours Flown | Sum of flight time from duty records per crew member | Hours | Duty records |
| Duty Hours | Sum of duty periods per crew member | Hours | Duty records |
| Leg Count | Count of trip legs assigned to the crew member | Count | Trip legs |
| K-Score | Composite fatigue score per AC 120-103A | Score | Duty records |
Crew Dimensions
| Dimension | Allows grouping by |
|---|---|
| Crew Member | Individual crew performance |
| Role | PIC, SIC, FA, etc. |
| Date | Daily, weekly trends |
Financial Metrics
| Metric | Calculation | Unit | Source Data |
|---|---|---|---|
| Total Revenue | Sum of revenue from trip estimates | Currency | Trip estimates |
| Total Costs | Sum of fuel + crew + landing + handling + catering + other | Currency | Trip estimates |
| Net Margin | (Revenue - Costs) / Revenue x 100 | Percent | Trip estimates |
| Revenue / Block Hour | Total revenue / total block hours | Currency | Trip estimates |
| Fuel Cost | Sum of fuel costs from trip estimates | Currency | Trip estimates |
| Crew Cost | Sum of crew costs from trip estimates | Currency | Trip estimates |
| Landing Fees | Sum of landing fees from trip estimates | Currency | Trip estimates |
| Handling Fees | Sum of handling fees from trip estimates | Currency | Trip estimates |
Financial Dimensions
| Dimension | Allows grouping by |
|---|---|
| Aircraft | Per-aircraft profitability |
| Month | Monthly P&L |
| Cost Type | Cost composition analysis |
Customer Metrics
| Metric | Calculation | Unit | Source Data |
|---|---|---|---|
| Trip Count | Count of trips per customer | Count | Trips |
| Total Revenue | Sum of revenue per customer | Currency | Trip estimates |
| Last Trip Date | Most recent departure per customer | Date | Trips |
| Customer Segments | Charter vs. owner usage split | Category | Trips |
Route Metrics
| Metric | Calculation | Unit | Source Data |
|---|---|---|---|
| Trip Count | Count of legs per origin-destination pair | Count | Trip legs |
| Total Revenue | Sum of revenue per route | Currency | Trip estimates |
| Total Costs | Sum of costs per route | Currency | Trip estimates |
| Avg Block Time | Average block time per route | Hours | Trip legs |
| On-Time % | (On-time departures / total departures) per route | Percentage | Trip legs |
Anomaly Detection
The anomaly engine evaluates the following conditions at page load:| Check | Severity | Trigger condition |
|---|---|---|
| Zero-hour aircraft | Warning | Aircraft with zero flight hours in the selected period. |
| Low OTP | Warning | On-time percentage below configurable threshold. |
| Duty limit approach | Critical | Crew member within 5% of 14 CFR 135.267 weekly limit. |
| Overdue maintenance | Critical | Maintenance due items past their due date. |
| Cost spike | Warning | Daily cost exceeds 2x the period average. |
AI Insights
AI-generated insights are stored in theanalytics_insights table and categorized as:
| Category | Description |
|---|---|
| Opportunity | Revenue or efficiency improvement suggestion. |
| Alert | Condition requiring attention. |
| Trend | Notable pattern in the data. |
| Anomaly | Statistical outlier detected. |
Data Explorer
The Data Explorer provides access to the following data sets for ad-hoc queries:| Data Set | Available Fields |
|---|---|
| Trips | Trip number, status, origin, destination, departure time, arrival time, aircraft |
| Trip Legs | Trip reference, origin, destination, departure time, arrival time, block time, flight time |
| Trip Estimates | Cost and revenue estimate fields per trip |
| Aircraft | Registration, make, model, status |
| Crew Members | Crew roster with role |
Natural-Language Queries
The Ask Copilot feature maintains a history of your queries. Each entry records:- The natural-language question asked
- The generated query and its results summary
- Execution time and row count
- Whether the query executed successfully