GDPR Spreadsheet Improvements to aid data collection for the Transparency Report

[sallytay]

Comments/Suggestions taken from ticket https://github.com/mysociety/whatdotheyknow-theme/issues/938

Improved record keeping for how cases are closed on the GDPR spreadsheet. This is mainly a policy/practice point on the use of the existing "Decision" and "Erased?" fields. Maybe change "Decision" to "Final decision"? Consider an additional option for the "Erased?" field for cases where some material has been removed. Maybe we don't need both a "Decision" and "Erased" field, but rather just the "Final Decision" field, and the "Done" field records if the decision has been actioned?

Suggested solution: For this to work correctly, you'd need a categorisation (which we already do, in the form of selecting the case type), and then a closure reason (e.g. Resolved - comply), followed by a sub-category which confirms what we've done. If we wished to be precise, two sub-categories may be best. That could look like: Category: Erasure (Art 17) Closure reason: Resolved closure reason 1: Comply in full closure reason 2: All data removed That's a very early suggestion, and isn't a final answer! I'd like to give some thought to how we balance the need for improvement, against the need to reduce complexity - automating things would likely help.

Suggested improvement: We could go for a fresh start, a new sheet, possibly with a wider scope to cover all takedown requests, requests for user data and complaints? (SR)Could work if data is easily retrievable - would it be better to keep GDPR separate as legal and then another sheet for everything else? Do we need all the detail for takedowns/user data with new labels? Risk it would take too much admin time? (MD) Possibly, but we need to think carefully about that. I have an idea of sorts, I do need to flesh it out a bit though…

NOTE: The existing setup is heavily bodged from what was there in the beginning, so it doesn't really do what we always need it to do. We do have an issue of metadata overall, and a lack of consistency in terms of audit logs, which is a key thing to have when handling these cases.

Linking to mysociety/whatdotheyknow-private#239 and mysociety/whatdotheyknow-private#238

[sallytay]

Some suggestions/ideas from me.

I think the spreadsheet works well and the weekly report on due cases is incredibly useful.

I think that we all now seem to be fairly consistent in how we use the options on the spreadsheet in relation to 'erasure' 'decision' but I know you had some has some potential improvement ideas @mdeuk

It may be useful to have a simple guide to each of the options with drop down choices and when to use each one to ensure consistency. I'll draft something up for this next week.

The way I'd like to be able to get the data from spreadsheet would be:

To be able to filter the data by date range, which could be a week, month, quarter, year - preferably an option to do all of those.

Then a breakdown of:

Count of how many of cases were logged for each right during the set date range Count of how many cases there are of each decision type (all and then also by right)

If it's possible to get this as a list as well so that I can see all the specific right cases together that would also be useful - perhaps something a filter could be used for.

It would be great to have these a report that I could run, say once a month, but that requires more work.

Equally I'm happy to take the data from the spreadsheet myself using basic filtering and / or something like a pivot table - I'd just need some instructions on how best to do this as my excel skills are not much use with Google sheets. I could take a copy of the master sheet and run my filters/pivot tables separately so as not to affect the way the current spreadsheet works?

Would welcome other's thoughts on this too.

Sally

[sallytay]

I've had a look at the data that we all enter when putting a case on the GDPr spreadsheet and I think generally we are pretty consistent in our approach.

I've written up how I think each of the options works, which I hope is in keeping with the methodology that @mdeuk originally laid out (please correct anything I've got wrong) and a couple of suggestions on how it could be tweaked slightly perhaps

https://docs.google.com/spreadsheets/d/11MqL5jwf0NfLNX92UNuymB2KbHIyyehBlmqnEz-9cHo/edit#gid=0

[sallytay]

Update:

I've worked out a very basic way to extract the data that I think I need from the spreadsheet, which I can do once month that doesn't require any changes to be made to the spreadsheet. This is definitely not a final version but it's a start.

By workings/drafts/ideas are in here https://drive.google.com/drive/folders/1N-PJ2thga9tYLjpF3eTNSCWJ0LFJx0EK

Sally

[mdeuk]

Sorry for not getting to this until now Sally.

The way I'd like to be able to get the data from spreadsheet would be:

To be able to filter the data by date range, which could be a week, month, quarter, year - preferably an option to do all of those.

Then a breakdown of:

Count of how many of cases were logged for each right during the set date range Count of how many cases there are of each decision type (all and then also by right)

If it's possible to get this as a list as well so that I can see all the specific right cases together that would also be useful - perhaps something a filter could be used for.

I've reviewed our reporting in Data Studio [1] and it does look like we can repurpose some of the existing reporting mechanisms, rather than reinvent the wheel.

We have a dashboard which lists all cases in a given period - this was created to review some of the transparency report data from last year. I reckon it should be possible to repurpose this, with some improvement on the data export part.

Can you give me a brief outline of the data you'd find particularly helpful to have (as in, if an export was provided - what should it include), and I can see if it's possible to do this without much work? Drop me an email if you'd prefer 😀

Edit: Actually, I realise the data needed might be similar to that outlined at https://github.com/mysociety/whatdotheyknow-theme/issues/938#issuecomment-988915606 - would this be a useful starter-for-ten, considering generically rather than ICO specific?

---

[1] Data Studio takes a live extract of the data in the raw sheet - the great thing here is that it then allows for analysis without modifying the raw datasource. It's the tool we use for automated reports, so the reporting on there just now is primarily what comes out in the weekly report. It has scope for improvement!

[sallytay]

@mdeuk thank you for this - I'll drop you an email with a copy of a very basic manual version I created for myself as they might explain it better. If we can do something without having to re-invented the wheel that would be great.

[sallytay]

I think we can closed this now - due to all the improvements that have been done and continue to be done at: https://github.com/mysociety/whatdotheyknow-private/labels/Tracker