SA + TVA Ops Monthly Tasks

Table of Contents

Task Creation Data Collection Methods Statuses API & SFTP Verify Sales - Automated Process Accounting Dates Investigating Issues with Sales Feeds DealerVault Lagging Feeds Downloads & Collects Collect Data - File Problems Data Locations Parent Properties Troubleshooting #sales-leads-processing Errors Verifying Data Ingestion: Preprocessing Boards Leads Preprocessing Sales Pre-Processing

Monday Board: SA + TVA Ops Monthly Tasks

Ops Specialist is accountable for the ingestion of all Sales and leads data. Customer Success Engineer provides backup with data ingestion needs and also is accountable for ensuring that data is mapped and processed correctly once it’s received.

Task Creation

Tasks are automatically created on the SA + TVA Ops Monthly Tasks board when they are marked as Status = Completed in Rocketlane.

Tasks should be separated or combined, representative of how the data is received.

All tasks with method=API or DV SFTP should have separate tasks for each property. We receive API data for each property separately. We receive DealerVault data for each property separately. Occasionally, multiple API keys or DV feeds go into 1 property, but in those cases, there should be just 1 task for the property as we are verifying the combined data at the property level.

We can combine tasks under the following circumstances:

If we Download a (multi) file for multiple properties, those are combined into 1 task with all property IDs listed in the task name.
If we receive a (multi) file via Collect or SFTP for multiple properties, those are combined into 1 task with all property IDs listed in the task name.
If a single Download login contains multiple properties which must be downloaded separately, those are combined into 1 task with all property IDs listed in the main task name. Subtasks are created for each store so we know which stores to download.
- If you are able to map the property aliases, you may use a single (multi) folder to load all files in (put that in the Processing Link column on the main task).

Data Collection Methods

Collect - We request files monthly from the client OR directly from the vendor and the file is emailed to us or uploaded to our secure server at clarivoy.com. Once uploaded by the client, a new item is created automatically on the Ops Task Board (+Send this File) board. Download the files by opening the task conversation updates, viewing the email and clicking the link to the sendthisfile.com download. IMPORTANT! Files expire after 10 days. Files should be processed the day they are received, unless after 4 PM (may be processed the next day).
Download - We obtain login credentials to the client dashboard for a particular vendor and export a CSV/XLSX file ourselves on a monthly basis. Instructions for each vendor are on the Vendor Details and Instructions Board.
Automated Report - Same as download, except we setup automated delivery of the report to datateam email box. Instructions for each vendor are on the Vendor Details and Instructions Board.
SFTP (Secure File Transfer Protocol): A secure way to send & receive data. Files are delivered to us (daily or monthly) over a Secure Shell data stream and accessed via an open-source client such as WinSCP, CyberDuck, or Filezilla. Instructions for each vendor are on the Vendor Details and Instructions Board.
DV SFTP - DealerVault SFTP. Sales records are sent from client's DMS to DealerVault. DealerVault sends sales files to our SFTP for these clients.
API (Application Programming Interface): A software-to-software interface. Our computers “call” their computers and request data be sent over on regular intervals, usually daily.
Special Task - These are usually Collect tasks for test/experimental/R&D data types. Do not hold up monthly processing of the client's SA data for a Special Task. (If not received, just move on without it.)

Statuses

