Сколько раз клиент посещает сайт застройщика перед звонком

Среди маркетологов распространено мнение, что клиент при покупке недвижимости долго выбирает квартиру: много раз заходит на сайты застройщиков, всё тщательно изучает, а потом уже звонит. Так говорят о медийных площадках и «охватных» ресурсах: якобы клиенты с них переходят на сайт застройщика, изучают информацию, потом возвращаются на сайт, например, по брендовым запросам через поисковики и только тогда звонят. На самом деле зачастую это не так.

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

Понадобятся динамический коллтрекинг. В нашем варианте это CoMagic (UIS), сырые данные Яндекс.Метрики и Google BigQuery.

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

При динамическом коллтрекинге каждая сессия пользователя на сайте получает собственный телефонный номер. Для каждой такой сессии CoMagic определяет clientID Метрики через yaCounterXXXXXX.getClientID().

В интерфейсе CoMagic такой clientID можно найти в отчете Список обращений → Звонки:

CoMagic передает в Метрику CSV-таблицу, где есть ClientID, дата и время визита:

Метрика получает от CoMagic информацию о звонке и по ClientID добавляет эти данные к тому визиту посетителя, который предшествовал звонку.

Синхронизация CoMagic и Метрики происходит раз в сутки, данные передаются за прошедший день. На 2, 7, 14 и 20-й день CoMagic повторно отправляет данные в Метрику, если что-то поменялось в информации о звонке, например, если к звонку добавили теги.

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

Сначала нужно выгрузить логи Метрики в BigQuery. В Logs API понадобятся поля:

Далее разберем пример, когда в CoMagic звонки тегируются. Если вы не ставите теги в CoMagic и относите к целевым звонки больше минуты, берите комбинацию двух признаков: выполнение цели с нужным ID (goalID like ‘%XXX%’) и offlineCallTalkDuration > 60. К тому же можно настроить код так, чтобы он не учитывал короткие сессии или прямые доходы.

1. Из сырых данных оставляем только нужные столбцы.

Каждая строка — отдельный визит, для которого мы знаем:

• clientID (уникальный идентификатор пользователя);
• visitID (ID визита);
• dateTime (дата и время начала визита);
• visitDuration (продолжительность в секундах);
• UTMSource (метка utm_source);
• offlineCallTag (теги из CoMagic для визитов со звонками).

Запрос (Legacy SQL):

SELECT
  clientID,
  visitID,
  dateTime,
  visitDuration,
  UTMSource,
  REGEXP_REPLACE(offlineCallTag, r'[\,\'\]\[]|null', '') AS offlineCallTag
FROM
  [%dataset%]
ORDER BY
  offlineCallTag DESC

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

В примере целевой звонок имеет тег «Целевой РА»:

Запрос

SELECT
  clientID,
FROM
  [%dataset%]
WHERE
  offlineCallTag LIKE '%Целевой РА%'
GROUP BY
  clientid

3. Объединяем две таблицы, чтобы оставить только сессии тех, кто звонил; нумеруем визиты по порядку для каждого пользователя:

Видно, что, например, пользователь 154589812623271896 заходил трижды: 18 января, 1 и 4 февраля. Позвонил с третьей сессии.

Запрос

SELECT
  all.clientID AS clientID,
  all.visitID AS visitID,
  ROW_NUMBER() OVER(PARTITION BY all.clientID ORDER BY all.datetime) AS visitNumber,
  all.dateTime AS dateTime,
  all.visitDuration AS visitDuration,
  all.UTMSource AS UTMSource,
  REGEXP_EXTRACT(all.offlineCallTag, '(Целевой РА)?') AS offlineCallTag
FROM (
  SELECT
    clientID,
    visitID,
    dateTime,
    visitDuration,
    UTMSource,
    REGEXP_REPLACE(offlineCallTag, r'[\,\'\]\[]|null', '') AS offlineCallTag
  FROM
    [%dataset%] ) all
INNER JOIN (
  SELECT
    clientID,
  FROM
    [%dataset%]
  WHERE
    offlineCallTag LIKE '%Целевой РА%'
  GROUP BY
    clientid ) call
ON
  all.clientid = call.clientid
ORDER BY
  clientID,
  dateTime

4. Находим для каждого clientID номер визита, во время которого клиент совершил целевой звонок:

Запрос

SELECT
  clientid,
  MIN(visitNumber) AS FirstCallVisitNumber
FROM (
  SELECT
    all.clientID AS clientID,
    all.visitID AS visitID,
    ROW_NUMBER() OVER(PARTITION BY all.clientID ORDER BY all.datetime) AS visitNumber,
    all.dateTime AS dateTime,
    all.visitDuration AS visitDuration,
    all.UTMSource AS UTMSource,
    REGEXP_EXTRACT(all.offlineCallTag, '(Целевой РА)?') AS offlineCallTag
  FROM (
    SELECT
      clientID,
      visitID,
      dateTime,
      visitDuration,
      UTMSource,
      REGEXP_REPLACE(offlineCallTag, r'[\,\'\]\[]|null', '') AS offlineCallTag
    FROM
      [%dataset%] ) all
  INNER JOIN (
    SELECT
      clientID,
    FROM
      [%dataset%]
    WHERE
      offlineCallTag LIKE '%Целевой РА%'
    GROUP BY
      clientid ) call
  ON
    all.clientid = call.clientid
  ORDER BY
    clientID,
    dateTime)
WHERE
  offlineCallTag = 'Целевой РА'
GROUP BY
  clientID

5. Делаем сводную таблицу:

Запрос

SELECT
  FirstCallVisitNumber,
  COUNT(clientid) AS clients
FROM (
  SELECT
    clientid,
    MIN(visitNumber) AS FirstCallVisitNumber
  FROM (
    SELECT
      all.clientID AS clientID,
      all.visitID AS visitID,
      ROW_NUMBER() OVER(PARTITION BY all.clientID ORDER BY all.datetime) AS visitNumber,
      all.dateTime AS dateTime,
      all.visitDuration AS visitDuration,
      all.UTMSource AS UTMSource,
      REGEXP_EXTRACT(all.offlineCallTag, '(Целевой РА)?') AS offlineCallTag
    FROM (
      SELECT
        clientID,
        visitID,
        dateTime,
        visitDuration,
        UTMSource,
        REGEXP_REPLACE(offlineCallTag, r'[\,\'\]\[]|null', '') AS offlineCallTag
      FROM
        [%dataset%] ) all
    INNER JOIN (
      SELECT
        clientID,
      FROM
        [%dataset%]
      WHERE
        offlineCallTag LIKE '%Целевой РА%'
      GROUP BY
        clientid ) call
    ON
      all.clientid = call.clientid
    ORDER BY
      clientID,
      dateTime)
  WHERE
    offlineCallTag = 'Целевой РА'
  GROUP BY
    clientID )
GROUP BY
  FirstCallVisitNumber
ORDER BY
  FirstCallVisitNumber

6. Визуализируем данные, если необходимо.

Из BigQuery легко отправить данные в Google Data Studio, чтобы сделать простую визуализацию:

В моем примере, построенном на основе данных московского проекта бизнес-класса, видно, что 67% пользователей звонят после первого визита на сайт, почти 80% — не позже второго. Аналогичные расчеты для проектов экономкласса показали, что после первого визита застройщику звонит даже большее число клиентов. Оказывается, люди не тратят много времени и сессий, чтобы позвонить застройщику. Так и передайте своим медиапланерам.

Над статьей работали: автор Виталий Бахвалов, редактор Ника Троицкая