Data Integration

Owned by Melih Ayhan (Unlicensed)
Last updated: Dec 19, 2022 by Berk Topaloğlu

Member List Upload

Data Import to RMC Data Warehouse platform requires 1 zip file with the following 2 files:

  • TXT // CSV: text file containing data MANDATORY
  • XML: xml file containing campaign definitions OPTIONAL


The developers are responsible with the following in order to upload the data and launch the related campaigns:      

  1. Select data from the database and prepare the text file using the data.
    The extension of the text file must be .txt or .csv

  2. If it exists, prepare the XML file that contains the campaign definitions.

  3. Zip these two files into a single Zip file and name it.

  4. Send it via SFTP to the RMC Data Warehouse SFTP site sftp://file.euromsg.com using the SFTP Username & Password provided by the RMC Security Team.


  • If there is a single text file in the Zip, the Data Warehouse system will just upload the data and prepare a corresponding table with the customer data.
  • If there is an XML file in the Zip, the system will launch the campaigns in the XML file once it uploads the records.

Things to consider while preparing the TXT data file

The most important issue while transferring data to the RMC Data Warehouse platform is to prepare a .TXT data file. There are a number of things to consider in order to prevent erroneous upload:

  1. Your data TXT file needs to have a header column in the first row. 

  2. Column names must be composed of following ASCII characters. A-Z,  a-z , _ . 
    Eg.  BIRTH_DATE, BIRTHDATE, BirthDate, Birth_Date, birth_date.

  3. Turkish characters can not take place in the column names.

  4. The length of any column name must at least 2 characters long.

  5. The extension of the text file must be .TXT or .CSV

  6. Some columns have reserved words:

    1. Please use EMAIL column name for your column containing email addresses.

    2. Please use GSM_NO for your column containing the GSM numbers. 
      The GSM_NO column values should be in the following format: 530.4444444, 905304444444 or 05304444444.
      For numbers outside of Turkey use the format countrycode.phonenumber ,example for Germany 49.1711234567.
    3. Please use KEY_ID for your column containing KEY ID's like customer_id. If KEY_ID column is used, the system takes this column as a unique key and uploads the data accordingly. If this column does not exist in the data file, the system will choose the EMAIL column as the unique column and will upload the data accordingly. You must choose the KEY_ID or EMAIL column as the unique column the first time you upload your data and never change it afterwards. You must upload your data using KEY_ID or EMAIL values as a unique column.

    4. Please use EMAIL_PERMIT and GSM_PERMIT for your columns containing permission information for your members.

    5. Please use ACTIVITY_ID  for your CRM/ERP activity ID's.

  7. If the column name KEY_ID exist in the table, system will use this column as the reference.

  8. Please use | (pipe), ; (semicolon) or “,” (comma) char to separate your columns and make sure that your data in your columns doesn’t have “|”, ”;” or “,” accidentally.

  9. Please make sure that your rows have CRLF at the end of each line.

  10. Birth dates and any other date related columns must be in the following forma:t yyyy-mm-dd, like 1977-03-17 (17 March 1977).

  11. Filtering and comparison in these tables in RMC Data Warehouse platform is done via String Search. Because of this reason, your integer fields must be transferred to these tables accordingly.
    If you have an integer column in your database to keep shopping points and these numbers are going from 1 to 999, you should extract these numbers to your data file like 001, 002, 003, ..., 999. Because only using this method, you can select your target group whose shopping points between ‘050’ and ‘099’ in RMC platform. If you use numeric column types, you don’t need to padleft function.  

  12. The name of text file in the ZIP file is very important for you. You will use these file names in order to distinguish tables in your account in RMC platform. 
    The name of the table that will be created in your account will be the same as your text file’s name

  13. Please use a methodology while giving the name of zip files. “Date” should be included in your file names. Because the name of zip file will be the name of the table you will be working in your account. You are able to upload tables with the same name although this is not suggested.

  14. The encoding of the file should be ISO-8859-9. If you use UTF-8 as the encoding, our support team can configure your account for that. Then, you can use UTF-8 encoding with BOM (byte order mark)

Data types of Columns

You can set the data types of columns. For example, you could use the ‘money’ type for currency data or  ‘float’ for decimal data. Other data types are datetime, string(length), integer and combobox.If you don’t specify the data types of the column names, the system will generate all columns as varchar (100). 

The max length of string variable is 1024
You don't have to add data types to column headers in incremental uploads if you use the MAIN DW table. You only need to have the data type in the first MAIN table upload.

