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

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

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

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

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

Кейс Refocus: данные и путь клиента

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

В нашем случае мэтчинг был важнее всего по трем источникам из тринадцати:

• amoCRM, где фиксируется весь клиентский путь студента;
• Discord, где проходило сопровождение студентов;
• Thinkific, сама образовательная платформа с курсами.

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

В Discord и Thinkific данные попадали напрямую, от студентов при регистрации в системах, а затем подтягивались в amoCRM. Основные причины несовпадения клиентских данных как у Refocus, так и в похожих случаях — человеческий фактор (опечатки), наличие у людей более чем одного телефона или адреса почты и ограничения самих платформ, с которых приходят данные: разный заданный формат полей и их количество.

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

Задача и поиск решения

Данные в Refocus мы подгружали в хранилище в BigQuery напрямую из интересующих нас источников (рекламных кабинетов, LMS и т. д.), используя Python. В дальнейшем на этих данных строились дашборды в Tableau.

Обнаружить проблему несложно — при создании хранилища и дальнейшей выгрузке данных из него мы в любом случае чистим датасет от дубликатов и несовпадений.

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

• имя — да, люди иногда вводят разные вариации ФИО (Юлия, Юля и Бля — на деле один человек!);
• телефон — с кодом страны или без, с пробелами, дефисами или слитно;
• электронная почта — длинные строки сложного формата, в которых легко опечататься.

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

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

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

Вообще, в такой ситуации возможны несколько вариантов. Можно бесконечно править скрипты мэтчинга, учитывая новые и новые случаи и создавая костыли. А можно, например, настроить алерты в оркестраторе процессов (в нашем случае  — Airflow), которые позволят моментально узнавать о появившемся несовпадении и объединять “потерянные” клиентские сущности по паре за раз. Но это все еще неполная автоматизация, и она только ускоряет, а не упрощает процесс.

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

Реализация решения и рабочий процесс

Чтобы понять масштаб проблемы, мы начали с того, что создали таблицы несовпадающих персональных данных. Для этого мы использовали скрипты на Python. Эти скрипты объединяли данные из разных источников и создавали из них большую сводную таблицу. Для того, чтобы свести данные о студенте в одну сущность, использовался мэтчинг по адресу электронной почты. Мы попробовали мэтчить по имени, фамилии, телефону (который сначала надо было привести к одному формату!) и почте, и именно последний вариант показал самую высокую точность. Возможно, дело в том, что из всех данных почта имеет самый однородный формат, поэтому остается учитывать только опечатки.

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

WITH snapshot_ AS (
      SELECT DISTINCT s.*,
        IFNULL(ae.name, ap.name) as contact_name,
        ap.phone, ae.email,
        split(replace(trim(lower(ae.email)),' ',''),'@')[OFFSET(0)] as email_first_part,
        ai.thinkific_id, ai.intercom_id, ac.student_id
      FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,
        FROM `Differture.amocrm_leads_snapshot`
      ) s
      LEFT JOIN (
        SELECT DISTINCT lead_id, contact_id, name, email,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num1_
        FROM `Differture.amo_emails`
      ) ae using(lead_id)
      LEFT JOIN (
        SELECT DISTINCT lead_id, contact_id, name, phone,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num2_
        FROM `Differture.amo_phones`
      ) ap using(lead_id)
      LEFT JOIN `Differture.amo_contact_thinkific_intercom_match` ai using(lead_id)
      LEFT JOIN `Differture.AmoContacts` ac on cast(ae.contact_id as string)=ac.amo_id
      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null) and (num2_=1 or num2_ is null)
        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'
    )

Как можно заметить, идея сквозного идентификатора здесь уже присутствует — фигурирует student_id. На самом деле, в этой версии скрипта это графа из AmoContacts — таблицы, в которой хранятся только данные из amoCRM. Никаких джойнов по student_id пока не происходит. А происходят по email_first_part, адресу почты до символа @:

select distinct * from th_amo_ds_rf
    left join calendly ce using(email_first_part)
    left join typeform_live tfl on email_first_part=tf_email_first_part
    left join typeform tf using(email_first_part)
    left join csat using(email_first_part)

Первым шагом по практическому введению идентификатора была таблица students_main_info, созданная в BigQuery in-house специалистом Refocus. К сожалению, у нас нет доступа к коду, который использовался для присвоения идентификатора. Зато мы можем показать вид этой таблицы:

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

При этом поле student_id использовалось пока не везде; также использовались другие поля этой таблицы — например, thinkific_student_id или discord_user_id.

После выгрузки и мэтчинга данных с помощью students_main_info студентов, которые потерялись при объединении, стало меньше, чем при первой схеме мэтчинга. Так мы убедились, что движемся в верном направлении. Тем не менее, использование одной таблицы, которая содержит больше десятка полей обо всех имеющихся персональных данных, не очень эффективно. Данные в ней уже обработаны скриптом специалиста Refocus, и если надо сверить их с сырыми источниками или ввести новый критерий отслеживания, все придется менять на бэкенде.

