I have a metric calculation question. I am trying to calculate churn. Churn is defined as ordering 1 month, and then not ordering the next month. On the next month's column (the month they stopped ordering) I want to count the location as "churned". The problem I am having is when there is no order amount for the next month, the value is "missing". The Metric doesn't return any values for missing data. I have the following example, The first row I would expect there to be a 1 in the Churn column for August (the month they stopped ordering), and for the second row I would expect there to be a 1 in the churn column for July. How do I build a calculation that will identify this account as "Lost" . Here is a screenshot of the report. And the calculations.

Hi Willie, looking at the metrics and thinking of what you’re trying to accomplish here (thank you for the pictures and explanation, by the way), I think we might need to have a closer look at the

Order Amount Total (inc. 0)

metric. Both your conditions on the final metric depend on this value, and I think that when either of them returns a null you get the result we’re seeing on the screenshots. My first suspicion is where the IFNULL statement is located right now. May I suggest trying something like this instead: 1. Start by creating a metric without the IFNULL, let’s call it `Order Amount Total`:

SELECT SUM(Total) WHERE Order Status NOT IN (canceled, declined)

2. Then, create an IFNULL metric that envelopes the entire metric, like so:

SELECT IFNULL (Order Amount Total, 0) *1

I ran some tests, and this ensures that the IFNULL calculations are done after the WHERE filters are applied. Based on my tests, I think that the metric was giving you nulls due to the WHERE not being “included” in the IFNULL (i.e., it would not check if the conditional would be a null, only the SUM). Kindly give that a shot and let me know if it works out better!

I changed the metric and it had no affect.

Thanks for trying it out. I ran some additional tests on my end, and the syntax of the metrics looks OK. So it may be related to how it’s built (in the context of the top-level metric). I would suggest checking to ensure that each part of your top-level metric works by itself before putting it together. First, ensuring the submetrics there are functional. Then, checking how they are used in the main metric. For example, check if you get any results when you only use one half of the WHERE conditional on the

Churn - At Risk Locations

metric; or if a statement such as

SELECT Order Amount Total (inc. 0) BY Location ID) = 0

actually gives out results. The goal is to identify where the failure is occurring. I would also make sure that all of these objects are properly connected in the LDM.

Hi Willie, this was a tough one, but I believe I might have a solution for you 🙂. I was working with my sample model and data where I have Customers instead of Locations and do not have the order status, but it should be easy to make the adjustments for your case. I believe the core of the issue is that you want your metric to be 1 in a month where there are no data (orders) for that particular location/customer. The engine works only with the data in your tables and can not generate additional rows there. But because you want it to be 1 the month immediately after month with the last order of that particular customer/location, we can use a trick with FOR PREVIOUS to shift the actual orders to the following month (so we get a (virtual) shifted record for each location/customer and month. And we get this also for one month after their last order). Then we can use these shifted virtual records to base our metric on 🙂. So the core of the metric is

COUNT(Customer,Order ID),0) FOR PREVIOUS (Month/Year (Order Date)

and then there are conditions to filter it only to months where there are no current orders and at the same time are some orders in previous month. I used

BY Customer, Month/Year ALL OTHER

within the inner metrics for conditions so it should work well even for insights that do not have customer/location and month in them. In my data I had orders for the same customer in different months and this metric also detects “repeated” churn risk. One issue might be that thanks to its date ever shifting nature it will warn about churn risk also for month which is not yet in the data, but that should be possible to be filtered out. Here is the full metric that worked for me. See the screenshots with my data if it is what you were trying to achieve:

SELECT 
  IFNULL( COUNT(Customer,Order ID),0) FOR PREVIOUS (Month/Year (Order Date),1 ) 

WHERE 
  (SELECT IFNULL(COUNT(Order ID),0) BY Customer, Month/Year (Order Date) ALL OTHER)=0
AND
  (SELECT IFNULL(COUNT(Order ID),0) BY Customer, Month/Year (Order Date) ALL OTHER FOR PREVIOUS (Month/Year (Order Date),1) )>0

BTW in one of your metrics you are using

Month

, I think you might want to use

Month/Year

instead Month is really only month without a year so it might give you some weird results if your data go across more than one year. BTW2: if you don’t care about showing 0 for those where churn risk is not detected, you can remove the very first IFNULL around the COUNT