New - When items come over from the Onboarding workflow, they will start as New status. ONLY when the Start date passes and we are processing their first month, they need to be linked to the client on the SA + TVA Ops Overview board and all information needs to be complete (Month, CSL, email+name+rooftop - if collect item, vendor, file type).
Waiting on XXXXXXX - Each month, all REQUIRED data collection tasks (Chat, CRM, Call Tracking, DMS) for all active properties start at Waiting on XXXXXXX (depending on the data type).
- ** If the CSL decides to deliver a month while we are still missing certain data (usually CT, Email, or TV), leave the task as “Waiting on ______”. We still want Collect emails to go out, in hopes of receiving that data later.
XXXX Not Received - Each month, all NON-REQUIRED data collection tasks (Email, Direct Mail, Special Tasks, TV) for all active properties start at XXXX Not Received (depending on the data type).
- Ops Overview processing will automatically progress to QA stage if these non-required data pieces are missing
Waiting on Report Delivery - Used for Downloads where the report must be delivered via email (you cannot download directly). After you request the report to be emailed, but before it arrives, you can use this status in the interim to note that you have requested the data.
Stuck - Stuck on an Internal related issue
CSL/Dealer Stuck - Waiting for CSL to engage with client for resolution or waiting on Dealer to respond
DealerVault Stuck - Stuck due to a DealerVault feed issue
CDK Stuck - Stuck due to a CDK feed issue
Vendor Stuck- Stuck due to a data issue and we are working with the vendor to resolve
Dealer Unresponsive -
- If dealer has not sent a Collect file by the 25th of the month, then the task is automatically set to Dealer Unresponsive by Make.com
- OR If we need dealer engagement on an issue (low lead or sales count, leads or sales stop after a certain date, need new login, DealerVault issue, etc.) and they have not responded by the 25th of the month, you can manually set the status to Dealer Unresponsive.
- Task stays in this status until the issue is resolved (for all subsequent processing months).
Done - Data has been loaded (for all data types except DMS)
DMS Done - Data has been loaded (for DMS only)
Skip -
- Advertising not running for the current month (TV ads not running on a particular station/cable provider, Email or Direct Mail not running that month for a particular store). TV provider, vendor, or client will notify Ops of this.
- OR can be used by a CSL if they opt to "skip" chasing a piece of Collect data for a month (usually due to a client not turning it in by the middle or end of a month, but the CSL wants to push their data to PROD anyway). If this option is chosen, we will never come back for that month's data again.
- If you want us to keep chasing a piece of data but want to move the client's monthly processing along without the data, DO NOT CLICK SKIP. [If you click Skip, collect emails will stop and we will not attempt to collect this month's data for that particular task anymore in the future.] Instead follow these steps:
  - Leave the task as "Waiting on XXXX"
  - Go to Ops Overview and find the Ops Overview item
  - In the conversation updates, make a note "Approve moving on without XXXXXX data this month" and tag Ops
  - Set Processing Status to Prelim QA
  - Ops will perform Prelim QA and move the item along without that piece of data. Meanwhile, the Ops Monthly Task board will continue to chase the data by sending reminders to the collect contact. If the data is not received this month, it will remain in the "Processing Months" column next month and the collect contact will be asked for 2 months of data (last month + current month).
Not Running TV - The client is not running any TV at all (all stations) for a specific month. CSL will inform Ops when to use this status.

API & SFTP

Leads or sales data that is ingested through API or via SFTP goes directly to our database. You will not find API data in the Dropbox folders. For data locations, see Where Data is Stored.

Verify Sales - Automated Process

For full details, see the Verify Sales Confluence documentation.

Every morning, the verify_sales_counts DAG runs. This DAG does NOT run the last 3 days of the month, to avoid interference with Board Resets. Additionally, it would be too late in the month for a dealer's data to make it to PROD anyway if their Sales task was verified.
For each task in the DMS group:
1. If the Timeline date is blank, the DAG assumes the Timeline as specified in Timeline Default Date in the QA Criteria Google Sheet If the Timeline column date has arrived, the DAG will check sales for that particular task. (If the Timeline date has not arrived, the task is skipped)
2. The DAG will only check tasks in specific statuses (Monthly Task Statuses to Check in the QA Criteria Google Sheet)
3. The DAG will only check tasks with specific Methods (Monthly Task Methods to Check in the QA Criteria Google Sheet)
4. The task gets Sales counts for each Property ID specified in the task name, and for each processing month specified in the P Month column. Sales counts for all Property IDs and all months must meet the following criteria to pass the automated check:
  1. Minimum Months to Compare - At least X number of months' sales must exist
  2. Comparison % - The DAG will get a historical average (at least Minimum Months to Compare and up to a max of Maximum Months to Compare). Total sales count for the processing month and property ID being checked must fall between Low Comparison % and High Comparison % when compared to the past average.
  3. Full Daterange % - Specifies the % of days in each month which must have 1 or more sale (to ensure we do not have a lot of days within a month with 0 sales)
  4. Missing Days in a Row Threshold - Specifies the maximum number of days in a row that can exist with 0 sales. If there exists a period of consecutive days with 0 sales that is greater than this number of days, the check will fail.
The Task Status is set based on the result:
1. If the sales counts failed any of the checks, the task is placed into Waiting on Manual Review See Manual Review process below.
2. If the sales counts passed all checks, the task is placed into DMS Done
Manual Review:
1. Verify receipt of all data for API, SFTP & DV SFTP in Looker using the link in the Processing Link column. Processing Links come from the following boards:
  - Leads - Leads QA 2
  - Sales (DMS) - Sales Counts Verification
2. If verifying manually (not using the Processing Link):
  1. Set the property name filter
  2. Set date filters to the previous 3 complete months