Что получилось в итоге

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

Вот так выглядела схема соотношения этих таблиц:

А вот так выглядела основная таблица Students:

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

Остальные таблицы выглядели похоже: всегда было поле с идентификатором и информация о какой-то характеристике студента — когорта, курс, роль в дискорде и так далее.

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

Сначала он отбирал собранные нами данные из amoCRM (amocrm_leads_snapshot) и объединял их с контактной информацией клиентов. Затем в таблицу добавлялось поле student_id и отбирались данные, которые понадобятся нам дальше.

WITH snapshot_ AS (
      SELECT DISTINCT s.*,
        ac.name as contact_name, ac.phone, ac.email,
        split(replace(trim(lower(ac.email)),' ',''),'@')[OFFSET(0)] as email_first_part,
        ac.intercom_id, ac.student_id
      FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,
        FROM `Differture.amocrm_leads_snapshot`
      ) s
      LEFT JOIN (
        select cast(al.amo_id as INT64) as lead_id, cast(ac.amo_id as INT64) as contact_id,
          ac.name, emails as email, phone, student_id, ic.intercom_id,
          ROW_NUMBER() OVER(PARTITION BY al.amo_id ORDER BY ac.amo_id) as num1_
        from `Differture.AmoContacts` ac
        left join `Differture.AmoLeads` al on al.amo_contact_id=ac.id
        left join `Differture.IntercomContacts` ic using(student_id)
        , unnest(ac.emails) emails
      ) ac using(lead_id)
      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null)
        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'
    )

Теперь при создании общей таблицы о возвратах с данными из amo, Thinkific и Discord объединение проходило через student_id:

th_amo_ds_rf as (
      select distinct * except (channel_id, channel),
        ifnull(channel_id, 'Not in discord') as channel_id,
        ifnull(channel, 'Not in discord') as channel
      from thinkific_amo_refunds
      full outer join discord using(student_id)
    )

Когда объединенные таблицы данных студентов были созданы, получить таблицы несовпадений можно было простой строкой кода в Tableau:

Пустое значение поля student_id означает, что мэтча не случилось — где-то информация расходилась слишком сильно и не подтянулась в таблицы с идентификатором. Раньше, до введения идентификатора, поиск был таким же, но обращался к полям почты, телефона или имени-фамилии.

Ниже можно увидеть таблицу, где данные из Thinkific не совпадали с amoCRM после перехода на Student ID. В этом случае студент есть в LMS, значит, на курсе учится — но его либо нет в системе учета, либо данные в ней разнятся с LMS.

А вот таблица, где данные из Discord не совпадали с amoCRM. Все так же, как выше — студент есть в чатах сопровождения, но не ищется по своим данным в amoCRM.

Оба скриншота показывают количество несовпадений примерно за месяц. Как видно по этим таблицам, количество несовпадений уменьшилось с 80-90 до пары десятков — примерно на 75%. Это позволило сократить количество перезапусков кода выгрузки вручную и уменьшить затраты времени и технических ресурсов на поддержание системы.

Выводы

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

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

Дело в том, что в современных BI-тулах, которые мы используем — в частности, в популярном Tableau — возможности кастомизации гораздо шире, чем может себе представить сотрудник, мигрирующий с excel-таблиц. Часто пользователь просто не знает, каким вообще может быть дашборд и как составить ТЗ.

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

Именно такая ситуация сложилась у нас в процессе работы с образовательным стартапом Refocus.

Один из главных их дашбордов — обзор SLA (Service Level Agreement). Его пользователи — руководители отдела продаж и сейлз-менеджеры. Именно здесь их интересует прежде всего скорость обработки входящих лидов в зависимости от ряда факторов, от источника лида до региона.

Нормативное значение этого показателя для Refocus — 15 минут, значит, там, где он выше, есть потенциал для оптимизации процессов и роста выручки.

Мы работаем над дашбордами итеративно: интервью с пользователем — прототип — фидбэк — следующая версия — фидбэк, и так далее. Но в случае с SLA после утверждения финальной версии остались нерешенные аналитиками функциональные проблемы.

У заказчика было желание оптимизировать дашборды и бюджет на это, так что мы предложили общую переверстку и подключили к проекту датавиз-эксперта.

Версия 1: с чем мы работаем

Первая версия SLA-дашборда, которой некоторое время пользовался клиент, выглядела так:

В фокусе три графика:

1. Скаттерплот по времени ответа с момента получения лида.
2. Гистограмма по медианному времени ответа.
3. Гистограмма по средней длительности первого звонка с лидом.

Справа — 15 фильтров, от региона и гранулярности до имени сотрудника, работавшего с лидом.

Сверху — много текста, поясняющего тонкости работы с фильтрами по времени.

Задача

