System Efficiencies: Analysing your data

Rachael Norris
Rachael Norris
  • Updated

Cleaning your data and making your system as efficient as possible is a great thing to do. This is especially true if you have been using Spektrix for a number of years or are thinking about embarking on a consultancy project.

In this article, we’ll provide the steps and tools to undertake a full analysis of your system efficiency.

TIP: The process will require access to several interfaces of the Spektrix system. Be sure you have the appropriate user roles and permissions to access each interface mentioned and complete the tasks associated with the system efficiency check.

 

What is a system efficiency check?

Through the process of analysing your system data, you can measure how efficiently your system is running. First, you’ll start by assessing each section of your system and then decide what action is needed to make improvements.

Some of the benefits of undertaking this analysis can be:

  • Faster and more streamlined system
  • Better understanding of customer booking behaviour
  • Faster and more standardised processes
  • Mailings with greater ROI
  • Mailings with increased open rates

 

Preparing for a system efficiencies check

To start, you’ll need to request the System Efficiencies Custom Reports from our team. To do this, contact our support team.

Once you have confirmation that the Reports are on your system, you should see the folder in Reports > System Efficiencies & Healthcheck.

Next, you’ll need to copy and download the System Health Checklist.

 

How to use the checklist

Work through this article alongside the System Health Checklist [Google Sheet] to analyse the following areas of your system.

The System Health Checklist will give you prompts and questions to help you interpret the data. 

Write your results and observations and check the Action Needed box if you think that area needs attention.

TIP: Make sure to consult your colleagues who work closely in specific areas of the system.

Once you have completed this task, you can work through the Results page of the checklist to make improvements to your system.

 

Customer Tags

To analyse the Customer Tags you have on your system and their usage, follow the steps below.

You will first need to create a Customer List and then create a pivot table from the data.

The video below details the process, with written steps below where you can follow along.

VIDEO TIP: Click the CC option to turn on captions. Click into the Settings menu cog icon to control speed, captions and quality.

  1. To start, create a Customer List without any Segments so that it includes all the customers in your database. For the output, choose Customer ID and Tags. Export your Customer List as an Excel File.
  2. Open the file and select all data in the document by clicking the table in the top left.
  3. Then, to create a Pivot Table, go to Insert > Pivot Table > From Table / Range. Place your Pivot Table on a New Worksheet.
  4. Under the Pivot Table Fields menu, check the boxes next to each of the Tags. This will add the data into the Values section.

    TIP: If you have a large number of tags on your system, this may take a while. Follow steps 5 - 8 to apply a Macro to automate this process for you. Otherwise, skip to step 9.  

  5. Press Alt + F11 which will open up Microsoft Visual Basic for Applications in a new Window.
  6. Go to Insert > Module. This will open a blank sheet for you where you will copy and paste the following code.
    Sub AddAllFieldsValues()

    Dim pt As PivotTable

    Dim iCol As Long

    Dim iColEnd As Long

    Set pt = ActiveSheet.PivotTables(1)

    With pt

     iCol = 1

     iColEnd = .PivotFields.Count

     For iCol = 1 To iColEnd

     With .PivotFields(iCol)

     If .Orientation = 0 Then

     .Orientation = xlDataField

    End If

    End With

    Next iCol

    End With

    End Sub
  7. Click the Save icon. Go back to Excel and your Pivot table sheet, select View > Macros > AddAllFieldsValues > Run. This will add all of the tags to the pivot table. If there is a lot of data this might take a few seconds to add all of the fields.
  8. Move the Values metric into the Rows field.
  9. Once all of the values have been added, you may want to sort the document alphabetically so that the tags are sorted by Tag Group. To do this, select all tags on the sheet and go to Sort & Filter then sort alphabetically.

TIP: If you have a lot of tags on the system you may need to break up the data into separate sheets to analyse.

Now you can see how many times each tag is used and what falls under each tag group as well as any duplicate tags. Make a note of the analysis on your System Health Checklist.

 

How to delete Tags

