Dropbox Data Locations

Most of our sales and leads data is downloaded, collected, or sent to our SFTP on a monthly basis in support of Multi Touch Sales Attribution. 

When downloaded, collected, or SFTP leads files are received, they are dropped off here:

• Leads:  Dropbox/Insyncing Data Drop/0-Leads/SPECIFIC ROOFTOP FOLDER/SPECIFIC VENDOR FOLDER
• Sales:  Dropbox/Insyncing Data Drop/0-Sales/SPECIFIC ROOFTOP FOLDER

After downloaded, collected, or SFTP leads files are processed, you can find them here (in addition to the raw and standardized tables in the database):

• Leads:  Dropbox/Data Archive/Leads/SPECIFIC ROOFTOP FOLDER/SPECIFIC VENDOR FOLDER
• Sales:  Dropbox/Data Archive/Sales/SPECIFIC ROOFTOP FOLDER

Property Folders

SPECIFIC ROOFTOP FOLDERS must have a Property ID # in the folder name following the Rooftop name.  The Property ID must be enclosed in parentheses.  Example:

• McCluskey Chevrolet (48989)

MULTI FOLDERS

Sometimes clients or vendor websites provide data files that contain data for multiple rooftops.  These files must have a “Dealership Name” column identifying the rooftop.  Ops Specialist must map the rooftop names in this column to the property IDs - See Mapping Multi Store Files.

Multi folders should follow the same folder hierarchy structure above, but the “SPECIFIC ROOFTOP FOLDER” should end in “(multi)”.  Example:

• Step One Auto Group (multi)

Mapping Data

Single Store File

If the file you are loading contains data for a single store (or you must download multiple stores individually), you will load data to an individual property folder in Dropbox.

Multi Store File

