Fleet Revenue and Expense Dashboard
A visualizaiton of key fleet management metrics.
Purpose
This was a quick dashboard put together to visualize some fleet management metrics. I wanted to visualize the following metrics:
- Net Income per Kilometer
- Fuel Cost per Kilometer
- Visualized cost metrics by cost type
- Visualized revenue, costs, & profit over time by categorization of truck
Data Preparation
This dataset was pulled from Kaggle, and includes 3 dimension tables and 2 fact tables. Below is an entity relationship diagram I created to describe the tables and schema:
Creating the Database
1
docker exec -it postgres-container psql -U postgres
1
2
postgres=# CREATE DATABASE fleetDW WITH OWNER 'fleetowner';
postgres-# \q
1
2
3
4
5
6
docker cp ./dcustomers.csv postgres-container:/dcustomers.csv
docker cp ./ddrivers.csv postgres-container:/ddrivers.csv
docker cp ./dvehicles.csv postgres-container:/dvehicles.csv
docker cp ./fcosts.csv postgres-container:/fcosts.csv
docker cp ./ffreight.csv postgres-container:/ffreight.csv
docker exec -it postgres-container psql -U trees fleetdw;
Creating the Tables and Constraints
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
CREATE TABLE dCustomers (
CustomerID INTEGER NOT NULL,
City TEXT,
State TEXT,
Latitude NUMERIC,
Longitude NUMERIC,
UNIQUE (CustomerID)
);
CREATE TABLE dDrivers (
DriverID INTEGER NOT NULL,
Driver TEXT,
UNIQUE (DriverID)
);
CREATE TABLE dVehicles (
VehicleID INTEGER NOT NULL,
Plate TEXT,
Brand TEXT,
TruckType TEXT,
TrailersType TEXT,
VehicleYear SMALLINT,
UNIQUE (VehicleID)
);
CREATE TABLE fCosts (
CostDate DATE,
TruckID INTEGER NOT NULL,
DriveID INTEGER NOT NULL,
KMTraveled INTEGER,
Liters NUMERIC,
Fuel NUMERIC,
Maintenance NUMERIC,
FixedCosts NUMERIC,
CONSTRAINT fk_costs_vehicles
FOREIGN KEY(TruckID)
REFERENCES dVehicles(VehicleID),
CONSTRAINT fk_costs_driver
FOREIGN KEY (DriveID)
REFERENCES dDrivers(DriverID)
);
CREATE TABLE fFreight (
TripDate DATE,
CustomerID INTEGER NOT NULL,
TruckID INTEGER NOT NULL,
InvoiceNumber INTEGER NOT NULL,
FreightID TEXT NOT NULL,
City TEXT,
NetRevenue NUMERIC,
WeightkG NUMERIC,
WeightCubic NUMERIC,
GoodsValue NUMERIC,
CONSTRAINT fk_freight_customer
FOREIGN KEY (CustomerID)
REFERENCES dCustomers(CustomerID),
CONSTRAINT fk_freight_vehicle
FOREIGN KEY (TruckID)
REFERENCES dVehicles(VehicleID)
);
Importing the Data
1
2
3
4
5
\copy dCustomers(CustomerID, City, State, Latitude, Longitude) FROM 'dcustomers.csv' WITH DELIMITER ',' CSV HEADER;
\copy dDrivers(DriverID, Driver) FROM 'ddrivers.csv' WITH DELIMITER ',' CSV HEADER;
\copy dVehicles(VehicleID, Plate, Brand, TruckType, TrailersType, VehicleYear) FROM 'dvehicles.csv' WITH DELIMITER ',' CSV HEADER;
\copy fCosts(CostDate, TruckID, DriveID, KMTraveled, Liters, Fuel, Maintenance, FixedCosts) FROM 'fcosts.csv' WITH DELIMITER ',' CSV HEADER;
\copy fFreight(TripDate, CustomerID, TruckID, InvoiceNumber, FreightID, City, NetRevenue, WeightkG, WeightCubic, GoodsValue) FROM 'ffreight.csv' WITH DELIMITER ',' CSV HEADER;
We’re ready to query now!
Aggregating for Tableau
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
--The CostsPerMonth CTE aggregates the KM traveled adn the fuel, maintenance, and fixed costs by month for each truck type.
WITH CostsPerMonth AS (
SELECT
DATE_TRUNC('Month',fCosts.CostDate)::DATE AS CostMonth,
dVehicles.TruckType,
SUM(fCosts.Fuel) AS TotalFuel,
SUM(fCosts.Maintenance) AS TotalMaintenance,
SUM(fCosts.FixedCosts) AS TotalFixedCosts,
SUM(fCosts.KMTraveled) AS TotalKM,
SUM(fCosts.Fuel + fCosts.Maintenance + fCosts.FixedCosts) AS TotalCost
FROM fCosts
LEFT JOIN dVehicles
ON fCosts.TruckID = dVehicles.VehicleID
GROUP BY CostMonth, dVehicles.TruckType
HAVING SUM(fCosts.KMTraveled) > 0
),
--The FreightPerMonth CTE aggregates the revenue generated by month for each truck type.
FreightPerMonth AS (
SELECT
DATE_TRUNC('Month',fFreight.TripDate)::DATE AS FreightMonth,
dVehicles.TruckType,
SUM(fFreight.NetRevenue) AS TotalRevenue
FROM fFreight
LEFT JOIN dVehicles
ON fFreight.TruckID = dVehicles.VehicleID
GROUP BY FreightMonth, dVehicles.TruckType
)
SELECT
FreightMonth,
FreightPerMonth.TruckType,
TotalRevenue,
TotalFuel,
TotalMaintenance,
TotalFixedCosts,
TotalKM,
TotalCost,
TotalFuel / NULLIF(TotalKM, 0) AS FuelCostPerKM, --Fuel costs divided by KM traveled.
(TotalRevenue - TotalCost) / NULLIF(TotalKM, 0) AS MarginPerKM, --Net income divided by KM traveled.
TotalCost / NULLIF(TotalKM, 0) AS TotalCostPerKM --Costs divided by KM traveled.
FROM FreightPerMonth
LEFT JOIN CostsPerMonth
ON FreightPerMonth.TruckType = CostsPerMonth.TruckType
AND FreightMonth = CostMonth
ORDER BY CostMonth ASC;
The output of this aggregation is fed into Tableau to create the Fleet Revenue & Expense Tracker dashboard!
This post is licensed under CC BY 4.0 by the author.