У цій статті буде проаналізовано базу даних chinook за допомогою SQL.
фото з unsplash.com
Ця база даних містить дані про продукти музичного магазину (плейлисти, треки, альбоми, жанри та артисти), продажі, а також дані про клієнтів і співробітників.
Структура бази даних представлена наступною діаграмою сутність-зв'язок (ER).
Популярність жанрів з часом
Мета: Дізнатися, як змінилася популярність музичних жанрів з часом.
У цьому випадку популярність визначається за кількістю проданих треків.
Підхід:
- Обчислити кількість проданих треків у кожному році для кожного жанру.
- Рангувати ці жанри за кількістю проданих треків
*DENSE_RANK() OVER (PARTITION BY SaleYear ORDER BY TracksSold DESC) AS GenreRankЦя частина присвоює номер рангу кожному жанру на основі кількості проданих треків, згрупованих по кожному році продажів. - Витягнути лише топ 3 найпопулярніших жанрів у кожному році.
/* Еволюція жанрів з часом*/
WITH GenreSales AS (
SELECT
STRFTIME('%Y', InvoiceDate) AS SaleYear,
t4.Name AS GenreName,
COUNT(t1.InvoiceLineId) AS TracksSold -- Підрахунок позицій в рахунках
FROM invoice_items t1
JOIN invoices t2 ON t1.InvoiceId = t2.InvoiceId
JOIN tracks t3 ON t1.TrackId = t3.TrackId
JOIN genres t4 ON t3.GenreId = t4.GenreId
GROUP BY SaleYear, GenreName
),
RankedGenres AS (
SELECT
SaleYear,
GenreName,
TracksSold,
DENSE_RANK() OVER (PARTITION BY SaleYear ORDER BY TracksSold DESC)
AS GenreRank -- Рангуємо жанри по продажах в кожному році
FROM GenreSales
)
SELECT
SaleYear,
GenreName,
TracksSold
FROM RankedGenres
WHERE GenreRank <= 3 -- Обмеження для топ 3 жанрів кожного року.
ORDER BY SaleYear, TracksSold DESC;
З результатів можна побачити, що "Latin" постійно з'являється в топ 3 жанрів кожного року. Це може бути важливим висновком для компанії, який можна використовувати для подальшої роботи.
Переваги жанрів для клієнтів
Мета: Дізнатися, які жанри є найулюбленішими для кожного клієнта (жанри, на які вони витратили найбільше) і зробити відповідну сегментацію клієнтів.
Підхід:
- Витягнути витрати кожного клієнта на жанри.
- Рангувати витрати на жанри для кожного клієнта.
- Витягнути лише топ 3 жанрів для кожного клієнта.
/* Переваги клієнтів на жанри:*/
WITH genre_spending AS (
SELECT t5.CustomerId as ID, t5.FirstName AS firstname,
t1.Name AS genre_name,
ROUND(SUM((t3.UnitPrice)*(t3.Quantity)),2) AS total_spent
FROM genres t1
JOIN tracks t2 ON t1.GenreId = t2.GenreId
JOIN invoice_items t3 ON t2.TrackId = t3.TrackId
JOIN invoices t4 ON t3.InvoiceId = t4.InvoiceId
JOIN customers t5 ON t4.CustomerId = t5.CustomerId
GROUP BY t5.CustomerId, t1.Name
),
ranked_genres AS (
SELECT ID, firstname, genre_name, total_spent,
DENSE_RANK() OVER (PARTITION BY ID ORDER BY total_spent DESC)
AS genre_rank
FROM genre_spending
)
SELECT ID, firstname, genre_name, total_spent
FROM ranked_genres
WHERE genre_rank <= 3; -- Фільтруємо для топ 3
Приклад результату. Це жанри, на які Луїс і Леоні витратили найбільше.
Що ми можемо отримати з цієї інформації?
Припустимо, ми хочемо запустити кампанії по email маркетингу.
Сегментація клієнтів на основі їх музичних уподобань дозволяє налаштувати кампанії для підвищення їх ефективності.
Приклад: Цільова аудиторія для жанру "Latin":
Запит нижче є прикладом отримання електронних адрес клієнтів, у яких уподобання жанру — "Latin":
-- приклад отримання електронних адрес клієнтів, у яких уподобання жанру "Latin"
WITH genre_spending AS (
SELECT t5.CustomerId as ID, t5.FirstName AS firstname,
t1.Name AS genre_name,
ROUND(SUM((t3.UnitPrice)*(t3.Quantity)),2) AS total_spent, t5.Email
FROM genres t1
JOIN tracks t2 ON t1.GenreId = t2.GenreId
JOIN invoice_items t3 ON t2.TrackId = t3.TrackId
JOIN invoices t4 ON t3.InvoiceId = t4.InvoiceId
JOIN customers t5 ON t4.CustomerId = t5.CustomerId
GROUP BY t5.CustomerId, t1.Name
),
ranked_genres AS (
SELECT ID, firstname, genre_name, total_spent, email,
DENSE_RANK() OVER (PARTITION BY ID ORDER BY total_spent DESC)
AS genre_rank
FROM genre_spending
)
SELECT ID, firstname, total_spent, email
FROM ranked_genres
WHERE genre_rank <= 3-- Фільтруємо для топ 3 жанрів
AND genre_name = 'Latin'
Приклад результату. Це клієнти, які витратили найбільше на жанр Latin порівняно з іншими жанрами.
Вплив підтримки співробітниками на витрати клієнтів
Мета: Проаналізувати, чи показують клієнти, яких підтримували різні співробітники, різні шаблони витрат.
Спочатку можемо проаналізувати організаційну структуру за допомогою наступного запиту.
SELECT employeeid, title, reportsto
FROM employees;
Результат показує, що лише співробітники з ідентифікаторами 3, 4 та 5 повинні підтримувати клієнтів.
Для подальшого аналізу ми можемо просто отримати кількість клієнтів, середні витрати клієнтів і загальні витрати клієнтів, групуючи по кожному співробітнику, що підтримує.
/* аналізуємо, чи клієнти, яких підтримували різні співробітники,
будуть мати різні витрати.*/
SELECT
t1.SupportRepId,
COUNT(DISTINCT t1.CustomerId) AS NumberOfCustomers,
ROUND(AVG(t2.Total),2) AS AverageCustomerSpending,
SUM(t2.total) AS total_sales
FROM Customers t1
JOIN Invoices t2 ON t1.CustomerId = t2.CustomerId
GROUP BY t1.SupportRepId
Результат містить лише співробітників з ідентифікаторами 3, 4 та 5, що відповідає нашому аналізу; це агенти підтримки продажів.
Хоча співробітник 3 згенерував найбільше продажів загалом, співробітник 5 досяг найвищих середніх витрат клієнтів, незважаючи на те, що обслуговував менше клієнтів.
Що робити, якщо у нас є більша команда агентів підтримки продажів?
Якщо ми хочемо чітко визначити тих, хто ще не підтримував клієнтів, нам потрібно внести деякі зміни. Наступний запит допоможе.
SELECT
t3.EmployeeId,
COUNT(DISTINCT t1.CustomerId) AS NumberOfCustomers,
ROUND(AVG(t2.Total),2) AS AverageCustomerSpending,
SUM(t2.total) AS total_sales
FROM Customers t1
JOIN Invoices t2 ON t1.CustomerId = t2.CustomerId
RIGHT JOIN employees t3 on t1.SupportRepId = t3.EmployeeId
WHERE t3.Title = 'Sales support agent'
GROUP BY t3.EmployeeId
Цей запит використовує RIGHT JOIN, щоб включити всіх агентів підтримки продажів, навіть тих, хто ще не працював. Це дозволяє провести повний аналіз ефективності агентів.
Висновок
Ця стаття надає приклади аналізу даних музичного магазину Chinook. Моніторинг тенденцій жанрів може допомогти в прийнятті кращих рішень щодо управління запасами. Використовуючи дані про вподобання клієнтів, можна розробити персоналізовані маркетингові кампанії. Розуміння впливу агентів підтримки продажів дозволяє оцінити їхню ефективність і здійснити цільове навчання.
Ця стаття та SQL запити були написані за допомогою Gemini.
Перекладено з: Chinook music store database analysis using SQL