Validate Customers
Introduction
Regular maintenance of the format of customer records on Spektrix is important for several reasons:
- Printing poorly formatted names and addresses on ticket address slips that do not conform to the Post Office standard for addresses makes the theatre look unprofessional.
- Certain Spektrix reports rely on town names being spelt consistently. For example, St. Albans with a full stop rather than St Albans without.
- Most importantly, Spektrix is unable to reclaim gift aid on a customer’s donations if their postcode is not formatted correctly e.g. contains lower case letters. Remarkably, given that Spektrix provides a Find Address button, a surprising number of customers still insist on entering their addresses manually, complete with incorrectly formatted postcodes.
With over 20,000 active customer records on Spektrix it is clearly infeasible to manually inspect each one so some form of automated checking is necessary. Previously this was done by extracting a list of all active customers from Spektrix, running this through a standalone program to identify invalid records, and then laboriously correcting them via the Spektrix Sales interface. However, there are often many hundreds of records to correct, especially after a popular show, and this process can take many hours. Moreover, this record checking requires the installation of the validation program on the local PC and so is restricted to only those who have this program installed. Hence the need to create a more efficient Validate Customer application that can be used by anyone with the appropriate permissions.
The secure Validate Customer web app on the Members’ website helps speed up the customer record checking process significantly and can be used by any website admin or by the System User without requiring a separate program to be installed locally. In summary, using this application the process involves the following steps:
- Creating and downloading the list of active customers from Spektrix as before.
- Loading this list into a secure web page which scans through it to produce a table of invalid customer records, with invalid fields clearly identified.
- Working through the table using the application’s inbuilt editor to correct the records. The editor suggests appropriate corrections, thereby minimising the amount of typing required.
- Once each record has been corrected, clicking on an Apply button which causes the customer record to be updated directly via the secure Spektrix Application Programming Interface (API), without the need to use the Spektrix Sales interface.
- Once all records have been corrected, deleting the active customers list from the local computer. This latter step is essential for the confidentiality of customer information.
The reason that the list of active customers still has to be obtained from Spektrix first is because the API does not provide any way to download customer data in bulk for security reasons. The customer’s unique identifier, contained within the active customers list, is required in order to update any incorrect data in their record.
Confidentiality and Security
Confidentiality of our customers data is paramount to ensure that their information is not compromised and to ensure that we meet our obligations under GDPR. Therefore, with the exception of COT Members who have given their permission for their details to appear in the Email and Phone list, customer data must not be uploaded to the website itself. Instead, customer data is only processed on the application user’s local PC within the browser. Communication between the browser, the website server and Spektrix is authenticated and encrypted so that only valid users can access the data.
Once all customer records have been corrected the active customers list must be deleted from the users’ local PC.
Using the Validate Customers Application
Here are the detailed steps required for you to use the Validate Customers application on the Members’ website.
- Logon to Spektrix, select the Insights & Mailings interface from the Spektrix Control Panel, and select the Customer Lists. Expand the Customer List Explorer in the left hand panel by clicking on the + sign and then click on Administration. Locate and click on the list called Active Customers, then from the Choose an action drop down, select Output as CSV. Spektrix will create the list and once it has finished, will display a link at the top right of the screen. Click on this link to download the Active Customers file called CustomerList.csv to the Downloads folder on your computer.
- Logon to the Members’ website at members-abbeytheatre.org.uk as the special System User. Then click on the Customers/Validate menu in order to be able to access the Validate Customer Details web page. Only the System user (and admin users) may view this page; any other user who inadvertently stumbles across it, will be told that they not authorised to use it.
- Click on the Choose File button and in the resulting File Open dialog, navigate to your Downloads folder and select the CustomersList.csv file that you have just downloaded from Spektrix. After a few seconds, the Validate Customer application will display a table containing just those records containing errors, with invalid fields highlighted in red. A count of the number of invalid records is displayed above the table. If there are no invalid records an appropriate message will appear instead of the table. If you attempt to load a file with the wrong format, it will be rejected with an error message.
- Click on any entry in the table of invalid records to bring up the Validate Customer Editor which will show you the customer whose entry you just clicked on. The editor’s left hand column displays the customer’s title, first name, last name, email address, billing name and address and, where different, their delivery name and address with any invalid fields highlighted in red. The editor’s righthand column shows the editor’s suggested corrections for those fields that have been marked as invalid. If necessary, you are able to edit these suggestions although your own input must be valid otherwise it too will be highlighted in red and you will be unable to save your changes to Spektrix.
- At the bottom of the editor is a set of buttons. You may use these to move to the previous or next customer record, or to close the editor. The Apply button will be highlighted once the edited customer details are correct and pressing this button will update the customer’s details in Spektrix. If you correct a record but then move to a previous or next record without pressing Apply to update Spektrix, any suggested edits or further edits that you may have made will be retained by the editor until you return to that customer again.
- When you have finished correcting and applying changes to some or all of the customer records, pressing the Close button will close the editor and return back to the initial table of invalid customer records but minus those customers whose records you have corrected.
- If you refresh your browser and re-open the Customer.csv file, the table of invalid customers will contain an entry for all the customers that were originally identified as being invalid, including all those records that you have just corrected. However, when you work through these records again with the editor, the editor rereads the current values from Spektrix so that you don’t have to work through all the same corrections again. (The reason that the application doesn’t start by reading the current values from Spektrix as soon as the Customer.csv file is loaded so that records you may already have corrected are excluded is because there may be several hundred records needing correction and it takes a very long time to read this many records from Spektrix).
- Once you have corrected all the invalid customer records you MUST delete the Customer.csv file from your Downloads folder and then empty your computer’s Recycle Bin to completely remove the file from your PC.
Remove Email Preferences
Introduction
Approximately once a month the theatre sends an e-newsletter containing details of upcoming events to those of our customers that have given their permission for us to send them marketing emails. Whilst our objective is to increase the number of customers who are interested in the theatre, over time some customers change their emails without notifying us or are no longer interested and stop opening our emails. At the time of writing we are sending emails to approximately 5650 customers of whom around 750 (13%) have not opened any marketing email within the past 3 years (but excluding anyone who has only signed up within the past three months). Whilst the cost of sending these unopened emails is fairly small (0.3p per email), nevertheless it is helpful to remove these customers from the distribution list so that we get a more accurate picture of who is interested in buying tickets to our productions.
Removing customers from the list involves resetting the Marketing Email Preference on their account record. This can be done via Spektrix but only by sending a list of customers to Spektrix Support and then waiting for them to schedule the update. The Remove Email Preferences web app on the Members’ website allows us to do it for ourselves without involving Spektrix Support.
Using the Remove Customer Preferences Application
Below are the detailed steps required for you to use the Remove Customer Preferences application on the Members’ website. The same considerations for customer security as described above for the Validate Customers application apply here so it is necessary to download the list of customers from Spektrix and then delete it after use.
- Logon to Spektrix, select the Insights & Mailings interface from the Spektrix Control Panel, and select the Customer Lists. Expand the Customer List Explorer in the left hand panel by clicking on the + sign and then click on Administration. Locate and click on the list called Monthly e-newsletter who haven’t opened an email in the past 3 years, then from the Choose an action drop down, select Output as CSV. Spektrix will create the list and once it has finished, will display a link at the top right of the screen. Click on this link to download the Customers file called CustomerList.csv to the Downloads folder on your computer.
- Logon to the Members’ website at members-abbeytheatre.org.uk as the special System User. Then click on the Customers/Remove Email Preferences menu in order to be able to access the Remove Email Preferences web page. Only the System user (and admin users) may view this page; any other user who inadvertently stumbles across it, will be told that they not authorised to use it.
- Click on the Choose File button and in the resulting File Open dialog, navigate to your Downloads folder and select the CustomersList.csv file that you have just downloaded from Spektrix. After a few seconds, the Remove Email Preferences application will display a paginated table with a count of records, a page navigator and an Update button. If you attempt to load a file with the wrong format, it will be rejected with an error message.
- Updating the email preferences for a page’s worth of customer records takes quite a long time so rather than updating the entire collection of customer records, pressing the Update button updates only the customers on the currently displayed page. As the updates progress a progress bar is displayed and you will note that the Standard Email column changes from Yes to No as each record is updated. You can press the Cancel button on the progress bar to stop the update at that point although this will not affect any records that have already been updated.
- If you refresh your browser and re-open the Customer.csv file, the original table of customers will appear, including all those records that you have just updated. However, when you work through the pages of records again, the application reads the current values from Spektrix and only updates those for which Standard Email is still marked as Yes so it takes slightly less time.
- Once you have updated all the customer records you MUST delete the Customer.csv file from your Downloads folder and then empty your computer’s Recycle Bin to completely remove the file from your PC.