On my dashboard i want a card to represent this over time, i.e. sending in {{working_month}} for every month of the last rolling 12 months. Is this possible?
Hi @Ascension
I’m not quite sure I understand what you’re trying to do.
If you want to display the past 12 months, then you need to get the data from the entire period and then group by month.
Let me explain my use case, I have a table representing purchases and what I’m trying to visualize is the number of customers that are “engaged” per month in a twelve month rolling period. An “engaged” customer in month 3 is defined as someone who has made a purchase in month 2 and 1.
So for example given that it is October and Customer X has made a purchase in both August and September, i will count X as an “engaged” customer in October.
This is what my query is doing, finding the “engaged” customers in a given month. I need this then to be run over the rolling twelve month period based on the input date, so I can visualize this statistic over the last year.
Okay, it sounds like you’re trying to do Customer Retention Analysis or Cohort Retention Analysis.
You would make a where-clause that was a sub-query, checking if the customer has been active the last 12 months from whatever month you’re querying in the current lower sub-query.
You might need to redo some of your initial query, since it already contains several levels of queries, but it depends on your data size and indexing, so if everything is still fast, then it’s not that important.
Yeah, I ended up starting from scratch and getting all the purchases made in the year (grouped by month) and then checking if there existed a purchase in the previous month for that customer.