Importing customer data into Spaaza
Spaaza provides the functionality necessary to create new customer profiles that provide a unified view of that customer across channels. However sometimes our clients may also need to import existing customer data into Spaaza and convert these into Spaaza profiles or merge them into existing profiles. This may include members from a legacy loyalty program, CRM database, or webshop or identity management system accounts.
This document provides an overview of the common import cases and what needs to be considered for each.
In most cases importing customer data requires professional services support from Spaaza’s Technical Support Team and is not considered as part of our standard offering. The amount of work required will vary based on the type of data that needs to be imported and the quality of that data.
2. Importing new webshop or identity system accounts (created after Spaaza is implemented)
In most cases if a customer signs up for the loyalty program they will need to create an account authenticated by the webshop or identity system. As a result every Spaaza customer profile has a connected webshop/identity system account and every webshop/identity system account created has an equivalent profile in Spaaza. There accounts are kept in sync so that as data is changed in one system it is updated in real-time in the other.
3. Importing existing webshop or identity system accounts (created before the Spaaza integration is live)
In the case of existing accounts that were created before Spaaza was implemented there are generally two options: import the customer account into Spaaza when the customer first interacts with the program; or importing customer accounts in bulk, usually done just before Spaaza is launched.
3.1 Importing the customer account into Spaaza when the customer first interacts with the program
There are various ways in which an existing webshop account could be connected to a Spaaza profile, depending on the program touch-points. There are the most common we see:
3.1.1 Opt-in to the program on the webshop or identity system
When customer’s opt-in to the loyalty program on the webshop a profile is created in Spaaza (if it doesn’t already exist). From that point on the Spaaza profile and webshop accounts are kept in sync.
3.1.2 Staff member attempts to create a profile for the customer in Spaaza’s store interface or POS using Spaaza’s API
If a staff member attempts to create an account for a customer that already has a webshop account then Spaaza will create a new profile for that customer. From that point on the Spaaza profile and webshop accounts are kept in sync. Note that giving staff the ability to search for existing customer accounts in store via Spaaza’s Store interface is one reason why importing existing webshop accounts in bulk may be useful (see below).
3.1.3 Customer logs into the Spaaza loyalty app for the first time
The first time that a customer tries to login using Spaaza’s app for iOS or Android with a valid webshop username (email address) and password a profile will be created in Spaaza. From that point on the Spaaza profile and webshop accounts are kept in sync.
Should another app be used then the app can also be configured to work in the same way or alternatively only create the Spaaza profile at a later stage, say for example if the customer opt-in to the loyalty program via the app.
3.2 Importing webshop or identity system account data into Spaaza in bulk
Spaaza has a bulk import tool which can be used to import accounts into Spaaza. This is often used by clients who want to have all of their existing customers available in the Spaaza’s Console and Store apps. This may be useful so that head office staff can segment on this data and store staff can search for an existing customer when they come into store before attempting to create an account that might already exist.
To import webshop accounts in bulk Spaaza requires the data to be provided to us in a CSV file. The format for this file is described at the end of this document.
4. Importing legacy loyalty program data into Spaaza
Spaaza’s solution will often be implemented at clients who may have run a loyalty program in the past. In some cases clients may require that their customers have their points transferred into Spaaza together with any profile data. A common challenge with “legacy” loyalty program data that we encounter is that the data quality is often quite poor with missing data and also mistakes in fields like email addresses. We often find duplicate accounts for customers as a result. Because of this some data cleanup is often required.
Once cleaned there are typically two ways in which this data can be imported, depending on whether a valid email address is present or not.
4.1 Legacy loyalty accounts with a valid email address
Spaaza has a bulk import tool which can be used to import legacy members into Spaaza.
In most cases Spaaza requires a webshop account to be setup and connected to a profile. As a result the default behaviour is that when the legacy account is imported in Spaaza a webshop account is also created. Spaaza will not set the password for the account. Normally an email will be sent to the customers who were imported to set their password on their new account.
If a customer has points, vouchers or some existing status (Silver, Gold etc) then these can also be imported however how this is done depends on the new program rules in Spaaza and often requires some custom work.
To import legacy loyalty program profiles in bulk Spaaza requires the data to be provided to us in a CSV file. The format for this file is described at the end of this document.
4.2. Legacy loyalty accounts without a valid email address
In order to create a profile in Spaaza a valid email address is required. We often see legacy loyalty data where there is no email address for the customer.
Should you wish to import this data then the recommended route is to allow either staff or the customer (or both) to link a legacy profile with a new Spaaza profile that has been created. For example in a webshop this might work as follows:
- Customer creates a new webshop/identity system account and opts in to the loyalty program
- Customer visits My Account page and is prompted to link an existing loyalty account
- Customer adds two separate pieces of data (e.g. Last name and legacy Member Number) that will allow Spaaza to find their legacy profile and know with some degree of certainty that the match is valid (this helps to stop people from entering in a member number that is not theirs for example)
- Customer clicks or taps a button and the accounts are merged and any points are transferred. Clients may wish to incentivise customers to merge accounts and in cases like these additional points or vouchers may be added to the unified account as a result of merging.
Spaaza has built some tools which can be used to complete the flow above however these tools are not provided as standard and will likely require setup and some customisation for the retailer’s legacy data.
These migration tools are also not included by default in Spaaza’s apps iOS and Android apps or in Spaaza’s Console, Store interface or webshop extensions at this time. These can be made available but will require additional setup work from both Spaaza and the webshop agency in this example above.
5. Importing transaction history into Spaaza
Currently there is no generic tool to import transaction history into Spaaza and this needs to be approached on a case-by-case basis where a customer transaction importer needs to be created for each customer.
Note that transaction history can only be imported once the customer exists in Spaaza and thus is typically done after a bulk import. For existing webshop accounts holders whose Spaaza profile is created when the opt-in to the program or signup for the first time transaction history import needs to be done manually by Spaaza and does not happen in real time. For example once built the importer can be run every week so that the profiles created earlier that week are then linked to their transaction history.
6. Format for CSV for bulk import of customers with email address
For any bulk import of customers into Spaaza the file supplied needs to be a valid CSV file with the following properties:
- Filename suffix .csv
- File format: unix
- Character encoding: utf-8
- Header row
- All text fields properly quoted
- Blank fields left empty
An example file is available here.
The field names to be included for each customer are the same as in the add-user endpoint, with the addition of the
store_id fields, and are as follows:
- user_id: the Spaaza user ID. In most cases this is not know at the time of import so can be left empty. By arrangement this field can also contain a member or customer number in an existing loyalty programme.
- chain_id: (required) the ID of the retailer/brand chain in the Spaaza ecosystem.
- username: (required) the email address of the member. Email address must be correctly formatted in conformance with RFC5322.
- authentication_point_identifier: the identifier of the member on the customer’s webshop or remote Identity system (if they have an account on that system already).
- programme_opted_in: whether the customer is opted in to the programme or not. Possible values are 0 (false) and 1 (true). If this value is empty the default value of 0 (false) is used.
- first_name: (required): the first name of the customer.
- last_name: the last name of the customer.
- gender: the gender of the customer. Possible values are “F” for female and “M” for male.
- birthday: the birth date of the customer. This should be in the format YYYY-MM-DD.
- country_code: the ISO ALPHA-2 two-letter country code of the customer, e.g. “NL”.
- address_streetname: the street name of the customer.
- address_housenumber: the house number or name associated with the address. This value is a string so either a number or building name can be used.
- address_housenumber_extension: any extension to the house number such as apartment number.
- address_line_2: any second line for the address, such as the name of a neighbourhood.
- address_line_3: any third line for the address, such as the name of a village or sub-town.
- address_towncity: the postal town or city of the address.
- address_regionstate: the region, province or state of the address.
- address_postalcode: the postal code of the address. This field is a string field, with the postal codes for the following countries being validated strictly:
- Belgium (BE): 4 digits followed by an optional “-BE”. The first digit must be between 1-9 and the last 3 digits between 0-9.
- Germany (DE): An optional “D-“ followed by 5 digits between 0-9.
- Netherlands (NL): 4 digits followed by an optional space, followed by two letters. The first digit must be between 1-9 and the last 3 digits between 0-9.
- store_id: the store in which the customer signed up. This would be the retailer’s store or branch code. This field is known as branch_business_owner_code and business_owner_code in Spaaza API endpoints.
- mailing_list_sub_offered: whether the customer was offered to subscribe to the mailing list. Possible values are 0 (false) and 1 (true). If this value is empty the default value of 0 (false) is used.
- mailing_list_subscribed: whether the customer has opted in to receive emails. Possible values are 0 (false) and 1 (true). If this value is empty the default value of 0 (false) is used.
- printed_mailing_list_subscribed: whether the customer has opted in to receive direct mail by post. Possible values are 0 (false) and 1 (true). If this value is empty the default value of 0 (false) is used.
- secondary_email: any secondary email address of the member. Email address must be correctly formatted in conformance with RFC5322.
- status_membership: whether the customer was a special member of any previous loyalty programme. Possible values are 0 (false) and 1 (true). If this value is empty the default value of 0 (false) is used. This field is used mostly as a custom field for applying “VIP” or other specific status from a previous loyalty programme to the new customer record in Spaaza.
- phone_number: a phone number for a customer. This field is a string so any format can be used including plus (“+”) and parentheses.
- revenue: a previous revenue or points value for a customer. This field is a float value rounded to a maximum of two decimal places with a decimal point (not comma) and no other separators. The field is used mostly as a custom field for applying previous revenue value of a customer to the Spaaza programme and assigning points or status on that value. This field can be treated as a currency value or as a points value in a points wallet programme or campaign. This field can work separately from importing previous transaction history if that feature has been used.
- wallet_total: a points or wallet total in a previous programme, which is to be imported into the Spaaza programme. This field is a float value rounded to a maximum of two decimal places with a decimal point (not comma) and no other separators.
Common problems seen with CSV customer import files:
- Text fields are “double double quoted” meaning that instead of each quoted text field being surrounded by one set of double quotes, it is in fact surrounded by two sets. This is a common problem when exporting customers from Microsoft Excel - sometimes it can work better to open the Excel file in LibreOffice and use the “Save As -> CSV” functionality (and filters in that programme) instead. This problem can be detected by opening the CSV file in Notepad (Windows), TextEdit (Mac) or Vim (Unix/Linux).
- Correct: “Jan”, “Jansen”
- Incorrect: ““Jan””,”“Jansen””
- Email addresses in incorrect format. We often receive email addresses with incomplete domain names (“@com”) or where the first part of the email address ends in a dot (“john.@example.com”) which do not adhere to our RFC5322 validation.
- Numeric or currency values with comma decimal separators instead of decimal points. This most often occurs when a CSV file has been exported from Microsoft Excel where a comma is used as a separator by default.
- Numeric or currency values with thousand and millions separators. This most often occurs when a CSV file has been exported from Microsoft Excel where a currency or number format contains a separator.
- Birthday values not in YYYY-MM-DD format. This most often occurs when a CSV file has been exported from Microsoft Excel and the birthday column uses a different date format.