I’m developing a report to show the % of customers who are giving recurring sales who cancel each month by the following sales groups:
I’ve managed the first part of the solution. That is (ignoring the sales) I can calculate the number of active customers at the start of the month, then how many of those active customers cancel during the month and therefore the percentage that cancelled.
For example, there are 1,000 customers at the start of the month, 100 of those cancel their recurring sales during the month so there is a 10% cancellation rate.
All this could be calculated using the Pledges table.
However, the second part of the solution is proving to be more problematic. That is, how many of each sales group cancel during the month and the resulting % cancellation.
For example, there are 100 customers who have made two sales at the beginning of the month and 10 of those customers cancel during the month so there is a 10% cancellation rate.
All the sales are in the Sales table.
I’m struggling to filter these sales so that the slicer will work correctly. I created calculated columns for a customer’s sales count and then a sales group but this only shows the current picture. A customer who has made their fifth sale this month would have only made one sale by the start of June.
Thanks very much for you reply and solution. Unfortunately it’s not showing the desired results.
I’m beginning to think that what I’ve been asked to deliver may not be possible. It’s certainly above my skill level.
Say that the chart above shows the percentage who cancelled each month out of those customers who at the beginning of the month had made one sale and were active (that is they hadn’t cancelled their recurring transactions).
The Jan 2021 data point is calculated by counting all active customers as at 1 Jan who had made one transaction. This is then divided by the customers from this group who cancelled during Jan to give the % customers cancelled during Jan who had made one transaction at and were active at Jan 1st.
The Feb 2021 data point starts from scratch. It is calculated by counting all active customers as at 1 Feb who had made one transaction. This is then divided by the customers from this group who cancelled during Feb to give the % customers cancelled during Feb who had made one transaction at and were active at Feb 1st. etc.
Therefore the chart will be showing over time whether the cancellation rate was increasing or decreasing for customers who at the beginning of that month had made one transaction and were still active.
(It might be easier instead of grouping the transactions to just have a slicer with increments - 0, 1, 2, 3 etc.)
I used weekly but it is easier to look at monthly.
So for the chart with Donation Group = 1, the chart is meant to show the % cancelled each month where each data point is calculated as explained above. (The Jan 2021 data point is calculated by counting all active customers as at 1 Jan who had made one transaction. This is then divided by the customers from this group who cancelled during Jan to give the % customers cancelled during Jan who had made one transaction at and were active at Jan 1st.)
I have several remarks on the results.
You placed a filter on years 2019, 2020 and 2021, so you will never have results for the groups above 5 number of sales because the maximum number of sales for a customer in a month is 5.
I’ve verified certain customers (high number of transaction) and I saw they were still active, so it’s logic I don’t see % of cancellation.
Your problem is quite complicated…
My solution seems to work, but again I can’t be sure because your problem it’s really complex.
My advice is to try to verify my solution.
If it’s not correct, I’m sorry I can’t help you… I have no ideas left
