SFTP Integration Guide -- Ecommerce Data

This guide walks you through setting up an SFTP connection to send your ecommerce revenue data to Prescient. You will provide us with your SFTP server credentials and upload CSV files to a designated folder.

Prerequisites

An SFTP server that Prescient can reach over the internet
Credentials for an SFTP user with read access to the data folder
Permission to upload CSV files to the server

Step 1: Prepare Your SFTP Server

Ensure your SFTP server is accessible from external IP addresses. If your server is behind a firewall, you may need to allowlist Prescient's IP range (provided during onboarding).

Step 2: Create a Data Folder

Create a dedicated folder on your SFTP server to hold your CSV files. For example:

/data/prescient/
  └── ecommerce/
        ├── revenue_2024_01.csv
        ├── revenue_2024_02.csv
        └── revenue_2024_03.csv

The path to your folder (e.g., /data/prescient/ecommerce/) is your base path. All CSV files placed in this folder will be picked up by Prescient.

Step 3: Gather Your Connection Details

You will need to provide the following credentials through the Prescient platform:

Field	Required	Description
Host	Yes	The hostname or IP address of your SFTP server (e.g., `sftp.yourcompany.com`).
Username	Yes	The SFTP username.
Password	Yes*	The password for the SFTP user.
Private Key (PEM)	Yes*	An RSA private key in PEM format, as an alternative to password authentication.
Private Key Passphrase	No	The passphrase for the private key, if it is encrypted.
Port	No	The SFTP port. Defaults to `22` if not specified.
Base Path	Yes	The folder path on the server where your CSV files are located.

You must provide either a Password or a Private Key (PEM). If both are provided, the private key takes precedence.

Important: Store your credentials securely. We recommend using a dedicated SFTP user with read-only access scoped to the data folder.

File Format and Schema Requirements

All files must be in CSV format (.csv extension) with a header row. Files that do not end in .csv will be ignored.

The required columns must be present in every file. Optional columns can be omitted -- they will default to NULL if not included.

Expected Table Schema

Our integration expects CSV files containing your daily ecommerce revenue and order data. This table should follow the schema below:

Column Name	Type	Required	Description
date	`DATE`	Yes	The date of the ecommerce data in YYYY/MM/DD format.
new_customers	`INTEGER`	Yes	The number of new customers acquired on the given date.
transactional_orders	`INTEGER`	Yes	The total number of one-time purchase orders.
transactional_revenue	`FLOAT`	Yes	The total revenue from one-time purchases.
subscription_orders	`INTEGER`	No	The number of subscription orders.
subscription_revenue	`FLOAT`	No	The total revenue from subscription orders.
wholesale_orders	`INTEGER`	No	The number of wholesale orders.
wholesale_revenue	`FLOAT`	No	The total revenue from wholesale orders.

Note: Each row should represent one day's ecommerce metrics. If you do not have subscription or wholesale data, you can omit those columns entirely.

How Ingestion Works

Understanding how Prescient processes your files helps you structure your uploads correctly.

Automatic New File Detection

Prescient automatically scans your SFTP folder on a regular schedule. Each time it runs, it identifies only new files that have not been previously ingested. Files that have already been processed are skipped -- you never need to remove old files from your folder.

Latest File Takes Precedence

When multiple files contain records with the same key (e.g., the same date), the records from the most recently uploaded file always take precedence. This means:

To correct data: Simply upload a new CSV file containing the corrected records. The new values will automatically overwrite the previous ones.
No need to delete or modify old files: Earlier files remain on the server, but their overlapping records are superseded by the newer file.

Example

Suppose you upload two ecommerce files:

revenue_jan_v1.csv (uploaded Jan 15) -- contains revenue data for Jan 1-14
revenue_jan_v2.csv (uploaded Jan 20) -- contains corrected revenue data for Jan 10-14

Prescient will use the data from revenue_jan_v1.csv for Jan 1-9 and the corrected data from revenue_jan_v2.csv for Jan 10-14, since it is the more recently uploaded file.