How to best design for unique users count

[LucianaPadua]

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):

• date
• host
• device
• channel

And several metrics that can be calculated:

• sessions
• revenue
• costs

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