Example header with data types
EMAIL|NAME[string(50)]|BIRTH_DATE[datetime]|LOYALTY_POINT[float]|AGE[int]|INCOME_LEVEL[money]|GENDER[combo.string(1)]
Sample TXT Data File
EMAIL|NAME|SURNAME|BIRTH_DATE|SEGMENT|LOYALTY_POINT
it@euromsg.com|IT|EuroMessage|2003-01-01|A|09
jason@hotmail.com|Jason|Goodwill|1987-03-02|B|77
hans@yahoo.com|Hans|Muller|1983-08-04|A|63

This sample file will create a 6 column table in your RMC Data Warehouse account. 

Please note that LOYALTY_POINT column is 2 length integer column and data is prepared accordingly. Please don’t forget that the first row must include column names.

Sending Your Data in Your Account

  • After you prepare your TXT data file, you must zip the file and place it sftp://file.euromsg.com site in order to upload the data into your account in RMC Data Warehouse platform. You must use username and password provided by RMC Security team while login to the sftp site.

  • The system will create a database table for your data in the database with the same columns and data in it. And please note that the name of the table will be the same with the name of the text file in the zip you uploaded to sftp directory.


Incremental Data Upload

You can add new members or update the user information in the current tables with incremental data upload.

You may use REST or SOAP methods for incremental data upload.

RMC determines the base and incremental files by zip file name notation.

If base table name is all_member_data.zip, incremental file name should be all_member_data_20141225_inc.zip 

System can detect the _inc notation and know that it is an incremental upload, it will match the base file name (all_member_data) and set the version name (20141225). Data is always collected in base table therefore while sending email campaign you should only use base file as the target list.

Example Base File
EMAIL|NAME|SURNAME
email1@test.com|name1|surname1
email2@test.com|name2|surname2
email3@test.com|name3|surname3
Example Incremental Data
EMAIL|NAME|SURNAME
email1@test.com|updated_name1|surname1 (This one updates the first name of the first user)
email4@test.com|name4|surname4  (This one inserts a new user to the base table)
email5@test.com|name5|surname5  (This one inserts a new user to the base table)


Uploading your Product Catalog

You can upload your Product Catalog using the Data Warehouse module. 

Things to consider while preparing your PRODUCTS table:

  1. The file name for the product table must be PRODUCTS.zip

  2. You need to have a column, named PRODUCT_ID in the products file.

  3. PRODUCTS tables are global. If your product catalog is changing every day, you can upload the PRODUCTS table three or four times in a day.

  4. The system always uses the last successful PRODUCTS table upload while sending a campaign.
Example PRODUCTS File
PRODUCT_ID;PRODUCT_NAME;COLOR;PRODUCT_PRICE;IMG_URL;TARGET_URL
1354ASC;shoe;brown;149.99; http://yourwebsite.com/img/shoe.jpg; http://yourwebsite.com/products/1354ASC
139CSA1;tshirt;blue;23.99; http://yourwebsite.com/img/tshirt.jpg; http://yourwebsite.com/products/139CSA1
1535KCA;bag;black;79.99; http://yourwebsite.com/img/bag.jpg; http://yourwebsite.com/products/1535KCA


Uploading Promotion Codes

You can upload the promotion codes using the Data Warehouse module.

Things to consider while preparing PROMO_CODES table:

  1. The file name for the promo code table must be PROMO_CODES.txt in PROMO_CODES.zip

  2. You need to have the following column names; CODE, CATEGORY, EXPIRE_DATE, DESCRIPTION in promo_codes.zip file

  3. The type of data must be in the following format:

    1. CODE → varchar (20)

    2. CATEGORY → varchar (20)

    3. EXPIRE_DATE → date (sample format:  dd.mm.yyyy)

    4. DESCRIPTION → varchar (50)
Example PROMO_CODES file
CODE;CATEGORY;EXPIRE_DATE;DESCRIPTION
DWH012;Anniversary;19.04.2015; 10% discount code for your anniversary.
DWH013;Anniversary;19.04.2015; 10% discount code for your anniversary
DWH014;Birthday;19.04.2015; 10% discount code for your birthday.
DWH015;Anniversary;19.04.2015; 10% discount code for your anniversary.
DWH016;Birthday;19.04.2015; 10% discount code for your birthday.


Parent Topic: Data Warehouse SFTP Integration

Copyright 2020 Related Digital