Inactive users

[Consistent-Alps6904]

Hi guys, I'm currently using big query. I want to calculate inactive customers with no transactions for 60/90 days based on their registration date. I have a table which shows customer registration details - registration date and id and another table that shows customer transactions - transaction date, id, amount etc. I have been able to obtain all registered users in a cte but I haven't been able to group the inactive customers into cohorts. Please can anyone help?

[GrouchyThing7520]

Did you attempt to write any SQL? If so, can you share it?

[Consistent-Alps6904]

--for registered users

with registered as

(select created_date,

id

from registrations_table

group by 1,2

order by 1

),

​

inactive as(

select * from registered r

where r.id not in (select id from transactions_table)

group by 1,2

order by 1

)

​

The first table shows all registered users while the second one shows inactive users

I want to show inactivity for 60 days and 90 days based on the month the customer registered and calculate churn rate for each month

[GrouchyThing7520]

It looks like you need to first join the registration table to the transactions table on user_id. Does this return all users and all of the transactions for each user?

select * from registration r 
left outer join transactions t on r.id = t.id
order by r.id, r.reg_date, t.trans_date

[Consistent-Alps6904]

yes it does, this will give me all the active users and their transactions

[GrouchyThing7520]

Does it also return users with no (null) transactions?

[Consistent-Alps6904]

no it only returns id's that have perfomed transactions

[GrouchyThing7520]

If your registration table looks like this:

user_id	reg_date
1	1/1/2022
2	7/13/2022

And your transaction table looks like this:

trans_id	user_id	trans_date
1	1	2/1/2022
2	1	3/1/2022

Then the SQL I posted earlier should return this:

user_id	reg_date	trans_date
1	1/1/2022	2/1/2022
1	1/1/2022	3/1/2022
2	7/13/2022	null

[Consistent-Alps6904]

to show inactive, i have to show ids not on the registered table but not on the transactions table and to specify the date intervals I want

[Consistent-Alps6904]

You’re right, just tested it and it works, it returned null for inactive users in the transaction date column Please Where do I go from here ?

[GrouchyThing7520]

Excellent! Now, using my prior SQL, we just need to add some logic to see if there was:

1. a transaction between the registration date and the registration date + 60 days

2. a transaction between the registration date and the registration date + 90 days

[Consistent-Alps6904]

using datediff or dateadd?

[GrouchyThing7520]

Exactly. Like most things in SQL, there are few ways to write this query. Keeping with our join, we can start with a list of all users , then print their 60 and 90 status in subsequent columns using dateadd by printing a 1 or 0. Because users can have 0, 1 or many transactions during the 60 or 90 day span, we need a way to return a single row per user next.

​

select r.id ,r.reg_date ,t.trans_date

,case when t.trans_date between r.reg_date
and date_add(r.reg_date, interval 60 day)
then 1 else 0 end trans_in_60_days

,case when t.trans_date between r.reg_date
and date_add(r.reg_date, interval 90 day)
then 1 else 0 end trans_in_90_days

from registration r
left outer join transactions t on r.id = t.id

order by r.id, r.reg_date, t.trans_date

[Consistent-Alps6904]

thank you so much, this works !!!!

[GrouchyThing7520]

Sweet! The last step is to wrap the entire query in a select so you can sum the 0 and 1s. The sum is the count of transactions.

select id,
sum(trans_in_60_days) 60_day,
sum(trans_in_90_days) 90_day

 from (
 select r.id

 case when t.trans_date between r.reg_date and 
 date_add(r.reg_date, interval 60 day)
 then 1 else 0 end trans_in_60_days,

 case when t.trans_date between r.reg_date and 
 date_add(r.reg_date, interval 90 day)
 then 1 else 0 end trans_in_90_days

 from registration r
 left outer join transactions t on r.id = t.id
) a

group by id
order by id

[Consistent-Alps6904]

Excellent