Кейс: SQL + Yandex DataLens. Создаем прозрачную систему принятия решений для EdTech (и не только) за 5 шагов

Представим, что существует вымышленная онлайн-школа «СмартВек», которая специализируется на таких направлениях, как IT, маркетинг и дизайн.

Как и у любого бизнеса, у нее возникают типичные вопросы:

Куда мы сливаем бюджет?
Какие продукты приносят реальные деньги?
Окупается ли наша реклама?
Сколько денег приносит один средний клиент?
И какова наша чистая прибыль?

Специально для этого с помощью Google AI и Яндекс.Таблиц я спроектировала базу данных, импортировала ее в программу DBeever и написала запросы на базе SQL Lite. На фото представлены всего 5 таблиц: студенты, курсы, заказы, маркетинговые расходы и активность.

Кейс: SQL + Yandex DataLens. Создаем прозрачную систему принятия решений для EdTech (и не только) за 5 шагов

Вы спросите: "Ну и чем же твой SQL здесь может помочь?" Разберем на конкретных примерах.

Допустим, вы обратились ко мне с проблемой:

«У нас в категории IT много курсов, но мы не понимаем, на что мы сливаем бюджет, а что реально кормит компанию. И еще, какой там разрыв между первым и третьим местом? Может, нам пора вообще закрыть те курсы, которые никто не покупает?»

Сразу же анализируем:

Ага, если нас интересуют курсы и оплата, значит из базы данных достаем таблицу курсов и таблицу заказов.
Соединяем эти две таблицы с помощью JOIN.
Ставим фильтр WHERE для статуса об успешной оплате и в категории выбираем именно IT.
Группируем по названию и категории курса с помощью GROUP BY.
А в конце добавляем сортировку ORDER BY по убыванию (чтобы самые популярные курсы стояли наверху) и не забываем про LIMIT.

Вот такой SQL-запрос получился бы:

SELECT c.category AS "Направление", c.course_name AS "Курс", COUNT(o.order_id) AS "Количество оплат" FROM courses AS c JOIN orders AS o USING(course_id) WHERE o.status = 'Оплачен' AND c.category = 'IT' GROUP BY c.category, c.course_name ORDER BY "Количество оплат" DESC LIMIT 3;

И вуаля! Результат:

Аналогично можно найти ТОП-3 продукта и для других категорий.

На скриншоте видно, что курсы "Data Scientist" и "Python-разработчик" обгоняют курс "Специалист по нейросетям" с ощутимым разрывом.
Значит, у этих двух курсов самая высокая конверсия.

Как будущий маркетолог-аналитик, я бы предложила компании 10% бюджета с менее популярных курсов (например, "Frontend-разработчик") вложить в эти два. По моему мнению, это могло бы дать быстрый рост выручки уже в следующем месяце.

Если в ТОП-3 не попали другие курсы, такие как "Аналитик данных" и "Веб-разработчик" (хотя это тоже тренд), значит у компании:
1. либо плохой лендинг,
2. либо слишком высокая цена,
3. либо отдел продаж не дожимает клиентов.

Проблема:

«Мы тратим деньги на привлечение трафика из 8 каналов: Поиск, VK, Авито, Яндекс, YouTube и т.д. В кабинетах всё красиво, клики идут. Но непонятно — мы вообще зарабатываем на этой рекламе или нет?»

Для написания запроса я использовала JOIN (сразу 3 таблицы), агрегатные функции (SUM) и математическую формулу:

ROMI = (Доход - Расход) / Расход * 100%.

SQL-запрос:

SELECT mc.channel AS "Канал привлечения", SUM(mc.ad_spend) AS "Затраты на рекламу", SUM(o.final_amount) AS "Выручка с продаж", ROUND((SUM(o.final_amount) - SUM(mc.ad_spend)) / SUM(mc.ad_spend) * 100.0, 2) AS romi FROM marketing_costs AS mc JOIN users AS u ON mc.channel = u.source JOIN orders AS o USING(user_id) WHERE o.status = 'Оплачен' GROUP BY mc.channel ORDER BY romi DESC;

