Фото від Ruben Ramirez на Unsplash
Ласкаво просимо назад до серії, де я аналізую набір даних, а мій зацікавлений учасник, відомий також як ChatGPT, ставить мені питання для вирішення. Як зазначено в заголовку, це третя частина!
Якщо вам цікаво побачити першу частину, де я аналізую дані супермаркетів, натискайте тут -> [Стаття Частина 1], або другу частину, де я аналізую дані поведінки клієнтів, натискайте тут -> [Стаття Частина 2].
Сьогодні ми працюємо в Каліфорнії і відповідаємо за аналіз даних з кількох торгових центрів у штаті. Наш зацікавлений учасник, пан ChatGPT, надав нам набір даних для аналізу та подальших інсайтів щодо того, як працює кожен торговий центр у Каліфорнії.
Дані, з якими ми працюємо, є набором даних Kaggle, і якщо вам цікаво, ви можете натискати тут -> [Набір даних California Mall]
Пан ChatGPT надав нам 3 таблиці для роботи. (Перелічено з посилання на Kaggle)
Таблиця з даними продажів: Цей набір даних містить деталі транзакцій для товарів, проданих у торгових центрах. Основні стовпці включають:
- invoiceno:_ Унікальний ідентифікатор кожної транзакції.
- customerid:_ Ідентифікатор покупця, який здійснив покупку.
- category: Категорія продукту (наприклад, одяг, взуття).
- quantity: Кількість кожного продукту, що було придбано.
- invoice date: Дата транзакції.
- price: Ціна кожного продукту, що було придбано.
- shoppingmall_: Торговий центр, де відбулася транзакція.Таблиця з даними клієнтів: Цей набір даних надає демографічні дані для кожного клієнта, зокрема:
- customerid_: Унікальний ідентифікатор кожного клієнта.
- gender: Стать клієнта.
- age: Вік клієнта.
- paymentmethod_: Улюблений спосіб оплати для транзакцій.Таблиця з даними торгових центрів: Цей набір даних містить деталі різних торгових центрів Каліфорнії, де відбуваються транзакції. Стовпці включають:
- shoppingmall_: Назва торгового центру.
- constructionyear_: Рік заснування торгового центру.
- areasqm_: Загальна площа торгового центру в квадратних метрах.
- location: Місто в Каліфорнії, де розташований торговий центр.
- storescount_: Кількість магазинів в торговому центрі.
Підготовка, очищення та створення переглядів
Тепер, коли дані імпортовано в базу даних, першим кроком буде очищення та підготовка даних перед аналізом.
Я використовую PostgreSQL як інструмент для роботи з базою даних.
Почнемо з Таблиці з даними продажів, перевіримо на наявність дублікатів і порожніх значень.
select customer_id
from
(select customer_id
,invoice_no
,category
,quantity
,invoice_date
,price
,shopping_mall
,count(*) as dup
FROM "California_Mall_Data"."Sales_Data"
group by 1,2,3,4,5,6,7) as t
where dup >2
Немає дублікатів!
select customer_id
,invoice_no
,category
,quantity
,invoice_date
,price
,shopping_mall
FROM "California_Mall_Data"."Sales_Data"
where shopping_mall is null
or invoice_no is null
or category is null
or quantity is null
or invoice_date is null
or price is null
Немає порожніх значень!
Створюємо вигляд для зручності.
create view vw_sales_data as (
select customer_id
,invoice_no
,category
,quantity
,invoice_date
,price
,shopping_mall
FROM "California_Mall_Data"."Sales_Data"
)
Повторюємо ці кроки для інших таблиць даних.
Таблиця Customer_Data
-- Перевірка на дублікати
select customer_id
,count(*) as total_dupes
from
(SELECT customer_id
,gender
,age
,payment_method
FROM "California_Mall_Data"."Customer_data") as t
group by customer_id
having count(*) > 2
Немає дублікатів
-- Перевірка на порожні значення
SELECT customer_id
,gender
,age
,payment_method
FROM "California_Mall_Data"."Customer_data"
where age is null
or gender is null
or customer_id is null
or payment_method is null
109 порожніх значень для поля ВІК
З 99,457 рядків даних, є 109 порожніх значень для поля AGE.
Ми можемо вирішити цю проблему двома способами:
- Видалити 109 рядків, що мають порожні значення для віку, оскільки це мала вибірка, і це не повинно сильно вплинути на результати.
- Знайти середній вік для чоловіків і жінок.
У цьому проекті я обрав другий варіант, зміни будуть враховані при створенні нового вигляду для цієї таблиці.
select gender
,round(avg(age)) as avg_age
from "California_Mall_Data"."Customer_data"
group by gender
-- Середній вік для чоловіків = 43
-- Середній вік для жінок = 43
-- Створення нового вигляду
create view vw_customer_data as
( select customer_id
,gender
,case when age is null and gender ilike 'male' then 43
when age is null and gender ilike 'female' then 43
else age end as age
,payment_method
from "California_Mall_Data"."Customer_data"
)
Таблиця Shoppingmalldata
Щодо даних торгових центрів, є 10 записів загалом, без дублікатів чи порожніх значень (Простенько!)
Але я все одно створю вигляд для збереження узгодженості.
-- Створення вигляду
create view vw_shopping_mall_data as (
SELECT shopping_mall
,construction_year
,area_sqm
,location
,store_count
FROM "California_Mall_Data".shopping_mall_data
)
На завершення, я створю більший вигляд, який об’єднує ці 3 таблиці разом, щоб уникнути дублювання при роботі з майбутніми запитами для пана GPT.
Я також створив підмножини нового вигляду для сегментації за конкретними роками.
-- Великий вигляд
create view vw_mall_data as
( select t1.customer_id
,t2.invoice_no
,t2.invoice_date
,t1.gender
,t1.age
, case when t1.age between 18 and 24 then '18-24'
when t1.age between 25 and 34 then '25-34'
when t1.age between 35 and 44 then '35-44'
when t1.age between 45 and 55 then '45-54'
when t1.age >= 55 then '55_plus'
else 'n/a' end as age_group
,t2.category
,t2.price
,t2.quantity
,t1.payment_method
,t3.shopping_mall
,t3.location
from vw_customer_data as t1
join vw_sales_data as t2 on t1.customer_id = t2.customer_id
join vw_shopping_mall_data as t3 on t2.shopping_mall = t3.shopping_mall
)
-- Рік 2021
create view vw_mall_data_2021 as
(select *
from vw_mall_data
where extract (year from invoice_date) = 2021
)
-- Рік 2022
create view vw_mall_data_2022 as
(select *
from vw_mall_data
where extract (year from invoice_date) = 2022
)
-- Рік 2023
create view vw_mall_data_2023 as
(select *
from vw_mall_data
where extract (year from invoice_date) = 2023
)
Обрізане зображення vwmalldata (Великий вигляд)
Відповідаємо панові GPT
Питання від ChatGPT
Тепер, коли наші дані очищені та підготовлені, ми готові до аналізу.
select age_group
,UPPER(gender) as gender
,round(avg(price),2) as avg_price
,round(avg(quantity),0) as avg_quantity
from vw_mall_data
group by 1,2
order by age_group
,gender
with new_t as (
select extract (month from invoice_date) as date_num
,to_char(invoice_date,'Month') as month
,sum(case when extract(year from invoice_date) = 2021 then price else 0 end) as year_2021
,sum(case when extract(year from invoice_date) = 2022 then price else 0 end) as year_2022
,sum(case when extract(year from invoice_date) = 2023 then price else 0 end) as year_2023
from vw_mall_data
group by 1,2
order by 1
)
select month
,year_2021
,year_2022
,year_2023
from new_t
Загальні дані за всі місяці
with new_t as
(select extract (month from invoice_date) as date_num
,to_char(invoice_date,'Month') as month
,category
,sum(case when extract(year from invoice_date) = 2021 then price else 0 end) as year_2021
,sum(case when extract(year from invoice_date) = 2022 then price else 0 end) as year_2022
,sum(case when extract(year from invoice_date) = 2023 then price else 0 end) as year_2023
from vw_mall_data
group by 1,2,3
order by 1
)
,year_2021_max as
(select concat(trim(month),('_2021')) as month_year
,category
,total_spent
from
(select month
,category
,max(year_2021) as total_spent
,rank() over(order by max(year_2021)desc) as max_rank
from new_t
group by month
,category
order by 3 desc ) as t
where max_rank = 1
)
,year_2022_max as
(select concat(trim(month),'_2022') as month_year
,category
,total_spent
from
(select month
,category
,max(year_2022) as total_spent
,rank() over (order by max(year_2022)desc) as max_rank
from new_t
group by month
,category
order by 3 desc)as t
where max_rank = 1
)
,year_2023_max as
(select concat(trim(month),'_2023') as month_year
,category
,total_spent
from
(select month
,category
,max(year_2023) as total_spent
,rank() over (order by max(year_2023)desc) as max_rank
from new_t
group by month
,category
order by 3 desc)as t
where max_rank = 1
)
,t_union as (select month_year
,category
,total_spent
from year_2021_max
union all
select month_year
,category
,total_spent
from year_2022_max
union all
select month_year
,category
,total_spent
from year_2023_max
)
select month_year
,category
,total_spent
from t_union
with t_avg_spent as ( select round(avg(price),2) as avg_spent
from vw_mall_Data
)
,cust_sales as (select customer_id
,sum(price) as total_spent
from vw_mall_data
group by customer_id
)
select customer_id
,total_spent
,(select avg_spent from t_avg_spent) as avg_spent
from cust_sales
where case when (total_spent) > (select (avg_spent * 2) from t_avg_spent) then 1
else 0 end = 1
обрізано: 16,151 клієнтів витратили більше середнього
select age_group
,gender
,category
,revenue
from
(select age_group
,gender
,category
,sum(price) as revenue
,rank() over (partition by gender,age_group order by sum(price) desc) as rank_key
from vw_mall_data
group by age_group
,gender
,category) as t
where rank_key <= 3
order by age_group
,gender
select age_group
,payment_method
,revenue
from
(select age_group
,payment_method
,sum(price) as revenue
,rank() over (partition by age_group order by sum(price) desc) as rank_key
from vw_mall_data
group by age_group,payment_method) as t
where rank_key <2
with y_2021 as (select extract(month from invoice_date) as month_num
,to_char(invoice_date,'Month') as Month
,sum(price) as revenue
,lag(sum(price)) over() as previous_month_rev
from vw_mall_data_2021
group by 1,2
order by 1 asc
)
select month
,revenue
,previous_month_rev
,round((revenue - previous_month_rev)/previous_month_rev,2) as mom_perc
from y_2021
Місяць до місяця для 2021 року
with y_2022 as (select extract(month from invoice_date) as month_num
,to_char(invoice_date,'Month') as Month
,sum(price) as revenue
,lag(sum(price)) over() as previous_month_rev
from vw_mall_data_2022
group by 1,2
order by 1 asc
)
select month
,revenue
,previous_month_rev
,round((revenue - previous_month_rev)/previous_month_rev,2) as mom_perc
from y_2022
Місяць до місяця для 2022 року
with y_2023 as (select extract(month from invoice_date) as month_num
,to_char(invoice_date,'Month') as Month
,sum(price) as revenue
,lag(sum(price)) over() as previous_month_rev
from vw_mall_data_2023
group by 1,2
order by 1 asc
)
select month
,revenue
,previous_month_rev
,round((revenue - previous_month_rev)/previous_month_rev,2) as mom_perc
from y_2023
Місяць до місяця для 2023 року
with t_yoy as (select extract(year from invoice_date) as year
,sum(price) as revenue
from vw_mall_data
group by 1
order by 1
)
,t_lag as (select year
,revenue
,lag(revenue) over() as prev_rev_year
from t_yoy
)
select year
,revenue
,prev_rev_year
,round((revenue - prev_rev_year)/prev_rev_year,2)
from t_lag
select *
from
(select rank() over(order by sum(price) desc,count(customer_id) desc ) as ranking
,t1.location
,t1.shopping_mall
,t2.store_count
,sum(t1.price) as revenue
,count(t1.customer_id) as total_customers
from vw_mall_Data as t1
join vw_shopping_mall_data as t2 on t1.location = t2.location
and t1.shopping_mall = t2.shopping_mall
group by 2,3,4) as t
where ranking <= 5
with new_t as (select invoice_date
,to_char(invoice_date, 'Mon') as date_abr
,shopping_mall
,location
,price
from vw_mall_data
)
,t_seasons as
(select invoice_date
,case when date_abr ilike 'mar' or date_abr ilike 'apr' or date_abr ilike 'may' then 'Spring'
when date_abr ilike 'jun' or date_abr ilike 'jul' or date_abr ilike 'aug' then 'Summer'
when date_abr ilike 'sep' or date_abr ilike 'oct' or date_abr ilike 'nov' then 'Fall'
when date_abr ilike 'dec' or date_abr ilike 'jan' or date_abr ilike 'feb' then 'Winter'
else date_abr end as Season
,shopping_mall
,location
,price
from new_t
)
select location
,season
,revenue
from
(select location
,season
,sum(price) as revenue
,rank() over(partition by location order by sum(price) desc) as rank_key
from t_seasons
group by season, location
order by 2 desc) as t
where rank_key = 1
order by revenue desc
Нарешті, щоб об'єднати все разом, давайте створимо інформаційну панель (dashboard), щоб пан
ChatGPT може далі досліджувати свої дані.
Для цього ми будемо використовувати Power BI!
Заключні зауваження
Пан ChatGPT вдячний за роботу, яку ми для нього виконали!
Дякую, що прочитали цю статтю. Мені було цікаво зануритися в цей набір даних Каліфорнійських торгових центрів. Сподіваюся, ви отримали корисні відомості та насолоджувалися процесом читання. Сподіваюсь, пан ChatGPT вдячний за те, що ми для нього зробили.
Якщо вас цікавлять інші статті, не соромтеся ознайомитися з ними -> https://medium.com/@rmirville !
До наступного проекту, на цьому все!
Перекладено з: Analyzing California Mall Data with ChatGPT as My Stakeholder Part 3