Once you have done an analysis of your Tags, you may want to delete the surplus tags.

WARNING: Deleting a Tag is an irreversible action. Only delete a Tag from your system if you are sure you will not want to use it or Report on it again in the future. Be sure these Tags are removed from any Customer Lists or schedules, as they will cause errors when running these Lists.

To delete Tags, go to the Admin Interface > Customers > Tags. Click into each Tag Group to see the list of Tags contained within. To remove a Tag, click the red cross icon on the selected Tag.

 

 

Customer Attributes

To analyse the Customer Attributes you have on your system and their usage, follow the steps below. 

You will first need to create a Customer List and then a pivot table from the data pulled. 

  1. To start, create a Customer List without any Segments so that it includes all the customers in your database. For the output, choose Customer ID and Attributes. Export your Customer List as an Unformatted Excel File.
  2. Open the file and select all data in the document by clicking the table in the top left corner.
  3. Then, to create a Pivot Table, go to Insert > Pivot Table > From Table / Range. Place your Pivot Table on the Existing Worksheet.
  4. Under the Pivot Table Fields menu, check the boxes next to each of the Attributes.  This should add the data into the Values section. Sometimes the fields are automatically added to columns - if this happens you’ll need to move them to the Values Section and move 'Value' into the Rows section.

    TIP: If you have a large number of attributes on your system, this may take a while. Follow steps 5 - 8 to apply a Macro to automate this process for you. Otherwise, skip to step 9.

  5. Press Alt + F11 which will open up Microsoft Visual Basic for Applications in a new Window.
  6. Go to Insert > Module. This will open a blank sheet for you where you will copy and paste the following code.
    Sub AddAllFieldsValues()

    Dim pt As PivotTable

    Dim iCol As Long

    Dim iColEnd As Long

    Set pt = ActiveSheet.PivotTables(1)

    With pt

     iCol = 1

     iColEnd = .PivotFields.Count

     For iCol = 1 To iColEnd

     With .PivotFields(iCol)

     If .Orientation = 0 Then

     .Orientation = xlDataField

    End If

    End With

    Next iCol

    End With

    End Sub
  7. Click the Save icon. Go back to Excel and your Pivot table sheet, select View > Macros > AddAllFieldsValues > Run. This will add all of the Attributes to the pivot table. If there is a lot of data this might take a few seconds to add all of the fields.
  8. Move the Values metric into the Rows field.
  9. Once all of the values have been added, you may want to sort the data to find unique responses. To do this add a new sheet and in the top left cell write the Expression =UNIQUE(select the expression UNIQUE).
  10. Return to the Customer List sheet, select the first column that is not the Customer Id and select the column, then close off the expression by adding a closing bracket.  For example =UNIQUE('CustomerList - 2022-08-04T14584'!B:D)
  11. Finally, select the cell with the expression in it and drag it along the top to select all of the cells on the other sheet. Sometimes dragging the cells across just copies the header, if this is the case you can cut and paste the top cell over. Any results returned as 0 means there is not a unique response in that field. 

Now you can see any unique Attributes on your system and consider if any are unused, could be made into tags, could turn free text into dropdown values. Make a note of the analysis on your System Health Checklist.

 

How to delete or edit Attributes

Once you have done an analysis of your Attributes, you may want to delete or edit them.

WARNING: Deleting an Attribute Definition or Value is an irreversible action. Only delete an Attribute Definition or Value from your system if you are sure you will not want to use it or Report on it again in the future. Be sure these Attributes are removed from any Customer Lists or schedules, as they will cause errors when running these Lists.

To delete an Attribute from your system, go to the Settings Interface > Attribute Definitions. Within each Attribute Definition, you can choose to edit or remove the Attribute Values within. You can delete Attribute Definitions by clicking the grey trash can symbol.

 

trash can icon.png

 

You may wish to edit your Attributes so that they are more efficient. For example, if you are receiving a number of similar free text responses, you might want to amend that to a drop down selection. Read more about creating and editing Attributes.

 

Reports

