Enterprise and Payment Partner Billing Report
Machine-processable reporting for companies that prefer automation to ingest and process transaction data
Overview
The Enterprise and Payment Partner Billing Report provides a tool for both enterprise groups and payment partners to perform reconciliation against completed transactions relating to a payment flow both in-store and online.
This report covers successful payments that are captured as well as partial and full refunds.
This report should be used to aid an enterprise group or payment partner in reconciling successful cash flow impacting transactions, reconciling against invoice amounts and bank account payments.
Change History
| Report Version | Date | Changes |
|---|---|---|
| 1.0 | 23 Mar 2024 | Initial release of enterprise and payment partner reporting |
| 1.1 | 17 Jun 2024 | Introduction of new Partner Merchant ID field to support partners needing to passthrough sub-merchant identifiers for reconciliation |
Onboarding
In order to make use of Enterprise and Partner Reporting, you need to be onboarded to our reporting service.
The following information is needed in order to do this:
Information Required
- Private-Public Key Credentials - these will operate either by:
- ShopBack generates an RSA public-private key pair, both of which are supplied to the partner in secure zip form (password supplied separately) OR
- The partner providing us with an RSA public key to register
- Key setup must meet one of the schemes supported here if the partner is providing the public key
- A list of IP addresses from which the partner will access the Secure File Transfer Protocol folder
- Optionally: The list of merchants for which the partner wishes to receive consolidated reporting.
- Per country, ShopBack supports grouping reporting for multiple billing entities where a partner is handling settlement for multiple merchants.
- Note, if a merchant is available in multiple countries, reporting will be isolated by country e.g. one report for SG, another report for MY
Actions
The partner’s account manager will verify the mapping of billing entities to reporting group as required for the engineering operational team to finalise setup.
Once the above information is provided, ShopBack will provision setup for accessing the reporting service and supply
- the server (sftp.shopback.com) and
- folder name to access reports.
The partner may then test access to the sFTP folder using their chosen sFTP client by:
- Connecting to the server sftp.shopback.com using the private key per the information required in point 1 above
- Accessing the relevant folder provided by ShopBack
Report Format
Comma Separate Values (CSV) File Format in UTF-8 encoding
Content Type: text/csv; charset=UTF-8
How to handle quotes
Strings will be quoted in double quotes "" per the CSV standard RFC 4180
Event Records in this Report
The following events will generate records in the report:
- A successful in-store dynamic QR POS payment initiated from a payment channel e.g. a payment terminal
- A successful in-store static QR payment that results in a payment channel notification
- A successful online payment initiated from an online integration
- A successful partial or full refund attempt from a payment channel
Report Delivery
Reports will be provided in a specific folder on ShopBack’s sFTP server sftp.shopback.com in the relevant Group ID folder
Individual reports will be delivered for each country in which the enterprise group or payment partner operates.
Report Frequency
Daily on ShopBack billing business days being generally Monday to Friday.
File Name Structure
File Name Format
EPR_{Group ID}_{Report ID}_{Report Version}.{Major Version}.{Minor Version}.{format}
EPR_000001928_20220826_0001.01.01.csv
| File Name Part | Description |
|---|---|
| EPR | A 3-letter indicator for the type of report - in this case the enterprise and partner reconciliation report. |
| Group ID | The identifier used to indicate which group of billing entities to include in reporting. |
| Report ID | Using intended generation date format of yyyymmdd matching the value in the header of the file |
| Report Version | Sequence number for the report, starting at 1, to allow for file regeneration in the event of problems or need to regenerate in the format nnnn |
| Format Major Version | The major version number for the report format in the format nn |
| Format Minor Version | The minor version number for the report format in the format nn |
| Format | The file format - in this case csv |
File Report Structure
| Row Type Code | Description |
|---|---|
| HH | Header Headings - usually just the first row if needed - maps to the column labels in the header layout itemised below |
| HR | Header Row - usually just the first or second row - provides some initial information relating to the file - refer to the expected values in the header layout itemised below |
| BH | Body Headings - maps to the column labels in the body layout itemised below |
| BR | Body Row - the main content of the file - refer to the expected values in the body layout itemised below |
| FH | Footer Headings - usually the second to last row if needed - provides headings for the footer content section - maps to the column labels in the footer layout itemised below |
| FR | Footer Row - usually just the last row - provides some footer information relating to the file if needed - refer to the expected values in the header layout itemised below |
Header Layout
| Column Name | Column Label | Column Position | Description | Expected Format | Example Values |
|---|---|---|---|---|---|
| Row Type | row_type | 1 | The identifier for this row of data | HH - for the header row HR - for the data | HR |
| Group ID | group_id | 2 | The identifier used to indicate which group of billing entities to include in reporting | A grouping identifier, may be in an 18 character format | 5003000000D8cuIQAA |
| Report ID | report_id | 3 | An identifier for the report generated based on timestamp | yyyymmdd | 20220802 |
| Report Generation Timestamp | report_timestamp | 4 | The timestamp for the report generation | ISO 8601 - yyyy-mm-dd Thh:mm:ssz | 2022-08-02T02:23:01Z |
| Report Start Timestamp | report_start | 5 | The starting timestamp for transactions relevant to the report | ISO 8601 - yyyy-mm-dd Thh:mm:ssz | 2022-08-01T00:00:00Z |
| Report End Timestamp | report_end | 6 | The ending timestamp for transactions relevant to the report | ISO 8601 - yyyy-mm-dd Thh:mm:ssz | 2022-08-01T23:59:59Z |
| Major Version | major_version | 7 | The major version number for the report format | nn | 01 |
| Minor Version | minor_version | 8 | The minor version number for the report format | nn | 00 |
| Report Version | report_version | 9 | Sequence number for the report, starting at 1, to allow for file regeneration in the event of problems or need to regenerate | nnnn, starting at 0001 | 0001 |
Body Layout
Records will be sorted by timestamp in ascending chronological order.
| Column Name | Column Label | Column Position | Description | Expected Format | Example Values |
|---|---|---|---|---|---|
| Row Type | row_type | 1 | The identifier for this row of data | BH - for the header row BR - for the data | BR |
| Record ID | record_id | 2 | This is a record identifier mapping to the event taking place | [billingId] | e533da1a-e16c-4de3-bb04-f369e7d42034 |
| Billing Code Type | billing_code | 3 | The type of the transaction record | See Billing Code Types | CAPTURE |
| Billing Code Sub Type | billing_sub_code | 4 | A sub-type for the transaction record - only used in itemised reports | See Billing Code Sub Types | CAPTURE |
| Transaction Timestamp | transaction_timestamp | 5 | A timestamp for the transaction at the point of capture or point of refund depending on transaction type in ISO-8601 format | ISO 8601 - yyyy-mm-ddT hh:mm:ssz | 2022-08-01T14:29:33Z |
| Payment Reference ID | payment_reference_id | 6 | The payment reference ID provided in the request, where applicable (only successful payments will show here) | If POS - the referenceId, if Online - Merchant Order Ref or Order ID if provided during API call, otherwise Order UUID | Order-Ref-1011 |
| External Order Number | external_order_number | 7 | The brand or merchant’s external order number identifier related to this transaction | Currently always blank - for future use | |
| ShopBack Order ID | customer_order_id | 8 | The customer-facing ShopBack Order ID shown to the customer in the ShopBack app | yyyymmdd-nnnn-AAAA | 220801-0679-YVUY |
| Product Code | product_code | 9 | The product to which the particular transaction relates such as PAY or PAYIN3. | String mapping according to product codes shown for all relevant records except tax records | PAY |
| Country | country | 10 | The two letter country code in uppercase letters | ISO-3166-1 alpha-2 | SG |
| Payment Channel | payment_channel | 11 | The payment channel through which the transaction took place - for the POS API, this would simply be POS | CUSTOMER - for in-store Static QR POS - for POS API ONLINE - for online API BILLING - for specific system adjustments such as resolution of tax rounding end of month PORTAL - for portal refunds ADMIN - for SB initiated refunds ADJUSTMENT - for adjustments | POS |
| Payment Channel ID | payment_channel_id | 12 | The payment channel ID through which the transaction took place - for the POS API, this would be the posId that was used to execute the transaction. For adjustments, this will show the reason for the adjustment. | CUSTOMER - the POS terminal ID If POS - the POS terminal ID If ONLINE - the e-outlet ID If PORTAL - the user ID If ADMIN - blank If ADJUSTMENT - the text reason for an adjustment | a1w2x000001ZGs6AAG |
| Transaction Currency | transaction_currency | 13 | The currency of the transaction in uppercase letters | ISO-4217 3-letter | SGD |
| Rate | rate | 14 | A rate relevant to the amount calculation. This will depend on the different types of billing records that are generated and helps to understand how the amount is derived | A percentage value expressed as a floating point up to 2 decimal places | 4.5 |
| Net Amount | net_amount | 15 | The net value of the record after relevant calculation using an applicable rate with decimal and decimal places included after any fee credits or debits applied but before any settlement adjustments | Zero, positive (if payment) or negative (if refund) amount with decimal places SGD - up to 2 decimals MYR - up to 2 decimals AUD - up to 2 decimals | 217.32 |
| Settlement Currency | settlement_currency | 16 | The currency of the settlement in uppercase letters | ISO-4217 3-letter | SGD |
| Settlement FX Rate | settlement_fx_rate | 17 | This is the FX rate applied on the settlement amount applicable to 4 decimal places | Floating point value up to 4 decimal places | 1.0000 |
| Settlement Adjustment | settlement_adjustment | 18 | This is the amount of any adjustments that should be deducted from the net transaction amount | This will be a zero or positive value to indicate any deduction from the net transaction amount Zero, negative (if offsetting) or positive (if adding back) amount with decimal places SGD - up to 2 decimals MYR - up to 2 decimals AUD - up to 2 decimals | 0 |
| Settlement Amount | settlement_amount | 19 | The net settlement value of the transaction (signed if negative) with decimal and decimal places included after any fee credits or debits applied but before any settlement adjustments | Zero, positive (if payment) or negative (if refund) amount with decimal places | 217.32 |
| Billing Entity | billing_entity | 20 | The billing entity (also referred to as invoicing unit or bank account) to which the settlement will be specifically allocated | An alphanumeric string, no spaces | 00001928 |
| Partner Merchant ID ❗️ New with version 1.1 | partner_merchant_id | 21 | The partner’s merchant ID, passed to ShopBack during an API call as the merchant ID in the partner object | An alphanumeric string, can contain spaces | 1928112 |
| Narration | narration | 22 | Field to cover any narration or comments relating to a record | Currently always blank - for future use |
Billing Record Code Type Fields
| Billing Code Type | Billing Code Sub Type | Description | Notes |
|---|---|---|---|
| CAPTURE | SALE | Amount captured for an order/payment with a customer | Transaction has been authorised and charge completed for capture |
| FEE | MDR | Merchant Discount Rate - Merchant Fee Applied | Fees will only be reported where settlement takes place with the partner or group |
| FEE | TRANSACTION | Transaction Processing Fee - for applying a capture | Fees will only be reported where settlement takes place with the partner or group |
| FEE | CASHBACK_RETURN | Return cashback fee - for cashback to be charged depending on the cashback model | Fees will only be reported where settlement takes place with the partner or group |
| FEE | CASHBACK_OFFSET | Return cashback discount - for cashback to be charged depending on the cashback model | Fees will only be reported where settlement takes place with the partner or group |
| FEE | PLATFORM | Platform Fee - for the provision and access to the ShopBack platform and services | Fees will only be reported where settlement takes place with the partner or group |
| FEE | PROMO | Promo Fee - Fee for applying ShopBack merchant-funded promos | Fees will only be reported where settlement takes place with the partner or group |
| FEE | PROMO_OFFSET | Promo Fee - Discount for applying ShopBack merchant-funded promos where ShopBack or another partner funds the promo | Fees will only be reported where settlement takes place with the partner or group |
| TAX | SALE | Sales tax as applicable to any fees | Taxes will only be reported where settlement takes place with the partner or group |
| REFUND | SALE | Amount refunded for an order/payment with a customer | Transaction has been refunded partially or in full |
| FEE | REFUND | Refund Processing Fee - for applying a refund | Fees will only be reported where settlement takes place with the partner or group |
| FEE_REVERSAL | MDR | Reversal of Merchant Discount Rate - Merchant Fee Applied | Fees will not initially be reported until cash flow settlement is direct with the payment partner |
| FEE_REVERSAL | CASHBACK_RETURN | Reversal of Return cashback fee - for cashback to be charged depending on the cashback model | Fees will only be reported where settlement takes place with the partner or group |
| FEE_REVERSAL | CASHBACK_OFFSET | Reversal of Return cashback discount - for cashback to be charged depending on the cashback model | Fees will only be reported where settlement takes place with the partner or group |
| FEE_REVERSAL | PLATFORM | Reversal of Platform Fee - for the provision and access to the ShopBack platform and services | Fees will only be reported where settlement takes place with the partner or group |
| FEE_REVERSAL | PROMO | Reversal of Promo Fee - Fee for applying ShopBack merchant-funded promos | Fees will only be reported where settlement takes place with the partner or group |
| FEE_REVERSAL | PROMO_OFFSET | Reversal of Promo Fee - Discount for applying ShopBack merchant-funded promos where ShopBack or another partner funds the promo | Fees will only be reported where settlement takes place with the partner or group |
| TAX_REVERSAL | SALE | Reversal of Sales tax as applicable to any fees | Taxes will only be reported where settlement takes place with the partner or group |
| REVERSAL | SALE | Adjustment as required to total settlement | Reversal of any principal amounts due to an adjustment |
| TAX_ADJUSTMENT | SALE | End of month tax adjustments to deal with rounding at transaction level vs rounding end of month | Holding of sales tax amount due to rounding under holding tax |
| TAX_ADJUSTMENT_REVERSAL | SALE | End of month tax adjustments to deal with rounding at transaction level vs rounding end of month | Reversal of sales tax due to rounding over holding tax |
Products in Scope
| Product Code | Description |
|---|---|
| PAY | Covers ShopBack Pay |
| PAYIN3 | Covers ShopBack PayLater with 3 instalments |
| VOUCHER | Covers ShopBack vouchers billed under consignment and redemption models |
| CASHBACK | Covers manual cashback that is billable |
| LOYALTY | Covers bonus cashback that is billable under payments loyalty |
| BILLING | Covers billing specific events |
Footer Layout
| Column Name | Column Label | Column Position | Description | Expected Format | Example Values |
|---|---|---|---|---|---|
| Row Type | row_type | 1 | The identifier for this row of data | FH - for the header row FR - for the data | FR |
| Record Count | record_count | 2 | The number of billing records in the report | Unsigned integer | 23 |
| Settlement Currency | settlement_currency | 3 | The currency of the settlement in uppercase letters | ISO-4217 3-letter | SGD |
| Settlement Credits Amount | settlement_credits | 4 | The credits value of all credit transactions (signed if negative) with decimal and decimal places | Optional negative sign, amount with decimal places, SGD - 2 decimals, MYR - 2 decimals, AUD - 2 decimals | 1023.21 |
| Settlement Debits Amount | settlement_debits | 5 | The debits value of all debit transactions (signed if negative) with decimal and decimal places | Optional negative sign, amount with decimal places, SGD - 2 decimals, MYR - 2 decimals, AUD - 2 decimals | 23.10 |
| Settlement Net Amount | settlement_net_amount | 6 | The net settlement value of all transactions (signed if negative) with decimal and decimal places | Optional negative sign, amount with decimal places, SGD - 2 decimals, MYR - 2 decimals, AUD - 2 decimals | 1000.11 |
| Payment Reference | payment_reference | 7 | An identifier that will be provided to the remitting bank to link a bank transfer to the transactions in this file, where payouts are performed | Text Reference no longer than 15 characters | 02-Jan-2024 |
| Settlement Period | settlement_period | 8 | The estimated number of business days when payment settlement will be completed or payment is expected to be received depending on the business model | Unsigned integer | 3 |
Sample Report
Updated 5 months ago