Найти тему
In Data We Trust
158 подписчиков

Построение дашборда DataLens для клиентского сервиса на базе Jivo, AirFlow и ClickHouse

146
9 мин
Оглавление

Заказчик попросил собрать дашборд для оценки клиентского сервиса. Получился простой и информативный кейс, на примере которого можно рассказать про основные процессы и сущности ETL/ELT пайплайна.

Постановка задачи

Построить дашборд для оценки качества и скорости обработки обращений посетителей.

Параметры и показатели:

  • Количество обращений
  • Количество решенных заявок
  • Количество пропущенных заявок
  • ...

Фильтры:

  • Дата
  • Менеджер
  • Источник
  • ...

Клиентская служба использует для общения с посетителями сервис https://www.jivo.ru/, который помогает общаться с посетителями через онлайн-чат на сайте, telegram бот, email и другие мессенджеры.

Стек

  • Яндекс Облако в качестве рабочего облака;
  • Яндекс DataLens в качестве BI инструмента;
  • Яндекс Object Storage в качестве файлового хранилища;
  • ClickHouse поднятный в Яндекс Облаке в качестве базы данных для обработки и хранения данных;
  • Apache Airflow 2 поднятый в Яндекс Облаке для оркестрации ETL/ELT процессов.

Алгоритм решения

  1. Получить все доступные исторические данные из Jivo.
  2. Подготовить Airflow для получения сырых данных из Jivo раз в сутки.
  3. Создать в ClickHouse таблицу для сырых данных Jivo.
  4. Создать в ClickHouse витрину с агрегатами для DataLens.
  5. Настроить уведомления о завершении DAG сбора витрины.
  6. Настройка DAG Airflow для сбора витрины.
  7. Собрать в DataLens дашборд на базе витрины с агрегатами.

1. Получение всех доступных исторических данных из Jivo

Для обсчета метрик необходимо получить именно сырые данные из Jivo.

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

В документации Jivo есть информация только о Webhooks API, что плохо подходит для решения задачи, и требует привлечения дополнительных ресурсов.

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

Но оказалось, что у Jivo есть свой REST API, который позволяет собирать данные за 7 последних дней.

Токен и инструкцию можно получить у менеджера.

В первую очередь Python скриптом я собрал все доступные исторические данные из Jivo, и сохранил их в формате JSON в бакет Object Storage.

Скрипт экспорта данных из Jivo.

2. Подготовка Airflow для получения сырых данных из Jivo раз в сутки

Плохая практика хранить секреты внутри DAG, поэтому токен авторизации и эндпоинт лучше добавить в Airflow Variables.

Variables в Airflow
Variables в Airflow

Получить данные из connections внутри DAG можно через обращение к Variable:

from airflow.models import Variable
API_TOKEN = Variable.get("jivo-token") # Указывается значение key
BASE_URL = Variable.get("jivo-url")

Доступы к ClickHouse и Object Storage можно хранить в Airflow Connections.

Connections в Airflow
Connections в Airflow

Настройки Connections для Clickhouse:

Для подключения я использую Airflow ClickHouse Plugin, в основе которого лежит Clickhouse-driver.

  • Connection Id — название подключения. Оно указывается в Task
  • Connection Type — тип подключения. В соответствии с рекомендациями библиотеки Airflow ClickHouse Plugin
  • Host — адрес подключения к ClickHouse
  • Login — логин пользователя
  • Password — пароль пользователя
  • Port — 9440
  • Extra — {"ca_certs": "/etc/ssl/certs/ca-certificates.crt", "secure": true, "verify": true}
ClickHouse connection в Airflow
ClickHouse connection в Airflow

Настройки Connections для Yandex Object Storage:

  • Connection Id — название подключения. Оно указывается в Task
  • Connection Type — Amazon Web Services
  • Extra — {"aws_access_key_id": "<ID ключа>", "aws_secret_access_key": "<Ключ>", "endpoint_url": "https://storage.yandexcloud.net"}

3. Создание в ClickHouse таблицы для сырых данных Jivo