Next, we will analyse the Reports you have on your system. 

The video below details the process, with written steps below where you can follow along.

  1. Navigate to the Report Explorer page and right click > View Page Source.
  2. Ctrl+A to select all the text and then Copy.
  3. Open a new blank workbook in Excel.
  4. Right click Paste Special and select Text.
  5. Delete all rows from the top until the list of Reports begins, then delete the first blank column.
  6. Copy all the data (Report name, Report Type and Date last run). Create a new sheet and right click > paste Values.
  7. Expand columns/rows to make the data more clear and readable. Delete any blank rows.
  8. Select the date column and format into date format (06/02/2023). This will be day/month/year for UK and Ireland users and month/day/year for US and Canada users.
  9. Highlight the top row and select Data > Filter.
  10. Select all the data and go to Data > Sort.
  11. Select Column C and set the Order column from oldest to newest.
  12. You can then Filter the Date Last run column to highlight only the blank fields to show you Reports which have never been run. You can also select date ranges to see Reports which have not been ran in the last few years.

TIP: If you just want to see Reports that have been run, you can filter the Reports in the Report Explorer by Date last run.

Going through this process should allow you to see any duplication and any Reports which haven’t been used in a long time. Make a note of the analysis on your System Health Checklist.

 

How to delete Reports

Once you have done an analysis of your Reports, you may want to delete Reports no longer in use. While it is not possible to delete Standard Reports, it is possible to remove any Custom Reports that you no longer use.

WARNING: Deleting a Custom Report is irreversible action. Only delete a Custom Report from your system if you are sure you will not want to use it again in the future.

To delete a Custom Report, go to the Report Explorer. Select the plus button next to the Report you want to delete to expand the details. Then, select the Delete Report button then Confirm.

 

 

Customer Lists

Next, we will analyse the Customer Lists you have on your system. 

