From Raw Logistics Data to Business Insights: An Excel Analytics Project (RFM Analysis)

-

Analyzed logistics shipment data in Excel to evaluate delivery performance, cost, transit time, distance trends. Extended the analysis with RFM-based carrier segmentation to understand carrier utilization and spending patterns, supporting more data-driven operational decisions.

My objective was to explore two practical business questions:

  1. How are delivery delays and shipment outcomes behaving across time and distance?
  2. How can carrier performance be evaluated using cost, transit time, shipment activity, and delivery performance?

Starting with data quality

I first reviewed the dataset for completeness and consistency.

The initial cleaning process included identifying missing delivery dates and transportation costs, checking transit-day inconsistencies, and investigating numerical outliers.

One thing I wanted to avoid was treating every unusual value as bad data.

After null-value treatment, the working dataset contained 1,928 shipment records, while the dataset used after the selected outlier treatment contained 1,669 records. 

Understanding delivery performance

The next step was to move from individual shipment records to operational KPIs using Excel PivotTables.

Across the 1,669 records used for the final analysis, approximately 82.2% were marked Delivered, while about 9.4% were Delayed. The remaining records were distributed across In Transit, Lost, and Returned statuses.

I then explored shipment status across months and different distance bands.

Evaluating carriers beyond shipment volume

One of the main objectives of the project was carrier analysis.

I compared carriers across several dimensions, including:

shipment frequency, transportation expenditure, average shipment cost, average transit time, and shipment-status distribution.

This was important because shipment volume alone doesn't indicate carrier performance.

Adapting RFM analysis to logistics

I also wanted to experiment with RFM analysis, but this dataset does not contain a Customer ID.

Rather than presenting it incorrectly as customer segmentation, I adapted the RFM concept into a Carrier RFM Segmentation model.

For each carrier, I calculated:

Recency — how recently the carrier handled a shipment
Frequency — the number of shipments handled
Monetary — the total transportation expenditure associated with the carrier

I used 1 January 2024 as the reference date for Recency and created three-level rankings for the R, F, and M dimensions.

I used RFM as a way of understanding carrier utilization and spending patterns, which can then be considered alongside cost, transit time, and delivery performance.

Building the dashboard

Finally, I brought the analysis together through an Excel dashboard supported by PivotTables.

The dashboard focuses on:

Delivery Trends → Status by Distance → Carrier Shipment Patterns → Transportation Cost

with the underlying tables retained separately so that the calculations remain traceable.

For me, this was an important part of the project because a dashboard should not exist only to make the workbook visually attractive.

Each visualization should answer a specific business question.

What I learned

The biggest takeaway from this project wasn't a particular Excel function.

It was the importance of moving through analytics in the right order:

Understand the business question → Profile the data → Validate data quality → Transform carefully → Define KPIs → Analyze patterns → Visualize → Interpret → Communicate

I also learned that the same analytical technique can mean something different depending on the business context.

RFM is a good example. A framework commonly used for customer segmentation can be adapted to study carrier utilization—but its interpretation needs to change with the business problem.

Observations


Excel Workbook that I've worked


Comments

Post a Comment

Popular posts from this blog

Understanding Titanic Survival Predictions with Decision Trees

-
Image
  Decision Tree: Supervised learning Algorithm Can do both Regression and Classification capabilities Inverted tree like structure with bunch of if-else statements The Titanic dataset is one of the most famous case studies in data science. It captures passenger details such as age, sex, class, fare, and family aboard, along with whether they survived or not. In this post, we’ll walk through how a decision tree model uses these features to predict survival, and what the visualization reveals. How the Decision Tree Works Note: These features are based on the importance table at the right side Root node: “Was the passenger male?” If female , survival chances are much higher. The tree then considers passenger class and fare to refine predictions. If male , survival chances drop significantly, but age and family aboard (siblings/spouses) still play a role. Each node in the tree shows: Gini impurity: How mixed the group is (lower / purer). Samples: Number of passengers in that group. ...
Read more

Unable to access the PaloAlto VM image via HTTPS

-
Image
Recently, I encountered an Issue of "Unable to access the PaloAlto VM image via HTTPS". Here's how I applied troubleshooting steps to resolve it: Configuration Changes done before: Configured static management IP & default gateway and rebooted the PA VM, then faced the issue. Observed: Unable to access the PaloAlto VM image via HTTPS. Unable to ping to the default Gateway from the PA and viceversa. Error- getting Destination Host Unreachable in both cases. Analyzed: + Checked the Management IP address and netmask, both are same as bridged network (physical) + Checked the default route "show routing route" - no route configured + While pinging from the base machine to PA and vice versa getting error as "Destination is Unreachable". Hypothesized: + As per the Error message, primarily issue is Network related, that the PA VM is unable to reach physical NIC IP. Tested: + Tried with changing the management IP address within the same subnet (as physic...
Read more