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
    (select 1+a.nr+10*b.nr as session_id, ceil(rand()*3) as nr
        (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
        (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
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

2 Answers 2


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.

  • $\begingroup$ A very good answer! For those reading, I'd like to add that in the case of 3rd party cookies, many safari mobile versions will not take those by default, and other browsers have the same in their pipelines. Keep those in mind and treat them separately. $\endgroup$
    – AdrianBR
    May 16, 2014 at 5:10
  • 1
    $\begingroup$ Cookie churn is quite the problem for services that don't require log in. Many users simply don't understand cookies though so you are likely to have some cohort that you can follow for an appreciable amount of time. $\endgroup$
    – cwharland
    May 16, 2014 at 14:44

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.

  • $\begingroup$ The context would be info trackable within a single site with a 3rd party cookie, via an iframe. The site would be ecommerce. I find google analytics mostly looks at IP, sometimes at useragent, and I am able to get very similar numbers from looking only at IP in a timeframe. But google analytics is known to over-report by 30% ish, depending on context $\endgroup$
    – AdrianBR
    May 15, 2014 at 14:57
  • $\begingroup$ Looking at visited product pages doesn't help much either, as the structure of the shop is such that it leads users down predetermined paths, leading to very similar behaviour $\endgroup$
    – AdrianBR
    May 15, 2014 at 14:59
  • 1
    $\begingroup$ Also, I am aware that ML does not fit in the context of this question. Rather, hard coded algorithms are used by most tracking solutions that offer sensible results. The last few degrees of accuracy, that would be achievable with ML are of less relevance, since this info is rather used for observing trends. $\endgroup$
    – AdrianBR
    May 15, 2014 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.