Aggregate file description
The -aggregate.csv file includes a summary-level report of fees on all payments, refunds, and chargebacks across all boarded merchants within a given settlement period. This will be delivered every day that settlement has been made along with the other 7 describing that settlement period.
The naming convention is:
<merchant number>-<frequency>-<year>-<ongoing number>-<suffix>.csv
Example: 1-999-W-2016-07-aggregate.csv
The file will contain the following data:
| Column | Description | Type |
|---|---|---|
MERCHANT_ID | Contract ID identifying each unique merchant as provided by PPRO upon onboarding[a-zA-Z0-9.,-_]{1,40} | string |
PAYMENT_METHOD | Name of the payment method (e.g. “SEPA Direct Debit”)[a-zA-Z0-9]{1,40}(see Appendix B for a full list of values) | string |
EVENT_TYPE | See EVENT_TYPE list below | string |
EVENT_COUNT | The number of individual transactions summarized in this entry | numeric |
AGGREGATE_AMOUNT | The aggregate amount of transactions summarized in this entry | numeric |
CURRENCY | 3-digit ISO currency code | string |
EVENT_TYPE Descriptions:
| EVENT_TYPE | Description |
|---|---|
TRANSACTION | A normal payment transaction (cash-in) |
REFUND | A normal refund or return of funds to the consumer (cash-out) |
CHARGEBACK | A disputed transaction |
CLEARED_TO_MERCHANT | Amount of successful transactions cleared by merchants. This amount does not include one-off (e.g.: WIRE_FEE) or per-transaction fees |
ACQUIRING_FEE | The fee per transaction (not a percentage of the transaction value) deducted from the ultimate settlement |
DISCOUNT_FEE | The percentage per transaction that is debited from the ultimate settlement |
PROCESSING_FEE | Additional per-transaction (not a %) fee |
SUCCEEDED_FEE | The fee charged when a transaction moves from any state to a SUCCEEDED state (not a % of transaction value) |
WIRE_FEE | The fee charged to send funds to a client |
MERCHANT_SETUP_FEE | A one-time per-merchant fee charged when a new merchant is set up |
MERCHANT_MONTHLY_FEE | The recurring monthly fee for every merchant |
INQUIRY_FEE | A fee that a scheme charges PPRO for any inquiry (non-fraud/risk) into a transaction |
ONE_TIME_FEE | One-time fee assessed due to external factors (fraud research, supplement send fee from the bank, etc.) |
FRAUD_INQUIRY_FEE | Same as INQUIRY_FEE. Highlights that the external fee was related to a fraud inquiry |
MONTHLY_MINIMUM_FEE | The difference between the contractually agreed monthly minimums and the amount processed (for example, this is 0 if the monthly minimum is hit) |
MONTHLY_BASE_FEE | The monthly fixed price that PPRO charges for transaction processing |
PROVIDER_PROFIT_SHARE | For referral partners only – fees that PPRO charges for profit sharing for referrals |
RESERVED_HOLDBACK | The amount of settlement held back (refer to contract for a schedule of holdback timelines) |
RELEASED_HOLDBACK | The amount of holdback released (refer to the contract for the schedule of holdback timelines) |
FX_SALE | Source currency (currency to be converted) - granular FX rates are published in the footer of the aggregate file; see section below for Currency Conversion (forex) Footer Samples |
FX_PURCHASE | Destination currency (currency to be settled) - granular FX rates are published in the footer of the aggregate file; see section below for Currency Conversion (forex) Footer Samples |
FX_FEE | FX fee assessed by PPRO for offering currency conversion services; same currency as FX_PURCHASE - granular FX rates are published in the footer of the aggregate file; see section below for Currency Conversion (forex) Footer Samples |
STARTING_BALANCE | positive:• the amount in reserve that remains from the prior period • the amount that is below the settlement threshold (refer to your contract to see which case applies) negative:• the amount to be billed that has not reached an invoicing threshold In both scenarios: this field is generated for the currency when it is relevant per your PPRO contract with PPRO. It can also appear multiple times, one time per currency. This field is not generated in the following scenarios: • There is no reserve or arrears • The STARTING_BALANCE is 0STARTING_BALANCE is not associated with a MERCHANT_ID. It appears in the file similar to standalone fees (e.g: WIRE_FEE).The same value appears in PPRO’s PDF Clearing Statement |
FORWARDED_BALANCE | positive:• the amount remaining in reserve that is forwarded to the next settlement cycle • the amount under the settlement threshold that will be forwarded to the next cycle (refer to the contract to see which case applies) negative:•.the amount to be billed that has not reached an invoicing threshold In both scenarios: this field is generated for the currency when it is relevant per your PPRO contract with PPRO. It can also appear multiple times, one time per currency. This field is not generated in the following scenarios: • There is no reserve or arrears • The STARTING_BALANCE is 0FORWARDED_BALANCE is not associated with a MERCHANT_ID. It appears in the file similar to standalone fees (e.g: WIRE_FEE)The same value appears in PPRO’s PDF Clearing Statement |
| VAT | The Value Added Tax (VAT) assessed due to the location of a customer's legal, contracted address. The value is deducted from the settlement as a negative value in the aggregate file of a billing period. VAT will only appear as an event in the aggregate settlement file if it is relevant to a customer - this will be known during the contracting period with PPRO |
| MANUAL_ADJUSTMENT | Manual adjustments (credit/debit) to align with the generated bills |
Note
See your PPRO contract for information about the applicability of each fee type. Reach out to your account manager for more details.
FX Footer Field
| FX Footer Field | Explanation | Sample Value | Sample Explanation |
|---|---|---|---|
| FXCONVERSION_RATEABC_XYZ | Conversion rate from currency to sell ABC and buy XYZ. Values bolded for readability. | FXCONVERSION_RATEMYR_USD,0.238892 | The rate PPRO used to sell MYR and buy USD is 1 MYR : 0.238892 USD |
| FXFEE_RATEABC_XYZ,0.01 | Foreign exchange fee charged for conversion between currencies ABC and XYZ. Values are bolded for readability. | FXFEE_RATEMYR_USD,0.01 | The FX fee PPRO charged for MYR to USD conversion is 1% |
Note
There will be multiple entries for FX_CONVERSION_RATE and FX_FEE_RATE in the aggregate file's footer in the event that there are multiple currency conversions in a single settlement. A sample of multiple forex entries is documented in the following sample file:
FX Aggregate Sample File
Example: calculating reserve
This sample helps calculate reserve with 50,000 EUR available based on a daily settlement cycle.
| Day 1 | Day 2 | Day 3 | Day 4 | Day 5 | Day 6 | Day 7 | |
|---|---|---|---|---|---|---|---|
STARTING_BALANCE | 50000 | 50000 | 40000 | 50000 | 25000 | 0 | 50000 |
FORWARDED_BALANCE | 50000 | 40000 | 50000 | 25000 | 0 | 50000 | 50000 |
| Note | Normal day | 0 sales, 10k refunds | On a normal day with >10K in sales, 10K is removed from settlement to replenish the reserve | 0 sales, 25K refunds | 0 sales, 25K refunds | Enough sales to replenish reserve and settle the remaining amount | Normal day |
Holdback Information
Important
Holdbacks will only appear for customers who have holdbacks configured as a part of their agreement with PPRO. Please reach out to your account manager for any uncertainty regarding holbacks and their applicability.
PPRO will provide enhanced detail into the holdbacks held and paid out to customers, by LPM, currency and value. A negative value implies an amount held back; a positive value implies a holdback amount that will be paid out on the SETTLEMENT_DATE noted in the aggregate file’s footer. The date in a RESERVED_HOLDBACK_SUMMARY record indicates the date at which the amount will be released, while that in a RELEASED_HOLDBACK_SUMMARY record recalls when the released amount was originally reserved.
Note
The release date reported in the Reserved Holdback record is not the date that previously-held funds will be transferred to the partner, but instead indicates the day upon which the held funds will be reported as released in the aggregate settlement file, to be settled on the corresponding SETTLEMENT_DATE in that file
The holdback summary data will be present in the aggregate CSV file at the footer of the file.
Note
There will be a line break preceding the holdback data’s printing in the file. The data below are represented in tabular form, rather than in raw comma-separate form, for readability purposes.
| RESERVED_HOLDBACK_SUMMARY | ALIPAY | USD | 2021-12-09 | -75303.69 |
| RESERVED_HOLDBACK_SUMMARY | ALIPAY | EUR | 2021-12-10 | -4175.34 |
| RESERVED_HOLDBACK_SUMMARY | ALIPAY | SGD | 2021-12-11 | 55827.9 |
| RESERVED_HOLDBACK_SUMMARY | WeChatPay | EUR | 2021-12-12 | -1222.92 |
| RESERVED_HOLDBACK_SUMMARY | WeChatPay | EUR | 2021-12-13 | 1593.24 |
In the sample above, an SGD holdback for Alipay was released to be paid out on the SETTLEMENT_DATE, as was a EUR holdback for WeChatPay. Alipay also had USD and EUR amounts held back; WeChatPay had an amount also held back.
Note
The footer holdback values are aggregate for all LPM and Currency pairings across all merchants.
Individual transactions can be identified in the holdback as follows (within the aggregate file):
| MERCHANT_ID | PAYMENT_METHOD | EVENT_TYPE | EVENT_COUNT | AGGREGATE_AMOUNT | CURRENCY |
|---|---|---|---|---|---|
| MERCHANT1 | Alipay | RESERVED_HOLDBACK | 1 | -100 | USD |
| MERCHANT1 | Alipay | TRANSACTION | 3 | 100 | USD |
Based on the Event Count for the Event Type as Transaction, one can look up the individual transactions in the volume-per-transaction file as shown in the table below:
| MERCHANT_TX_ID | TX_ID | PAYMENT_REFERENCE | EVENT_TYPE | EVENT_TIMESTAMP | MERCHANT_ID | PAYMENT_METHOD | AMOUNT | CURRENCY | COUNTRY |
|---|---|---|---|---|---|---|---|---|---|
| 1234567890 | 99954321 | ABC1234 | SUCCEEDED | 2022-02-08T00:00:09Z | MERCHANT1 | Alipay | 20.00 | USD | CN |
| 234567890 | 99965432 | DEF2345 | SUCCEEDED | 2022-02-08T00:10:13Z | MERCHANT1 | Alipay | 35.00 | USD | CN |
| 345678901 | 99976543 | EFG3456 | SUCCEEDED | 2022-02-08T00:20:54Z | MERCHANT1 | Alipay | 45.00 | USD | CN |
Important
When files are delivered in the SFTP, they will be zipped in a gzip format (*.gz). Please ensure that your system can extract gz files before processing.
Updated 7 months ago