Веб-аналитика, в моем понимании, делится на 4 базовых блока: сбор данных, хранение и обработка, визуализация и собственно анализ. А самый мой любимый блок — это визуализация. Очень уж мне нравится строить красивые графики, гистограммы и «пироги», после чего собирать их в крутые дашборды. Видимо сказывается творческая натура.
А в каком инструменте получаются самые красивые визуализации? Правильно, в Google Data Studio 😉
Достаточно часто при визуализации, нам может не хватать каких-то параметров и показателей в источнике данных и приходится создавать свои.
Для создания вычисляемых полей Data Studio предлагает ряд функций, которые могут использоваться в формулах. Их список опубликован в официальной документации на английском языке. Почему-то никто не догадался перевести эту доку. А я не только перевел, но и специально для вас собрал кейсы, как можно применить ту или иную функцию. Но в начале немного теории.
Итак, поехали!
Что такое вычисляемые поля?
Вычисляемые поля позволяют создавать новые параметры и показатели для ваших отчетов.
Так же, как и обычные поля, вычисляемые поля зависят от любых фильтров или диапазонов дат, действующих в отчете.
Подобно обычным полям, вычисляемые поля имеют определенный тип данных. Например, если ваше вычисляемое поле использует арифметические или агрегационные функции, то тип данных будет «Число», а если текстовую функцию, то «Текст».
Что можно делать с вычисляемыми полями:
- простые арифметические вычисления (сложение, вычитание, деление и умножение);
- применять функции (агрегирование, арифметические, функции даты, текстовые и геофункции);
- использовать условную логику;
- использовать в формуле для расчета другого вычисляемого поля.
Как создать вычисляемое поле?
- Откройте источник данных.
- В правом верхнем углу нажмите
ДОБАВИТЬ ПОЛЕ .
- Введите имя для этого поля (имя должно быть уникальным и не должно повторять существующие названия параметров и показателей).
- Введите формулу.
- Сохраните новое поле.
Функции
Вот мы и подобрались к самому интересному и мощному инструменту Data Studio — функциям.
Функции нужны для того, чтобы в рамках вычисляемого поля, создать какой-то новый параметр или показатель, которого нет в источнике данных или же скорректировать существующие данные.
Все функции можно разделить на 5 больших блоков, которые я расположил в порядке их полезности по моей субъективной оценке.
А в конце статьи будет большой блок с примерами использования функций, так что если вы уже хорошо изучили справку Google и душа требует кейсов, смело переходите к примерам.
Самое полезное
CASE WHEN Страна = 'Россия' THEN 'Материк: Евразия' ELSE 'Другие материки' END
— вычисляет значение, основанное на наборе заданных булевых выражений.
В условиях WHEN можно использовать следующие арифметические операторы:
- =
- !=
- >
- >=
- <
- <=
И следующие логические:
- AND
- OR
А также поиск по регулярному выражению и списку:
- 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'
.
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 у вас не настроен фильтр, объединяющий хост и путь, то это вполне можно сделать на уровне визуализации.
CONCAT(Имя хоста, Страница)
Извлекаем параметры из URL
Параметры запроса часто используются для запуска динамического содержимого, для идентификации источников трафика или отслеживания внутренней рекламы. В любом случае, вытащить содержания параметра из URL можно в Data Studio.
// Замените параметр 'source' на необходимый вам (тут и далее, Data Studio не поддерживает комментарии в формулах, так что удаляйте их). REGEXP_EXTRACT(Страница, 'source=([^&]+)')
Приводим названия источников трафика к нижнему регистру
Часто при анализе, нам необходимо привести названия рекламных кампаний, источника или канала к нижнему регистру, из-за того что кто-то из рекламщиков накосячил с UTM-метками.
Например, помимо канала ‘cpc’, у нас могут быть каналы ‘Cpc’ или ‘CPC’.
// Аналогично, вы можете использовать LOWER(Источник) или LOWER(Кампания) LOWER(Канал)
Считаем количество уникальных Client ID
Если вы захотите посчитать какой либо параметр содержащий строки, будь то Client ID, Session ID или User ID (которые предварительно должны быть заведены в GA как пользовательские параметры), вам понадобиться функция COUNT_DISTINCT для подсчета уникальных значений и COUNT для неуникальных.
COUNT_DISTINCT(Client ID)
Делаем из текста число
В источнике данных Google Analytics Длительность сеанса
является текстовым параметром. Чтобы использовать этот параметр для каких-нибудь дальнейших вычислений, нам необходимо изменить его тип на NUMBER.
CAST(Длительность сеанса AS NUMBER)
Считаем количество сеансов дольше 60 секунд
Ранее мы создали числовое вычисляемое поле содержащее длительность сеанса. Теперь посчитаем сколько на сайте сеансов больше 1 минуты.
SUM(CASE WHEN Преобразованная длительность сеанса > 60 THEN 1 ELSE 0 END)
Группируем страницы по типам
Чаще всего на сайте существует определенное количество типов страниц, например для интернет магазина — это каталог, карточка товара, корзина, персональный раздел, информационные страницы. Что делать, если мы ходим анализировать не конкретные страницы, а их кластеры? Правильно, разобьем страницы на типы при помощи CASE.
CASE WHEN REGEXP_MATCH (Страница, ".*/catalog/.*") THEN "Каталог" WHEN REGEXP_MATCH (Страница, ".*/tovar/.*") THEN "Карточка товара" WHEN REGEXP_MATCH (Страница, ".*/korzina/.*") THEN "Корзина" WHEN REGEXP_MATCH (Страница, ".*/personal/.*") THEN "Персональный раздел" WHEN REGEXP_MATCH (Страница, ".*/info/.*") THEN "Информационные страницы" ELSE "Прочие страницы" END
Считаем количество страниц каждого типа
В предыдущем кейсе мы разбили страницы на определенный типы, но как быть, если теперь мы хотим посчитать количество страниц каждого типа. На помощь к CASE приходит арифметическая функция SUM.
SUM(CASE WHEN Тип страницы = "Каталог" THEN 1 ELSE 0 END)
Создаем собственную группу каналов
Иногда, нам недостаточно группы каналов по умолчанию, которую предлагает Google Analytics. И тогда мы создаем свою с блэкджеком и CASE.
// Группа не претендует на правильность, просто пример того, как можно сделать. CASE WHEN ((Источник="direct" AND Канал="(not set)") OR Канал="(none)") THEN "Прямой трафик" WHEN Канал="organic" THEN "Бесплатный поисковый трафик" WHEN REGEXP_MATCH(Канал,"^(social|social-network|social-media|smm|social network|social media)$") THEN "Трафик из социальных сетей" WHEN Канал="email" THEN "Трафик с email-рассылок" WHEN Канал="affiliate" THEN "Аффилиаты" WHEN Канал="referral" THEN "Реферальный трафик" WHEN REGEXP_MATCH(Канал,"^(cpc|ppc|paidsearch)$") THEN "Платный поисковый трафик" WHEN REGEXP_MATCH(Канал," ^(cpv|cpa|cpp)$") THEN "Прочая реклама" WHEN REGEXP_MATCH(Канал,"^(display|cpm|banner)$") THEN "Медийная реклама" ELSE "Прочий трафик" END
Создаем кастомную воронку продаж
Грех не вставить сюда ссылку на мою прошлую статью — Как визуализировать воронку продаж в Google Data Studio? В которой я подробно разбираю, как при помощи CASE и REGEXP_MATCH и уличной магии создать собственную воронку.
На этом, пожалуй, все. Вопросы приветствуются.
- Кейс: как построить отдел аналитики в большой компании? - 06.05.2022
- Учимся применять оконные функции - 29.09.2020
- Автоматизация отчетности при помощи SQL и Power BI - 05.04.2020
Здравствуйте. А есть возможность создать отфильтрованное значение? Например, есть много event action и есть ряд из них которые содержат visit, как значение. Соответсвенно хочу посчитать количество таких эвентов и поделить на количество пользователей, чтобы узнать количество их на пользователя,
Здравствуйте.
Такая возможность есть.
Попробуйте следующую формулу:
SUM(CASE WHEN REGEXP_MATCH(Действие по событию,’.*visit.*’)THEN 1 ELSE 0 END) / Пользователи
Очень странно, все время пишет Invalid formula
Странно, вчера проверял, работало.
Тогда попробуйте так.
1) Сначала считаем события в отдельном поле которое назовем, к примеру «Считаем события»:
SUM(CASE WHEN REGEXP_MATCH(Действие по событию,’.*visit.*’)THEN 1 ELSE 0 END)
2) А уже потом, в другом рассчитываемом поле, считаем количество событий на пользователя:
Считаем события / Пользователи
Большое спасибо! Сработало!
Считает все 1
Подскажите пжлст,
SUM(CASE
WHEN Действие по событию = «tournaments_open» THEN 1
ELSE 0 END)
Получаю единицу, хотя это точно не так, хочу вывести на диаграмме по дням, но везде получаются единицы.
В дальнейшем хочу использовать это число для вычисления процента юзеров совершавших это действие
Получилось разобраться?
Здравствуйте! Ругается на деление поля таблицы на конструкцию COUNT (…). Говорит, что нельзя одновременно использовать агрегированные и неагрегированные значения…. Как быть? Подскажите, если Вы еще здесь
Макс роялти (руб.) / COUNT(CONCAT(Название портала,»|»,Начало подписки))
Спасибо за статью! Очень полезный материал. Я бы еще добавил пример из справки:
CASE
WHEN Country IN («USA»,»Canada»,»Mexico») THEN «North America»
WHEN Country IN («England»,»France») THEN «Europe»
ELSE «Other»
END
Спасибо!
Да, про IN никак не упомянул.
Для тех кто будет читать комменты, оператор IN позволяет определить, совпадает ли значение объекта со значением в списке.
То есть в CASE вы можете использовать арифметические операторы (=, !=, >, >=, <, <=), регулярное выражение (REGEXP_MATCH) и список (IN).
Здравствуйте. Интересная статья, спасибо.
Столкнулась с проблемой, при создании вычисляемых полей.
Сначала посчитала количество сеансов в которых били просмотрены страницы товаров, допустим 270 новый показатель «Product pages» .
COUNT_DISTINCT(CASE WHEN REGEXP_MATCH (Page, «.*/a/.*») THEN Session ID ELSE «» END)
Количество транзакций 6. Когда я попыталась создать новое вычисляемое поле где, Transactions / Product pages, получаю 2. Если в формулу подставить 270 считает корректно, Transactions / 270. В чем может быть проблема?
Это происходит потому что Data Studio кривой и недоработанный продукт 🙂 Надо использовать Power BI (все чаще прихожу к этой мысли).
А если по делу, то скорее всего в том новом вычисляемом поле, которое вы пытаетесь создать, в выборку попадают все страницы сайта. То есть нужно для диаграммы, на которой вы будете выводить значение показателя применить фильтр содержащий выборку страниц .*/a/.*
Я бы копал в этом направлении, к сожалению не видя данных не могу более точно сказать…
Спасибо) попробую.
Спасибо за замечательную публикацию, очень помогла. Но пока не могу рассчитать сессии с транзакциями, может у кого-то есть опыт или идеи?
Здравствуйте!
Как-то так:
CASE WHEN Транзакции > 0 THEN Сеансы ELSE 0 END
И совсем забыл) В GA же есть отдельный показатель «Сеансы с транзакциями», так что ничего рассчитывать не нужно)
Все прояснилось, конечно, лучше не усложнять, я тоже о таком совсем забыла) Спасибо!
Спасибо Роман за полезный материал. Пытаюсь решить задачу с оценкой NPS с помощью GDS. Необходимо распределить ответы респондентов CASE
WHEN Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге 6 AND <9
THEN "Нейтралы"
ELSE Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге
"Промоутеры"
END Когда значения в строке меньше или равно 6 то новое поле "критики"
Когда значения в строке 7-8 то новое поле "нейтралы"
Иначе когда значения в строке 9-10 то новое поле "промоутеры"
Но что-то пишет ошибку. Может подскажите в чем ошибка?
Здравствуйте! А можете код целиком привести?
Добрый день Роман https://uploads.disquscdn.com/images/cc1eaec61182ea178d6dbfa15c97d12cc6896a1f153401c537fb756f0e196241.png (эта как таблица выглядит связанная с Гугл Формой). Попробовал 2 варианта. Первый — отдельно подсчитать критиков COUNT (CASE WHEN Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге <7 THEN 1 ELSE 0 END) - результат 0 хотя должно быть 7 второй вариант CASE
WHEN Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге <=6 THEN "Критики" WHEN Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге >6 AND <9
THEN "Нейтралы"
ELSE Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге
"Промоутеры"
END
У вас синтаксические ошибки в CASE, вот так работает:
CASE
WHEN Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге <=6 THEN "Критики" WHEN Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге >6 AND Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге <9
THEN "Нейтралы"
ELSE "Промоутеры"
END
https://uploads.disquscdn.com/images/2ecae295859fef6d75f73d9b494f771fccc2108a4b7665c04f6df7adb0c7cc81.png
Добрый день Роман! Да действительно — не подумал про условие and. Спасибо большое.
Здравствуйте, хотел построить группу каналов через фильтр по UTM, но таблица не выдает нужные строки.
Пример
CASE
WHEN Страница = «.*pm_device_type=desktop.*» THEN «desktop»
WHEN Страница = «.*pm_device_type=mobile.*» THEN «mobile»
END
Выдает ошибку конфигурации.
Оказало фильтр не совместим с данными категории Adwords.
Подскажите в чем проблема.
И как настроить чтобы данные показывались по своим параметрам utm_content=txt1, txt2…. независимо от того сколько этих параметров
Вам нужно добавить в условие регулярное выражение:
WHEN REGEXP_MATCH(Страница,».*pm_device_type=desktop.*») THEN «desktop»
А также вашей формуле не хватает ELSE.
Роман, отличный материал, спасибо.
У меня остался неразрешенный вопрос. Как разделить одно значение в исходных данных на два по определенному разделителю?
Пример есть некое поле со значениям:
источник1|канал1
источник2|канал1
источник1|канал2
источник1|канал5
Как получить данные вида (разбить это Поле на два):
Поле 1:
источник1
источник2
источник1
источник1
Поле 2:
канал1
канал1
канал2
канал5
К сожалению, в DS сейчас нет функции SPLIT (разделение строки), поэтому то что вы хотите сделать весьма затруднительно https://www.en.advertisercommunity.com/t5/Data-Studio/Split-a-string-in-Data-Studio/td-p/1620844
Можно попробовать комбинации функций SUBSTR, REGEXP_REPLACE.
Роман, здравствуйте! Подскажите пожалуйста, необходимо в отчете менять метки кампаний из Яндекс Директ на фактические их названия. Например, Analytics подтягивает в параметр «Кампания» метку кампании 19307270, нужно заменять это значение на «Барбекю | Поиск». Возможно ли это сделать?
Роман, здравствуйте! Подскажите пожалуйста, необходимо в отчете менять метки кампаний из Яндекс Директ на фактические их названия. Например, Analytics подтягивает в параметр «Кампания» метку кампании 19307270, нужно заменять это значение на «Барбекю | Поиск». Возможно ли это сделать?
все просто. вам нужно хранить эти данные (название компании) в каком-то справочнике и тянуть оттуда.
либо — передавать название кампании в ютмке (utm_campaign=НАЗВАНИЕ_КАМПАНИИ&utm_content=Campaign_{campaignid}|Group_{adgroupid}….
Спасибо за статью, где все логично объяснено. Но есть один вопрос. Как создать 2 условия на одно значение? Пример:
CASE
WHEN REGEXP_MATCH(Campaign name,».*Астана.*») THEN «Астана»
WHEN REGEXP_MATCH(Campaign name,».*Казань.*») THEN «Казань»
else «other»
END
Здесь выделены кампании, в названии которых есть определенное название города. Но мне надо исключить, например из полученных значений еще компании, в чьих названиях содержится «бренд»
Как правильно это сделать?
Буду признателен за помощь.
Попробуйте AND или вложенный CASE.
Роман здравствуйте.
Задача — посчитать количество событий определенной категории. делаю по вашему рецепту: SUM(CASE WHEN Категория событий = «lead_new» THEN 1 ELSE 0 END)
и получаю 1 вместо 5745 событий
подскажите, что я делаю не так?
https://uploads.disquscdn.com/images/bfd19ec9a953a99c6af207c674ea323a172dfc251daf9dd99f090ccebb2347ad.png
таже проблема с подсчетом действий по событию, в гайде что-то не учтено или недосказано.
выбирая показателем вычисляемый параметр
SUM(CASE
WHEN Действие по событию = «tournaments_open» THEN 1
ELSE 0 END)
так же получаю единицу
Попробуйте вот так https://support.google.com/datastudio/thread/11892833?hl=en
у меня вот это https://prnt.sc/1070bl9 работает
Роман, здравствуйте,
https://uploads.disquscdn.com/images/bfd19ec9a953a99c6af207c674ea323a172dfc251daf9dd99f090ccebb2347ad.png
Как сделать разбиение на столбцы по количеству строк
пробовал COUNT_DISTINCT(Average Position = 1) но не работает
хочу сделать столбец 1, 2-3, 4-10, 11-20, 21-30, 31-50, и 51+
пример как хочу сделать http://dl3.joxi.net/drive/2019/05/17/0030/0614/1974886/86/494caa6d19.png
Спасибо.
Нужно создать кастомное поле, например:
CASE
WHEN Ваш показатель > 0 AND Ваш показатель < 1 THEN '1' WHEN Ваш показатель > 1 AND Ваш показатель < 4 THEN '2-3' ... ELSE '' END
Роман, у меня нубский вопрос при том, что я вроде как нормально пользуюсь power bi.
Когда используется выражение CASE что-то не могу понять как его использовать для фильтрации и суммирования.
У вас есть пример
SUM(CASE WHEN Тип страницы = «Каталог» THEN 1 ELSE 0 END)
Но я что-то не пойму что будет суммироваться, какой показатель и как это применить в визуализациях.
Скажем, у меня в action события GA приходят какие-то значения. Я хочу посчитать сколько каких значений приходит и сделать с ними какие-то арифметические действия.
Например, в event action приходят такие значения
next1
next2
next3
back1
back2
back3
Я хочу по определенному event category посчитать сумму срабатываний событий
А = сумма(next1)+сумма(back1)
Меня смущает, что в формуле вроде
SUM(CASE WHEN Тип страницы = "Каталог" THEN 1 ELSE 0 END)
не указывается, что мы будем суммировать.
Я думал, что можно сделать что-то вроде
SUM(CASE WHEN Тип страницы = "Каталог" THEN total events ELSE 0 END)
но на такое выдается ошибка, что нельзя смешивать metrics и dimensions.
Я видимо после power bi какую-то базовую вещь в DS упускаю
Привет!
В примере мы сначала создаем новое вычисляемое поле «Тип страницы:
CASE
WHEN REGEXP_MATCH (Страница, ".*/catalog/.*")
THEN "Каталог"
WHEN REGEXP_MATCH (Страница, ".*/tovar/.*")
THEN "Карточка товара"
WHEN REGEXP_MATCH (Страница, ".*/korzina/.*")
THEN "Корзина"
WHEN REGEXP_MATCH (Страница, ".*/personal/.*")
THEN "Персональный раздел"
WHEN REGEXP_MATCH (Страница, ".*/info/.*")
THEN "Информационные страницы"
ELSE "Прочие страницы"
END
А потом уже берем это новое поле «Тип страницы» и считаем в нем количество получившихся страниц с типом «Каталог»:
SUM(CASE WHEN Тип страницы = "Каталог" THEN 1 ELSE 0 END)
Роман, спасибо. Может подскажете как в вашем примере посчитать индекс: кол-во страниц «Каталог» разделить на кол-во страниц «Товар»?
У меня чуть другой пример, мне надо разделить кол-во одних event action на кол-во других. Т.е. параметр, который для вашего примера надо было создать, здесь по идее уже создан — это и есть event action. Но не пойму как фильтровать для формул значения показателя total events для этого параметра т.е. как в итоге разделить кол-во одних событий на кол-во других.
> как в вашем примере посчитать индекс: кол-во страниц «Каталог» разделить на кол-во страниц «Товар»
SUM(CASE WHEN Тип страницы = "Каталог" THEN 1 ELSE 0 END) / SUM(CASE WHEN Тип страницы = "Товар" THEN 1 ELSE 0 END)
Либо просто создать новые вычисляемые поля: «Каталог» и «Товар», а далее разделить одно на другое.
> мне надо разделить кол-во одних event action на кол-во других
Все тоже самое:
SUM(CASE WHEN REGEXP_MATCH(Event Action,'.*blablabla.*')THEN 1 ELSE 0 END) / SUM(CASE WHEN REGEXP_MATCH(Event Action,'.*testtesttest.*')THEN 1 ELSE 0 END)
Что-то ругается, если такое подставить в формулу вычисляемого показателя: http://joxi.ru/jnAy3ycgWzaBmZ?d=1
А в этом примере
SUM(CASE WHEN REGEXP_MATCH(Event Action,'.*blablabla.*')THEN 1 ELSE 0 END)
оно должно суммировать кол-во именно total events т.е. суммировать то, сколько раз параметр Event Action со значением blablabla встречается в сырых данных GA?
Что-то ругается, если такое подставить в формулу вычисляемого показателя: http://joxi.ru/jnAy3ycgWzaBmZ?d=1
А в этом примере
SUM(CASE WHEN REGEXP_MATCH(Event Action,'.*blablabla.*')THEN 1 ELSE 0 END)
оно должно суммировать кол-во именно total events т.е. суммировать то, сколько раз параметр Event Action со значением blablabla встречается в сырых данных GA?
Ошибка в синтаксисе?
Роман, у меня нубский вопрос при том, что я вроде как нормально пользуюсь power bi.
Когда используется выражение CASE что-то не могу понять как его использовать для фильтрации и суммирования.
У вас есть пример
SUM(CASE WHEN Тип страницы = «Каталог» THEN 1 ELSE 0 END)
Но я что-то не пойму что будет суммироваться, какой показатель и как это применить в визуализациях.
Скажем, у меня в action события GA приходят какие-то значения. Я хочу посчитать сколько каких значений приходит и сделать с ними какие-то арифметические действия.
Например, в event action приходят такие значения
next1
next2
next3
back1
back2
back3
Я хочу по определенному event category посчитать сумму срабатываний событий
А = сумма(next1)+сумма(back1)
Меня смущает, что в формуле вроде
SUM(CASE WHEN Тип страницы = "Каталог" THEN 1 ELSE 0 END)
не указывается, что мы будем суммировать.
Я думал, что можно сделать что-то вроде
SUM(CASE WHEN Тип страницы = "Каталог" THEN total events ELSE 0 END)
но на такое выдается ошибка, что нельзя смешивать metrics и dimensions.
Я видимо после power bi какую-то базовую вещь в DS упускаю
Роман, добрый день!
Спасибо за полезный материал!
Подскажите, пожалуйста, как решить такую задачу?
Надо сделать новый вычисяемый параметр. У него должно быть условие:
Пользоваели (из конкретного сегмента, который ранее был создалв GA) / Конверсии
Как в GDS применить сегмент к параметру и использовать его в дальнейшем?
Спасибо!
Спасибо за статью, сохранил.
Такой вопрос. Надо к стоимости прибавлять НДС но только для Гугла
Конструкция такого вида не работает
Case
WHEN Источник или канал = «yandex / cpc» THEN Стоимость
WHEN Источник или канал = «google / cpc» THEN Стоимость *1.2
END
Как создать такой вариативный вычисляемый показатель ?
Здравствуйте!
Скорее всего не работает, потому что вы забыли про ELSE.
Попробуйте так:
CASE
WHEN Источник или канал = "google / cpc" THEN Стоимость *1.2
ELSE Стоимость
END
Здравствуйте!
Подскажите, вам удалось решить эту проблему ? Мне гугл пишет следующее:
В рассчитываемых полях нельзя одновременно указывать показатели (агрегированные значения) и параметры (неагрегированные значения).
Здравствуйте!
Чтобы не испытывать подобных проблем, лучше всего предварительно выгружайте ваши данные в любую БД или в Google Sheets и стройте дашборд на них.
В GA есть много особенностей связанных с типами данных, агрегированными значениями и тп, которые затрудняют работу.
Ваш ресурс самый чоткий ресурс по веб-аналитике в рунете!
Посты, как боженька на душу положил!
Что ни пост, то огурчик!
Спасибо товарищ!
Спасибо и вам за то что читаете, товарищ)
Товарищ помогите, направьте!
Подскажите как провести сравнение в каждой ячейке за предыдущий период, сгруппировать эти ячейки по неделям и отобразить сравнение для каждой недели.
Сейчас я пытаюсь использовать элемент Сводка, но страница накладывает ограничение в 50 диаграмм
Чтобы обойти ограничение в 50 диаграмм на страницу, теперь мне нужно встроить несколько отчетов во фрейм.
А отчет сводной таблицы не имеет этой функции.
Сравнение за предыдущий период есть в таблице, и что-то подсказывает, что нужно создать вычисляемое поле на основе DATE_DIFF и как-то это все поделить, но пока реализовать не шмогла.
Скриншоты «ожидание/реальность» может смогут помочь? https://uploads.disquscdn.com/images/e863d8462046f4a02390d91287c5ff8a2d433dc4b60a08b0bb8fee3833aa0f28.png
UPD. Нашел вот такой способ https://stackoverflow.com/questions/55239842/previous-week-same-day-comparision-in-google-data-studio
Но одной недели не хватает, только 3 раза можно подмешивать данные, но способ хорош!
UPD2. А ну собственно, я тогда и буду отталкиваться сразу от прошлой недели.
Пока вариант не найду.
UPD3. Вопрос снят! Всех источников хватает. Как раз 5 источников и можно смешать. )
Добрый день, Роман!
Ниже уже спрашивали, к, сожалению ответа не нашел(
Проблема в том что формула возвращает результат «1»
SUM (CASE WHEN Источник = «sendpulse» THEN 1 ELSE 0 END)
Хотя должно быть несколько десятков тысяч…подскажите пожалуйста, в чем может быть причина?
Попробуйте посчитать количество Session Id для интересующего вас источника. Обычно это решает проблему.
У меня похожая проблема, только чуть сложнее.
Мне нужно отношение событий с определённым значением к просмотрам страниц.
Проблема такая же как и у Ростислава.
Задача выянить сколько страниц было дочитано до конца, т.е. поделить «дочитывания» (событие) на просмотры страниц.
Пробовал через SUM () и через Count () всегда возвращает «1».
Можете подсказать как победить GDS в таком случае?
Подскажите, как написать формулу, что если 2*0=0, то тогда значение к примеру 3
Роман, доброго вам времени суток. Может вы сможете помочь, уже весь мозк сломал.
В отчетах в экселе делаю столбцы с распределением бюджета https://prnt.sc/s0ihmg. Вот не получается у меня реализовать это в Студии. Возможно ваш опыт подскажет возможно ли вообще это реализовать?
Спасибо!
Доброго!
Не совсем понял логику подсчета.
Вам нужно создать новое вычисляемое поле, примерно так: https://uploads.disquscdn.com/images/9b46dc4b82a0058b906c5a42f7b64eb8811790c39330349ee1bc3e50a4984b29.png
Роман, приветствую
Сделал простое вычисляемое поле «Цена клика». Стоимость разделил на клики. Поле считается корректно для каждой строки данных, но при попытке посмотреть срез данных не по дням, а за неделю/месяц, то поле считается не корректно, т.к. агрегация по умолчанию стояла «Сумма». Получается дата студио за выбранный период складывает все «Цены клика». Пробовал агрегацию «Среднее», но вычисляет среднее из значений «Цена клика» и тоже не корректно
Вопрос: можно ли сделать так, чтоб поле рассчитывалось исходя из параметра выбранной даты? Скрины прилагаю
https://uploads.disquscdn.com/images/28e4afeaea32146ed02a15a8d4cad5a15f399fa619397648ea70c6f30bff05f6.png
https://uploads.disquscdn.com/images/c9f9219224a9ecb2e5b067042d15feec3635c4e8953042cb38b8ab14dd9ed78d.png
Здравствуйте!
Очень странно ведет себя вычисляемое поле, вычисление ведь простейшее.
Можете показать формулу?
Роман, добрый день!
Столкнулась с такой проблемой, есть таблица в Google Sheets с данными в формате продолжительность (чч.мм.сс).
В гугл дата студио они импортируются в формате текста, как их можно преобразовать или импортировать в числовом формате, тип «Продолжительность (сек)» ?
Доброго дня
Есть таблица с данными по отказам. Хочу в диаграмму вывести эти показатели с разбивкой на три группы «до12%», «от 12 до 25%» и «более 25%». Делаю такое выражение в новом поле
CASE
WHEN Отказы (Гармония) > 0 and Отказы (Гармония) <= 12 then "до 12%" WHEN Отказы (Гармония) > 12 and Отказы (Гармония) < 25 THEN "от 12 до 25%" ELSE "более 25%" END
Выводится только "до 12%" и "более 25%". В данных присутствуют значения всех трех групп.
Что не так в выражении?
Ошибки в выражении не вижу. Ищите проблему в данных.
Добрый день!
А где можно почитать про использование символов в запросах? Например «=([^&]+)», ^(cpv|cpa|cpp)$») и т.д.
Роман, спасибо за эту статью! Очень интересная и полезная. Еще оч помагла статья про визуализацию воронки )
Столкнулся с таким моментом. Пытаюсь составить таблицу со старинцами, которые посещали пользователи совершившие транзации.
создал такое вычисляемое поле
CASE
WHEN Revenue > 0
THEN Unique Page Views
ELSE 0
END
Но в итоге получаю только страницы на которых произошли транзакции. Возможно вы в курсе какую ошибку я допустил?
И как ее можно исправить )
https://uploads.disquscdn.com/images/33571f3da0496c55e2ee12750587493ce6530e02d0e89428648e54b2a03845b1.png
Добрый день! Кто-нибудь знает как GDS вычисляет медиану? Мне выдает некорректные данные, в чем может быть причина? Например, есть 19 чисел, медиана должна быть = 0:30:42, но GDS выдает 00:55:16 https://uploads.disquscdn.com/images/adb262139d164303a39fd351a7038c60963441522e3e237d6be8a3df2df07f66.jpg
https://support.google.com/datastudio/answer/7582581?hl=en
Подскажите, можно ли решить такую задачку: В столбце «Тип операции» есть значения «Приход» и «Расход» и есть столбец «Сумма операции», можно ли вычленить из столбца «Тип операции» те строки где «Приход» и посчитать их сумму?
Здравствуйте!
Ваша задача решается при помощи простого CASE, например:
SUM(CASE WHEN Тип операции = ‘Приход’ THEN Сумма операции ELSE 0 END)
Спасибо)
Приветствую. Прошу помощи с задачкой.
Есть столбец «ID», в нем уникальный номер каждой строки. В столбце «Номер операции» указывается «ID» той строки, к которой привязывается эта операция. То-есть есть так сказать материнская строка со своим » ID» и в ней с столбце «Номер операции» пусто. И есть строки, в которых тоже свои «ID», но в столбце «Номер операции» указан «ID» той строки которой привязана данная операция. Также есть столбец «Тип операции» и «Сумма операции».
ВОПРОС: Как посчитать сумму всех «Сумма операции», по » ID» материнской сделки и по определенному «типу операции», например-Приход? Спасибо за ответ!
Здравствуйте!
Спасибо за статью.
Помогите пожалуйста, решить проблему.
У меня дублируются кампании в таблице
_Poisk__Msk_Pechat_mobayl_id54170634
_Poisk__Msk_Pechat_desktop_id52144687
54170634
{campaign_name_lat}_id52144687
{campaign_name_lat}_id54170634
Одни и те же кампании, отображаются тремя разными способами.
Как мне задать такое условие, чтобы дата студио понимал по номерам кампаний, что это одно и то же и выводил только на транслите?
Добрый день, огромное спасибо за статью! В ней нашел очень много ответов.
Но, к сожалению, не представляю решения для текущей задачи.
Есть кольцевая диаграмма, которая показывает долю наличных и безналичных средств.
Данные поступают раз в неделю, в воскресенье
Условно:
05.06.21 б/н 1000
05.06.21 наличные 1000
12.06.21 б/н 500
12.06.21 наличные 500
И если установить период «Последние 7 дней», то диаграмма покажет корректные данные:
б/н – 500, наличные – 500
Но если выбрать период 2 недели (на дашборде есть столбчатые диаграммы, для других показателей), то GDS суммирует данные и получается:
б/н – 1500, наличные – 1500
Что является неправдой.
Если включить «особые настройки даты» последние 7 дней, то получается другая ситуация, когда выбираешь предыдущую неделю – GDS пишет «нет данных»
Подскажите, как создать функцию, которая бы заставляла показывать данные на последнее воскресенье периода?
Огромное спасибо
Добрый день. Подскажите, как посчитать долю от итогового значения? Например, посетители с разных видов устройств / все посетители. Хочу получить в таблице столбец с % напротив каждого вида устройств. Спасибо!
Подскажите, перелопатил все по АБС анализу, ничего не нашел в сети под data studio. Одна из формул, та, что в экселе C2/СУММ($C$2:$C$171). А вот как ее через поле реализовать в studio. Если указывать к примеру «Выручка/SUM(Выручка)», то data studio ругается «В рассчитываемых полях нельзя одновременно указывать показатели (агрегированные значения) и параметры (неагрегированные значения). Проверьте типы значений в формуле.» Можете подсказать как обойти этот конфуз?
Здравствуйте!
Используйте в рассчитываемых полях агрегированные данные.
Такая же проблема «В рассчитываемых полях нельзя одновременно указывать показатели (агрегированные значения) и параметры (неагрегированные значения). Проверьте типы значений в формуле. » а пример можно??? не получается посчитать SUM всего событий по конкретному ярлыку. Похоже на какой-то баг Datastudio раньше вреде все считалось