-
Your SQL query isn’t inherently flawed, but there seems to be a mismatch between your actual data distribution and how the query processes the data. One possible reason for sky-high values from Day 1 onward might be multiple records per user in the ‘table’ you are using for the analysis. Ensure that the ‘first_open’ event is unique per user – ideally, there should only be one occurrence of ‘first_open’ per user. If there are multiple ‘first_open’ actions per user, that might create a redundancy in the ‘user_pseudo_id’ leading to inflated numbers after Day 0.
You may want to check your data source or tracking setup for any setting that might log multiple ‘first_open’ events for the same user. Another possibility could be that the ‘user_pseudo_id’ isn’t persistent for each user across different days, causing inaccurate counts of ‘unique’ users in your output.
Remember that even though the structure of your SQL query is okay, the input data quality and data tracking setup play a significant role. The query can only analyze based on the data it is given, and if that data is flawed in some way, it reflects in your analysis.
