Exporting product data with a CSV file

  • Updated

If you select CSV format, your product data is prepared in two steps:

  1. Export product data from your e-commerce system or another data source into a CSV file.
  2. Move the CSV file to your personal directory on the server.  Optimizely Campaign retrieves the CSV file then makes its product data available in the Template Kit.

    If you have a personal directory on the server but no login information, contact customer support to set up a user account.

Export product data from your system as a CSV file. CSV files exported from different systems can differ widely. For example, names and column heading order can be different. Such differences can create problems when importing product data. If no information is provided about what the dataset columns contain (for example, the column that contains the product name), Optimizely Campaign cannot read the CSV file properly nor correctly classify the individual datasets.

But, if you use an Optimizely integration for one of these e-commerce systems, the content interface can recognize CSV files with different structures and process them correctly :

  • Magento
  • OXID
  • Shopware

If you use Optimizely integration with one of these systems, you can export product data using the Optimizely plug-in with no adjustment.

If you do not use an Optimizely integration, you must use Optimizely Campaign's generic CSV structure.

Generic CSV structure

Optimizely Campaign's generic CSV structure standardizes the structure and naming of column headings in CSV files, ensuring the correct import of product data.

If your system/your data source has a feature that supports the export of datasets to a CSV file (such as a plug-in), configure the CSV file with the following structure and column headings:

Generic CSV structure
"id";"name";"category";"text1";"text2";"text3";"text4";"text5";"text6";"text7";"text8";"text9";"text10";"link1Text";"link1Url";"link2Text";"link2Url";"link3Text";"link3Url";"image1ImageUrl";"image1AltText";"image1Link";"image2ImageUrl";"image2AltText";"image2Link";"image3ImageUrl";"image3AltText";"image3Link";"image4ImageUrl";"image4AltText";"image4Link";"image5ImageUrl";"image5AltText";"image5Link";"image6ImageUrl";"image6AltText";"image6Link";"additionalData1";"additionalData2";"additionalData3";"additionalData4";"additionalData5";"additionalData6";"additionalData7";"additionalData8";"additionalData9";"additionalData10";"additionalData11";"additionalData12";"additionalData13";"additionalData14";"additionalData15";"additionalData16";"additionalData17";"additionalData18";"additionalData19";"additionalData20";

Configuring an export feature generally requires programming.

When configuring the export feature, observe the following:

  • The CSV file must be RFC-compliant. Technical notes are contained in RFC 4180.
  • Set character encoding for the CSV file to UTF-8 without BOM.
  • Use the semicolon (;) as a separator.
  • If some product data includes quotation marks (inverted commas), mask them with escape characters. Use the quotation mark " as the escape character. If you do not mask quotation marks, they are misinterpreted as code, and the product data import fails.

    Example: You surround a word or a word group with quotation marks, such as "Savoir Vivre." In your CSV file, wrap the quoted text with double quotation marks: ""Savoir Vivre"". The outer quotation marks are recognized as escape characters so are not displayed in the message text.

Creating a CSV file manually

