[Сквозная аналитика] Получаем данные из Битрикс24 в Excel с помощью Power Query

Представим следующую ситуацию: необходимо проанализировать данные из Битрикс24. Но отчет нам нужен не стандартный, который можно построить в Битрикс24, а немного другой. Например, выполненный в других разрезах. Как можно быстро решить задачу? Импортируем данные из Битрикс24, и уже в Excel построим необходимый отчет. Например, используя механизм сводных таблиц.

Но здесь возникает другая проблема — чтобы обновить отчет через некоторое время, нужно будет заново выгружать данные из Битрикс24. А можно ли сделать так, чтобы загрузка информации из Битрикс24 в Excel производилась автоматически?

Можно. Для этого мы будем использовать API Битрикс24 и специальную надстройку для Excel — Power Query.

Подключаемся к Битрикс24 через API

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

Итак, настраиваем входящий вебхук. Права доступа указываем «CRM (crm)».

Вебхук.jpg

Для получения списка лидов из Битрикс 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.

Таблица-Excel.jpg

Почему скрипт вывел именно эти колонки? Мы это прямо указали директивой TableExpandRecordColumn = Table.ExpandRecordColumn(TableExpandListColumn, «Value», {«ID», «TITLE», «STATUS_ID»}, {«ID», «TITLE», «STATUS_ID»}). То есть в Power Query мы «подтянули» все данные, которые содержатся в Лидах Битрикс24. А фильтрацию данных осуществляли уже используя средства работы с данными Power Query.

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

сводная-таблица.jpg

Постойте, но эти данные можно получить средствами Битрикс24, не прибегая к использованию API? Зачем столько телодвижений? Дело в том, что наш пример учебный. И в конце работы мы можем выяснить, правильно ли мы работаем с API, все ли данные получили из Битрикс24. Сравнивая данные из нашего отчета и в Битрикс24 мы убедились, что данные получены в полном объеме.

Как улучшить запрос?

Скажем сразу, что наш запрос далек от идеала.

Во-первых, опытные пользователи наверное заметили, что первые 50 записей мы получали 2 раза — одни раз первым запросом, второй раз в цикле.

Во-вторых, в свою очередь внутри запроса batch есть ограничение в 50 «подзапросов». Таким образом, за один запрос batch можно получить данные о 2500 записях. Если нужно получить больше информации — необходимо сделать несколько запросов. В данной статье приведен скрипт для обхода данного ограничения.

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

В четвертых, для получения данных из Битрикс24 правильнее использовать не вебхуки, а метод авторизации OAuth.

Кроме того, более подробное изучение языка M наверняка позволит более изящно манипулировать данными при формировании строки запроса к API Битрикс24. Как это реализовано в скрипте на PHP.

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

Выводы

Power Query — технология относительно молодая. В настоящее время идет бурное развитие, достаточно часто выходят обновления, появляются новые коннекторы для подключения к различным источникам данных.

Но даже на данном этапе, как показала практика, можно относительно несложно подключиться к практически любым API.

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

В частности, получив данные из Метрики Яндекса, данные по звонкам из системы телефонии, данные из CRM по продажам, связав полученные данные между собой, можно  ответить на вопрос — какие каналы рекламы приносят прибыль. Не просто генерирует лиды и звонки, а приводят к продажам, и как следствие, к прибыли. Как оптимизировать нашу рекламу, чтобы максимизировать прибыль? А это актуальный вопрос для любого бизнеса. Ответ на него и дает система сквозной аналитики. Наша статья — лишь маленький шаг в построении такой системы.

Заказать услугу

Если вам кажется, что все написанное сложно, и этот инструмент не для вас - доверьте работу профессионалам. Заполните форму ниже, и получите персональное предложение.

Возврат к списку