Tableau Project for a Digital Graphics Study. Request was to analyze sales over a period of 5 years, from 2018 to 2022.
To view the Tableau visualization please visit my Tableau profile: Studio Design with Customer Analysis
The requirements were to verify:
- sales
- quantity of sales
- quantity of products sold
- quantity of customers
for each year, comparing the values with those of the previous year.
- A plus would have been to have a geographical visualization of these values distributed on a map of Italy.
- The client specifically requested a dedicated dashboard that contained a single chart providing an overview of the sales of each product for each year.
- A very important element for the client was to improve customer retention, implementing a system to reactivate customers.
For this reason, I created a dedicated dashboard to see the situation of customers activity in general:
- Average Customer Lifetime
- Average Customer Lifetime Value
- New Customer Acquisition
- Customer Activity by Quarter (first, recurrent, last purchase)
Then another dashboard to go into more detail on what each customer spent during each year, with relevant meterics.
The data comes from fattureincloud.it. I was provided with 5 Excel files, one for each year. The project started in July, so at that time the file contained only sales up to June. Over time, the client sent me the data to integrate new information into the dashboards.
- The data cleaning was performed with Python: in the folder data quality there is an extract of the Data_Quality.ipynb file. Obviously not the complete file is shown because of data privacy protection. With data cleaning I standardized and made the names of the cities consistent for the subsequent geolocation.
- I merged the 5 sources into a single file, and cleaned it of unnecessary columns. This is how the final output looks like:
- I created a relational table with geolocation data for each customer, to improve dashboard performance.
The KPI Dashboard displays the values for each year, allowing for a comparison with the previous year, for the following metrics: sales, quantity of sales, quantity of products sold, and quantity of customers. It is possible to select the reference year (from 2018 to 2022).
- Sales by category
- Top 5 products on sales
- Top 10 cities on sales
- Nr. of Orders by category
- Top 5 products on nr. of orders
- Top 10 cities on nr. of orders
- Product quantity by category
- Top 5 products on product quantity
- Top 10 cities on product quantity
- KPI (switchable between: sales, nr. of orders, product quantity) by region
- KPI (switchable between: sales, nr. of orders, product quantity) by cities
- Option to render the map by region or by city
- Option to select a specific region
Contains a single chart providing an overview of the sales of each product for each year.
Product Revenue Dashboard
- Avg. Customer Lifetime
- Avg. Customer Lifetime Value
- Option to choose view between:
- Customer Activity
- Retention Rate
The Customer Activity view contains:
- New Customers Acquisition
- Purchase Frequency
- Customer Activity by Quarter from 2018 to 2022
The other view contains the Retention Rate broken down by Elapsed Quarters vs. the Customer Acquisition Quarter. The Retention Rate is colored with Orange-Blue Diverging color, where orange is the lowest and Blue is the highest value.
Customer Growth Dashboard - Retention Rate view
- Customer Purchase Summary
- Revenue by Client Over Years
- Achievements of the Customer Regain Efforts
As of the analysis processing start date on 10/06/2022, there were 316 customers who had not made a purchase in over a year.
Thanks to the analysis conducted, the sales account manager was able to take action to re-engage customers and increase sales in the second half of the year.
As a result of the Customer Regain Efforts, 21 customers who had not made a purchase in over a year were successfully regained, representing 6.6% of the potential customer base. These customers contributed 20.711€ to revenue, which would not have been realized without the intervention of the Customer Regain Efforts.
Also there has been an increase in:
- Average Customer Lifetime from 7.3 to 8.2 months (+12.3%)
- Average Customer Lifetime Value from 857.8€ to 943.3€ (+12.2%)
before and after Customer Regain Efforts
The Retention Rate has increased significantly, as can be seen from the chart Retention Rate up to 09/06/2022.
Retention Rate up to 09/06/2022 (before Data Analysis)
Barbara Callegari
To learn more about the author visit my LinkedIn Profile