PostHog
Incorrect result for a trend unique users query in table
Bug description
User mentioned that numbers inbetween funnels and unique user count in trends table don't match up.
Example:
I verified this on our account: The for a pageview -> pageview count funnel the number in step 0 does not match up with trends result and is higher than what metabase would report as expected.
How to reproduce
- Open trends, select unique users
- Note down total from table down
- Open funnels, do a pageview -> pageview funnel
- Note down total there
2 and 4 don't match
Metabase query for an example expected result run on our team
Environment
- [x] PostHog Cloud
- [ ] self-hosted PostHog, version/commit: please provide
Additional context
cc @EDsCODE @neilkakkar
Came up during support hero.
Related issue seems to be that the date range filter is odd - it's filtering via timestamp >= toTimezone(toDateTime(toStartOfDay(toDateTime('2022-09-13 07:00:00')), 'US/Pacific'), 'UTC') AND timestamp <= toDateTime('2022-09-20 23:59:59')
I think the problem is that the table underneath sums each day up and reports the result, while funnel reports total number of unique users for whole period. If so, we should fix the table.
Thank you for your bug report – we love squashing them!
I think the problem is that the table underneath sums each day up and reports the result, while funnel reports total number of unique users for whole period. If so, we should fix the table.
Agreed. Will change this
timestamp >= toTimezone(toDateTime(toStartOfDay(toDateTime('2022-09-13 07:00:00')), 'US/Pacific'), 'UTC') AND timestamp <= toDateTime('2022-09-20 23:59:59')
There's a few things going wrong here. The start and end time should both be 7:00 because US/Pacific based. The rounding logic in the first clause clamps the timestamp to 00:00:00 which should not happen but it's because toStartOfDay behaves differently compared to the other truncating functions
Working through the timestamp issues.
Consideration on the table total unique reporting. Ideally, we want to get these results without running more separate queries
I think removing the summary table column conditionally for now would be the right thing to be doing. E.g. only show it for totals and sums, but not for avg, formulas etc.