SFTP Integration Guide -- Retail Data

This guide walks you through setting up an SFTP connection to send your retail 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/
  └── retail/
        ├── retail_2024_01.csv
        ├── retail_2024_02.csv
        └── retail_2024_03.csv

The path to your folder (e.g., /data/prescient/retail/) 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.

Expected Table Schema

Our integration expects CSV files that aggregate your key daily or weekly retail metrics. This table should follow the schema below:

Column Name	Type	Required	Description
date	`DATE`	Yes	Reported date in YYYY/MM/DD format. Dates need to either be daily or a consistent day of the week when aggregating to weekly values.
revenue_amount	`FLOAT`	Yes	Total revenue that will be modeled against.
revenue_ccy	`STRING`	Yes	Currency the revenue is in.
date_aggregation	`STRING`	Yes	The data aggregation level D(aily) or W(eekly). The weekly aggregation is assumed to end on the date you give it in the column.
total_units_sold	`INTEGER`	No	The total number of units sold in the time period.
num_of_stores	`INTEGER`	No	The total number of actively selling stores in the time period. Count of all stores with sales greater than 0 in the specific time period.

Note: The recommendation is for the data to be aggregated at the daily level, with one row per date. If that is not possible then a weekly rollup to the start of week can be supported.

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 retail files:

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

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