Create custom CSV reports of transactions

In addition to the CSV statements you can export via the Revolut Business Dashboard, you can generate custom CSV reports using the Merchant API: Report runs operations. Unlike the default statements, creating custom reports offers you more flexibility in determining what data you wish to incorporate. This allows you to have more control over your analytics.

This tutorial walks you through the process of:

• Defining the structure of your custom CSV report
• Generating a new report run
• Polling the status of the generation
• Extracting the data from the result

Additionally, if you use the metadata object in your order management system, we provide you with examples of how you can include your custom metadata parameters in your reports.

Get data from the report​

When it comes to getting the data from your reports in CSV format, you have three options:

1. CLI with manual copying: You'll employ the CLI to make the necessary API calls in this method. Once you receive the results, you need to copy and paste them into a text file manually.

2. API client tool with manual copying: Another approach is to utilise an API client tool like Postman. This tool streamlines API calls. After retrieving the results, you need to copy and paste them into a text file manually.

3. Custom implementation with frontend and backend: For a more tailored solution, you can create your own implementation. This involves developing a basic frontend for data input. On the backend, you'll handle API calls and facilitate the direct download of the report file.

Each option provides a different level of automation and control, allowing you to choose the approach that best aligns with your preferences and requirements.

Prerequisites​

Before you start this tutorial, ensure that you have completed the following steps:

• Get started: 1. Apply for a Merchant account
• Get started: 2. Generate the API keys

Step 1. Create a custom report run​

You can generate a custom CSV report using the Merchant API: Create a new report run operation. Several parameters help you customise your reports. There are two main categories:

• The filter object lets you define which transactions to include in your report
• The options object lets you define how you want to present your results: columns to include, timezone to use for timestamps

When you know what and how you wish to include in your custom report, create the corresponding request body as a JSON object.

For example, suppose you want to generate a report about failed and declined EUR payments in the first half of 2023. Furthermore, you want to include each transaction and order ID, the payment method, the amount, the currency, the respective order states and descriptions, and the last time the orders were updated.

To do this, pass the following JSON as the body of the request:

{
  "filter": {
    "from": "2023-01-01T00:00:00Z",
    "to": "2023-07-01T00:00:00Z",
    "entity_types": [
      "payment"
    ],
    "entity_states": [
      "failed",
      "declined"
    ],
    "currency": "EUR"
  },
  "format": "csv",
  "type": "custom_report",
  "options": {
    "columns": [
      "transaction_id",
      "order_id",
      "payment_method",
      "amount",
      "currency",
      "state",
      "updated_date"
    ]
  }
}

The response of the request contains the report_run_id. Save this ID, as you will need it to check the report run's status.

Step 2. Poll report run status​

After initiating your custom report run, use the report_run_id from the response to repeatedly send a request to the Retrieve report run details endpoint. You can track the report run's status changes.

You should continue to check the report run state until the response contains a final state. Depending on the received state, you need to do the following:

Step 3. Save reports as CSV files​

When your report run transitions to completed state, the response of the Retrieve report run details call will contain the file_url where the generated CSV file is available for you to download.

Here is an example CSV report generated based on the custom report defined in Step 1:

For all options detailed in the Get data from the report section, all steps so far are the same. Depending on your chosen approach, you need to do the following to save your reports as CSV files.

CLI with manual copying​

To save your report as a CSV file with CLI, follow these steps:

1. After the report run transitions to completed state, call the Download report file endpoint. The response will contain the raw CSV data.

   note

   Alternatively, you can make an authorised GET call to the file_url retrieved from a completed report run object.

2. Select and copy the data from the response.

3. Open a new text file and paste the data into the file.

4. Save the text file in CSV format.

API client tool with manual copying​

Similar to the previous option, this method involves manually copying the results. The only difference is that now you are using an API client tool to extract the data. To save your report as a CSV file with an API client tool, follow these steps:

1. After the report run transitions to completed state, call the Download report file endpoint. The response will contain the raw CSV data.

   note

   Alternatively, you can make an authorised GET call to the file_url retrieved from a completed report run object.

2. Select and copy the data from the response.

3. Open a new text file and paste the data into the file.

4. Save the text file in CSV format.

Custom implementation with frontend and backend​

Compared to the other options, with this more advanced solution, you'll have a customised solution to handle the entire process, including retrieving and saving the report data. Here's how the process works in this case:

1. You interact with the frontend to initiate the report run request. (Step 1.)

2. The frontend sends a request containing the request's body to the backend. (Step 1.)

3. The backend makes the API call to the Merchant API to retrieve the report data. (Step 1.)

4. Poll the report run status until it reaches a final state. (Step 2.)

5. After the report run transitions to completed state, call the Download report file endpoint. The response will contain the raw CSV data.

   note

   Alternatively, you can make an authorised GET call to the file_url retrieved from a completed report run object.

6. The backend sends the report data to the frontend.

7. Optionally, the frontend displays the data.

8. You can download the data as a CSV file directly from the frontend.

Regardless of the option you choose, the main goal of Step 3. remains the same - retrieving and saving the report data as a CSV file.

Work with metadata​

If you use custom metadata on your orders, you can include them in your report file as additional columns. To do this, you can add them to the options.columns array the following way:

{
  ...
  "options": {
    "columns": [
      ...,
      "metadata.custom_attribute_1",
      "metadata.custom_attribute_2"
    ]
  }
}