Using External Data Sources for KPIs

Learn how to automate data input from third-party business tools to KPIs in BSC Designer with Google Sheet Add-ons and RESTFull API.

General Information

An account provides 100 API calls per hour per power user. To update KPIs automatically, use one of these methods:

  • BSC Designer Sync. A Google Sheets Add-on, suitable for writing data to KPIs, no programming required.
  • RESTFull API. Programming interface with API calls, suitable for reading and writing data to KPIs, programming required.

Connect KPIs to Data Sources via Add-Ons

Use Google Sheets Add-ons to obtain information from external data sources, aggregate data, and update KPIs with aggregated data automatically.

  1. Create Google Sheets document.
  2. Select the menu Extensions > Add-ons > Get add-ons.
  3. Install and configure one of the add-ons for a third-party data source.
  4. Install add-on called BSC Designer Sync (will appear on the right toolbar).
BSC Designer add-on for Google Sheets BSC Designer Sync's use and transfer to any other app of information received from Google APIs will adhere to Google API Services User Data Policy, including the Limited Use requirements.

Initial Setup

  1. Click the API Key command to enter a valid API key (create a new API key in BSC Designer via > Settings > Integrations > Add API Key).
  2. Click the Add KPI command to select a scorecard and KPI from your account.
  3. Click Insert.
  4. A sheet BSC Designer Sync will be created.
  5. Repeat steps 2-3 to add more KPIs.
BSC Designer Sync sheet was created and a KPI

Aggregating Output of Data Source Add-on

Most data sources will provide unaggregated data. For example, an add-on for social media returns daily engagement data with Date column and number of Likes column for each of the dates.

Aggregating value from a data source by last month

To get an aggregated data for the last month, use the formula:

  • =SUM(FILTER(B:B,A:A > (TODAY() - 30)))

where A is a column with dates, and B is a column with values for each date (exclude texts, such as column name, from the range if needed).

Updating KPI with an Output of Data Source Add-on

Using IMPORTRANGE formulate to enter aggregated data into the KPI

To use the aggregated data as an input for a KPI:

  1. Switch to BSC Designer Sync sheet.
  2. Find New KPI Value column for the destination KPI.
  3. Use IMPORTRANGE formula for the New KPI Value cell.
  4. Click the Sync command to start manual synchronisation or click the Schedule command to automate synchronisation.

The KPI in the BSC Designer scorecard will be updated according to the update interval configured for the indicator.