Importing transactional data

To prepare analyses powered by the AI engine or simple analyses of transactions, you need to have transactional data in the database. To import transactions to Synerise, follow this procedure.

Requirements


  • User permissions to conduct such a data update.
  • The business profile API key must allow conducting imports.
  • Prepare a file according to the instructions here.
    • Spaces and special characters in the column headers are not allowed.
    • Every record in the file musth have a unique identifier assigned.
    • The file cannot contain empty fields. If you want to leave empty fields, enter the null value.

Example

Image presents the example of file
Image presents the example of file

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.

Warning: The instruction contains the code samples which are adjusted to the example used in the instruction. If you want to make use of them, make sure that the column names: productPriceAmount and productQuantity, orderID and eventTimestamp are named exactly the same in your file.
Tip: The eventTimestamp column is not required. When you don't add it to the file, the date of the import is assumed as the date of the transaction.

Procedure


  1. Go to Settings icon 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, click the Define button and adjust the settings. Confirm them with the Apply button.

  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 Upload file icon 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.

    A file before mapping
    A file before mapping

    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 price of the product 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 Upload file icon icon and from the dropdown list select the Template option.
    • In the Template field, paste the following code:
    {% set line_sums = [0] %}{% for prod in root.products %}{% if line_sums.append(prod["productQuantity"]|int*prod["productPriceAmount"]|replace(",", ".")|float) %}{% endif %}{% endfor %}{{ line_sums|sum }}
        
        
    Warning: Make sure the column names used in the code correspond with those used in your file.
    • 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. The result of each record in the column amounts to 0.
  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 Upload file icon 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 Upload file icon 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.
    Image presents the example of file
    Image presents the final result of mapping the columns.
  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.

Important: Depending on the import method you have chosen in the step 3, the flow of the import will vary.

  • If you have chosen File upload method, the Save button completes the process. The import is processed immediately.
  • If you have chosen Pull method, the Save button completes the process and the import will be processed at the scheduled time.
😕

We are sorry to hear that

Thank you for helping improve out documentation. If you need help or have any questions, please consider contacting support.

😉

Awesome!

Thank you for helping improve out documentation. If you need help or have any questions, please consider contacting support.