Number of distinct users

[mkulov]

Hi,

Any idea how to calculate the numer of unique tagged users over a period of time?

I tried the query below however it returns multiple rows with value 1.

This is great so far. 

Now I only need to sum up all rows.

Any Idea how to caluclate the sum of all distinct users?

SELECT count(DISTINCT userId) FROM usersession GROUP BY userId

Thank you,

Martin Kulov

[DanielS]

Hi,

I suggest this one:

SELECT count(DISTINCT ip) FROM usersession WHERE userType IS "REAL_USER" AND newUser =true

also you can filter by application name.

Hope it helps

[ABB]

I had earlier used DISTINCT userId instead of DISTINCT ip and both have different counts. Which one is preferred?

Another confusing part is for application filtering. For the above USQL, I see selection of useraction.application, whereas the distinct user count is from usersession table. Will it be okay to use useraction.application to filter by application?

SELECT count(DISTINCT userId) FROM usersession WHERE userType IS "REAL_USER" AND newUser =true   ....returns more distinct records

SELECT count(DISTINCT ip) FROM usersession WHERE userType IS "REAL_USER" AND newUser =true .....returns lesser distinct records

[DanielS]

I couldn't establish a general rule, but personally, I preferred IP. Why? Because if the same user is in the office or at home, the public IP is the same also if he uses different devices because the public IP is NATed for those local devices. It may happen that if the user disconnects the Wifi device and switches to 4G/5G, the IP changes, but it could allow this Beta(error) because it is not the most common behavior. I have done tests this way and for me it is much more convenient.
Another scenario that has been evaluated is that of different users from the same home/office looking for the same page. But I have done tests and with the exception of newspapers/big shopping sites/social networks. It is highly unlikely that the pages will tend to be the same. So if your app is from the sites described above, maybe another approach is needed, but for everyone else it's good.

[mkulov]

it was the GROUP BY clause that was bugging me 🙂