Harbourline Freight · Logistics

Logistics Operations Analysis

Review a freight company's year: who ships the most, which routes run late, and what customers still owe.

SQLExcelPower BIPythonIntermediate

Overview

The business

Harbourline Freight moves containers and air cargo into West Africa, mainly through Lagos, for manufacturers, retailers and pharmaceutical companies in Nigeria, Ghana and Benin.

The business has grown quickly, but the operations director suspects some routes regularly miss their promised transit times, and the finance team says too much money is still owed on delivered shipments. Nobody has looked at the whole picture in one place.

Your brief

The operations director wants a short review of the last 20 months ahead of the annual planning meeting: who the biggest customers are, how volume has moved, which routes are unreliable, and where unpaid revenue is sitting.

Questions to answer

1Who are the ten highest-volume customers?
2How did monthly shipment volume change?
3Which routes are busiest, and how reliable are they?
4How much revenue is still unpaid, and by whom?

What to deliver

A one-page summary with your three most important findings and a recommendation for each.
Your queries, workbook or notebook, so someone else can check the numbers.
Optional: a one-page dashboard showing volume, on-time delivery and unpaid revenue.

Suggested approach

Look at each file and how they link: shipments connect customers, routes and payments.
Decide what counts: exclude cancelled shipments from volume, and use delivered shipments with both dates for transit time.
Transit time is delivery_date minus ship_date. A shipment is late when that is more than the route's target_transit_days.
Unpaid revenue is the freight charge minus everything paid against the shipment.
Write up the findings in plain language before you build any charts.

Data

Harbourline Freight (logistics). A freight company's customers, shipments, routes, payments and staff, January 2025 to August 2026.

5 files · 5,266 rows in total · 294 kB. Download everything as a ZIP.

customers.csv

120 rows · 7 columns · 8 kB

Download

Companies that ship with Harbourline, one row per customer.

Column	Type	Description	Values	Missing
customer_id	Number	Unique customer number. Links to shipments.customer_id.	1 to 120	None
company_name	Text	Customer's company name.	120 distinct	None
industry	Text	The customer's industry, such as Electronics or Construction.	10 distinct	None
city	Text	City of the customer's main office.	8 distinct	None
country	Text	Nigeria, Ghana or Benin.	3 distinct	None
signup_date	Date	Date the customer opened an account.	2021-01-10 to 2025-01-27	None
account_manager_id	Number	Harbourline employee who manages the account. Links to employees.employee_id. Blank if unassigned.	1 to 8	8

employees.csv

24 rows · 6 columns · 1 kB

Download

Harbourline's staff.

Column	Type	Description	Values	Missing
employee_id	Number	Unique employee number.	1 to 24	None
full_name	Text	Employee's name.	24 distinct	None
role	Text	Job title, such as Account Manager or Customs Specialist.	5 distinct	None
team	Text	Sales, Operations, Customs or Finance.	4 distinct	None
hire_date	Date	Date they joined.	2016-08-27 to 2024-07-27	None
manager_id	Number	Their manager's employee_id. Blank for the most senior staff.	23 to 24	2

payments.csv

2,409 rows · 5 columns · 100 kB

Download

Customer payments against shipments. A shipment can have more than one payment, or none yet.

Column	Type	Description	Values	Missing
payment_id	Number	Unique payment number.	1 to 2,409	None
shipment_id	Number	Shipment being paid for. Links to shipments.shipment_id.	100,001 to 102,641	None
payment_date	Date	Date the money was received.	2025-01-16 to 2026-08-31	None
amount	Number	Amount paid, in naira.	237,000 to 39,033,000	None
method	Text	Card, Bank transfer or Cheque.	3 distinct	None

routes.csv

30 rows · 5 columns · 919 B

Download

The lanes Harbourline operates, one row per route.

Column	Type	Description	Values	Missing
route_id	Number	Unique route number.	1 to 30	None
origin	Text	Where shipments start.	20 distinct	None
destination	Text	Where shipments end.	15 distinct	None
mode	Text	Sea, Air or Road.	3 distinct	None
target_transit_days	Number	Promised door-to-door time in days. A delivery that takes longer is late.	1 to 42	None

shipments.csv

2,683 rows · 10 columns · 183 kB

Download

Every shipment booked from January 2025 to August 2026, one row per shipment.