If your system/your data source does not have a feature for exporting product datasets to a CSV file, and you want to manually create a CSV file with product data, perform the following steps:

  1. Download the template for the generic CSV structure here: template generic structure.csv
  2. Open the template using a spreadsheet program, such as Microsoft Excel or LibreOffice Calc.

    Image: Template spreadsheet

    When opening the template, if you need to enter a separator, select semicolon. If you need to specify character encoding, use UTF 8 without BOM. Some programs, such as Microsoft Excel, do not provide UTF-8 without BOM. In that case, select UTF-8.

    When filling out the template in the generic CSV structure, you must observe the following:

    If some product data includes quotation marks (inverted commas), mask them with escape characters. Use the quotation mark " as the escape character. If you do not mask quotation marks, they are misinterpreted as code, and the product data import fails. Example: You surround a word or a word group with quotation marks, such as "Savoir Vivre." In your CSV file, wrap the quoted text with double quotation marks: ""Savoir Vivre"". The outer quotation marks are recognized as escape characters so are not displayed in the message text.

  3. Begin entering product data in row 2. Enter the data for each product side by side (see image).

    Do not change the column headings or their order in row 1. If you do, your product data cannot be loaded into the Template Kit.

    Image: Entering product data

    To add another product offer, insert the data again side-by-side in the next row (see image).

    Image: Entering more product data

    —Table: Product data and corresponding columns—
    Template column heading Product data to enter
    id Enter the unique product ID. It is not shown in your messages. You may only enter a product ID in the document once.
    name Enter a name for your product offer. Later, in the Template Kit, you can select the product offer by its name to include it in your messages (static CSV article only). It is not shown in your messages.
    category Enter the product category. It is not shown in your messages. With the product category, you can create overview category trees in the Template Kit and compile related products together to find specific products (static CSV article only). Enter the subcategories in this field, separated by hash symbols. Example: Mediterraneantravel#Italy#Sicily

    A category may have up to 10 sub-categories.

    text1 – text10 Up to 10 text fields are available. Enter one piece of information in each field. For example:
    • the heading for your product offer, such as text1 in the text field
    • the name of the product to be shown in your message, such as text2 in the text field
    • the description of your product, such as text3 in the text field
    • the old price of your product, such as text4 in the text field
    • the current price of your product, such as text5 in the text field
    • the supplier/manufacturer of your product, such as text6 in the text field

    You can choose what you want to enter in the text fields. Adapt the content according to your wants and the requirements of your product/your service.

    It may be best to organize product offer components sequentially from text1 through text10, which is how they are shown from top to bottom in the finished product offer. If you are not sure of the best order, contact customer support.

    The formatting (text size and text color) of the text field content is set later using an HTML layout.

    link1Text – link3Text Enter the heading for the link or links to your product offer.

    For example: Offer, More Information or Buy Now.

    link1Url – link3Url Enter links to pages to which recipients are directed if they click a link (such as the corresponding product page in your web shop).
    image1ImageUrl – image6ImageUrl Enter the URLs for the corresponding product images. The images are loaded by Optimizely Campaign and displayed in your messages.
    image1AltText – image6AltText Enter alternative text for images. This text appears if an image cannot be loaded.
    image1Link – image6Link Enter a link to the product image. If the image is clicked, the recipient is forwarded to the link target. This can be your web shop or another address that you want to connect to the image.

    Avoid mixing up ImageLink with ImageURL, which indicates the saved location of the image.

    additionalData1 – additionalData20 Enter additional data records that you can use, such as metadata for the program logic of the HTML layouts.
  4. Save and rename the file.

    Give the file the name you entered for your CSV file when setting up content interface paragraphs. If you use a different name, the product data import will fail. If you forgot the name, contact customer support.

    When saving the file, make sure the spreadsheet program does not assign a new file type. The file must have the file type CSV or file extension .csv.

Transferring the CSV file to the Optimizely server

After you save the completed and renamed CSV template, transfer it to your personal directory on the Optimizely server.

If you use an Optimizely integration for Magento, Shopware, or OXID, the transfer is automatic. If you do not use an Optimizely integration, but want to automate the transfer, use a script. Consult your system administrator or software service provider for a programming solution.

To transfer the completed and renamed Optimizely template in your directory to the Optimizely server, perform the following steps:

  1. Log in to the Optimizely server with your user data.

    If you do not have a user account for the Optimizely server, contact customer support. See FTP API (Windows) for information about logging in to the Optimizely server; see FTP API (macOS/OS X) for macOS.

  2. Upload the Optimizely template to your personal directory on the Optimizely server.

    If you have multiple directories on the Optimizely server, save the template in the directory set up for your CSV files during the configuration of the content interface. If you save the template to another directory, your product data import fails.

    After you load the completed and renamed template into the correct directory, your current product offers are available in the Template Kit for integration into your messages.

    It can take some time for your product offers, transferred as a CSV file, to be available in the Template Kit. You should automatically perform the data transfer at night.