How to Audit Lazada Report in 10 sec. New format and New tips!
Lazada has changed its data format in Feb 18. This blog is to address this New Change and help readers analyze their report better in 10 seconds with New Tips.
Lazada reports is a always popular issue in the community. One of the most anticipated moment for sellers is to open their Finance Statement 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.
Additionally, Lazada had lowered their commission rate in respond to Shopee no commission strategy. Thus it will be expected that Lazada will lowered their operation cost, hence Sellers will have to depend more on themselves, in return for lower commission rate.
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 using Excel.
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:
1. Column B: “Transaction type” which describe the category for the amount
2. Column C: "Fee Name" which describe the transaction detail
This 2 column is actually linked as below table.
3. Column F: Seller SKU
4. Column H: “Amount” which show the amount deducted (with -) or paid (positive number) to seller
5. Column N “Order No” which describe the seller specific Order No
6. Column 0: “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. This a user friendly and powerful data tool which do not require any amount of coding for beginners. Notice the new layout. On the right side is the PivotTable Fields.
The Key is to play on the PivotTable Fields.
1st Tip: To check each Order No. Sales and relative payments.
1. Click on “Fee Name”, hold the mouse button and pull it to Columns in the circled window.
2. Click on the “Order No”, hold the mouse button and pull it to Row in the circled window.
3. Click on the “Amount”, hold the mouse button and pull it to the Values in the circled window. The result should as below:
4. Then click on small arrow the “Amount” and select “Value Field Settings”
5. 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 "Commission" with Item "Credit Price"
Then use filter or other method suitable for you to find those commission which is abnormal.
:) YAHOO!!!!!! In the new Lazada commission rate, I can see some 0% commission Already!
Notice Most of of the 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 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.
2nd Tip: Check The Hottest SKU and its Quantity and Sales.
1. Click on "Transaction Type", hold and drag it to Filters
2. Click on "Seller SKU", hold and drag it to Rows
3. Click on "Seller SKU", hold and drag it to Values. Select "Count" as it Values Field
4. Click on "Amount", hold and drag rag it to Values. Select "Sum' as it Values Field
5. At the excel worksheet, Select Transactions Type", Select "Orders - Item Charges"
And then you have it. Your Top SKU with its Quantity and Sales at that period.
There are so many things you can do with pivot table, and it depends on your objectivity and creativity haha
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!