Viewing a question over time on a single card

Get Help
1

Hi,

I have a question written in native sql:

image
image941Ă—215 17.9 KB

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?

DB: mysql 5.7.14
OS: Windows 10
Metabase: v0.33.4

Let me know if more information is required.

Thanks

2

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.

3

Hi @flamber,

Thanks for your response.

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.

I hope that makes sense.

Thanks

4

@Ascension

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.

Also check this thread: Customer cohort and retention analysis

Otherwise try to do a bit of searching for either of the terms - there are many examples on stackoverflow and similar sites.

1 Like
5

@flamber

Thanks for the insight.

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.

Thanks again

1 Like