SQL - Automobile Database Analysis with Interactive Dashboard by Looker Studio

Introduction

     This dataset is mocked-up automobile customer information that was made to segment customer groups for different communication plans, advertisements, and future product launching.

Even the most popular model of business is sixth, fourth, third, and others respectively. How we can specify customer groups more intensely with the most of transactions are come from Y and X generations?

My SQL Analysis with Looker Studio Visualization

     Firstly, I separated customers into two groups; household customers and single customers because I believe that having family affected the buying decision of customers whether budget or car size.

-- Creating Household Customer Segmentation

CREATE TABLE Family_Cust AS

SELECT 

ID,

Buying_Power,

Model,

COUNT (*) N_Customers

FROM Customers

WHERE Model IS NOT NULL AND Ever_Married = "Yes" AND Family_Size >= 2.0

GROUP BY 2,3

ORDER BY 4 DESC

-- Creating Single Customer Segmentation

CREATE TABLE Single_Cust AS

SELECT 

ID,

Buying_Power,

Model,

COUNT (*) N_Customers

FROM Customers

WHERE Model IS NOT NULL AND Ever_Married = "No"

GROUP BY 2,3

ORDER BY 4 DESC

I filtered the null value in a model column out and assumed that customers who have a married history and are having family members more than or equal to two people are the household customer group. Almost half of the customers are average buying power group and another half are high and low buying power customers. Although the order of sold models is the same but marketers can use buying power information to design suitable strategies for every group of customers. Meanwhile, all single customers are low buying power customers with the same order of popular sold models. Marketers have to concentrate on implementing valuable perspectives due to the price concerns of this group of customers. After that, this dataset only has age information among customers. To analyze behavior in the buying decision, I decided to categorize customers into generations. Moreover, we can fully utilize advertising media from this information and gain brand recognition. 

-- Creating Generation Segmentation

-- Beginning with querying customer age range

SELECT

MIN (Age),

MIN(Age)

FROM Customers

-- Post-War Generation

CREATE TABLE PostWar_Cust AS

WITH sub AS (

SELECT * FROM Customers

WHERE Age BETWEEN 78 AND 95

)

SELECT

ID,

Gender,

Buying_Power,

Model,

COUNT (*) N_Customers,

'PostWars' AS Generation

FROM sub

WHERE Model IS NOT NULL

GROUP BY 2,3,4

ORDER BY 5 DESC

-- Creating other generations by changing WITH sub AS query

CREATE TABLE Boomers_Cust AS

WITH sub AS (

SELECT * FROM Customers

WHERE Age BETWEEN 69 AND 77

)

CREATE TABLE Jones_Cust AS

WITH sub AS (

SELECT * FROM Customers

WHERE Age BETWEEN 59 AND 68

)

CREATE TABLE X_Cust AS

WITH sub AS (

SELECT * FROM Customers

WHERE Age BETWEEN 43 AND 58

)

CREATE TABLE Y_Cust AS

WITH sub AS (

SELECT * FROM Customers

WHERE Age BETWEEN 27 AND 42

)

CREATE TABLE Z_Cust AS

WITH sub AS (

SELECT * FROM Customers

WHERE Age BETWEEN 11 AND 26

)

-- Using CASE WHEN function to query in one table

CREATE TABLE Generation AS

SELECT

ID,

CASE WHEN Age BETWEEN 18 and 26 THEN "Z"

WHEN Age BETWEEN 27 and 42 THEN "Y"

WHEN Age BETWEEN 43 and 58 THEN "X"

WHEN Age BETWEEN 59 and 68 THEN "Jones"

WHEN Age BETWEEN 69 and 77 THEN "Boomers"

WHEN Age BETWEEN 78 and 95 THEN "PostWar"

ELSE "NULL"

END "Generation",

Buying_Power,

Model,

COUNT (*) N_Customers

FROM Customers

WHERE Model IS NOT NULL

GROUP BY 2,3,4

ORDER BY 5 DESC

The most of Y and Z generations are low buying power customer groups with the same popular model order. Even we have some average to high buying power customers in both groups, Z generation is low buying power customers more than Y generation for 22.7% with the highest number among generations.

Meantime, X and Jones (2nd Boomers) generations have similar buying power ratios between low to average groups but Jones generation have a higher number in high buying power than X generation for 10.5%

Unexpectedly, the two least number of customers are the most high buying power customers. I believe that we can convince them and offer reasonable products or services to maintain relationships for repeated buying transactions, whether garage services or suitable advertising channels.

-- Creating Profession Segmentation

SELECT 

ID,

COALESCE(Graduated, "Not Specified") Graduation,

Profession,

Buying_Power,

Model,

COUNT (*) N_Customers

FROM Customers

WHERE Profession IS NOT NULL AND Model IS NOT NULL

GROUP BY 2,3,4,5 

ORDER BY 6 DESC

After profession sorting from each model, I found that that are similar occupation ratios in each model. Our brand is popular among artists and healthcare industries 48.3% from all of customers.

Referring to the existing data, It is also possible that most of the people in healthcare, engineering, or entertainment are still working on their degrees. Operations should be aware of buying power changes in the future to offer the most suitable products and services according to the customers’ growth.