We’ve migrated our documentation to a new site, which means some URLs have changed.
Audience
Audience
Breadcrumbs

Working with Excel

Introduction

Microsoft Excel can be used together with the Piano Insight API in several ways, e.g.:

  • As a dictionary management system. The platform can be customized to make use of customer-specific dictionaries (e.g., for applying custom taxonomies or for various search features), and these dictionaries can be supplied as Excel workbooks. API methods for this are described, e.g., here. 

  • As a "GUI" over the API. I.e., data emitted by the platform can be represented as tables in Excel, where the data can be further manipulated (e.g., filtered or joined with other tables) or graphed. How to do this is the main topic of this page.

Power Query

In the following, we'll assume that you have a Microsoft Excel installation running on Windows that is recent enough to be able to run the Power Query add-in for Excel. This add-in was previously known as Data Explorer.

The steps required for importing data from the Piano Insight API into Microsoft Excel are as follows:

  1. Download and install the Power Query add-in for Excel.

  2. Prepare a persisted authenticated request that retrieves the data you want to have imported into Excel. The purpose of this step is to enable the Power Query add-in to retrieve the data from the API without authentication getting in the way. You can create a persisted authenticated request in two different ways:

    1. Using the API and the /persisted/create API method.

    2. Using the GUI available at https://audience-insight.piano.io/ under the tab named "API". In the GUI, a persisted authenticated request is referred to as a "stored query".

  3. Having prepared a persisted authenticated request, you now have an identifier that can be used together with the /persisted/execute API method. In the following, let's assume that this identifier is abc123.

  4. In Excel, go to the Power Query add-in and select "From Web".

  5. In the dialog that appears, paste in the URL https://api.cxense.com/persisted/execute?persisted=abc123 (replacing abc123 with your own identifier). You can also add parameters to the URL, if you when you created the persisted request specified that the parameters were mutable. 

  6. Hit the "OK" button and follow the import wizard to shape and transform the API's JSON response into a table.

CSV

In the following, we'll assume that you have downloaded and installed the jq command-line tool and that you're using the cx.py script.

Another option is to export data from the Insight API to a CSV file, and then import that into Excel. The Insight API returns JSON, but tools like jq can be used to conveniently transform this to CSV format.

$ python cx.py /traffic/event '{"start":"-7d","siteId":"246808642","groups":["city","region","country"],"count":3 }' | jq -r ".groups[]|.group as $groupname|.items[]|[$groupname,.item,.data.events]|@csv"
"region","rio de janeiro",2037
"region","hubei",2016
"region","london",1986
"country","us",5212
"country","cn",4048
"country","au",4030
"city","wuhan",2016
"city","london",1986
"city","zurich",1985

$ python cx.py /segment/read '{"siteGroupId":"123456789"}' | jq -r ".segments[]|if .active then [.id,.name] else [] end|@csv"
"12ftof3bog7u","UK Users"
"hg8940e4tb6c","UK Business Users"
"k0iq40e4tb6d","UK Tech Readers"
"99gg7jpwxyh1","UK Practice Users"

# Here we merge the nested JSON response for history timestamps,
# events and unique user counts into a single table.
$ python cx.py /traffic '{"start":"-6h","siteId":"246808642","fields":["events","uniqueUsers"],"historyFields":["events","uniqueUsers"],"historyResolution":"hour"}' | jq -r ".historyData as $hd| range(0; $hd.events|length) as $idx | [.history[$idx], $hd.events[$idx], $hd.uniqueUsers[$idx], $hd.sessionStarts[$idx]]|@csv"
1425014990,15424,4318,
1425018590,22019,6547,
1425022190,24026,7248,
1425025790,21008,6989,
1425029390,19716,7477,
1425032990,18759,7278,


Last updated: