Как импортировать данные из retailCRM в PowerBI

В работе нашего интернет-магазина используется retailCRM. А для анализа эффективности работы магазина используем PowerBI. Система собирает данные из различных источников, объединяет их, и представляет в удобном для принятия решений виде. Для тех, кто не использует PowerBI, я покажу пару отчетов.

Так мы узнаем долю заказов по статусам в динамике по месяцам. Если вдруг доля возвратов начнет расти от месяца к месяцу, мы это вовремя заметим.

А на этом графике мы отслеживаем динамику конверсии по месяцам.

Все данные из retailCRM также подтягиваются в PowerBI. Первоначально при построении модели, данные выгружали с помощью csv файлов. Но это решение неудобно так как требовало ежедневной ручной выгрузки файлов. Впоследствии мы перешли к загрузке данных посредством API.

Подготовка к импорту

Для отправки запроса к API retailCRM нам понадобится ключ API. Чтобы его получить нужно перейти в раздел Администрирование, далее Интеграция, Ключи доступа к API и создать новый ключ.

Отметьте галочками нужные вам методы API. Например мне были необходимы только 2 метода:

• /api/orders — для получения информации о заказах
• /api/customers — для получения информации о клиентах

Создаем пользовательскую функцию.

В Power Query создаем пустой запрос и вставляем в него следующий код:

(page as number) as table =>
Let
/*получаем дату вчерашнего дня */
    yesterday = Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1),"yyyy-MM-dd"), 
/* отправляем запрос к Retail CRM */
    Source = Json.Document(Web.Contents("https://demo.retailcrm.ru/api/v4/orders?filter[createdAtTo]="&yesterday&"&page=" & Number.ToText(page) & "&limit=100&apiKey=xxxxx")), 
/* полученный ответ конвертируем в таблицу */
    orders = Source[orders],
    #"Converted to Table" = Table.FromList(orders, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #" Converted to Table

Сохраняем полученную функцию и называем ее удобным именем, например у меня это RetailCRMOrderAPI

1. На первом шаге переменной yesterday присваивается дата вчерашнего дня. Мне это было необходимо чтобы в запросе в качестве фильтра указать что данные должны быть за весь период кроме текущего дня.
2. Далее с помощью функции Web.Contents отправляется GET запрос и указывается ссылка для запроса.

Source = Json.Document(Web.Contents("https://demo.retailcrm.ru/api/v4/orders?filter[createdAtTo]="&yesterday&"&page=" & Number.ToText(page) & "&limit=100&apiKey=xxxxx")), 

• В ссылке вместо demo.retailcrm.ru нужно подставить ваш адрес RetailCRM. Для каждого метода API используется свой вариант ссылки.

• Также в ссылку подставляется параметр &page значение которого указывает какую страницу ответа нужно загрузить. Дело в том что, если ответ на ваш запрос содержит большое число строк, то этот ответ разбивается на страницы.

• Параметр limit указывает какое количество строк должно содержаться на странице

• apiKey — указываем созданный вами ключ API

Все варианты фильтров можно посмотреть в справочнике методов, а общую информацию о работе с API можно посмотреть в правилах работы с API.

3. Далее полученные данные конвертируются в таблицу На этом подготовка завершена и можем приступать к импорту.

Импорт данных из retailCRM в PowerBI

Алгоритм следующий:

1. Делаем первый запрос и из ответа сохраняем в переменную TotalPageCount количество страниц содержащихся в ответе на наш запрос
2. Создаем таблицу с одним столбцом от 1 до TotalPageCount
3. Создаем пользовательский столбец из нашей функции RetailCRMOrderAPI в которую в качестве параметра передается значение страницы
4. Преобразовываем полученную таблицу.

Теперь создаем новый пустой запрос со следующим кодом

 let /*получаем дату вчерашнего дня */ yesterday = Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1),"yyyy-MM-dd"), /*делаем первый запрос для получения информации о количестве страниц содержащихся в ответе/ Не забываем подставить ваш адрес CRM и ключ API*/ Source = Json.Document(Web.Contents("https://demo.retailcrm.ru/api/v4/orders?filter[createdAtTo]="&yesterday&"&page=1&limit=100&apiKey=00000")), /* сохраняем полученный полученное количество страниц в переменную TotalPageCount */ #"Converted to Table" = Record.ToTable(Source), Value = #"Converted to Table"{1}[Value], #"Converted to Table1" = Record.ToTable(Value), #"Filtered Rows" = Table.SelectRows(#"Converted to Table1", each ([Name] = "totalPageCount")), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type number}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Name"}), TotalPageCount = #"Removed Columns"{0}[Value], /* создаем таблицу с одним столбцом с числами от 1 до TotalPageCount */ #"Page" = List.Numbers(1,TotalPageCount), #"Converted to Table2" = Table.FromList(Page, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table2",{{"Column1", "Page"}}), /*Создаем пользовательский столбец с нашей функцией RetailCRMOrderAPI в которую как параметр передается номер страницы */ #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each RetailCRMOrderAPI([Page])), /* Преобразуем полученные данные */ #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Column1"}) In #"Expanded Custom1" 

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

Примечание: код Power Query написан для англоязычной версии Power BI Desktop, если вы используете русскоязычную версию, то скорее всего при копировании кода у вас возникнет ошибка. Это связано с тем что в русскоязычной версии в качестве разделителя между параметрами функции используется точка с запятой, а в англоязычной — запятая.

Автор: Олег Басманов — веб-аналитик в интернет-магазине toptop.ru