Before being able to use the Analytics API, you've to enable access. Learn more about it on this page.

Install Tablib

You will need to install Tablib for the example below, which is a very lightweight library for managing various data file types. You can install Tablib with xlsx support by running both of these commands:

  • pip install tablib
  • pip install tablib[xlsx]

The code also uses standard libraries for JSON and requests, which will be present in the latest versions of python.

Check for statusUrl vs. resultUrl

When making a call to create a calculation, the response may contain an element of either (but not both) of the following:

  • resultUrl - this means that the calculation is already done, and you can call the URL here to get the data.
  • statusUrl - this means that we are waiting for a result, and need to check back later by calling the statusUrl.
    • As soon as the result is ready, you will get the resultUrl element returned.

The example below converts the response text to a Python dictionary, and hence looking up an element can throw an exception. Using try...except when retrieving an element from the dictionary will protect against problems.

Please be sure that you have the correct upper and lower limit set. When you copy the JSON from Mapp Intelligence, the upper limit will stay the same as it is onsite. If you would like to increase it, you can either change it before you copy the JSON or after in the JSON itself. Mapp can produce results with 5 million rows, but limits may apply depending on your consuming system (e.g. excel). In Python, this is the "upperLimit" value. 


Here is this example as a file: AnalyticsAPItoExcel.py

import json         # standard
import requests     # standard
import time         # standard
import tablib       # see https://pypi.org/project/tablib/

# credentials (add your own here)
user = 'XXX'
secret = 'YYY'
baseurl = 'https://intelligence.eu.mapp.com'

# connect and authorize
url = baseurl + "/analytics/api/oauth/token"
querystring = {"grant_type":"client_credentials","scope":"mapp.intelligence-api"}
try:
    response = requests.request("POST", url, auth=(user,secret), params=querystring)
except:
    print("failed to connect and authorize")
    exit()

# something went wrong
if response.status_code != 200:
    print("Autorization returned - ", response)
    exit()

# get the token (make a dictionary using json, then extract the actual token)
values = json.loads(response.text)
token = values["access_token"]

# we can now build a "Create Calculation" call adding the token to the headers
url = baseurl + "/analytics/api/analysis-query"
headers = {'Authorization': 'Bearer ' + token,
           'Content-Type': "application/json"}

# the json payload - the query we are trying to run
# this can be retrieved from the 'copy JSON' menu in Intelligence
payload="{\r\n    \"resultType\": \"DATA_ONLY\",\r\n    \"queryObject\": {\r\n        \"columns\": [\r\n            {\r\n                \"name\": \"session_id\",\r\n                \"scope\": \"OBJECT\",\r\n                \"context\": \"SESSION\",\r\n                \"variant\": \"NORMAL\",\r\n                \"lowerLimit\": 1,\r\n                \"upperLimit\": 500\r\n            },\r\n            {\r\n                \"name\": \"pages_pageImpressions\",\r\n                \"columnPeriod\": \"ANALYSIS\",\r\n                \"scope\": \"OBJECT\",\r\n                \"context\": \"PAGE\",\r\n                \"variant\": \"NORMAL\"\r\n            },\r\n            {\r\n                \"name\": \"order_value\",\r\n                \"columnPeriod\": \"ANALYSIS\",\r\n                \"scope\": \"OBJECT\",\r\n                \"context\": \"ACTION\",\r\n                \"variant\": \"NORMAL\"\r\n            }\r\n        ],\r\n        \"variant\": \"LIST\",\r\n        \"predefinedContainer\": {\r\n            \"filters\": [\r\n                {\r\n                    \"name\": \"time_dynamic\",\r\n                    \"filterPredicate\": \"LIKE\",\r\n                    \"connector\": \"AND\",\r\n                    \"caseSensitive\": false,\r\n                    \"context\": \"NONE\",\r\n                    \"intern\": false,\r\n                    \"value1\": \"last_90_days\",\r\n                    \"value2\": \"\"\r\n                }\r\n            ],\r\n            \"containers\": []\r\n        }\r\n    }\r\n}"

# request the query
# - if the response contains "resultUrl" then it's complete already
# - if the response contains "statusUrl" then we need to call that to check back when it is finished
try:
    response = requests.request("POST", url, data=payload, headers=headers)
except:
    # something went wrong
    print("Create calculation call failed")
    exit()

# something went wrong
if response.status_code > 201:
    print("Create Calculation returned - ", response)
    exit()

# unpack the response
values = json.loads(response.text)

try:
    resultUrl = values["resultUrl"]
except:
    resultUrl = ""

try:
    statusUrl = values["statusUrl"]
except:
    statusUrl = ""

# now we can call for the result...
tries = 0
while True:
    try:
        if resultUrl != "":
            # we have a result already, just retrieve it and exit the loop
            response = requests.request("GET", resultUrl, headers=headers)
            break
        else:
            # wait for a short time (optional)
            sleep(1)

            # is it done yet?
            # call the status URL, and refresh the values of the URLs
            response = requests.request("GET", statusUrl, headers=headers)
            values = json.loads(response.text)              
            try:
                resultUrl = values["resultUrl"]
            except:
                resultUrl = ""
           
            tries +=1
    except:
        print("failed to fetch the result")

    # give up after trying an arbitrary number of times
    if tries == 10:
        print("tried several times, didn't get result")
        exit()

# you will need to install tablib for this example
# - pip install tablib (see https://pypi.org/project/tablib/)
# ...create a tablib data set to build excel file
ds = tablib.Dataset()

# convert string into a python dictionary
data = json.loads(response.text)

# walk through headers
headers = []
for item in data["headers"]:
    headers.append(item["alias"])

# add the headers we just retrieved to the data set
ds.headers = headers

# walk through the rows
for item in data["rows"]:
    # build a tuple for each row
    tuple = []
    for row in item:
        tuple.append(row)

    # add the row to the data set
    ds.append(tuple)

# write it as an excel file
with open('results.xlsx', 'wb') as f:
    f.write(ds.xlsx)
    f.close()
PY

Please note that onsite, if there are no values returned, you will see a 0. However, When using an API this will return as null. 

Technical Documentation