E-commerce став важливою частиною глобальної економіки, і компанії, як-от Tokopedia, обробляють мільйони транзакцій щодня. Розуміння поведінки клієнтів, ефективності продуктів і переваг щодо способів оплати є важливим для збільшення продажів та залишання конкурентоспроможними на ринку. Наявність великих даних дає можливість отримати цінні інсайти, що можуть оптимізувати бізнес-операції.

У цьому проекті основною метою було проаналізувати змодельований набір даних Tokopedia, щоб виявити ключові тенденції, оцінити ефективність за різними метриками (наприклад, сезонні тренди, топові категорії, переваги оплат), та надати практичні поради для вдосконалення бізнес-стратегій. Використовуючи SQL як основний інструмент, цей проект демонструє, як ухвалення рішень на основі даних може привести до підвищення ефективності та прибутковості для платформ електронної комерції.

Чи замислювалися ви коли-небудь, як інтернет-магазини, як Tokopedia, аналізують мільйони транзакцій?

Відповідь — SQL. Structured Query Language (SQL) — це мова програмування, яка використовується для керування та маніпулювання даними в реляційних базах даних. Завдяки SQL великі компанії електронної комерції можуть щодня аналізувати мільйони транзакцій, отримувати цінні інсайти, приймати розумні бізнес-рішення та автоматизувати звітність. SQL пропонує велику гнучкість і ефективність у обробці даних, що робить його важливим інструментом у світі аналізу даних та бізнесу.

Чому SQL важливий у електронній комерції?

SQL є незамінним інструментом для аналізу великих наборів даних, зокрема даних про транзакції в електронній комерції. За допомогою SQL компанії можуть:

• Оцінювати ефективність продуктів: SQL допомагає аналізувати продажі, прибутковість та тенденції продуктів.
• Виявляти шаблони клієнтів: SQL використовується для розуміння поведінки клієнтів та покращення досвіду користувачів.
• Оптимізувати операції: SQL спрощує процеси аналізу для звітності, автоматизації та кращого прийняття рішень.

Більше 60% компаній у світі використовують SQL як частину своєї стратегії даних. У електронній комерції SQL дозволяє зв’язувати різні набори даних, такі як транзакції, продукти, клієнти та способи оплати, щоб виявляти бізнес-тенденції.

Аналіз набору даних: Розуміння даних електронної комерції

У цьому аналізі ми використовуємо чотири основні набори даних, що відображають різні аспекти транзакцій електронної комерції:

1. order_detail: Цей набір даних містить дані про транзакції, включаючи ID замовлення, ID клієнта, ID продукту (SKU), ціну, кількість замовлених товарів, знижки та дані про оплату.
2. sku_detail: Цей набір містить детальну інформацію про кожен продукт (SKU), таку як назва продукту, категорія, базова ціна та собівартість товару (COGS).
3. customer_detail: Цей набір містить реєстраційні дані клієнтів, такі як ID клієнта та дата реєстрації на платформі.
4. payment_detail: Цей набір містить доступні методи оплати, кожен з яких ідентифікується унікальним ID оплати.

Ці набори даних становлять основу нашого аналізу, допомагаючи зробити висновки про транзакції, ефективність продуктів та поведінку клієнтів. Ми можемо безпосередньо запускати запити, використовуючи ці набори даних, і результати в PostgreSQL будуть показувати таблиці, як показано на зображенні нижче, якщо запит буде успішно виконаний.

Ці таблиці взаємопов'язані через зовнішні ключі. Таблиця order_detail пов’язана з таблицею customer_detail через стовпець customer_id, що вказує на клієнта, який зробив транзакцію. Вона також пов’язана з таблицею sku_detail через стовпець sku_id, що показує придбаний продукт. Крім того, order_detail пов’язана з таблицею payment_detail через стовпець payment_id, що визначає спосіб оплати, використаний у транзакції. Таким чином, кожна транзакція в таблиці order_detail пов’язана з одним клієнтом, одним продуктом та одним способом оплати, що формує відношення один-до-багатьох між клієнтами, продуктами, способами оплати та транзакціями в таблиці order_detail.
Це відношення дозволяє виконувати запити JOIN через таблиці для отримання комплексної інформації про транзакції, клієнтів, продукти та платежі.

