Historical data import

Historical data import

Some clients would like us import their historical data from other third parties they have used in the past. This could be from an old survey or mystery visit. We can do these but more often than not, we will need to manipulate the data into a format we can import it.

Firstly, we need to ask the client to send us their data export - this is usually (and best) in Excel format. These can look quite complex and have a lot of data in them so we need to break it down into fields we recognise and ensure we are choosing the data that will be most valuable to them.

Alert
In some cases, we can't import all the clients data so we need to understand where the value is and give some continuity of reporting in these areas.

What we need to do:
  1. Understand the different columns and language used - sometimes on a client export there will be language or code that we don't understand so we will need to ask the client so we can be clear on what we are importing. We can then make a decision if this will be valuable for them to import.
  2. Create a new questionnaire - we need to ensure there is a questionnaire to map the data to. In most cases, it is best to create a new questionnaire that only contains the relevant questions you want to pull in from the export. Although, if the survey we set up is the same as their previous one, we will not need to do this.
  3. Create a new spreadsheet so we can copy and paste the valuable data into the fields that we need. There is a template for this in the implementation share point but the key fields we need are:
    1. Diner ID - this will be the dummy diner 60660.
    2. Visit ID - this will need to be unique and is sometimes already included on the export. However, if it's not, we will need to make one up. This can include the client ID.
    3. Visit Date
    4. Branch ID - this is the branch ID on our system.
    5. Branch Name
    6. Question ID - this will be the question ID on our system that we are mapping the data to.
    7. Answer - what the answer is on the client export.
      1. We can convert some answer scores into scales we use, if necessary. For example, if they rate NPS (or something similar) out of 5, we can double the results to convert to a scale of 1 - 10.
      2. Some people may have NPS on a scale of 0 - 10. In these instances we can make 0's into 1's.
    8. Weight - what the max weight is on the question.
    9. Score - what the question score is on the export.
    10. Comment - any comments we can include from the client export.
  4. Copy and paste the relevant data columns from the export into our new spreadsheet. You must paste values to be clear with formatting.
  5. Each row should have the complete visit details i.e. include every question we are importing.
Example below:





Repeat the question fields as appropriate. 

Idea
Here is a handy Excel formula for when comments may be across different columns and you want to merge them - =conc (CONCATENATE(select tab,  “ “, select tab, " ", select tab)  - this will take contents of the first cell you select, have a space, contents of second cell, have a space etc. You can then run this down the columns and it'll merge all the comments.

Ticket for dev:
Once you have completed the new import spreadsheet, you will need to add a ticket for Dev to import it. In the ticket you will need to include:
  1. Client name and ID
  2. Questionnaire ID
  3. Question IDs - usually included in the spreadsheet
  4. What years the spreadsheet covers.


    • Related Articles

    • Reviews Import Times

      Reviews are imported into the Hub at set times of the day. For all sources on ReviewTrackers, the import runs 5 times a day collecting reviews left in the last 24 hours: 5:15am 9:15am 1:45pm 5:15pm 9:15pm For clients that are using an S3 Bucket for ...

    • Exporting ShopMetrics Data from Partner Platform

    • Menu Imports/Updates

      All capabilities and processes of the Menus and Products pages on the Hive. Viewing a Menu - Click on the menu name in the Hive to view a menu version. - This will show you the menu details, including code, start date and end date. You also have the ...

    • Client Terminations

      1. If a Small Client, add a change form and change client status to ‘Offboarding’ following the steps required. 2. If they have visits, check for any visits scheduled after the agreed end date for services. 3. Email diners and cancel visits, ensuring ...

    • Testing Partner Imports

      For partners Second To None and Onion Insights, we are unable to test these ourselves and a ticket to the Product will be required to test these imports. For all other partners that use ShopMetrics: 1. Ensure you have the following Dotnet software ...