Requires Admin permissions.
Adobe Commerce Intelligence is powerful not only because of its visualization features, but because it gives you the ability to put all of your data into a single Data Warehouse. Even data that lives outside your databases and integrations can be brought into Commerce Intelligence by using the File Upload tool in the Data Warehouse Manager.
Use ad campaigns as an example. If you are running both online and offline campaigns, you cannot get the whole picture if you are only analyzing data from an online integration. Uploading a spreadsheet with the offline campaign data allows you to analyze both sets of data and get a more robust understanding of your campaign performance.
The only supported format for file uploads is
comma separated values. If you are working in Excel, you can use the Save As function to save the file in
CSV files must use
UTF-8 encoding. Most the time, this is not an issue. If you encounter this error while uploading a file, consult this Support article.
Files cannot be larger than 100MB. If the file is larger than this, separate the table into chunks and save them as individual files. You can append the data after the initial file is loaded.
All tables must have a
primary key. There needs to be at least one column in your table that can be used as a
primary key, or a unique identifier for each row in the table. Any column designated as a
primary key can never be null. A
primary key can be as simple as adding a column that gives a number to each row, or can be two columns concatenated to make a column of unique values (for example,
campaign name and
If a column (or columns) is designated as unique but there are duplicates, the duplicate rows are not imported.
Before you can upload your data into Commerce Intelligence, check that it is formatted according to the guidelines in this section.
To ensure that columns are labeled and imported properly, make sure that the first row of your spreadsheet is a header that describes the data in each column.
Column names must be unique and contain only letters, numbers, spaces, and these symbols:
$ % # /. If a column name contains a comma, it is split into two columns when the file uploads. Also, Adobe recommends that there be fewer than 85 columns in the file to optimize update speed.
Because files have to be in
CSV format, the use of commas can cause problems with uploading data.
CSV files use commas to indicate new values, therefore a column with a name like
August is read as two columns (
August) instead of one, shifting all of your data over one row. Adobe recommends avoiding commas wherever possible. You can use
Data Preview to see if your data is displaying correctly once an update completes.
Any dataset that includes dates must use the standard date format
YYYY-MM-DD HH:MM:SS or
Some special characters are not accepted. For example, the pipe symbol
& # 1 2 4 is interpreted as creating a column and causes errors when uploading a file.
Currency values should have the datatype
Decimal Number selected, and these columns automatically round to two decimal places in your Data Warehouse. If you do not want to round your decimal numbers or have a degree of precision greater than this, you should select the
Non-Currency Decimal Number datatype.
Percentages must be entered as decimals. For example:
Some values in your file - like ZIP codes and IDs - may begin or end with zeroes. To ensure that the zeroes are properly retained and uploaded, you can change the formatting type (for example, from number to text) or enforce number formatting.
US ZIP codes as an example of how to change number formatting. In Excel, highlight the column containing
ZIP codes and change the number format to
ZIP code. You can also select a custom number format, and in the
Type window, enter
00000. Keep in mind this method could present problems if some codes are formatted as
00000 and others are
Type can be formatted differently to accommodate other data types, such as IDs. If an
ID is nine digits long, for example, the
Type could be
000-000-000. This would change
For Google Docs and Apple Numbers resources, refer to the Related list at the bottom of this page.
Now that your spreadsheet is correctly formatted and Commerce Intelligence-friendly, add it to your Data Warehouse.
To get started, go to Data > File Uploads.
Click the Upload to New Table tab.
Click Choose File and select the file. Click Open to start the upload.
After the upload completes, a list of the columns Commerce Intelligence found in your file displays.
Check that the column names and data types are correct. Specifically, check that any date columns are being read as dates and not numbers.
datatype is important, so do not skip this step!
Select the column (or columns) that make up the
primary key for the table by using the checkboxes under the key icon.
Name the table.
Click Save Table.
A Success! message appears at the top of the screen after your table is saved.
If you need a visual, look at the whole process:
Uploaded tables display under the File Uploads section of the table list (in both the All Tables and Synced Tables options) in the Data Warehouse Manager:
Got some new data to add to a file you have already uploaded? No problem - you can easily update and append data in Commerce Intelligence.
To get started, go to Manage Data > File Uploads.
Click the Edit/Upload
.csv to Existing Tables tab.
In the dropdown, click the name of the table you want to update or append.
Use the dropdown to select the option for handling duplicate rows:
Overwrite old row with new row
|This overwrites existing data with new data if a row has the same primary key in both the existing table and the new file. This is the method to use for columns with values that change over time - for example, a Status column. Existing data is overwritten and updated with the new data. Rows with primary keys not in the existing table are added as new rows.
Retain old row; discard new row
|This causes new data to be ignored if a row has the same primary key in both the existing table and the new file.
Purge all existing rows first and ignore duplicate keys within the file
|This deletes any existing data and replaces it with the new data from the file. Only use this option if you do not need any of the data in the existing table.
Click Choose File and select the file.
Click Open to start the upload.
After the upload completes, Commerce Intelligence will validate the data structure in the file. A Success! message appears at the top of the screen after your table is saved.
Just like calculated columns, data from file uploads is available after the next update cycle completes. If an update was in progress during the file upload, the data will not be available until after the next update. Once an update cycle is completed, you can navigate to the
Data Preview tab in your Data Warehouse to ensure that the file uploaded correctly and data is displaying as expected.
This topic covered only the basics for using importing data, but you may want to do something more advanced. Check out the Related articles for guidance on formatting and importing financial, eCommerce, ad spend, and other types of data.
Also, file upload is not the only way to get your data into Commerce Intelligence. The Data Import API functions allow you to push arbitrary data into your Commerce Intelligence Data Warehouse.