What happens when a spreadsheet full of transport bookings becomes a story about revenue, customer behaviour, cancellations, and business opportunities?
Every dataset has a story.
The challenge for a data analyst is figuring out how to tell it.
For this project, I worked with SafariConnect, a fictional transportation booking company with a dataset containing customer and booking information. At first glance, it was simply a collection of rows and columns.
But behind those rows were questions that a business would actually care about:
- How much revenue is being generated?
- How many bookings are being made?
- Which travel class do customers prefer?
- How many customers cancel their bookings?
- How satisfied are customers?
- What patterns could help the business make better decisions?
My goal was to take SafariConnect through a complete analytics workflow:
Raw Data → Data Cleaning → SQL → Transformation → Analysis → Power BI → Insights → Recommendations
This article documents that journey from beginning to end.
Project Overview
Project: SafariConnect Transport Booking Analytics
Role: Junior Data Analyst
Tools Used:
- SQL
- Power BI
- Data Cleaning & Transformation
- Data Analysis
- Data Visualisation
Dataset: Transportation booking data
Final Clean Dataset: 289 records
Total Revenue: KES 226K+
Cancellation Rate: 7%
Average Customer Rating: 3.53/5
1. The Problem: Data Without Direction
Imagine you're managing SafariConnect.
You have hundreds of booking records sitting in a spreadsheet.
You can see booking IDs, customers, travel classes, booking amounts, cancellation statuses, ratings and other information.
But if someone asks:
"How is the business actually performing?"
Looking at the spreadsheet won't immediately give you the answer.
You need to transform the raw records into information that management can understand and act upon.
Therefore, the main objective of this project was:
To analyse SafariConnect's booking data and develop an interactive business intelligence dashboard that reveals revenue performance, booking behaviour, customer satisfaction and cancellation patterns.
The analysis was designed around several key business questions.
Business Questions
Revenue
- What is the total revenue generated?
- How does revenue vary across different categories?
Bookings
- How many bookings were made?
- Which travel class is most popular?
Cancellations
- What percentage of bookings were cancelled?
- Are cancellations significant enough to require attention?
Customer Experience
- What is the average customer rating?
- What does the rating suggest about customer satisfaction?
These questions became the foundation of the entire project.
2. Understanding the Dataset
Before touching SQL or Power BI, I first needed to understand the data.
This is one of the steps that can easily be skipped when you're eager to start building charts.
I didn't skip it.
The dataset contained approximately 300 records before cleaning and included information relating to:
- Booking identification
- Customer information
- Journey details
- Travel class
- Booking amount
- Cancellation status
- Customer ratings
- Other booking attributes
Each row represented a booking transaction.
The first task was therefore to understand:
What does each column mean?
What type of data does it contain?
Can I trust the values?
Are there duplicates or inconsistencies?
This initial exploration helped determine what needed to be cleaned before analysis.
Raw Dataset Screenshot
The raw SafariConnect dataset before cleaning and transformation.
3. Data Cleaning
One of the biggest misconceptions about data analytics is that the main job is creating beautiful dashboards.
It isn't.
A dashboard is only as good as the data behind it.
If your underlying data is inconsistent, your beautiful visualisations can simply become beautifully presented mistakes.
I therefore began by cleaning and validating the dataset.
The cleaning process included:
Removing duplicates
Duplicate records can inflate:
- Booking counts
- Revenue
- Cancellation numbers
- Other KPIs
I checked the dataset for duplicate records and removed irrelevant duplicates where necessary.
Standardising categorical values
Categorical columns such as travel class and booking status need consistent values.
For example, if the same category appears as:
Economy
economy
ECONOMY
a dashboard may interpret these as three different categories.
Standardisation ensured that equivalent values were treated consistently.
Handling missing values
Missing values were identified and assessed based on their relevance to the analysis.
Rather than blindly deleting every row containing a blank value, I considered whether the missing information would affect the specific analysis being performed.
Validating numerical fields
Revenue and rating fields were checked to ensure that numerical values were correctly interpreted.
Checking categorical fields
Fields such as cancellation status and travel class were reviewed for inconsistencies and unexpected values.
After cleaning, the dataset contained:
289 usable records
This cleaned dataset became the foundation for the rest of the project.
Cleaned Dataset Screenshot
The dataset after cleaning, validation and standardisation.
4. Structuring the Data
Once the data was cleaned, I needed a structure that would support analysis.
The core dataset was organised around the booking transaction.
At a high level, the booking data contained fields representing:
| Category | Example Purpose |
|---|---|
| Booking ID | Unique booking identification |
| Customer Data | Customer-related information |
| Journey Data | Trip-related information |
| Travel Class | Economy or other classes |
| Booking Amount | Revenue generated |
| Cancellation Status | Booking outcome |
| Rating | Customer satisfaction |
The important idea here was to ensure that every record could be consistently analysed.
A clean analytical dataset makes it much easier to answer questions such as:
"How much revenue came from Economy bookings?"
or:
"What percentage of bookings were cancelled?"
5. SQL: Turning Business Questions Into Queries
Once the dataset was structured, SQL became one of the most useful tools in the workflow.
SQL allowed me to move from:
"I wonder..."
to:
"Let's calculate it."
For example, if management wants to know the total revenue:
SELECT
SUM(booking_amount) AS total_revenue
FROM bookings;
To determine the total number of bookings:
SELECT
COUNT(*) AS total_bookings
FROM bookings;
To analyse bookings by travel class:
SELECT
travel_class,
COUNT(*) AS total_bookings
FROM bookings
GROUP BY travel_class
ORDER BY total_bookings DESC;
To analyse cancellation status:
SELECT
cancellation_status,
COUNT(*) AS booking_count
FROM bookings
GROUP BY cancellation_status;
And to calculate the average customer rating:
SELECT
AVG(rating) AS average_rating
FROM bookings;
These queries transformed raw transactions into measurable business metrics.
SQL Queries Screenshot
SQL queries used to aggregate, validate and analyse SafariConnect booking data.
6. From Numbers to KPIs
After cleaning and querying the data, I identified the metrics that could provide the clearest overview of SafariConnect's performance.
The final dashboard focused on four major KPIs.
Total Revenue
KES 226K+
This represents the total revenue generated from the analysed booking records.
Total Bookings
289
This represents the number of usable booking records after data cleaning.
Cancellation Rate
7%
Approximately 7% of the analysed bookings were cancelled.
Average Rating
3.53/5
The average customer rating was approximately 3.53.
Together, these metrics provided a high-level snapshot of the business.
But KPIs alone don't tell the whole story.
That's where visualisation came in.
7. Building the Power BI Dashboard
With the data cleaned and the key metrics identified, I moved into Power BI.
The purpose wasn't to create a dashboard filled with as many charts as possible.
The goal was to create something that a stakeholder could open and understand within seconds.
The dashboard therefore focused on:
- Revenue
- Bookings
- Cancellation rate
- Customer ratings
- Travel class preferences
- Booking patterns
Interactive filtering was also incorporated to allow the data to be explored from different perspectives.
Power BI Dashboard Screenshot
SafariConnect interactive Power BI dashboard showing the key business performance indicators and booking patterns.
8. What Did the Data Actually Tell Me?
This is where the project became interesting.
The objective wasn't to calculate numbers simply because we could.
The objective was to understand what those numbers meant.
Insight #1: SafariConnect Generated Over KES 226K
The analysed dataset generated more than:
KES 226,000 in revenue
This provided an important baseline for understanding the financial performance represented in the dataset.
But revenue becomes more useful when we start breaking it down.
Instead of asking only:
"How much money did we make?"
we can ask:
"Where is that revenue coming from?"
This opens the door to deeper analysis by travel class, route, customer segment and other dimensions.
Insight #2: Economy Class Was the Most Preferred
One of the clearest patterns in the dataset was the dominance of Economy Class bookings.
This suggests that affordability and price sensitivity may play an important role in the customer base.
From a business perspective, this could influence:
- Pricing strategies
- Promotional campaigns
- Capacity planning
- Customer segmentation
- Loyalty programmes
The important lesson here is that customer preferences shouldn't be based purely on assumptions.
The data can tell us what customers are actually choosing.
Travel Class Visualisation Screenshot
Economy Class emerged as the most preferred travel option among SafariConnect customers.
9. Cancellation Rate: Small Number, Important Question
The cancellation rate was approximately:
7%
At first glance, 7% may not appear alarming.
Most bookings were not cancelled.
But this raises another question:
Why are customers cancelling?
A cancellation is more than a percentage.
It can represent:
- Lost revenue
- Empty seats
- Operational inefficiencies
- Customer dissatisfaction
- Planning difficulties
The current dataset provides the cancellation rate, but a deeper analysis could investigate cancellation behaviour by:
- Travel class
- Route
- Customer segment
- Booking lead time
- Time period
- Payment method
This is an example of how one KPI can lead to another analytical question.
Cancellation Analysis Screenshot
Cancellation analysis showing the proportion of bookings that were cancelled.
10. Customer Satisfaction: 3.53/5
The average customer rating was:
3.53 out of 5
This places customer satisfaction somewhere in the middle.
It's not a catastrophic score.
But it also isn't a score that suggests customers are overwhelmingly delighted.
That creates an opportunity for further investigation.
For example:
- Which journeys receive the lowest ratings?
- Do cancelled bookings have lower ratings?
- Are some travel classes rated better than others?
- Are certain routes associated with lower satisfaction?
The dashboard therefore doesn't just answer questions.
It creates new questions worth investigating.
And that's an important part of data analytics.
Customer Rating Visual Screenshot
Average customer rating of 3.53/5, highlighting an opportunity to further investigate customer experience.
11. The Relationship Between Bookings and Revenue
Another important lesson from the analysis is that booking volume and revenue are not necessarily the same thing.
Imagine two categories:
Category A
- 150 bookings
- KES 50,000 revenue
Category B
- 80 bookings
- KES 70,000 revenue
If we only looked at booking volume, Category A would appear more successful.
If we only looked at revenue, Category B would win.
This is why good analysis requires multiple metrics to be viewed together.
For SafariConnect, I considered:
Bookings + Revenue + Cancellations + Ratings
Together, these metrics provide a much more complete picture of business performance.
12. Challenges I Faced
Every data project comes with challenges.
SafariConnect was no different.
Challenge 1: Raw data quality
The original dataset wasn't immediately ready for analysis.
Solution:
I performed systematic cleaning, validation and standardisation before creating the final analytical dataset.
Challenge 2: Translating business questions into technical questions
"Understand the business" is too broad to analyse.
Solution:
I converted broad objectives into measurable KPIs.
For example:
Business question:
How is the company performing?
became:
- What is total revenue?
- How many bookings were made?
- What percentage were cancelled?
- What is the average customer rating?
Challenge 3: Dashboard clutter
It can be tempting to put every available metric on one page.
Solution:
I prioritised the metrics that directly answered the project's business questions.
The goal was clarity, not quantity.
Challenge 4: Validating the results
A wrong calculation can still produce a convincing-looking chart.
Solution:
I cross-checked calculated values and validated the transformed data before relying on the dashboard results.
13. Recommendations
Based on the analysis, SafariConnect could consider several actions.
1. Leverage Economy Class demand
Since Economy was the most preferred travel class, SafariConnect could develop targeted pricing and promotional strategies around this segment.
2. Investigate cancellations
The 7% cancellation rate should be monitored and analysed further to understand the underlying causes.
3. Improve customer experience
The 3.53/5 average rating suggests there is room for improvement.
Customer feedback should be analysed alongside journey and booking characteristics to identify specific pain points.
4. Analyse revenue beyond booking volume
Management should monitor revenue alongside booking counts to identify the most financially valuable segments.
5. Continue monitoring performance
The dashboard can serve as a foundation for continuous reporting as new booking data becomes available.
14. What I Learned
This project taught me several lessons that go beyond writing SQL queries or creating Power BI charts.
Data cleaning isn't the boring part
It is tempting to rush toward visualisation.
But reliable analysis begins with reliable data.
Garbage in, garbage out.
SQL is about asking better questions
SQL isn't just syntax.
The real skill is knowing what question you're trying to answer and then translating that question into a query.
A dashboard needs a story
A dashboard shouldn't force someone to interpret dozens of unrelated charts.
It should guide them.
What happened?
↓
Why does it matter?
↓
What should we investigate?
↓
What action could we take?
Insights need business context
Finding that Economy Class is the most popular category is interesting.
Understanding what that means for pricing, marketing and capacity planning is where the analysis becomes valuable.
Every answer creates another question
The 7% cancellation rate led to:
"Why are customers cancelling?"
The 3.53 rating led to:
"What is causing dissatisfaction?"
The popularity of Economy led to:
"How can SafariConnect better serve this customer segment?"
That is what makes analytics exciting.
You don't simply finish an analysis.
You uncover the next question.
15. Project Workflow
The complete SafariConnect workflow can be summarised as:
Raw Dataset
↓
Data Exploration
↓
Data Cleaning
↓
Data Validation
↓
Database / Data Structure
↓
SQL Queries
↓
Data Transformation
↓
KPI Calculation
↓
Power BI Visualisation
↓
Analysis
↓
Business Insights
↓
Recommendations
This is what makes SafariConnect an end-to-end analytics project rather than simply a Power BI dashboard.
16. Final Dashboard
The final Power BI dashboard brought the analysis together into one interactive view.
It transformed hundreds of individual booking records into a concise business story covering:
Revenue.
Bookings.
Cancellations.
Customer satisfaction.
Travel preferences.
Final Dashboard Screenshot
Final SafariConnect Power BI dashboard.
If you're publishing this on DEV, I recommend placing your strongest dashboard screenshot here because this is the point where the reader can finally see the result of the entire workflow.
Conclusion: The Real Product Wasn't the Dashboard
When I started the SafariConnect project, I had a dataset.
By the end, I had something much more useful:
a story supported by data.
The project took SafariConnect's raw booking records and transformed them into measurable business insights.
The analysis revealed:
- 289 cleaned booking records
- KES 226K+ in analysed revenue
- 7% cancellation rate
- 3.53/5 average customer rating
- Economy Class as the most preferred travel option
But the most valuable outcome wasn't the numbers themselves.
It was learning how to move through the entire analytical process:
Understand the problem → Clean the data → Query the data → Analyse the results → Visualise the findings → Extract insights → Recommend action.
That's the real role of a data analyst.
Not simply creating charts.
Not simply writing SQL.
Not simply cleaning spreadsheets.
But turning data into something a business can understand and act on.
And SafariConnect was another step in learning how to do exactly that.
Project Resources
Tools: SQL | Power BI | Data Cleaning | Data Analysis | Data Visualisation
Project Type: End-to-End Data Analytics
Domain: Transportation / Travel
Dataset: SafariConnect Booking Data
Top comments (0)