Anonymisation

Normal - But Not Good - Practice

Field	Value
first name	DATA ANONYMISED
middle initials	DataAnon
last name	DATA ANONYMISED
title	Data Anonymised
address line 1	Data Anonymised
address line 2	Data Anonymised
town	Data Anonymised
postcode	SE12 anon
email address	dataanonymised@company.org
daytime phone	00000000000000
evening phone	00000000000000
mobile phone	00000000000000
last-used phone	00000000000000
bank account name	DATA ANONYMISED
bank account number	12345678
bank sort code	000000
date of birth	00-00-1963

Why Is This Bad?

• a data value should always be either a valid, true value, or null
• Most of the time, users of all sorts are working with current, active, data; they should not have to check for anonymisation. Especially, they should not have to check multiple fields for multiple values
• this scheme wastes a lot of space and results in a lot of records having the same values; this makes indexing and searching inefficient.
• having seen these examples, individual users do put in partial anonymisation manually. When they do this they often enter values similar to these, but not quite the same - spelling, upper/lower case, the wrong number of zeros, some other "dummy" value (like 999999). Users checking for anonymisation will miss these unexpected, and undocumented, values
• This approach pollutes your data, and guarantees that your reports will be wrong.

Why Do We Anonymise Data

To simplify the rules: in general an organisation can keep the data it needs to service its customers' real needs. (Customers really do not need to know about what the organisation describes as its latest, greatest offer.) If you are a payment processing company, you need to know the identity of the person paying and the person receiving the payment so that you can deliver the payment safely. You also need to know that the payer has enough money (or credit) in her account in order to make the payment. If she is paying by card, you need to know the card issuer, the card number, the expiry date, and the card verification value. You need to keep enough of this information to record that the transaction happened and to investigate it in the rare cases where something goes wrong. You do not need the payer's address, post code, phone number, age, gender, nationality, or many other things and you must not keep these, however much you would like them to try to get the customer to use your service again.

The rules about the use of personal data, and the policing of these rules, vary from one country to another. The most enlightened jurisdictions recognise the privacy of each citizen, and protect it.

However, it is generally accepted that organisations can collect statistics about their customers at a broader level. This may include the geographic area in which a customer lives, the age-group the customer belongs to, the frequency of transactions, the value of transactions, the length of time that the customer has been associated with the organisation, and many other things, so long as the customer cannot be identified individually from the data retained.

Some different levels of data an organisation could record is illustrated by the UK postcode. The UK postcode is made up of letters and numbers, in the general format: xxnn yzz, where:

• xx represents an "area" (typically a large town, or an area including some smaller towns)
• nn represents a district within the area
• y represents the "sector", a group of streets within the district
• zz represents the "unit", a group of houses

There are 121 areas, so on average there are about 500,000 people in an area
There are 2,858 districts, so on average there are about 23,000 people in a district
Within each full postcode, there are on average about 40 people.

These are averages; the actual number in each part of the hierarchy is quite variable.

Given the full postcode, you need only one or two other items of information to identify an individual person.

It is therefore considered acceptable to store the postcode area and district, but not the sector and unit in anonymised data.

Other data items need to be specified in broad groups. It is possible to identify an individual by date of birth, when this is combined with just a few other data points. However, age groups are often considered useful for demographic profiling and, certainly in 10-year bands, they are not identifying.

"Pseudonymisation"

"Pseudonymisation" is an ugly word, and a sneaky one. Pseudonymisation means making personal information a little bit harder to find.

If, using the examples in this article, we kept another table with the customer_id and all the personal information, and restricted access to this table, then we could claim that we had "pseudonymised" the data. In practice we would have done a piece of work to achieve nothing.

We should always restrict access to personal information. Our systems should be protected by security which ensures that only authorised people, with a legitimate need, can see or change data: personal data, financial data, anything that is important to us. Simply restricting access to personal data is not anonymisation.

