Стероиды для данных в Google Data Studio или руководство по вычисляемым полям

Веб-аналитика, в моем понимании, делится на 4 базовых блока: сбор данных, хранение и обработка, визуализация и собственно анализ. А самый мой любимый блок — это визуализация. Очень уж мне нравится строить красивые графики, гистограммы и «пироги», после чего собирать их в крутые дашборды. Видимо сказывается творческая натура.

А в каком инструменте получаются самые красивые визуализации? Правильно, в Google Data Studio 😉

Достаточно часто при визуализации, нам может не хватать каких-то параметров и показателей в источнике данных и приходится создавать свои.

Для создания вычисляемых полей Data Studio предлагает ряд функций, которые могут использоваться в формулах. Их список опубликован в официальной документации на английском языке. Почему-то никто не догадался перевести эту доку. А я не только перевел, но и специально для вас собрал кейсы, как можно применить ту или иную функцию. Но в начале немного теории.

Итак, поехали!

Что такое вычисляемые поля?

Вычисляемые поля позволяют создавать новые параметры и показатели для ваших отчетов.

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

Подобно обычным полям, вычисляемые поля имеют определенный тип данных. Например, если ваше вычисляемое поле использует арифметические или агрегационные функции, то тип данных будет «Число», а если текстовую функцию, то «Текст».

Что можно делать с вычисляемыми полями:

простые арифметические вычисления (сложение, вычитание, деление и умножение);
применять функции (агрегирование, арифметические, функции даты, текстовые и геофункции);
использовать условную логику;
использовать в формуле для расчета другого вычисляемого поля.

Как создать вычисляемое поле?

Откройте источник данных.
В правом верхнем углу нажмите ДОБАВИТЬ ПОЛЕ .
Введите имя для этого поля (имя должно быть уникальным и не должно повторять существующие названия параметров и показателей).
Введите формулу.
Сохраните новое поле.

Функции

Вот мы и подобрались к самому интересному и мощному инструменту Data Studio — функциям.

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

Все функции можно разделить на 5 больших блоков, которые я расположил в порядке их полезности по моей субъективной оценке.

А в конце статьи будет большой блок с примерами использования функций, так что если вы уже хорошо изучили справку Google и душа требует кейсов, смело переходите к примерам.

Самое полезное

CASE WHEN Страна = 'Россия' THEN 'Материк: Евразия' ELSE 'Другие материки' END — вычисляет значение, основанное на наборе заданных булевых выражений.

В условиях WHEN можно использовать следующие арифметические операторы:

И следующие логические:

А также поиск по регулярному выражению и списку:

REGEXP_MATCH
IN

CAST(Поле или выражение AS Тип) — преобразование типов (параметр типа может иметь значение NUMBER или TEXT).

Агрегирование

MAX(X) — возвращает максимальное значение X.

MIN(X) — возвращает минимальное значение X.

SUM(X) — возвращает сумму всех значений X.

AVG(X) — возвращает среднее всех значений X.

COUNT(X) — возвращает количество значений X.

COUNT_DISTINCT(X) — возвращает количество уникальных значений X.

MEDIAN(X) — возвращает медиану всех значений X.

PERCENTILE(X,Y) — возвращает N-ю процентиль всех значений X. N представляет собой значение с плавающей запятой в диапазоне от 0 до 100 (не включительно).

VARIANCE(X) — возвращает отклонение (дисперсию) X.

Текстовые функции

CONCAT(X, Y) — возвращает текст, представляющий собой объединение X и Y.

HYPERLINK(URL, Текст ссылки) — возвращает гиперссылку.

UPPER(Поле или выражение) — преобразует X в верхний регистр.

LOWER(Поле или выражение) — преобразует X в нижний регистр.

REGEXP_EXTRACT(Поле или выражение, Регулярное выражение) — возвращает первую совпадающую подстроку в X, которая соответствует регулярному выражению.

REGEXP_MATCH(X, Регулярное выражение) — возвращает true, если X равен Y, в противном случае – false. Используется только в условии WHEN утверждений CASE, а не в качестве самостоятельной функции.

REGEXP_REPLACE(A, '[a-z]+', 'A') — заменяет в Х все вхождения регулярного выражения на указанную строку.

REPLACE(Поле или выражение, Искомое значение, Заменяющее значение) — возвращает копию X, в которой все вхождения Y заменены на Z

SUBSTR(Поле или выражение, Начальная позиция, Длина) — позволяет извлечь из выражения его часть заданной длины, начиная от начальной позиции.

TRIM(Поле или выражение) — возвращает X с удалением начального и конечного пробелов.

LENGTH(Поле или выражение) — возвращает количество символов в X.

Функции даты

Дату и время можно вводить в следующих форматах:

