Home SELECT SUM(completedamount) AS total, customerid
FROM transaction
GROUP BY customerid
ORDER BY total DESC;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, with all data analysis 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.
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.
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:
|
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.
|
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": 1,
"sql": "SELECT SUM(completedamount) AS total, customerid FROM transaction GROUP BY customerid ORDER BY total DESC"
}The query request will be executed by the account with ID 1 (specified by 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"
}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 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 Query Execution Request method).
See the previous section for instructions how to Submit the Query Execution.
The response of the call to 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-08c5d0f33a00Once the query execution reaches the SUCCESS status, a response similar to the following will be received:
{
"accountId": 1,
"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 long-running request, with a maximum timeout of approximately
|
A query execution in PROCESSING status can be canceled using Cancel Query Execution method (HTTP DELETE).
Include the queryToken parameter of the Submitted Query Execution response returned by the Submit Query Execution Request method.
Refer to the previous section for instructions how to Submit the Query Execution.
The request URL will look as follows:
/api/v2.0/analytics/queries/4d135f47-8c13-4b51-87ce-08c5d0f33a00Canceling 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.
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).
The queryToken from the Submitted Query Execution response, returned by the Submit Query Execution Request method, should be included.
The request URL will look as follows:
api/v2.0/analytics/queries/4d135f47-8c13-4b51-86ce-08c5d0f33a00/resultThe 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 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.
|