Дослідження ефективності електронної комерції та інсайтів | SQL, Python, Looker - javascript.org.ua

текст перекладу

Фото від Rodion Kutsaiev на Unsplash

У цьому дослідженні я проаналізував ефективність роботи бізнесу з електронною комерцією, який спеціалізується на відновленій електроніці. Використовуючи транзакційні дані за 2019 рік, я зосередився на виявленні ключових тенденцій, визначенні можливостей для зростання та оцінці ефективності обслуговування. Це завдання включало очищення даних у Python, підготовку їх у BigQuery та візуалізацію результатів через Google Looker панелі.

Завдання

На основі транзакційних даних за 2019 рік -

Створити загальну панель ефективності для показу KPI
Знайти цікаві комерційні інсайти
Знайти критичні розробки
Показати топ N продуктів за маржею
Провести аналіз рівня обслуговування за часами обробки (тривалість прийняття рішення про покупку, кількість днів для продажу товару)
Дати пропозиції та рекомендації

Підготовка та обробка даних

Очищення

Я очистив та проаналізував дані за допомогою Python на Google Colab.

[

Google Colab

Редагувати опис

colab.research.google.com

](https://colab.research.google.com/drive/1ObOdj782kGBjbyT1jOEuLkH-frDPo8y?usp=sharing&source=postpage-----39a3a1784df4--------------------------------)

Завантаження та створення подань

Я завантажив очищену таблицю в BigQuery.
текст перекладу
Я створив 4 подання в BigQuery, щоб пізніше підключити їх до Google Looker Studio.

(1) marginbyproduct

CREATE VIEW task.margin_by_product AS  
WITH  
 purchase AS (  
 SELECT  
 item_id,  
 product_id,  
 NAME,  
 CE_MEDIA,  
 MAIN_CATEGORY,  
 SUM(REVENUE) AS total_purchase_cost  
 FROM  
 task.data  
 WHERE  
 TYPE = 'purchase'  
 AND CREATED_DATE >= '2018–06–14'  
 GROUP BY  
 item_id, product_id, NAME, CE_MEDIA, MAIN_CATEGORY  
 ),  
 sale AS (  
 SELECT  
 item_id,  
 product_id,  
 NAME,  
 CE_MEDIA,  
 MAIN_CATEGORY,  
 SUM(REVENUE) AS total_sale_revenue  
 FROM  
 task.data  
 WHERE  
 TYPE = 'sale'  
 AND CREATED_DATE >= '2018–06–14'  
 GROUP BY

(2) decision_duration

CREATE VIEW task.decision_duration AS  
SELECT  
 FORMAT_DATE('%Y-%m', DATE(CREATED_DATE)) AS year_month,  
 MAIN_CATEGORY,  
 APPROX_QUANTILES(  
 DATE_DIFF(DATE(PURCHASED_DATE), DATE(GRADED_DATE), DAY),   
 100  
 )[OFFSET(90)] AS decision_duration_90_percentile  
FROM  
 task.purchase  
GROUP BY  
 year_month,  
 MAIN_CATEGORY;

(3) daystosellfirstsale

CREATE VIEW task.days_to_sell_first_sale AS  
WITH sorted_data AS (  
 SELECT  
 FORMAT_DATE('%Y-%m', DATE(s.CREATED_DATE)) AS year_month,  
 DATE_DIFF(DATE(s.SENDING_DATE), DATE(p.PURCHASED_DATE), DAY) AS days_to_sell  
 FROM  
 task.sale AS s  
 JOIN  
 task.purchase AS p   
 ON   
 s.ITEM_ID = p.ITEM_ID  
 WHERE  
 s.RETURNED_DATE IS NULL -- first sale  
)  
SELECT  
 year_month,  
 APPROX_QUANTILES(days_to_sell, 100)[OFFSET(90)] AS days_to_sell_90_percentile  
FROM  
 sorted_data  
GROUP BY  
 year_month;

(4) daystosellsecondorlatersale

CREATE VIEW task.days_to_sell_second_or_later_sale AS  
WITH sorted_data AS (  
 SELECT  
 FORMAT_DATE('%Y-%m', DATE(s.CREATED_DATE)) AS year_month,  
 DATE_DIFF(DATE(s2.SENDING_DATE), DATE(s.RETURNED_DATE), DAY) AS days_to_sell  
 FROM  
 task.sale AS s  
 JOIN  
 task.sale AS s2   
 ON   
 s.ITEM_ID = s2.ITEM_ID  
 AND s2.CREATED_DATE > s.CREATED_DATE -- next sale  
 WHERE  
 s.RETURNED_DATE IS NOT NULL -- second or later sale  
)  
SELECT  
 year_month,  
 APPROX_QUANTILES(days_to_sell, 100)[OFFSET(90)] AS days_to_sell_90_percentile  
FROM  
 sorted_data  
GROUP BY  
 year_month;

Поділитися панеллю

Я створив візуалізацію в Google Looker.

[

Case study аналітика даних

Looker Studio перетворює ваші дані на інформативні панелі та звіти, які легко читати, ділитися та повністю...

lookerstudio.google.com

](https://lookerstudio.google.com/reporting/57bba6d3-686f-414c-87c8-f24050e618e2?source=post_page-----39a3a1784df4--------------------------------)

Аналіз даних

Деякі висновки

[Загалом]

Оцінюючи ДОХІД за ПРОДАЖЕМ та ПОКУПКОЮ, продажі залишаються більш ніж удвічі більшими за витрати на покупку.
Є хороші ознаки того, що витрати знизились, а продажі зросли з останнього періоду близько 2019–04.

[Покупка]

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

[Продаж]

Транзакції зростають через серпень та листопад, а потім зменшуються.
За останні місяці доходи були вищими за минулий рік, а за останні 12 місяців доходи зросли приблизно на 17% порівняно з минулим роком.

[З початку року]

Порівняно з попереднім періодом та минулим роком, червень 2019 має дуже низькі транзакції та продажі.
Я перевірю деякі фактори (канали, нові/повторні сегменти клієнтів, категорії товарів та варіанти), щоб знайти зміни в тенденціях.

[Медіа]

CE vs Media загалом

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

Сезонні тенденції для CE та Media
текст перекладу
- CE, як правило, трохи повільніша наприкінці року (грудень та січень), тоді як Media залишається відносно стабільною.
- CE є більш змінною з місяця в місяць і може бути більш підданою впливу акцій та сезонності.

[Канали]

reBuy.de

reBuy.de — канал з найвищими загальними транзакціями та доходами, що вказує на дуже високий обсяг трафіку.
Потрібно перевірити, чому покупки за червень 2019 року відбуваються лише через reBuy.de.

Amazon

Amazon підтримує стабільний дохід від ПРОДАЖУ, але не виділяється так, як reBuy.de.

B2B

B2B покупки CE мають значний вплив на результати покупок, але майже не було здійснено жодних B2B покупок з березня 2019 року.

[Сегмент клієнтів — нові vs постійні]

Постійні клієнти є стабільним джерелом доходу.
Покупки та продажі нових клієнтів зростатимуть до Різдва та кінця року.
текст перекладу
Це, ймовірно, є важливим фактором, що сприяє зростанню доходів.

Коментарі та рекомендації для вжиття дій

[Про бізнес]

Товари для першої покупки будуть продаватися приблизно 5 місяців. Знаючи це, рекламні акції на покупки слід проводити негайно, щоб створити сплеск продажів під час подарункового сезону з жовтня по листопад і розширити запаси.
Пік покупок зазвичай припадає на період з липня по жовтень, тому важливо збільшити запаси саме в цей пік.
Зараз Media має значно більше транзакцій з покупок і продажів, ніж CE, але я рекомендую зосередитися на CE, оскільки його одинична ціна вища, при просуванні покупок найближчим часом.
Також слід придбати більше CE класу A1/A2, оскільки більшість продажів CE складаються з A1/A2.
Було б корисно оптимізувати стратегію продажів і зосередити маркетингові зусилля на особливо сильних ринках (Німеччина) або на нових ринках (наприклад, Великобританія, Нідерланди, Іспанія).
Потрібно перевірити, чому покупки за червень 2019 року відбуваються лише через reBuy.de.
Вплив на результати покупок значний, коли є покупки B2B CE, але з березня 2019 року вони взагалі не здійснюються через B2B. Потрібно розслідувати, чому.
Після червня 2019 року, коли кількість покупок повинна зрости, було б хорошою ідеєю націлити на повторних клієнтів, щоб поповнити запаси на Handy, Bücher, Tablet тощо.
І провести кампанії для залучення нових клієнтів на продажі, особливо для Handy та Bücher під час подарункового сезону, такого як Різдво.

[Про операцію з даними]

Я б перевірив і оптимізував джерело набору даних, щоб отримувати більш чисті дані в майбутньому.
Багато записів покупок мають значення ‘GRADEDDATE’ або ‘PURCHASEDDATE’ = NULL.
Багато записів продажів, де ‘Payment date’ = NULL навіть після року.
Деякі записи мають ‘NEWCUSTOMERORDER’ = ‘false’, хоча ‘ORDER_RANK’ = 1.

— Це все. Дякую! 🙂

Перекладено з: Exploring E-Commerce Performance and Insights | SQL, Python, Looker