Ми можемо досліджувати та аналізувати ці чотири набори даних, виконуючи наступні запити один за одним, а результати можна переглянути на зображеннях нижче.

SELECT * FROM order_detail  
SELECT * FROM sku_detail  
SELECT * FROM customer_detail  
SELECT * FROM payment_detail

Приклад даних з customerdetail_

Приклад даних з orderdetail_

Приклад даних з paymentdetail_

Приклад даних з skudetail_

Пояснення використаних SQL команд:

• SELECT: Ця команда використовується для вибору стовпців, які ми хочемо показати в результатах запиту.
• *: Цей символ використовується для відображення всіх стовпців у таблиці.
• FROM table_name: Цей синтаксис вказує, яка таблиця буде джерелом даних.

Ми бачимо, що ці чотири таблиці взаємопов’язані, як вказано за допомогою полів ID.
Далі ми розглянемо кілька запитань, пов'язаних з нашим набором даних.

SQL вправи та пояснення

Вправа 1: Найбільша транзакція у 2021 році

Запитання: У транзакціях, що відбулися у 2021 році, який місяць мав найвищу загальну вартість транзакцій (afterdiscount)? Використовуйте **isvalid = 1** для фільтрації валідних транзакцій.

Відповідь: Для відповіді на це питання ми використаємо наступний SQL запит:

SELECT   
 TO_CHAR(order_date, 'Month') AS Transaction_Month,  
 SUM(after_discount) AS Transaction_Sum  
FROM order_detail  
WHERE   
 EXTRACT(YEAR FROM order_date) = 2021  
 AND is_valid = 1  
GROUP BY 1  
ORDER BY 2 DESC  
LIMIT 1;

Пояснення запиту:

• SELECT TO_CHAR(order_date, 'Month') AS Transaction_Month Перетворює стовпець order_date, щоб показати місяць у текстовому форматі, і задає йому псевдонім Transaction_Month.
• SUM(after_discount) AS Transaction_Sum Підсумовує значення з колонки after_discount і задає йому псевдонім Transaction_Sum, згрупувавши за місяцем Transaction_Month.
• FROM order_detail Вказує, що дані беруться з таблиці order_detail.
• WHERE EXTRACT(YEAR FROM order_date) = 2021 AND is_valid = 1 Фільтрує дані, щоб включати лише транзакції, що відбулися у 2021 році і є валідними (is_valid = 1).
• GROUP BY 1 Групує результати за першим стовпцем, яким є Transaction_Month.
• ORDER BY 2 DESC Сортує результати за другим стовпцем, Transaction_Sum, у спадаючому порядку.
• LIMIT 1 Обмежує результат лише одним рядком, яким буде місяць з найвищою вартістю транзакцій.

Результат запиту:

Результат:
Дані показують, що місяць з найвищою загальною вартістю транзакцій (after_discount) у 2021 році був серпень, з загальною сумою 227,862,744.

Вправа 2: Категорія з найбільшими транзакціями у 2022 році

Запитання: У транзакціях, що відбулися у 2022 році, яка категорія принесла найбільшу транзакційну вартість? Використовуйте is_valid=1 для фільтрації валідних транзакцій.

Відповідь: SQL запит для відповіді на це питання:

SELECT   
 sd.category,  
 SUM(od.after_discount) AS Transaction_Sum  
FROM   
 order_detail AS od  
LEFT JOIN sku_detail AS sd ON od.sku_id = sd.id  
WHERE   
 EXTRACT(YEAR FROM od.order_date) = 2022  
 AND od.is_valid = 1  
GROUP BY 1  
ORDER BY 2 DESC  
LIMIT 1;

Пояснення запиту:

