Shopfront Developers

Reporting API

We've developed a custom query language that is designed to be simple to read and write yet powerful enough to run highly detailed reports.

The syntax for our Reporting Query Language is similar in design to SQL (Structured Query Language) so if you have used SQL in the past you should be able to easily transition into using our query language.

Getting Started

We would suggest reviewing and understanding how the syntax for the Reporting Query Language works before continuing.

Once you've had a read through the documentation, you should go and test your knowledge by writing your own reports within Shopfront (if you don't have access to a store to write the reports for, you can sign up for a trial store). You can do this by going to the menu, selecting the Reporting option and clicking on Advanced Reports.

Now that you've hopefully been successful with writing some of the reports that you want to query, you can continue to build your integration.

Scopes

To use the Reporting API you will require the following scopes:

  • run_advanced_reports

For further information on scopes, please see Scopes.

Rate Limit

The Reporting API has it's own rate limit which is currently set to 10 requests per 60 seconds. As this is a new API we will be monitoring use to either increase or decrease the limit (or change the limit to a complexity check). Because of this, we would suggest using the following headers to implement your own rate limiting:

  • X-RateLimit-Limit: The maximum amount of requests (10) per minute
  • X-RateLimit-Remaining: The remaining amount of requests you have in the rolling window
  • Retry-After: The number of seconds until you can retry again
  • X-RateLimit-Reset: The POSIX timestamp when you can start making requests again

The rate limit is not related to the GraphQL rate limit so you can reach one and keep querying the other.

Querying Shopfront

After you've gone through the authentication flow and have connected the store you can begin querying Shopfront through the API.

You simply need to send a POST request to https://[vendor].onshopfront.com/api/v2/reports with the following parameters:

Parameter Required Description
 query Required The query to run
key Optional The key to retrieve the report with (explained below)
disableFormat Optional Whether default formatting should be disabled (defaults to false)
ignoreDivisionByZero Optional Whether division by zero errors should throw an error or should just fill the data in as null

If the query was successful, you'll receive a status of 200. If there is an error with the query, you'll receive a status of 400 and if there is an internal server error it will be in the 500 range. If you receive a 500 error, we would suggest checking your query as it could be due to contradictory terms that we have not yet encountered.

Query Example (HTTP)
POST /api/v2/reports HTTP/1.1
Host: example.onshopfront.com
Accept: application/json
Authorization: Bearer ...
Content-Type: application/json
{
    "query": "SELECT BY_NAME, revenue, cost FROM sales BY outlet",
    "key": null,
    "disableFormat": false,
    "ignoreDivisionByZero": false
}

Failed Query

When a query has encountered an error with the data, it will return a response that contains the following fields:

Parameter Type Description
 message string The error message, check this to see what the issue is with your query
error boolean Whether this is an error (always true)
code integer The error code, if you need assistance with this error, please provide this to us along with the message
Error Response (JSON)
{
    "message": "The error message",
    "error": true,
    "code": 123
}

Successful Query

When a query is successful you'll receive a query that matches the data you requested (grouping is slightly complex). An example of the response is available on the right (as long as you're not on a mobile phone).

