Hi,
Trying my luck with a new topic as I have not found anything yet.
We have daily aggregated data with certain dimensions (just naming some to simplify):
And several metrics that can be calculated:
However, one of our main metrics is users, and as we know, a user can have more than one device, visit in different days, etc. So the count distinct cannot be pre-aggregated because it will depend on the dimensions filtered.
So what we do to count distinct users properly, we join the aggregated view with the session view on date, host, device, channel. We have millions of records and it takes ages, even using approximate counts.
In SQL we would do a join only with the aggregated dimensions, depending on what the user is filtering. For example, if filtering host but no channel, we would count distinct for the host, day level:
WITH user AS (
SELECT date, host, count(distinct userid) as users
FROM session
GROUP BY 1,2)
SELECT date, host, users,sum(sessions), sum(revenue), sum(costs)
FROM aggregated data a
LEFT JOIN user u
ON a.host = u.host and a.date = u.date
GROUP BY 1,2
How I could implement this in Looker? I could have aggregated awareness explores, but I cannot join 2 explores.
Any help is appreciated!!
Thanks!
(we are using Redshift, unfortunately).
Luciana