Forum Discussion
Sales Contribution by Customers Cohort Analysis
- Anonymous7 years ago
To your table name "Orders", add the following calculated column
CustomerFirstPurchaseYear = YEAR( MINX( FILTER(ALL(Orders), Orders[Customer ID]=EARLIER(Orders[Customer ID]) ) ,Orders[Order Date] ) )You don't have a customer master, so you have to use this formula instead of the one I suggested first.
If you use this column on your Legend and OrderDate's year on your axis with "Sales" on the values, you will get the results on your stacked column chart.
- Anonymous7 years ago
I have never created a Cohort Analysis and I don't actually understand what those percentages are or what are Q0 through Q15.
But out of curiosity, and based on your post, I assume you want to find the following
1) How many new customer IDs were created each quarter?
2) How many of them purchased in the subsequent quarters?
3) Show the ratio counts as a percentage.For the sake of better understanding, I will illustrate the workings step by step so that you will get started on this and you will be able to modify the codes on your own to suit your requirements.
Add the following Columns to your "Orders" table.Step1: Add a calculated column for Customer's First Purchase Date
CustomerFirstPurchaseDate = MINX ( FILTER ( ALL ( Orders ), Orders[Customer ID] = EARLIER ( Orders[Customer ID] ) ), Orders[Order Date] )Step2: Add a calculated column for Order's Quarter No
OrderQtrNo = VAR BaseYear = YEAR ( MINX ( ALL ( Orders ), Orders[Order Date] ) ) VAR OrderYear = YEAR ( Orders[Order Date] ) VAR MF = ( OrderYear - BaseYear ) * 4 RETURN Orders[Order Date].[QuarterNo] + MFStep3: Add a calculated column for Customer's Q00
CustomerQ0 = VAR BaseYear = YEAR ( MINX ( ALL ( Orders ), Orders[Order Date] ) ) VAR CFPYear = YEAR ( Orders[CustomerFirstPurchaseDate] ) VAR MF = ( CFPYear - BaseYear ) * 4 RETURN Orders[CustomerFirstPurchaseDate].[QuarterNo] + MFStep4: Add a calculated column for Customer's 1st Quarter as text ( like 2014-Q3 etc...)
FirstQuarterOfCustomer = Orders[CustomerFirstPurchaseDate].[Year] & "-Q" & Orders[CustomerFirstPurchaseDate].[QuarterNo]
Step 5: Add a calculated column for Customer's Returning Quarters
ReturningQuarterNo = "Q" & FORMAT ( Orders[OrderQtrNo] - Orders[CustomerQ0], "00" )Step 6: Add a measure for finding the count of active customers.
ActiveCustomerCount = DISTINCTCOUNT(Orders[Customer ID])
Step 7: Add a measure to find the total number of customers activated each quarter.
TotalCustomers = CALCULATE ( DISTINCTCOUNT ( Orders[Customer ID] ), Orders[ReturningQuarterNo] = "Q00" )Step 8: Add a measure to find the percentages
CA = DIVIDE([ActiveCustomerCount],[TotalCustomers],0)
Here is the result...
Dear,
Thank you reply and helping.
https://community.tableau.com/docs/DOC-10198
This is the dataset that I am using.
Please take time to download and help me to solve the issue!!
Many thanks and appreciation!!
To your table name "Orders", add the following calculated column
CustomerFirstPurchaseYear = YEAR(
MINX(
FILTER(ALL(Orders),
Orders[Customer ID]=EARLIER(Orders[Customer ID])
)
,Orders[Order Date]
)
)You don't have a customer master, so you have to use this formula instead of the one I suggested first.
If you use this column on your Legend and OrderDate's year on your axis with "Sales" on the values, you will get the results on your stacked column chart.
- Anonymous7 years agoNot applicable
Dear,
it works perfectly.
Thanks so much.
I also posted how to build the Cohort Analysis in this forum to know the return of customer through months.. If you have time, please help me also!
here the picture I attached the final result.
Thanks for spending time to help me!!!
- Anonymous7 years agoNot applicable
I have never created a Cohort Analysis and I don't actually understand what those percentages are or what are Q0 through Q15.
But out of curiosity, and based on your post, I assume you want to find the following
1) How many new customer IDs were created each quarter?
2) How many of them purchased in the subsequent quarters?
3) Show the ratio counts as a percentage.For the sake of better understanding, I will illustrate the workings step by step so that you will get started on this and you will be able to modify the codes on your own to suit your requirements.
Add the following Columns to your "Orders" table.Step1: Add a calculated column for Customer's First Purchase Date
CustomerFirstPurchaseDate = MINX ( FILTER ( ALL ( Orders ), Orders[Customer ID] = EARLIER ( Orders[Customer ID] ) ), Orders[Order Date] )Step2: Add a calculated column for Order's Quarter No
OrderQtrNo = VAR BaseYear = YEAR ( MINX ( ALL ( Orders ), Orders[Order Date] ) ) VAR OrderYear = YEAR ( Orders[Order Date] ) VAR MF = ( OrderYear - BaseYear ) * 4 RETURN Orders[Order Date].[QuarterNo] + MFStep3: Add a calculated column for Customer's Q00
CustomerQ0 = VAR BaseYear = YEAR ( MINX ( ALL ( Orders ), Orders[Order Date] ) ) VAR CFPYear = YEAR ( Orders[CustomerFirstPurchaseDate] ) VAR MF = ( CFPYear - BaseYear ) * 4 RETURN Orders[CustomerFirstPurchaseDate].[QuarterNo] + MFStep4: Add a calculated column for Customer's 1st Quarter as text ( like 2014-Q3 etc...)
FirstQuarterOfCustomer = Orders[CustomerFirstPurchaseDate].[Year] & "-Q" & Orders[CustomerFirstPurchaseDate].[QuarterNo]
Step 5: Add a calculated column for Customer's Returning Quarters
ReturningQuarterNo = "Q" & FORMAT ( Orders[OrderQtrNo] - Orders[CustomerQ0], "00" )Step 6: Add a measure for finding the count of active customers.
ActiveCustomerCount = DISTINCTCOUNT(Orders[Customer ID])
Step 7: Add a measure to find the total number of customers activated each quarter.
TotalCustomers = CALCULATE ( DISTINCTCOUNT ( Orders[Customer ID] ), Orders[ReturningQuarterNo] = "Q00" )Step 8: Add a measure to find the percentages
CA = DIVIDE([ActiveCustomerCount],[TotalCustomers],0)
Here is the result...