Overall operational and data workflows for payment reconsolidation

This blog aims to cover a broad overview of operational and data workflows for the payment reconsolidation process.

1. Definition of prepaid vs postpaid

Figure 1: Prepaid vs postpaid payment method

Prepaid payment refers to when the customer makes payment before order is shipped. Vatico receives update of full payment and ships out the order. This includes orders paid by installment. Postpaid payment is defined as any other use case that does not fit in the definition of prepaid. This includes marketplace orders with Amazon and Tiki and orders that are paid cash on delivery (COD).

2. Postpaid payment flow

Figure 2: Postpaid payment process

When a customer selects a postpaid payment option, order information is sent to warehouse partners and full payment is collected after order is shipped. Postpaid payment methods include cash on delivery, bank transfers and QR code payment.

For cash on delivery specifically, order is shipped first and the customer pays cash on delivery. Shippers collect the cash and give back to their last mile service. Last mile delivery services then hold and transfer the money back to Vatico.

How is data used to facilitate the postpaid money flow?

Figure 3: Postpaid payment operational and data workflow

Use Case 1:

For orders placed on Shopee and Vatico and customers can choose to pay cash on delivery, order is shipped first and the customer pays cash on delivery. Shippers collect the cash and give back to their last mile service. Last mile delivery services then hold and transfer the money back to Vatico after order fulfillment is completed.

Use Case 2:

For marketplace orders where money that is collected by partners, payment information is loaded in Vatico’s data warehouse through partner API. This flow is independent from the payment flow in blue. This means that the data flow is periodically running to check if there is new payment regardless of how money is being collected in the running payment flow.  Even if payment reconsolidation is stooped, it does not affect the process of collecting money.

3. Prepaid payment flow

Figure 4: Prepaid payment process

When a customer selects a prepaid payment option, the customer makes payment first and Vatico receives an update of full payment then order is shipped. Prepaid payment methods include Stripe, bank transfer, VNpay for Vietnam and Wise for international orders. 

Figure 5: Prepaid payment process – Stripe example

An example of a prepaid money movement flow is when a customer pays through Stripe. When customers pay first through Stripe on marketplace platforms or on Vatico’s website, immediately after an order is placed, Woo receives a notification that order is paid.  Woo sends order information to our warehouse partners to fulfill orders. When money is transferred from Stripe to Vatico, payment status will be updated by Finance in the data warehouse.

Figure 6: Prepaid payment operational and data workflow – Stripe

Here is an example of how data helps the process of financial reconsolidation for the payment method of Stripe in Figure 6. When notification of order paid is received, payment status = confirmed.

Once customers successfully pay for their order by card to Stripe, Stripe sends an update to Woo through a unique ID. When the status of the Stripe charge ID is complete, payment_status = confirmed. When a customer fails to make a payment and repay, there will be a trigger for the system to update as well. Typically after a week, when money is transferred from Stripe to Vatico, payment status = paid. 

Every week, Stripe sends a statement and Finance will check if the orders in the statement has been paid on an item level and if the amount in the statement is the amount received in the bank. If the amount is correct, payment status = paid.

4. Fulfillment and payment flows together

It is also important to note and understand that fulfillment and payment flows run independent of each other and can function without each other. In the automated process, certain triggers help sync and realign both fulfillment and payment so data is more complete and timely in the data warehouse.

For example, for COD, when customer selects option to pay cash on delivery, order_status will be marked as processing. Once payment is received from last mile delivery partners, payment information is extracted, loaded and transformed through the partner API to the CRM and order_status = paid.

Another example for prepaid payment, when finance checks and reconsolidates that revenue reported is correct, payment status = paid and order status (fulfillment) = paid. 

In someway, payment and fulfillment are indirectly related, however it is never directly related and we can have two independent process. If one fails, the other can still run.

5. Learnings from manually processing statements 

Generally, information is processed through data to represent entities, trigger rules and actions. On the side of data, processing the statement manually has allowed one to see and understand payment data in different levels and hierarchy of information. 

First, each marketplace has a different definition of revenue from Vatico. For example, Tiki’s revenue is defined as the value of goods to match the revenue in Vatico’s CRM. While for Lazada, the revenue is defined as the unit price – promotions. Hence, there is a need to record and align payment data from different platforms with different definitions.

However, the reality is that the payment data and information cannot always be easily obtained directly from the platform for reconsolidation purposes. This was the case for Lazada. For instance, when reconsolidating payment for Lazada, it was found that Lazada organises cost and revenue based on time. Hence, there might be duplicates and one order can be present in two files and have two statement periods. However, for revenue, we wish to focus and look at the cost related to order (instead of logistic or fulfillment costs that are not related to revenue but labelled as cost under statements section for Lazada). On top of multiple statement periods, to check if the order had the correct revenue, the payment data had to be found under two different files – statement overview and order overview. Statement overview file contained all the orders that are marked as paid by Lazada but did not provide the value for unit price and promotion price. These two information was found in another file for order overview.

After reconsolidating payment on Lazada, there were two approaches that we found that we could do. First, to search order that is not paid in CRM and go to partner marketplace (Lazada in this case) and check if it is confirmed and paid. Alternatively,  we could search from statement level in partner platform which statement is not reconsolidated and consolidate orders that statement with orders in CRM.

Figure 7: Comparison between the two approaches of reconsolidating payment

From here, it was realized that payment data can be understood from being organized on an order level and a statement level. In conclusion, when a piece of information or discrepancy is found, we can identify it at different levels or investigate and find a way to get the information we need from different sources of data.