First of all, I will like to wish everybody a Happy Deepavali. I had enjoyed this holiday also with a short trip locally thus not posting much over last week.
After my first post for the Lazada 10 second series, there were some reader which shared their questions privately. One of the most popular question is Freight overcharged by Lazada. So this blog will focus on this issue as previous blog just briefly touch on this matter.
First of all, Lazada cooperate with 3PL (3rd Party Logistic) to deliver packages from Seller to Customer. Once a sales is made In Lazada system, an AWB (airwaybill) is generated for seller to attach with the packages. Then the 3PL provider will collect the goods or seller drop of at 3PL fulfillment center. 3PL provider supposed to scan the AWB in each stages of the delivery – hence the tracking function. Information is forwarded to Lazada system thru both party API (Application Program Interface). This will allow Lazada and customer to view the progress of the shipment and after the shipment is collected, 3PL providers billed Lazada, and Lazada will map the profit and cost to respective seller.
In this multi-party cooperation, it easy to get messy in human influenced processes. Somebody forget to scan the AWB, or the package was left in some dark corner, or sync error in 3PL system, late submission of Invoices and etc. This caused various delay and abnormal data is flying all over the transaction reports, causing massive confusion and headache to sellers.
In additional how do seller want to issue a valid complain when they are not sure? If they are not sure, they will most probably issue a false complain ticket. Even if the seller issue a correct complain ticket, are there enough capable data analysis in Lazada to find the source and validate the root cause of the error in multi – company scenario? Even if Lazada found the root cause of the error or if it is a false alarm, will Lazada spend more time to explain thoroughly with Seller which do not know much? Mostly, especially small seller can only vent their frustration without solution. Most just gave up and as long the revenue collected is able to cover the cost of material.
Thus the objective of this Blog is to share some humble knowledge, to at least help the seller identify the issues better. Hopefully this will reduce seller anxiety of being victimized and allow the seller to identify true error which affect them, and not focusing on noises (error which does not affect them).
The first step is to prepare the data for analysis. In this case, let’s select the data which is already confirm paid by Lazada to seller. This preparation is critical for subsequent discussion.
In Lazada Seller Center, go to Finance – Account Statement and choose to most recent Period (in this case 15 Sep 2017 – 30 Sep 2017) which has been paid. Then click export to download the raw file.
Then Open the raw file and save it as “Master.xls” excel file.
Goto Finance – Account Statement again and choose to previous Period (In this case 1 Sep 2017 – 14 Sep 2017). Then click export to download the raw file. Now without the header, copy all the data. Paste it in the data in “Master.xls” right under the most recent records.
Keep repeating to include more Period of transaction report. The more period you have, the more insights you will gained. At the end you will have a good amount of data in multiple statement period. Excel 2007 onwards support more than 1 million rows and congrats. If your data is more than that, and in such case, you don’t need to read this blog and employ expert to do it haha.
As this series of blog is to share something which can be done in 10 seconds, I will first discuss the first confusion.
1. Confusion caused by multiple entries for transaction type “Orders-Lazada Fees-Shipping Fee (Charged by Lazada)” and "Orders-Other Credit-Shipping Fee (Paid By Customer)"
In the master file, lets repeat the Pivot Table again. Control-A to select All data. Then Select Insert, Pivotable and click on it. Then click ok and a worksheet will be created。
In the right corner which is circled PivotTable Fields, Find the “Transaction Type”, hold the mouse button and drag “Transaction Type” to below “COLUMNS”. Report this by dragging “Order No” to below “Rows”. Report this by dragging “Amount” to “VALUES”. Below is the result of the action.
Now adjust the column and use cell alignment to make it more readable. The “1” in the pivot table means there is 1 record under the this transaction type for that specific Order ID.
Most data record will have that 1. “Orders-Lazada Fees Shipping Fee (Charged by Lazada)”, right click and select sort largest to smallest. However there is other Order ID which have more than 1 entries, hence the confusion. Goto a “1” under “Orders-Lazada Fees Shipping Fee (Charged by Lazada)”, right click and select sort largest to smallest.
Now you will find all those multiple entries Order Id. On the top, an Order ID 397624589 had a total 82 Entries for various transaction!
One of the great tool of Pivot table is to extract the data efficiently. For example if you want to know about the 82 entries. Just goto the 82, double click on it and viola, a new worksheet is created with 82 entries only for this Order ID!
Dont ask my why there is 82 entry for one single ID. There is many possibility. But don’t go and report this error to Lazada! As it is an insignificant error. Why insignificant, because it does not victimized seller. What we should concern is the total sum amount of freight charge to decide if we had been shortchange.
Now again at the pivot table. Goto Amount, left click, select value field setting, then in the value field settings window, select sum and ok.
Now this the Sum amount for multiple entries under a specific Order ID. Since we are interested in “Orders-Lazada Fees Shipping Fee (Charged by Lazada)”,
Lets again sort it. Goto any cell under “Orders-Lazada Fees Shipping Fee (Charged by Lazada)”, right click and select sort smallest to largest this time. Why smallest to largest? We are interested in the highest shipment charge which start with negative number. So the larger the negative number, the smaller it is.
Now the largest negative number should start with (Blank). This is a topic for another day. We should now focus on those with specific Order ID. Click the Row Label, Scroll to bottom, and deselect “Blank”
Next I normally encourage people just to copy the whole table and paste in another worksheet as text. Right after column H “Orders-Other Credit-Shipping Fee (Paid By Customer)”, I insert a new column and put in the formula of “Orders-Lazada Fees-Shipping Fee (Charged by Lazada)” + “Orders-Other Credit-Shipping Fee (Paid By Customer)”. In this example it is just simple F3+H3. Then I just copy this formula over all rows.
After that I use a filter and find the abnormal value after F3+H3, and the highest net charge is RM5.01 !. In this example, I had checked over 3000 Order ID and not a single Order ID is more than RM5.01
What Happen to the Order ID 397624589 with 82 entries, I search for this Order ID and found that after summing up all entries, the freight charges is only -5!
Thats the reason, as even I can drag out 82 entries with ease, I don't encourage others to analyze such error, as because it is insignificant. At the end we want to look at the higher level to identify if we are shortchanged, and it is not a case here. Even I report to Lazada, it is pointless. Thus needless for me to even explain in detail what happen to the 82 entries.
Remember, It does not matter HOW HIGH your of “Orders-Lazada Fees-Shipping Fee (Charged by Lazada)” are, or HOW MANY repeats entry. As long as Lazada reimburse you in “Orders-Other Credit-Shipping Fee (Paid By Customer)” and the number is corrects in total, then you are all right! And this can only be verified if data from multiple period is combine together。
After a few practice, you will be able to check this out in 10 seconds! And have a better sleep later at night hehe.
However this is the first of the series, and tune to more sharing of abnormality in Lazada Freight Charges calculation and analysis. Keep your master file for future discussion.
And if you do like this blog, please like our facebook, www.facebook.com/kkecommerce/ Cheers.