Руководитель веб-аналитики eLama Антон Леонтьев разбирает, как оценивать эффективность мероприятий с помощью Google BigQuery, какие данные для этого нужны и как этот процесс автоматизировать.
Под мероприятиями мы имеем в виду семинары, тренинги, выставки, конференции, фестивали, вебинары и любые другие события, где мы участвуем, чтобы привлекать клиентов. Не важно, организовали ли мы это событие, просто арендовали стенд или отправили сотрудников отдела продаж общаться с участниками и собирать контакты.
С каждого мероприятия у нас должны быть списки email-адресов и телефонов. Мы загрузим их в BigQuery и сопоставим с клиентскими покупками или платежами, которые тоже загрузим в базу данных. О том, что такое Google BigQuery можно прочитать в моей вводной статье.
1. Составим список мероприятий в Google Sheets. Лист назовем, например, schedule:
- event_id — уникальный идентификатор мероприятия, может быть произвольным. Мы инкрементируем с шагом в 10, чтобы потом между событиями можно было добавлять новые, не ломая порядок. Это необходимо, когда какое-то мероприятие мы сразу не учли;
- event_date — дата мероприятия;
- event_name — название мероприятия.
2. Затем создадим в BigQuery пустой dataset events:
3. После этого нужно загрузить список мероприятий в таблицу events.schedule. Это можно сделать с помощью плагина для браузера OWOX BI BigQuery Reports:
Или в самом BigQuery:
4. Теперь нужно обработать email-адреса, полученные на мероприятиях. Для каждой даты мероприятия сформируем свой CSV-файл. В нашем примере — три файла: emails20 180 316.csv, emails20 180 418.csv, emails20 180 510.csv. Если в один день было несколько мероприятий, то все электронные адреса с этих мероприятий нужно записать в один файл.
CSV-файлы должны содержать через запятую event_id и email. Удобно обрабатывать такие файлы в текстовом редакторе SublimeText. Чтобы за один раз ввести event_id во все строки файла, поставьте курсор, нажмите Shift на клавиатуре и правую кнопку мыши и, не отпуская, ведите курсор вниз. Ваш курсор станет активен на нескольких строках, теперь введите идентификатор мероприятия:
Затем загрузите файлы в BigQuery:
Наши три таблицы объединятся в одну партиционированием (разбивкой) по датам:
5. Аналогичным образом обработаем номера телефонов:
6. Затем нужно создать в BigQuery view — виртуальную таблицу — с названием events.emails. Она представляет собой SQL-запрос, который делает следующее:
- все email-адреса объединяются в один список, чтобы в дальнейшем упростить работу и поиск адресов;
- все знаки в адресах приводятся к строчным значениям, убираются пробелы;
- убираем дубли email внутри одного мероприятия.
SELECT
e.email as email,
e.event_id as event_id,
s.event_date as event_date,
s.event_name as event_name,
FROM
(
SELECT
event_id,
LOWER (REPLACE (email,' ', '')) as email, //приводим все емейлы к строчным значениям, удаляем пробелы если были
FROM TABLE_QUERY (events, 'table_id CONTAINS «emails_"')
GROUP BY event_id, email, //группируем чтобы убрать дубли емейлов внутри одного мероприятия (страхуемся от ошибок в исходных данных)
) as e
JOIN events.schedule as s ON s.event_id=e.event_id,
Чтобы сохранить view, нужно вставить текст SQL-запроса в поле для выполнения и сохранить его:
7. Аналогичным образом создадим view для номеров телефонов events.phones:
- все номера телефонов объединяем в один список;
- преобразуем исходный номер телефона phone в phone_clean: удалим скобки и прочие знаки, оставим только цифры, а номера вроде 963 XXX XX XX преобразуем в 7963XXXXXXX и т. д.;
- убираем дубли телефонов внутри одного мероприятия.
SELECT
e.phone as phone,
e.phone_clean as phone_clean,
e.event_id as event_id,
s.event_date as event_date,
s.event_name as event_name,
FROM
(
SELECT
event_id,
phone,
//для анализов будем использовать не исходное поле phone, тк в нем возможны лишние знаки, а преобразованное phone_clean
//алгоритм преобразования неидеальный, и возможны какие-то ошибки, но в целом работает верно
//удалим в нем нецифровые символы (все не цифры заменим на пустой символ)
//если длина 9 и меньше, или 13 и больше, значит это не номер телефона, вернем NULL
//если длина 10 и номер телефона начинается с популярных 495 и тд, значит человек просто забыл добавить 7 в начале (код РФ) — добавим сами
//если длина 10, первая цифра 0, вторая не 0, тогда это украинский номер и добавим 38 в начале //если длина 11 и номер начинается с 8, то заменим ее на 7 (код РФ)
//во всех остальных случаях возьмем просто все цифры из номера CASE
WHEN LENGTH (REGEXP_REPLACE (phone,r'\D',''))<=9 OR LENGTH (REGEXP_REPLACE (phone,r'\D',''))>=13 THEN NULL
WHEN LENGTH (REGEXP_REPLACE (phone,r'\D',''))=10 AND LEFT (REGEXP_REPLACE (phone,r'\D',''),3) IN ('495','499','812','916','926','903','911','921','985','905','925','904','910','909','915','912','915','927','963','913','960','950','920','906','999','961','965','917','952','987','902','988','981','918','937','953','928','919','951','964','962','908','966','967','923','929','924','914','977','968','931') THEN CONCAT ('7', LEFT (REGEXP_REPLACE (phone,r'\D',''),3), RIGHT (REGEXP_REPLACE (phone,r'\D',''),LENGTH (REGEXP_REPLACE (phone,r'\D',''))-3))
WHEN LENGTH (REGEXP_REPLACE (phone,r'\D',''))=10 AND LEFT (REGEXP_REPLACE (phone,r'\D',''),1) IN ('0') AND SUBSTR (REGEXP_REPLACE (phone,r'\D',''),2,1)≠'0' THEN CONCAT ('38',REGEXP_REPLACE (phone,r'\D',''))
WHEN LENGTH (REGEXP_REPLACE (phone,r'\D',''))=11 AND LEFT (REGEXP_REPLACE (phone,r'\D',''),1)='8' THEN CONCAT ('7',RIGHT (REGEXP_REPLACE (phone,r'\D',''),10))
ELSE REGEXP_REPLACE (phone,r'\D','')
END as phone_clean,
FROM
TABLE_QUERY (events, 'table_id CONTAINS «phones_"')
GROUP BY
event_id, phone, phone_clean //группируем чтобы убрать дубли (страхуемся от ошибок в исходных данных)
) as e
JOIN events.schedule as s ON s.event_id=e.event_id,
8. Теперь нужно загрузить таблицу events.transactions_source со всеми платежами ваших клиентов. Это может быть выгрузка из 1С, CRM, файл Excel, база данных или другое. Важно, чтобы там были следующие поля:
- date_payed — дата платежа;
- price — сумма платежа;
- email, phone — электронный адрес и телефон. Если какого-то из них нет, то ничего страшного, отсутствующий параметр просто не будет учитываться при анализе.
Затем создадим view events.transactions, чтобы привести номера телефонов к единому виду phone_clean:
SELECT
date_payed,
price,
email,
CASE
WHEN LENGTH (REGEXP_REPLACE (phone,r'\D',''))<=9 OR LENGTH (REGEXP_REPLACE (phone,r'\D',''))>=13 THEN NULL
WHEN LENGTH (REGEXP_REPLACE (phone,r'\D',''))=10 AND LEFT (REGEXP_REPLACE (phone,r'\D',''),3) IN ('495','499','812','916','926','903','911','921','985','905','925','904','910','909','915','912','915','927','963','913','960','950','920','906','999','961','965','917','952','987','902','988','981','918','937','953','928','919','951','964','962','908','966','967','923','929','924','914','977','968','931') THEN CONCAT ('7', LEFT (REGEXP_REPLACE (phone,r'\D',''),3), RIGHT (REGEXP_REPLACE (phone,r'\D',''),LENGTH (REGEXP_REPLACE (phone,r'\D',''))-3))
WHEN LENGTH (REGEXP_REPLACE (phone,r'\D',''))=10 AND LEFT (REGEXP_REPLACE (phone,r'\D',''),1) IN ('0') AND SUBSTR (REGEXP_REPLACE (phone,r'\D',''),2,1)≠'0' THEN CONCAT ('38',REGEXP_REPLACE (phone,r'\D',''))
WHEN LENGTH (REGEXP_REPLACE (phone,r'\D',''))=11 AND LEFT (REGEXP_REPLACE (phone,r'\D',''),1)='8' THEN CONCAT ('7',RIGHT (REGEXP_REPLACE (phone,r'\D',''),10))
ELSE REGEXP_REPLACE (phone,r'\D','')
END
as phone_clean,
FROM events.transactions_source
Результат выполнения events.transactions:
9. У нас обработаны и загружены в BigQuery все исходные данные. Теперь построим отчет по следующему алгоритму: для каждого контакта из сделок найдем дату первого платежа (время, когда он стал платящим клиентом) и атрибутируем всю ценность контакта ближайшему предшествующему мероприятию, но не раньше, чем за 90 дней. Расчеты по этому алгоритму будут производиться в следующем SQL-запросе:
SELECT
event_id, event_date, event_name, type_contact, contact, date_activation, transactions_6M, money_6M, emails, phones,
FROM
(
SELECT event_id, event_date, event_name, COUNT (email) as emails,
FROM events.emails
GROUP BY event_id, event_date, event_name,
),
(
SELECT event_id, event_date, event_name, COUNT (phone_clean) as phones,
FROM events.phones
GROUP BY event_id, event_date, event_name,),
(
SELECT event_id, event_date, event_name, 'emails' as type_contact, email as contact, date_activation, transactions_6M, money_6M,
FROM
(
//в этой части скрипта мы каждый емейл с каждого мероприятия сопоставим с датой первого платежа (если он был)
//и найдем одно ближайшее мероприятие перед первым платежом (но не более 90 дней)
SELECT
e.email as email,
e.event_id as event_id,
e.event_date as event_date,
e.event_name as event_name,
DATE (t.date_activation) as date_activation,
DATEDIFF (TIMESTAMP (e.event_date), t.date_activation) as datediff,
//количество дней между мероприятием и датой активации
//row_num — порядковый номер мероприятия по близости к дате активации.
//если в один день емейл участвовал в нескольких мероприятиях, то минимальный номер получит контакт с минимальным event_id ROW_NUMBER () OVER (PARTITION BY e.email ORDER BY datediff DESC, event_id) as row_num,
t.transactions_6M as transactions_6M,
t.money_6M as money_6M,
FROM events.emails as e
LEFT JOIN //для каждого емейла найдем дату первого платежа, а также сумму и количество платежей за первые 6 мес.
(
SELECT
t.email as email,
a.date_activation as date_activation,
COUNT (t.price) as transactions_6M, SUM (t.price) as money_6M,
FROM events.transactions as t LEFT JOIN //найдем дату первого платежа (SELECT email, MIN (date_payed) as date_activation FROM events.transactions
GROUP BY email
)
as a ON a.email=t.email
WHERE t.date_payed<=DATE_ADD (a.date_activation,6,"MONTH")
GROUP BY email, date_activation
) as t ON t.email=e.email
HAVING datediff<=-1 AND datediff>=-90 //оставим для расчетов только емейлы с мероприятий до даты первого платежа,) //то есть datediff отрицательные, но не более 90 дней
WHERE row_num=1 //row_num=1 — то есть возьмем только одно ближайшее мероприятие до первого платежа
),
(
SELECT event_id, event_date, event_name, 'phones' as type_contact, phone_clean as contact, date_activation, transactions_6M, money_6M,
FROM
(
//в этой части скрипта мы каждый телефон с каждого мероприятия сопоставим с датой первого платежа (если он был)
//и найдем одно ближайшее мероприятие перед первым платежом (но не более 90 дней)
SELECT
p.phone_clean as phone_clean,
p.event_id as event_id, p.event_date as event_date,
p.event_name as event_name, DATE (t.date_activation) as date_activation,
DATEDIFF (TIMESTAMP (p.event_date), t.date_activation) as datediff, //количество дней между мероприятием и датой активации
//row_num — порядковый номер мероприятия по близости к дате активации.
//если в один день телефон участвовал в нескольких мероприятиях, то минимальный номер получит контакт с минимальным event_id
ROW_NUMBER () OVER (PARTITION BY p.phone_clean ORDER BY datediff DESC, event_id) as row_num,
t.transactions_6M as transactions_6M,
t.money_6M as money_6M,
FROM events.phones as p
LEFT JOIN //для каждого телефона найдем дату первого платежа, а также сумму и количество платежей за первые 6 мес.
(
SELECT t.phone_clean as phone_clean, a.date_activation as date_activation, COUNT (t.price) as transactions_6M, SUM (t.price) as money_6M,
FROM events.transactions as t LEFT JOIN //найдем дату первого платежа (SELECT phone_clean, MIN (date_payed) as date_activation FROM events.transactions
GROUP BY phone_clean) as a ON a.phone_clean=t.phone_clean WHERE t.date_payed<=DATE_ADD (a.date_activation,6,"MONTH") GROUP BY phone_clean, date_activation
) as t ON t.phone_clean=p.phone_clean
HAVING datediff<=-1 AND datediff>=-90 //оставим для расчетов только телефоны с мероприятий до даты первого платежа,) //то есть datediff отрицательные, но не более 90 дней
WHERE row_num=1 //row_num=1 — то есть возьмем только одно ближайшее мероприятие до первого платежа
),
ORDER BY type_contact, contact, event_id,
Затем этот SQL-запрос нужно добавить через OWOX BI BigQuery Reports в документ из пункта 1 с расписанием мероприятий, а также настроить расписание для регулярного обновления:
В результате в исходном документе появится новый лист с результатом выполнения запроса:
В первых нескольких строках содержится статистика по мероприятиям:
- emails — количество адресов, собранных на мероприятии;
- phones — количество номеров телефонов, собранных на мероприятии;
Далее таблица содержит контакты клиентов, которые сконвертировались после мероприятий:
- type_contact — тип контакта (email или телефон);
- contact — сам контакт;
- date_activation — дата первого платежа;
- transactions_6M — сколько оплат (покупок) он совершил в первые шесть месяцев;
- money_6M — суммы покупок в первые шесть месяцев.
Мы используем период шесть месяцев, чтобы у всех мероприятий был одинаковый конечный срок учета платежей, иначе для старых мероприятий цифры будут гораздо выше, чем у новых.
10. В предыдущем пункте мы получили данные по каждому контакту, который стал нашим клиентом. Добавим на первом листе schedule формулы со ссылками на тот лист, чтобы увидеть статистику по мероприятиям:
где:
- email_clients — количество email-адресов, владельцы которых стали нашими новыми клиентами после мероприятия;
- phone_clients — количество номеров телефонов, владельцы которых стали нашими новыми клиентами после мероприятия.
Также хотел бы в этой статье рассказать о функциях БДСУММ и БСЧЁТА в Google Таблицах. Они аналогичны классическим функциям СУММЕСЛИМН () и СЧЁТЗ (), но удобней, т. к. в них можно использовать названия колонок в качестве исходных данных. Если в исходной таблице изменится состав или порядок колонок, то в отчете ничего не сломается.
Пример формулы:
БДСУММ ('Статистика по каждому лиду'!$A:$BA; F$ 1; {{"event_id";"='"&$A2}\{"type_contact";"='"&$D$ 1}});
Мы выбираем данные с листа «Статистика по каждому лиду' (пункт 9) из диапазона столбцов $A:$BA (сразу запас побольше); далее суммируем только значения из колонки, название которой будет совпадать с содержимым ячейки F$ 1 (то есть «transactions_6M'), и только из тех строк, у которых есть совпадения по event_id и type_contact.
В итоге мы получили статистику по каждому лиду после мероприятия, а также сводные данные по мероприятиям. Это поможет сделать выводы по прошедшим мероприятиям и запланировать будущие маркетинговые активности.
Отчет обновляется автоматически по расписанию. Для добавления новых данных нужно только обновлять платежи в BigQuery, добавлять новые мероприятия на лист с расписанием и загружать лиды в соответствующую папку BigQuery. Если чего-то не хватает, смотрите документацию по BigQuery SQL Dialect или спрашивайте в комментариях.
