Топ 5 метрик в недвижимости. Часть II Как создавать выражения в К50:BI

Отметим, что в данной статье описываются синтаксис выражений для расчёта относительных метрик из имеющихся абсолютных. Здесь можно брать информацию по значению и синтаксису функций.

Настройка всех перечисленных относительных метрик производится в структуре во вкладке “Поля”.

CTR и BR

Так как CTR является относительной величиной, то её можно вывести из других абсолютных метрик – кликов и показов:

Clicks – клики (для Яндекс Директ, Google Ads, Facebook/Instagram, VK).
Impressions – показы (для Яндекс Директ, Google Ads, Facebook/Instagram, VK).
base_shows – показы (для MyTarget).
base_clicks – клики (для MyTarget).

Для расчёта метрики, используйте выражение:

Case when SUM(Impressions)!=0
then SUM(Clicks)/SUM(Impressions)*100
else 0
end



Настройка показателя отказов схожа с настройкой CTR.

Для расчёта BR на данных Google Analytics вам потребуется 2 поля:
- ga:bounces (отказы)
- ga:sessions (сессии)

На основе этих абсолютных метрик можно вывести BR.

Для расчёта метрики, используйте выражение:

Case when SUM(ga_sessions)=0
then 0
else SUM(ga_bounces)/SUM(ga_sessions)*100
end



С Яндекс.Метрикой немного иначе.

Потребуются 2 поля:
- ym_s_bounce (отказы)
- ym_s_visits (сессии)

Поле с отказами в текстовом формате, т.е. Метрика отдаёт информацию в формате “Отказ / Не отказ”. И, чтобы перевести это в числовой формат, можно либо заменить столбец или добавить новый с выражением:

Case when ym_s_bounce like "Отказ" then 1 else 0 end

Тип данных у нового столбца должен быть Integer.



После этого в создаваемом поле выражение будет выглядеть следующим образом:

Case when SUM(ym_s_visits)=0 then 0 else SUM(название изменяемого столбца/нового столбца)/SUM(ym_s_visits)*100 end

CPC, глубина просмотра, длительность сеанса

Для расчёта CPC потребуются клики и расходы из рекламных систем:
- Clicks - клики (для Яндекс Директ, Google Ads, Facebook/Instagram, VK), 
base_clicks - клики (для MyTarget)
- spend - Facebook/Instagram, spent - VK, base_spent - MyTarget, Cost - Яндекс Директ, Google Ads.

Для расчёта этой метрики используйте следующее выражение:

CASE WHEN SUM(Clicks)=0 THEN 0 ELSE SUM(Cost)/SUM(Clicks) END



Для вычисления глубины просмотра на данных Google Analytics потребуются:
- ga:sessions (сессии)
- ga:pageviews (общее количество просмотренных страниц)

Выражение в создаваемом поле:

Case when SUM(ga_sessions)=0 then 0 else SUM(ga_pageviews)/SUM(ga_sessions) end



Для вычисления глубины просмотра на данных Яндекс.Метрики необходимы:
- ym_s_visits (сессии)
- ym_s_pageviews (количество просмотров во время всех визитов)

Выражение в создаваемом поле:

Case when SUM(ym_s_visits)=0 then 0 else SUM(ym_s_pageviews)/SUM(ym_s_visits) end

Для вычисления средней длительности сеанса на данных Google Analytics нужны:
- ga:sessions (сессии)
- ga:sessionDuration (общая продолжительность сеансов пользователей в секундах)

Выражение в создаваемом поле:

CASE WHEN SUM(ga_sessions)=0 THEN 0 ELSE (floor(SUM(ga_sessionDuration)/SUM(ga_sessions)/60)) +((((SUM(ga_sessionDuration)/SUM(ga_sessions)/60) - (floor(SUM(ga_sessionDuration)/SUM(ga_sessions)/60)))*60)/100) END



Для вычисления средней длительности сеанса на данных Яндекс Метрики потребуются:
- ym_s_visits (сессии)
- ym_s_visitDuration (время на сайте (детально в секундах))

Выражение в создаваемом поле:

CASE WHEN SUM(ym_s_visits)=0 THEN 0 ELSE (floor(SUM(ym_s_visitDuration)/SUM(ym_s_visits)/60)) +((((SUM(ym_s_visitDuration)/SUM(ym_s_visits)/60) - (floor(SUM(ym_s_visitDuration)/SUM(ym_s_visits)/60)))*60)/100) END



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

CR из этапа в этап

Рассмотрим группировку данных по дате. Т.е. нам важно видеть ситуацию по воронке и CR этапов без разбивки по источникам, кампаниям и т.д. и иметь возможность выбирать нужный нам временной период.

Если “Продажи” и “Встречи” передаются в одной выгрузке, то схематично структура будет выглядеть так:



Если продажи и встречи в разных выгрузках, то так:



На выходе у вас будет срез со следующими полями:
Даты(Date), Визиты(Visits), Звонки(Calls), Встречи(Meeting), Продажи(Sale).

Чтобы вывести виджет CR (визиты в звонки) нужно прописать в поле выражение:

Case when SUM(название метрики “Визиты” в системе аналитики)=0 then 0
else SUM(calls)/SUM(название метрики “Визиты” в системе аналитики)*100
end


Скриншот на примере данных из Google Analytics

Аналогично для CR (звонки во встречи):

Case when SUM(calls)=0 then 0
else SUM(meeting)/SUM(calls)*100
end



И для CR (встречи в продажи):

Case when SUM(meeting)=0 then 0
else SUM(sale)/SUM(meeting)*100
end

CPL

Чтобы вывести стоимость одного целевого звонка или действия в К50:BI, нужно подготовить структуру с нужным срезом данных (на уровне дат, источника, кампаний и т.д.).

Разберём логику построения структуры, когда нужен срез на уровне источника.
Доступные источники данных: Яндекс.Директ, Google Ads, Facebook/Instagram, Коллтрекинг, Google Analytics.

Схематичная структура:



То есть мы объединяем рекламные системы в одну таблицу, приводим к единообразию название столбцов и создаём столбцы в каждой таблице с названием рекламной системы.


Скриншот K50:BI из настроек структуры при добавлении нового столбца с фиксированным текстовым значением в строках

Аналогично делаем во втором юнионе. Если звонки и цели нужно разграничивать, оставьте разные названия. Создайте столбцы с названием рекламных систем.

Например, для Google Analytics будет так:

case
when ga_source like "google"
and ga_medium like "cpc" then
"Google Adwords"
when ga_source like "yandex"
and ga_medium like "cpc" then
"Яндекс Директ"
when ga_source like "facebook.com"
and ga_medium like "cpc" then
"Facebook"
else
"-"
end

Далее присоединяем по левому join данный union с целевыми конверсиями к рекламным системам по двум полям: дате и названию рекламной системы.

Выражение (для расчёта стоимости одного целевого звонка) должно выглядеть так:

CASE WHEN SUM(Calls)=0 THEN 0
ELSE sum(Cost)/sum(Calls) end



Для расчёта средней стоимости одной цели из Google Analytics:

CASE WHEN SUM(ga_goal2Comletions)=0 THEN 0
ELSE Sum(Cost)/Sum(ga_goal2Comletions) end