For the same store in the same month, the logistics fees deducted in the platform backend, the total freight in ERP, and the amount on the logistics provider's bill often do not match. The gap usually comes from differences in three definitions: the platform deducts fees based on settlement, ERP records costs based on orders, and the logistics provider bills based on chargeable weight and batch. The tools are not calculating incorrectly; it is that the term "logistics cost" does not refer to the same amount of money in these three places.
Combining these three numbers into a difference that can be clearly explained is the starting point for building the dashboard. The following sections proceed in the order of "decision questions—definition card—order wide table—allocation calculation—layout and trends—go-live reconciliation." Throughout the process, only the logistics cost vertical metric is run through end to end; once it works, replicate it to other metrics such as advertising and inventory.
First define three decision questions, then determine the dashboard fields
The value of a dashboard is not in having many metrics, but in each metric corresponding to an action to take next week. The first version answers only three questions:
- Which stores, countries, or SKUs have significantly high logistics cost rates and need channel switching or price adjustments?The corresponding metric is the logistics cost rate by store, by country, and by SKU; the owner is operations.
- Which cost segment is eating into profit: first-mile, last-mile, packaging materials, returns, or duties?The corresponding metric is the logistics cost composition breakdown, and the owner is Finance.
- Which specific channel and which pricing will be changed next week?The corresponding metrics are logistics cost per order and channel comparison, and the owner is the Operations Lead.
If a field does not have a corresponding question and owner, it will not be included in the first version for now. Metrics such as on-time delivery rate and receipt exception rate are meaningful, but they will lengthen development time; add them after the main scope is stable.
Create a metric definition card: what counts as orders, logistics costs, and gross profit respectively
The definition card is the foundation of the dashboard. Each metric has six fixed columns: business definition, calculation and allocation method, inclusion scope, time attribution, currency and exchange rate, and owner; the entire team pulls data according to the same card.
| Metric | Business definition | Calculation and allocation | Time attribution | Owner |
|---|---|---|---|---|
| Valid order volume | Orders that have shipped, have not been canceled, and have not been fully refunded | Deduplicated count by store ID + order number | Ship date | Operations |
| Total logistics cost | First-mile + last-mile + packaging handling + returned and lost shipments + customs duties and miscellaneous fees | Details are aggregated on a per-order basis, and bill fees are allocated by chargeable weight | Shipment month; cross-month bills use accruals | Finance |
| Logistics cost per order | Average logistics cost borne per order | Total logistics cost ÷ valid order count | Same period and filters as the numerator | Finance |
| Logistics cost rate | Logistics costs as a proportion of sales | Total logistics cost ÷ Product sales (tax-exclusive, unified exchange rate) | Same period and filters as the numerator | Finance |
| Gross profit (after logistics) | Gross profit after deducting product cost, platform commission, and logistics cost | Sales − Product cost − Platform commission − Total logistics cost | Shipment month | Operations lead |
The most easily blurred item is logistics cost. If only last-mile shipping is counted, the cost ratio is bound to be low, and decisions to switch channels will also be wrong. The definition sheet must clearly state: whether first-mile includes domestic pickup and customs declaration; whether packaging materials are based on actual consumption or a fixed amount allocated per order; and whether returns and lost shipments are accrued at historical ratios or recorded as actually incurred.
Exchange rates must likewise be fixed in advance: lock a conversion rate for the settlement month, keep original-currency amounts as usual, and avoid recalculating historical months with the current-day rate every time the dashboard is refreshed. For the platform commission treatment in the gross profit line, refer to,Cross-Border E-Commerce Platform Commission Rates Explained: Fee Rate Comparison Across Major Marketplaces and Cost Accounting, and include both explicit rates and hidden fees.
Use an order-level detail wide table to hold multi-platform data
Do not start with a summary table for multi-platform data; first create a detail wide table with one row per order and no aggregation. Once a summary table is miscalculated, it is impossible to trace back which order caused the problem; a wide table retains the original details, so any metric can be recalculated.
- Primary key and attribution: store ID, platform, order number, SKU, quantity, destination country, shipping date, logistics channel.
- Three weight fields: actual weight, volumetric weight, and chargeable weight—keep all three; later judgments about light/bulky goods and billing discrepancies depend entirely on them.
- Cost fields: last-mile shipping, first-mile batch number and allocation amount, packaging handling fee, accrual for returns and lost shipments, and customs duties and miscellaneous fees.
- Settlement fields: currency, conversion rate, settlement status (accrued / settled), data source (platform backend / ERP / logistics provider bill).
The source column may seem redundant, but it is actually the entry point for troubleshooting: when summary figures do not match, you can immediately distinguish whether it is a data entry error or a difference in billing definitions. When multiple stores operate in parallel, the correspondence between account entities and store IDs must be fixed; otherwise, the same order number will be conflated across different stores. For specific methods, seeAmazon Multi-Account Operations in Practice: How to Uniformly Manage Store Data and Compliance Risks; for aggregated cross-platform permission and environment setup, see further readingFrom a standalone Shopify store to multi-account eBay management: collaborative operating strategies for a cross-platform seller hub.
Allocate shipping bills to SKUs: four steps to calculate per-unit logistics cost
- Determine the chargeable weight.The chargeable weight is the greater of the actual weight and the volumetric weight, where volumetric weight = length × width × height ÷ channel billing factor. Factors vary by channel, so write them into the calculation card instead of filling them in from memory each time.
- Allocate first-leg freight.First-leg freight is billed by batch; allocate it to each order using "the order's chargeable weight ÷ total chargeable weight of the batch × total first-leg freight of the batch". For light and bulky goods, allocating by volumetric weight comes closer to the actual space occupied.
- Group last-mile charges and surcharges.Group last-mile freight, remote-area surcharges, oversize surcharges, and peak-season surcharges by order; do not merge them into a single "freight" field and split them afterwards.
- Handle returned items, lost items, and exchange rates.Register returns separately as recoverable and non-recoverable, and record lost items as accrued items based on claim progress; in months when the bill has not arrived, book amounts under the accrual rules and reverse the difference in the settlement month.
Order logistics cost = first-leg allocation + last-mile freight + packaging and handling + provision for returns and lost parcels + tariff allocation; average logistics cost per order = total logistics cost for the period ÷ valid order volume for the period. Differences in channel billing methods directly affect these two figures, so compare them before choosing a channel.Differences and cost gaps between cross-border e-commerce small parcels and dedicated line logistics.
Allocating first-leg costs by sales amount is only suitable as a transitional solution when weight data is unavailable. When heavy and light goods are shipped together, it underallocates heavy-goods costs, making SKUs that appear profitable actually lose money.
Dashboard layout: only four decision modules per screen
Place a set of global filters at the top of the dashboard: store, destination country, logistics channel, and date range. Filter conditions must match the time attribution in the metric definition card; otherwise, the filtered numbers will again fail to reconcile with finance.
The main body should contain only four blocks: top left, logistics cost rate (by store and country); top right, average logistics cost per order trend; bottom left, cost comparison by channel and country; bottom right, exception order list. The exception list is sorted into three categories—large deviation between billable weight and actual weight, unregistered returns, and missing billing amounts—and is used to assign tasks directly to operations. Share-based pie charts have no decision value at this stage and should not be included at all.
Keep only one main line in the trend chart: average logistics cost per order and cost rate
One line chart in the trend section is enough: the average logistics cost per order index and the logistics cost rate index on the same chart, with switching by channel and country. If both lines rise in the same direction, it is usually due to channel price increases or a higher proportion of bulky/light cargo; if only the cost rate rises while average cost per order remains stable, it is most likely a problem with the pricing or promotion mix.
Accruals and reversals can create step changes in the curve during settlement months. Making “Settlement status” a legend toggle helps avoid mistaking a methodology adjustment for a channel price increase. Keep a detail drill-through below the chart so that clicking any point returns to the corresponding order list.
Pre-launch reconciliation: only usable when three figures align
- Align the three-party monthly summary.List the platform settlement amount, total ERP order shipping fees, and logistics provider bill amount side by side by store and month.
- Reconcile volume first, then amount.Align order count and chargeable weight first. Nine times out of ten, amount discrepancies come from weight basis or missing surcharge records; checking amounts directly takes a detour.
- Review order by order once the threshold is exceeded.The discrepancy threshold is set by Finance in the basis card; once exceeded, export the order list, annotate the reason, and write it back to the source column.
- Lock in the refresh cadence.Specify the dashboard refresh frequency, bill freeze period, and discrepancy owner; before discrepancies are explained, do not report dashboard figures externally or use them for pricing.
FAQ
The logistics bill arrives only after the month ends; how can this month's dashboard avoid distortion?
Handle it according to the accrual rules in the basis card: use the per-kilogram price for the same channel from recent months multiplied by the current month's chargeable weight to accrue; in the settlement month when the bill arrives, reverse the difference; keep the "Accrued / Settled" marker on the order. The dashboard should show both the accrual basis and the settled basis at the same time; do not mix them into one line.
When multiple platforms use different currencies, should the dashboard use RMB or the original currency?
Keep both. The detail wide table stores original-currency amounts and conversion rates; the presentation layer defaults to RMB and switches back to the original currency when cross-checking against the platform backend. Exchange rates are locked by settlement month, and historical months are not recalculated using the current-day rate.
We don't have budget for ERP and BI. Can we start with Excel?
Yes. The minimum viable version is an order-level wide table plus a pivot table, with cost rates dragged out by store, country, and channel. The prerequisites are a unique primary key, a fixed metric-definition card, and monthly reconciliation; if any one of these three is missing, the larger the table, the less trustworthy it becomes.
Should first-leg freight be allocated by weight or by sales revenue?
Allocate by chargeable weight or volumetric weight first, as this is closer to the actual space occupied and freight cost. Allocating by sales revenue should only be used as a temporary fallback when weight data is unavailable; when heavy and light goods are shipped together, it systematically underestimates the cost of heavy goods.
Why doesn't the logistics cost rate on the dashboard match the financial income statement?
Usually because the scope and timing differ: the dashboard includes accruals and attributes costs to the shipment month, while the income statement records them by invoice and settlement month, and tariffs may also be included in procurement costs. Add a bridge table to the metric-definition card listing accrual differences, tariff classification, and exchange rate differences; when they don't match, check these three items first.