The video below details the process, with written steps below where you can follow along.

  1. Navigate to the Customer List Explorer and right click > View Page Source.
  2. Ctrl+A to select all the text and then Copy.
  3. Open a new blank workbook in Excel.
  4. Right click Paste Special and select Text.
  5. Delete all rows from the top until the list of Customer lists begins and then delete the last column.
  6. Copy all the data (Customer List name and Date last modified). Create a new sheet and right click Paste Special and choose Values.
  7. Expand columns/rows to make the data more clear and readable. Select the date column and format into date format (06/02/2023. This will be day/month/year for UK and Ireland users and month/day/year for US and Canada users.
  8. Highlight the top row and select Data > Filter.
  9. Select all the data and go to Data > Sort.
  10. Sort by the Date modified column from oldest to newest.
  11. You can then filter the Date Last Modified column to show you Customer Lists which have been modified since certain dates.

Going through this process should allow you to see any duplication and any Customer Lists which haven’t been used in a long time. Make a note of the analysis on your System Health Checklist.

 

How to delete Customer Lists

Once you have done an analysis of your Customer Lists, you may want to delete those no longer in use.

WARNING: Before deleting any Customer Lists it’s important to check whether they are in use in Customer List schedules - date last modified will only indicate if they have been edited recently so they may still be in use.

You can delete any Customer Lists that are no longer needed by going to the Customer List Explorer and clicking the red cross icon next to the selected Customer List.

 

 

Offers & Promo Codes

Next, we will analyse the Offers and Promo Codes you have on your system. 

Follow the steps below.

  1. Navigate to Reports > Spektrix > System Efficiencies. Search for a Report called Offers and Promo Codes. 

    - REMINDER: This is a Custom Report which you’ll need to request from our team. To do this, fill in the Report Request form.

  2. Run this report and output it as an Excel file.
  3. Open the Report in Excel. Filter this Report to see a breakdown of all Offers used, ticket use count, first use date and last use date. You can also view this for each promo code, their number of uses and their first and last use dates.

This Report allows you to see any duplication and any Offers or Promo codes which haven’t been used in a long time. Make a note of the analysis on your System Health Checklist.

 

How to deactivate Offers and Promo Codes

Once you have done an analysis of your Offers or Promo Codes, you may want to remove or edit them.

WARNING: Before deactivating any Offers or Promo Code it’s important to check that they are no longer in use. Any historical purchases made using Offers or Promo Codes can still be reported.

 

Offers

Navigate to the Admin Interface > Other > Offers. Select the Offer you wish to make inactive. Click Edit to make changes. On the Basic Details screen, uncheck the box marked Active? and Save.

 

 

Promotion Codes

Navigate to the Admin Interface > Other > Promotion Codes. Select the Promotion Code you wish to make inactive. Click Edit to make changes. On the Basic Details screen, uncheck the box marked Active? and Save.

 

 

Seating Plans

Next, we will analyse the Seating Plans you have on your system. 

Follow the steps below.

  1. Navigate to Reports > Spektrix > System Efficiencies. 
  2. Search for and run three Reports: Seating Plan Use, Seating Plan and Price List Usage Report and Seating Plan and Price Usage Report - (Including Ticket Types) and download them as Excel files.

These Reports allow you to review all of the Seating Plans on your system and the first and latest Instances dates used. Make a note of the analysis on your System Health Checklist.

  • Not currently in use means that the Seating Plan / Price List is not used in any future Events.

 

How to delete Seating Plans

Once you have done an analysis of your Seating Plans, you may want to delete those no longer in use.

WARNING: Before deleting any Seating Plans it’s important to check that they are no longer in use or needed. Deleted Seating Plan data will still be available for Reports for past Instances.

To delete Seating Plans that you no longer need, navigate to the Admin Interface > Seating > Seating Plans. 

Select the Delete button next to the Seating Plan you wish to delete. A new window will appear to ask if you are sure you want to delete this Seating Plan. Select Confirm to delete the Seating Plan.

 

Price Lists

Next, we will analyse the Price Lists you have on your system.

Follow the steps below.

  1. Navigate to Reports > Spektrix > System Efficiencies.
  2. Search for and run the Price List Usage. Download the Report as an Excel file. 

This Report contains a breakdown of all Price lists in use, their first and latest Instances dates used, the most recent event used against and the use count. Make a note of the analysis on your System Health Checklist.

 

How to delete Price Lists

Once you have done an analysis of your Price Lists, you may want to delete or edit them.

WARNING: Clicking the Delete button will immediately remove your Price List. Before deleting any Price Lists it’s important to check that they are no longer needed. Any Instances with the Price List will keep the values of their Price Lists intact but the name will change to show it as a custom Price List. Deleted Price List data will still be available for past Instances.

To delete Price Lists that you no longer need, navigate to the Admin Interface > Pricing > Price Lists.

Select the Delete button next to the Seating Plan you wish to remove. 

 

Mailing Categories

Next, we will analyse the Mailing Categories you have on your system. 

To see all of the Mailing Categories on your system go to Insights & Mailings Interface > Mailings > Mailings Settings and find them under Custom Mailing Categories.

To analyse their usage, follow the steps below.

  1. Navigate to Reports > Spektrix > System Efficiencies.
  2. Search for and run the Mailing Category Check. Download the Report as an Excel file. 

REMINDER: This Report will only return Mailing Categories which have been set with a Targeted Event set against them.

This will give you an unformatted report that outputs the Mailing categories and their use. 

Make a note of the analysis on your System Health Checklist.

TIP: For Mailing Categories that have already been used, it’s not possible to delete these yourself but you can request that our support team assist you with this.

 

 

How to delete Mailing Categories

Once you have done an analysis of your Mailing Categories, you may want to delete or edit any that you have not already used.

WARNING: Before deleting any Mailing Categories it’s important to check that they are no longer needed and that you won’t need to Report on them again in the future.

To delete any Custom Mailing Categories that you no longer need, navigate to the Insights & Mailings Interface > Mailings > Mailings Settings.

Select the red x button next to the Mailing Category you wish to remove. 

 

REMINDER: You can only delete Mailing Categories which you have not ever used.

 

Ticket Types

Next, we will analyse the Ticket Types you have on your system.

Follow the steps below.

  1. Navigate to Reports > Spektrix > System Efficiencies.
  2. Search for and run the Ticket Type Use (Estimate). Download the Report as an Excel file. 

This Report will provide information on Ticket Types that have been sold per Event. 

This will not help you analyse Ticket Types which have not been used.

If you do wish to analyse all of the Ticket Types on your system, follow the steps outlined below. Then, cross reference with the results from the Ticket Type Use (Estimate) report to find those which are not in use. Make a note of the analysis on your System Health Checklist.

    1. Navigate to the Admin Interface > Pricing > Ticket Types.
    2. Uncheck the box marked View inactive? to view all of the Ticket Types.
    3. Right click on the page > View Page Source.
    4. Ctrl+A to select all the text and then Copy.
    5. Open a new blank workbook in Excel.
    6. Right click, choose Paste Special and select Text.
    7. Delete all rows from the top until the list of Ticket Types begins and then delete the last column.

Going through this process should allow you to see any duplication and any Customer Lists which haven’t been used in a long time. Make a note of the analysis on your System Health Checklist.

 

How to deactivate Ticket Types

WARNING: Before making any Ticket Types inactive, it’s important to check that they are no longer needed. Any historical purchases made with these Ticket Types can still be reported.

To deactivate any Ticket Types that you no longer need, navigate to the Admin Interface > Pricing > Ticket Types. Select the Edit button next to the Ticket Type you wish to deactivate. Then, uncheck the box marked Active and Save.

 

Event & Instance Attributes

Next, we will analyse the Event & Instance Attributes you have on your system.

Follow the steps below.

  1. Navigate to Reports > Spektrix > System Efficiencies.
  2. Search for and run two Reports - Event Attribute List and Instance Attribute List. Download the Reports as Unformatted Excel files. 

These are unformatted Reports that give you a list of all Event or Instance Attributes on your system. Take a look at these Reports and sum each column to see how many times each Attribute is in use. Make a note of the analysis on your System Health Checklist.

TIP: Follow the steps in How to remove or edit Attributes to remove any Event or Instance Attributes that you no longer need.

 

Duplicate Accounts

Next, we will analyse Potential Duplicate accounts you have on your system.

Once you have completed this, be sure to make a note of the analysis on your System Health Checklist.

 

Potential Bot Accounts

Finally, we’ll need to identify Potential Bot Accounts in your system.

Bot accounts are generally created via the simple sign-up form. They also generally have no account history, were created by a web user, and have opted in to contact by email and nothing else. 

We recommend following these steps on a regular basis to keep on top of potential bot accounts. 

To identify potential bot accounts, you’ll need to create a series of Customer Lists with the following segmentation criteria:

  1. Customer > Never Booked.
  2. Using the AND criteria, add Purchasing > Order Items > Date transaction confirmed > Relative Date Range > First Recorded date to end of time. Check the Not box to exclude the customers in this Segment. 
  3. Using the AND criteria add Mailing > Sent an email and never responded
  4. Using the AND criteria add Customer > Date account created > More than a year (to exclude new sign ups who haven’t had a chance to book).
  5. AND Is Active

Run this list and check through the Customers to find any real people. For any Potential Bot Accounts you find and would like to remove, you’ll need to send us a Customer List with the output columns SpektrixID and IsActive. 

TIP: You can use a Customer List Schedule to automate the delivery of this list to your team so that you can keep on top of checking for bot accounts.

You can use learnings from this exercise to inform a re-engagement campaign, exclude these contacts from future mailings or deactivate inactive contacts. If you have over 50 customers you want to deactivate, get in touch with our Support team and we can do this in bulk for you. 

 

Next steps

Now you have completed your System Health Checklist the next step is to go through the Results page, make improvements and get back in touch with one of our Consultants who will work with you to help clean your data.

In the meantime, if you’d like to keep learning about how to make more efficiencies on your system and clean your data then we recommend the following articles: