Re-engagement campaigns need a reliable definition of "reactivated": a user who had prior activity, went quiet for at least one full calendar month, then came back. Distinguishing reactivations from continuous users lets growth teams measure win-back campaign effectiveness and calculate the true size of the lapsed user pool. Using the user_events table, return user_id and reactivation_month for every month where a user reactivated, ordered by reactivation_month then user_id.
user_events
| column | type |
|---|---|
| id | INTEGER |
| user_id | INTEGER |
| event_type | TEXT |
| event_date | DATE |
user_events
| id | user_id | event_type | event_date |
|---|---|---|---|
| 1 | 1 | login | 2024-01-05 |
| 2 | 1 | login | 2024-03-10 |
| 3 | 2 | login | 2024-01-10 |
| 4 | 2 | login | 2024-02-15 |
| 5 | 3 | login | 2024-01-20 |
| 6 | 3 | login | 2024-04-01 |
| user_id | reactivation_month |
|---|---|
| 1 | 2024-03-01 |
| 3 | 2024-04-01 |
User 1 was active in January, skipped February, and returned in March — that's a reactivation. User 2 was active in January and February with no gap — not reactivated. User 3 was active in January, skipped February and March, and returned in April — that's a reactivation.