Solved

What does the unsubscribe_detected_at field in the S3 exports represent?

  • 21 July 2021
  • 4 replies
  • 258 views

Badge +1

Can you please explain how the unsubscribe_detected_at field is populated?

I have some users who have opted out of auto renewal, but their unsubscribe_detected_at field is null. And some users who don’t have it as null.

The only difference I see is that users in the latter group re-subscribed after opting out.

icon

Best answer by sharif 21 July 2021, 23:23

View original

4 replies

Userlevel 5
Badge +9

The unsubscribe_detected_at field is set when RevenueCat detects a user unsubscribed (in other words, opted out of renewal) and is unset (set to null) when a user resubscribes (in other words, opted in to automatic renewals.) RevenueCat will detect unsubscribes much quicker if you have platform server notifications enabled. Without platform server notifications, RevenueCat relies on polling Apple/Google/Stripe, which introduces longer delays.

S3 exports are generated daily, so they’re more of a snapshot of the state of your subscribers. It’s possible for users to subscribe/unsubscribe multiple times in a day, but the state will be captured once per day for the S3 export. So what you see in Google Play Console and Stripe might not match exactly with what’s in the S3 exports if the user subscribed/unsubscribed since the last export.

Badge +1

Thanks for the clarification Sharif. In that case, how do we detect if and when a user unsubscribed and then resubscribed from the data in S3? I’d expect the unsubscribe_detected_at field to stay non null for the transaction row for which it occurred. For example:

 

transaction id start date end date unsubscribe detected at  
100 Jan 1 2021 Jan 31 2021    
101 Feb 1 2021 Feb 28 2021 Feb 15 2021 user unsubscribes
102 Apr 1 2021 Apr 30 2021   user resubscribes

 

From what I can tell, the Feb 15 2021 value will become null when transaction id 102 occurs according to your answer.

Userlevel 5
Badge +9

If the customer re-subscribes, the unsubscribe_detected_at column will get set back to null, since it’s based off a snapshot of the current state of transactions.

If you want to detect and track events like this, webhooks may be a better solution since you’ll get alerted of these types of things when they occur and wouldn’t miss any state changes between deliveries.

To detect these through ETLs, you may be able to check if the transaction already exists in your table (based off the store_transaction_id) and see what has changed between the latest delivery and what you already have saved. For example a change in the unsubscribe_detected_at column between deliveries would indicate the customer either cancelled, or resubscribed. 

Badge +1

Thanks Ryan.

Is there any way for you to give us data for a user journey via the ETLs without us having to build it? For example, if I look up a customer on the RevenueCat console, it lists the events properly - like when the user opted out of auto renew and when they resubscribed.

Reply