Представьте, что ваш технический бэкграунд испарился, и вы на месте пользователя: о чем вам говорят эти графики? Сколько кликов вам нужно совершить, чтобы оценить перформанс подчиненных в вашем отделе? Чтобы сравнить SLA для лидов из разных источников? Ясен ли текст-легенда?

А главное — есть ли у вас понимание, как сделать понятнее и что сказать исполнителю проекта?

У аналитиков в этой области может быть слепая зона — нам-то на картине выше все понятно. А в прямые задачи датавиз-спеца входит проинтерпретировать запрос пользователя в контексте существующего функционала и создать визуализацию на стыке предпочтений заказчика и здравого смысла.

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

Проблемы этого дашборда:

• Основной график — скаттерплот с SLA для всех лидов — выстроен на логарифмической шкале по оси Y без возможности переключиться на линейную. Есть сомнения, что все сейлз-менеджеры, глядя на дашборд, понимают, как работает логарифмическая шкала.
• При выборе гранулярности по неделям масштаб позволяет показать распределение точек на оси X по дням — но они распределены случайно с помощью функции RANDOM().
• Ось Y не синхронизирована между графиками, поэтому сложно оценить связь всех трех значений.
• Невозможно сравнить медианный SLA по следующим параметрам: курс, источник лида, команда, сейлз-менеджер — все они расположены в фильтрах. Сравнение требует скриншотов разных отображений или нескольких вкладок с одним дашбордом.

Установленные причины:

• Логарифмическая шкала: заказчик, который являлся только одним из юзеров дашборда, попросил реализовать логарифмическую шкалу, чтобы смотреть на значения SLA, близкие к нулю. Видимо, это было релевантно для целей именно его отдела.
• Рандомное распределение по оси X: попытка сделать график проще и красивее, потому что скаттерплот уже перегружен информацией, и оценить распределение значений внутри недели — не приоритет.
• Неэффективные фильтры: до ввода дашборда заказчик не знал, какие именно сравнения окажутся ему полезны.

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

Решение

Наш датавиз-специалист рисовал макеты карандашом на бумаге, а после утверждения переносил в Tableau: аналитический функционал и UX-дизайн для готового дашборда можно оптимизировать и без специальных тулов. Здесь хорошо ориентироваться на заказчика: если ему приятнее смотреть на файл в Figma — замечательно. Все понятно и на бумаге — отлично.

Предложенные изменения:

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

Версия 2: рабочая версия после переверстки

Что поменялось:

• В левой части дашборда появилась таблица-фильтр по командам и сейлз-менеджерам. По ней можно оценить показатели работы отдельных сотрудников и отделов и получить общее представление о том, как обстоят дела с SLA в компании — а именно это изначальное назначение дашборда.
• В центральной части дашборда остался скаттерплот с SLA по всем лидам, только теперь формат отображения оси (линейная или логарифмическая) можно переключить.
• Фильтры курсов, источников лидов и категорий превратились в кликабельные бар-чарты в правой части экрана. Они позволяют сразу визуально оценить медианный SLA по этим параметрам.
• Все глобальные фильтры по категориям, которые нет необходимости сравнивать между собой, оказались в верхней части дашборда, там же — фильтр по дате. Теперь пространство экрана организовано эффективнее.

Фидбэк:

• Потерялся график по средней длительности первого звонка.
• На центральном графике исчезли отсечки с медианным SLA.
• Не подписаны столбцы в таблице слева.
• Центральный скаттерплот очень долго загружается при первом открытии дашборда, так как Tableau пытается вывести все лиды из датасета — по умолчанию ни один фильтр не применяется и никак не ограничивает количество информации для отображения.
• Необходимы мелкие правки по оформлению и тултипам.

Версия 3: доработки и брендирование

Что поменялось:

• В таблице слева появились подписи столбцов — теперь половина информации о работе отделов понятна при первом взгляде на дашборд.
• Центральный скаттерплот теперь отображается только после выбора фильтра по команде или конкретному сейлзу — это снижает нагрузку на хранилище и сильно ускоряет загрузку графика.
• Добавились отсечки с медианным SLA за конкретный период (месяц, неделю или день в зависимости от выбранной гранулярности).
• Вернулся график с длительностью первого звонка.
• Ось времени всех трех графиков теперь синхронизирована — не нужно сверять единицы измерения, чтобы выявить общие тренды.

Когда функционал доработан и утвержден, вводится брендирование по корпоративным гайдлайнам. Они у Refocus выглядели так:

Обзор функционала

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

Так выглядит финальный дашборд при открытии, пока не выбран ни один фильтр:

А так, если выбрать сейлз-менеджера из таблицы слева:

Использование фильтров справа (например, отобразить только лидов, пришедших с вебинаров):

Тултипы со ссылкой на первоисточник на скаттерплоте с лидами (время в них отображается в минутах при выборе любой шкалы):

Наконец, переключение между линейной и логарифмической шкалами:

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