3. Verify that the # of Leads or Sales is in line with previous months and there are no large gaps or large number of days with 0 leads/sales. If there is a significant difference, see Researching QA Issues in the SA+TVA Ops Overview Monthly Processing.
4. SALES TASKS: Action based on findings:
  1. Date Range and Lead Counts are Consistent with Previous Month Trends - Set task status to DMS Done.
  2. Very Low or Zero Sales Count = Investigate to see if there is an issue with the feed. See Investigating Issues with Sales Feeds
  3. DMS Full Date Range but Low Total Sales Counts = If sales numbers are not consistent with a previous month, but there is a full range of dates (consistent with previous month trends), tag the CSL to notify them of the issue and then set the task status to DMS Done. Per AG/HKM.
  4. DMS Timeline date has not passed = keep waiting and set task status back to Waiting on DMS
  5. DMS Timeline is blank and it is before the 10th of the month = keep waiting and set task status back to Waiting on DMS
  6. DMS Timeline date has passed and we do not have a full range of dates = Investigate to see if there is an issue with the feed. See Investigating Issues with Sales Feeds. Tag the CSL to explain the issue and include a screenshot of the data. Task Status depends on what you find in the investigation (DealerVault Lagging Stuck, Vendor Stuck, Stuck [internal Clarivoy issues]).
  7. DMS Timeline date is blank, it is the 10th of the month or later, and we do not have a full range of dates= Investigate to see if there is an issue with the feed. See Investigating Issues with Sales Feeds. Tag the CSL to explain the issue and include a screenshot of the data. Task Status depends on what you find in the investigation (DealerVault Lagging Stuck, Vendor Stuck, Stuck [internal Clarivoy issues]).
5. LEADS TASKS: Action based on findings:
  1. Date Range and Lead Counts are Consistent with Previous Month Trends- Set task status to Done.
  2. Very Low or Zero Sales Count = Investigate to see if there is an issue with the feed. See Researching QA Issues
  3. Full Date Range but Low Total Lead Counts = If leads numbers are not consistent with a previous month, but there is a full range of dates (consistent with previous month trends), tag the CSL to notify them of the issue and then set the task status to Done. Per AG/HKM.
  4. API
    1. Incomplete Date Range and 1st or 2nd of the month = Keep waiting. Set status back to "Waiting on XXX".
    2. Incomplete Date Range and 3rd or later= Investigate to see if there is an issue with the feed. See Researching QA Issues
  5. SFTP Incomplete Date Range - Check Task Notes and/or Vendor Details board Integration Instructions to find the schedule for when the data is sent, and when the SFTP DAG runs to pick up the data.
    1. If you need to keep waiting, set the task status back to "Waiting on XXX".
    2. If we should have received the data by now, see Researching QA Issues

Accounting Dates

For DMS tasks that are not a COLLECT, we ask a dealer during Onboarding for their Accounting Date. The Accounting Date represents the date by which the dealer's accounting department has all prior month deals finalized each month. For example, if the Accounting Date is the 6th, the dealer is saying "Our accounting department will have all prior month deals finalized by the 6th of the month." See the Accounting Survey process for more details.

