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.
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.
- Create Google Sheets document.
- Select the menu Extensions > Add-ons > Get add-ons.
- Install and configure one of the add-ons for a third-party data source.
- Install add-on called BSC Designer Sync (will appear on the right toolbar).
- 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).
- Click the Add KPI command to select a scorecard and KPI from your account.
- Click Insert.
- A sheet BSC Designer Sync will be created.
- Repeat steps 2-3 to add more KPIs.
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.
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
To use the aggregated data as an input for a KPI:
- Switch to BSC Designer Sync sheet.
- Find New KPI Value column for the destination KPI.
- Use IMPORTRANGE formula for the New KPI Value cell.
- 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.