⏱ 10 min
Create a CSV file with information about orders, deliveries and payments of your customers, so we can use it for our risk assessment. Usually it contains the last 24 months.
This list must be pseudonymized. For identification purposes, your list should contain unique customer identifiers. This could be the customer IDs of your shop, MD5 checksums of customer's data or anything else.
The same customer identifier must be transmitted within the PAYMENT_REQUEST or Authorize call, as stated in the CSV file, so that an allocation can happen.
The following data is basically required for each buyer:
- Unique customer identifier
- Order id
- Order date
- Date of delivery
- Date of receipt of payment
- Value of cancellations/returns (if applicable)
- Currency
- Payment method
Your list must not contain any due or outstanding receivables
| Rows | Description |
|---|---|
| 1 | Customer A ordered on 01-DEC-2017 for 110 € on shop's open invoice. The delivery took place on 03-DEC-2017. The payment was received on 17-DEC-2017. |
| 2 - 4 | Customer B ordered on 03-JAN-2018 for 200 € via Ratepay Open Invoice. The delivery took place in 3 parts on 06-JAN-2018, 10-JAN-2018 and 15-JAN-2018. The payments from Ratepay were received on 25-JAN-2018, 01-JAN-2018 and 08-JAN-2018 on the account of the shop. |
| 5 | Customer A has moved into a new apartment and ordered on 04-APR-2018 by shop's prepayment for 150 €. The payment was received on 06-APR-2018. The goods were shipped 3 days later. Goods with a value of 20 € were returned afterwards. |
| 6 | Customer C ordered by shop's direct debit on 03-DEC-2018. A part of the order with a value of 30 € was canceled. The delivery took place on 05-DEC-2018. The payment took place on 08-DEC-2018. The customer has no shop login, but has ordered as a guest. |
| 7 | Customer D created a shopping cart for 120 € on 31-JUL-2018. In the shopping cart there is a voucher for 5 €. This offer was valid until 31-JUL-2018. The customer placed the order (Checkout) too late, on 01-AUG-2018, by shop's invoice. Due to an error the voucher was not removed automatically. Before the goods were shipped on 01-AUG-2018, the error was detected and the voucher was cancelled in consultation with the customer. Payment has not yet been received. |
FileRownumber;InterfaceVersion;ShopsOrder_ID;ShopsCustomer_ID;OrderDate;OrderTime;DeliveryDate;PaymentDate;PaymentMethod;Currency;OrderAmount;PaymentAmount;CancellationAmount;ReturnAmount;LoginFlag;NewAddressFlag;ReturningPeriod
1;1;"123456";"sci1234";"2017-12-01";"09:17:24";"2017-12-03";"2017-12-17";"INV1";"EUR";110.00; 90.00;0.00;0.00;1;0;14
2;1;"123467";"sci2345";"2018-01-03";"16:23:27";"2018-01-06";"2018-01-25";"INV2";"EUR";200.00; 100.00;0.00;0.00;1;0;14
3;1;"123457";"sci2345";"2018-01-03";"16:23:27";"2018-01-10";"2018-02-01";"INV2";"EUR";200.00; 75.00;0.00;0.00;1;0;14
4;1;"123457";"sci2345";"2018-01-03";"16:23:27";"2018-01-15";"2018-02-08";"INV2";"EUR";200.00; 25.00;0.00;0.00;1;0;14
5;1;"123896";"sci1234";"2018-04-04";"11:27:05";"2018-04-09";"2018-04-06";"PP1";"EUR";150.00; 150.00;0.00;20.00;1;1;14
6;1;"123411";"sci3231";"2018-11-03";"08:03:43";"2018-11-05";"2018-11-08";"DD1";"EUR";80.00; 50.00;30.00;0.00;0;0;14
7;1;"123411";"sci2273";"2018-07-31";"20:12:59";"2018-08-01";"9999-12-31";"INV1";"EUR";120.00; 0.00;-5.00;0.00;1;0;14Download: Regular Customers Example 1 CSV
Not filled fields must be passed empty.
FileRownumber;InterfaceVersion;ShopsOrder_ID;ShopsCustomer_ID;OrderDate;OrderTime;DeliveryDate;PaymentDate;PaymentMethod;Currency;OrderAmount;PaymentAmount;CancellationAmount;ReturnAmount;LoginFlag;NewAddressFlag;ReturningPeriod
1;1;"123456";"sci1234";"2017-12-01";;"2017-12-03";"2017-12-17";"INV1";"EUR";;90.00;;;;;
2;1;"123467";"sci2345";"2018-01-03";;"2018-01-06";"2018-01-25";"INV2";"EUR";;100.00;;;;;
3;1;"123457";"sci2345";"2018-01-03";;"2018-01-10";"2018-02-01";"INV2";"EUR";;75.00;;;;;
4;1;"123457";"sci2345";"2018-01-03";;"2018-01-15";"2018-02-08";"INV2";"EUR";;25.00;;;;;
5;1;"123896";"sci1234";"2018-04-04";;"2018-04-09";"2018-04-06";"PP1";"EUR";;150.00;;;;;
6;1;"123411";"sci3231";"2018-11-03";;"2018-11-05";"2018-11-08";"DD1";"EUR";;50.00;;;;;
7;1;“123411“;“sci2273“;“2018-07-31“;;“2018-08-01“;“9999-12-31“;“INV1“;“EUR“;;0.00;;;;;Download: Regular Customers Example 2 CSV
| File formats | |
|---|---|
| File format | CSV |
| Header | yes |
| Column separator | ; |
| Separator for text columns | " |
| Line separator | CRLF |
| Encoding | UTF-8 with BOM (Byte Order Mark) |
| Parameter | Description |
|---|---|
| Format | ShopId + _history_ + Date + _ + No. + .csv |
| ShopId | Unique identifier of your shop, defined by Ratepay |
| Date | Date of file transfer (Format: YYYY-MM-DD) |
| No. | Three characters, starting with 001; increment by 1 for each transmission on the same day. |
| Sample | 99980000_history_2014-02-15_001.csv |
| Data types / coding | Description |
|---|---|
| Boolean | 1 = TRUE, 0 = FALSE |
| Date | YYYY-MM-DD in the time zone of the shop |
| Time | HH:MI:SS in the time zone of the shop |
| Number x,y | x = Number of digits, y = Number of digits after the decimal separator |
| Decimal separator | . |
| Sign | + optional for positive values, - mandatory for negative values |
| # | Field name | Description | Max. length | Sample |
|---|---|---|---|---|
| 1 | FileRownumberInteger Mandatory | Line number in this file, starts with 1 and is incremented by 1 for each new line. | 8 | 1 |
| 2 | InterfaceVersionInteger Mandatory | Version number of this interface. This is version 2. | 3 | 2 |
| 3 | ShopsOrder_IDText Mandatory | Purchase order number in the shop system. According to Ratepay Payment API 1.8: <order-id> | 50 | oxll3j0783 |
| 4 | ShopsCustomer_IDText Mandatory | Customer Identifier. According to Ratepay Payment API 1.8: <merchant-consumer-id> | 100 | 646459673 |
| 5 | OrderDateDate Mandatory | Date of the order | 10 | 2013-07-15 |
| 6 | OrderTimeTime Optional | Time of the order | 8 | 17:31:07 |
| 7 | DeliveryDateDate Mandatory | Delivery date of the goods. Use different rows for partial deliveries. | 10 | 2013-07-16 |
| 8 | PaymentDateDate Mandatory | Payment receipt for this order. If not paid, use 9999-12-31.For partial payments, enter the date of the last payment. | 10 | 2013-07-15 |
| 9 | PaymentMethodText Mandatory | Selected payment method. See table "Payment methods". | 5 | CC |
| 10 | CurrencyText Mandatory | Currency of this order. According to Ratepay Payment API 1.8: <shopping-basket@currency> | ||
| 11 | OrderAmountNumber Optional | Shopping cart amount. According to Ratepay Payment API 1.8: <shopping-basket@amount> | ||
| 12 | PaymentAmountNumber Mandatory | Amount of the payment. If payment is done, this means: PaymentAmount = OrderAmount - CancellationAmount - ReturnAmount | ||
| 13 | CancellationAmountNumber Optional | Amount of cancellations | 8,2 | 20.01 |
| 14 | ReturnAmountNumber Optional | Amount of returns | 8,2 | 17.83 |
| 15 | LoginFlagBoolean Optional | Pseudo code: If the user has successfully logged in with a password: 1, otherwise: 0 | 1 | 1 |
| 16 | NewAddressFlagBoolean Optional | Flag for the 1st order for which he uses a different billing address. steps in pseudo code: If only one order exists: result = 0. If billing address is not equal to the billing address of previous purchase order = 1. Otherwise = 0 | 1 | 0 |
| 17 | ReturningPeriodNumber Optional | Max. days between checkout and end of return period (at that time) | 3 | 14 |
| Payment method | English description | Deutsche Beschreibung |
|---|---|---|
CC | Credit Card & Apple Pay | Kreditkarte & Apple Pay |
DD1 | Direct Debit via Shop | Lastschrift Händler |
DD2 | Direct Debit via Ratepay | Lastschrift Ratepay |
DD3 | Direct Debit via other 3rd party | Lastschrift Dritte |
INS1 | Instalment via Shop | Rate Händler |
INS2 | Instalment via Ratepay | Rate Ratepay |
INS3 | Instalment via other 3rd party | Rate Dritte |
INV1 | Invoice via Shop | Rechnung Händler |
INV2 | Invoice via Ratepay | Rechnung Ratepay |
INV3 | Invoice via other 3rd party | Rechnung Dritte |
OTH1 | Other, payment method with factoring | Sonstige, Zahlungsart mit Factoring |
OTH2 | Other, payment method without factoring | Sonstige, Zahlungsart ohne Factoring |
PAY | PayPal | PayPal |
POD | Payment on Delivery | Nachnahme |
PP1 | Prepayment via Shop | Vorkasse Händler |
PP2 | Prepayment via Ratepay | Vorkasse Ratepay |
PP3 | Prepayment via other 3rd party | Vorkasse Dritte |
SOF | Sofortüberweisung or Giropay | Sofortüberweisung oder Giropay |
To ensure that the payment history file has been transferred correctly, create an MD5 hash sum. Save it to a separate MD5-file.
Please make sure that the md5 checksum file has the same filename as the regular customer file.
The md5 file should be ASCII-encoded.
Linux
Use the command line:
$ md5sum meinshop_history_2018-04-15_001.csv | awk '{print $1}' meinshop_history_2018-04-15_001.csv.md5Learn more: https://en.wikipedia.org/wiki/Md5sum#Create_MD5_hash_file_hash.md5
Windows
Use the Windows PowerShell:
certutil -hashfile myshop_history_2022-04-15_001.csv MD5macOS
Use the command line:
md5 -q meinshop_history_2018-04-15_001.csv > meinshop_history_2018-04-15_001.csv.md5myshop_history_2022-04-15_001.csv.md5
4c001dfc2d0eceaef16cfc627d71d5c9This file contains the hash total for the previous example of a payment history file. Upload this file together with the corresponding payment history file (CSV).