Column	Type	Description	Values	Missing
shipment_id	Number	Unique shipment number. Links to payments.shipment_id.	100,001 to 102,683	None
customer_id	Number	Customer who booked it. Links to customers.customer_id.	7 to 120	None
route_id	Number	Route used. Links to routes.route_id.	1 to 30	None
booking_date	Date	Date the shipment was booked.	2025-01-02 to 2026-08-31	None
ship_date	Date	Date it left the origin. Blank if it hasn't shipped yet or was cancelled.	2025-01-04 to 2026-09-02	168
delivery_date	Date	Date it arrived. Blank if not delivered.	2025-01-13 to 2026-08-30	272
status	Text	Delivered, In transit, Booked or Cancelled.	4 distinct	None
containers	Number	Number of containers (or equivalent units).	1 to 8	None
weight_kg	Number	Total weight in kilograms.	94 to 191,992	None
freight_charge	Number	Amount charged to the customer, in naira.	376,000 to 39,406,000	None

Get started

Starter code to get you going. The Python runs as it is in Google Colab: it loads the data straight from this site. For SQL, import each CSV as a table with the same name as the file.

SQLTop customers by shipments

SELECT c.company_name,
       COUNT(*) AS shipments,
       SUM(s.containers) AS containers
FROM shipments s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.status <> 'Cancelled'
GROUP BY c.company_name
ORDER BY shipments DESC
LIMIT 10;

PythonLate deliveries by route

import pandas as pd

base = "https://academy.cloudtechanalytics.com/datasets/logistics/"
shipments = pd.read_csv(base + "shipments.csv", parse_dates=["booking_date", "ship_date", "delivery_date"])
routes = pd.read_csv(base + "routes.csv")

delivered = shipments[shipments["status"] == "Delivered"].merge(routes, on="route_id")
delivered["transit_days"] = (delivered["delivery_date"] - delivered["ship_date"]).dt.days
delivered["late"] = delivered["transit_days"] > delivered["target_transit_days"]

delivered.groupby(["origin", "destination"])["late"].mean().sort_values(ascending=False).head(10)

ExcelIn Excel

Open shipments.csv and format it as a Table (Ctrl + T).
Add customer names with XLOOKUP from customers.csv.
Insert a PivotTable: company_name in Rows, count of shipment_id in Values, sorted largest to smallest.
Filter status to exclude Cancelled.

When you've finished

Put your work on GitHub with a short README, add it to your portfolio, and share what you found. The Git & GitHub and Student Portfolio courses show you how.

Loading…

Overview

The business

Harbourline Freight moves containers and air cargo into West Africa, mainly through Lagos, for manufacturers, retailers and pharmaceutical companies in Nigeria, Ghana and Benin.

Your brief

Questions to answer

1Who are the ten highest-volume customers?
2How did monthly shipment volume change?
3Which routes are busiest, and how reliable are they?
4How much revenue is still unpaid, and by whom?

What to deliver

A one-page summary with your three most important findings and a recommendation for each.
Your queries, workbook or notebook, so someone else can check the numbers.
Optional: a one-page dashboard showing volume, on-time delivery and unpaid revenue.

Suggested approach

Look at each file and how they link: shipments connect customers, routes and payments.
Decide what counts: exclude cancelled shipments from volume, and use delivered shipments with both dates for transit time.
Transit time is delivery_date minus ship_date. A shipment is late when that is more than the route's target_transit_days.
Unpaid revenue is the freight charge minus everything paid against the shipment.
Write up the findings in plain language before you build any charts.

Data

Harbourline Freight (logistics). A freight company's customers, shipments, routes, payments and staff, January 2025 to August 2026.

5 files · 5,266 rows in total · 294 kB. Download everything as a ZIP.

customers.csv

120 rows · 7 columns · 8 kB

Download

Companies that ship with Harbourline, one row per customer.

Column	Type	Description	Values	Missing
customer_id	Number	Unique customer number. Links to shipments.customer_id.	1 to 120	None
company_name	Text	Customer's company name.	120 distinct	None
industry	Text	The customer's industry, such as Electronics or Construction.	10 distinct	None
city	Text	City of the customer's main office.	8 distinct	None
country	Text	Nigeria, Ghana or Benin.	3 distinct	None
signup_date	Date	Date the customer opened an account.	2021-01-10 to 2025-01-27	None
account_manager_id	Number	Harbourline employee who manages the account. Links to employees.employee_id. Blank if unassigned.	1 to 8	8

