_edited_edited.png)
If you enjoyed learning Tableau basics and want to try out it's different features in real-life business analytics, then you are at the right place. With LOD expressions, we get a flexible tool to explore various levels of data granularity - a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED). LOD also allows computation at the data source as well as at the visualisation level. In this exercise, I am using FIXED LOD.
For anyone new to consumer behaviour tracking, let me briefly introduce you. Any dataset that captures the activities of customers over a period of time is a potential data source for this kind of analysis. Business analysts can study a wide variety of trends like buying patterns over time, customer acquisition and retention, buying frequency, trend of items bought, customer propensity to spend money etc. One such technique is to use the RFM analysis. RFM stands for (R)ecency, (F)requency and (M)onetary. It tells one how recently a customer has made a purchase, how frequently they purchase and what is the size or monetary value of their purchase to understand customer lifetime value. A business may also want to segment customers into different cohorts or groups displaying homogeneous attributes. This helps the business to identify trends and understand their behaviour better so they can know what worked and what didn’t. Accordingly, they can plan to supply or communicate with stakeholders accordingly.
The exercise
The Dataset
For this analysis, I have used a dataset that tracks retail purchase data in a UK-based gift store. There are 9 fields and 537966 rows covering 2 years 2010 and 2011. The link to the dataset is provided at the bottom of the blog in the references section for anyone who wants to practice.
Target Analysis and Visualisations
In this exercise, I am taking up these 3 different kinds of analysis and visualising my findings in 3 charts.
- Customer acquisition
- Customer retention
- Order frequency
Null values are excluded from the visualisations.
Process and Output
Customer Acquisition analysis
In simple terms, we are trying to find out the trend of the addition of new customers to the business, the period considered here is a quarter though any time period (month/ week) can be considered for the analysis.
WHAT TO CALCULATE? NUMBER OF NEW CUSTOMERS ADDED AT EACH SPECIFIED PERIOD
1) I started by playing around with the data, a simple table like purchase dates plotted against customer ID gives a fair amount of idea about the task at hand. We can see that for every customer ID, there is a list of invoice dates indicating purchase. If we can find the minimum invoice date for each customer, we will gain insights into when new customers are being acquired.
2) Next, we will compute the first purchase quarter per customer. To achieve this, I am using the fixed LOD { FIXED} expression to calculate the first invoice date for each customer ID. MIN is a function that returns the minimum value of a single expression or measure across all the records. Here I used it in conjunction with a handy date function in Tableau — DATETRUNC to return the date truncated as per the specified date_part, here my specified date part was ‘quarter’, however, you can go more granular to month or week level. Overall this calculated field goes into the invoice record against the customer to find the date of their purchase and returns the first quarter of purchase.
The other LOD expression computes the count of customers per CustomersFirstPurchase, here the CustomerFirstPurchase (calculated as above) is fixed while counting the number of distinct customer IDs. Overall, it returns the number of customers doing first purchase every quarter.
CustomerFirstPurchase
{FIXED [Customer ID] : MIN ( DATETRUNC ('quarter', [Invoice Date] ) ) }CustomersFirstPurchase per quarter
FIXED [CustomersFirstPurchase] : COUNTD ( [Customer ID] ) }3) Drag CustomersFirstPurchase to rows and change to quarter discrete setting, CustomerFirstPurchase per quarter to rows and invoice date to columns. Note that CustomerFirstPurchase per quarter is set to dimensions and CustomersFirstPurchase is a date output. Distinct count of customer ID is dragged to text.
How to interpret this table? 254 customers purchased first time in 2010 Q1, out of which 18 repeated in Q2, 47 in Q3 and so on. In 2010 Q2, there were 144 new customers and out of them again 18 purchased in the next quarter, 51 in the next quarter and so on.
4) We can make this visualisation more robust by dragging CNTD(Customer ID) to colours, I chose a divergent colour scheme to convert the visualisation to a heat map, you can choose to show or hide mark labels. A heat-map style visualisation is effective for visualisation where the audience can easily recognise trends and anomalies.
Retention Analysis
Retention analysis is closely related to the acquisition, here we calculate the percentage of customers making repeat purchases out of initially acquired customers.
WHAT TO CALCULATE - (NUMBER OF CUSTOMERS MAKING REPEAT PURCHASE/ NUMBER OF INITIAL CUSTOMERS ) %
1) Moving on to retention analysis, one more calculated field would be needed to calculate the retention rate
WHAT TO CALCULATE? NUMBER OF NEW CUSTOMERS ADDED AT EACH SPECIFIED PERIOD
Retention rate
COUNTD ([Customer ID])/ SUM([CustomersFirstPurchase per Quarter] )1) Dragging the retention rate to colours yields us a clearer picture of what is going on with customer retention.
I used a divergent colour scheme so that audience can readily interpret the visualisation. A lower percentage of customers made repeat purchases in the first few quarters, the numbers go up from there probably due to the nature of the store ( gift), the purchase is higher during holiday seasons.
Order frequency analysis
This analysis will provide answers to the question – “ how many customers purchased ‘n’ number of times ?”
1) A new calculated field is created using fixed LOD expression to get started. This function goes over to all invoices for every customer ID and counts distinct ( COUNTD) invoice numbers per customer ID.
InvoicePerCustomer
{ FIXED[Customer ID] : COUNTD([Invoice No] ) }
2) By dragging CNTD(Customer ID) to columns, InvoicePerCustomer to rows and CNTD(Customer ID) to colours, we can quickly achieve this visualisation. You can swap rows and columns or play around with sorting to achieve the desired visualisation.
Conclusion
We got several insights about the business from the visualisations. The retail business was able to acquire the highest number of new customers in 2011 Q1 followed by 2011 Q2 and Q3. Repeat purchase is high in the last quarter, probably driven by the holiday season.
As expected, the number of customers purchasing fewer times is higher with 1502 customers buying only once followed by 831 customers buying twice and 530 buying thrice.
This exercise gives an idea of the robustness of Fixed LOD expressions through their ability to switch to different independent levels.
How can a business make use of this data?
The seasonal trends in purchases can help businesses plan their inventory movements and accurately forecast demand trends and help keep inventory costs down and also prevent stockout and brand value erosion.
This can also help businesses adopt various pricing and marketing strategies to attract repeat buyers and also create loyalty incentives for repeat buyers thereby increasing retention.
Can you think of any other use cases that we can apply this to?
References
Online Retail Business Cleaned Dataset. (2021, May 10). Kaggle. https://www.kaggle.com/datasets/atanaskanev/online-retail-business-cleaned-dataset
Level of detail expressions. (n.d.). Tableau. https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod.htm
FIXED level of detail expressions. (n.d.). Tableau. https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_fixed.htm