На основе анализа ответа API Jivo я пришел к выводу, что таблица для сырых данных будет выглядеть следующим образом:

  • webhook_id — ID записи. Пример: 1000
  • widget_id — ID виджета (способа связи). Пример: 4522390
  • event_name — Название события. Пример: chat_finished
  • event_dt — Дата и время события. Пример: 2023-01-01 00:40:30
  • chat_id — ID чата. Пример: 1487
  • topic_id — ID темы, присвоенной чату менеджером. Пример: 42
  • visitor_id — ID посетителя. Пример: 150
  • visitor_name — Указанное посетителем имя. Пример: Иван Иванов
  • visitor_number — ID посетителя. Пример: 150
  • visitor_description — Комментарий, который менеджер добавил в карточку посетителя. Пример: Хороший человек
  • visitor_email — Указанный посетителем email. Пример: test@gmail.com
  • visitor_phone — Указанный посетителем телефон. Пример: 000000007
  • visitor_plain_message — Текст email сообщения от посетителя. Пример: Добрый день
  • visitor_html_message — Текст email сообщения от посетителя с HTML разметкой. Пример: <b>Добрый день</b>
  • visitor_chat_total — Количество чатов с посетителем. Пример: 2
  • visitor_social_profile_name — Массив с названиями социальных сетей посетителя. Пример: tg
  • visitor_social_profile_link — Массив со ссылками на профиль посетителя в социальных сетях. Пример: https://telegram.me/test
  • visitor_social_profile_icon_name — Массив с названиями социальных сетей посетителя. Пример: tg
  • visitor_social_profile_icon_link — Массив со ссылками на фотографию посетителя в социальной сети. Пример: https://media-sber1.jivo.ru/-230816/120358D449C05A0B6
  • assigned_agent_id — ID менеджера, назначенного ответственным за клиента. Пример: 2
  • assigned_agent_email — Email менеджера, назначенного ответственным за клиента. Пример: hi@gmail.com
  • assigned_agent_name — Имя менеджера, назначенного ответственным за клиента. Пример: Кеша
  • agent_id — ID оператора, который принял сообщение от посетителя. Пример: 1
  • agent_email — Email оператора, который принял сообщение от посетителя. Пример: hello@gmail.com
  • agent_name — Имя оператора, который принял сообщение от посетителя. Пример: Иван
  • agents_id — Массив ID операторов, участвовавших в общении с посетителем. Пример: 1
  • agents_email — Массив Email операторов, участвовавших в общении с посетителем. Пример: hello@gmail.com
  • agents_name — Массив имен операторов, участвовавших в общении с посетителем. Пример: Иван
  • geoip_country_code — Код страны посетителя, определенный по IP. Пример: RU
  • geoip_region_code — Код региона посетителя, определенный по IP. Пример: KZN
  • geoip_country — Название страны посетителя, определенное по IP. Пример: Russia
  • geoip_region — Название посетителя, определенное по IP. Пример: Kazan
  • geoip_city — Название города посетителя, определенное по IP. Пример: Kazan
  • geoip_isp — Провайдер посетителя. Пример: MTS
  • geoip_latitude — Географическая широта . Пример: 55.796127
  • geoip_longitude — Географическая долгота. Пример: 49.106414
  • geoip_organization — Объект с информацией о компании, к которой принадлежит клиент. Пример: Tele2 Russia Groups
  • session_utm — Полная строка UTM, которая привела посетителя на сайт. Пример: campaign=(organic)|source=Yandex|medium=search
  • session_utm_campaign — utm_campaign метка, которая привела посетителя на сайт. Пример: (organic)
  • session_utm_source — utm_source метка, которая привела посетителя на сайт. Пример: Yandex
  • session_utm_medium — utm_medium метка, которая привела посетителя на сайт. Пример: search
  • session_utm_term — utm_term метка, которая привела посетителя на сайт. Пример: 14838639836
  • session_utm_keyword — utm_keyword метка, которая привела посетителя на сайт. Пример: yandex.market
  • session_utm_content — utm_content метка, которая привела посетителя на сайт. Пример: banner001
  • ip_addr — IP адрес посетителя. Пример: 192.0.0.1
  • user_agent — User-agent посетителя. Пример: Mozilla/5.0 (CrKey armv7l 1.5.16041) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.0 Safari/537.36
  • page_url — URL страницы, на которой было отправлено сообщение. Пример: https://dzen.ru/idwt
  • page_title — Title страницы, на которой было отправлено сообщение. Пример: In Data We Trust
  • analytics_ga — Client ID посетителя в Google Analytics. Пример: 2028548107.16908
  • analytics_ym — Client ID посетителя в Яндекс Метрике. Пример: 1690822237771165267
  • chat_blacklisted — Добавлен ли чат в черный список. Пример: FALSE
  • chat_invitation_text — Текст сообщения оператора. Пример: Здравствуйте!
  • chat_rate — Оценка диалога. Пример: positive
  • offline_message_text — Текст офлайн сообщения посетителя. Пример: Тест
  • offline_message_id — ID офлайн сообщения посетителя. Пример: 1692210875885
  • chat_message_text — Массив текстов сообщений в переписке. Пример: /start
  • chat_message_ts — Массив Unix Timestamp сообщений. Пример: 45154.7183680556
  • chat_message_role — Массив ролей кто отправляет сообщение. Пример: visitor
  • chat_message_agent_id — Массив ID агентов в переписке. Пример: 2