• SELECT sd.category Вибирає назву категорії зі стовпця category з таблиці sku_detail.
• SUM(od.after_discount) AS Transaction_Sum Підсумовує значення в колонці after_discount з таблиці order_detail і задає йому псевдонім Transaction_Sum.
• FROM order_detail AS od Вказує, що основні дані беруться з таблиці order_detail з псевдонімом od.
• LEFT JOIN sku_detail AS sd ON od.sku_id = sd.id З'єднує таблицю sku_detail з таблицею order_detail за зв'язком між sku_id в order_detail та id в sku_detail.
• WHERE EXTRACT(YEAR FROM order_date) = 2022 AND is_valid = 1 Фільтрує дані для транзакцій, що відбулися у 2022 році та є валідними (is_valid = 1).
• GROUP BY 1 Групує результати за першим стовпцем, яким є category.
• ORDER BY 2 DESC Сортує результати за другим стовпцем, Transaction_Sum, у спадаючому порядку.
• LIMIT 1 Обмежує результат лише одним рядком, який буде категорією з найбільшою транзакційною вартістю.

Результат запиту:

Результат:
Дані вказують, що категорія з найбільшою транзакційною вартістю (after_discount) у 2022 році була Мобільні телефони та планшети, з загальною сумою 918,451,576.

Вправа 3: Порівняння транзакцій за категоріями в 2021 та 2022 роках

Запитання: Порівняйте транзакційні вартості кожної категорії в 2021 та 2022 роках.
Визначте, які категорії показали збільшення, а які — зменшення транзакційних вартостей з 2021 по 2022 рік.

Відповідь: Ось SQL запит, який порівнює транзакційні вартості за двома роками за допомогою спільних таблиць (CTE) та умовної агрегації:

WITH annual_transactions AS (  
 SELECT   
 sd.category,  
 SUM(CASE WHEN EXTRACT(YEAR FROM od.order_date) = 2021 THEN od.after_discount ELSE 0 END) AS transaction_2021,  
 SUM(CASE WHEN EXTRACT(YEAR FROM od.order_date) = 2022 THEN od.after_discount ELSE 0 END) AS transaction_2022  
 FROM order_detail AS od  
 LEFT JOIN sku_detail AS sd ON od.sku_id = sd.id  
 WHERE od.is_valid = 1  
 GROUP BY sd.category  
)  
SELECT   
 category,  
 transaction_2021,  
 transaction_2022,  
 (transaction_2022 - transaction_2021) AS difference,  
 CASE   
 WHEN transaction_2022 > transaction_2021 THEN 'Increased'  
 ELSE 'Decreased'  
 END AS status  
FROM annual_transactions  
ORDER BY difference DESC;

Пояснення запиту:

• WITH annual_transactions AS (…) Використовується для спрощення складних запитів і дозволяє використовувати тимчасові результати запитів в інших частинах запиту. Це робить управління та розуміння більш складних запитів простішим.
• SELECT sd.category Вибирає назву категорії зі стовпця category з таблиці sku_detail.
• SUM(CASE WHEN EXTRACT(YEAR FROM od.order_date) = 2021 THEN od.after_discount ELSE 0 END) AS transaction_2021
  Підсумовує транзакційні вартості з колонки after_discount, які відбулися у 2021 році, згрупувавши за категорією продукту, і задає результат псевдонім transaction_2021.
• SUM(CASE WHEN EXTRACT(YEAR FROM od.order_date) = 2022 THEN od.after_discount ELSE 0 END) AS transaction_2022
  Підсумовує транзакційні вартості, що відбулися у 2022 році, з псевдонімом transaction_2022.
• FROM order_detail AS od Вказує, що основні дані беруться з таблиці order_detail з псевдонімом od.
• LEFT JOIN sku_detail AS sd ON od.sku_id = sd.id
  З'єднує таблицю sku_detail з таблицею order_detail на основі зв'язку між sku_id в order_detail і id в sku_detail.
