Linking OpenCRM to Microsoft Power BI for CRM Reporting 

30 Oct 2018

Power BI is a great tool for analysing and presenting your businesses data, but that data needs to come from somewhere. Depending on what you are analysing, your data could come from a whole range of sources, accounts systems, sales platforms, CRM systems and could even 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, covering areas 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, while there is functionality for outside data sources, this can be achieved through custom integrations.

However, OpenCRM do provide an API, originally design as an integration for ClicData. Since ClicData is a paid service, and we were already using Power BI, I wondered if it was possible to use the openCRM API to get data from our CRM system into Power BI for analysis. With a little help from the OpenCRM support team, I managed to get all the data I needed into Power BI 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.

Note: although the API activation is free, you’ll need to discuss your usage requirements with your OpenCRM Account Manager to see if it will cost you anything extra to use. 

 

Linking OpenCRM Reports to Power BI 

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 Power BI reports as the OpenCRM API allows reports to be used as an endpoint. OpenCRM uses a REST API and outputs JSON that Power BI can convert to tables, allowing simple data manipulation from power query or within Power BI 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 give the new leads created in the last 30 days, along with some associated data. 

Start by navigating to the OpenCRM Reports module 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 Power BI you should pick Tabular Report to avoid subtotal rows. Pick the Columns you want included in the report:

OpenCRM Report Column Select 

 

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: 

OpenCRM Report Specify Criteria 

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 Power BI. 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 Power BI

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

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 “Authorisation” 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 Power BI.

One of the best part of this connection is that, since OpenCRM is Cloud based, once you’ve created your reports in Power BI desktop, you don’t need to have an on premises gateway set up to maintain the data link. Just publish your report to the Power BI service and you can use your visualisations in Power BI dashboards.

As long as you schedule a refresh from Power BI online you will get up to date data in your dashboards! Once the data is in Power BI 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.