Hi everyone,
I am trying to create a month over month table for user retention and churn.
Retention: If a user visited in current and previous month, then retained user
Churn: If a user visited in previous month but not in current month, then churned user
My data is essentially website visit log data, sample data below:
user id, visit_timestamp
U1, 2024-12-01
U1, 2025-01-01
U1, 2025-02-01
U2, 2025-02-01
U2, 2025-03-01
U3, 2025-03-01
Final output I am looking to get:
visit_month, Retained user count, Churned user count
Jan 2025, 1 [u1], 0
Feb 2025, 1 [u1], 0
Mar 2025, 1 [u2],1 [u1]
Apr 2025, 0, 2 [u2, u3]
I have already reviewed this, unfortunately it does not solve my use case exactly. Since I am trying to flag the users who visited in current month & previous month and then distinct count them and report next to current month essentially.
Hello @Pankhuri hope this message finds you well!!
I Spend a long time trying to understand your problem and how to bring something real for your problem. This solution is somewhat more complex, but it is a suggestion of what I would do and consider feasible to address the issue of inconsistent filter behaviour in qs. You can combine the approach of parameters with a SQL solution to ensure that the filter selection always triggers the update of visuals. Here is how you can implement this:
Utilise Parameters in QuickSight: Create a parameter in QuickSight named SelectedRoute. Configure the filter to update the parameter’s value based on the user’s selection. Use the SelectedRoute parameter to control the visuals on the dashboard.
Modify the SQL Query: In the dataset, adjust the SQL query to include logic that always returns results based on the parameter, even if the value remains the same after a reset. For example:
SELECT *
FROM routes_table
WHERE route = :SelectedRoute
OR (:SelectedRoute IS NULL AND route = 'Route A') -- Default to Route A if no selection
Configure the Dashboard: Set up the filter to update the SelectedRoute parameter. Ensure that the visuals depend on the modified dataset, which utilises the parameter.
The benefits of this approach include ensuring that any change in the filter is reflected in the visuals, even if the selected value remains the same after a reset. Additionally, the SQL logic ensures that the default value is correctly applied when the parameter is empty or undefined. This approach combines the flexibility of parameters in qs with robust SQL logic to avoid filter state issues.
@lary_andr Unfortunately this won’t work for me as I am trying to make this a dynamic calculation within Quicksight. Such that the users can select custom time periods and check the retention. I have solved this within a SQL query but then the dynamic nature within QS goes away