В марте мы опубликовали исследование «Python и тексты нового альбома Земфиры: анализируем суть песен», в котором при помощи Word2Vec-модели проанализировали близость песен альбома «бордерлайн» и получили самые близкие слова по духу альбома — ими оказались «пламень», «гореть», «тоска», «печаль», «сердце», «солнце» и другие.

Мы продолжили работу над альбомами Земфиры и проанализировали семь из них, а затем результаты собрали в один дашборд и опубликовали его в Tableau Public. Посмотрите, что получилось.

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

Coinkeeper — кроссплатформенное приложение для учёта финансов. Внутри можно выпустить виртуальную банковскую карту Visa с бесплатным годовым обслуживанием, которая будет присылать уведомления, если вы тратите больше, чем запланировали. Помимо уведомлений, приложение ведёт историю трат и позволяет выгрузить сводный отчёт в формате csv. Данные, которое выгружает приложение ещё не готовы к анализу и выглядят так:

Азат Шарипов сделал скрипт обработки данных в пригодный для Tableau вид и подготовил Tableau Public книгу, а Рома Бунин в рамках своего проекта «Переверстка» переработал дашборд.

Мы решили тоже поучаствовать, и с нашей стороны Елизавета Мазурова сделала чат-бота.

Чат-бот крутой! Помимо того, что он может как и прежде отдавать обратно .csv-файл, он позволяет автоматизировать рутину по обновлению отчета через Google-таблицы. Как, наверное, многие помнят, Tableau Public может работать на гугл-таблицах или csv файлах, но не разрешает подключение к данным. Бот умный: он создаст за вас гугл-таблицу и когда вы повторно отправите ему новый файл обновит ее.

Использование бота

Перейдите в диалог с ботом и введите команду /start — в ответе бот расскажет немного о себе. Для продолжения работы нажмите на кнопку «Начать».

Сразу после можно отправить csv-файл, выгруженный из Coinkeeper:

Выберите тип файла — csv или таблицу в Google Spreadsheets.

В случае выбора csv-файла бот пришлёт его:

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

Затем бот пришлёт ссылку на файл:

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

Как-то раз на просторах YouTube мы нашли вот такое видео с гайдом по Radial Pie в Tableau:

Нам очень понравилась реализация — диаграмма сильно напоминает кольца активности Apple Watch. Но, к сожалению, по задумке графика кольца останавливаются на 270 градусах. Показываем, как сделать максимально приближенную к кольцам активности реализацию.

Кольца активности в Apple Watch

Подготовка данных

Данная визуализация является весьма спорной в контексте бизнес-дашбордов

Загрузим датасорс в Tableau. Наши кольца — это круги из 360 точек, и для каждой нам нужно своё наблюдение. Это легко реализовать при помощи Bins: сначала перетянем файл под поле с этим же файлом, чтобы объединить датасет с самим собой. В результате датасет должен «удвоиться» и появится новое поле с наименованием файла.

Создадим новое вычисляемое поле и назовем его Path.

Затем перейдём на график. Кликнем правой кнопкой мыши по Path из раздела Measures и создадим из этого поля Bins. Size of bins установим на единицу:

Создадим новое вычисляемое поле Index:

И поле Percentage, которое отобразит, насколько выполнены цели. Если достижение по цели будет больше самой цели, мы отобразим 1, чтобы не появлялись значения больше единицы.

Теперь создаём следующие меры:

wc_start — мера начальной координаты каждого кольца. Она считается по полю Order, соответственно, у Stand Order равен 1, а значит начинаться это кольцо будет раньше всех, в точке 1 по OY. У кольца Exercise Order равен 2, оно будет в середине. У Move Order равен 3 — это кольцо будет внешним и начнётся в точке 3.

percentage_label — мера для Label, в которой записано процентное отношение достижения по цели к самой цели:

Y2 — вспомогательная мера для начальных точек колец:

Наконец, финальные поля X и Y. Если значение меньше 360, мы описываем при помощи синуса внутреннюю линию кольца, если больше — то внешнюю линию, иначе — острие, на котором кончается кольцо. Формула вычисления Y аналогична X, но считаем не синус, а косинус.

Визуализация

Измерение Path (bin) перетянем в поле Detail, X — в Columns, а Y — в Rows. X и Y должны вычисляться при помощи Path:

Тип графика сменим с Automatic на Polygon и перетянем меру Index в поле Path. Поле Description перетягиваем в Color.

Меру Y2 тоже перетягиваем в Rows и устанавливаем для оси Dual Axis. Из All в Marks необходимо удалить Measure Names. Правой кнопкой мыши кликаем на ОY и синхронизируем оси:

Для Y2 устанавливаем тип Circle и корректируем размер:

Работа над оформлением

В Tableau есть возможность самому подобрать нужную гамму. Для жмём на Colors, затем на Edit colors, выбираем нужное поле и указываем цвет. Для гаммы колец из WatchOS мы подобрали такие цвета:

