The Export API feature in Magento Business Intelligence is a powerful tool that allows you to extract data in your account and apply it to your specific business needs. While some documentation already exists in our Help Center, the goal of this post is to dive deep into some details, and outline some business use cases.

Have you ever needed to send your raw data to another integration? Or maybe you’re trying to manipulate that data in a way that’s not currently available in Magento BI. One request we often receive, for example, is to segment customer data to empower an email marketing service. How do we start collecting the right data so we can optimize our business and improve on metrics?

In this blog post we’ll walk through the steps on how to start using the Export API and automating tasks — from data retrieval to manipulation in Python.

Getting started

The first step to using the Export API is getting authenticated. API connections are authenticated via a token, which is similar to a password that can be generated in your Magento BI account.

The screenshot below shows where to generate a key, in the Export API screen. Make sure to specify the IP addresses you wish to grant access to.

tbaw1

Exporting raw data with the Export API

The Raw Data Export is a great way to download your data that’s stored within your data warehouse — whether it’s a an entire table, specific columns, or a subset of those with applied filters.

You may have used this feature before if you were downloading a table as a CSV. If not, here’s a Help Center article with instructions. You will need to generate the first base export that you are going to then export using the API.

Why is this useful? There are several benefits of using the Raw Data Export in conjunction with the Export API. For one, it’s a method to automate data retrieval. Going back to our use cases, you can create the initial Raw Data Export to retrieve relevant user information for an email marketing service. From there you can run this script as often as you’d like to get an updated user mailing list.

Also, you may have noticed that raw exports only last for one week. Using part of the Python script below to generate a new export is a great way to refresh an old export without having to manually create a new one each time.

Now let’s dive into some code!

Load the necessary libraries:

Enter your information in the block below:

Now, send a POST request to the existing Raw Data Export, and generate a new export to provide an updated version of the data. The message below prints the Table ID that applies to the newly created Raw Data Export.

There will be a slight delay for the new data export to generate in the dashboard. To streamline this entire process, a loop is created that waits until the status of the new report is shown as “Completed” before proceeding.

Now that the new raw export has fully loaded and is now visible in your dashboard, it’s time to retrieve the data. A GET request is sent to a different endpoint in the API using the same API key you provided with the new Table ID. The output is in the format of a ZIP file, so all that’s left is to unpack it and save the CSV inside to a location on your local machine.

What does the response code mean? Read more about HTTP status codes here.

And that’s it! You can run this script as often as you’d like and each time it will provide the most updated version of the table. If you’d like to find out how to run this on a timed schedule (i.e. hourly/daily/weekly) you can look into cron jobs.

What about existing reports?

Now that we’ve covered Raw Data Exports, what about retrieving data from reports and charts in your dashboard?

Instead of creating a new Raw Data Export, all you will need for this is a Figure ID that can be found in a dropdown in the top right corner of any report in your dashboard. One difference is that instead of the output being in a CSV format, this will be in JSON, which has become a popular format to easily store and access information. One thing to note is that report exports are only allowed on scalar and graphical reports. Tabular reports are not currently supported.

Why is this useful? If you’re working with other integrations, you can keep this as a JSON format to send to another API. You can also complete further analysis using different libraries in python or through a statistical program such as R.

In the example below, we’ll walk through the export of a sample revenue report. You’ll see how the data looks like in JSON format, as well as how to transform it back to a table.

Things you will need:

  1. Your Client ID:
  2. The export API key you previously generated
  3. The Figure ID referencing the specific report

First, load the necessary libraries…

If the response code came back as 200 — great! The API accepted your request to retrieve the revenue data. But what does it look like?

JSON comes in a nested form, so if you’re looking to perform some type of analysis we will first need to parse out relevant information. In this case it looks like that’s everything contained in “series”:

To do this, we create a list containing lists of data. That would be each row of data containing the date and revenue in this scenario. We print the first line in the list to verify that the loop captured it properly.

From here you can either keep it as a JSON format to send to another API as a post request or transform it to a tabular form to analyze further, or save as a CSV.

In review

The Export API is an incredibly powerful tool with endless use cases. Using it in conjunction with a programming language such as python is not only a great way to transport your data to different platforms, but also gives the capability to apply any type of analysis before sending it off to its destination.

Here at Magento Business Intelligence we’re always looking for new ways to innovate in the world of analytics. If you’re interested in exploring this further or would like to speak to our Professional Services team, contact us todayNot a customer yet? Schedule a demo.

  • Koba Khitalishvili

    Awesome!!!