And why would we pay to store and manage data that is not important to us? Organisations often want to hoard every scrap of information, no matter how old or trivial it may be. The Big Tech companies promote the idea that this pool of data is a resource that can deliver "insights" that will magically enable the organisation to beat its competitors by applying Data Science or Artificial Intelligence to it. In practice, this idea is massively over-sold.

I recently spent a couple of weeks analysing the relationship between donations and campaign actions for a large charity. (Campaign actions could be: signing a petition, writing to your MP, going on a march, displaying a sticker in your car window, and so on.)

The person who commissioned my work was a fund-raising manager who wanted to demonstrate a link between campaigning and donating so that he could get funding for some high-profile campaigns and therefore enhance his career prospects. My analysis showed very clearly, that supporters of the charity tended to be either campaigners or donors. A few did both, but almost nobody started with campaigning and then progressed to donating.

The data did not support the fund-raising manager's aspirations so he first suggested that the evidence was wrong, and then ignored it. Unfortunately, a lot of reports are munged to show what a senior manager wants to see.

The privacy regulations, explicitly allow the use of personal data in the many cases where it is necessary. Pseudonymisation is a performative practice that serves no legitimte purpose. When it is not necessary, we should delete personal data from our systems.

See Introduction to anonymisation from the Information Commisioner's Office.

Recommended Approach

Anonymised Data must be removed from tables that contain the live, active data.

Normal processing will be easier, more efficient, and less prone to error if the expired records are removed from the tables that contain the live, active data.

Anonymised data, with demographic summary groups, may be stored in separate tables

Tables specifically designed for the level of data that we want to preserve to enhance the statistical picture, will be smaller, easier to use and more effective than data with some variant of "anonymised" splashed all over it. Most queries will not want to look at anonymised data. Those that want to look at both can combine the data easily, as shown in the example below.

You can find all the SQL scripts to implement what we describe, further down this page.

An Example

Suppose you have customer and transaction tables, samples of which, look like this:

[In practice you would probably have the customer table normalised so that addresses and phone numbers were in separate tables. There would also be more columns in the transaction table. This simplified presentation is to help focus on the specific task of anonymisation.]

We're going to create a table for anonymised customers, like this:

The anonymised transaction table is just the same structure as the live, active transaction table in this case, because there is no personally identifiable information in the transaction table as we have defined it. This will not always be the case.

We have a separate table, to make it clear to anyone stumbling over these tables in the future, that the transaction table contains transactions for active customers and the anonymised_transaction table contains transactions for anonymised customers. You can join customer to transaction and you can join anonymised_customer to anonymised_transaction.

Populating The Anonymised Data

You can find all the SQL scripts to implement what we describe, further down this page.

We have decided, for the purpose of this example, to anonymise all data for any customer who has not had a transaction with us in the last 7 years. This is quite a common rule since the authorities in UK and the USA require financial records to be kept for 7 years. (Other countries vary between 0 and 10 years Follow the link and go to Table 11 - Record-keeping Requirements.)

So, we write a query to select all the customer records where the customer does not have a transaction record with a transaction date in the last 7 years. We copy the data that we can keep for these customers to another table, which we have called anonymised_customer.

We then copy the transactions for these customers to the anonymised_transaction table.

Removing The Anonymised Data From The Live Data Tables

Immediately after populating the anonymised data tables, we delete the transactions for these customers from the live, active transaction table, and then the customer records themselves from the live, active customer table.

Our live tables have a referential integrity check between the customer and the transactions because we want all transactions to be attached to a customer. This is why we do the steps in this order:

1. Copy inactive customers from live to anonymised
2. Copy transactions for inactive customers from live to anonymised
3. Delete transactions for inactive customers from live
4. Delete inactive customers from live

Since we have preserved the customer and transaction IDs, we can use these to delete the anonymised records from the live, active tables.

Using The Anonymised Data

Queries and reports on the live, active data, are what you could have done anyway.

Queries and reports on the anonymised data are simpler, since the personally identifiable data has been removed.

The example below shows how to see the active and anonymised data in the same query.

SQL Scripts

Create anonymised_customer Table

