How can I connect PoliteMail data to PowerBI?

Connecting to PoliteMail Data via the API

PoliteMail can provide you with a starting PowerBI or Excel file to get you started on connecting to your PoliteMail data via the Application Protocol Interface (API).

With a few steps, you can connect your extensive PoliteMail measurement data to PowerBI or Excel.   From there, the data can be further analyzed, shared, or used to create a real-time dashboard.

What exactly is PowerBI?

PowerBI, or Business Intelligence, is a product from Microsoft.  It is designed to pull in data from a number of sources in real-time to create a “master dashboard”.  This is great for executives who want a live snapshot of company KPIs, or to share data with others without having to give them full access.

This article is not intended to teach you how to use PowerBI, but rather only how to connect to the PoliteMail API.  There are many online resources, both from Microsoft as well as third parties, to learn how to use the application.  More information on the PoliteMail API may be found here.

Graphical user interface, application, Word

Description automatically generatedConnecting PoliteMail to PowerBI

  1. From PowerBI, create a new dashboard or open a .pbix file you already have.  Click on Get data > OData Feed.
  2. A window will appear to enter server URL. Enter,

Graphical user interface, text, application

Description automatically generatedwhere “hostname” is the name of your PoliteMail server.

Don’t forget to enter “https” before your domain, and “/api/odata” at the end!

  1. You will be prompted to enter your credentials (if not, refresh the data by going to Home > Refresh).

Graphical user interface, text, application

Description automatically generated

  • You have three ways to do this:
    1. Preferred Method. The best method is to use OAuth.   Click on ‘Organizational account’, and then click “Sign In”, which will use your Windows credentials to authenticate to the API.
    2. If you do not use OAuth, we recommend you go to the PoliteMail settings to create a Personal Access Token.  Copy this token and then return to PowerBI.  You can click on ‘Basic’ and enter the token as your password.  You can enter anything as the user (or even leave it blank), but we recommend using a standard username such as “api” for consistency.
    3. You could also just use your PoliteMail credentials in the ‘Basic” tab.  However, this is not recommended if you are sharing the data with external users as you risk exposing your credentials to others.  
  1. You’re in! Select the different tables you wish to access from the Navigator window. The most likely one to use is SentMessages, but there are a lot to choose from. We have provided some sample visualizations to get you started.

Connecting PoliteMail to Excel

Graphical user interface, application

Description automatically generatedIf you do not have PowerBI, you can still access PoliteMail data from Excel, and the process is very similar.

  1. Open the .xlsx file we provided to you in Excel.
  2. Click on Data > Get Data > From Other Sources >From OData Feed. From there, continue with Step 2 in the PowerBI section above.