1. Красный: rgb(229, 54, 83)
2. Зелёный: rgb(186, 252, 79)
3. Синий: rgb(117, 229, 228)

В Label Y2 перетягиваем поля Description и percentage_label. Устанавливаем выравнивание, Description выделяем жирным цветом, ставим галочку в Options у поля Allow labels to overlap other marks, чтобы Label был виден:

Скрываем все линии, границы и индикатор, заливаем фон чёрным цветом. Результат — такая диаграмма:

Книга и таблица из примера доступны в нашем репозитории на GitHub.

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

Таблица из примера доступна в нашем репозитории на GitHub

Данные должны быть представлены в следующем виде, и если вы получаете их при помощи Custom SQL Query, вам может быть полезен наш последний материал: «UNPIVOT данных с использованием CROSS JOIN».

В таблице из примера мы получим 6 разных этапов: Identify, Pursue, Contact, Proposal, Negotiation и Won. Для каждого нужно задать соответствующее вычисляемое поле: например, ниже описана формула для вычисляемого поля статуса Identify.

if ATTR([Status]) = 'Identify' or LOOKUP(ATTR([Status]), -1)="Identify" then SUM([Value]) END

Итого должно получиться 6 новых мер:

Перетяните Measure Values в верхнюю часть графика: должен получиться такой bar chart:

Над графиком в выпадающем меню отображения графиков поменяйте Standart на Entrie View — график должен в ответ расшириться:

Из Measure Values удалите меры CNT(Sheet) и SUM(Value), которые не относятся к воронке:

Измерение Status перенесите в поле Rows. Получится несколько столбиков — это и есть будущие этапы воронки:

Убедитесь, что все вычисляемые поля вычислены при помощи Table (down):

А Stack marks установлен на off:

Смените тип графика на Area:

Чтобы каждый этап был окрашен в собственный цвет, перенесите измерение Status в поле Color:

В фильтре Status справа отсортируйте этапы по убыванию, перетягивая левой кнопкой мыши в нужное место:

Зажав клавишу CMD на MacOS или Ctrl на Windows, зажмите левой клавишей мыши Measures Values в поле Columns и протяните в область рядом: должно появиться такое же, а на графике с воронкой рядом должен появиться идентичный график.

Нажмите на ось X у левого графика и перейдите в меню Edit Axis. В разделе Scale поставьте галочку на поле Reversed, чтобы «отзеркалить» левый график.

Получится такая диаграмма:

Поработаем над оформлением. Нажмите правой кнопкой мыши на область с наименованием этапов слева и поставьте галочку напротив «Show Header». Проделайте то же самое с осью внизу:

Скройте также индикатор внизу:

Нажмите правой кнопкой мыши по графику и перейдите в раздел Format. Перейдите в меню Format Lines и смените значение для каждого типа линий на None. В соседнем разделе Format Borders также везде установите None:

Затем перенесите измерение Status и меру Value в поле Label. Нажмите на SUM(VALUE) и перейдите в Add Table Calculation, чтобы добавить ещё процент от первого этапа. В поле Calculation Type выберите «Percent From», а в поле Relative to — «First». Чтобы отобразить на каждом этапе процент от предыдущего: нажмите правой кнопкой мыши по мере SUM(Value) и нажмите на Add Table Calculation. В поле Calculation Type выберите «Percent From», а в поле Relative to — «Previous».

После нажмите на Label, перейдите в Edit Label и расположите текст с процентным соотношением под статусом:

Выравнивание установите, как на скриншоте:

Ещё перенесите в Tooltip меру Value, чтобы отображать в нём абсолютные значения. Затем нажмите на Tooltip и поменяйте форматирование:

В итоге получится такой график, у которого в подсказках отображается значение по этапу, процент от прошлого этапа и процент от первого этапа:

На написание этой статьи нас вдохновил анлоязычный видеорецепт.

Зачастую мы получаем данные в предагрегированном виде, когда каждая отдельная колонка является посчитанной метрикой. По аналогии мы получаем подобный результат, когда строим сводную таблицу в Excel и используем некоторое количество фактов для агрегации. Но что делать, если нам нужно произвести обратную операцию — Unpivot?

Как поступить, если в датасете понадобилось трансформировать данные в реляционный вид? В Tableau есть фича Unpivot, которая сделает всё сама: если датасет построен из файла, достаточно выделить нужные колонки и нажать на кнопку «Pivot». А в некоторых диалектах SQL, например, в Transact, уже есть встроенные функции, которые тоже делают это сами.

Но в случае, если датасет построен на Custom SQL Query из базы данных, у которой в арсенале отсутствуют встроенные функции для трансформации в сводную и обратно, необходим какой-то другой подход, и Tableau порекомендует для такой таблицы:

Воспользоваться таким стандартным универсальным, но не очень эффективным решением:

select id, ‘a’ AS col, a AS value
from yourtable
union all
select id, ‘b’ AS col, b AS value
from yourtable
union all
select id, ‘c’ AS col, c AS value
from yourtable

