How to Audit Lazada Transaction Report in 10 Seconds
Today Blog is more technical in regards to Lazada. This is a popular issue and question in the community. Lazada Transaction or Payment to Seller. It is one of the most anticipated moment for members to see their hard earned monies “Ka-Ching” into their accounts. But then only the total amount was seen in the account. Well if it is more than expected, it’s ok. However if the amount is lower than expected, then they will had sleepless night wondering if Lazada has "curi" their hard earned monies.
Additionally in the Facebook Lazada Seller Group, often sellers claimed that Lazada has all sort of issue and cheats in the transaction by giving just a simple snap shot to claim that this is true. However in return, Lazada normally remained silent, or a best will reply they will look into it, which further make the sellers unhappy. Thus a lot of sellers start to doubt that they had receive less than what is entitle for the sellers.
This motivated the worried seller to download the transaction report to see if they are also cheated as reported. If you haven’t download any transaction report, it is fairly simple. Goto Finance then Transaction as the picture below.
Then select the date and select export as circled. Click the download and viola! You got the transaction report for the selected date.
However it is the start of another headache. Community Members and sellers often complained that it is so painstakingly to analysis or audit this report due to its data structure. It is not as simple as having a simple order with all its detail information in single row. In fact for a single order, the information exist in many rows. In such this cause a lot of confusion and sellers hate the moment of checking the lines one by one as it is very time consuming. Some just give up and don’t bother.
Actually this is the data structure for Lazada system and it is not really a bad data structure. However it is not suitable for normal person which has no knowledge about data structuring. Thus this blog is to help the reader to learn to check and audit their transaction report in very short time.
First this is the standard transaction report. Its in csv. You can just save it as xlsx in Excel. There is many columns. Thus I hide some column and only show the column which I think is the most important
The important columns are:
Column B: “Transaction type” which describe the reason for the amount
Column G: “Amount” which show the amount deducted (with -) or paid (positive number) to seller
Colum M: “Order No” which describe the seller specific Order No
Column N: “Order Item No” which describe the “order item No” in “Order No”. The can be more than one “Order Item No” in “Order No”
In Excel, Highlight all The Data by using Ctrl A, then Select Insert, Then Pivot Table and click on it.
Then Just Select OK
A new worksheet will be created with below format. Notice the new layout. On the right side is the PivotTable Fields. It the top place, it list all the column header.
Click on “Transaction Type”, hold the mouse button and pull it to Columns in the circled window. Click on the “Order No”, hold the mouse button and pull it to Row in the circled window. Click on the “Amount”, hold the mouse button and pull it to the Values in the circled window. The result should as below:
Then click on the “Amount” and select “Value Field Settings”
Then Select “Sum” and then OK!
And Viola!, Each Order ID had single Row of all the details of the Transaction! If you are familiar, this take you around 5 seconds only!
I then now select the whole pivot table, Copy and Then Past as Text in another worksheet. I adjust the 1st column so that it is easily understandable by reader. See! We had achieve the objective.
Normally seller want to check if they overcharge on the commission. You can achieve this by just in another column, use Excel formula to divide the “Orders-Lazada Fees-Commission” with the “Orders-Item Charges-Item Price Credit”.
Then use filter or other method suitable for you to find those commission which is abnormal.
Notice some of the “Orders-Lazada Fees-Shipping Fee (Charged by Lazada)” is empty.
Don’t be too happy and thought Lazada is giving free shipment, the payment will come later and you will see a single line in later report without any other charges like the circle example. Guess that Lazada only bill seller after 3PL billed lazada.
This is where a lot confusion for sellers thinking they have been double charge on shipment. In fact it is carry forward from last period of report.
Hope with this, it helps you save tons of time to check the line one by one. If you need help, just send me some of your transaction report (firstname.lastname@example.org) and I will send you back the copy with pivot table and audit. It’s Free of Charge! Because it really only use 10 second of my life. If you want to do something for me, just like my facebook page! Cheers!