Руководитель веб-аналитики 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 или спрашивайте в комментариях.

eLama.ru, руководитель группы веб-аналитики