Как оценить эффективность мероприятий в 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-файл. В нашем примере — три файла: emails_20180316.csv, emails_20180418.csv, emails_20180510.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 или спрашивайте в комментариях.