employees.csv

24 rows · 6 columns · 1 kB

Download

Harbourline's staff.

Column	Type	Description	Values	Missing
employee_id	Number	Unique employee number.	1 to 24	None
full_name	Text	Employee's name.	24 distinct	None
role	Text	Job title, such as Account Manager or Customs Specialist.	5 distinct	None
team	Text	Sales, Operations, Customs or Finance.	4 distinct	None
hire_date	Date	Date they joined.	2016-08-27 to 2024-07-27	None
manager_id	Number	Their manager's employee_id. Blank for the most senior staff.	23 to 24	2

payments.csv

2,409 rows · 5 columns · 100 kB

Download

Customer payments against shipments. A shipment can have more than one payment, or none yet.

Column	Type	Description	Values	Missing
payment_id	Number	Unique payment number.	1 to 2,409	None
shipment_id	Number	Shipment being paid for. Links to shipments.shipment_id.	100,001 to 102,641	None
payment_date	Date	Date the money was received.	2025-01-16 to 2026-08-31	None
amount	Number	Amount paid, in naira.	237,000 to 39,033,000	None
method	Text	Card, Bank transfer or Cheque.	3 distinct	None

routes.csv

30 rows · 5 columns · 919 B

Download

The lanes Harbourline operates, one row per route.

Column	Type	Description	Values	Missing
route_id	Number	Unique route number.	1 to 30	None
origin	Text	Where shipments start.	20 distinct	None
destination	Text	Where shipments end.	15 distinct	None
mode	Text	Sea, Air or Road.	3 distinct	None
target_transit_days	Number	Promised door-to-door time in days. A delivery that takes longer is late.	1 to 42	None

shipments.csv

2,683 rows · 10 columns · 183 kB

Download

Every shipment booked from January 2025 to August 2026, one row per shipment.

Column	Type	Description	Values	Missing
shipment_id	Number	Unique shipment number. Links to payments.shipment_id.	100,001 to 102,683	None
customer_id	Number	Customer who booked it. Links to customers.customer_id.	7 to 120	None
route_id	Number	Route used. Links to routes.route_id.	1 to 30	None
booking_date	Date	Date the shipment was booked.	2025-01-02 to 2026-08-31	None
ship_date	Date	Date it left the origin. Blank if it hasn't shipped yet or was cancelled.	2025-01-04 to 2026-09-02	168
delivery_date	Date	Date it arrived. Blank if not delivered.	2025-01-13 to 2026-08-30	272
status	Text	Delivered, In transit, Booked or Cancelled.	4 distinct	None
containers	Number	Number of containers (or equivalent units).	1 to 8	None
weight_kg	Number	Total weight in kilograms.	94 to 191,992	None
freight_charge	Number	Amount charged to the customer, in naira.	376,000 to 39,406,000	None

Get started

Starter code to get you going. The Python runs as it is in Google Colab: it loads the data straight from this site. For SQL, import each CSV as a table with the same name as the file.

SQLTop customers by shipments

SELECT c.company_name,
       COUNT(*) AS shipments,
       SUM(s.containers) AS containers
FROM shipments s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.status <> 'Cancelled'
GROUP BY c.company_name
ORDER BY shipments DESC
LIMIT 10;

PythonLate deliveries by route

import pandas as pd

base = "https://academy.cloudtechanalytics.com/datasets/logistics/"
shipments = pd.read_csv(base + "shipments.csv", parse_dates=["booking_date", "ship_date", "delivery_date"])
routes = pd.read_csv(base + "routes.csv")

delivered = shipments[shipments["status"] == "Delivered"].merge(routes, on="route_id")
delivered["transit_days"] = (delivered["delivery_date"] - delivered["ship_date"]).dt.days
delivered["late"] = delivered["transit_days"] > delivered["target_transit_days"]

delivered.groupby(["origin", "destination"])["late"].mean().sort_values(ascending=False).head(10)

ExcelIn Excel

Open shipments.csv and format it as a Table (Ctrl + T).
Add customer names with XLOOKUP from customers.csv.
Insert a PivotTable: company_name in Rows, count of shipment_id in Values, sorted largest to smallest.
Filter status to exclude Cancelled.

When you've finished

Put your work on GitHub with a short README, add it to your portfolio, and share what you found. The Git & GitHub and Student Portfolio courses show you how.