create table anonymised_customer
(
    id                           integer primary key,
    date_anonymised              timestamp,
    postcode_area_and_district   varchar(4),
    year_of_birth                integer
);

Create anonymised_transaction Table

create table anonymised_transaction
(
    id                      integer,
    customer_id             integer references anonymised_customer(id),
    transaction_date        date,
    transaction_amount      numeric(9,2)
);   

Populate anonymised_customer Table

Anonymise any customer who has not had a transaction in the last 7 years.

Note that we copy the original IDs. An ID does not identify a person if person's record has been deleted, so it is acceptable to keep it, and it does make it easier to attach each transacion to its correct customer.

insert into anonymised_customer
(
    id,
    date_anonymised,
    postcode_area_and_district,
    year_of_birth
)
select
    id,
    now(),
    substring(postcode for position(' ' in postcode) - 1),
    extract(year from date_of_birth)
from
    customer
where
    id in
    (
        select
            customer_id 
        from
            transaction 
        group by
            customer_id 
            having age(max(transaction_date)) > interval '7 years'
    );

Populate anonymised_transaction Table

Anonymise transactions for any customer who has not had a transaction in the last 7 years.

insert into anonymised_transaction
(
    id,
    customer_id,
    transaction_date,
    transaction_amount
)
select
    id,
    customer_id,
    transaction_date,
    transaction_amount
from
    transaction
where
    customer_id in
    (select id from anonymised_customer);

Remove Anonymised Data From Live, Active Tables

Delete anonymised records from, firstly, the transaction table, then the customer table.

Since we have preserved the customer and transaction IDs, we can use these to delete the anonymised records from the live, active tables.

delete from transaction
    where
    customer_id in
    (select id from anonymised_customer);


delete from customer 
where
    id in
    (select id from anonymised_customer);

Using The Anonymised Data

Queries and reports on the live, active data, are what you could have done anyway.

Queries and reports on the anonymised data are simpler, since the personally identifiable data has been removed.

This example shows the combination of active and anonymised data, with a few fiddly manipulations that you may find useful.

We have also used postcode data to find the name of the region that matches the first part of the postcode (area and district) that we keep in the anonymised data. Our source for the postcode data is Camden Council, which keeps postcode data for the whole country, with many useful attributes for each postcode. We have found this dataset to be kept up-to-date and be very easy to download, free-of-charge, using the export function on their website.

select
    a.customer_id,
    a.postcode_area_and_district,
    a.year_of_birth,
    case
        when extract(year from now()) - a.year_of_birth >= 90 
        then '90+'
        else
            floor((extract(year from now()) - a.year_of_birth) / 10) * 10        || '-' ||
            (floor((extract(year from now()) - a.year_of_birth) / 10) * 10) + 9
    end                        as age_group,
    b.region,
    c.transaction_year,
    c.transaction_count,
    c.total_transaction_value
from   
    (
        select        /* data from live, active customers */
            id                                                     as customer_id,
            substring(postcode for position(' ' in postcode) - 1)  as postcode_area_and_district,
            extract(year from date_of_birth)                       as year_of_birth
        from
            customer
        union
        select        /* data from anonymised customers */
            id,
            postcode_area_and_district,
            year_of_birth
        from
            anonymised_customer
    ) a
    join
    (
        select distinct
            substring(postcode for position(' ' in postcode) - 1) as postcode_area_and_district,
            replace(region, '(pseudo) ', '')  as region
        from
            postcode 
    ) b        
    on a.postcode_area_and_district = b.postcode_area_and_district
    join
    (
        select
            customer_id,
            extract(year from transaction_date) as transaction_year,
            count(*)                            as transaction_count,
            sum(transaction_amount)             as total_transaction_value
        from
            transaction
        group by
            customer_id,
            transaction_year
        union
        select
            customer_id,
            extract(year from transaction_date) as transaction_year,
            count(*)                            as transaction_count,
            sum(transaction_amount)             as total_transaction_value
        from
            anonymised_transaction
        group by
            customer_id,
            transaction_year
    ) c
    on c.customer_id = a.customer_id;