powerBI is a great tool for analysing and presenting your businesses data, but that data needs to come from somewhere. Depending on what your analysing, your data could come from a whole range of sources, accounts systems, sales platforms, CRM systems and could be hosted locally or online. In this post I’ll walk through how to connect to the CRM system that my employer uses, openCRM.

CRM stands for customer relationship management and a CRM system aims to simplify and optimise your relationship with customers, whether they are resellers or end users. We use openCRM, a web-based system, to manage our customer database, as well as using it to run email campaigns and manage our technical support via the “Helpdesk” feature. There’s a bunch of other modules in openCRM, such as sales and project management, but we don’t utilise them just yet (it’s one of my future projects).

There are many advantages to a CRM system like this, it keeps all your customer data in an accessible system, allowing you to quickly track interactions through email and record meetings against customer records. These are a few of the things we use openCRM for:

  • Storing general data on customers and leads
  • Managing customer email subscriptions for marketing
  • Recording customer data processing consent from meetings and contact forms
  • Sending out email campaigns and recording clickthrough and other metrics
  • Managing technical support tickets and recording product failures

The structured way in which data is input to openCRM massively simplifies the reporting process, as entries can be restricted to certain data types and certain fields made compulsory. This forces a change to a culture of good data quality that removes the need for data cleaning before analysis.

openCRM has its own reporting system that can be utilised to present key metrics on the main dashboard, but there is limited functionality for outside data sources (although you can pay for custom integrations). However, openCRM do provide an API, originally designed as an integration for ClicData. Since ClicData is a paid service, and we were already using powerBI, I wandered if it was possible to use the openCRM API to get data from our CRM system into powerBI for analysis. With a little help from the openCRM support team I managed to get all the data I needed into powerBI and use it to create a series of reports for the MD and integrate these into our Business Overview dashboard that reports on all areas of the business in one place. edit: although the API activation is free, you’ll need to discuss your usage requirements with openCRM to see if it will cost you anything extra to use.

Linking openCRM Reports to powerBI

As I mentioned before, openCRM has its own reporting tool that can create internal dashboards for use within the system. We can also use these for our powerBI reports as the openCRM API allows reports to be used as an endpoint. openCRM uses a REST API and outputs JSON that powerBI can convert to tables, allowing simple data manipulation from power query or within powerBI itself.

1. Set up the openCRM Report

The first step is to set up a report, in this example I’ll set up a simple report that gives the new leads created in the last 30 days, along with some associated data.

Start by navigating to the openCRM reports tab and click:

Pick the module that you want to get data from, in this case leads, and also any related modules (for example you can add company data in the report for each record). Pick the report type, for use in powerBI you should pick Tabular Report to avoid subtotal rows. Pick the Columns you want included in the report:

Columns from openCRM Reports

then you can skip forward to the “Specify Criteria” Tab and pick the time period for the “Created Date”, for this report we’ll go for 30 days:

Filter for Report

Now scroll to the bottom of the page and click “Save and Run”. Give your report a name and save it. Now we need to get the report ID, to find it, look in the URL, which looks like this:

https://yourcrm.opencrm.co.uk/index.php?module=Reports&action=SaveAndRun&record=32

As you can see the report ID is included in the final part of the URL. You’ll need this ID to set up the integration for powerBI. This report will stay updated with the latest information from your database, we now need to access it from powerBI using the API.

2. Add a data source for the openCRM report in powerBI

The openCRM report is added to powerBI as a “Web” source. Open powerBI, click “Get Data” and navigate to the web source.

powerBI Get Data interface
Select the “Web” data source

In the window that appears, click the advanced radio button, you have several fields to fill, in the URL parts field, add the API endpoint for the report. You can get a list of API endpoints on this page. The report endpoint looks like this:

https://yourcrm.opencrm.co.uk/api/rest/get_record_list_from_report/reportid/{report id}

In the HTTP Request Headers enter the headers as key1 and key2 and the values as the API keys (you’ll need to ask openCRM to activate the API for the API keys).

You also need to add your admin login details to the “Authorization” header in a certain format: <encoded username : password>

It should look like this:

How to fill out the “Web” data source fields.

Clicking OK takes you through to the authorisation options, for this you just need to leave authorisation as anonymous as the authorisation is handled by the API key and authorisation in the headers. After you’ve completed this step you’ll be taken to your data in power query. This data is in JSON format, but it can easily by converted to a table using the To Table button:

Button to covert JSON to table

Finally, expand the columns and select the data you want to keep:

Expand columns

3. Create visualisations and analyse your data

You can now manipulate the data in power query, adding filters and additional columns as required, before saving the query and using the data in powerBI. One of the best part of this connection is that, since openCRM is cloud based, once you’ve created your reports in powerBI desktop, you don’t need to have an on premises gateway set up to maintain the data link. Just publish your report to the powerBI service an you can use your visualisations in powerBI dashboards. As long as you schedule a refresh from powerBI online you will get up to date data in your dashboards! Once the data is in powerBI you can also link it to other data sets and refine it, for example, you can add a region for each contact using the postcode to region table in this post.

If you have any questions, leave a comment and I’ll do my best to help out.