Importing transactional data

The example above consists of the minimal set of the required fields. You can find the list of additional columns in the specification or you can add columns directly while mapping the data in the import wizard. The instruction on mapping the eventTimestamp, revenueAmount, currency and eventSalt fields is available further in this procedure.

Importing transactions

1. Go to Settings > Import > New import.

2. Choose Transaction as the type of the import.

3. Choose the method of import.

4. Enter the name of the import.

5. Only for Pull imports: To define the access to your SFTP, click the Define button. When you adjust the settings, confirm with the Apply button.

6. Only for Pull imports: To define the frequency of imports, in Schedule section click the Define button and adjust the settings. Confirm them with the Apply button.

   Tip: Instruction on scheduling imports is available here.

7. To upload a file with the data to be imported, click the Define button.

   Important:

   • If you have chosen the File upload as the method of import, the file you upload here is is imported immediately after saving the import.
   • If you have chosen the Pull method, the file you upload here is imported at the time you scheduled.
   • In the case of the Push method, the file you upload here is just a sample file. More information about the import flow for every method is available at the link.
   • Choose the type of the file you are going to import.
   • To upload the file, click the button.
   • Confirm the action by clicking the Apply button.

8. To proceed to the next step, click the Next button.

9. Connect (map) the fields on the list on the left side to the columns. To do so, click the circle next to the field name and join it with an appropriate column. Use the items both from the Required and Optional sections. As a result, under the column name, you can see the name of the field to which the column has been linked.

   

   The scheme of mapping the required fields (according to the example shown on the screenshot above):

   input	output
   customID	CustomId
   orderID	OrderId
   paymentInfo	Payment Info
   finalUnitPriceAmount	Final Unitprice Amount
   productQuantity	Quantity
   sku	Sku
   productName	Name
   eventTimestamp	recordedAt

   There might be some cases when the field list lacks the fields equivalent to the column in your file. In such a case, you can create your own fields by clicking the Add field button at the bottom of the list. The name of the field must start with the metadata. prefix (for example, metadata.inoviceID).

10. If the file includes the eventTimestamp column, you can map the transaction date format. Follow these steps:

    • Map the Recorded At field from the list with the column consisting of transaction dates.
    • Under the title of the column (in this example it is eventTimestamp), click the name of the field with which it was mapped.
    • On the pop-up, choose Time > Other patterns > and click the hyperlinked strftime.
    • On the external website, follow the instructions and define the date format you used in the file.
    • Copy and paste the date format pattern to the pop-up in the field.
    • To continue, click the Save button.

11. To add the required revenueAmount column that stores the total value of the order after taxation, follow these steps:

    • Click the Add column button.
    • In the Column name field, enter the header name of the column (for example, revenueAmount).
    • Click the icon and from the dropdown list select the Template option.
    • In the Template field, paste the following code (if necessary, change productQuantity and finalPriceAmount to correspond with the column names in your import file):

    {% set line_sums = [0] %}{% for prod in root.products %}{% if line_sums.append(prod["productQuantity"]|int*prod["finalUnitPriceAmount"]|replace(",", ".")|float) %}{% endif %}{% endfor %}{{ line_sums|sum }}

    Important:

    All items from the transaction must be contained in rows that immediately follow each other. Transactions are recognized by orderId and products are recognized by sku.

    

    • To continue, click the Save button.
    • Map the Revenue amount and Value amount fields from the list on the left with the newly created column. At this point, the result of each record in the column amounts to 0.
      Note: The result of the calculation is the total value of the transaction, not the individual product in the row.

12. To map the required currency column, follow these steps:

    • Click the Add column button.
    • In the Column name field, enter the header name of the column (for example, currency).
    • Click the icon and from the dropdown list select the Currency option.
    • Click the Currency field and from the dropdown list select the currency.
    • To continue, click the Save button.
    • With the newly created Currency column, link all the fields from the list on the left that represent columns in your file which contain records that signify an amount of money. In this example it is Revenue amount, Value amount, and Final Unitprice Amount.

13. To generate a UUID for each transaction event, you need to add the eventSalt column. It’s useful in a situation when you conduct another import and the imported file happens to have the same transactions. In such a situation, instead of being duplicated, the transactions will be overwritten.

    • Click the Add column button.
    • In the Column name field, enter the header name of the column (for example, eventSalt).
    • Click the icon and from the dropdown list select the Template option.
    • In the Template field paste the following code:

      {{root.orderID}}{{root.eventTimestamp}}

    • To continue, click the Save button.
    • Connect the Event Salt field from the list on the left with the newly created column.

    

14. To proceed, click the Next button.

15. Verify the mapping of the fields.

16. To complete the process, click the Save button.

17. Only for Push method imports: the Save button saves the mapping, and you get the webhook URL to send the data. You can use this mapping later for a single import of CSV files. To do so, go to the list of imports, and click the import. Click the Import data from CSV file. The structure of the file (number of columns, name of the columns, and the data format) must be the same.