Call it Pareto’s Law or the 80/20 Principle, either way it’s a powerful tool that can give you valuable insight and help you understand your customers better. This principle basically states that 80% of your business comes from 20% of your Clients. That's why it's useful to figure who who the top 20% of your Clients are so you can retain their business and most of your revenue.
This is a somewhat complicated process but can be very rewarding. To create a Pareto report, use the following steps:
- Go to Accounting > Payments.
- Hover over Actions. Select “Export.” A pop up will appear; set the date range to any time range you wish.
- Click Export. Click the "X" on the Export dialog to close it. Your export will be on the My Day page.
- Go to the My Day page. Click the name of the file to download and open it.
- In Excel, be sure to click “Enable Editing” and save the file where you will remember where it is.
- Click “Insert” at the top of the file, then choose “Pivot Table”. Be sure all of the data is selected, then click ok to close the pop-up.
- Excel will open a new pivot table in a new tab. You can rename the tab or leave it as “Sheet1.” Drag client name (from the right side of the screen) to the “Drop Row Fields Here” section. Then, drag “Amount” from the right side of the screen and place it in the “Drop Value Fields Here”. This groups your customer by name, thus showing the cumulative payment amount over the entire fiscal year.
- Click in the “Sum of Amount” field in most cases C3 and highlight the entire table. Copy (either Ctrl + C or Right Click > copy) the table.
- Click on an empty cell, either next to the table or on a different tab. Click the “Paste” down arrow and paste the values only. It should mirror the pivot table, but you will be able sort from Z to A.
- Click on the “Total” field but make sure not to pick multiple fields. Hover over “Sort & Filter” and click “Sort Z to A.” Now the cumulative payment amounts are listed from highest to lowest.
- Sum the payment amounts by clicking “AutoSum”.
- Create a cumulative amount column.
- Create a cumulative percentage column.
- Highlight your table in Excel and press ALT + F1. This is a quick way to create a chart.
- Right click in the Chart and click Select Data. Select Cumulative Amount and click Remove. Then click OK.
- Right click the chart and select Change Chart Series Type. Then click “Combo” and click the check box for Cumulative Percentage Secondary Axis. Make sure Cumulative Percentage shows “Line with Markers” under Chart Type. Click ok.
- Follow the line until it intersects with the 80% line. That is your Pareto cut-off. All customers on the left hand side comprise 80% of your revenue. Divide the amount of customers on the left of the 80% mark by total number of customers and you’ll know how many customers make up 80% of your revenue. Most likely, it will be in the 20% range, but could be much lower or slightly higher. E.g. if you have one extremely large client, 5% of your customers could make up 80% of your revenue. Or, if you are spread out a bit more evenly, 35% – 40% of your customers could make up your revenue. The latter is less typical.
- Go through and Tag these Clients in your SA account. You now have the ability to market towards your best customers, therefore significantly increasing the likelihood of new sales.
Other Pareto charts you can graph:
- Which 20% of the zip codes serviced comprise 80% of your revenue
- Which 20% of your services do customers choose 80% of the time
- Which 20% of your customers cause 80% of your time consuming problems