У нас новая версия — retailCRM 6.0 Хочу подробности

Блог

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

Олег Басманов работает веб-аналитиком в интернет-магазине toptop.ru. Он поделился опытом интеграции retailCRM c Microsoft Power BI.
retailCRM 5 мая 2017 года

В работе нашего интернет-магазина используется 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