Documentazione

1Introduction

The Analytics feature helps merchants to quickly analyze their transactions within Wallee and get insights into their business performance. With structured access to their data, they can predict how to adapt to customer needs. Reports can be customized by writing SQL queries, and all data analysis is powered by PrestoDB, an open-source SQL query engine provided by Amazon Athena.

The Analytics Schema describes the data structures exported from the main Wallee database, which can be queried in Portal UI and REST API.

2Overview

The overview of all analytics query executions can be accessed within the account under Account > Analytics > Queries.

Analytics queries executions List
Figure 1. The list of Analytics queries executions is visible in the account.

2.1Execute the Query Submission

The merchant can submit a query within the specific merchant’s account under Account > Analytics > Submit Query.

Submit Analytics query
Figure 2. The query can be submitted by clicking on the Submit button.

A submitted query is processed asynchronously. Initially, the query execution will have a PROCESSING status, and no details will be available. Once the query execution is processed and completed, the status will change to SUCCESS and the query details can be retrieved.

If the query execution fails or is canceled before completion, the status will change to FAILED or CANCELLED, respectively, and no detailed query information will be available.

For the restrictions on submitting the query, refer to Space and Account Restriction.

2.2Query Execution Details

Opening a specific query execution will display the detailed information, including the submitted query SQL request, a download link to the Result File (in CSV format), and available actions for the query (e.g. canceling the query execution).

Analytics Query Execution Details
Figure 3. The Query Execution details.

2.3Cancel the Query Execution

A query execution in PROCESSING status can be canceled by clicking the Cancel execution button from the query execution detailed view page Query Execution Details.

If the query execution has already reached a final status (SUCCESS, FAILED, or CANCELLED), the cancellation attempt will be ignored.

3The Analytics Queries REST API

The Analytics Queries REST API is used to manage the execution of Analytics queries. It allows users to submit query requests, retrieve results in CSV format, monitor query execution, and cancel running queries.

For the complete API reference, see the Analytics Queries REST API documentation.

3.1Example: List Customers by Accumulated Transaction Amounts

In this example, a query is executed to list customers ordered by the amount they spent in the merchant’s space. Using the Analytics Schema, we construct the following PrestoDB SQL query, which calculates the sum of transaction amounts grouped by customer:

SELECT SUM(completedamount) AS total, customerid
FROM transaction
GROUP BY customerid
ORDER BY total DESC;
Note
The user must specify table and column names in lowercase, as they are stored internally in lowercase, even though SQL keywords, clauses, and reserved keywords (such as SELECT) are case-insensitive.

3.2Submit the Query Execution

A query request can be submitted for execution using the Submit Query Execution Request method (HTTP POST). The request body should contain an Analytics Query Execution Request structure in JSON format.

To submit our example query, we send the following JSON:

{
    "accountId": 2,
    "sql": "SELECT SUM(completedamount) AS total, customerid FROM transaction GROUP BY customerid ORDER BY total DESC"
}

The query request will be executed within the account with ID 2 (specified in the accountId parameter). The actual SQL query is provided in the sql parameter. For more details, refer to Space and Account Restriction.

The response for a submission request will be an Analytics Query Execution Response structure in JSON format, containing the queryToken of the execution:

{
	"queryToken": "4d135f47-8c13-4b51-86ce-08c5d0f33a00"
}

3.3Monitor the Query Execution Status

A submitted query is processed asynchronously. Initially, the query execution will be in PROCESSING status, and no results will be available. Once the query execution is processed and completed, the status will change to SUCCESS and the query results can be fetched.

If the query execution fails or is canceled before completion, the status will change to FAILED or CANCELLED, respectively, and no results will be returned.

The current status of a previously submitted query can be checked using the Query Execution Status API method (HTTP GET) with queryToken set as a URL path parameter (use queryToken you obtained from your initial call to Submit the Query Execution).

The response of the call to the Query Execution Status method will be a Submitted Query Execution structure. In our example, the request URL will look like this:

/api/v2.0/analytics/queries/4d135f47-8c13-4b51-87ce-08c5d0f33a00

Once the query execution reaches the SUCCESS status, a response similar to the following will be received:

{
    "accountId": 2,
    "createdTimestamp": "2025-03-21T07:56:56.110252Z",
    "downloadRequests": 0,
    "originalQuery": "SELECT SUM(completedAmount) AS total, customerId FROM transaction GROUP BY customerId ORDER BY total DESC",
    "portalQueryToken": "4d135f47-8c13-4b51-87ce-08c5d0f33a00",
    "resultFileBytes": 1476198,
    "scannedBytes": 763075,
    "status": "SUCCESS",
    "totalBilledExecutionTimeMs": 1295
}
Note

This Query Execution Status is a long-running request, with a maximum timeout of approximately 100 sec. Queries are processed asynchronously and may take several minutes to reach the final status, typically involving long polling, which can take up to 100 seconds. If the query execution still has a PROCESSING status after this time, it is recommended to repeat the call later. Please avoid making frequent requests to this API method, as this will not have any effect on query processing.

HTTP status codes:

  • 200 OK: The query has reached its final status. If the query is still processing, a 200 status is returned with a Retry-After header.

  • 5xx - Internal server errors.

3.4Cancel the Query Execution

A query execution in PROCESSING status can be canceled using the Cancel Query Execution method (HTTP DELETE). Include the queryToken from Submit the Query Execution response.

The request URL will look as follows:

/api/v2.0/analytics/queries/4d135f47-8c13-4b51-87ce-08c5d0f33a00

Canceling a query execution will always return an empty response unless there is an error. If the query execution has already reached a final status (SUCCESS, FAILED, or CANCELLED), the cancellation attempt will be silently ignored.

3.5Getting the Query Execution Results

Once the Query Execution Status returns a SUCCESS status, the query execution results can be retrieved by calling the Query Execution Result API method (HTTP GET request). Include the queryToken from the Submit the Query Execution response.

The request URL will look as follows:

api/v2.0/analytics/queries/4d135f47-8c13-4b51-86ce-08c5d0f33a00/result

The response body contains a temporary Amazon S3 URL (valid for approximately 5 minutes), similar to the following:

https://...s3.eu-west-1.amazonaws.com/query-results/72aaf6a7-86eb-49bb-bded-5ef34e79385b.csv?X-Amz-Security-Token=[...]&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20250321T082449Z&X-Amz-SignedHeaders=host&X-Amz-Credential=[...]&X-Amz-Expires=300&X-Amz-Signature=[...]
Note

The Query Execution Result method generates a short-lived URL (valid for 5 minutes) for the Analytics Query result file. Each download of the output file incurs a charge, and we also count each file URL generation as a potential download attempt.

Please, DO NOT use this API method for periodic checks of the result file availability - use Query Execution Status for periodic checks instead.

HTTP status codes:

  • 200 OK: The short-lived URL is available.

  • 202 Accepted: The result is expected to be available later.

  • 204 No Content: The result set is empty (this may indicate that the query was canceled or failed).

  • 404 Not Found: No query was found for the given token.

4Space and Account Restriction

A query must always be executed within the context of an authenticated account. The query execution results will be limited to the spaces associated with that account. Queries in spaces not belonging to the executing account will fail with a permission error.