И в результате получить таблицу вида:

Порой, когда мы работаем с физической таблицей и нам надо быстро получить результаты для двух-трех колонок, действительно, подобное решение можно быстро применить, не задумываясь. Однако в случае, когда вместо таблицы содержится, например, сложный подзапрос с несколькими джойнами и нужно сделать Pivot для 5+ колонок, подзапрос вызовется целых 5+ раз, согласитесь, не очень действенно считать одно и тоже неоднократно. Вместо этого можно воспользоваться рецептом с CROSS JOIN, найденным на просторах Stack Overflow:

select t.id,
c.col,
    case c.col
        when 'a' then a
        when 'b' then b
        when 'c' then c
    end as data
from yourtable t
cross join
(
    select 'a' as col
    union all select 'b'
    union all select 'c'
) c

Разберём запрос подробнее. CROSS JOIN — перекрёстное соединение, декартово произведение, или, проще говоря, произведение всех строк со всеми. За ненадобностью в синтаксисе CROSS JOIN отсутствует ON — мы объединяем не по какому-то конкретному полю две таблицы, а сразу по всем существующим строкам.

Сначала мы формируем таблицу со всеми колонками, предназначенными для преобразования в строки. В нашем случае это колонки a, b и c: поэтому мы сделали таблицу c, в которой будет колонка col со значениями a, b и c:

(
    select 'a' as col
    union all select 'b'
    union all select 'c'
) c

Выглядит она так:

Затем таблицы yourtable и c объединятся перекрестным соединением, а после мы возьмём поля id, col и в зависимости от того, как называется ячейка в col, подставим соответствующие данные в поле data.

select t.id,
c.col,
    case c.col
        when 'a' then a
        when 'b' then b
        when 'c' then c
    end as value
from yourtable t
cross join
(
    select 'a' as col
    union all select 'b'
    union all select 'c'
) c

В итоге получим ту же самую искомую таблицу, с которой уже можно удобно работать любым аналитическим инструментом:

В этом году мне удалось пройти сертификацию Tableau Desktop Associate. И когда я думал о том, как к ней лучше подготовиться, я наткнулся на курсы elearning от Tableau, которые ещё и оказались бесплатными на 90 дней.

Я решил, что нельзя упускать такую возможность и решил пройти все три блока Fundamentals в бодром темпе. Когда получил сертификацию мне стало интересно, какие программы обучения предлагают другие производители BI-инструментов. И первым делом пошёл изучать обучающие материалы по PowerBI. В этой небольшой статье хочу попытаться сравнить программы обучения от Tableau и PowerBI.

Дисклеймер: в итоге у меня сформировалось предвзятое положительное отношение к Tableau, поэтому сторонникам PowerBI данная статья может оказаться не по нраву и в чем-то окажется субъективной (справедливости ради слова похвалы PowerBI тоже присутствуют).

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

Прежде всего, существует огромная пропасть в подходе к материалам и проверке их понимания. Несмотря на то, что обучающие материалы Tableau носят более технический характер и в меньшей степени уделяют внимание дизайну, обучаясь через их видео, всё же можно делать отличные рабочие визуализации. Что и говорить, после прохождения всех трёх ступеней обучения Tableau появляется желание творить новые крутые отчёты с использованием всех LOD Expressions, Filter Actions и создавать удобные интерфейсы. А вот после просмотра всех материалов по PowerBI остаётся один вопрос: зачем я потратил своё время? Для объективности сравнения и те, и другие материалы я изучал на английском языке. Думаю, в индустрии это стандарт, поскольку открыв 2-3 ссылки на русском понимаешь, что переведено это пяткой левой ноги.

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

Так выглядит хороший дашборд по версии Microsoft

Качество подготовки контента и примеров в обучении

Если посмотреть на логику изложения обучающих видео Tableau и вопросов в формате квиза, которые задаются в конце прохождения материала, начинаешь проникаться идеей софта. Но в случае с PowerBI тебя ждёт тотальное разочарование. Взгляните, к примеру, на материал об обнаружении выбросов, тут Microsoft предлагает построить диаграмму scatter plot и визуально определить все выбросы на глаз.

Дизайн отчётов и дашбордов

Существуют достаточно объективные придирки к обучающим материалам Tableau на тему дизайна графиков и элементов управления, но всё равно они сделаны аккуратно и красиво. А теперь взгляните на тот ужас, который предлагает в качестве результата работы аналитика Microsoft. А вот хорошо построенный дашборд по версии Microsoft.

Проверка полученных знаний из обучения

Во время обучения Tableau ты сразу же после небольшой лекции учишься применению куска изученного материала на практике. Нужно нажать конкретные кнопки в интерфейсе, чтобы решить задачу. В PowerBI предполагаются «лабораторные работы», которые должны были запуститься с удалённой машины. Мне не удалось начать ни одну лабораторную работу, я трижды писал в саппорт, саппорт так и не смог решить мою проблему, поэтому поэкспериментировать с заданиями в PowerBI у меня так и не вышло.