• WHERE is_valid = 1 Фільтрує дані, включаючи лише валідні транзакції.
• GROUP BY 1 Групує результати за першим стовпцем, яким є категорія.
• ORDER BY 2 DESC Сортує результати за другим стовпцем, тобто за transaction_2021 та transaction_2022, від найбільшого до найменшого.
• SELECT * Виводить всі дані з CTE annual_transactions.
• SUM(transaction_2022) - SUM(transaction_2021) AS difference Обчислює різницю між транзакційними вартостями для 2022 та 2021 років, задаючи псевдонім difference.
• CASE WHEN SUM(transaction_2022) > SUM(transaction_2021) THEN 'Increased' ELSE 'Decreased' END AS status
  Показує, чи зазнала категорія збільшення (Increased) або зменшення (Decreased) транзакцій на основі обчисленої різниці.
• GROUP BY 1, 2, 3 Групує результати за категорією продукту, transaction_2021 і transaction_2022.
• ORDER BY 4 DESC Сортує результати за стовпцем difference, від найбільшого до найменшого.

Результат запиту:

Результат:
Результати запиту показують, що кілька категорій, такі як Мобільні телефони та планшети, Розваги, Побутова техніка та Комп'ютери, показали значне збільшення транзакцій з 2021 по 2022 рік.
З іншого боку, деякі категорії зазнали зниження, такі як Книги та Інші.

Інсайт:

• Категорії, що збільшилися: Категорія Мобільні телефони та планшети зазнала найбільшого збільшення транзакцій у 2022 році, що вказує на сильний ріст у секторах технологій та мобільних пристроїв.
• Категорії, що зменшилися: З іншого боку, категорії, такі як Книги, зазнали зменшення транзакцій, що може бути пов'язано зі зміною споживчих звичок, коли більше людей переходять на цифровий контент або електронні пристрої.

Вправа 4: Найпопулярніші способи оплати у 2022 році

Питання: Покажіть 5 найбільш популярних способів оплати, які використовувалися у 2022 році (на основі загальної кількості унікальних замовлень).

Відповідь: Для отримання відповіді використовується наступний SQL запит:

SELECT   
 pd.payment_method,  
 COUNT(DISTINCT od.id) AS total_unique_order  
FROM order_detail AS od  
LEFT JOIN payment_detail AS pd ON od.payment_id = pd.id  
WHERE EXTRACT(YEAR FROM od.order_date) = 2022 AND od.is_valid = 1  
GROUP BY 1  
ORDER BY 2 DESC  
LIMIT 5;

Пояснення запиту:

• SELECT pd.payment_method Використовується для відображення назви способу оплати з колонки payment_method в таблиці payment_detail.
• COUNT(DISTINCT od.id) AS total_unique_order Підраховує кількість унікальних транзакцій (без дублікатів) на основі id з таблиці order_detail і задає псевдонім total_unique_order.
• FROM order_detail AS od Вказує, що основні дані беруться з таблиці order_detail з псевдонімом od.
• LEFT JOIN payment_detail AS pd ON od.payment_id = pd.id З'єднує таблицю payment_detail з таблицею order_detail на основі зв'язку між payment_id в order_detail та id в payment_detail.
• WHERE EXTRACT(YEAR FROM order_date) = 2022 AND is_valid = 1 Фільтрує дані, щоб включити лише транзакції, що відбулися у 2022 році і є валідними (is_valid = 1).
• GROUP BY 1 Групує результати за першим стовпцем, яким є payment_method.
• ORDER BY 2 DESC Сортує результати за другим стовпцем, total_unique_order, від найбільшого до найменшого.
• LIMIT 5 Обмежує результат до 5 найбільш популярних способів оплати.

Результат запиту:

Результат:
Найбільш популярним способом оплати у 2022 році був COD (Готівка при доставці).

Вправа 5: Сортування продуктів за транзакційною вартістю

Питання: Відсортуйте наступні продукти за їх транзакційною вартістю: Samsung, Apple, Sony, Huawei, Lenovo.

Відповідь: Для сортування продуктів за транзакційною вартістю використовується наступний запит:

