SFTP = Secure File Transfer Protocol.  Clarivoy has integrations with select vendors to receive sales and leads data via our SFTP server.

Existing SFTP Integrations

• Momentum CRM
• ProMax CRM
• Tekion DMS
• See the Vendor Details & Instructions board - SFTP Integrations for a list of all existing SFTP integrations

Click here to see instructions for setting up New SFTP Integrations.

New SFTP Integration

Initiation

CS Team

For new dealers or vendors who wish to setup data integration via SFTP:

1. Before promising anything to the dealer or vendor, consult with the Ops Team as they need to vet whether or not we can create an automated SFTP integration.
2. Tag Ops Team in the Rocketlane task and provide the dealer or vendor technical contact information (name, email address, phone number).
3. Invite the technical contact to the project and assign the task to the Tech Contact and Ops Team.
4. Set the task status to In Progress

Evaluate New Integrations

Ops Team

Determine if an SFTP integration is in our best interest.

Here are some reasons why we may NOT want to setup an SFTP integration:

• Smaller client (3 or fewer rooftops) - New SFTP integrations are reserved mainly for Vendors and large client groups.
• A Client who is temporary in nature (3 month trial basis / Limited Study / Canada / ODA)
• The Client or Vendor cannot automate the data push on their side - We need the data flow automated on their side so we know when to schedule our automated DAGs to pick up the data on our end.  If a client or vendor is manually pushing the data from their side, this has proven to be largely unreliable and they cannot consistently push data on the same day each month.  If a dealer is just 1 day late past when our automated DAG runs, their file will not get picked up by the DAG until it runs again (usually a month later).  Someone will need to go manually retrieve the file and load it, in which case we may as well have the dealer go through our secure upload site.
  • If the dealer or vendor is manually pushing data, go COLLECT instead.  Our upload portal is just as secure as SFTP.

Creating Credentials

Ops Team

1. On the Tickets board, open a Help Ticket for Engineering to create SFTP login credentials (assign to Sys Admin & tag them in the update comments).
2. Once the Sys Admin creates the login, they should enter the credentials into Keeper in the shared folder SFTP Credentials.  This folder is automatically shared with the appropriate people (Ops Team, Sys Admin, VP of Engineering).

Sending SFTP Integration Instructions

Ops Team

1. Share the login securely with the vendor or dealer:
   1. Open the login record, which should be saved in the shared folder SFTP Credentials
   2. At the top, click the Share button
   3. Choose the One-Time Share tab
   4. Click the Create One-Time Share button
   5. Link expires after: Choose the appropriate timeframe (typically 30 days)
   6. Click Create Link
   7. Copy the link and paste it into the Rocketlane message below.
2. In the Rocketlane task chat, send a message to the tech contact, including:
   1. A link to the Clarivoy SFTP Setup Instructions 
   2. Their username
   3. The Keeper One-Time Share link you copied above
   4. The expected filename for their data (see Clarivoy SFTP Setup Instructions)
      Example:  
   5. Request that they send a test file and confirm
3. Set the Rocketlane task status to Waiting on Vendor.

Verifying Data

Ops Team

1. Once the test file is received, verify the following:
   1. Filename is in the correct format
   2. File format is correct (CSV or XLSX)
   3. Headers/fields are good
   4. Data is formatted properly
   5. Data Quality - for example, for Sales, we only want deals that are CLOSED/SOLD. It should not contain any other Deal statuses (Booked, Unwind, Quote, etc.)
2. If the data is good:
   1. Map any property aliases for (multi) files. See Loading Sales and Leads Data through Dropbox.
   2. Create Dropbox Insyncing Data Drop folders. See Loading Sales and Leads Data through Dropbox.
      1. Property specific folder (or multi folder) - see How SFTP data is Loaded below to determine the type of property folder to create.
      2. Vendor specific subfolder
   3. Email client/vendor POC to confirm.
   4. Set the Task Status to Completed
      1. ✴️ PRO - Zapier Integration creates a new item in the SA+TVA Ops Monthly Tasks board.
         1. For details on this process, see: Data Tasks Setting to Completed in Rocketlane.
         2. If the Monthly Task board item cannot be created by Zapier, an error message should appear in the task Chat explaining why.  Typically this is because the required fields are not filled in on the task's Additional Info tab (Method, Task Vendor, Data Type).
   5. On the Tickets board, create a ticket for CS Engineer to create an SFTP DAG to pull the files over to Dropbox Insyncing Data Drop for this client/vendor.    Need Rocketlane automation  
      In the Help Ticket. Be sure to specify:
      1. Expected filename convention
      2. Dropbox IDD folder location(s)
         1. If files contain a Dealership Name (property alias) column, you can create a Vendor Name (multi) folder in IDD and have the files loaded there
      3. If files do not contain a Dealership Name (property alias) column, you will need to have Engineering hardcode each
      4. Delivery schedule (and when you want the DAG to run - if monthly, typically the day after the expected delivery)
      5. Any other special instructions or pertinent information
3. If the data is not good:
   1. Email client/vendor POC for corrections & revised file. 
   2. Leave the Rocketlane task status as In Progress and make sure it is still assigned to the tech contact.

How SFTP data is loaded

Automated Processes

• DealerVault Sales and Service data is loaded from the SFTP server via special DAGs.  DealerVault Sales data goes directly into sales_raw and does not pass through Dropbox.
• Trader.ca leads are loaded from the SFTP server via a special DAGs.  Trader.ca leads data goes directly into leads_raw and does not pass through Dropbox.
• Most other SFTP integrations use individual DAGs that copy the files into specified (hardcoded) Dropbox Insyncing Data Drop folders.  See Loading Sales and Leads Data through Dropbox.
  • For vendors whose files contain a property alias column, the files should be loaded into a multi folder named Vendor Name (multi)
  • For vendors whose files DO NOT contain a property alias column, the files should be loaded into specific property folders.  Each new property we add must be manually mapped by editing the Airflow DAG.  For every new dealer added on these vendors, you must create a ticket on the Tickets Board and request that Engineering update the Airflow DAG.  An example of this is Momentum CRM.
  • See the Vendor Details & Instructions board for details on specific vendors.
     

New Vendor Steps

Ops Team

If we are setting up a new vendor:

1. Add the new vendor to the claripedia.lead_data_suppliers table
2. Add this vendor to the Vendor Details & Instructions board with their SFTP process
   1. Add vendor if necessary
   2. Set method to SFTP
   3. In Integration Instructions, specify the Delivery schedule 
   4. Vendor contacts for adding new rooftops
   5. Vendor Email Fire(s) text
3. Create a new HelpJuice vendor page under the category Pro and Pro+ MTSA Data Integration, then under the appropriate data type subcategory.
   1.  If the vendor requires an authorization form to be completed by the dealer, either link to that URL or embed the authorization form in the HelpJuice article.
4. Update the Data Integration: SFTP Methods HelpJuice article and add this vendor to the list of existing SFTP integrations, linking to their vendor-specific HelpJuice article.
5. Setup a Phase 2 data task in the Rocketlane Phase 2 Pro/Pro+ data tasks template for this specific vendor
   1. Place integration instructions in the task Note.
   2. Place any internal notes on the integration process in the task Private Note, along with a link to the HelpJuice article.
   3. Be sure to fill in all applicable fields on the Additional Info task tab, including required fields (Data Type, Method, Vendor Name - use the preprocessing_key from the claripedia.lead_data_suppliers table).
   4. Go to the Dynamic tab and set the conditions for this task (where [vendor type field] is exactly [vendor]).