George Coltart George Coltart - 3 months ago 7
Ruby Question

Calculate Active Customers 30 days ago in Ruby/Rails

I'd like to calculate user churn in my rails app.

eg. Take all the customers you lose in the last 30 days, and divide it by the total number of active customers you had 30 days ago. You do not include any new sales from that month.

I have a

:deleted_at
column on my user table which determines when someone cancels, so the number of customers lost in the last month is relatively easy.

The calculation I don't know where to start with is 'Active Customers 30 days ago'

Answer

Your 'Active Customers 30 days ago' should be something like that :

previous_day = Date.today - 30.days
User.where("created_at < :date AND (deleted_at > :date OR deleted_at = nil)", {date: previous_day})

Edit : You could see it like this

-Result : Take all the customers you lose in the last 30 days, and divide it by the total number of active customers you had 30 days ago. You do not include any new sales from that month.

( (User.active_now - User.active_and_created_in_the_last_x_days) - User.was_active_x_days_ago ) / User.was_active_x_days_ago

-Take all the customers you lose in the last 30 days:

(User.active_now - User.active_and_created_in_the_last_x_days) - User.was_active_x_days_ago )

-Total number of active customers you had 30 days ago:

User.was_active_x_days_ago

-You do not include any new sales from that month.

User.active_and_created_in_the_last_x_days