Customer analytics enhances the customer experience. In this tutorial we’ll show you how to manage the key performance indicators in Excel using the power of interactive dashboards.
Our free template is nothing else than a statement of customer habits from a relatively large excel data source.
Today’s article is an experiment on our part and its goal is to show a completely different dashboard from the already seen analyses.
Monthly Performance Analytics
The customer analytics template consists of two parts. The first one provides a review of sales achievements in monthly breakdown of a one year period.
We didn’t have any particular problem here, only we had to find the minimum and maximum values of the examined period (which in this case is one year) and mark them on the line chart.
What’s even more interesting is the sales section on the right hand side of the template. In this section with the help of a drop-down chart we enabled the monthly breakdown.
The data of the chosen month from the list is portrayed by three indicators. The first is the monthly whole sale; this is shown by the value in the big rectangle.
It’s not difficult to define this; we have gathered the products sold in the given month with the help of a pivot-table.
As a supplement we displayed in the given month the largest and the smallest income-bringing customer’s results. By using these indicators we can make important conclusions.
Customer Analytics – Data Source
On the picture below we can see a part of that excel worksheet which provides the data source.
You can fill this up with arbitrary data. We built the excel template so a completely personal template can be created from it. As usual this template also can be downloaded and used for free.
For those who not in a hurry we would like to write down in a few sentences what excel formulas helped us to define the values of the customer analytics dashboard.
In the “End of Month” column we classified the date of the sale (“Date Sold”) with the help of the EOMONTH() function.
With this we can tell any element of the list consists of several thousand components, what month it belongs to. (1 line equals one sale). After this the monthly breakdown can be easily made.
By using the RANK() function we can decide from the 12 months summed up data what month brings the most and the least income.
Where the value of the function is 12 that indicates the least income. We can easily determine the minimum and maximum values of the line chart.
The other functions can be found on the “calc” worksheet. There are some interesting solutions, we recommend you examine closely the used solutions even if the functions look familiar (INDEX, SUMIF, COUNTIF, MIN, MAX).
Effective Data Visualization using Excel
If you would like to seriously care for data visualization and excel dashboard creating, than you should consider a few small things.
One of the most important rules of excel data visualization is, that few is always more! In our opinion this statement is well supported by the customer analytics template introduced today.
Always think about that no need to pile up the data, but rather show the trend and process behind the numbers. Most of the time to achieve this enough to use a few well-chosen indicators.
Another important standpoint is the well-chosen design. For example one that is different in its font can better the overall image.
The font was used in the making of the customer analytics template is included for download. So your dashboard can have the same appearance.
There’s nothing else left to do but presenting the link where you can access the sample workbook, by clicking here you already can start the work!