When dealing with a larger file of users, it’s not feasible to add them manually one by one. This is where the Bulk user import comes into play and in this article we will cover all the bases in order to ensure your imports will be successful every time.
Before we begin #
When dealing with user imports in Releva, each file you upload needs to follow precise column arrangement in order to be successful.
In order to know the required order of columns and data, you can use the template below as a sort of a cheat sheet:
Once you have downloaded the template, let’s continue to the next step.
The required file extension for the bulk user import is .csv and potentially you can prepare it in any software you have or want to use. Still, we have noticed that software such as Microsoft Office might not necessarily export the data format or phone number format properly into .csv, thus we highly recommend you to use Google Drive and its Sheets alternative when preparing your users for upload.
In our many years of experience now, we have noticed it produces the most consistent results and works without an issue but feel free to try an application of your choosing as well and see which one will produce the most reliable outcome for you.
For the purpose of this guide, we will cover the process in Google Drive via Google Sheets.
Column expectations #
Let’s quickly check the required order in which user files need to be uploaded:
| First name | Last name | Email* | Phone number | Registration date | Subscribe date | Subscriptions | Tags |
|---|---|---|---|---|---|---|---|
| Example | Example | Example | +35935988888888 | 2024-08-16 | 2024-08-16 | newsletter,house | import_list,11_March_2024,bay_house |
- First name – as the name suggest, this is the First name of the user. It can be left blank if not available.
- Last name – as the name suggest, this is the Last name / Surname of the user. It can be left blank if not available.
- Email – this is the email of the user. This is one of the two mandatory fields which should be available for a user.
- Phone number – this is the phone number of the user. This is one of the two mandatory fields which should be available for a user.
- Registration date – if you wish and have this information, you can add the date when the user registered on your website. The only accepted date format is YYYY-MM-DD
- Subscribe date – this is the column with which you will mark if a user has given consent to receive Marketing emails or you will only be able to send them Transactional ones (Abandoned Cart, Browse etc. in short – informational emails). The only accepted date format is YYYY-MM-DD
- Subscriptions – this can be used for really specific scenarios to create Segments so you can skip this.
- Tags – this is where you would be able to place tags so you can easily create different types of segments in order to retarget them using automations or campaigns.
Document preparation #
Let’s start by making a folder in Google Drive and uploading your user export file alongside with the Releva import template you downloaded earlier. This will ensure all of the data is neatly in one place and easy to find if needed at some point. At the end, it should looks similar to this:
Editing time #
When ready, double-click on your user export file and in some cases, the screen you will see is this:
If that happens, click on the button at the top center ‘Open with’ and from its options select Google Sheets.
This action will make a copy of the original file and open a new tab with your file waiting for it to be edited.
The format of the file can me similar to the one below but it can also be quite different.
Template file #
Now, switch to the previous tab where your folder in Google Drive is located and double-click on the Releva Template file.
Select both row 1 and row 2 alongside with each column from A to H included as shown on the Gif below. When selected, make a selection with right click and Copy from the context menu or hit Ctrl + C / Cmd + C:
With the template data copied, move again to the tab containing your opened file with users.
Start by creating two empty rows above all of your data and use Ctrl + V or Cmd + V to paste the copied data from the Template file as shown below:
Now, you can begin by moving the data around until everything is in its corresponding column.
You may have some columns with data like physical address, country, IP address, number of orders and more. That data will not be necessary and you can safely delete it after you check the required data in row one.
After you have moved the data around and deleted any unnecessary columns, if should look similar to this:
Checking the emails #
The email is one of the two mandatory fields that are required for import in Releva,
A row of user needs to have either a valid email or a valid phone number in order to be successfully imported, so let’s begin!
One of the first things we will do is check the emails to ensure they are properly written. It will be in your best interest to ensure the majority (if not even all) emails you import into Releva are real and not some bogus or filled with typos.
It’s true that you can’t guarantee there’s not a typo in the first part of the email but you can definitely check and ensure the email domain is correctly written.
To do that, make a new column on the right side of the emails:
With that empty column created, we will now use it for a formula which will help us identify potential broken or incorrectly written domains: =isemail(cell number). For example: =isemail(c4)
There might be similar formulas for the other office programs so do make sure to research it if you decide to use an alternative to Google Sheets.
Now, let’s put the formula to the test and see the results.
We begin by selecting the empty cell parallel to the first email and enter =isemail(C4). When you hit enter you should see the little pop-up to Auto Fill the rest of the cells with the formula so you can agree to that. In the event you do not get a pop-up to apply the formula to the bottom, you can mark the cell we just filled, click on its bottom right corner and drag down. This will apply the formula to the rest of the cells.
Please note, this formula will only look and check the domain extension and if it exists.
Correcting the wrong email #
Make sure to select the whole column with the results, go to menu ‘Data’ → select ‘Create a Filter’ → click on the filter itself and remove the checkmarks on ‘(Blanks)’ and ‘TRUE’, then click on the OK button to confirm:
This will leave you with all emails that may require your attention so do expand the column with the emails sideways and check each of them.
In this particular example, the emails have one extra ‘e’ at the end, or an ‘n’ or ‘g’ instead of ‘m’. In the case of .eu domains, these always end up as FALSE so you can leave them as they are.
Once cleaned up, the formula will change in real time that the domain extension is correct:
Still, even if the domain extension seems okay, there might be a problem with the domain itself. It’s very common for users to misspell gmail.com with:
- gmai.com
- gmal.com
- gemail.com etc.
Such errors can be by mistake but also can be made on purpose so you can’t send them marketing communication although they have agreed to receive such.
You can easily find such mistakes and correct them by performing a search:
Performing a Search #
Select the column with the emails and press Ctrl+ F or Cmd + F. Then, instead of writing right away, click on the three dots on the side pop-up to expand the search.
What you can do here is write the search keyword and even type the correct domain it should be replaced with. For example:
- Find – @gmai.com
- Replace with – @gmail.com
It’s highly recommended to first click Find and then Replace or Replace all.
Keep in mind:
It’s important to be very careful with this feature and make sure you’re sure about the change. In the case you are working with a large file, reverting the replacement can take quite a bit of time. Because of that, always use this feature with care and caution.
There are numerous spelling error that can be done with emails so do take a close look and try to fix as many of them as possible as you will benefit from this at the end. The more correct emails are imported into Releva, the higher the number of Marketing and Retargeting emails will reach your users and not bounce because of an invalid email, so do take your time.
Remove the column with the formula #
After you have made the correction where you saw fit, you can select the whole column with the formulas and delete it as it has served its purpose:
Checking the phone numbers #
The phone number is one of the two mandatory fields that are required for an import in Releva,
A row of user needs to have either a valid email or a valid phone number in order to be successfully imported, so let’s begin!
Please note, even though the cell might visualise the correct format, it’s recommended to click on it and view the actual value shown below the menu.
In order to be considered a valid number, the format needs to include the country code with a + in front and no spaces need to be present in its true value (not the one visualized in the cell). Here’s an example:
- Incorrect – +359 88 888 8888
- Correct – +359888888888
Simply typing +359 in front though will result in this + to be considered a = sign when exported thus, we need to handle this with another formula. Let’s check our example and see how we can prepare them.
Using a formula #
As you can see in the below example, at the moment the phone number column and data does not comply with the expected format so we will use another formula to fix this. The formula in question is called =concat() and we will use it to add +359 (or you can add any other country code in front of the mobile number). Other Office software might have a different version of this formula, so if you’re using an alternative software do research what would work in your case.
To continue forward, first we need to add a new column on either side of the phone numbers, and for the purpose of this document I’ll place it on the right side:
Now, let’s utilize the formula. We begin by selecting the empty cell parallel to the first phone number and enter =concat(“+359“,D4) (In your case both the country code and cell can be different).
When you hit enter you should see a little pop-up to Auto Fill the rest of the cells with the formula so you can agree to that. In the event you do not get a pop-up to apply the formula to the bottom (as shown in the .Gif below), you can mark the cell where we wrote the formula, click on the dot in its bottom right corner and drag down. This will apply the formula to the rest of the cells.
Copy and paste the new phone numbers #
Now that we have the correct format, all we need to do is place them in the right column but for that purpose, we need to first remove the formula and leave it as plane text.
We begin by first selecting all cells containing the formula, making a copy with Ctrl + C, Cmd + C or right click and selecting Copy from the context menu. Then we select the first row of the original phone numbers where we right click, hover over the ‘Paste special’ option and select ‘Values only’.
This is an important step in the preparation of the phone numbers. Make sure to not skip it, otherwise the import will not be a successful one.
Remove the column with the formula #
After the phone numbers have been pasted as Values only, you can select the whole column with the formulas and delete it as it has served its purpose:
Registration and Subscription Date #
Those two columns will be covered in a single chapter as both have the same requirement: the date needs to follow the format YYYY-MM-DD.
As their names suggest, one will serve as Registration date and the other indication when the client has agreed to receive Marketing emails. If that particular client has not given consent, you can leave the cell blank. Same goes for the Registration date if you don’t have it or it’s not needed.
Please note, even though the cell might show the required date format, it might only be visual while the true value is completely different. Here’s an example:
Here’s how a correctly written date looks:
Now that you know what the correct date format is, fill in the cells as necessary and let’s move to the next column.
Utilizing the Tags #
The tags is what will allow you to easily segment users by something else other than their behavior and interaction with your website or marketing content sent via Releva.
In other words, this is what will allow you to place something custom for each imported user and use that custom information at a later point for retargeting purposes.
When writing your Tags, they need to be consistent (if you have used them before, they need to be exactly the same as the previous time).
For example, if you have imported users with tag regular_user before, you need to use that exact same tag again if you want to add more people to the segment where it’s being used. That’s because tags are case sensitive.
You can add multiple tags to a user and each of them needs to be separated by a single comma symbol (you can check the screenshot below). No spaces between or before the comma should be placed. Let’s see an example of some tags.
In this example, we have used the following tags: order_above50 / regular_user / premium_user and some are left blank. As you can see, one of the user has multiple tags all separated by a comma symbol and nothing else. When imported, this user will receive all three tags in its profile in Releva and if you create a Segment with either, this user will be present there.
Once again, please remember that tags are case sensitive and if you want to use a tag you have used previously, you need to enter the exact value as used before.
Removing unnecessary rows #
One of the final steps is to make sure the user data begins from Row 2. Row 1 can be left as is, containing the titles from the template file or completely blank as this won’t affect the outcome from the import.
In our example, Row 2 and 3 contain unnecessary information thus we select and delete them. What we are left is Row 1 and the rest is filled with the user data ready for import.
Please note, it’s important to make sure there’s at least one row above your user data as Row 1 is skipped in the import. If there is user data in row 1, that won’t be imported to Releva.
Empty rows #
In the case you may have users with neither an email or a phone number, you will have to delete that row as if left, all users after it will be skipped. If enough rows like that are left, the import will not be successful. Same goes for empty rows in between your users – those need to be deleted.
Exporting the file #
Once you have done all necessary changes and updates to the file to prepare it for upload, it’s time to download it.
When importing the file in Releva, the only supported file extension is .csv so if you are using another software other than Google Sheets, make sure to export it to this format.
Now, let’s see how we can do that in Google Sheets – expand the File menu → select Downloads and choose Comma Separated Values (.csv) from its options. Once selected, the file will be prepared and downloaded when ready.
Import the file #
With the file now ready and exported, all that’s left is to import it into Releva. If you are unsure how to do that, please check the article below:
