Создание дашборда Яндекса Директа в Yandex DataLens с использованием ClickHouse

В этом руководстве я покажу вам, как можно создать дашборд для вашего рекламного кабинета Яндекса Директа в Yandex DataLens, используя подключение ClickHouse.

Этот материал является продолжением руководства Владимира Зенина, который опубликовал отличную статью по установке ClickHouse на собственном сервере. Все эти гайды являются частью большой онлайн-документации, аналогов которой в русскоязычном сообществе просто нет. Если вы хотите научиться гибко работать со своими данными, выгружать статистику из множества рекламных кабинетов Яндекс Директа, самостоятельно управлять всеми визуализациями, строить отчетность для своих клиентов, объединять информацию с другими источниками (например, CRM-системой или другой рекламой), обязательно подключайтесь к нашему проекту. Поверьте, маркетолог тоже умеет писать код.

Как вы знаете, в Yandex DataLens нет готового подключения к Яндекс Директу. Для этого вы можете воспользоваться несколькими вариантами:

1. выгружать статистику в Google Таблицы, а затем подключаться к бесплатному коннектору Google Sheets;
2. выгружать данные локально из интерфейса Яндекс Директа, а потом подключать файлы в формате CSV, TXT или XLSX в DataLens и сводить все внутри вручную (не больше 10 файлов, а вес каждого из них не должен превышать 200 МБ);
3. выгружать статистику в собственную базу данных (например, в PostgreSQL, MySQL, ClickHouse, Snowflake и др.), а затем использовать соответствующий коннектор в Yandex DataLens (если он есть в списке подключений).

У каждого способа есть свои преимущества и недостатки. Например, выгрузка данных в Google Таблицы подходит для небольших проектов, где количество данных не превышает лимиты и возможности самих Google Sheets. Если у вас один рекламный кабинет, мало статистики, или вы только начинаете работу с Yandex DataLens, то разумно использовать именно таблицы Google.

Примечание: Google Таблицы имеют собственные ограничения по объему хранения данных - максимальное количество ячеек на одну Google таблицу 10 000 000 (10 миллионов) ячеек или 18 278 столбцов (до столбца ZZZ) для файлов, созданных в Google Таблицах или преобразуемых в этот формат или если импортировать таблицу из Microsoft Excel. Это число распространяется на все рабочие листы одной рабочей книги, то есть на один файл Google Sheets.

Выгружать статистику на компьютер из Мастера отчетов, а затем подключаться к локальным файлам в Yandex DataLens - то же можно. Этот способ подходит тем, кто не хочет автоматизировать однотипные операции и кому рутинные задачи не доставляют особого дискомфорта. Если заказчик требует от вас отчет раз в месяц или квартал (=редко), то выгрузить несколько раз один и тот же набор данных, но с разными диапазоном дат - не составит особого труда. Подключив их в DataLens, вы получите хорошее и бесплатное решение.

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

Именно тогда специалисты выгружают данные из Яндекс Директа в другое место, используя API Директа. Например, в облачное хранилище данных Google BigQuery, ClickHouse, на свой собственный сервер, в базы данных MySQL, PostgreSQL и т.д., чтобы в дальнейшем иметь возможность связать их с другими данными компании – складским отчетом, финансовым отделом, логистикой, продажами, производством и прочими отделами. А если вы используете еще и несколько каналов продвижения, то сможете построить общий отчет по рекламе, объединив информацию из разных источников (Facebook, VK, TikTok и пр.).

* Деятельность американской компании Meta (бывшая Facebook) запрещена в России, организация признана экстремистской.

Аренда серверных решений стоит денег, поэтому такой способ организации данных нужен только тем, кому "тесно" в интерфейсе Яндекс Директа и кто желает полного доступа и контроля статистики. Если вы обладаете навыками проектирования баз данных, то вам не составит выгрузить статистику в свою собственную БД, а затем использовать готовый коннектор к ней в Yandex DataLens. Окончательный выбор той или иной базы данных принимаете лично вы и ваша команда.

В последнее время многие организации все чаще и чаще используют ClickHouse в качестве основной системы управления базами данных (СУБД). Ключевая причина - скорость обработки большого количества данных в режиме онлайн и построение отчетности.

ClickHouse изначально разрабатывался для обеспечения работы Яндекс Метрики. При более 13 триллионах записей в базе данных и более 20 миллиардах событий в сутки, ClickHouse позволяет генерировать индивидуально настроенные отчеты на лету напрямую из неагрегированных данных. Для решения этой задачи используется кластер из 374 серверов, хранящий более 20,3 триллионов строк в базе данных. Объем сжатых данных, без учета дублирования и репликации, составляет около 2 ПБ. Объем несжатых данных (в формате tsv) составил бы, приблизительно, 17 ПБ.

Также ClickHouse используется:

• для хранения данных Вебвизора;
• для обработки промежуточных данных;
• для построения глобальных отчетов Аналитиками;
• для выполнения запросов в целях отладки движка Метрики;
• для анализа логов работы API и пользовательского интерфейса.

