[Сквозная аналитика] Получаем данные из Битрикс24 в Excel с помощью Power Query
Представим следующую ситуацию: необходимо проанализировать данные из Битрикс24. Но отчет нам нужен не стандартный, который можно построить в Битрикс24, а немного другой. Например, выполненный в других разрезах. Как можно быстро решить задачу? Импортируем данные из Битрикс24, и уже в Excel построим необходимый отчет. Например, используя механизм сводных таблиц.
Но здесь возникает другая проблема — чтобы обновить отчет через некоторое время, нужно будет заново выгружать данные из Битрикс24. А можно ли сделать так, чтобы загрузка информации из Битрикс24 в Excel производилась автоматически?
Можно. Для этого мы будем использовать API Битрикс24 и специальную надстройку для Excel — Power Query.
Подключаемся к Битрикс24 через API
Итак, задача следующая: нужно получить в Битрикс24 список лидов из тестовой Битрикс 24. Подключаться к Битрикс24 мы будем используя Вебхуки. Но при этом не стоит забывать о том, что данный механизм нужно использовать достаточно аккуратно.
Итак, настраиваем входящий вебхук. Права доступа указываем «CRM (crm)».
Для получения списка лидов из Битрикс 24 мы будем использовать метод crm.lead.list. Но у данного метода есть определенные ограничения, а именно: он «отдает» только первые 50 записей.
При этом у Битрикс24 есть ограничения на количество запросов с приложения — не более 2 запросов в секунду. Если с вашего приложения слишком часто идут запросы, то Битрикс24 с целью снижения нагрузки может «забанить» ваше приложение.
Но с другой стороны, в API Битрикс24 есть специальный метод batch, внутри которого можно использовать необходимое количество вызовов метода crm.lead.list.
Код на PHP будет выглядеть следующим образом:
<?
$records = 50;
$result = '';
getLeads($records);
function getLeads($record_count) {
$batch = array();
for ($i = 1; $i <= ceil($record_count / 50); $i++) {
$batch['get_'.$i] =
'crm.lead.list?'.http_build_query(
array(
"start" => '$next[get_'.($i-1).']'
)
);
}
$leads = executeHook(array('cmd' => $batch));
//print_r($batch);
}
function executeHook($params) {
$queryUrl = 'https://"Ваш битрикс24".bitrix24.ru/rest/1/"Ваш код вебхука"/batch.json';
$queryData = http_build_query($params);
/* echo "<pre>";
print_r($queryData);
echo "</pre>";*/
$curl = curl_init();
curl_setopt_array($curl, array(
CURLOPT_SSL_VERIFYPEER => 0,
CURLOPT_POST => 1,
CURLOPT_HEADER => 0,
CURLOPT_RETURNTRANSFER => 1,
CURLOPT_URL => $queryUrl,
CURLOPT_POSTFIELDS => $queryData,
));
$result = curl_exec($curl);
curl_close($curl);
return json_decode($result, true);
}
Отдельные пояснения по коду давать не будем, отметим лишь, что сделан на основе примера, приведенного в курсе обучения Битрикс24. Пример нам нужен был для того, чтобы понять, в каком формате необходимо формировать запросы для получения списка лидов.
Поясним лишь, что в данном случае мы прямо указали количество записей, которые необходимо получить. По правильному нужно поступить следующим образом — сначала «дергаем» метод crm.lead.list. В ответе помимо первых 50 записей будет будет прямо указано количество записей. Далее уже на основании полученных данных формируем необходимый запрос.
Реализация на PowerQuery
Работать с данными в Power Query можно с помощью специального языка программирования M. Язык относительно несложен для обучения, но есть свои нюансы.
Запрос на языке M получения данных из Битрикс24 выглядит следующим образом:
let
GetString=(x)=>
let
QueryCmd="cmd[get_"&Number.ToText(x)&"]",
QueryFilter="$next[get_"&Number.ToText(x-1)&"]",
QueryString = "crm.lead.list?start="&Uri.EscapeDataString(QueryFilter),
QueryStringURI=Uri.EscapeDataString(QueryCmd)&"="&Uri.EscapeDataString(QueryString)
in
QueryStringURI,
B24Json1 = Json.Document(Web.Contents("https://"ваш битрикс24".bitrix24.ru/rest/1/""Ваш код вебхука""/batch.json?"&GetString(1))),
NumberOfRecord = B24Json1[result][result_total][get_1], //получили количество записей
URLStringEach=List.Generate(
()=>1,
(x)=>x<=Number.RoundUp(NumberOfRecord/50),
(x)=>x+1,
(x)=>GetString(x)
),
URLString=Text.Combine(URLStringEach,"&"),
B24Json = Json.Document(Web.Contents("https://"ваш битрикс24".bitrix24.ru/rest/1/"Ваш код вебхука"/batch.json?"&URLString)),
LeadList = B24Json[result][result],
RecordToTable = Record.ToTable(LeadList),
TableExpandListColumn = Table.ExpandListColumn(RecordToTable,"Value"),
TableExpandRecordColumn = Table.ExpandRecordColumn(TableExpandListColumn, "Value", {"ID", "TITLE", "STATUS_ID"}, {"ID", "TITLE", "STATUS_ID"}),
TableRemoveColumns = Table.RemoveColumns(TableExpandRecordColumn,{"Name"})
in
TableRemoveColumns
Внесем пояснения по коду.
Работа механизма формирования url из массива в PHP и языке M отличаются между собой. Поэтому мы идем по простому пути — формируем запрос, аналогичный тому, который мы использовали в коде на PHP. Для этого мы определили функцию GetString=(x), на вход которой поступает шаг итерации, а на выходе формируется необходимая строка для метода Batch.
Основной код делает следующее.
Сначала мы в первый раз «дергаем» вебхук для того, чтобы узнать, какое количество нужных нам записей хранится в базе СРМ.
Далее на основе полученной информации с помощью функции List.Generate мы формируем список с необходимыми «подзапросами» для метода batch. Еще отметим тот факт, что в отличии от скрипта на PHP мы используем GET запрос.
С помощью Text.Combine мы объединяем полученный список в одну строку, не забывая указать & для соединения элементов списка.
Ну а дальше уже идет обработка полученной информации средствами Power Query.
Результаты работы запроса
Результатом выполнения нашего скрипта будет таблица из 3 колонок: ID, TITLE, STATUS_ID.
Почему скрипт вывел именно эти колонки? Мы это прямо указали директивой TableExpandRecordColumn = Table.ExpandRecordColumn(TableExpandListColumn, «Value», {«ID», «TITLE», «STATUS_ID»}, {«ID», «TITLE», «STATUS_ID»}). То есть в Power Query мы «подтянули» все данные, которые содержатся в Лидах Битрикс24. А фильтрацию данных осуществляли уже используя средства работы с данными Power Query.
Ну а используя в дальнейшем сводные таблицы к полученным данным, пользователь может получить отчет в нужных срезах, например, по количеству лидов в статусах.
Постойте, но эти данные можно получить средствами Битрикс24, не прибегая к использованию API? Зачем столько телодвижений? Дело в том, что наш пример учебный. И в конце работы мы можем выяснить, правильно ли мы работаем с API, все ли данные получили из Битрикс24. Сравнивая данные из нашего отчета и в Битрикс24 мы убедились, что данные получены в полном объеме.
Как улучшить запрос?
Скажем сразу, что наш запрос далек от идеала.
Во-первых, опытные пользователи наверное заметили, что первые 50 записей мы получали 2 раза — одни раз первым запросом, второй раз в цикле.
Во-вторых, в свою очередь внутри запроса batch есть ограничение в 50 «подзапросов». Таким образом, за один запрос batch можно получить данные о 2500 записях. Если нужно получить больше информации — необходимо сделать несколько запросов. В данной статье приведен скрипт для обхода данного ограничения.
В-третьих, все параметры запроса мы задаем в скрипте. При этом у Битрикса запрашиваем весь массив данных. Правильнее было бы пользователю дать возможность ввести, какие данные он хотел бы получить. Например введя их в определенных ячейках таблицы. А мы уже в запросе обрабатываем значения этих ячеек.
В четвертых, для получения данных из Битрикс24 правильнее использовать не вебхуки, а метод авторизации OAuth.
Кроме того, более подробное изучение языка M наверняка позволит более изящно манипулировать данными при формировании строки запроса к API Битрикс24. Как это реализовано в скрипте на PHP.
Все это — направления, куда можно развивать скрипт.
Выводы
Power Query — технология относительно молодая. В настоящее время идет бурное развитие, достаточно часто выходят обновления, появляются новые коннекторы для подключения к различным источникам данных.
Но даже на данном этапе, как показала практика, можно относительно несложно подключиться к практически любым API.
А получив данные из различных источников, можно построить гибкую модель данных, под конкретные потребности.
В частности, получив данные из Метрики Яндекса, данные по звонкам из системы телефонии, данные из CRM по продажам, связав полученные данные между собой, можно ответить на вопрос — какие каналы рекламы приносят прибыль. Не просто генерирует лиды и звонки, а приводят к продажам, и как следствие, к прибыли. Как оптимизировать нашу рекламу, чтобы максимизировать прибыль? А это актуальный вопрос для любого бизнеса. Ответ на него и дает система сквозной аналитики. Наша статья — лишь маленький шаг в построении такой системы.
Заказать услугу
Если вам кажется, что все написанное сложно, и этот инструмент не для вас - доверьте работу профессионалам. Заполните форму ниже, и получите персональное предложение.