Field Type Description
completed boolean Whether this page is the final page of the report (explained below)
data object The data for the report
key string The key for the report (explained below)
data.totals { string: mixed } The totals for each column
data.data mixed The report data
data.groups Array Each group (in order of grouping) that the data is grouped by
data.columns Array The columns (in order) of the data
data.range { start: DateTime, end: DateTime } The start and end date for the report (formatted based on the Vendor's date and time settings)

The field data can exist in one of two ways, either as an array (default) or as an object (when grouped). Each level of grouping will contain an object of data and the final (most nested) level will contain an array of data.

When data is an array it is formatted as Array<{ string: mixed }> where the key is the name of the column and the value is the value of the data for that column.

When data is an object (in a group) it will look like the following:

Field Type Description
[group] object Each key of data is the group identifier (not necessarily the name)
[group].data mixed The data in the group (either an array or an object)
[group].name string The name of the group (you can present this to a user if required)
[group].totals { string: mixed } The totals for each column in this group
Success Response (JSON)
{
    "data": {
        "totals": {
            "Name": null,
            "Revenue": "$195.42",
            "Cost of Goods Sold": "$161.45",
            "Transaction Count": 10,
            "Average Sale": "$19.54",
            "Profit": "$33.97",
            "Profit Percentage": "17.38%",
            "Tax Amount": "$17.78",
            "Discount Amount": "$1,553.98",
            "Revenue Percentage": "100.00%",
            "Transaction Percentage": "100.00%"
        },
        "data": [
            {
                "Name": "Test Outlet #2",
                "Revenue": "$195.42",
                "Cost of Goods Sold": "$161.45",
                "Transaction Count": 10,
                "Average Sale": "$19.54",
                "Profit": "$33.97",
                "Profit Percentage": "17.38%",
                "Tax Amount": "$17.78",
                "Discount Amount": "$1,553.98",
                "Revenue Percentage": "100.00%",
                "Transaction Percentage": "100.00%"
            }
        ],
        "groups": [],
        "columns": [
            "Name",
            "Revenue",
            "Cost of Goods Sold",
            "Transaction Count",
            "Average Sale",
            "Profit",
            "Profit Percentage",
            "Tax Amount",
            "Discount Amount",
            "Revenue Percentage",
            "Transaction Percentage"
        ],
        "range": {
            "start": "01/04/2019 00:00:00",
            "end": "16/04/2019 23:59:59"
        }
    },
    "key": "5a9ac008-440b-4c62-bbb4-0ba9261d4131",
    "completed": true
}
Success (Grouped) Response (JSON)
{
    "data": {
        "totals": {
            "Name": null,
            "Revenue": "$195.42",
            "Cost of Goods Sold": "$161.45",
            "Transaction Count": 10,
            "Average Sale": "$19.54",
            "Profit": "$33.97",
            "Profit Percentage": "17.38%",
            "Tax Amount": "$17.78",
            "Revenue Percentage": "100.00%",
            "Transaction Percentage": "100.00%"
        },
        "data": {
            "11e72a0b5269be12994f5cf9dd713698": {
                "name": "Test Outlet #2",
                "totals": {
                    "Name": null,
                    "Revenue": "$195.42",
                    "Cost of Goods Sold": "$161.45",
                    "Transaction Count": 10,
                    "Average Sale": "$19.54",
                    "Profit": "$33.97",
                    "Profit Percentage": "17.38%",
                    "Tax Amount": "$17.78",
                    "Revenue Percentage": "100.00%",
                    "Transaction Percentage": "100.00%"
                },
                "data": [
                    {
                        "Name": null,
                        "Revenue": "$100.49",
                        "Cost of Goods Sold": "$102.08",
                        "Transaction Count": 3,
                        "Average Sale": "$33.50",
                        "Profit": "-$1.59",
                        "Profit Percentage": "-1.58%",
                        "Tax Amount": "$9.14",
                        "Revenue Percentage": "51.42%",
                        "Transaction Percentage": "30.00%"
                    },
                    {
                        "Name": "Gifts - Glasses, Bar Access.",
                        "Revenue": "$25.00",
                        "Cost of Goods Sold": "$15.00",
                        "Transaction Count": 1,
                        "Average Sale": "$25.00",
                        "Profit": "$10.00",
                        "Profit Percentage": "40.00%",
                        "Tax Amount": "$2.27",
                        "Revenue Percentage": "12.79%",
                        "Transaction Percentage": "10.00%"
                    },
                    {
                        "Name": "White Wines",
                        "Revenue": "$69.93",
                        "Cost of Goods Sold": "$44.37",
                        "Transaction Count": 6,
                        "Average Sale": "$11.66",
                        "Profit": "$25.56",
                        "Profit Percentage": "36.55%",
                        "Tax Amount": "$6.37",
                        "Revenue Percentage": "35.78%",
                        "Transaction Percentage": "60.00%"
                    }
                ]
            }
        },
        "groups": [
            "outlet"
        ],
        "columns": [
            "Name",
            "Revenue",
            "Cost of Goods Sold",
            "Transaction Count",
            "Average Sale",
            "Profit",
            "Profit Percentage",
            "Tax Amount",
            "Revenue Percentage",
            "Transaction Percentage"
        ],
        "range": {
            "start": "01/04/2019 00:00:00",
            "end": "16/04/2019 23:59:59"
        }
    },
    "key": "1d037157-0ef0-4b30-8b99-2a1c1ff635a0",
    "completed": true
}

Multi Page Queries

Sometimes when you query a report on Shopfront, we will split it into several parts to ensure that the data is quick to respond with and make it easy to parse. This typically occurs at every 250 records but may happen more or less often on occasion. This will also sometimes result in splitting a grouped query across multiple requests.

When this happens, you'll receive a response that looks like:

{
  "completed": false,
  "key": "abcde-12345-etc",
  "data": "..."
}

Whenever you receive a response with completed set to false you'll need to request the next page by providing the key that was in the response.

If you need to merge the data back together you can use a function similar to what is displayed on the right:

Merge Data Function (JavaScript)
function mergeLayer(layer, results) {
    // Check if this layer is an Array
    if(Array.isArray(layer)) {
        // Just add the data to the results
        for(let i = 0, l = layer.length; i < l; i++) {
            results.push(layer[i]);
        }
    } else {
        // Iterate through the layers and merge
        for(let group in layer) {
            if(!layer.hasOwnProperty(group)) {
                continue;
            }

            let child = layer[group];

            // Check if the child has been merged previously
            if(!results[group]) {
                results[group] = {
                    name  : child.name,
                    totals: child.totals,
                    data  : Array.isArray(child.data) ? [] : {},
                };
            }

            // Merge the child recursively
            mergeLayer(child.data, results[group].data);
        }
    }
}

function mergeReportData(reports) {
    // Reports is an array, make sure that at least one report has been provided
    if(!reports.length) {
        return [];
    }

    // Prepare the base layer of the reports
    let results = Array.isArray(reports[0].data.data) ? [] : {};

    // Loop through all of the reports
    for(let i = 0, l = reports.length; i < l; i++) {
        // Merge each report (by object reference)
        mergeLayer(reports[i].data.data, results);
    }

    return results;
}