Update your KPI tracker on demand directly in Excel

  • 1 minute(s) read
Prev Next

This video demonstrates how to update your Mapp Intelligence Analysis directly within Excel using the Intelligence API.

The exact paths and designations may differ depending on your Excel version.

All the steps we mentioned in the video are listed below.

Before anything, you must have the API activated for your Mapp Intelligence Account. If not already active, please reach out to your CSM.

Step

Navigation path

Description

1

User Management > Roles > Account Configuration

Make sure the user has permission to access the API.

  1. Go to User Management > Roles, and select the role that should have API access.

  2. Enable Intelligence API.

2

Settings > API Client IDs

  1. Click the configuration symbol, then click API Client IDs.

  2. Select Create New Client ID

  3. Add a description, such as the user's name.

  4. Copy the Client ID and Secret to your clipboard and keep them somewhere safe! You will need them any time you use the API.

Find more information here.

3

Visitors > Time > Weeks

  1. Go to Visitors > Time > Weeks and add Visits, Qty Orders, Conversion Rate %.

  2. Set in the calendar dynamic time frame Current Year until Yesterday.

  3. In the upper right-hand corner, select the menu and Copy JSON Configuration.

  4. Paste the JSON into an editor.

  5. Replace all the single quotation marks (") with double quotation marks ("").

4

Excel file configuration

  1. JSON_API_powerBI_demo_version1_5
    28.66 KB

    and open the example sheet.

  2. Enable external data connections  

  3. Under the Data tab, select Get Data > Launch Power Query Editor.

  4. Change Privacy Settings

    • Window OS:

      1. Go to File > Options > Settings > Query Options > Current WorkBook > Privacy

      2. Under Privacy Levels, Select Ignore the Privacy Levels and Potentially Improve Performance. Please note this could potentially expose sensitive data.

    • Mac OS:

      1. Go to Options > Project > Privacy

      2. Activate Allow combining data from multiple sources

  5. Right-click on getToken to use the advanced editor.

  6. Replace the placeholders with your clientID and secret.

  7. In getAnalysisData, right click to get the advanced editor.

  8. Paste your JSON into queryAsJson = "< your JSON >".

    Depending on the Excel version, you may see hint boxes or a loading hint. In most cases, this does not affect the functionality, so you can simply continue.

  9. Click Close and Load.

5

Excel file update

Click Data > Refresh All to update data.

  • In our example file, a new token is automatically requested every time you refresh data. You'll need a new token to refresh the data only if you use a different file.

Because your ID and Secret are saved in the file, be careful about sharing it directly with others.