Beast mode for rolling count of purchasing customers within a specified period month over month

I'm trying to develop a beast mode formula to help me derive count of customers who have purchased within the last 30, 60, and 90 days, which rolls month over month. For example, the count of patients who have purchased within 90 days prior to September, within 90 days prior to October, and within 90 days prior to November. Whether in a chart or table, these dynamic values would be reported for the respective month. Based on what I've found so far, it seems this might be achieved in beast mode with a formula like this, which would be used as a filter (set to 'Yes"), with the 'Order Completed Date' on the x axis (summarized by "month') and the customer count (count(distinct 'Customer ID') on the Y-axis:

case when date(`Order Completed Date`)<date('DateCol') AND date(`Order Completed Date`)>date('DateCol')-30 then 'Yes' else 'No' end

The formula validates, but when I add to the chart filter, it is blank with no options (no 'yes' or 'no' values to select). I also tried using 'DateColumn' instead of 'DateCol', which appears in some of the formulas in the beastmode interface (e.g. Date_Sub('DateCol', interval 30 day)), but it does not seem to make a difference.vIf I could get it to work, I'd simply replicate this for 30 and 60 days, but I can't seem to get a solution that works with 'DateCol'.

I welcome any feedback or suggestions.

FURTHER DETAIL, IF HELPFUL

"90 days (rolling 90 day look), it’s how many people are with us (defined by Purchased within the last 90 days) that started their relationship with us >90 days ago. Said another way, how many customers persist with us on a rolling 90 day retention look. Then the 30 day window will be a tighter view of that, then on a rolling 60 day look. I’m making the assumption that the customers who ordered >90 days ago and haven’t shopped again are lapsed or lost customers. Customers outside 60 and have not purchased w/in the last 60 days, but purchase between 60-90 days are at risk of lapsing"

Find more posts tagged with

Comments

jaeW_at_Onyx

High level there are two ways of approaching this.

1) for each period on your X axis (month), you want to force your dataset to include ALL THE DATA of interest (i.e. the last 90 days) for that period. (so Rpt_Month Oct, would include Activity from Oct, Sept, Aug)

that way your beast mode is nice and simple

CASE WHEN date_diff(Rpt_Date, Activity_Date ) <= 90 then .... END

To do this, build a date dimension with one row per Rpt_month for all the Activity_Days in that Rpt_Month (each month would have 90 days.)

Alternatively, keep your data at the current granularity, and use the LAG() window function (submit a feature request to your CSM to have window functions enabled). You'd do LAG(sum(Amount), 1) over (order by Rpt_Month desc) as demonstrated in this video.

https://www.youtube.com/watch?v=cnc6gMKZ9R8&t=25s

if you need COUNT DISTINCT, the LAG() method won't work b/c you can't get a collective distinct count across months, you'd get SUM(54 unique customers in Aug, 46 in Sept 47 in Oct) and it's likey you have overlapping customers between months.

user095459

Thank you for the quick reply! So for the date dimension, is that based on the beast mode case statement you recommended? Also, it would be ideal if this could be dynamic with variables, so you don't have to hard code months. You mention "Rpt_Date", which I presume is placeholders for hard-coded value for the month in question, e.g. 10/31/2020 for October, which you would adjust for each month you want to analyze across multiple case statements, correct? Ideally there would be a way to use a variable in this statement that represents the date for the granularity of the reporting, whether monthly, weekly, or otherwise chosen with the "graph by" field in the chart. Maybe that's not possible, but that's one of the things I was wondering with the 'DateCol' reference I saw in e.g. the DateSub function:

DATE_SUB
Subtracts date or datetime values (as intervals) to date values in a date column.
DATE_SUB('DateCol', interval 12 day)

For the "Activity_date" reference in your beast mode example, I presume that is where I'd put 'Order Completed Date'.

Also, the area following "then..." is where I'd add the date dimension, and this case statement in beast mode would result in the date dimension I'd use for rows in the report, in which I could summarize the distinct count of customers in a separate column.

I am somewhat new to more advanced beast mode functions, so I sincerely appreciate the advice and welcome any input on the above follow-up questions!

jaeW_at_Onyx

The two different methods are

use LAG() in windowed functions (previously linked tutorial) OR this one https://www.youtube.com/watch?v=CDKNOmKClms

instead of current year / previous year, you'd build offsets for 90 day buckets.

-- Also, it would be ideal if this could be dynamic with variables.

if you watch the videos, neither option hardcodes dates. so not a problem!

-- You mention "Rpt_Date", which I presume is placeholders for hard-coded value for the month in question.

No. In Solution2, Rpt_Date would be the date column that you put on the axis. As opposed to the activity_date which is the date the activity actually occured. In your example " have purchased within 90 days prior to September, within 90 days prior to October, and within 90 days prior to November." so the report_date (I also referred to it as periods)

-- you still have access to the GRAPH_BY settings in analyzer. But if you want to change the granularity of the X-Axis, you'll probably find most success using LAG() functions (video 1) instead of the calendar_dimension (video 2)

DATE_SUB
Subtracts date or datetime values (as intervals) to date values in a date column.
DATE_SUB('DateCol', interval 12 day)

... this function won't work with your data in it's current state because if you put months on the axis, then your beast mode can only see data IN THAT MONTH. (i.e. at most it can see the last 30 days of data, and never 90, because there are only 90 days in a month).

user095459

I am checking into the possibility of enabling the windows function, but the beast mode seems better, since we want the distinct count of customer ID's over these different time periods. From your video, I see you build a table that you join. Our Domo instance unfortunately is only a front-end with federated external data, so we don't have the ability to import or join tables, so any solution would require everything done through beast mode. It seems that really complicates things, since were are limited to variables in the dataset and, beast mode functions. If perhaps this is possible and I'm missing something, I definitely welcome any suggestions. Thank you for these tutorials -- they are very well presented and quite insightful! I am hopeful there is a way ?

jaeW_at_Onyx

Lol .... federated makes everything harder.

but that's fine. build your date_dimension dataset in your database, and federate your JOIN'ed date + transaction table. then you can do it all in simple beast modes.

you can't count distinct with the LAG() function method. I explained why in my first response.

"if you need COUNT DISTINCT, the LAG() method won't work b/c you can't get a collective distinct count across months, you'd get SUM(54 unique customers in Aug, 46 in Sept 47 in Oct) and it's likey you have overlapping customers between months."