5 May 2017

How to import data from retailCRM to PowerBI

Oleg Basmanov is a web analyst in online store. He has shared his integration experience of retailCRM with Microsoft Power BI.
In the work of our online store is used retailCRM. And for analysis of the store efficiency we use PowerBI. The system collects data from various sources, unites it and presents in a convenient form for decision making process. For those, who do not use PowerBI, I will show several reports.
In such a way we get orders share by statuses in months dynamics. If the share of returns suddenly starts its growth from month to month, we notice it on time.
And on this graph we monthly monitor conversion dynamics.
All data from retailCRM is also pulled into PowerBI. Initially, when creating the model, data was uploaded via csv files. But this solution was inconvenient because it required daily manual files uploading. Later, we proceeded to uploading the data via API.

Preparing for import

In order to send a query to retailCRM API, we need an API key. To get it, open "Administration" section, then "Integration", "API access keys" and create a new key.

Mark the necessary for you API methods allowed. For example, I needed only 2 methods:

  • /api/orders — for order information
  • /api/customers — for customer information

Create a user function.

Create an empty query in Power Query and paste the following code into it:
(page as number) as table =>
/*get a yesterday date */
    yesterday = Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1),"yyyy-MM-dd"), 
/* send a query to retailCRM */
    Source = Json.Document(Web.Contents("[createdAtTo]="&yesterday&"&page=" & Number.ToText(page) & "&limit=100&apiKey=xxxxx")), 
/* convert the received data into the table */
    orders = Source[orders],
    #"Converted to Table" = Table.FromList(orders, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    #" Converted to Table"
Save the received function and give a convenient name to it, for example, I have retailCRMOrderAPI
1. As the first step, yesterday date is assigned to variable "yesterday". For me it was necessary in order to specify in query filter that the data should be for the whole period except the current day.

2. Then, using Web.Contents function, GET query is sent and a reference for the query is specified.
Source = Json.Document(Web.Contents("[createdAtTo]="&yesterday&"&page=" & Number.ToText(page) & "&limit=100&apiKey=xxxxx")),
  • In the link instead of, you need to paste your retailCRM address. For each API method its own reference is used.
  • Also, parameter "& page" is added in the reference. Its value indicates which response page you would like to upload. The matter is in that if your response contains a large number of lines, then this response is divided into pages.
  • The "limit" parameter specifies how many lines should be on the page
  • apiKey — specify API key created by you

You can look at all filter variants in the reference book and at general information on working with API in libraries for working with API.

3. The received data is converted into the table further. This completes the preparation and one can start importing.

Data import from retailCRM to PowerBI

The algorithm is the following:

  1. Make the first query and from the response save in the TotalPageCount variable the number of pages contained in response to your query.
  2. Create a table with one column from one to TotalPageCount.
  3. Create a user`s column from our retailCRMOrderAPI function in which page value is transmitted as a parameter.
  4. Convert the received table.

Now create a new empty query with the following code
/*get a yesterday date */
   yesterday = Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1),"yyyy-MM-dd"),

/*make the first query for information about the number of pages contained in response / Do not forget to paste your retailCRM address and API key*/
    Source = Json.Document(Web.Contents("[createdAtTo]="&yesterday&"&page=1&limit=100&apiKey=00000")),

/* save the received number of pages in the TotalPageCount variable */
    #"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],

/* create a table with one column from one to 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"}}),

/*Create a user`s column with our retailCRMOrderAPI function in which page value is transmitted as a parameter */
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each RetailCRMOrderAPI([Page])),

/* Convert the received data */
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Column1"})
In #"Expanded Custom1"
Then we carry out further transformations of the tables and make up the necessary reports.

Note: the Power Query code was written for the English version of Power BI Desktop. And if you use the Russian version, it's likely that when copying the code, you will have an error. It is connected with that the Russian version has semicolon as a separator between the function parameters, while in English version it is comma.
Author: Oleg Basmanov, web analyst in online store