Measuring the number of users who have blocked 3rd party cookies


#1

It is not straightforward to measure how many users tracked with the JS tracker and Clojure or Scala RT collectors have blocked third party cookies. That’s because the network_userid field, that contains the cookie value, is always set: it is just reset with each page view. (Because the user’s browser wont persist the cookie between page views.)

We can use this fact to measure the number of users who have third party cookie IDs blocked. The logic is as follows:

  • Identify users by the more reliable first party cookie ID value (domain_userid)
  • For each domain_userid value count the number of page views
  • For each domain_userid value count the number of distinct network_userid values
  • For users with more than one page view, if they have the same number of distinct network_userid values as page views count you know that they’ve blocked third parties. (Because each page view is recorded against a new network_userid value.

The SQL is as follows:

with tpcids_and_pvs_by_fpcids as (
select
domain_userid,
count(distinct(network_userid)) as number_of_3rd_party_cookie_ids,
count() as page_views
from atomic.events
group by 1
)
select
case when number_of_3rd_party_cookie_ids = page_views then 'third party cookies blocked' else 'third party cookies enabled' end,
count()
from tpcids_and_pvs_by_fpcids
where page_views > 1
group by 1;

This returns a table like the following:

        case                | count
-----------------------------+-------
third party cookies blocked | 650
third party cookies enabled | 1972

#2

Actually with the upcoming cookie bounce feature, it should be much easier, as the stream collector can identify those clients blocking 3rd party cookies. And then you can use a placeholder UUID for these clients, which enables you to filter directly by this value.
https://github.com/snowplow/snowplow/pull/2755