Результат работы аналитика по версии Microsoft

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

Кроссплатформенность

Я давно работаю с Tableau, и 4 года назад пересел на Mac. После перехода с Windows мой опыт использования Tableau никак не изменился: по сути, Tableau развивался, а я вместе с ним, но при этом ключевые элементы интерфейса команда не меняла. Я экспериментировал с построением отчётов в PowerBI, но мне были неудобны различные архаизмы Microsoft типа публикаций через какой-нибудь share-портал, где обязательно нужно иметь учётную запись MS и настраивать что-то через администратора. Вся эта головная боль жутко утомляет.

Однако гораздо больше меня поразил тот факт, что я не могу воспользоваться PowerBI на Mac. Вообще, совсем никак, и это принципиальная позиция Microsoft, которая в ближайшем будущем не планируется меняться. С моей точки зрения, такое программное обеспечение относится к сегменту B2B в области аналитики, предполагает подключение ко всевозможным СУБД, но отрицает факт существования альтернативной операционной системы, на которой потенциальное n-ное количество консультантов могут продвигать и использовать PowerBI как аналитический инструмент.

Наверняка есть рациональные причины, связанные с тем, что любой софт от Microsoft не очень здорово работает на Mac, но факт остаётся фактом: для меня софт становится недоступным. Тем не менее, я не искал лёгких путей и поставил PowerBI через Parallels для того, чтобы всё-таки честно посмотреть ещё раз на инструмент с учётом обучающих материалов.

Опции визуализации

И в Tableau, и в PowerBI очень крутые опции визуализации данных. К слову, в данном разрезе PowerBI всё же предлагает видео и чуть больше информации, чем обычно. Так что по этой части инструменты представлены одинаково хорошо.

Функциональность

А тут хочется отдать должное функциональности PowerBI. Действительно, багаж инструментов даже без подключения сторонних библиотек крайне широкий. К примеру, автоматическая кластеризация, Decomposition Tree, Data Profiler или Настройка фильтров по графику

Синтаксис внутреннего языка

Для работы с PowerBI следует выучить DAX. Это не язык программирования, а функциональный язык. Что-то своё написать не получится, но оно и не понадобится — внутри уже реализованы все функции, которыми нужно только научиться правильно пользоваться. Microsoft неплохо рассказывает про DAX в мануале. Определение новой меры на языке DAX выглядит так:

Revenue YoY % =
DIVIDE(
	[Revenue]
		- CALCULATE(
			[Revenue],
			SAMEPERIODLASTYEAR('Date'[Date])
	),
	CALCULATE(
		[Revenue],
		SAMEPERIODLASTYEAR('Date'[Date])
	)
)

Подготовка данных к анализу

Внутри PowerBI есть фича Unpivot, которая позволяет привести данные, разложенные по столбцам с временными периодами к форме, удобной для использования в сводных таблицах:

Впрочем, в ETL-инструменте для очистки и предобработки данных Tableau Prep такое тоже реализовано

Выводы:

1) Программы обучения построены совершенно по-разному, методология погружения в инструмент от Tableau намного продуманнее и эффективнее. Есть возможность сразу же получить практический опыт решения задач и получить обратную связь (хоть и автоматическую).
2) Дизайн отчетов и дашбордов в обучающих материалах от Microsoft выглядит едва ли профессионально, у Tableau реализация выглядит на порядок лучше
3) Реализация проверки знаний от Microsoft ниже плинтуса (совершенно формальные тесты как в плохой школе), у Tableau реализовано хорошо, погружаешься в задачу, думаешь над ответом и решаешь.
4) Кроссплатформенность явно не является коньком PowerBI, однако в случае Tableau это отличное конкурентное преимущество
5) Функциональность и возможности инструментов, разумеется, находятся на высоком уровне, и в чем-то победу одерживает PowerBI.

Посмотрите наши обзоры дашбордов в Tableau, PowerBI, Google Data Studio, SAP Analytics Cloud, QlikSense, Redash и в других BI-системах.

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

В видео рассказываю весь процесс создания дашборда в первом рассматриваемом инструменте — Tableau: как мы подготавливали данные, создавали отчёты, верстали дашборд, с какими сложностями и правками столкнулись, а также как опубликовать его на сервере Tableau Public и насколько результат соответствует поставленной задаче.

Мы оценили внутренней командой дашборд по критериям и получили следующие средние оценки (1 — худшая оценка, 10 — лучшая):

1. Отвечает ли заданным вопросам — 10,0

2. Порог входа в инструмент — 5,5

3. Функциональность инструмента — 9,0

4. Удобство пользования — 8,5

5. Соответствие результата макету — 10,0

6. Визуальная составляющая — 9,7

Итог — дашборд на Tableau получает 8,8 баллов из 10 от нашей команды. Посмотрите на полученный результат.

