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

Автор: Роман Романчук

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример создания вычисляемого поля

Функции

Вот мы и подобрались к самому интересному и мощному инструменту 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'.

А также любой формат 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 у вас не настроен фильтр, объединяющий хост и путь, то это вполне можно сделать на уровне визуализации.

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 и уличной магии создать собственную воронку.

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

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

Эксперт по аналитике данных, ex-директор по аналитике Сравни.ру. Директор команды данных в Xsolla.
80 replies on “ Стероиды для данных в Google Data Studio или руководство по вычисляемым полям ”

  1. Здравствуйте. А есть возможность создать отфильтрованное значение? Например, есть много event action и есть ряд из них которые содержат visit, как значение. Соответсвенно хочу посчитать количество таких эвентов и поделить на количество пользователей, чтобы узнать количество их на пользователя,

    Ответить

    1. Здравствуйте.
      Такая возможность есть.
      Попробуйте следующую формулу:
      SUM(CASE WHEN REGEXP_MATCH(Действие по событию,’.*visit.*’)THEN 1 ELSE 0 END) / Пользователи

      Ответить

        1. Странно, вчера проверял, работало.
          Тогда попробуйте так.
          1) Сначала считаем события в отдельном поле которое назовем, к примеру «Считаем события»:
          SUM(CASE WHEN REGEXP_MATCH(Действие по событию,’.*visit.*’)THEN 1 ELSE 0 END)
          2) А уже потом, в другом рассчитываемом поле, считаем количество событий на пользователя:
          Считаем события / Пользователи

          Ответить

          3. Подскажите пжлст,
            SUM(CASE
            WHEN Действие по событию = «tournaments_open» THEN 1
            ELSE 0 END)

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

    2. Здравствуйте! Ругается на деление поля таблицы на конструкцию COUNT (…). Говорит, что нельзя одновременно использовать агрегированные и неагрегированные значения…. Как быть? Подскажите, если Вы еще здесь

      Ответить

  2. Спасибо за статью! Очень полезный материал. Я бы еще добавил пример из справки:
    CASE
    WHEN Country IN («USA»,»Canada»,»Mexico») THEN «North America»
    WHEN Country IN («England»,»France») THEN «Europe»
    ELSE «Other»
    END

    Ответить

    1. Спасибо!
      Да, про IN никак не упомянул.
      Для тех кто будет читать комменты, оператор IN позволяет определить, совпадает ли значение объекта со значением в списке.
      То есть в CASE вы можете использовать арифметические операторы (=, !=, >, >=, <, <=), регулярное выражение (REGEXP_MATCH) и список (IN).

      Ответить

  3. Здравствуйте. Интересная статья, спасибо.

    Столкнулась с проблемой, при создании вычисляемых полей.
    Сначала посчитала количество сеансов в которых били просмотрены страницы товаров, допустим 270 новый показатель «Product pages» .
    COUNT_DISTINCT(CASE WHEN REGEXP_MATCH (Page, «.*/a/.*») THEN Session ID ELSE «» END)
    Количество транзакций 6. Когда я попыталась создать новое вычисляемое поле где, Transactions / Product pages, получаю 2. Если в формулу подставить 270 считает корректно, Transactions / 270. В чем может быть проблема?

    Ответить

    1. Это происходит потому что Data Studio кривой и недоработанный продукт 🙂 Надо использовать Power BI (все чаще прихожу к этой мысли).
      А если по делу, то скорее всего в том новом вычисляемом поле, которое вы пытаетесь создать, в выборку попадают все страницы сайта. То есть нужно для диаграммы, на которой вы будете выводить значение показателя применить фильтр содержащий выборку страниц .*/a/.*
      Я бы копал в этом направлении, к сожалению не видя данных не могу более точно сказать…

      Ответить

  4. Спасибо за замечательную публикацию, очень помогла. Но пока не могу рассчитать сессии с транзакциями, может у кого-то есть опыт или идеи?

    Ответить

        1. Все прояснилось, конечно, лучше не усложнять, я тоже о таком совсем забыла) Спасибо!

          Ответить

  5. Спасибо Роман за полезный материал. Пытаюсь решить задачу с оценкой NPS с помощью GDS. Необходимо распределить ответы респондентов CASE
    WHEN Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге 6 AND <9
    THEN "Нейтралы"
    ELSE Оцените по шкале от 0 до 10, какова вероятность того, что вы будете рекомендовать нас другу или коллеге
    "Промоутеры"
    END Когда значения в строке меньше или равно 6 то новое поле "критики"
    Когда значения в строке 7-8 то новое поле "нейтралы"
    Иначе когда значения в строке 9-10 то новое поле "промоутеры"
    Но что-то пишет ошибку. Может подскажите в чем ошибка?

    Ответить

      1. Добрый день Роман 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

        Ответить

        1. У вас синтаксические ошибки в 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

          Ответить

          1. Добрый день Роман! Да действительно — не подумал про условие and. Спасибо большое.

  6. Здравствуйте, хотел построить группу каналов через фильтр по UTM, но таблица не выдает нужные строки.
    Пример
    CASE
    WHEN Страница = «.*pm_device_type=desktop.*» THEN «desktop»
    WHEN Страница = «.*pm_device_type=mobile.*» THEN «mobile»
    END
    Выдает ошибку конфигурации.
    Оказало фильтр не совместим с данными категории Adwords.
    Подскажите в чем проблема.

    И как настроить чтобы данные показывались по своим параметрам utm_content=txt1, txt2…. независимо от того сколько этих параметров

    Ответить

    1. Вам нужно добавить в условие регулярное выражение:
      WHEN REGEXP_MATCH(Страница,».*pm_device_type=desktop.*») THEN «desktop»

      А также вашей формуле не хватает ELSE.

      Ответить

  7. Роман, отличный материал, спасибо.
    У меня остался неразрешенный вопрос. Как разделить одно значение в исходных данных на два по определенному разделителю?
    Пример есть некое поле со значениям:
    источник1|канал1
    источник2|канал1
    источник1|канал2
    источник1|канал5
    Как получить данные вида (разбить это Поле на два):
    Поле 1:
    источник1
    источник2
    источник1
    источник1
    Поле 2:
    канал1
    канал1
    канал2
    канал5

    Ответить

  8. Роман, здравствуйте! Подскажите пожалуйста, необходимо в отчете менять метки кампаний из Яндекс Директ на фактические их названия. Например, Analytics подтягивает в параметр «Кампания» метку кампании 19307270, нужно заменять это значение на «Барбекю | Поиск». Возможно ли это сделать?

    Ответить

  9. Роман, здравствуйте! Подскажите пожалуйста, необходимо в отчете менять метки кампаний из Яндекс Директ на фактические их названия. Например, Analytics подтягивает в параметр «Кампания» метку кампании 19307270, нужно заменять это значение на «Барбекю | Поиск». Возможно ли это сделать?

    Ответить

    1. все просто. вам нужно хранить эти данные (название компании) в каком-то справочнике и тянуть оттуда.
      либо — передавать название кампании в ютмке (utm_campaign=НАЗВАНИЕ_КАМПАНИИ&utm_content=Campaign_{campaignid}|Group_{adgroupid}….

      Ответить

  10. Спасибо за статью, где все логично объяснено. Но есть один вопрос. Как создать 2 условия на одно значение? Пример:
    CASE
    WHEN REGEXP_MATCH(Campaign name,».*Астана.*») THEN «Астана»
    WHEN REGEXP_MATCH(Campaign name,».*Казань.*») THEN «Казань»
    else «other»
    END
    Здесь выделены кампании, в названии которых есть определенное название города. Но мне надо исключить, например из полученных значений еще компании, в чьих названиях содержится «бренд»
    Как правильно это сделать?
    Буду признателен за помощь.

    Ответить

  11. Роман здравствуйте.

    Задача — посчитать количество событий определенной категории. делаю по вашему рецепту: SUM(CASE WHEN Категория событий = «lead_new» THEN 1 ELSE 0 END)

    и получаю 1 вместо 5745 событий

    подскажите, что я делаю не так?

    https://uploads.disquscdn.com/images/bfd19ec9a953a99c6af207c674ea323a172dfc251daf9dd99f090ccebb2347ad.png

    Ответить

    1. таже проблема с подсчетом действий по событию, в гайде что-то не учтено или недосказано.
      выбирая показателем вычисляемый параметр
      SUM(CASE
      WHEN Действие по событию = «tournaments_open» THEN 1
      ELSE 0 END)

      так же получаю единицу

      Ответить

    1. Нужно создать кастомное поле, например:

      CASE
      WHEN Ваш показатель > 0 AND Ваш показатель < 1 THEN '1' WHEN Ваш показатель > 1 AND Ваш показатель < 4 THEN '2-3' ... ELSE '' END

      Ответить

  14. Роман, у меня нубский вопрос при том, что я вроде как нормально пользуюсь 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 упускаю

    Ответить

    1. Привет!
      В примере мы сначала создаем новое вычисляемое поле «Тип страницы:

      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)

      Ответить

      1. Роман, спасибо. Может подскажете как в вашем примере посчитать индекс: кол-во страниц «Каталог» разделить на кол-во страниц «Товар»?

        У меня чуть другой пример, мне надо разделить кол-во одних event action на кол-во других. Т.е. параметр, который для вашего примера надо было создать, здесь по идее уже создан — это и есть event action. Но не пойму как фильтровать для формул значения показателя total events для этого параметра т.е. как в итоге разделить кол-во одних событий на кол-во других.

        Ответить

        1. > как в вашем примере посчитать индекс: кол-во страниц «Каталог» разделить на кол-во страниц «Товар»
          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)

          Ответить

          1. Что-то ругается, если такое подставить в формулу вычисляемого показателя: 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?

          2. Что-то ругается, если такое подставить в формулу вычисляемого показателя: 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?

  15. Роман, у меня нубский вопрос при том, что я вроде как нормально пользуюсь 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 упускаю

    Ответить

  16. Роман, добрый день!
    Спасибо за полезный материал!
    Подскажите, пожалуйста, как решить такую задачу?

    Надо сделать новый вычисяемый параметр. У него должно быть условие:

    Пользоваели (из конкретного сегмента, который ранее был создалв GA) / Конверсии

    Как в GDS применить сегмент к параметру и использовать его в дальнейшем?

    Спасибо!

    Ответить

  17. Спасибо за статью, сохранил.
    Такой вопрос. Надо к стоимости прибавлять НДС но только для Гугла
    Конструкция такого вида не работает
    Case
    WHEN Источник или канал = «yandex / cpc» THEN Стоимость
    WHEN Источник или канал = «google / cpc» THEN Стоимость *1.2
    END
    Как создать такой вариативный вычисляемый показатель ?

    Ответить

    1. Здравствуйте!
      Скорее всего не работает, потому что вы забыли про ELSE.
      Попробуйте так:

      CASE
      WHEN Источник или канал = "google / cpc" THEN Стоимость *1.2
      ELSE Стоимость
      END

      Ответить

    2. Здравствуйте!
      Подскажите, вам удалось решить эту проблему ? Мне гугл пишет следующее:
      В рассчитываемых полях нельзя одновременно указывать показатели (агрегированные значения) и параметры (неагрегированные значения).

      Ответить

      1. Здравствуйте!
        Чтобы не испытывать подобных проблем, лучше всего предварительно выгружайте ваши данные в любую БД или в Google Sheets и стройте дашборд на них.
        В GA есть много особенностей связанных с типами данных, агрегированными значениями и тп, которые затрудняют работу.

        Ответить

  18. Ваш ресурс самый чоткий ресурс по веб-аналитике в рунете!
    Посты, как боженька на душу положил!
    Что ни пост, то огурчик!
    Спасибо товарищ!

    Ответить

      1. Товарищ помогите, направьте!
        Подскажите как провести сравнение в каждой ячейке за предыдущий период, сгруппировать эти ячейки по неделям и отобразить сравнение для каждой недели.
        Сейчас я пытаюсь использовать элемент Сводка, но страница накладывает ограничение в 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 источников и можно смешать. )

        Ответить

  19. Добрый день, Роман!
    Ниже уже спрашивали, к, сожалению ответа не нашел(
    Проблема в том что формула возвращает результат «1»
    SUM (CASE WHEN Источник = «sendpulse» THEN 1 ELSE 0 END)
    Хотя должно быть несколько десятков тысяч…подскажите пожалуйста, в чем может быть причина?

    Ответить

      1. У меня похожая проблема, только чуть сложнее.
        Мне нужно отношение событий с определённым значением к просмотрам страниц.
        Проблема такая же как и у Ростислава.

        Задача выянить сколько страниц было дочитано до конца, т.е. поделить «дочитывания» (событие) на просмотры страниц.

        Пробовал через SUM () и через Count () всегда возвращает «1».

        Можете подсказать как победить GDS в таком случае?

        Ответить

  20. Подскажите, как написать формулу, что если 2*0=0, то тогда значение к примеру 3

    Ответить

  21. Роман, доброго вам времени суток. Может вы сможете помочь, уже весь мозк сломал.
    В отчетах в экселе делаю столбцы с распределением бюджета https://prnt.sc/s0ihmg. Вот не получается у меня реализовать это в Студии. Возможно ваш опыт подскажет возможно ли вообще это реализовать?
    Спасибо!

    Ответить

  22. Роман, приветствую
    Сделал простое вычисляемое поле «Цена клика». Стоимость разделил на клики. Поле считается корректно для каждой строки данных, но при попытке посмотреть срез данных не по дням, а за неделю/месяц, то поле считается не корректно, т.к. агрегация по умолчанию стояла «Сумма». Получается дата студио за выбранный период складывает все «Цены клика». Пробовал агрегацию «Среднее», но вычисляет среднее из значений «Цена клика» и тоже не корректно

    Вопрос: можно ли сделать так, чтоб поле рассчитывалось исходя из параметра выбранной даты? Скрины прилагаю
    https://uploads.disquscdn.com/images/28e4afeaea32146ed02a15a8d4cad5a15f399fa619397648ea70c6f30bff05f6.png
    https://uploads.disquscdn.com/images/c9f9219224a9ecb2e5b067042d15feec3635c4e8953042cb38b8ab14dd9ed78d.png

    Ответить

  23. Роман, добрый день!

    Столкнулась с такой проблемой, есть таблица в Google Sheets с данными в формате продолжительность (чч.мм.сс).

    В гугл дата студио они импортируются в формате текста, как их можно преобразовать или импортировать в числовом формате, тип «Продолжительность (сек)» ?

    Ответить

  24. Доброго дня
    Есть таблица с данными по отказам. Хочу в диаграмму вывести эти показатели с разбивкой на три группы «до12%», «от 12 до 25%» и «более 25%». Делаю такое выражение в новом поле

    CASE
    WHEN Отказы (Гармония) > 0 and Отказы (Гармония) <= 12 then "до 12%" WHEN Отказы (Гармония) > 12 and Отказы (Гармония) < 25 THEN "от 12 до 25%" ELSE "более 25%" END
    Выводится только "до 12%" и "более 25%". В данных присутствуют значения всех трех групп.
    Что не так в выражении?

    Ответить

  25. Добрый день!
    А где можно почитать про использование символов в запросах? Например «=([^&]+)», ^(cpv|cpa|cpp)$») и т.д.

    Ответить

  26. Роман, спасибо за эту статью! Очень интересная и полезная. Еще оч помагла статья про визуализацию воронки )
    Столкнулся с таким моментом. Пытаюсь составить таблицу со старинцами, которые посещали пользователи совершившие транзации.
    создал такое вычисляемое поле
    CASE
    WHEN Revenue > 0
    THEN Unique Page Views
    ELSE 0
    END

    Но в итоге получаю только страницы на которых произошли транзакции. Возможно вы в курсе какую ошибку я допустил?
    И как ее можно исправить )
    https://uploads.disquscdn.com/images/33571f3da0496c55e2ee12750587493ce6530e02d0e89428648e54b2a03845b1.png

    Ответить

  27. Добрый день! Кто-нибудь знает как GDS вычисляет медиану? Мне выдает некорректные данные, в чем может быть причина? Например, есть 19 чисел, медиана должна быть = 0:30:42, но GDS выдает 00:55:16 https://uploads.disquscdn.com/images/adb262139d164303a39fd351a7038c60963441522e3e237d6be8a3df2df07f66.jpg

    Ответить

  28. Подскажите, можно ли решить такую задачку: В столбце «Тип операции» есть значения «Приход» и «Расход» и есть столбец «Сумма операции», можно ли вычленить из столбца «Тип операции» те строки где «Приход» и посчитать их сумму?

    Ответить

    1. Здравствуйте!
      Ваша задача решается при помощи простого CASE, например:

      SUM(CASE WHEN Тип операции = ‘Приход’ THEN Сумма операции ELSE 0 END)

      Ответить

  29. Приветствую. Прошу помощи с задачкой.
    Есть столбец «ID», в нем уникальный номер каждой строки. В столбце «Номер операции» указывается «ID» той строки, к которой привязывается эта операция. То-есть есть так сказать материнская строка со своим » ID» и в ней с столбце «Номер операции» пусто. И есть строки, в которых тоже свои «ID», но в столбце «Номер операции» указан «ID» той строки которой привязана данная операция. Также есть столбец «Тип операции» и «Сумма операции».
    ВОПРОС: Как посчитать сумму всех «Сумма операции», по » ID» материнской сделки и по определенному «типу операции», например-Приход? Спасибо за ответ!

    Ответить

  30. Здравствуйте!
    Спасибо за статью.

    Помогите пожалуйста, решить проблему.
    У меня дублируются кампании в таблице
    _Poisk__Msk_Pechat_mobayl_id54170634
    _Poisk__Msk_Pechat_desktop_id52144687
    54170634
    {campaign_name_lat}_id52144687
    {campaign_name_lat}_id54170634

    Одни и те же кампании, отображаются тремя разными способами.
    Как мне задать такое условие, чтобы дата студио понимал по номерам кампаний, что это одно и то же и выводил только на транслите?

    Ответить

  31. Добрый день, огромное спасибо за статью! В ней нашел очень много ответов.
    Но, к сожалению, не представляю решения для текущей задачи.

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

    Условно:
    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 пишет «нет данных»

    Подскажите, как создать функцию, которая бы заставляла показывать данные на последнее воскресенье периода?

    Огромное спасибо

    Ответить

  32. Добрый день. Подскажите, как посчитать долю от итогового значения? Например, посетители с разных видов устройств / все посетители. Хочу получить в таблице столбец с % напротив каждого вида устройств. Спасибо!

    Ответить

  33. Подскажите, перелопатил все по АБС анализу, ничего не нашел в сети под data studio. Одна из формул, та, что в экселе C2/СУММ($C$2:$C$171). А вот как ее через поле реализовать в studio. Если указывать к примеру «Выручка/SUM(Выручка)», то data studio ругается «В рассчитываемых полях нельзя одновременно указывать показатели (агрегированные значения) и параметры (неагрегированные значения). Проверьте типы значений в формуле.» Можете подсказать как обойти этот конфуз?

    Ответить

    1. Здравствуйте!
      Используйте в рассчитываемых полях агрегированные данные.

      Ответить

      1. Такая же проблема «В рассчитываемых полях нельзя одновременно указывать показатели (агрегированные значения) и параметры (неагрегированные значения). Проверьте типы значений в формуле. » а пример можно??? не получается посчитать SUM всего событий по конкретному ярлыку. Похоже на какой-то баг Datastudio раньше вреде все считалось

        Ответить
Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *