Appendix A: GDPR SQL scripts

Use the following SQL scripts when running a version of Apteco software earlier than Q1 2018. They're essentially in pairs: one to provide a count and one to execute the action.

Cascade scripts¶

CS_GDPR_Anonymise_Count.sql

Action: Doesn't remove any records. Retrieves a distinct and non-distinct count of communications records to anonymise for a given Unique Reference Number (URN) in the Cascade database.
Usage: Supply a URN in line 3 of the script.

CS_GDPR_Remove_Count.sql

Action: Doesn't 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: Doesn't 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: Doesn't 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: Doesn't 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: Doesn't remove any records. Retrieves distinct and non-distinct counts of records it would remove 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: Doesn't 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: Doesn't remove any records. Retrieves distinct and non-distinct counts of records it would remove 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.