Bike Sales Analysis: Unveiling Demographic Trends and Strategic Insights
Introduction:
This data analysis project which was performed on MS Excel delves into bike sales, leveraging a dataset comprising essential buyer information such as ID, Marital Status, Gender, Income, Children, Education, Occupation, Home Ownership, Number of Cars, Commute Distance, Region, Age, and Bike Purchase Status.
Data Preparation:
- Working Sheet Creation: To maintain data integrity, a new sheet called “Working Sheet” was established, housing the dataset copied from the original “Bike Buyers” sheet.
2. Data Cleansing:
— Unique Data Check: Utilizing the filter function, unique data in each column was examined.
— Removal of Duplicates: Duplicate entries were eliminated using the “Remove Duplicate” feature, resulting in 1000 unique values.
Clarity Enhancement:
— Marital Status: A temporary column was created to replace “S” with “Single” and “M” with “Married” for clarity of the Marital Status column using the IF statement
=IF(B3=”M”,”Married”,”Single”)
— Gender: Similarly, a temporary column was created to replace “M” with “Male” and “F” with “Female” for clarity of the Gender Column using the IF statement.
=IF(C3=”F”,”Female”,”Male”)
— Age Bracket: A new column, “Age Bracket,” was introduced to categorize ages into Adolescent, Middle Age, and Old utilizing a nested IF statement
=IF(L2>54,”Old”,IF(L2>=31,”Middle Age”,(IF(L2<31,”Adolescent”,”Invalid”))))
Data Analysis:
- Pivot Table Creation: A Pivot Table sheet was generated to facilitate in-depth analysis.
2. Analysis Tables and Visualizations:
— Average Income per Purchase: A table and visualization showcased the average income of customers per purchase.
— Purchased Bikes by Customer Age Bracket: Another table and visualization illustrated the number of purchased bikes by customer age bracket.
— Purchased Bikes by Commute Distance: A table and visualization were created to analyze bike purchases based on commute distance.
Dashboard Creation:
A comprehensive dashboard was crafted using the generated visualizations, incorporating slicers for Marital Status, Region, and Education to enable interactive filtering.
Recommendations:
- Target Marketing to Males: Since males purchased more bikes than females, targeted marketing campaigns tailored to male preferences and interests could amplify sales further.
- Focus on Middle-Aged Demographic: With middle-aged individuals displaying a higher propensity for bike purchases compared to older and adolescent demographics, strategic efforts should be directed towards this segment, such as offering promotions or discounts targeted at this age group.
- Emphasize Urban Mobility: Given that shorter commute distances correlate with higher sales, emphasizing the benefits of biking for urban mobility, such as environmental sustainability and cost-effectiveness, could drive increased sales in urban areas.
Conclusion:
Through meticulous data analysis and insightful observations, this project has uncovered key demographic trends and provided actionable recommendations to enhance bike sales strategies. By leveraging these insights, businesses can optimize their marketing efforts and product offerings to capitalize on emerging opportunities in the bike sales market.