I built an End-to-End Bike Shop Dashboard and it is more than just a "pretty chart"—it’s about the journey of data from a raw server to a clear business decision. It's a comprehensive sales report for a Bike Shop built using SQL Server and Power BI.
This project was a deep dive into answering real-life business questions through strategic data manipulation and thoughtful UI design.
🚲 THE PROJECT SCOPE
The goal was simple but ambitious: create a tool that allows stakeholders to understand sales performance at a glance while being able to drill down into the specifics of their inventory.
In this project, I acted as a Lead Data Analyst for Rahimah Bike Share to address a critical business challenge: developing a pricing strategy for the upcoming year based on historical performance. The goal was to build a dashboard that tracks key performance metrics—specifically hourly revenue, profit, and rider demographics—to support informed decision-making.
The Technical Stack
- Data Source: SQL Databases (accessed via Power BI).
- Tool: Power BI.
- Methodology: Data cleaning, DAX modeling, and interactive visualization.
- Credits: Special thanks to Absent Data for the project framework and tutorial.
Data Extraction (The SQL Foundation)
The journey began in SQL Server. Instead of just "dumping" data into Power BI, I focused on extracting clean, relevant datasets. This stage was crucial for ensuring that the model remained performant and that the data relationships were logical before any visualization took place.
The Request vs. The Reality
Every great dashboard starts with a business request. In this scenario, the primary needs were:
Visibility: Identifying which bike categories were driving the most revenue. The Rider Demographics & Seasonality are as follows:
User Segments: Registered riders dominate the platform, making up 81.17% of the 3M total riders.
Seasonal Peak: Season 3 is the highest revenue generator at $4.9M, followed by Season 2 at $4.2M.
Granularity: Understanding the "Average Price" points without the clutter of irrelevant totals. These are the Financial Metrics:
- Total Revenue: Reached $15M.
- Profit: Successfully generated $10.45M.
- Profit Margin: Currently stands at 0.45.
Trend Analysis: Seeing how sales fluctuated over time.
Hourly Performance: Analysis reveals that 10:00 to 15:00 is the most profitable window.
Day-of-Week Variation: Wednesdays and Fridays show notably higher sales, indicating variable profitability across the week.
RECOMMENDATION AND STRATEGY BASED ON THE DATA
The report doesn't just show "what happened"; it hints at "what to do." Considering the substantial increase implemented last year, a conservative hike is prudent to avoid hitting a "price ceiling" where demand drops. When the price was increase by $1(25%) the year before, there was a huge increase in demand(64%). If the price is further increased by 25%, there would be a price elasticity of 2.56, but we must bear in mind that other market factors could come into play.
- Recommended Range: A 10-15% increase.
- Price Point 1 (10%): If the 2022 price was $4.99, a 10% increase sets the new price at $5.49.
- Price Point 2 (15%): A 15% increase sets the price at approximately $5.74.
Market Strategy
- Segmented Pricing: Differentiate pricing for casual versus registered users, as they exhibit different price sensitivities.
- Agile Monitoring: Implement new prices while closely monitoring immediate customer feedback and sales data to fine-tune the strategy.
🎨 Design & UI: The "Pop" of Color
One of my biggest takeaways from this project was the importance of UI Design. I wanted the dashboard to feel modern and professional.
Contrast & Clarity: I used a deep blue header and vibrant orange accents (like the bike icon) to guide the user's eye to key metrics.
The "Callout" Style: I utilized callout cards to highlight the most important KPIs, ensuring that the "Big Numbers" are the first thing a viewer sees.
Custom Formatting: I spent a significant amount of time refining the Matrix views—specifically learning how to handle DAX measures to show Average Price accurately without including confusing "Total" sums.
đź’ˇ What I Learned
Through this build, I sharpened several key skills:
Data Manipulation: Handling joins and cleaning data within the SQL-to-Power-BI pipeline.
DAX Logic: Writing specific measures to ensure the data told the right story (and hiding those pesky "Average Totals"!).
User Experience: Designing with the end-user in mind, ensuring the report is intuitive and answers questions before they are even asked.
CONCLUSION
The resulting dashboard provides a single source of truth for Rahimah Bike Shop. It moves beyond simple "pretty visuals" to provide a recommendation engine that manages outliers and tracks median prices to ensure long-term profitability.
Bottom line: Leveraging data-driven insights allows the business to increase revenue while protecting its most valuable asset: its 3M-strong rider community. A huge shoutout to the Absent Data YouTube channel for the inspiration. Taking a concept from a tutorial and rebuilding it from scratch allowed me to truly understand the why behind the design and the how behind the data.
Top comments (0)