If the file you are loading contains data for multiple stores, we can load the data into a (multi) folder in Dropbox but the file must contain a property alias column (typically named “Dealership Name", “Dealership”, or “Store”).  This column identifies the store names and we map those aliases to the property IDs.

The values in the property alias column must contain a unique value that would identify each specific property only.  
For example:

• Acceptable property aliases:  McLarty Daniel Ford, McCluskey Chevrolet, Milwaukee Ford North, 12345 [a property ID]
• Unacceptable property aliases:  Ford, Toyota, Ford North, Chevrolet East, 1, 10, 25
  *these are unacceptable values because they are too generic and could refer to any number of stores

1. Map the Property aliases - If you do not have access to Datagrip and Postgres, notify the Ops Specialist or Ops Coordinator and request that they map the property aliases.
2. Map the property aliases by adding new rows in the claripedia.property_aliases table for all stores in the file.
   Use the  +  button to add a new row.
   1. property_alias = the alias in all lowercase, no spaces, no punctuation
   2. property_id = the property ID to which you want the data mapped
3. Remember to click the Update button ⬆︎ to commit the changes!

Quality Checking Files

These are steps for Quality Checking a file that will be loaded through Dropbox.

1. Check the file format
   Acceptable formats are .CSV, .XLS, .XLSX
   If saving as a CSV - pick CSV UTF-8 Comma Delimited
2. Correct rooftop(s)?
3. Correct dates? Match with P. Month column on the Monthly Task board to make sure we are receiving all of the data we need.
   1. PARTIAL MONTH/MISSING DATES: If a file does not contain a complete month of data (full range of dates):
      1. DMS Sales - It is okay for a Sales file to be missing the 1st-10th of a month as some dealers' Accounting Departments spend the first 10 days finalizing sales from the previous month and do not start entering new sales until the 10th (may vary slightly).
   2. MULTIPLE MONTHS: If we are missing multiple months and a dealer does not send all of them:
      1. Do not mark the task status as Done or Completed (leave as "Waiting on...").
      2. Note in the conversation/updates which month(s) we received.
      3. Email the collect contact to confirm receipt of the file(s) and request the missing months. See Collect Data - File Problems section below.
4. Multi-Sheet (Tab) Excel files: If a file contains multiple sheets:
   1. SPLIT BY MONTH: If all sheets are for the same property (or all are a multi for the same group) AND are the same data vendor and type, you CAN load a file with multiple sheets. Each sheet will be processed. For example, dealers may split multiple months into separate sheets.
   2. SPLIT BY CAMPAIGN: If each sheet contains a separate campaign:
      1. If a Campaign column exists (and is mapped), you can load the file
      2. If a Campaign column does not exist and get_campaign_from_filename=true for this lead data supplier
      3. Separate the sheets into separate files (leaving the campaign name in each filename).
      4. Load the separate files.
      5. Email the collect contact to politely request that they send separate files for each store moving forward. See Collect Data - File Problems section below.
   3. SPLIT BY STORE:
      1. If a (multi) file is expected (we are loading to a multi folder) and each sheet contains a Dealership Name column, you can load the file.
      2. If we expect individual files and each sheet contains a separate store, you must:
      3. Separate the sheets into separate files.
      4. Load the separate files.
      5. Email the collect contact to politely request that they send separate files for each store moving forward. See Collect Data - File Problems section below.
5. Correct # of data columns?
   1. Compare to previous month in Data Archive to ensure the same columns are present as in the past. If missing a required or highly suggested column that was in previous months' files, email the dealer to request a revised file. If needed, explain the importance of having the missing data.
   2. If the file is a new file we are receiving for the first time, make sure all required columns are present. See Clarivoy Data Input Requirements.
6. Clean Things Up
   1. Delete any rows ABOVE the header row or summary rows below the data.
   2. Delete any blank columns.
7. Batching Files - VERIFY the # of rows (leads) in the file. Our processing DAGs (server jobs) can only handle up to 100,000 leads at a time in 1 file. 
   If a file contains more than 100,000 leads or sales:
   1. Split into multiple files, each of 100,000 rows or less
   2. Load the first file
   3. Leads:  Run the etl_leads_preprocessing_processing DAG in Airflow
      Sales: Run the etl_sales_preprocessing_processing DAG in Airflow
   4. Repeat steps a through c for each subsequent file (loading 1 file at a time and running both DAGs before loading another file).

Loading Data

1. Quality Check the File:  See Quality Checking Files
2. Special Instructions:  Pay special attention to any instructions in the task’s Notes column on the SA+TVA Ops Monthly Task board and follow them.  Also note any special instructions on the Vendor Details & Instructions board.
3. Name the file appropriately
   1. Sales:  Use the naming convention YYYY MM - Client Name - Sales
      OR for multi month files use  YYYY MM to MM    or    YYYY MM to YYYY MM
   2. Leads:  Use the naming convention YYYY MM - Client Name - Vendor Name
      OR for multi month files use  YYYY MM to MM    or    YYYY MM to YYYY MM
      Special exceptions include:
      1. Campaign Names in Filenames - For most Direct Mail, if there is no Campaign column, the campaign name will be specified in the filename. You must leave that campaign name at the end of the filename (after the vendor).
         For these vendors, in the lead_data_suppliers table, get_campaign_from_filename should be set to TRUE
         Example filename: YYYY MM - Client Name - Vendor Name - Campaign Name
      2. NO DATES - For Direct Mail or other leads where we do not know the actual lead date, we assume the first of the month. 
         In those cases, use: YYYY MM 01 - Client Name - Vendor Name - Campaign Name
         For these vendors, in the lead_data_suppliers table, get_date_from_filename should be set to TRUE
      3. EXACT DATE - If we know the exact send date for Direct Mail or Email Sends and there is no date column in the file, use the exact date in the filename.
         For example: Bill Marsh always tells us which date their Mailchimp email newsletter was sent out each month. If their "Platinum Customers" newsletter was sent out on 12/18/2023, the filename would be:
         2023 12 18 - Bill Marsh - MailChimp - Platinum Customers
         (where "Platinum Customers" is the name of the campaign)
4. Create Dropbox Property Folder:
   1.  If one does not already exist, create a property folder in Dropbox/Insyncing Data Drop/0-Leads     **ops improvement: to be automated in the future for Rocketlane Collect & Download data tasks
      1. If downloading for a single store (or you must download multiple stores individually), use the format:  Property Name (PROPID)
      2. If you are able to download a multi file and it contains a column to identify the store name, use the format:  Client Name (multi)
         1. Map the Property aliases - Notify the Ops Specialist or Ops Coordinator and request that they map the property aliases.  This maps the store names to the property IDs.  They will map the property aliases in the claripedia.property_aliases table for all stores in the file.
5. Leads only - Create Dropbox Vendor Folder:  Create a vendor subfolder inside the property folder.  Reference the Vendor Details board - Lead Data Supplier Name column.
6. Load the Data:  
   1. Sales - Load the data file inside the property folder.
   2. Leads - Load the data file inside the vendor subfolder.
7. Set the task status to Completed
   1. ✴️ PRO - Rocketlane Zapier Integration creates a new item in the SA+TVA Ops Monthly Tasks board.
      1. If the Monthly Task board item cannot be created by Zapier, an error message should appear in the task Chat explainign why.  Typically this is because the required fields are not filled in on the task's Additional Info tab (Method, Task Vendor, Data Type).

ETL Processes

The following Airflow DAGs pick up the files in the Insyncing Data Drop

• etl_leads_preprocessing_processing
• etl_sales_preprocessing_processing

The automated job to pull data from the above DropBox folders and load to our database runs every couple of hours (5 to 8 times per day), via the application AirFlow. Specifically, this data is loaded to our sales.leads_raw table (for leads) or sales.sales_raw table (for sales). After the sales or leads processing job is complete and data is filtered, the data is moved to sales.leads_standardized table (for leads) or sales.sales_standardized table (for sales). These tables can be viewed via the application DataGrip.

Errors related to Sales and Leads file processing will be broadcast in the #sales-leads-processing Slack channel. You must watch this channel and fix any errors that are broadcast as files will not be processed if they error here; The file in question will stay "stuck" in Insyncing Data Drop until the error is fixed. FOR troubleshooting #sales-leads-processing errors, see Troubleshooting #sales-leads-processing Errors

After the nightly matching job completes, the attribution results are available in our dashboard in the stage environment (only visible internally to Clarivoy). After attribution results are QA’d, we push the data to our dashboard in the production environment (visible to our clients).