When did my users deposit for the first time?
We usually recommend sending a boolean parameter for first time depositors as best practice. That way you can study the first time a user actually creates his first deposit, what lead him to do so, how long it took him and so on. (is_ftd =1 or 0 on purchase events)
In cases where such a parameter is not available, this can be calculated by creating a user purchase aggregation and adding a serial number to each user purchase (by timestamp).
First time deposits are all the purchases in which purchase_serial_num =1.
Select customer_user_id, user_id, event_time_ts as purchase_timestamp, purchase_amount as purchase_amount, session_id, is_new, sys_user_create_date as user_first_seen,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time_ts ASC) as purchase_serial_num
From cooladata
Where date_range (all) and event_name = ‘purchase’ and purchase_status = ‘success’