Но этим возможности системы не ограничиваются. Со временем к ClickHouse была написана подробная документация, и базу данных стали активно использовать в других продуктах Яндекса — Директе, Маркете, Почте, ADFOX, Вебмастере, в мониторинге и бизнес-аналитике. Например, сервис нагрузочного тестирования Яндекс Танк использует ClickHouse для хранения данных телеметрии. СУБД позволяла решать задачи, для которых раньше не было подходящих инструментов, или же с ней получалось это делать намного эффективнее, чем с другими системами.

Некоторые архитектурные особенности ClickHouse:

• столбцовое хранение данных - данные считываются только из нужных колонок, и однотипная информация эффективно сжимается;
• поддержка приближенных вычислений на части выборки - снижается число обращений к жесткому диску, что ещё больше повышает скорость обработки данных;
• физическая сортировка данных по первичному ключу - можно быстро получить конкретные значения или диапазоны;
• векторные вычисления по кусочкам столбцов - снижаются издержки на диспетчеризацию, эффективно используется CPU;
• распараллеливание операций как в пределах одного сервера на несколько процессорных ядер, так и в рамках распределенных вычислений на кластере за счет механизма шардирования;
• линейная масштабируемость - есть возможность построить кластер очень большого размера;
• работа с жесткими дисками - ClickHouse достаточно эффективно работает, когда данные не попадают в кеш памяти целиком. Дополнительно это снижает стоимость эксплуатации системы, так как жесткие диски дешевле RAM;
• отказоустойчивость - система представляет собой кластер шардов, где каждый шард - это группа реплик.

Подробнее о ClickHouse читайте в официальной документации Yandex Cloud и ClickHouse.

Вероятно, о ClickHouse вы стали чаще слышать еще и потому, что данная СУБД является частью Yandex Cloud. Сейчас она позиционируется как главная замена облачному хранилищу данных Google BigQuery, который с 9 сентября 2024 года прекратил работу в России. М - маркетинг! То есть вы можете использовать облачные решения от самого Яндекса, арендовав у них нужную конфигурацию. Служба Yandex Managed Service for ClickHouse помогает разворачивать и поддерживать кластеры баз данных ClickHouse в инфраструктуре Yandex Cloud. Каждый кластер состоит из одного или нескольких хостов БД - виртуальных машин с развернутыми СУБД. Вы сами можете определить количество хостов в кластере, технические характеристики и зону доступности для каждого хоста, а также размер требуемого хранилища. За аренду каждого кластера вы будете платить ежемесячную абонентскую плату согласно выбранным настройкам.

Ну, и, конечно же, ClickHouse стал популярен среди интернет-маркетологов из-за готового подключения в Yandex DataLens. Так как разработчики пока еще не добавили коннектор для Яндекс Директа, мы вынуждены использовать альтернативные варианты. Одним из таких как раз и является подключение к ClickHouse:

Чем же еще хорош ClickHouse? Тем, что его можно установить на своем собственном сервере. ClickHouse - это система управления базами данных  с открытым исходным кодом, и она поддерживает установку на различных операционных системах, включая Linux и macOS. А это означает, что вы можете не использовать ClickHouse в инфраструктуре Yandex Cloud, а развернуть его на своем собственном сервере, что позволит вам существенно сократить расходы.

Ниже приведен пример самой простой конфигурации кластера ClickHouse. Его стоимость в Yandex Cloud - 2779 руб./мес.

А вот пример аналогичной конфигурации, только на одном из известных хостинг-провайдеров - 915 руб./мес.

Экономия в 3 раза! Только в случае с Yandex Cloud кластер ClickHouse вам предоставляется за несколько минут и кликов, а для установки той же СУБД на своем собственном сервере вам потребуются выполнить n-ое количество шагов, в частности:

• настроить VPS;
• установить терминал;
• установить и настроить ClickHouse;
• настроить безопасное соединение (чтобы подключаться по TLS; желательно).

Подробно об этом написано в двух материалах Владимира Зенина. Обязательно прочтите и выполните все этапы до этого руководства:

• Первичная настройка виртуального сервера (VPS)
• Установка ClickHouse на собственном сервере

Примечание: если вы планируете использовать ClickHouse для работы с данными ТОЛЬКО ваших рекламных кабинетов Яндекс Директа, то, на мой взгляд, это избыточность и усложнение, поскольку для такого рода задач существуют и более простые решения - базы данных MySQL, PostgreSQL, MS SQL Server. Представьте, что вы купили самый мощный компьютер, но используете его только для игры в косынку. Абсолютно неэффективное расходование средств и использование вычислительных ресурсов. Но поскольку целью данного материала является демонстрация возможностей подключения, мы будем использовать связку Яндекс Директ - ClickHouse - Yandex DataLens.

