Data Loading Introduction
Welcome! You have a very important role in the company! The data we collect makes Clarivoy’s products and services possible. All of the data coming into the company starts here, with the Data Team. So, it’s extremely important we do things right the first time. Careless mistakes by just one person on our team can turn into time-consuming issues for several people when the data gets farther down the line.
To make sure everything runs smoothly, here’s our 3 most important rules to live by:
- Accuracy over Speed! We like speed a lot, and we really appreciate when you get the job done quickly – but we absolutely LOVE accuracy even more. Our job is to get the data off on the right foot!
- Attention to Detail! There are tons of tiny details in our world, so be SURE to pay careful attention and double check everything! Follow the protocols, stay organized, be as thorough as you can be, dot your I’s and cross your T’s.
- Document Everything Thoroughly! We handle a lot of information, and a lot of different people in the company rely on us to keep them updated on the status of things. We use Monday.com to keep everything in order. It’s very important that you document everything and leave good, specific, thorough notes!
And here are some other great reminders on the job:
When you download data, verify these 4 things, every file, every time:
- Correct rooftop?
- Correct dates?
- Correct # of data columns?
- Dropped off in the correct folder?
- Less than 100,000 leads per file? If more, see notes above on Batching Leads files.
Remember your workflow:
- Download file
- Quality check file (Compare to previous month in Data Archive – see items above!)
- Rename file (copy & paste title from Data Archive file)
- Drop the file in “Insyncing Data Drop”
- Mark Monday (change Status to Done or DMS Done)
- Delete any rows ABOVE the header row
- When saving as a CSV - pick CSV option closest to the top (UTF-8 Comma Delimited)
- Make sure your file is named correctly
- Keep your computer desktop extremely organized (so you don’t get incoming files mixed up with each other)
- Login to ELEAD CRMs every two weeks (so we don’t get locked out)
- Pay close attention to special instructions on Monday items (combined rooftops, export limits, etc.)
- If you have any issues along the way, please remember we LOVE questions. There are NO dumb ones. Better safe than sorry! Shoot us any questions in the #operations Slack channel!
Data Loading Timeline
- Downloads: Work to obtain leads data retrieved via downloads will begin on the 1st of each month OR the first business day if the 1st is on a weekend. All downloads are generally completed within two days.
-
Collects: Leads & sales data retrieved via collects are processed simultaneously with downloads, as soon as the data arrives. As a rule, if a file is received before 12 noon (on a business day) - it will be processed that day. If a file is received after 12 noon - it will be processed by 12 noon the following business day.
- Collects that are sales data will be processed first (so that 7 days Live Ramp processing period can begin ASAP)
- Collects that are TV Spot Logs will be prioritized over leads data.
- The collect request schedule is as follows and is on a automated schedule in Monday.com (they will go out automatically unless the data is marked as received):
- If Collect Type is SALES or SALES & LEADS.......requests go out on 5th, 8th, 11th, & 14th
- If Collect Type is LEADS.......requests go out on 1st, 4th, 7th, & 10th
- If Collect Type is TV SPOT LOGS.......requests go out on 1st, 4th, 7th, & 10th
- If item is set to ROUND B WAITING ON SALES.......requests go out on 10th, 13th, 16th, & 19th (these dealers have specifically asked us to give them more time to finalize sales each month)
- SFTP: Most leads & sales data received via SFTP has automated jobs to pull the data out and ingest it at various intervals from the 1st to the 5th of the month (depending on when the data is scheduled to be sent by the client or vendor). A few select files are pulled out manually and processed (that happens within a day of delivery and is managed via the Monday.com SA Processing board).
- API: Leads data received via API is processed daily, as API calls are programmatically made.
Approximate Data Processing Timeline
The matching job for Television Attribution consists of only one job (daily_TVA) and runs every night, completing at approximately midnight.
Data Ingestion: What, Where & How
| TYPE OF DATA: | WHERE WE GET IT FROM: | HOW WE GET IT: |
| Website Traffic | Dealer website | Clarivoy Tracking Code |
| SEM and Display Leads | Dealer website | Clarivoy Tracking Code (if traffic is tagged properly using UTM) |
| Sales Data | Client OR via DMS by way 3rd party vendor partner DealerVault | Collect from client, SFTP (manually via client), Automated SFTP |
| CRM Leads | Client OR directly from the CRM Vendor | Collect from client, Download from vendor dashboard, Automated SFTP |
| Call Tracking Leads | Client OR directly from call tracking Vendor | Collect from client, Collect from vendor, Download from vendor dashboard, API |
| Email Leads |
Client OR directly from email Vendor via "opens" reports |
Collect from client, Collect from vendor, Download from vendor dashboard |
| TV Spot Logs | Client OR directly from TV Agency | Collect from client, Collect from TV Agency |
| Website Chat Leads |
CRM data OR directly from chat Vendor (select preferred vendors ONLY) |
(See CRM Leads above), API |
| Direct Mail Leads |
CRM data OR through call tracking (if campiagn has dedicated phone #) |
(See CRM Leads above), (See Call Tracking Leads above) |
| 3rd Party Auto/Marketplace Listing Site Leads | CRM data OR vendor web traffic via our tag (select vendors ONLY) | (See CRM Leads above), Clarivoy Tracking Code |
| Webform Leads | CRM data | (See CRM Leads above) |
| Collects are requested each month via email then uploaded to our secure site by client or vendor | ||
Data: Where Source Data Is Stored
See Data Team Charts
| METHOD | WHERE SOURCE DATA IS STORED: | WHERE DATA IS STORED AFTER LOADING: |
| COLLECT | Files uploaded to https://upload.clarivoy.com are stored in the Amazon S3 Web Bucket. | Files are copied to Dropbox (see below). Once files are copied to Dropbox, Ops Team moves files to the DONE subfolder. |
| DOWNLOAD | Data is exported from vendor website. | Files are copied to Dropbox (see below). Once files are copied to Dropbox, original files are deleted on the local machine. |
| AUTOMATED REPORT | Received via email to datateam@clarivoy.com. | Files are copied to Dropbox (see below). Once files are copied to Dropbox, the emails are archived in the AA Automated Reports folder/label |
| SFTP | Clarivoy SFTP Server. See vendor- or dealer-specific folder, then /to_clarivoy. | Files are copied to Dropbox (see below). Once copied to Dropbox, the original data files get moved to the /to_clarivoy/done subfolder. |
| DV SFTP | Clarivoy SFTP Server/dealervault/to_clarivoy. | Once loaded directly into sales_raw, the original data files get moved to the /to_clarivoy/done subfolder. |
| SCRAPER | Google Cloud Storage bucket [unknown] | Google Cloud Storage bucket wall-e-archive |
| CDK API | DAG writes data to cdk_raw table | DAG transfers all new/updated rows from cdk_raw to sales_raw each time it runs |
|
Purple Cloud API CallRevu API Marchex API (TBD July 2024) |
DAG writes data to a Treasure Data table | DAG transfers all new rows from Treasure Data to leads_raw each time it runs |
| Other Leads APIs | Data is written by the API DAG directly into leads_raw. There are no source files. | |
| Dropbox | Dropbox: Data is loaded into the appropriate Insyncing Data Drop folder. | Once ingested by the ETL DAGs, data gets move to the appropriate Data Archive folder |
Data Requirements
This has moved to the Clarivoy Data Input Requirements document.
Existing Client Data Changes
New Vendors for an Existing Client
New vendors for an existing client should go through the same process as Vendor Changes.
This section has moved to the HelpJuice article Vendor Changes.
Vendor Changes for an Existing Client
If a client changes a vendor:
IF we already have everything we need for the new vendor (login credentials, collect contact details, API key):
- Add new item for the new vendor, ensuring to complete all fields (including contact info for Collects) and linking to the correct Ops Overview item(s).
- Move the previous vendor item on the Monthly Task board to the Inactive group!
IF we do not have everything we need for the new vendor:
This section has moved to the HelpJuice article Vendor Changes.