Трансформируем ваши данные в прибыль

Геолокация

Офис Москва

Пн — Пт: с 10:00 до 19:00

ГлавнаяБлогКак узнать совершил клиент первую или повторную покупку?

Как узнать совершил клиент первую или повторную покупку?


3 минут(ы)

WP Post Author

В ответственность работы маркетинга входит детальный анализ данных целевой аудитории и собственного клиентского списка.

В наше время выполнять эти задачи при помощи работы с современными инструментами аналитики становиться все проще. Но даже сейчас без знаний SQL иногда приходит сложно.

Давайте вместе разберем пример, как нам присвоить ярлык первой или повторной покупки каждой транзакции пользователей в нашей базе данных.

Предположим, что у вас есть следующие данные в СУБД:

  • Дата
  • Идентификатор пользователя
  • Индектификатор транзакции

Исходная таблица
Рис. Исходная таблица.

К данной таблице нам нужно добавить столбец, который будет содержать напротив каждой транзакции информацию о том первичная она или вторичная.

Задача кажется очень простой, но вся загвоздка в том, что в нашем датасете нет времени покупки, есть только дата. Поэтому правильным решением будет использование следующей конструкции, содержащей оконную функцию:

SELECT
date
, user_id
, transaction_id
, CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date, transaction_id) = 1 THEN 'first'
ELSE 'replay' END AS transaction_Type
FROM dataupload-230410.Clients_Data.alisa_test
ORDER BY
user_id
, date

В результате мы получим следующую результирующую таблицу:
результирующая таблица

Вся хитрость запроса заключается в том, что мы проверяем не только минимальную дату, но еще и минимальное значение транзакции

OVER (PARTITION BY user_id ORDER BY date, transaction_id)

для присвоения ей порядкового номера при помощи

WHEN ROW_NUMBER()

и в том случае, если номер такой строки равен 1

WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date, transaction_id) = 1

, то присваиваем ему признак ‘first’, в ином случае ELSE ‘replay’ и выводим результат в столбце END AS transaction_Type

Здорово, но пока это еще не похоже на какое-то аналитическое решение. А что делать, если нас попросят вывести таблицу, где в первом столбце будет дата, а в оставшихся двух название типа транзакции (ярлыка) со значение количества таких событий на указанную дату?

В этом случае нам нужно воспользоваться конструкцией вида:

WITH result AS (
SELECT
date
, user_id
, transaction_id
, CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date, transaction_id) = 1 THEN 'first'
ELSE 'replay' END AS transaction_Type
FROM ВАША ТАБЛИЦА
ORDER BY
user_id
, date
)
SELECT * FROM
(SELECT date, transaction_Type FROM result)
PIVOT (COUNT(*) FOR transaction_Type IN ('first', 'replay'))
ORDER BY date

В результате мы увидим следующее:
image1

Согласитесь, теперь мы можем построить красивый график, который покажет нам, сколько новых и повторных продаж у нас приходится на ту или иную дату.

Но как все это работает?
Для начала нужно рассказать про оператор WITH, который создает временный результирующий набор данных, над которым мы уже выполняем другие операции. В нашем случае мы даем этому набору название result.
Result представляет собой таблицу, которую мы получали на прошлом шаге, за счет запроса

(
SELECT
date
, user_id
, transaction_id
, CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date, transaction_id) = 1 THEN 'first'
ELSE 'replay' END AS transaction_Type
FROM ВАША ТАБЛИЦА
ORDER BY
user_id
, date
)

Далее следует запрос, который мы выполняем над временной таблицей.

SELECT * FROM
(SELECT date, transaction_Type FROM result)
PIVOT (COUNT(*) FOR transaction_Type IN ('first', 'replay'))
ORDER BY date

В рамках этого запроса используем PIVOT для построения сводной таблицы на основе содержимого столбца transaction_Type.

В результате получаем искомое решение нашей задачи.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Как StreamMyData помогает строить сквозную аналитику и BI

Друзья! Рады пригласить вас на наш первый вебинар. На вебинаре мы расскажем вам о базовых функциях сервиса построения сквозной…

Иван Барченков

Генеральный директор/Партнер