Elsayed Fuyyad (Elsayed Ahmed)

Case study: Examining demographics of bike buyers

Scenario

A fictional bike seller company is presenting the demographic data of its bike buyers and those who didn’t buy bikes. The task is to explore trends and relationships using the company’s historical data.

Analysis Tools

For analysis and visualization purposes, I used EXCEL in this project.

Ask

Business task

Exploring the dataset to see which demographic data could lead (not) to increasing bike sales.

Key stakeholders

The company’s CEO
Sales/ marketing mangers/director
Sales/ marketing teams

Prepare

· The data set used in this case study can be found here.

· This data is made available by Alex the Analyst on his YouTube channel.

· It is a public dataset.

· The data was updated on March 21, 2022

· The Excel file contains 1 worksheet entitled: bike buyers.

· There is no date range for the data.

· The table consists of 13 columns: ID, Marital Status, Gender, Income, Children, Education, Occupation, Home Owner, Cars, Commute Distance, Region, Age and Purchased Bike

· The data is reliable, original, comprehensive, current, and cited.

· The data contains no null values.

Process

· 1000 duplicates have been removed from the table.

· In Column B, 539 ‘M’ values were replaced with ‘Married.’

· In Column B, 463 ‘S’ values were replaced with ‘Single.’

· In Column C, 489 ‘F’ values were replaced with ‘Female.’

· In Column C, 511 ‘M’ values were replaced with ‘Male.’

· ‘Age Brackets’ column was created in column M, containing the values: ‘Midde Age’ when the value in the Age column >31, ‘senior citizen’ when it is >54, and ‘adolescent’ when it is =<31

· In Column ‘commute distance’, 111 ‘10+ Miles’ values were replaced with ‘More than 10 miles’.

Analyze

Average income of bike buyers

· By using PivotTables, the data was aggregated and manipulated to meet the business task.

· The average income of males who bought a bike is 60,124 USD per annum.

· The average income of females who bought a bike 55,774 USD per annum.

· The average income of males who didn’t buy a bike is 56,208 USD per annum.

· The average income of females who didn’t buy a bike is 53,440 USD per annum.

Average commuting distance of bike buyers

· The percentage of commuters of 0-1 miles who bought a bike is 42%.

· The percentage of commuters of 1-2 miles who bought a bike is 16%.

· The percentage of commuters of 2-5 miles who bought a bike is 20%.

· The percentage of commuters of 5-10 miles who bought a bike is 16%.

· The percentage of commuters of 10+ miles who bought a bike is 7%.

· The percentage of commuters of 0-1 miles who didn’t buy a bike is 32%.

· The percentage of commuters of 1-2 miles who didn’t buy a bike is 18%.

· The percentage of commuters of 2-5 miles who didn’t buy a bike is 13%.

· The percentage of commuters of 5-10 miles who didn’t buy a bike is 22%.

· The percentage of commuters of 10+ miles who didn’t buy a bike is 15%.

Age range of bike buyers

· The percentage of adolescents who bought a bike is 8%.

· The percentage of middle aged people who bought a bike is 80%.

· The percentage of senior citizens who bought a bike is 12%.

· The percentage of adolescents who didn’t buy a bike is 14%.

· The percentage of middle aged people who didn’t buy a bike is 61%.

· The percentage of senior citizens who didn’t buy a bike is 25%.

Owned cars

· The percentage of o car owners who bought a bike is 31%.

· The percentage of 1 car owners who bought a bike is 32%.

· The percentage of 2 car owners who bought a bike is 26%.

· The percentage of 3 car owners who bought a bike is 7%.

· The percentage of 4 car owners who bought a bike is 4%.

· The percentage of o car owners who didn’t buy a bike is 18%.

· The percentage of 1 car owners who didn’t buy a bike is 22%.

· The percentage of 2 car owners who didn’t buy a bike is 42%.

· The percentage of 3 car owners who didn’t buy a bike is 10%.

· The percentage of 4 car owners who didn’t buy a bike is 7%.

Marital status

· Overall, married people buy more cars with a percentage of 54% of the total bikes sold.

· Within the married population, only 43% bought a bike.

· On the other hand, Singles who bought a bike constitute 54% of their total population.

Region

· The least number of sold bikes is in the Pacific region, with only 23% bought bikes in that region.

Children

· Those with no children constitute the highest number of those who bought bikes and those who didn’t segments, with percentages of 30% and 27% respectively.

· Those with 5 children constitute the lowest number of those who bought bikes and those who didn’t segments, with percentages of 12% and 4% respectively.

Profession

· The highest percentage of people who bought bikes is 31% for Professional workers.

· The lowest percentage of people who bought bikes is 11% for Manual laborers.

· The highest percentage of people who didn’t buy bikes is 27% for Skilled Manual laborers.

· The lowest percentage of people who didn’t buy bikes is 12% for Manual laborers.

Homeowners

· The percentage of people who owned a home and bought bikes is 68%.

· The percentage of people who don’t own a home and bought bikes is 32%.

· The percentage of people who owned a home and didn’t buy bikes is 69%.

· The percentage of people who don’t own a home and didn’t buy bikes is 31%.

Data visualization can be found here.

Act

Upon examining the data, one can find that:

- - There is no significant difference in the average income of those who bought a bike and those who didn’t. However, average male income is slightly higher than females’; so, there is a need to create a balanced marketing campaign targeting both genders.
  - Commuting distance plays an important part in bike purchases: 2-5 miles+ commuting results in lower bike sales. The company should address that by, for example, manufacturing faster and more durable bikes for long distances and the sales/marketing teams should promote that aspect.
  - Adolescents and senior citizens are the least age groups for buying bikes. Two separate campaigns should be directed towards these age groups to increase the sales.
  - The more cars customers have, the less is their the desire to own a bike. However, the company could promote using bikes for car owners as a sustainable eco-friendly solution at least at weekends and holidays, if not all time.
  - Single people are buying less bikes; the company needs to address this issue by introducing campaigns that are suitable for that lifestyle, associating notions of freedom, being on the go, speed among other things in the targeted campaign. Also, presenting free accessories such as bike locks, and some discounts on some models for single buyers.
  - Also, married people sales could increase when introducing solutions for families such as payment/family plans; promotions like buy 1 bike, get the second with a 50% discount, etc., and reintroducing the bike riding experience as a shared family experience through marketing and ad campaigns.
  - Pertaining to the previous point, the more children people have, the less bikes they buy. So, incorporating this factor with the previous one could be a suitable solution.
  - The least number of sold bikes is to manual laborers; the company should introduce some reduced payments and installment plans for this category.
  - A marketing/ad campaign targeting non-homeowners should be made, highlighting that our bikes are affordable, sustainable, light, and easy to store.

Page updated

Google Sites

Report abuse

Scenario

Analysis Tools

Ask

Prepare

Process

Analyze

Share

Act