A Make.com scenario adds two days to the Accounting Date and place that date in the Timeline date column. (It takes approx. 2 days for data to flow from the dealer's DMS, through DealerVault/CDK/Tekion Data Warehouse, to Clarivoy)

The Timeline is the date on which we begin verifying the dealer's sales.

If a dealer has provided a specific AccountingDate (the date their sales will be finalized by each month), we will not begin checking their sales until 2 days after that date.
If a dealer has not specified an AccountingDate (Indy Auto Man has not), we assume the 5th is the AccountingDate. This means we do not begin checking their sales until the 7th.

Investigating Issues with Sales Feeds

If DealerVault, see Researching QA Issues: DV SFTP
If CDK, see CDK API: Not Receiving Sales
If Tekion or other SFTP, see Researching QA Issues: SFTP

DealerVault Lagging Feeds

This portion has moved to HelpJuice. See https://clarivoy.helpjuice.com/dealervault-lagging-feeds

Downloads & Collects

Download file
Find Task - If a collect, search the SA+TVA Ops Monthly Task board by the sender's email address. This is the most likely way to find the task.
1. If no results are returned, try searching by dealership name and/or vendor name
2. If it still does not come up, check these boards instead: (It may be helpful to use Monday's Search Everything feature by clicking the magnifying glass icon in the top right corner. Search by the uploader's email address.)
  1. ODA Boards
    1. Cox ODA boards
    2. Trader ODA
    3. KBB ODA
    4. Kijiji ODA
    5. Edmunds ODA
  2. Rocketlane Tasks
Quality Check File - See Loading Sales and Leads through Dropbox
Set the task Status to Done or DMS Done on the SA+TVA Ops Monthly Task board.
Errors related to Sales and Leads file processing will be broadcast in the #sales-leads-processing Slack channel. FOR troubleshooting #sales-leads-processing errors, see the Troubleshooting #sales-leads-processing Errors section below 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.

Collect Data - File Problems

If there is an issue with a file that is received and we need to communicate with the dealer to request a revised file:

Set either the Data required email column to one of the following statuses, depending on the issue:
1. Ops Stuck - Email Contact
2. Ops Stuck - Email Contact, CC CSL
3. Ops Stuck - Missing Columns
4. Ops Stuck - Wrong Dates
A Make.com scenario (Monthly Tasks - Email Dealer File Problems) will:
1. 🚦 Create a draft in the Datateam email box in the Drafts folder, addressed to the dealer, with a template for requesting a revised file. The template will have the following information filled in:
  1. To: Main POC Email
  2. CC: The Monday board item's pulse. If "CC CSL" was chosen, the CSL will also be CC'd.
  3. Subject: File Type (and if CRM Leads, the CRM name) and Dealer Name
  4. Body: Addressed to Main POC First Name
  5. Body: File Type (and if CRM Leads, the CRM name) and Dealer Name
  6. Body: If "Missing Columns" or "Wrong Dates" was chosen, the template will be customized to the issue at hand.
2. 🚦 Set the Data Required Email column back to its previous value (usually "Send [DATATYPE] Email").
Go to the Drafts folder in the Datateam email box and locate the draft.
Fill in the template blanks, explaining the issue.
Send the email.
In the Amazon S3 Upload file bucket, place the bad file in the STUCK folder.
Typically, with most collect file issues, we will leave the monthly task Status as "Waiting On [DATATYPE]" and the Data Required Email column as "Send [DATATYPE] Email" so the dealer continues to receive the collect reminder emails every few days until they send us good data.
1. If, for some reason, you do not want the dealer to keep receiving these email reminders, change the task status to CSL/Dealer Stuck or Vendor Stuck, depending on the contact. You may wish to do this is if the file contained the correct data but you had a question about it before you could load it - for example: a Call Tracking file is in a different format than previous months and you suspect the dealer changed Call Tracking Vendors. You may need to ask what the new Vendor name is in this case, but do not want the dealer to continue to receive the reminder emails.

Data Locations

See Loading Sales and Leads through Dropbox

Parent Properties

See Loading Data to a Parent Property

Troubleshooting #sales-leads-processing Errors

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.

Unrecognized property alias: ""
- Problem: Rows exist in the file with blank values in the property alias column (whatever column is mapped to *property, usually Dealership Name or Dealership)
- Solution:
  - If it is only a few rows, simply delete those rows
  - If there are a large number of rows (or all rows) with blank aliases, contact the vendor and ask them to send a revised file with values filled in. Place the task in STUCK status and delete the offending file from IDD.
Unrecognized property alias: "[SOMEVALUE]"
- Problem: The property alias specified is not mapped in the claripedia.property_aliases table
- Solution: Add the property alias to the claripedia.property_aliases table
duplicate key value violates unique constraint “leads_raw_source_document_source_row_number_key”
- Problem: A file has already been loaded previously with the exact same filename. Solution: Check that you named your file correctly.
  - Are the dates in your filename correct? Fix the dates if necessary.
  - If it is a revised file, add "REVISED MM-DD-YYYY" to the end of the filename. Make sure the original file was deleted if necessary!
  - If it is a reload due to mapping corrections, add "RELOAD MM-DD-YYYY" to the end of the filename. Make sure the original file was deleted if necessary!
- Problem: This sometimes happens if a file gets "stuck" in IDD due to having blank sheet(s). The sheet(s) with actual data will be processed and ingested, then the DAG will fail due to an Excel sheet being entirely blank. Solution: Verify that the data from the sheets with data got ingested into sales_raw or leads_raw by querying the appropriate table using the source_document (remember excel file source_document values have _SheetName after the .xlsx or .xls extension!). If all actual data from the file was ingested, simply delete the empty sheets and move the file to the appropriate Data Archive folder.
[COLUMNNAME] column could not be identified. this is a required column.
- Problem: A required column is missing from the file. Solution: If download, fix the report and redownload the data. If a Collect or SFTP, email the data source and request they add the missing column. Place the task in Stuck status. Delete the offending file from the IDD folder.
Problem: A required column exists in the file, but is not mapped to the correct target_field in the mapping tables Solution: Map the column in one of the following tables:
- leads_schema_mapping
- leads_schema_mapping_custom_headers
- sales_schema_mapping
- sales_schema_mapping_custom_headers
Header row has duplicate mapping: "new_or_used".
- Problem: The file contains multiple columns which are mapped to the same target_column in the table.
- Solution: Correct the mapping issue, mapping one of the columns to <null> in the appropriate _custom_headers table OR remove one of the duplicate columns from your file.
Failed to add to pgSession. '[COLUMNNAME]' is an invalid keyword argument for leads_raw
- Problem: A column is mapped in one of the mapping tables to an invalid target_field. Check that the target_field is a valid column name for the table you are ingesting into (for example, you may have accidentally mapped a column to 'zip' instead of 'zip_code').
- Solution: Correct the mapping in one of the mapping tables.
ERROR 23848: Header row could not be identified.
- Problem: Blank or extraneous rows exist above the header row. Solution: Delete those rows & reprocess the file.
- Problem: At least 3 columns must be mapped in leads_schema_mapping or sales_schema_mapping for the processing DAGs to be able to process a file. Solution: If the majority of the columns are mapped in a _custom_headers table, you may need to move some entries to the regular leads_schema_mapping or sales_schema_mapping table instead in order to get the file to process!
- Problem: The file may not be saved in the proper format that matches its extension. If CSV, the file could be in a bad format (not a proper CSV format). Open in Excel. If you see this message, it means the file is not formatted properly:
  
  Note it says "text (.txt) format". Solution: Resave the file (Save As) in CSV UTF-8 (Comma delimited) (.csv) format
Postgres writing failed for document: [FILENAME] The (psycopg2.errors.CheckViolation) new row for relation "leads_raw" violates check constraint "max_document_size"
- Problem: The file is too large and has too many rows.
- Solution: Split into smaller chunks of 50,000 rows or less per file.

Verifying Data Ingestion: Preprocessing Boards

Check the following 3 Looker boards daily to verify proper loading of Leads, Sales and TV spot log files. Reference the above section Researching QA Issues for more details on researching errors.

Leads Preprocessing

Check Daily

Set dates to the past 3 days
Check Recently Loaded Files section for errors:
Action depends on the number of errors:
1. If a small number or % of rows are filtered out, it could be a handful of bad leads (bad data entry on the client's end) - no action needed
2. If a large number or % of rows are filtered out:
  1. Reference the Source Document name to determine if it is a file we loaded or an SFTP file.
    1. Files we loaded (Downloads, Automated Reports & Collects) will use our standard naming convention. Check the file in Data Archive and repull the data if necessary.
    2. Files loaded from SFTP typically have a property ID # somewhere in the filename and will not follow our standard convention (YYYY MM - PropertyName - VendorName). Check the file in Data Archive and engage with the client, vendor and/or CSL if necessary (if data is missing or does not meet our requirements).
Action also depends on the type of errors:
1. Invalid/Empty Date = the row(s) did not have a date or the date format was invalid
  1. Check the file in Data Archive to see if the row contains a date. Make sure the date is in a format readable by our system (dates cannot contain days of the week - e.g. Thu 6/23/2022. If this is the case, you must use Excel's Text to Columns function or Search & Replace function to remove the day of the week from all values.)
  2. If the date format appears valid, check file mapping in DataGrip - check leads_schema_mapping and leads_schema_mapping_custom_headers
  3. Email Opens lead files will typically have a large % of rows filtered out due to Invalid/Empty Date, as the system only loads rows with dates. Non-click/Non-open rows will not have an Opened Date. These are okay - no action needed.
2. Unparsable Date = the row(s) had a date format that our system cannot read OR the date/timestamp was in a timezone that does not match the property's timezone in the DMUI.
  1. If the date/timestamp is in a format similar to this: 2023-05-31T17:29:00-05:00 Reference this chart: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones to determine what Timezone that timestamp is for. In the example above, -05:00 at the end denotes the UTC timezone US/Eastern [EST]
    1. Check the property in the DMUI and ensure the timezone is set correctly.
    2. If timezone was incorrect on the property, fix it and try reloading the file.
    3. If timezone was correct on the property, the data in the file may be in the wrong timezone. If it is a download, check region/timezone settings in your user profile in the vendor system. If it is a collect, email the contact and/or vendor for assistance in correcting the data.
3. No PII = the row(s) did not have enough PII to meet the matching threshold
  1. Verify that the file in Data Archive has the appropriate PII columns (See Clarivoy Data Input Requirements for general column requirements, then check Vendor Details & Instructions board for vendor specific columns). If columns are missing, redownload the file (if a Download or Automated Report) or email the client/vendor for a revised file (if a Collect).
  2. Check file mapping in DataGrip - check leads_schema_mapping and leads_schema_mapping_custom_headers tables

Sales Pre-Processing