We'll be releasing a series on how you can use your shipping data to view, manage, and analyze your shipping costs. Freightview's .csv export feature compiles all your shipment data into an Excel spreadsheet, so you can instantly view the metrics that matter to you. This week, we'll show you how to analyze your shipping data on a state-by-state basis.
Do you ever need to report what your average shipping cost is by state? Do you charge your customers a flat shipping rate based on the state they're in? Some of our users certainly do, so this week we'll discuss how you can do this, and how you might benefit. No more manual entry or estimations--we'll give you an instant state-by-state view of all your shipping data. Here's how:
1. Go to the Analytics tab on your Freightview account, and select the time frame of data you want to analyze (week, month, three months, year, or custom).
You can skip this step if you'd like to export all your shipping data.
2. Click the Export button.
This will generate a .csv file of the details of all the shipments for the timeframe you selected.
3. Delete your cancelled shipments.
To do so, simply sort column B (the "status" column"), and highlight and delete the shipments listed as cancelled.
This will ensure that you're only evaluating the shipments that have successfully shipped.
4. Put all your data in a pivot table.
Pivot tables make data easier to summarize.
To create a pivot table, you should first highlight all the data by clicking the little arrow in the top left corner of your spreadsheet.
Go to the "Data" tab, select the "pivot table" button, and then "create manual pivot table."
A window will appear asking you which data you want to analyze and where you want it to appear. Select the following settings and click "ok."
5. Customize your pivot table using the PivotTable Builder.
Since we're looking for how much you pay in shipping for each state, we'll customize it as follows.
Under "Field name" check the box next to "Destination state." Now click the word "Destination state" and drag it into the "Row Labels" square.
Then, under Field Name, check the box next to "Total price" and click and drag that into the "Values" box. Click the "i" button to the right of that value. This will allow you to change how you're measuring. Select "Average" from the list and click "ok."
There you go! You now have a complete analysis of your average freight cost by state.
You can use this information to determine the best price to charge your customers for freight shipping by state. Charging your customers a flat rate per state may have several benefits:
-You'll be able to cover standard shipping cost plus margin
-Because the rate is well-known and established, your customer knows what to expect
-You can analyze the data to determine whether you have the right partnerships with carriers
Want to do more with your LTL shipping data? Sign up for free trial today.