Sales Deduping Process
In the past (prior to 2023), our Sales ETL processes would dedupe Sales based on same VIN, same Sale Date, same Property ID. If we received the same sale a 2nd (or 3rd or 4th…) time, the first sale is what remained and all subsequent versions were filtered out as “Duplicate Sales”.
Our systems no longer “dedupe” sales in the traditional method, but instead we take in Sales Row Updates. With the Sales Row Updates process, if we receive the same sale multiple times, the newest version is retained and all previous versions are filtered out as “Duplicate Sales”.
Sales Row Updates
We receive Sales Row Updates from the following integration methods:
- DealerVault SFTP
- Tekion SFTP
- CDK API
- Outsell SFTP
- Wall-E Scraper - Every day we pull the previous 5 days and perform “Sales Row updates” using the newest data.
- Collect Files (rare) - If a collect file is loaded containing a matching ("duplicate") sale that was in a previous collect file (already exists in our Sales tables), then the existing row in Sales Standardized will be updated with the data from the newest version of that sale.
Sales Row Updates functions as follows:
- When a sale row is received from the vendor, the row goes into Sales Raw.
- The etl_sales_preprocessing_processing Sales Processing DAG checks to see if the sale already exists in Sales Standardized based on:
- Same VIN
- Same Sale Date
- Same Property ID
- The next action is based on the results of the check:
-
If the Sale DID exist in Sales Standardized (same VIN, same Sale Date, same Property ID)
- The existing row in Sales Standardized will be updated with any changed values from the newly received sale row
- Any previous sales in Sales Raw are filtered out (added to sales.sales_filtered) with reason="DB Duplicate"
- If the Sale DID NOT exist in Sales Standardized, a new sale row is created in Sales Standardized.
-
If the Sale DID exist in Sales Standardized (same VIN, same Sale Date, same Property ID)
Example 1 - Email Address Update:
- Email address is blank on a sale when we first receive it. The dealer updates the sale later and adds the Email address.
- Sales Raw would contain two rows:
- the original row with no Email address
- the newly updated row with the Email address.
- Sales Standardized would contain one row with all values matching the newly updated row (with the Email address added).
Example 2 - Front Gross Updates:
- We receive a sale from a dealer. Later, the dealer adjusts the Front Gross value. Then at another future date, they adjust the Front Gross again.
- Sales Raw would contain three rows:
- the original row with the original Front Gross value
- the first update with the first updated Front Gross value
- the second update with the second updated Front Gross value
- Sales Standardized would contain one row with all values matching the second updated row.
Reasons for Duplicate Sales
- If a dealer changes the Sales Date on a Sale, we have no method of identifying that this is an update and we will end up with duplicate sales on different dates.
- If a dealer changes a VIN on a Sale, we have no method of identifying that this is an update and we will end up with duplicate sales on different dates.