Post

Fleet Revenue and Expense Dashboard

A visualizaiton of key fleet management metrics.

Click here to open the interactive Tableau dashboard!

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:

Desktop View

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!

Click here to open the interactive Tableau dashboard!

This post is licensed under CC BY 4.0 by the author.