Hi
I am trying to work out if a subscription has recovered during a period.
I have three tables, date, subscription and customers.
If a subscription churns (does not renew) at the end of the subscription period however within a 12 month period returns (from the end date of the subscription), to have a measure that is able to look back over the last 12 months to see if the customer had a subscription. If this is the case, then to be able to calculate what the difference between the old subscription to the new subscription.
For example, have a customer who had a subscription for the period 1 July 2016 to 30 June 2017 for $1,000. At 30 June 2017 the subscription does not renew and therefore churned. However, on the 15 December 2017 that customer returns with a new subscription value of $2,000.
So, need a measure to be able to do the calculation to say for the month of June 2017 you have churn of -$1,000 and December 2017 churn of $1,000 and upsell of $1,000. Therefore, the half year (Jul-Dec) churn $0 and upsell $1,000.
Or if the new subscription value is $500 to be downgrade of $500 but the churn numbers to be the same from above.
Or if the new subscription value is $1000 to only show the churn number from above.
Thanks
Jon
Hi Jon,
The formula will depend on how your data is structured - what fields are in what tables and what are the relationships. Please provide a mock up Excel file containing the format of your data so we can help you further.
Thanks,
Mynda
Hi Mynda
I have attached a mock up file.
The data is very basic just has the customer name and the subscription amount per month for each customer. I also have a date table and a customer table.
Also in the file I have shown how I have done this using excel formulas, however I think there would be an easier way to do this.
As also, thanks for your help Mynda.
Kind regards
Jon
Hi Jon,
I is possible, but you have to use a different solution, thinking in PQ is different than regular excel.
I created a function to calculate the Variance, added a column to find the Type of customer, then pivoted he Type using the Varinace values.
Here is the function:
let
//CustomerName="Customer 7",
Source = SourceData,
LastValue=Table.SelectRows(Source, each (Number.From([Date]) =List.Max(Source[Date])) and ([Customer] = CustomerName)){0}[Amount],
IsConstant=List.IsEmpty(Table.SelectRows(Source, each
([Amount] <> LastValue) and ([Customer] = CustomerName))[Amount]),
SelectRows = Table.SelectRows(Source, each
([Amount] <> LastValue) and ([Amount] <> 0) and ([Customer] = CustomerName)),
NewValue=if IsConstant then LastValue else (if List.IsEmpty(SelectRows[Amount]) then 0 else SelectRows{0}[Amount]),
Result=if NewValue=LastValue then 0 else LastValue-NewValue
in
Result
To see what the function does, you can convert it to a query, by adding 2 forward slashes to the first row:
//(CustomerName)=>
then remove the forward slashes from //CustomerName="Customer 7",
You will have to manually type here the customer number, to see the results for that customer only.
I attached the test file too, hope it helps.
Note that for Customer 8, in month 5, you have 60000, but all other months has 40000. Your calculation says that this customer has no change, but my results are showing -20000, a downgrade. Was that an error, or your calculations are correct? That value seems to be unusual.
Hi Catalin
Thank you for your reply.
Correct, it was my error in relation to Customer 8.
Customer 11 and 12 is a combination of churn and upsell/downgrade. So for customer 11 it should be churn of 18,216 and upsell of 6,784. Customer 12 churn of 5,316 and downgrade of -3,751. Is this possible?
Thanks
Jon
Hi Jon,
Only one category can be assigned to a customer, unfortunately. Its still possible I think, but requires a different approach, your sample data has only one category in the result column. How many combinations of categories can be: churn-upsell, churn-downgrade, are there other combinations? How do you calculate based on existing data when a customer falls into one of these combinations of categories? We have to rebuild that conditional column formulas to return all possible combinations in the same column.
In Data query, this is the step you have to edit:
if [#"31/12/2017"] = [#"30/06/2017"] then "No Change"
else if ([#"30/06/2017"]=0 and [#"30/06/2017"] < [#"31/12/2017"]) then "New Business Or Recovered"
else if ([#"30/06/2017"]>0 and [#"31/12/2017"]=0) then "Churn"
else if [#"30/06/2017"] < [#"31/12/2017"] then "Upsell"
else if [#"30/06/2017"] > [#"31/12/2017"] then "Downgrade"
else if (condition1 and condition 2 and any other condition) then "Churn | Upsell"
else if (condition1 and condition 2 and any other condition) then "Churn | Downgrade"
else null),
The next step, Pivoted Column, is using Sum as the aggregate function, it should not use any aggregation.
Lastly, the function that calculates the values should be modified to return 2 values in case of multiple cases : 5316 | -3781, this is tricky.
In case you want to go for such a way to display the results, you have to prepare the conditions for any other category combinations and explain the formulas to calculate for each new category/combination.
Hi Catalin
That is where I have been struggling in working out how I can split between churn-upsell and churn-downgrade in separate headings.
For example using Customer 11
The subscription churned in Jun 17 for -18,216 then the subscription comes back in Aug 17 for 25,000. As this is not a new subscription (has to come back within 12 months) then its not churn so at Dec 17 I need churn of 18,216 and upsell of 6,784. So when I look at it at the end of the year all I show is upsell of 6,784.
In the mock file I attached I have a tab called Excel formula which I have stepped it out using Excel formulas but was hoping to do this in POwer Query or Power Pivot.
Thanks
Jon
Hi Jon,
I did replicated the Excel Formula calculations in PQ, but there are no calculations there for how to detect the combined categories, that's what I need to help you.
Hi Catalin
The way I worked out the combined categories was to use the IF formula to work out if it is a New Business or Recovered. If it returned new business or recovered then look back over the last 12 months to see when the first zero month was. Then take the before month number and add or subtract it to the Dec number. If it is positive then it is an upsell, if a negative a downgrade and the amount from the before month is the positive churn number. I could only work this out by doing multiple steps. Was hoping there was one formula to do it all.
Thanks
Jon
Hi Jon,
Hope you can use the attached layout.
Or this one.
Cheers,
Catalin
Hi Catalin
It works!
Thanks so much for helping me out on this.
Kind regards
Jon
Which version?
v3
