Теория: Сопоставление данных из разных систем

Сопоставление данных из различных систем

Источники для сквозной аналитики

Введение в сквозную аналитику начинается с процесса сопоставления данных из разных источников. Давайте более подробно разберем эту тему.

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

Некоторые производители, такие как заводы кондитерских изделий "Красный Октябрь" и "Бабаевский", начинают открывать собственные магазины для продажи своей продукции. Однако большинство розничных магазинов предлагают товары разных брендов, и для производителей, таких как "Красный Октябрь", они также представляют собой источник данных для сквозной аналитики.

В страховых компаниях есть страховые агенты, которые заключают договоры со страхователями. Эти агенты получают вознаграждение за продажу страховых услуг, и существуют специальные расчеты, чтобы определить размер этой комиссии. Например, если компания производит и продает свои продукты, то она имеет дело как с производством, так и с продажей. Она также может использовать различные аналитические инструменты, чтобы оптимизировать процессы и повысить эффективность маркетинга.

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

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

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

Практика

У нас есть данные по регистрациям в таблице registrations и по покупкам в таблице payments

SELECT * FROM registrations;

SELECT * FROM payments;

Ссылка на датасет

Давайте расчитаем средний LTV пользователя. Напомним, что LTV расчитывается как произведение стоимости покупок пользователя на количество покупок за период.

Объединим таблицы и посчитаем срок активности каждого клиента. Будем считать, что дата последней покупки (в нашем случае для упрощения всего одна покупка) это конец периода, а дата регистрации - начало. Так как пользователи платят каждый месяц, то переведем жизненный срок в месяцы.

SELECT
    r.user_id,
    p.amount,
    EXTRACT(YEAR FROM AGE(p.payment_date, r.registration_date)) * 12 +
       EXTRACT(MONTH FROM AGE(p.payment_date, r.registration_date)) AS lifetime
FROM registrations AS r
LEFT JOIN payments AS p
ON r.user_id = p.user_id
LIMIT 3;

https://www.db-fiddle.com/f/2gR99rUuRhS4FftgVAwZWo/3

Наконец, расчитаем средний месячный чек пользователя и средний срок активности. И перемножив, найдем средний LTV.

WITH tab AS (
    SELECT
        p.amount AS amount,
        EXTRACT(YEAR FROM AGE(p.payment_date, r.registration_date)) * 12 +
           EXTRACT(MONTH FROM AGE(p.payment_date, r.registration_date)) AS lifetime
    FROM registrations AS r
    LEFT JOIN payments AS p
    ON r.user_id = p.user_id
)
SELECT
    ROUND(AVG(t.amount)) AS avg_amount,
    ROUND(AVG(t.lifetime)) AS avg_lifetime,
    ROUND(AVG(t.amount) * AVG(t.lifetime)) AS avg_ltv
FROM tab AS t;

https://www.db-fiddle.com/f/2gR99rUuRhS4FftgVAwZWo/4