Insight into the Automobile Industry For Car Dealerships
A deep dive into the automobile industry for car dealerships
Tobou Egbekun
9/1/20253 min read
Tools: Power BI, SQL, Excel
Source of Data: Kaggle(Data was generated by Mockaroo)
Project Goal
In this project, I take a look at data from the automobile industry, studying customer trends and patterns to discover how car dealerships who are looking to scale into foreign countries can know exactly what sort of brands they should be looking to partner with, what customer segments to focus on that will provide the best profit outcome.
Methodology:
I started my Cleaning by checking for null values to ensure analysis accuracy and Exploratory Data Analysis by using SQL to create a dummy table where I can explore the dataset without interfering with the original data.
select * from sales_analysis.cars_analys;
insert into sales_analysis.cars_analys
select * from cars_analysis ;
select * from cars_analys;
< Screenshot below>
Explored the Total Sales per Country using the Query Below:
SELECT country, COUNT(*) AS total_sales
FROM cars_analys
GROUP BY country
ORDER BY total_sales DESC;
Result: China, Indonesia, Russia, Philippines, Brazil were the top 5 countries with the most sales.
Most Popular Car Brands by Country
Most popular car brands per country
SELECT country, car_brand, COUNT(*) AS brand_sales
FROM cars_analys
GROUP BY country, car_brand
ORDER BY country, brand_sales DESC;
Result: This showed the most popular brands in each country.
The Most Popular Car Models per Brand
Most popular car brands per country
SELECT country, car_brand, COUNT(*) AS brand_sales
FROM cars_analys
GROUP BY country, car_brand
ORDER BY country, brand_sales DESC;
Result: This showed the most popular Car Models in each country.
Most common car Colours
SELECT car_color, COUNT(*) AS color_popularity
FROM cars_analys
GROUP BY car_color
ORDER BY color_popularity DESC;
Result: The top 5 colours customers got were Indigo, Crimson, Turquoise, Purple and Red
Most Used Credit Card Types
SELECT credit_card_type, COUNT(*) AS sales_count
FROM cars_analys
GROUP BY credit_card_type
ORDER BY sales_count DESC;
Result: The top 5 most used credit cards are Jcb, mastercard, maestro, switch and american express
Best Selling Brand Per Country
SELECT country, car_brand, brand_sales
FROM (
SELECT country, car_brand, COUNT(*) AS brand_sales,
RANK() OVER (PARTITION BY country ORDER BY COUNT(*) DESC) AS rnk
FROM cars_analys
GROUP BY country, car_brand
) t
WHERE rnk = 1;
Result: The Best Selling Brand in each country using a window function.
Top 5 Car Models per Country
SELECT *
FROM (
SELECT country,
car_model,
COUNT(*) AS model_sales,
RANK() OVER (PARTITION BY country ORDER BY COUNT(*) DESC) AS rnk
FROM cars_analysis
GROUP BY country, car_model
) t
WHERE rnk <= 5;
Result: This shows the top 5 car models per country using a window function
After using SQL to explore the dataset and get this insights, i used Power BI to get proper visuals and representation of these findings.
Summary of Findings and Visuals Using Power BI
Total Sales:
The dataset had a total of 30,000 purchases recorded
Top 5 used Credit Cards:
JCB
Mastercard
Maestro
Switch
American Express
JCB processed 42.39% of transactions for customers.
Top 5 Countries with the highest Purchases
China
Indonesia
Russia
Phillipines
Brazil
China has the highest Purchases(5,465)
Top 5 Brand with the highest Purchases
Ford
Chevrolet
Toyota
Dodge
GMC
Ford has the highest number of purchases with 2,562.
Key Insights and Data Driven Recommendations
Insight on Market Entry:
China might have the highest number of sales but there will be more competition because the market is likely saturated with more recognised dealerships. It would be better to start with Brazil where the competition would be lower and relatively easier to market the brand identity with lower costs. Easier Market capture and higher profit margins.
Asia cannot be ignored and dealerships looking to scale could choose to start in the Philippines.
Insight on Brand Partnership:
Ford, Chevrolet and Toyota are top performers across multiple countries including Asia and South America. For a dealership looking to scale, securing partnerships with well established brands like these, will ensure that inventory will not exceed more than the quantity necessary for sale or revenue compared to niche brands.
Insight on Inventory and Operations
JCB and Mastercard account for over 60% of transactions, The dominance of JCB(42.39%) makes it evident that payment processing systems have to be optimised for JCB transactions and negotiate favourable rates with JCB providers to directly reduce operational costs.