Today is another normal day for me, but for the people who work on front lines, it's not. I wrote each word in this blog with my gratitude to people who are fighting for COVID-19 out there. I believe everything is going to be fine.
WFH is not an excuse of doing nothing, but a good time to calm down and keep learning and growing. Let's continue our cohort analysis!
Sometimes marketers want to track if a new campaign launched on a day would work after several weeks of retaining existing customers. For example, a store manager wants to know if the customers came back to make more purchases after the special offer launched on 2/29/2020. As an analyst, I would provide a visualization to show the portion of customers who purchased again in 1,2,3…weeks after 2/29/2020.
I often picture the Viz before creating one – the base would be like this:
For example, in the view, I want to show the store manager that there are 7 customers made purchases again in week 1 among the 62 customers who purchased in week 0 (a certain date). You may also create a dynamic chart that allow users to adjust the date of 'week 0'.
Let’s do it together in Tableau.
🤪Yuchen's Tip: Before we get started, please refer my previous blog to familiarize yourself with cohort analysis and LOD expression in Tableau.
Step 1: Create ‘Cohort Week Parameter’
We expect to change the start week to a certain date, so a dynamic parameter is needed.
Creating a calculated field ‘Week of Order’:
DATETRUNC('week',[Order Date]) ‘DATETRUNC’ function can truncate the specific date to the accuracy specified by the date_part and returns the new date.
Right click ‘Week of Order’ to create a parameter name it as ‘Cohort Week Parameter’ which controls the date of ‘0-week customers’.
Step 2: Create Week Bin for the X-Axis
Create a calculated field ‘Normalized week of order’ between the date selected in the parameter and the ‘Order Date’ using DATEDIFF function.
DATEDIFF('week',[Cohort Week Parameter],[Order Date])
Right click ‘Normalized week of order’ to create bins and set the size to 1
Step 3: Create 12-Week Filter
We use '12-week window filter' to limit the view from ‘week 0’ to ‘week 12’. ‘DATEADD’ function is used to add an increment to the date.
[Week of Order]>=[ Cohort Week Parameter]
AND
[Week of Order]<=DATEADD('week',12,[ Cohort Week Parameter])
Step 4: Bring Customers into Cohort Weeks to Create ‘Retained Customer’ Filter
This step is used to identify ‘0-week’ customers from cohort week 1-12
{FIXED [Customer Name]: MAX([Week of Order]=[Cohort Week Parameter])}
Now you may create a sample table in the tableau and download the result to see if all the calculations and filters give you the correct result.
Step 5: Finalize the view
Drag ‘Normalized week of order’ to ‘Columns’, COUNTD([Customer Name]) to ‘Rows ‘
Drag ‘12-week window filters’ and ‘Retained Customers’ to ‘Filters’ and set both default value to ‘True’
Right click ‘Cohort Week Parameter’ and select ‘Show Parameter Control’ and change type to 'Slider'
Right click COUNTD([Customer Name]) in the ‘Rows’ and add select ‘Create Table Calculation’ to compute the percentage of customers retained in each cohort week.
Now, you’ll have a base view like this:
Step 6: Add Tooltips to Show the Insights
Create ‘Customers in Week 0’:
WINDOW_MAX(
IF ATTR(ZN([Normalized week of order]))=0
THEN
ZN(COUNTD([Customer Name]))
END
)Drag COUNTD([Customer Name]) together with ‘Customers in Week 0’ calculation into ‘Detail’ and click ‘Tooltip’ and enter the descriptions and values.
Now, you’ll see the details if you hover over each circle point.
Tell Your Story
Let's take 9/9/2018 as the initial week of purchase.
7.55% of customers who placed orders in the week beginning 9/9/2018 made purchased in one week, and 11.32% of them made purchases in 12 weeks later. We can see the result is good because roughly 1 out of 10 people are willing to purchase again in 3 months since their initial purchase. We can also see the in weeks 9 to 12, the percentage of customers is in increasing trend compared to the previous weeks, which reflects the customer needs for a certain product increased in 3 months. Therefore, we may explore more of the product features to deliver further insights.
🤪Yuchen's tip: Tableau has tons of dynamic features, you may add more in the tooltips and dashboards to enable end users to better interact with your visualization!
Happy analyzing!
Comments