WITH top_brands AS (  
 SELECT   
 CASE  
 WHEN LOWER(sd.sku_name) LIKE '%samsung%' THEN 'Samsung'  
 WHEN LOWER(sd.sku_name) LIKE '%apple%' OR LOWER(sd.sku_name) LIKE '%iphone%'  
 WHEN LOWER(sd.sku_name) LIKE '%mac%' THEN 'Apple'  
 WHEN LOWER(sd.sku_name) LIKE '%sony%' OR LOWER(sd.sku_name) LIKE '%playstation%' THEN 'Sony'  
 WHEN LOWER(sd.sku_name) LIKE '%huawei%' THEN 'Huawei'  
 WHEN LOWER(sd.sku_name) LIKE '%lenovo%' OR LOWER(sd.sku_name) LIKE '%zuk%' THEN 'Lenovo'  
 END AS product_names,  
 SUM(od.after_discount) AS transaction_sum  
 FROM order_detail AS od  
 LEFT JOIN sku_detail AS sd ON od.sku_id = sd.id  
 WHERE is_valid = 1  
 GROUP BY 1  
 ORDER BY 2 DESC  
)  

SELECT *  
FROM top_brands  
WHERE product_names IS NOT NULL;

Пояснення запиту:

• WITH top_brands AS (…) Використовується для спрощення складного запиту і дозволяє зберігати результат запиту тимчасово в CTE (Common Table Expression), що дозволяє повторно використовувати результат в інших частинах запиту.
• SELECT CASE WHEN LOWER(sd.sku_name) LIKE '%samsung%' THEN 'Samsung' … OR LOWER(sd.sku_name) LIKE '%zuk%' THEN 'Lenovo' END AS product_names Використовується для вибору та категоризації продуктів від брендів таких як Samsung, Apple, Sony, Huawei та Lenovo на основі ключових слів, знайдених у колонці sku_name в таблиці sku_detail.
  Результат позначено як product_names.
• SUM(od.after_discount) AS transaction_sum Підсумовує значення транзакцій (з колонки after_discount в таблиці order_detail) за брендом продукту та позначає результат як transaction_sum.
• FROM order_detail AS od Вказує, що основні дані беруться з таблиці order_detail, з псевдонімом od.
• LEFT JOIN sku_detail AS sd ON od.sku_id = sd.id З'єднує таблицю sku_detail з таблицею order_detail на основі зв'язку між sku_id в order_detail і id в sku_detail.
• WHERE is_valid = 1 Фільтрує дані, включаючи лише оплачувані транзакції (валідні).
• GROUP BY 1 Групує результати за першим стовпцем, яким є product_names.
• ORDER BY 2 DESC Сортує результати за другим стовпцем, transaction_sum, від найбільшого до найменшого.
• SELECT * Виводить усі дані з CTE top_brands.
• WHERE product_names IS NOT NULL Фільтрує результати, включаючи лише рядки, де стовпець product_names має значення (не є null).

Результат запиту:

Результат:
Продукт з найбільшою транзакційною вартістю — це Samsung, за ним йдуть Apple, Sony, Lenovo та Huawei.

Висновок

Ця дискусія надає аналіз кількох питань, пов'язаних з даними, з використанням різних SQL команд, функцій і клауз в PostgreSQL. Завдяки SQL ми можемо ефективно отримувати цінні інсайти з великих наборів даних, таких як транзакції в електронній комерції, клієнти, продукти та методи оплати.

А ви готові почати свою подорож з SQL? Не хвилюйтеся — SQL — це потужний інструмент, який легко вивчити і який надзвичайно ефективний для аналізу даних. За допомогою кількох рядків коду ви можете отримати цінні інсайти з таких даних, як транзакції в електронній комерції, поведінка клієнтів, продукти та методи оплати. Починайте з простих запитів і досліджуйте можливості, які можуть виникнути з даних, які у вас є. Так що, чого чекаєте? Візьміть свій набір даних, практикуйтеся та відкривайте тренди й інсайти, які можуть покращити прийняття бізнес-рішень. Успіхів у дослідженнях та ставайте експертом з даних!

Дякую за читання!

Якщо у вас є питання або ви хочете обговорити цей проект більш детально, не соромтеся зв'язатися зі мною через LinkedIn. Сподіваюся, що цей проект буде корисним посібником для всіх, хто хоче дізнатися більше про дані! 😊

linkedin | email | github

Перекладено з: Unveiling E-Commerce Patterns: A Transaction Analysis with SQL