Так же описание полей доступно в документации Jivo.

DDL скрипт создания таблицы событий Jivo в ClickHouse.

Так как данные с событиями Jivo сохраняются в бакете Object Storage, для подключения можно использовать ClickHouse движок S3. А для разбора JSON файла наряду с классическими функциями используются функции для работы с массивами.

DML скрипт разбора JSON файла с данными Jivo.

Пример заполненной таблицы с сырыми данными Jivo
Пример заполненной таблицы с сырыми данными Jivo

4. Создание в ClickHouse витрины с агрегатами для DataLens

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

  • chat_id — ID чата с посетителем. Пример:
  • visitor_number — ID посетителя. Пример:
  • topic_name — Тема обращения. Пример:
  • agent_name — Имя менеджера, который общался с посетителем. Пример:
  • widget_name — Источник обращения. Пример:
  • chat_rate — Оценка чата. Пример:
  • chat_date — Дата чата. Пример:
  • chat_duration_minute — Время закрытия обращения в минутах. Пример:
  • time_to_response_second — Время до первого ответа в секундах. Пример:
  • chat_status — Статус обращения. Пример:
  • offline_status — Статус офлайн сообщения
  • working_time_status — Статус рабочего и нерабочего времени обращения.

DDL скрипт создания витрины с агрегатами в ClickHouse.

Агрегаты в витрину собираются на уровне ClickHouse.

DML скрипт сбора агрегатов в витрину ClickHouse.

5. Настройка уведомлений о завершении DAG сбора витрины

Для контроля ошибок работы Airflow я завел двух ботов в телеграме (как завести своего бота для уведомлений в telegram). В первый бот приходят уведомления о корректном завершении работы по сбору витрины, и он работает в бесшумном режиме. Во второй бот приходят сообщения, если при сборе витрины произошла ошибка.

Настройки подключения к ботам в Airflow добавляются в Connections:

  • Connection Id — название подключения. Оно указывается в Task
  • Connection Type — HTTP.
  • Host — ID чата, в который бот будет присылать сообщение.
  • Password — Токен бота
Настройка подключения Telegram в Airflow
Настройка подключения Telegram в Airflow

6. Настройка DAG Airflow для сбора витрины

Алгоритм DAG:

  1. Первая задача: Airflow подключается к API Jivo, забирает данные о событиях за прошедшие сутки, сохраняет результат в Object Storage в формате JSON.
  2. В рамках второй ClickHouse разбирает загруженный JSON файл и дописывает данные в таблицу с сырами данными.
  3. В рамках третей задачи ClickHouse очищает таблицу с витриной.
  4. В рамках четвертой задачи ClickHouse агрегирует данные в витрину.
  5. При успешном завершении DAG в Telegram отправляется сообщение об успешном завершении.
  6. При ошибке в выполнении DAG в Telegram отправляется сообщение об ошибке.

DAG запускается каждый день в 5 часов ночи.

7. Разработка дашборда в DataLens

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

У Яндекса есть бесплатный курс обучения работы с Datalens.

Пример расчета метрик для дашборда:

  • Общее количество обращений — количество уникальных значений из поля chat_id
  • Решенные обращение — количество уникальных значений из поля chat_id с учетом значения "Заявка решена" по полю chat_status.
  • Среднее время до закрытия обращения в минутах — среднее значение по полю chat_duration_minute.

Результат:

-6

Заключение

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

github.com
GitHub - RuslanFatkhutdinov/airflow_pipeline_jivo_clickhouse_datalens