Марафон DataLens → День 05

Вычисляем Метрики

ДЕНЬ 05

Как научиться думать на языке формул

Научимся визуализировать данные из Метрики, чтобы освоить еще один источник данных и на его примере разобрать работу вычисляемых полей и важную роль функций DataLens. Всем любителям веб-аналитики и экономистов юнит-экономик будет интересно оценить легкость и доступность подключения, хранения и обработки веб данных. Рассмотрим несколько вариантов доступа, пообсуждаем дефолтные дашборды, соберем свои, чтобы провести наш ежедневный анализ.

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

Теория дня

... функции, типы данных и вычисляемые поля

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

Функции в DataLens можно объединить в смысловые группы.

По типам данных, с которыми работает функция:

Функции даты и времени
Географические функции
Строковые функции

По назначению:

Агрегатные функции
Логические функции
Математические функции
Операторы
Функции преобразования типов
Оконные функции

Полная справка по функциям доступна в документации DataLens, а также – внутри окна создания формулы

У каждой функции есть свой синтаксис. Синтаксис описывает аргументы, которая функция принимает. Аргументами могут быть как поля определенного типа из источника данных (и исходные и расчетные), так и различные константы — текстовые и числовые аргументы.

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

Количество аргументов и сложность функции может сильно варьироваться

К наиболее полезным и часто используемым функциям можно отнести Строковые функции, Функции даты и времени и Логические функции. Эти разделы позволяют нам прописывать свои алгоритмы расчета новых вычисляемых полей, создавать группировки в данных, приводить данные к новым измерениям (например, если агрегировать данные поквартально), задавать логику расчета и условия логических проверок.

Особенно полезные строковые функции


SPLIT – разделение строки на подстроки
REPLACE – замена части строки
LEN – возврат длины строки в кол-ве символов
CONTAINS – логическая проверка «содержит или нет»
LOWER / UPPER – изменение регистра строк
STARTSWITH / ENDSWITH – логическая проверка «начинается или заканчивается на…»
LEFT / RIGHT – возврат заданного количества символов слева или справа
TRIM / LTRIM / RTRIM – отсечение висящих пробелов

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

Примеры функций по работе с текстом


//проверка наличия текста в строке
CONTAINS("RU0891923", "RU") = TRUE

//получение длины строки
LEN("Computer") = 8

//замена указанных символов в подстроке
REPLACE("350 RUB", "RUB", "USD") = "350 USD"

//разделение строки на подстроки по разделителю
SPLIT("RU-MOW-SVO", "-", 1) = "RU"

//приведение строки к верхнему регистру (обычно используется для упрощения дальнейшего сравнения)
UPPER("Карл у Клары") = "КАРЛ У КЛАРЫ"

Особенно полезные функции даты и времени


DATEADD – добавление (или вычитание) определенного значения даты от указанной
DATEPART – возврат части даты в виде целого числа
DATETRUNC – возврат даты, округленной по аргументу unit
NOW/TODAY – возврат текущего времени с датой или просто текущей даты

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

«year» — год,
«month» — месяц,
«day» — день,
«hour» — час,
«minute» — минута,
«second» — секунда.

Таким образом, если вы хотите прибавить к определенной дате некое количество дней, то на второй месте в функции вы запишите значение «day». Если захотите прибавить 9 месяцев, вместо 9 дней, то запишите значение «month».

Пример разбора синтаксиса функции DATEADD, которая прибавляет определенную величину времени (в годах, месяцах, днях и т.п.) к указанной дате. В справочнике

Используйте функции по работе с датой и временем, когда вы хотите задать более сложные условия расчета показателей. Например, если вы хотите взять продажи, которые были сделаны только за выходные, то вы возьмете функцию DATEPART или DAYOFWEEK, в которой зададите, что день недели равен 6 или 7 (субботе или воскресенью, соответственно).

Примеры функций по работе с датой и временем


