BIKE-LIFE-SQL-Excel-and-Tableau

Introduction

In this project I combined SQL, Excel and Tableau. Bike Life's (a retail bicycle company) management wanted to know the sales activities of the company to gain insights to the various trends happening in the sales volume over the 2016-2018 period. They also wanted to know the revenue per region, per store, per product category and per brand, a list of the top customers and sales reps was also insightful. Having understood the needs and goals of the company, I needed to present this information in the most organized and digestible way. My number one task was to make the management team’s lives as easy as possible. As such I settled on Excel and Tableau to show the various metrics. Although either Excel or Tableau would have sufficed I needed to use both of them to show my flexibility in this particular project.

Steps Followed

Step 1

I tapped into the company’s relational database and retrieved the data through sql by writing an sql code to generate a detailed dataset that provided me with all the data and information I needed to put together a dynamic sales dashboard for the executive team.

Step 2

I created a new workbook in Excel and called it Bike stores. I connected the entire Excel workbook to the company’s database and then imported the data set directly into the worksheet which formed a connection between the work book and the data base so that any changes made in the database are automatically reflected in the Excel version of the data set sparing me the trouble of manually having to update the Excel version of the dataset whenever an update is made.

Step 3

In Excel I used pivot tables to generate a dynamic dashboard.

Step 4

I then connected the Excel data sheet that contains the sql generated data set to Tableau, checked to ensure that all fields had been imported correctly and used the data to generate a visually pleasing dashboard for management.

The comprehensive analysis of Bike Life's performance across the years 2016, 2017, and 2018 provides valuable insights into various aspects of the business. Here are the key conclusions drawn from the report:

Brand and Category Performance:

• Baldwin Bikes consistently led in revenue across the years, reflecting its strong market presence.

• Mountain Bikes and Road Bikes emerged as top-performing categories, contributing significantly to the overall revenue.

Sales Representative Contributions:

• Venita Daniel and Marcelene Boyer consistently stood out as top-performing sales representatives, showcasing their dedication and effectiveness in driving sales.

• Genna Serrano, while maintaining a top-three position, demonstrated variability in performance, warranting further analysis and support.

Customer Dynamics:

• The identification of top and bottom customers in each year provides insights into customer spending patterns and loyalty.

• Implementing customer retention strategies and understanding the preferences of high-value customers can further enhance customer relationships.

Category Preferences:

• The treemap analysis of categorywise revenue distribution highlights the popularity of Mountain Bikes and Road Bikes.

• Diversifying products within these popular categories and understanding market demand can contribute to sustained growth.

Geographic and Seasonal Trends:

• Monitoring statewise revenue variations is crucial for strategic planning and resource allocation.

• Understanding seasonal trends can guide inventory management and promotional activities to capitalize on peak months.

Top Customer Analysis:

• Recognizing the highest and lowest spending customers provides opportunities for targeted marketing and personalized engagement.

• Implementing loyalty programs and maintaining strong relationships with high-value customers is essential for long-term success.

In conclusion, a holistic approach to business strategy, including brand management, category optimization, customer engagement, and sales team support, will contribute to sustained growth and success for Bike Life. Continued monitoring, adaptability, and proactive decision-making are key in navigating the dynamic landscape of the bike industry.

Recommendations:

• Implement a recognition program for top-performing sales representatives to boost morale and motivation.

• Provide additional training and support to sales representatives showing variability in performance.

• Diversify product offerings based on category performance and market demand.

• Encourage team collaboration and knowledge-sharing to enhance overall sales team performance.

• Monitor and adjust sales goals regularly to align with individual performance and market conditions.