Appendix A: GDPR SQL scripts

CS_GDPR_Anonymise_Count.sql

• Action: Does not remove any records. Retrieves a distinct and non‐distinct count of communications records to anonymise for a given URN in the Cascade database.

• Usage: supply a URN in line 3 of the script on which to perform the action.

CS_GDPR_Remove_Count.sql

• Action: Does not remove any records. Retrieves a distinct and non‐distinct count of communications records to remove for a given URN in the Cascade database.

• Usage: supply a URN in line 3 of the script on which to perform the action.

CS_GDPR_Remove_Execute.sql

• Action: Caution, removes records! Retrieves a distinct and non‐distinct count of communications records to remove for a given URN in the Cascade database, then removes them.

• Usage: supply a URN in line 3 of the script on which to perform the action.

FB_GDPR_Anonymise_Count.sql

• Action: Does not remove any records. Retrieves a distinct and non‐distinct count of Facebook users and Attributes to anonymise for a given firstname/lastname combination in the Facebook Responses database.

• Usage: supply values for the FirstName and LastName variables in lines 3 & 4 of the script.

FB_GDPR_Anonymise_Execute.sql

• Action: Caution! Anonymises and deletes data. For a given firstname/lastname combination, this script sets the following table column combinations to NULL/delete in the Facebook Responses database.

  Table Name	Column Name	Action
  FacebookUsers	first_name	Null
  	last_name	Null
  	link	Null
  	name	Null
  FacebookUserAttributes	attribute_value	Null
  FacebookEventRSVP	event_rsvp	Delete
  FacebookComment	source	Delete
  FacebookLike	like	Delete
  FacebookSharedPost	sharedpost	Delete

• Usage: supply values for the FirstName and LastName variables in lines 3 & 4 of the script.

FB_GDPR_Remove_Count.sql

• Action: Does not remove any records. Retrieves a distinct and non‐distinct count of Facebook users and Attributes to anonymise for a given firstname/lastname combination in the Facebook Responses database.

• Usage: supply values for the FirstName and LastName variables in lines 3 & 4 of the script.

FB_GDPR_Remove_Execute.sql

• Action: Caution! Deletes data. For a given firstname/lastname combination, this script sets the following table/column combinations to NULL/delete in the Facebook Responses database.

  Table Name	Column Name	Action
  FacebookUsers	first_name	Delete
  	last_name	Delete
  	link	Delete
  	name	Delete
  FacebookUserAttributes	attribute_value	Delete
  FacebookEventRSVP	event_rsvp	Delete
  FacebookComment	source	Delete
  FacebookLike	like	Delete
  FacebookSharedPost	sharedpost	Delete

• Usage: supply values for the FirstName and LastName variables in lines 3 & 4 of the script.

PS_GDPR_Anonymise_Count.sql

• Action: Does not remove any records. Retrieves distinct and non‐distinct counts of records that would be anonymised for a given URN and Email Address from the Communications, CommunicationsTrackingHistory, EmailResponse, EmailResponseDetails & LiveData tables.

• Usage: supply values for the Urn and Email variables in lines 6 & 7 of the script.

PS_GDPR_Anonymise_Execute.sql

• Action: Caution, removes data! For a given URN and Email Address combination, this script performs the following table/column combination actions.

  Table Name	Column Name	Action
  CommunicationsTrackingHistory	ContentItemValue	Null
  EmailResponseDetails	Value	Delete
  EmailResponse	URN	Delete
  	Email	Delete
  StateHistoryExternalData	ColumnValue	Delete

• Usage: supply values for the Urn and Email variables in lines 6 & 7 of the script.

PS_GDPR_Remove_Count.sql

• Action: Does not remove any records. Retrieves distinct and non‐distinct counts of records that would be removed for a given URN and Email Address from the Communications, CommunicationsTrackingHistory, EmailResponse, EmailResponseDetails & LiveData tables.

• Usage: supply values for the Urn and Email variables in lines 6 & 7 of the script.

PS_GDPR_Remove_Execute.sql

• Action: Caution, removes data! For a given URN and Email Address combination, this script performs the following table/column combination actions.

  Table Name	Column Name	Action
  StateHistory	All items	Delete
  Pool	All items	Delete
  JourneyHistory	All items	Delete
  Communications	All items	Delete
  CommunicationsTrackingHistory	All items	Delete
  EmailResponseDetails	All items	Delete
  EmailResponse	All items	Delete
  StateHistoryExternalData	All items	Delete

• Usage: supply values for the Urn and Email variables in lines 6 & 7 of the script.

TW_GDPR_Anonymise_Count.sql

• Action: Does not remove any records. Retrieves distinct and non‐distinct counts of records that would be anonymised for a given twitter FullName and TwitterUsername from the user table of the Twitter response database.

• Usage: supply values for the FullName and TwitterUsername variables in lines 3 & 4 of the script.

TW_GDPR_Anonymise_Execute.sql

• Action: Caution! Removes data and records from the Twitter response database according to the following table

  Table Name	Column Name	Action
  user	screen_name	Null
  	name	Null
  	description	Null
  	url	Null
  	profile_image_url	Null
  	profile_image_url_https	Null
  tweet	All items	Delete
  retweet	All items	Delete
  mention_reply	All items	Delete
  follower	All items	Delete
  direct_message	All items	Delete

• Usage: supply values for the FullName and TwitterUsername variables in lines 3 & 4 of the script.

TW_GDPR_Remove_Count.sql

• Action: Does not remove any records. Retrieves distinct and non‐distinct counts of records that would be removed for a given twitter FullName and TwitterUsername from the user table of the Twitter response database.

• Usage: supply values for the FullName and TwitterUsername variables in lines 3 & 4 of the script.

TW_GDPR_Remove_Execute.sql

• Action: Caution! Removes records from the Twitter response database according to the following table.

  Table Name	Column Name	Action
  user	All items	Null
  tweet	All items	Delete
  retweet	All items	Delete
  mention_reply	All items	Delete
  follower	All items	Delete
  direct_message	All items	Delete

• Usage: supply values for the FullName and TwitterUsername variables in lines 3 & 4 of the script.