А что вы думаете о получившимся дашборде? Поставьте свои оценки в нашем Telegram-канале!

В прошлом видео мы научились визуализировать теннисные мячики из Swing Vision на графике в Tableau с кастомным фоном и кастомными фигурами. Сегодня будем анимировать дашборд, чтобы посмотреть, как менялись удары и получим видео с результатами игры, которое можно экспортировать и кому-нибудь показать.

Краткое резюме:

1. Создаём элемент Pages. Он позволяет управлять движением анимации: нажимая на кнопку Play, Title страницы будет меняться.
2. Добавим историю: ставим галочку на Show History ниже и выберем длину в 7 ударов.
3. Вернёмся на дашборд. Переходим во вкладку Worksheet — Show Cards и выбираем Current Page.
4. Для захвата видео с экрана добавим новый контейнер и перенесём в него панель с ударами.
5. Утилитой записи экрана выбираем область с дашбордом и жмём на Play. Для macOS можно воспользоваться встроенной: достаточно нажать комбинацию клавиш ⌘ + Shift + 5.

Я увлекаюсь теннисом и недавно обнаружил относительно свежее приложение, помогающее теннисистам понять качество своей игры — Swing Vision. Приложение чудесное: в реальном времени распознает удары ракеткой по мячу и отображает координаты каждого удара. Автор приложения — профессор UC Berkeley, Swupnil Sahai, недавно я написал ему на почту и поблагодарил за такую полезную вещь.

Так выглядит статистика в приложении

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

Так выглядит xls-файл

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

Краткое резюме:

1. Подготавливаем изображение теннисного корта
2. Импортируем данные из xls-файла в Tableau.
3. Создаём новые Calculated Fields, в которых преобразуем вертикально и горизонтально координаты удара.
4. Для всех мячей, попавших в сетку, ставим конкретную координату по Y = -11,89.
5. Фильтруем, добавляем цвета и размечаем данные на графике:
   a. Работаем с фоновым изображением
   b. Считаем ratio корта на изображении к корту в метрах. К примеру, в моём случае ширина 913px, а ширина корта — 10,97 метров. Значит, соотношение — 83,2270
   c. Считаем отступы, которые нужно сделать от 0 коориднат
   d. Аналогичные действия проделываем с Y-координатами
   e. Добавляем фоновое изображение и устанавливаем его свойства
6. Находим векторную иконку тенисного мяча.
7. Кладём иконку в папку Shapes репозитория Tableau.
8. Меняем иконку в меню Shapes.

В результате получаем:

В новой серии постов постараемся подробно изучить различные BI-системы на популярной группе датасетов SuperStore Sales. В основе данных — продажи и прибыль сетевого ритейлера в долларах.

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

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

Ниже перечислен перечень BI-систем и инструментов для работы, с данными, которые хотелось бы опробовать и описать опыт построения дашборда. Приглашаю тех, кто желает поучаствовать в решении данной задачи написать мне в Telegram — @valiotti. Разумеется, авторство дашборда будет указано. Проект некоммерческий, но полезный для сравнения современных систем для аналитики независимо от квадрантов Gartner.

Сейчас в планах подготовить материалы о следующих инструментах:

Бесплатные (Open source):

• Metabase: 8 баллов из 10
• Redash: 6,5 баллов из 10
• Apache Superset: 7,7 баллов из 10
• Dash / Plotly: 8,05 баллов из 10

Бесплатные (cloud):

• Google Data Studio: 7,7 баллов из 10
• Yandex Datalens: 8 баллов из 10

Платные (cloud):

• Mode
• Cluvio
• Holistic
• Chartio
• Periscope
• DeltaDNA
• Klipfolio
• Count.co
• SAP Analytics Cloud: 8,7 баллов из 10

Платные:

• PowerBI: 8,0 балла из 10
• Tableau: 8,8 баллов из 10
• Looker: 7,7 балла из 10
• Excel: 7,5 балла из 10
• Alteryx
• Qlik Sense: 8,4 балла из 10

Итоговая цель — оценить системы по нескольким внутренним критериям:

• порог входа в инструмент (1 — супер сложно, 10 — легко)
• функциональность инструмента (1 — очень бедный функционал, 10 — сложно что-то добавить)
• удобство пользования (1 — очень неудобно, 10 — супер удобно)
• соответствие результата задаче (1 — совсем не попали в желаемый макет, 10 — очень близко к описанию и макету)
• визуальная составляющая (1 — выглядит непривлекательно, 10 — визуально привлекательный дашборд)

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

Параллельно, результаты работы будут представлены в Telegram-канале @leftjoin, и подписчики также смогут высказать свое мнение относительно полученного результата.
В итоге каждый инструмент будет описан точкой на плоскости, а сама плоскость будет поделена на 4 части.

По мере написания новых материалов в цикле этот пост будет обновляться: будут добавляться ссылки на посты и оценки.