I want to create a cohort analysis with 2 dates- sale date and service date for vehicles purchased. So a vehicle will have only one sale date but multiple service dates. I want to calculate the cars sold in a particular month and how many came back for service(not all sold cars come back) and in how many months did they come back. Also, the vehicle can come back for service multiple times, so a vehicle will have only 1 sale date but multiple service dates. How do I do this with 2 different dates?
Hi @Prajakta - Can you share the sample data for this analysis? Give input and require output, this will help to explore the solution approach.
Regards - Sanjeeb
1 Like
This is the sample data. All vehicles have different Ids. There can be only one sale date for a vehicle. This is one dataset.
The next one just contains service data.
A vehicle can come for service more than once at the same store, so we will have multiple service dates for a vehicle ID. All vehicles may not come for service so the retention rate will never be 100% for the same store.
I will have to join (left join because I have all the sales records even if they have no service) those 2 datasets to get the sale and service dates of those vehicles by vehicle ID.
I want to perform a cohort analysis- something similar to this sample,
On the y axis (column), I would have the Month of sale date for vehicles (eg. 100 vehicles were sold in Jan 2021, 600 sold in feb 2021 etc) and one the x axis (rows) I would have the Number of days to come back for service based on the sale date.
For eg. 2% of vehicles came back for service within 30 days of Sale date. So if a vehicle was sold on Jan 13 2021, it comes back for service before Feb 13 2021. So in this particular example, only 2%(or 2 vehicles) came back before 30 days.
How will I be able to perform these calculations and set up this analysis? I have tried using a pivot table in quicksight but it did not help me much. I would like to know if this analysis is even possible in quicksight.
Hi @Prajakta,
You should be able to do this in QuickSight. You can use the dateDiff function to calculate the difference between the Sale Date and the Service Date. Then create a pivot date with the number of days as column and distinct count of Vehicle ID as your value. If you want to show it as a percentage, divide the distinct count by the number of vehicles sold.
2 Likes