Результат:

Канал Email-рассылка показывает ROMI 2700%. Это отличный результат! Потому что на каждый вложенный рубль компания получает 27 рублей прибыли.
А вот у канала Спецпроекты ROMI самый низкий (200).

Я бы порекомендовала забрать 50% бюджета со Спецпроектов и перекинуть его в Яндекс.Директ или Авито. Это позволило бы при тех же общих затратах значительно увеличить общую выручку компании.

Проблема:

«Нам нужно знать: сколько денег приносит один средний клиент за всё время? Мы не можем тратить на рекламу больше, чем этот клиент отдает. Как найти средний чек на одного уникального покупателя по каждому каналу?»

Чтобы найти совокупную прибыль, которую компания получает от одного клиента за всё время работы с ним, я использовала формулу:

ARPU = Выручка / Количество клиентов

SQL-запрос:

SELECT u.source AS "Канал", ROUND(SUM(o.final_amount) * 1.0 / COUNT(DISTINCT u.user_id), 2) AS "Средний_ltv" FROM users AS u JOIN orders AS o USING(user_id) WHERE o.status = 'Оплачен' GROUP BY u.source ORDER BY "Средний_ltv" DESC;

Результат:

Мы видим, что Яндекс.Директ приносит самых дорогих клиентов с ARPU аж 228 750 рублей! То есть, это горячий трафик и люди сами ищут обучение. В то время как Партнерская сеть дает ARPU в 5 раз меньше (всего лишь 42к).

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

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

Проблема:

«Мы платим налоги, платим кураторам за проверку заданий и, само собой, за хостинг. Как узнать, сколько остается чистыми после всех этих обязательств за всё время?»

Сначала я посчитала всю выручку. Потом общий налог (6%). Посчитала общую себестоимость. И только после этого нашла чистую прибыль при помощи формулы:

Чистая прибыль = (Выручка * Налог) - Себестоимость

SQL-запрос:

SELECT SUM(o.final_amount) AS "Выручка", SUM(o.final_amount * 0.06) AS "Общий налог", SUM(c.cost_price) AS "Общая себестоиомсть", SUM((o.final_amount * (1 - o.tax_rate)) - c.cost_price) AS "Чистая прибыль" FROM orders AS o JOIN courses AS c USING(course_id) WHERE o.status = 'Оплачен';

Результат:

И вот такая красота у нас получается!

Проблема:

«У нас много покупок, но кажется, что люди бросают учебу после первого модуля»

Чтобы отделить пассивных студентов от лояльной аудитории, я ввела критерий активного студента. Нас интересуют те студенты, которые прошли более 1 урока и заходили в ЛК не позже 1 января 2025 года (условно). Для этого я написала вложенный запрос внутри JOIN.

SQL-запрос:

SELECT u.source AS "Канал", COUNT(DISTINCT a.user_id) AS "Активные_студенты", ROUND(AVG(a.max_score), 1) AS "Средний_балл" FROM users AS u JOIN (SELECT user_id, MAX(test_score) AS max_score FROM activity AS a WHERE lessons_completed > 1 AND visit_date > '2025-01-01' GROUP BY user_id) AS a ON u.user_id = a.user_id GROUP BY u.source ORDER BY "Активные_студенты" DESC;

Результат:

Анализ активности показывает, что из Яндекса, YouTube и Email-рассылки приходят целеустремленные студенты с самым высоким потенциалом завершения курса. А вот из VK тоже много регистраций, но живыми остается лишь меньшинство.

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

Этот кейс был создан мной в качестве тренировочного проекта для анализа бизнес-процессов, маркетинговых метрик и написания SQL-запросов. Я делаю только первые шаги в мире маркетинга, поэтому, пожалуйста, не судите строго :)

Для визуального восприятия я собрала все данные Unit-экономики в интерактивный дашборд Yandex, как я бы это сделала, если бы работала маркетологом-аналитиком. Вы можете лично увидеть по ссылке ниже: