Technical description

Summary

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.

attention

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
danger

Your list must not contain any due or outstanding receivables


Samples

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.

Example 1: All fields filled in

Copy
Copied
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;14

Download: Regular Customers Example 1 CSV

Example 2: Only mandatory fields filled in

Not filled fields must be passed empty.

Copy
Copied
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


Payment history file

File format

File formats
File format CSV
Header yes
Column separator ;
Separator for text columns "
Line separator CRLF
Encoding UTF-8 with BOM (Byte Order Mark)

File name

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

File structure

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 FileRownumber
Integer
Mandatory
Line number in this file, starts with 1 and is incremented by 1 for each new line. 8 1
2 InterfaceVersion
Integer
Mandatory
Version number of this interface. This is version 2. 3 2
3 ShopsOrder_ID
Text
Mandatory
Purchase order number in the shop system. According to Ratepay Payment API 1.8: <order-id> 50 oxll3j0783
4 ShopsCustomer_ID
Text
Mandatory
Customer Identifier. According to Ratepay Payment API 1.8: <merchant-consumer-id> 100 646459673
5 OrderDate
Date
Mandatory
Date of the order 10 2013-07-15
6 OrderTime
Time
Optional
Time of the order 8 17:31:07
7 DeliveryDate
Date
Mandatory
Delivery date of the goods.
Use different rows for partial deliveries.
10 2013-07-16
8 PaymentDate
Date
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 PaymentMethod
Text
Mandatory
Selected payment method. See table "Payment methods". 5 CC
10 Currency
Text
Mandatory
Currency of this order. According to Ratepay Payment API 1.8: <shopping-basket@currency>
11 OrderAmount
Number
Optional
Shopping cart amount. According to Ratepay Payment API 1.8: <shopping-basket@amount>
12 PaymentAmount
Number
Mandatory
Amount of the payment. If payment is done, this means:
PaymentAmount = OrderAmount - CancellationAmount - ReturnAmount
13 CancellationAmount
Number
Optional
Amount of cancellations 8,2 20.01
14 ReturnAmount
Number
Optional
Amount of returns 8,2 17.83
15 LoginFlag
Boolean
Optional
Pseudo code: If the user has successfully logged in with a password: 1, otherwise: 0 1 1
16 NewAddressFlag
Boolean
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 ReturningPeriod
Number
Optional
Max. days between checkout and end of return period (at that time) 3 14

Payment methods

Payment method English description Deutsche Beschreibung
CC Credit Card Kreditkarte
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

MD5 hash

To ensure that the payment history file has been transferred correctly, create an MD5 hash sum. Save it to a separate MD5-file.

attention

Please make sure that the md5 checksum file has the same filename as the regular customer file.

The md5 file should be ASCII-encoded.

Examples for determining a hash sum

Linux

Use the command line:

Copy
Copied
$ md5sum meinshop_history_2018-04-15_001.csv | awk '{print $1}' meinshop_history_2018-04-15_001.csv.md5

Learn more: https://en.wikipedia.org/wiki/Md5sum#Create_MD5_hash_file_hash.md5

Windows

Use the Windows PowerShell:

Copy
Copied
certutil -hashfile myshop_history_2022-04-15_001.csv MD5

macOS

Use the command line:

Copy
Copied
md5 -q meinshop_history_2018-04-15_001.csv > meinshop_history_2018-04-15_001.csv.md5

Example

myshop_history_2022-04-15_001.csv.md5

Copy
Copied
4c001dfc2d0eceaef16cfc627d71d5c9

This 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).