Last modified January 14, 2021 by Shelly Wolfe

How do I export Swrve KPI data into Excel using the Export API?

Swrve’s Export API provides a basic set of per-KPI function calls that can be combined with the Excel Web Query feature to enable a direct pull of KPI information into Microsoft Excel spreadsheets.

Prerequisites

You need the following to get started:

Your API key and personal key. These are provided for each specific app and each user you have set up with the Swrve service.
Swrve’s Export API documentation
A copy of Microsoft Excel on your Mac or PC, and access to the Queries folder (On a Mac, this is in Microsoft Office 2011 > Office > Queries).

This article references Swrve’s URLs for all data and content stored in both our US and EU data centers. Click the relevant tab based on your app configuration. For more information, see How do I configure the Swrve SDK for EU data storage?

Create Web Query text files

Once you have what you need to start, create a sample Web Query file in the queries folder. Each specific query you plan to pull into a section of your Excel file requires a separate Web Query text file. The syntax of each file is nearly identical and many of the Excel parameters in these files are undocumented or have undocumented impacts on the outcome of the query, or both. Do not change them unless you know what you are doing.

The key part of each file is the Swrve URL that requests the data for a specific KPI from your app’s dashboard. The following is an example for determining the daily active users (DAU) for the entire year of 2021 so far (assume this file is called Get_DAU_for_MyApp):

WEB 1 https://dashboard.swrve.com/api/1/exporter/kpi/dau?personal_key=YOUR_PERSONAL_KEY_HERE&api_key=YOUR_API_KEY_HERE&start=2021-01-01

Selection=10,20
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False

WEB 1 https://eu-dashboard.swrve.com/api/1/exporter/kpi/dau?personal_key=YOUR_PERSONAL_KEY_HERE&api_key=YOUR_API_KEY_HERE&start=2021-01-01

Selection=10,20
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False

Create an Excel file

Create an Excel file and choose a specific cell you want to begin populating with the DAU data.

The Web Query destroy cells in the general region of the cell you select, depending on the Excel settings you can change. Be aware of these settings or at least enter an extra few blank rows above and below the cell you select. All cells to the left and right are also overwritten depending on the settings.

Bind the Query

When you have selected a cell, select Data from the main menu (not the Data tab in the toolbar). Select Get External Data from the Data menu, and select Run Saved Query. This posts a file selection dialog for the Query folder. Select the Get_DAU_for_MyApp.txt file and respond to the dialog Excel posts that enable you to determine where and how the data is inserted.

Alter Query behavior

If you click on the cell that you used to insert the query, you can modify its behavior. Once you have selected the cell with the query, you can change its properties by returning to the Data menu and selecting Refresh Data, or by selecting Data > Get External Data > Data Range Properties.

Create more Queries

You can easily create many Web Queries that invoke different Swrve Export API calls, though each must be in a separate file. The following are some examples. Consult the Export API documentation for API details, specifications and limitations.

Get the DAU for the year so far:

https://dashboard.swrve.com/api/1/exporter/kpi/dau?personal_key=&api_key=&start=2021-01-01

https://eu-dashboard.swrve.com/api/1/exporter/kpi/dau?personal_key=&api_key=&start=2021-01-01

Get the monthly active users (MAU) for the year so far:

https://dashboard.swrve.com/api/1/exporter/kpi/mau?personal_key=&api_key=&start=2021-01-01

https://eu-dashboard.swrve.com/api/1/exporter/kpi/mau?personal_key=&api_key=&start=2021-01-01