//добавление временного промежутка – 6 лет
DATEADD(#2018-01-12#, "year", 6) = #2024-01-12#

//получение значения даты – номера дня в указанной дате
DATEPART(#2019-01-23#, "day") = 23

//получение значения даты – дня недели в указанной дате. 4 день недели это четверг
DATEPART(#1971-01-14 01:02:03#, "dayofweek") = 4

Особенно полезные логические функции


IF ... THEN ... ELSE – самое популярное логическое выражение на проверку условий
CASE – быстрый и удобный способ для перебора значений и возврата результатов
ISNULL/IFNULL – проверка на NULL – отсутствие или присутствие значения в ячейке
ZN – возвращение указанного выражения, если оно не NULL

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

Примеры логический функций


//возврат полного названия страны на основе его кода
CASE [country] 
    WHEN "BY" THEN "Белоруссия"
    WHEN "KZ" THEN "Казахстан"
    WHEN "RU" THEN "Россия"
    WHEN "TR" THEN "Турция"
    WHEN "UZ" THEN "Узбекистан"
    ELSE "Другая страна"
END


//проверка величины значения
IF
    [Выручка] > 100
        THEN "Высокая"
    ELSEIF [Выручка] > 25
        THEN "Средняя"
    ELSE "Низкая"
END

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

Если обычные функции (агрегатные), вычисляются на основе полного списка значений, которые формируются в текущем чарте, то в оконных функциях вы можете указать параметры группировки с помощью ключевых слов WITHIN, AMONG, TOTAL.

→ Подробнее про оконные функции здесь

Примеры оконных функций


//Топ по продажам внутри каждой категории продаж
RANK([Продажи] WITHIN [Категория])

//Доля категории от общего значения за год
[Продажи] / SUM([Продажи] WITHIN [Год]) * 100


Для некоторых функций обязательна сортировка измерений на чарте. Подробнее – в справке
//Накопленный итог
RSUM([Продажи])

//Скользящее среднее за последние 2 дня
MAVG([Продажи],2)

Кстати, часть агрегатных функций также могут быть использованы как оконные: SUM, SUM_IF, AVG, AVG_IF, MAX, MIN, COUNT, COUNT_IF.

Например, написав MAX([Выручка] WITHIN [Дата]) – вы получите максимальную выручку на каждую дату.

В интерфейсе датасета и чарта можно установить правило агрегации для показателя. Однако там доступны только базовые функции (SUM, AVG, MAX, MIN, COUNT). Более сложную агрегацию можно настроить через формулы

...Еще раз про типы данных

DataLens работает с различными источниками данных, в которых типы данных полей могут отличаться. Например, в источниках Clickhouse дата хранится как поле datetime, а в источниках PostreSQL — timestamp. Для более эффективной работы с данными, DataLens конвертирует типы данных полей источника в собственные типы, это помогает быстрее и удобнее работать с данными из разных источников. Подробнее про соответствие типов данных вы можете прочитать здесь.

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

Рассмотрим типы данных подробнее.

Геоточка +
Геополигон

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

Геоточка – это точка координат, которая состоит из значений широты и долготы. В DataLens задается с помощью функции GEOPOINT.
Функция принимает на вход типы данных Строка, Геоточка, или два значения с типом Дробное число или Строка – координаты.

Геополигон – несколько точек координат, которые описывают полигон на карте. В DataLens задается с помощью функции GEOPOLYGON.


GEOPOINT("[55.7912,37.6872]")
GEOPOINT("[55.8538,37.6312]")

Дата +
Дата и время

При использовании в формулах необходимо задавать дату с помощью знака решетки #. Например DATETRUNC(#2018-07-12#, "year", 5).

Вы можете привести исходный тип данных в тип Дата с помощью функций DATE и DATE_PARSE.


#2018-01-18#
DATETRUNC(#2018-07-12#, "year", 5)
DATEADD(#2018-01-12#, "day", 6)

#2018-01-12 01:08:03#
DATEADD(#2018-01-12 01:02:03#, "second", 6)
DATETRUNC(#2018-07-12 11:07:13#, "month", 4)

Дробное число +
Целое число

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

Вы можете привести исходный тип данных в тип Дробное число с помощью функции FLOAT.
Чтобы привести в тип Целое число, используйте функцию INT.


1.47113
0.62024
FLOAT("34.567")

Логический тип

Принимает одно из двух значений – TRUE или FALSE.
Вы можете привести исходный тип данных в тип Логический с помощью функции BOOL.

Строковый тип

Строка с текстом. Задается с помощью символов одиночных или двойных кавычек.

В случае использования одного вида кавычек, второй вид можно свободно использовать в этой строке без экранирования.
Например, 'Да пребудут с вами "Звездные Войны". '

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

\n - перенос строки (LF);
\r - возврат каретки (CR);
\t - табуляция;
\" - двойная кавычка;
\' - одинарная кавычка;
\\ - обратная косая черта.

Вы можете привести исходный тип данных в тип Строка с помощью функции STR.

Подключение к Яндекс.Метрике

Сегодня мы также посмотрим на сценарий подключения DataLens к Яндекс.Метрике – системе для аналитики веб-сайтов.

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

Далее возможно множество сценариев построения кастомной аналитики и отчетов:

Собрать дашборд только по нужным показателям, разделам сайта (например, если вы запускаете промо с новым лендингом и хотите отслеживать именно его и связанные с ним страницы на одном дашборде)
Предоставить доступ внешним партнерам к части данных вашей метрики
Построить дашборд на базе нескольких разных счетчиков Яндекс.Метрики
Обогатить данные Яндекс.Метрики дополнительными источниками для построения сквозной аналитики (данные из рекламных кабинетов, CRM, продаж

В простых случаях можно обойтись обычным прямым подключением к Метрике. Для более сложных задач будет нужен экспорт в облачный ClickHouse. Ну а для полноценной сквозной аналитики потребуется более глубокая экспертиза по работе с сырыми данными на уровне Clickhouse или помощь квалифицированных партнеров-интеграторов.

Вы можете подключиться к своему имеющемуся счетчику Яндекс.Метрики (об этом напишем ниже), или же использовать демонстрационный дашборд в DataLens. Чтобы открыть демо-дашборд, перейдите в Каталог DataLens в раздел Дашборды и выберите Metrica Live Demo Dashboard. #

Чтобы подключиться к своему счетчику, создайте новое подключение – и выберите тип подключения Metrica. Введите имя подключения и Получите токен. Если вы первый раз создаете подключение к счетчику Яндекс.Метрики, то предоставьте сервису необходимые разрешения. Затем, выберите нужный счетчик из списка.

DataLens позволяет подключаться по прямому доступу или через Logs API. Прямой доступ имеет ряд ограничений по доступным в DataLens функциям и производительности. Подключение через Logs API позволяет вам предварительно выгрузить данные в кластер БД Clickhouse и работать с Clickhouse без ограничений. В базовом сценарии – подключайтесь по прямому доступу, так как он понятный и доступный для первого использования.

→ Подробнее про то, как создать кластер Clickhouse можно прочитать здесь
→ Подробнее про подключение к Logs API и заливку данных в кластер здесь

Включите опцию Автоматически создать дашборд, чарты и датасет над подключением. Даже если вы хотите создать свой собственный дашборд с нуля, получение уже подготовленных и описанных двух датасетов на основе ваших данных из Метрики – удобный шаг!

Практика дня

... попробуем посчитать

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

Для тех, кто хочет подробнее изучить формулы и расчеты – предлагаем ознакомиться с кейсом auto.ru!

Описание задачи

Проведем небольшое исследование списка объявлений на сайте по продаже и покупке автомобилей auto.ru. На этих данных как раз и посмотрим на работу разных вычисляемых полей.

Вам нужно будет составить аналитический дашборд анализа текущих предложений на сайте auto.ru

... и при помощи чартов, ответить на вопросы:

какие цвета автомобилей пользуются популярностью? какие встречаются реже?
какой средний возраст продаваемых автомобилей?
как меняется цена автомобиля в зависимости от возраста и от пробега машины?

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

На экране настройки подключения введите следующие данные:


Имя хоста: c-c9qi65umlg1u18vrgdkm.rw.mdb.yandexcloud.net
Порт: 8443

Пользователь: auto_ru_ro
Пароль: 3pAhdtQNO2uBkzQuhQUlJpoL6bbPf1+u

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

В некоторых публикациях может не быть даты окончания (finish date), это значит, что объявление все еще активно.

Решение от эксперта

Роман Бунин

Руководитель команды визуализации данных Yandex.Go

Этот набор данных от auto.ru было очень интересно анализировать и тут отлично пригодились расчетные функции которые есть в DataLens.

Сначала я решил удовлетворить своё любопытство про цвета автомобилей. Было интересно, как менялся процент разного цвета по году выпуска. В датасете были данные технических названий цветов, чтобы превратить их в человекочитаемые, я воспользовался логической функцией CASE.

Цвет авто


CASE [color]
WHEN "FAFBFB" THEN "Белый"
WHEN "040001" THEN "Черный"
WHEN "CACECB" THEN "Серебро"
WHEN "97948F" THEN "Серый"
WHEN "0000CC" THEN "Синий"
WHEN "EE1D19" THEN "Красный"
WHEN "007F00" THEN "Зеленый"
WHEN "200204" THEN "Черный"
WHEN "C49648" THEN "Бежевый"
WHEN "22A0F8" THEN "Голубой"
WHEN "4A2197" THEN "Фиолетовый"
WHEN "660099" THEN "Баклажан"
WHEN "DEA522" THEN "Желтый"
WHEN "FF8649" THEN "Оранжевый"
WHEN "FFD600" THEN "Желтый"
WHEN "FFC0CB" THEN "Розовый"
END

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

Дата выпуска


DATE(STR([year]) + "-01-01")

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

От более развлекательной задачи перешёл к более практичной. Скоро продавать автомобиль и стало интересно на каком году жизни это сделать лучше всего, не потеряв на этом много денег. Для этого, я посчитал возраст автомобиля. Сделал это через функцию TODAY(), для того, чтобы возраст автомобиля автоматически пересчитался, когда наступит новый год.

Возраст авто


YEAR(TODAY())-[year]

Построил график средней цены от возраста и сразу понял, что раритет начинается от 40 лет... :)

Но, помимо возраста, на стоимость также влияет и пробег. Чтобы построить зависимость от пробега, я округлил значения пробега с помощью формулы, ограничил пробег в фильтре меньше 500 тысяч и получил следующий график:

Округленный пробег


ROUND([mileage]/10000)*10000

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

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

% от максимальной стоимости


AVG([price_rub]) / MAX(AVG([price_rub]) AMONG [Возраст авто]) *100

Теперь, линии на графике выходят в большинстве случаев из 100% в первый год и спускаются до 20-30% от стоимости через 15-20 лет. Но интересно, что для части марок максимальная стоимость приходится не на первый год после выпуска, а на второй. А также отлично проявилась особенность, что быстрее всего дешевеет Opel, а медленнее всего – Hyundai и Toyota.

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

Используя этот дашборд, я сразу понял, что удачно купил свой Nissan Note по цене чуть ниже рыночной и продать его лучше через пару лет, иначе потеряю ещё 15-20% стоимости.

Все обсуждения теории и практики дня – в Телеграме!

Ищите по никнейму @YandexDataLens или переходите по кнопке ниже

Присоединиться

Вдохновение

... чтобы научиться понимать психологию

Для поиска вдохновения в днях нашего Марафона мы решили написать экспертам в области дизайна, работы с данными и их визуализации и услышать ценное мнение по какой-либо теме. Сегодня говорим про пользовательский опыт при разработке.

Михаил Хананашвили

UX Lead SberAutoTech

Человек воспринимает взаимодействие с интерфейсом как некий диалог, где он задаёт вопрос, нажимая или глядя в экран, а интерфейс ему отвечает. Будь то большие карты с разложенными красиво данными или самый обычный интерфейс сайта. Всегда есть потребность, контекст и цель. Создавая качественную карту с данными, мы не избавляемся от необходимости сверяться с потребностями пользователя и его ожиданиями. Изучи, сделай, протестируй — простой и понятный принцип избавления от собственных галлюцинаций.

«Дизайн привычных вещей»

Книга Дональда Нормана о вещах, которые нас окружают, и том, почему они были созданы именно такими. Дональд Норман по полочкам раскладывает десятки предметов, которыми мы пользуемся ежедневно, интересно и аргументированно рассуждая об ошибках, совершенных при их проектировании. Красивое – это далеко не всегда еще и удобное...

«Психбольница в руках пациентов»

...Наши телефоны, фотокамеры, автомобили – все, что нас окружает, автоматизируются, программируются, создаются людьми, которые, стремясь получить выгоду от применения микросхем, уклонились от своей прямой обязанности – делать эти продукты простыми в применении. Алан Купер предлагает решение проблемы: программированию должно предшествовать проектирование

Лучшие ресурсы в одном месте

Материалы от Ксении Стерниной по теме UX для разных уровней подготовки: для тех, кто только хочет начать, для джуниор-исследователей, для миддл-исследователей и для супер-UX-джедаев.

Предлагаем вам также посмотреть наш видео-разговор с Михаилом Хананашвили по теме UX-принципов работы с дашбордами

Почему возвращаются старые формы и стили в информационный дизайн? Как понять какой дизайн подойдет Заказчику? Как проверить интерфейс дашборда с точки зрения правильного пользовательского опыта? Как правильно тестировать свои продукты или инфоинтерфейсы? Разберемся в деталях.

Рома Колеченков позвонит:

Мы создаем DataLens с целью сделать аналитику популярной и доступной для всех категорий пользователей. Марафон для нашей команды — возможность улучшить продукт на основе ваших впечатлений.

Поделитесь своим мнением или идеей, чтобы помочь с развитием

Написать

Образовательный онлайн Марафон DataYoga и Yandex DataLens
thejump@datayoga.ru

2020