Appendix A: GDPR SQL scripts

The following SQL scripts are used when running a version of Apteco software earlier than Q1 2018. They are essentially in pairs—one to provide a count and one to execute the action.

Cascade 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.

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.

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.

Facebook scripts¶

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 and 4 of the script.

FB_GDPR_Anonymise_Execute.sql

• Action: Caution—anonymises and deletes data! For a given firstname/lastname combination, 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 and 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 for a given firstname/lastname combination.
• Usage: Supply values for the FirstName and LastName variables in lines 3 and 4 of the script.

FB_GDPR_Remove_Execute.sql

• Action: Caution—deletes data! 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 and 4 of the script.

PeopleStage scripts¶

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, and LiveData tables.
• Usage: Supply values for the Urn and Email variables in lines 6 and 7 of the script.

PS_GDPR_Anonymise_Execute.sql

• Action: Caution—removes data! For a given URN and Email Address combination, performs the following table/column 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 and 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.
• Usage: Supply values for the Urn and Email variables in lines 6 and 7 of the script.

PS_GDPR_Remove_Execute.sql

• Action: Caution—removes data! For a given URN and Email Address combination, performs the following table/column 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 and 7 of the script.

Twitter scripts¶

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 and 4 of the script.

TW_GDPR_Anonymise_Execute.sql

• Action: Caution—removes data and records from the Twitter response database.

  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 and 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.
• Usage: Supply values for the FullName and TwitterUsername variables in lines 3 and 4 of the script.

TW_GDPR_Remove_Execute.sql

• Action: Caution—removes records from the Twitter response database.

  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 and 4 of the script.