Я предполагаю, что установку и настройку ClickHouse на своем собственном сервере вы произвели. Теперь мы создадим дашборд в Yandex DataLens на выгруженных данных из Яндекса Директа с использованием подключения ClickHouse.

Для этого в Yandex DataLens перейдите в раздел Коллекции и воркбуки и создайте свой воркбук:

Задайте ему название. Например, Яндекс Директ:

Нажмите кнопку Создать. Вас перенаправит на страницу подключения. Выберите на ней ClickHouse:

Поскольку мы используем подключение к собственному серверу, переключитесь на вкладку Указать вручную:

Вручную укажите реквизиты внешней БД, чтобы подключиться через публичную сеть:

• Имя хоста. Задается путь до хоста или IP-адрес. Вы можете указать несколько хостов через запятую. Если к первому хосту подключиться не получится, DataLens выберет следующий из списка.

Поскольку в предыдущем материале мы установили сервер ClickHouse на Timeweb, то используем IP-адрес, который был выделен нам там. Посмотреть его можно в настройках конфигурации сервера. У вас он будет, конечно же, другим:

• Порт HTTP-интерфейса. ClickHouse доступен по IP-адресу сервера через порт 8123, поэтому необходимо изменить значение по умолчанию 8443 на это;
• Имя пользователя и Пароль. Те данные, которые вы установили на своем сервере;
• Время жизни кеша в секундах. Оставьте вариант По умолчанию;
• Уровень доступа SQL запросов. Позволяет использовать произвольный SQL-запрос для формирования датасета. Поскольку наши данные Яндекс Директа хранятся в единой таблице с большим количеством столбцов, и в рамках этого примера нет необходимости их объединения с другими источниками данных, вы можете оставить вариант Запретить. Если вы желаете работать с датасетами с помощью SQL-запросов, выберите Разрешить подзапросы в датасетах, а если еще хотите и создавать QL-чарты - тогда Разрешить подзапросы в датасетах и запросы из чартов.

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

 

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

TLS (Transport Layer Security) - это криптографический протокол, который обеспечивает безопасную передачу данных по сети. Подключение к серверу через TLS означает, что данные, передаваемые между клиентом и сервером, шифруются, что защищает их от перехвата и изменений. В большинстве случаев включение TLS является обязательным для обеспечения безопасности и защиты данных, в особенности, когда вы передаете конфиденциальную информацию и чувствительные данные, которые можно перехватить и использовать во вред (пароли, номера банковских карт или личные данные пользователей). Работать в безопасном соединении всегда надежнее, чем без него. Но для этого нужно выпустить SSL-сертификат. А это дополнительная задача, которую вы, как владелец сервера, должны выполнить самостоятельно.

Если вы не обладаете такими навыками, то в продвинутых настройках подключения на данном этапе я рекомендую выбрать Выкл. в TLS. Когда опция включена, при взаимодействии с БД используется протокол HTTPS, когда выключена — HTTP. Да, такое соединение не будет являться безопасным. Но поскольку мы работаем с данными только рекламных кампаний Яндекс Директа, которые не обладают высокой степенью конфиденциальности, в учебных целях мы можем этим пренебречь. Но я все же настаиваю на том, чтобы впоследствии вы выпустили SSL-сертификат и в повседневной работе использовали только безопасное соединение с включенным TLS.

Для создания дашборда Яндекса Директа в Yandex DataLens с использованием ClickHouse нам достаточно такой конфигурации подключения:

Чтобы проверить корректность введенных параметров, нажмите кнопку Проверить подключение.

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

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

Или ошибку, свидетельствующую о неверном порте:

Если вы не знаете, что означает ваша ошибка и как ее устранить, попробуйте написать в поддержку Yandex Cloud, приложив подробное описание, а также Request-ID и Trace-ID, по которым модераторы смогут быстро идентифицировать ваш запрос.

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

Введите название подключения (например, ClickHouse - Yandex Direct) и нажмите кнопку Создать:

После добавления подключения создайте чарт на его основе:

В открывшемся окне нажмите кнопку +Добавить и выберите из списка свое подключение:

После этого в разделе Таблицы вы должны увидеть все свои таблицы, которые доступны в вашей базе данных ClickHouse. Перетащите нужную таблицу со статистикой Яндекс Директа в правую часть окна. Вам станет доступен предварительный просмотр содержимого (не больше 1000 записей):

Переключившись на вкладку Поля, вы увидите конфигурацию вашего источника данных - список параметров и показателей, которые вы выгрузили с помощью API Директа и загрузили в таблицу ClickHouse:

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

Cделав все это, просто сохраните датасет, задав ему название (например, Датасет - Яндекс Директ):

Поскольку в этом примере мы используем подключение без создания QL-чартов и использования SQL-запросов, все последующие визуализации создаются как Чарты в Wizard:

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

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

А сам дашборд Яндекса Директа в Yandex DataLens с использованием ClickHouse вот так:

Хотели бы научиться создавать такие дашборды в Yandex DataLens? Тогда добро пожаловать!