Clustering unique visitors by useragent, ip, session_id

Question

Given website access data in the form session_id, ip, user_agent, and optionally timestamp, following the conditions below, how would you best cluster the sessions into unique visitors?

session_id: is an id given to every new visitor. It does not expire, however if the user doesn't accept cookies/clears cookies/changes browser/changes device, he will not be recognised anymore

IP can be shared between different users (Imagine a free wi-fi cafe, or your ISP reassigning IPs), and they will often have at least 2, home and work.

User_agent is the browser+OS version, allowing to distinguish between devices. For example a user is likely to use both phone and laptop, but is unlikely to use windows+apple laptops. It is unlikely that the same session id has multiple useragents.

Data might look as the fiddle here: http://sqlfiddle.com/#!2/c4de40/1

Of course, we are talking about assumptions, but it's about getting as close to reality as possible. For example, if we encounter the same ip and useragent in a limited time frame with a different session_id, it would be a fair assumption that it's the same user, with some edge case exceptions.

Edit: Language in which the problem is solved is irellevant, it's mostly about logic and not implementation. Pseudocode is fine.

Edit: due to the slow nature of the fiddle, you can alternatively read/run the mysql:

select session_id, floor(rand()*256*256*256*256) as ip_num , floor(rand()*1000) as user_agent_id
from 
    (select 1+a.nr+10*b.nr as session_id, ceil(rand()*3) as nr
    from
        (select 1 as nr union all select 2 union all select 3   union all select 4 union all select 5
        union all select 6 union all select 7 union all select 8 union all select 9 union all select 0)a
    join
        (select 1 as nr union all select 2 union all select 3   union all select 4 union all select 5
        union all select 6 union all select 7 union all select 8 union all select 9 union all select 0)b
        order by 1
    )d
inner join
    (select 1 as nr union all select 2 union all select 3   union all select 4 union all select 5
    union all select 6 union all select 7 union all select 8 union all select 9 )e
    on d.nr>=e.nr

Answer 1

One possibility here (and this is really an extension of what Sean Owen posted) is to define a "stable user."

For the given info you have you can imagine making a user_id that is a hash of ip and some user agent info (pseudo code):

uid = MD5Hash(ip + UA.device + UA.model)

Then you flag these ids with "stable" or "unstable" based on usage heuristics you observe for your users. This can be a threshold of # of visits in a given time window, length of time their cookies persist, some end action on your site (I realize this wasn't stated in your original log), etc...

The idea here is to separate the users that don't drop cookies from those that do.

From here you can attribute session_ids to stable uids from your logs. You will then have "left over" session_ids for unstable users that you are relatively unsure about. You may be over or under counting sessions, attributing behavior to multiple people when there is only one, etc... But this is at least limited to the users you are now "less certain" about.

You then perform analytics on your stable group and project that to the unstable group. Take a user count for example, you know the total # of sessions, but you are unsure of how many users generated those sessions. You can find the # sessions / unique stable user and use this to project the "estimated" number of unique users in the unstable group since you know the number of sessions attributed to that group.

projected_num_unstable_users = num_sess_unstable / num_sess_per_stable_uid

This doesn't help with per user level investigation on unstable users but you can at least get some mileage out of a cohort of stable users that persist for some time. You can, by various methods, project behavior and counts into the unstable group. The above is a simple example of something you might want to know. The general idea is again to define a set of users you are confident persist, measure what you want to measure, and use certain ground truths (num searches, visits, clicks, etc...) to project into the unknown user space and estimate counts for them.

This is a longstanding problem in unique user counting, logging, etc... for services that don't require log in.

Answer 2

There's not much you can do with just this data, but what little you can do does not rely on machine learning.

Yes, sessions from the same IP but different User-Agents are almost certainly distinct users. Sessions with the same IP and User-Agent are usually the same user, except in the case of proxies / wi-fi access points. Those you might identify by looking at the distribution of session count per IP to identify likely 'aggregate' IPs. Sessions from the same IP / User-Agent that overlap in time are almost surely distinct.

To further distinguish users you would need more info. For example, the sites or IP addresses that the user is connecting to would be a very strong basis for differentiating sessions. Then you could get into more sophisticated learning to figure out when sessions are the same or different users.