With a SFTP server integration, the customer is responsible for data accuracy and format
Dear Lucy will simply read the data as it is in the CSV file, minimizing risks of human errors and misinterpretations.
The Dear Lucy integration service runs in AWS. All data should be accessible from the following IP addresses: 18.104.22.168, 22.214.171.124, 126.96.36.199 and 188.8.131.52.
Each row in a CSV or database is an independent unit of data, other rows do not affect how it is interpreted.
Initial data is fetched from the beginning of the previous year up to the current date. There should be a way to retrieve this range of data when needed, eg. if there have been significant changes to the contents of the data that require all of it to be re-imported.
Updates to Existing Data
Each unit of data should have a unique ID so that it can be updated with new information when needed. There should be a method to fetch the latest changes to data.
It is important to consider that Dear Lucy dashboard fetches updated data from the same file regularly, but only reads the current state of the data.
In other words, if past data has been changed or deleted, there is no way for Dear Lucy’s system to capture those changes, since there is no log of changes. This is the case particularly for CSV files or time based structures.
Deletion of Existing Data
Deleted data in the source system should also be marked as deleted in the transferred data. This should use the same unique ID which is used to update existing data.
Data format Schemas
Your sales data in CSV format should be structured according to Dear Lucy's schema for sales data (see format example in attachment).
|id text PRIMARY KEY,||active boolean,|
|close_date date,||business_unit text,|
|expected_value real,||competitor text,|
|name text,||country text,|
|open_date date,||customer text,|
|probability real,||customer_type text,|
|state text (funnel state),||deal_type text,|
|status text (won, lost, open),||lead_source text,|
|value real,||product text,|
|sales_person text,||region text,|
|updated_at timestamp,||sales_person text,|
|lost_reason text||state_change_date date,|
|updated_at timestamp without time zone,|
|id text PRIMARY KEY,||company text,|
|active boolean,||owner_id text,|
|completed_date date,||source text,|
|created_date date,||business_unit text,|
|date date,||category text,|
|description text,||contact text,|
Upload the CSV file to your SFTP Server
Once the CSV file is ready you'll need to integrate it to your dashboards to see your data. The process requires input from Dear Lucy.
1. Open up your SFTP server to the following IP addresses: 184.108.40.206, 220.127.116.11, 18.104.22.168 and 22.214.171.124. The Dear Lucy integration service runs in AWS.
2. Upload the CSV file to your SFTP server.
3. Log in to your dashboard environment and navigate to the following page: https://admin.dearlucy.co/yourcompany/connectors. "yourcompany" in the url represents your website url address for your dashboards.
4. Press ”Add Authentication” in the top right corner and select ”SFTP” from the drop-down menu.
5. Add the SFTP server credentials: i. Host, ii. Username and iii. Password
6. Once authenticated, Dear Lucy will receive a notification that your SFTP server has been authenticated. As a final step, you need to send the name of the file to Dear Lucy, you can send this to email@example.com.