Задача: ежедневно необходимо сопоставлять данные внешней системы и CRM, результаты должны быть представлены в виде отчета в PowerBI.
- Проблема, подлежащая решению, описание задачи
- Описание способа решения проблемы
- Используемые технологии
- Результат автоматизации
- Стоимость использования полученного решения
Проблема, подлежащая решению, описание задачи
В целях оценки эффективности одного из каналов привлечения новых лидов используется внешний сервис. Для его работы на сайте внедрен специальный JavaScript код, который фиксирует некоторый набор данных о посетителя сайта (аналог системы веб-аналитики), таким образом система знает об источниках трафика на проекте. При фиксации данных о новом лиде в CRM передается информация об источнике трафика, который привел к его созданию. Ежедневно между CRM системой и внешней системой происходит обмен данными из которых внешняя система «узнает» о том, какие лиды в итоге перешли в разряд сделок и были успешно закрыты, а также обогащаются сведения по лидам.
В силу различных обстоятельств для построения отчета, который бы включал в себя данные из внешней системы и данные по лидам и сделкам из CRM, приходится формировать выгрузки из двух систем и сопоставлять, либо фиксировать данные с помощью поиска и фильтров в каждой систем и затем также сводить в единый отчет в стороннем инструменте.
Необходимо учесть особенность. Последние N дней происходит работа с лидами и данные, возвращаемые внешней системой могут быть изменены. Например, лид 12345 сегодня имеет статус Создан и о нем именно в таком статусе проинформирована внешняя система, присвоив ему свой статус Новый, следовательно именно его она и возвращает. Через несколько дней лид меняет статус на Реализован и также об этом информируется внешняя система, у себя она его меняет на Продажа. В результате если отчет получит данные единожды, то в нем статус останется Новый, хотя необходимо иметь статус фактический Продажа. Период равный N дней фиксированный, более него изменения либо не происходят либо единичны и ими можно пренебречь.
Описание способа решения проблемы
Для решения задачи необходим следующий набор данных:
- данные из внешней системы
- данные из CRM
Получить данные из внешней системы можно по ссылке. Сервис предоставляет некоторую точку входа, обратившись к которой возвращается набор данных за определенный период, в котором содержится информация о лидах. Формат возвращаемых данных XML.
Данные из CRM извлекаются из системы уже реализованным до начала работ механизмом и его изменение не требуется, т.к. полностью удовлетворяет своей функциональностью. Данные переносятся во внешнюю базу данных.
Инструментом визуализации данных и построения модели выбран PowerBI.
При попытке получить данные по ссылке, за период больший чем пять дней, из внешней системы возникает ошибка, которая заключается в том, что из-за объема данных сервис его не может вернуть корректно, это приводит к тому, что выгрузка завершается неудачно. Чтобы решить эту проблему принято решение данные из сервиса хранить во внешней базе данных.
С помощью скрипта, написанного на Python организовано получение данных за произвольный день, их трансформация из XML формата и сохранение в базе данных. Такой скрипт отлично отрабатывает на локальном компьютере. Но чтобы максимально автоматизировать задачу он был развернут в Google Cloud и настроен на ежедневное выполнение и извлечение данных.
Расписание извлечения данных по ссылке
В итоге на текущий момент вопрос извлечения данных их внешнего сервиса и сохранения в базе данных решен, но необходимо вспомнить, что у нас есть период в N дней, когда данные могут меняться, это значит, что за эти N дней данные не должны сохраняться в базе данных на постоянной основе. Как решена эта проблема?
Скрипт выгрузил данные в таблицу в базе данных с заданного дня до сегодня минус N дней, этот период не подлежит изменению. В эту таблицу ежедневно будут вноситься данные за день, который рассчитывается по формуле: сегодня — N дней.
За период с N дней назад по вчера, данные также извлекаются в базу данных, но в другую таблицу, которая ежедневно обновляется и содержит актуальные данные. Чтоб получить данные по всем заказам в базе данных создано представление, которое объединят данные из постоянной таблицы и таблицы с временными данными. Именно к нему в итоге подключается PowerBI для построения модели данных.
Используемые технологии
Все, что связано с развертыванием скрипта, написанного на Python, и его автоматического запуска настроено на платформе Google Cloud, внешним хранилищем для обоих таблиц является Big Query, построение финального отчета PowerBI.
Скрипт ответственный за выгрузку данных
Благодаря тому, что в Google Cloud происходит автоматический запуск скрипта и перенос сведений из внешней системы в Big Query, а PowerBI имеет встроенный коннектор к этой базе данных, отчет ежедневно обновляется в облаке и к началу рабочего дня сотрудники имеют актуальные данные по прошедшие сутки включительно.
Результат автоматизации
В результате проделанной работы автоматизирована ежедневная задача, отнимающая у сотрудника время, которое он мог потратить более эффективно с пользой для проекта.
Данные доступны в отчете в удобном виде, демонстрируют сведения в едином пространстве из двух источников, в процессе работы над визуальной частью созданы новые метрики, которые ранее не использовались и теперь отображают конверсию лидов в сделки в различных статусах. Использование фильтров позволяет проанализировать качество лидов в различных комбинациях, а возможность получить данные из CRM системы и данные из внешней помогает найти ошибки в интеграции между системами, а также в сборе данных.
Стоимость использования полученного решения
В заключении приведу информацию о том, сколько стоит обработка данных описанным способом:
Расходы на работу системы
Это затраты связанные с работой скриптов извлекающих данные их первоисточника и переносящих их в Big Query, в них не отражены затраты по использованию самого хранилища, т.к. помимо описанных выше таблиц в нем хранится много другой информации и выделить небольшой объем данных их общего массива не представляется возможным. Но исходя из практики эта сумма или равна нулю, или очень близка к этому значению