'BASIC' — %Y/%m/%d-%H:%M:%S.
'DEFAULT_DASH' — %Y-%m-%d [%H:%M:%S].
'DEFAULT_SLASH' — %Y/%m/%d [%H:%M:%S].
'DEFAULT_DECIMAL' — %Y%m%d [%H:%M:%S.
'RFC_1123' — например, Sat, 24 May 2008 20:09:47 GMT.
'RFC_3339' — например 2008-05-24T20:09:47Z.
'SECONDS' — секунд с начала отсчета.
'MILLIS' — милисекунд с начала отсчета.
'MICROS' — микросекунд с начала отсчета.
'NANOS' — наносекунд с начала отсчета.
'JULIAN_DATE' — дней с начала отсчета.
'DECIMAL_DATE' — то же, что 'DEFAULT_DECIMAL'.

А также любой формат strptime format. Если у параметра уже есть значение даты и времени, формат задавать не обязательно.

DATE_DIFF(X, Y) — возвращает разницу в днях между X и Y (X — Y).

DAY(X, Формат ввода) — возвращает значение дня для X.

HOUR(X, Формат ввода) — возвращает значение часа для X по всемирному координированному времени.

MINUTE(X, Формат ввода) — возвращает значение минут для X по всемирному координированному времени.

MONTH(X, Формат ввода) — возвращает значение месяца для X.

QUARTER(X, Формат ввода) — возвращает значение квартала для X

SECOND(X, Формат ввода) — возвращает значение секунд для X по всемирному координированному времени.

TODATE(X, Формат ввода, Формат вывода) — возвращает поле даты и времени в заданном формате для часового пояса UTC.

WEEK(X) — возвращает неделю для X с начала года в соответствии со стандартом ISO 8601.

WEEKDAY(X, Формат ввода) — возвращает день недели для X.

YEAR(X, Формат ввода) — возвращает год для X.

YEARWEEK(X) — возвращает год и неделю для X в соответствии со стандартом ISO 8601.

Арифметические функции

NARY_MAX(X, Y, [Z]*) — возвращает максимальное значение X, Y, [, Z] *. Все входные аргументы должны быть одного типа: все числа или все текст. По крайней мере один входной аргумент должен быть полем или выражением, содержащим поле.

ACOS(X) — возвращает обратный гиперболический косинус X.

ATAN(X) — возвращает обратный гиперболический тангенс X.

ABS(X) — возвращает абсолютное значение X.

COS(X) — возвращает косинус X.

FLOOR(X) — возвращает наибольшее целое значение X. Например, если значение X равно v, FLOOR (X) меньше или равно v.

LOG(X) — возвращает натуральный логарифм X.

LOG10(X) — возвращает логарифм к основанию 10 из X.

ASIN(X) — возвращает обратный гиперболический синус X.

NARY_MIN(X, Y, [Z]*) — возвращает минимальное значение X, Y, [, Z] *. Все входные аргументы должны быть одного типа, все числа или все текст. По крайней мере один входной аргумент должен быть полем или выражением, содержащим поле.

POWER(X, Y) — возвращает результат возведения числа X в степень Y.

ROUND(X, Y) — возвращает X округленных до Y цифр точности.

SIN(X) — возвращает синус X.

SQRT(X) — возвращает квадратный корень из X (X должен быть неотрицательным).

TAN(X) — возвращает тангенс X.

CEIL(X) — возвращает наименьшее целое значение X. Например, если значение X равно v, CEIL (X) больше или равно v.

Геофункции

Географические данные можно вводить в следующих форматах:

'CITY_ID' — код города.
'CONTINENT_CODE' — код континента.
'COUNTRY_ISO_CODE' — код страны.
'REGION_ISO_CODE' — код региона.
'SUB_CONTINENT_CODE' — код субконтинента.

Действующие геокоды определяются здесь:

Коды стран
Коды городов
Коды континентов

TOCITY(X, Формат ввода) — возвращает название города для X.

TOCOUNTRY(X, Формат ввода) — возвращает название страны для X.

TOREGION(X, Формат ввода) — возвращает название области для X.

TOSUBCONTINENT(X, Формат ввода) — возвращает название субконтинента для X.

TOCONTINENT(X, Формат ввода) — возвращает название континента для X.

Кейсы!

Сами по себе функции поодиночке не так интересны, а настоящая магия начинается когда в одной формуле применяется сразу несколько функций.

В формулах я буду использовать названия параметров и показателей из Google Analytics, так как это один из основных источников данных для отчетов.

И да, я понимаю что многое из того, что я привожу ниже как примеры, можно настроить при помощи фильтров, пользовательских параметров и прочих фишек в GA. Но моя задача, показать вам как работают функции в Data Studio, плюс кому-то удобнее модифицировать данные на уровне отчетности, а не сбора.

Объединяем имя хоста и путь к странице

Если в Google Analytics у вас не настроен фильтр, объединяющий хост и путь, то это вполне можно сделать на уровне визуализации.

1. CONCAT(Имя хоста, Страница)

Извлекаем параметры из URL

Параметры запроса часто используются для запуска динамического содержимого, для идентификации источников трафика или отслеживания внутренней рекламы. В любом случае, вытащить содержания параметра из URL можно в Data Studio.

1. // Замените параметр 'source' на необходимый вам (тут и далее, Data Studio не поддерживает комментарии в формулах, так что удаляйте их).
2. REGEXP_EXTRACT(Страница, 'source=([^&]+)')

Приводим названия источников трафика к нижнему регистру

Часто при анализе, нам необходимо привести названия рекламных кампаний, источника или канала к нижнему регистру, из-за того что кто-то из рекламщиков накосячил с UTM-метками.

Например, помимо канала ‘cpc’, у нас могут быть каналы ‘Cpc’ или ‘CPC’.

1. // Аналогично, вы можете использовать LOWER(Источник) или LOWER(Кампания)
2. LOWER(Канал)

Считаем количество уникальных Client ID

Если вы захотите посчитать какой либо параметр содержащий строки, будь то Client ID, Session ID или User ID (которые предварительно должны быть заведены в GA как пользовательские параметры), вам понадобиться функция COUNT_DISTINCT для подсчета уникальных значений и COUNT для неуникальных.

1. COUNT_DISTINCT(Client ID)

Делаем из текста число

В источнике данных Google Analytics Длительность сеанса является текстовым параметром. Чтобы использовать этот параметр для каких-нибудь дальнейших вычислений, нам необходимо изменить его тип на NUMBER.

1. CAST(Длительность сеанса AS NUMBER)

Считаем количество сеансов дольше 60 секунд

Ранее мы создали числовое вычисляемое поле содержащее длительность сеанса. Теперь посчитаем сколько на сайте сеансов больше 1 минуты.

1. SUM(CASE WHEN Преобразованная длительность сеанса > 60 THEN 1 ELSE 0 END)

Группируем страницы по типам

Чаще всего на сайте существует определенное количество типов страниц, например для интернет магазина — это каталог, карточка товара, корзина, персональный раздел, информационные страницы. Что делать, если мы ходим анализировать не конкретные страницы, а их кластеры? Правильно, разобьем страницы на типы при помощи CASE.

1. CASE 
2. WHEN REGEXP_MATCH (Страница, ".*/catalog/.*")
3. THEN "Каталог"
4. WHEN REGEXP_MATCH (Страница, ".*/tovar/.*")
5. THEN "Карточка товара"
6. WHEN REGEXP_MATCH (Страница, ".*/korzina/.*")
7. THEN "Корзина"
8. WHEN REGEXP_MATCH (Страница, ".*/personal/.*")
9. THEN "Персональный раздел"
10. WHEN REGEXP_MATCH (Страница, ".*/info/.*")
11. THEN "Информационные страницы"
12. ELSE "Прочие страницы"
13. END

Считаем количество страниц каждого типа

В предыдущем кейсе мы разбили страницы на определенный типы, но как быть, если теперь мы хотим посчитать количество страниц каждого типа. На помощь к CASE приходит арифметическая функция SUM.

1. SUM(CASE WHEN Тип страницы = "Каталог" THEN 1 ELSE 0 END)

Создаем собственную группу каналов

Иногда, нам недостаточно группы каналов по умолчанию, которую предлагает Google Analytics. И тогда мы создаем свою с блэкджеком и CASE.

1. // Группа не претендует на правильность, просто пример того, как можно сделать.
2. CASE 
3. WHEN ((Источник="direct" AND Канал="(not set)") OR Канал="(none)") THEN "Прямой трафик"
4. WHEN Канал="organic" THEN "Бесплатный поисковый трафик" 
5. WHEN REGEXP_MATCH(Канал,"^(social|social-network|social-media|smm|social network|social media)$") THEN "Трафик из социальных сетей" 
6. WHEN Канал="email" THEN "Трафик с email-рассылок" 
7. WHEN Канал="affiliate" THEN "Аффилиаты" 
8. WHEN Канал="referral" THEN "Реферальный трафик" 
9. WHEN REGEXP_MATCH(Канал,"^(cpc|ppc|paidsearch)$") THEN "Платный поисковый трафик" 
10. WHEN REGEXP_MATCH(Канал," ^(cpv|cpa|cpp)$") THEN "Прочая реклама" 
11. WHEN REGEXP_MATCH(Канал,"^(display|cpm|banner)$") THEN "Медийная реклама" 
12. ELSE "Прочий трафик" 
13. END

Создаем кастомную воронку продаж

Грех не вставить сюда ссылку на мою прошлую статью — Как визуализировать воронку продаж в Google Data Studio? В которой я подробно разбираю, как при помощи CASE и REGEXP_MATCH и уличной магии создать собственную воронку.

На этом, пожалуй, все. Вопросы приветствуются.

Об авторе
Свежие записи

Роман Романчук

Эксперт по маркетинговой и продуктовой аналитике, ex-директор по аналитике Сравни.

Что вы ищите?