retailCRM
5 мая 2017 года

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

Олег Басманов работает веб-аналитиком в интернет-магазине toptop.ru. Он поделился опытом интеграции retailCRM c Microsoft Power BI.
В работе нашего интернет-магазина используется 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
Оцените статью
comments powered by HyperComments