Counting active customers

Q&A
, ,
1

I have a dataset of transactions with a customer id and timestamp for each. For a client to be classified as “active” they need to have done at least 10 transactions in the last 30 days.

I want to show:

  1. the current COUNT of active customers
  2. how the count of active customers has varied over time

I seem to be going round in circles with windowSum as I don’t want to show all the customer ids on the visualisation I just want the count of them. I must be missing something, as an analysis as simple as this shouldn’t be that hard, surely?

Any guidance would be most welcome, thanks!

2

Hi @freshaspect -

You can use LAC-W and dateDiff functions for this.

Step 1 - Create a calculated field that counts the number of transactions within 30 days
c_transactions_last_30_cnt

// use LAC-A to retrive rows (transactions) in last 30 days, grouped by user

countOver(ifelse(dateDiff(dt,now()) >= 30,1,0),[{user_id}], PRE_AGG)

Step 2 - Create a calculated field that only shows user_id if c_transactions_last_30_cnt is over 10.
c_users_with_2+_transactions_in_30d

distinct_count(ifelse({c_transactions_last_30_cnt}>=2,{user_id},null))

Result:

image
image973×704 33.5 KB

3

Many thanks! I’ll give this a go and report back - really appreciate the detailed response.

4

Don’t understand how part 2 of this can work if the count is including now(). For example, if looking at the active number user account for 25 June 2022, it should be looking at who did at least 10 transactions between 24th May 2022 and 25th June 2022.

Maybe I am missing something but doesn’t this need to be using a moving time window?

5

In the end I had to move all the logic down to the SQL layer to get this to work which was pretty frustrating. Maybe there’s a way to get this to work - if I get a chance, I will revisit. Thanks again for your suggestion though.

6

@freshaspect -

Sorry about the delay. I took your definition of active customers as 30 days from current date. Where you need it as a rolling/window from the date of the transaction.

I wasn’t able to find another workaround and was going to suggest moving to the ETL layer, which I see you did.

I’ll flag this as a feature request and continue to explore if an acceptable workaround exists.

1 Like
7

Hey @freshaspect!

I have solved it. What I did is on the creation of the dataset I created a custom query were I did UNION ALL of the same table to get a new column date where creation and deletion dates were stored.

Then you create a calculated field for the users created with ifelse where date = creation date. Same to deletion. And then perform a runningSum subtracting this calculated fields.

Hope this works.