Background

Unclassified Sales exist when a dealer does not clearly identify if a sale is New or Used in the New/Used column in their sales data (Collect file, Download file, DealerVault data, CDK API, Tekion SFTP, other integration, etc.).

Standard Acceptable Values for New or Used

Our system can parse the following values in the New/Used column:

Any other values in the New/Used column will result in a sale being “unclassified”.  See Automated Process for how these sales are handled.

This code logic can be found in: https://github.com/Clarivoy/bbq-etl/blob/master/sales/scripts/sales_data_processing.py#L526

Automated Process

Beginning in 2023, we have automated classifying the New/Used values of sales that have non-standard values in the New/Used field (see above).  

sales_data_processing has been modified so that Sales whose New or Used value is non-standard (see above):

IF lower(source_document) does not contain ‘unclassified'
AND  lower(source_document) does not contain ‘order’
AND lower(source_document) does not contain ‘service’
AND vehicle_year >= current year [year of now]:
newrow['new_or_used_clean'] = 'new'

ELIF lower(source_document) does not contain ‘unclassified'
AND  lower(source_document) does not contain ‘order’
AND lower(source_document) does not contain ‘service’:
newrow['new_or_used_clean'] = 'used'

ELSE:
newrow['new_or_used_clean'] = 'unclassified'newrow['new_or_used_clean'] = 'new'

For further details, see the full process documentation in Confluence: Sales New/Used Classification

Manual Processes

Prior to the automated process described above:

• If a sale was unclassified, the New/Used value in Sales Standardized would read “unclassified” and the sales would show as “Unclassified” on the Executive Summary page in the DMUI.
• Weekly on Mondays, the CS Specialist would run a Looker report of unclassified sales, export the data, manually classify each sale according to the vehicle year, then provide the file to the Ops Specialist (in a Ticket on the Tickets board) to reclassify in DataGrip (see Manual processes below).
• Eventually, Charlie developed the below PostgreSQL statements so that the CS Specialist would not need to manually review unclassified sales each week.  This has since been replaced by the automated process described below.

Manual Process for Bulk Updates According to Year

Run these scripts in order in DataGrip.

First, query to determine how many rows you will be modifying. If the number of results is very large, STOP and investigate. Ask Engineering if it is okay to proceed with a large number of sales row updates (>1,500).

If the number of results looks okay, then proceed to run statements 1, 2 and 3 in "Update Unclassified".

-- =======================================================================
-- QUERY UNCLASSIFIED
-- =======================================================================
-- 1. FIND WHERE new_or_used=B2B reason (value should be in sale_type instead)
SELECT source_document, sales_date, property_id, vin, new_or_used, sale_type, reprocess_file, vehicle_year, vehicle_make, vehicle_model, delete_row from sales.sales_raw
where TRIM(UPPER(new_or_used)) IN ('FLEET','F','WHOLESALE','W','WS','COMMERCIAL','DEALER TRADE', 'TRADE', 'X', 'DT', 'DEALERTRADE')
and   (TRIM(UPPER(sale_type)) NOT IN ('FLEET', 'F', 'WHOLESALE','W','COMMERCIAL','DEALER TRADE', 'X') or sale_type is null)
and    created_ts >= CURRENT_DATE - INTERVAL '30 days';

-- 2. QUERY CDK RAW FOR UNCLASSIFIED SALES --
select original_json::json->>'deal.fiDealType' as "deal.fiDealType", new_or_used, vehicle_year, original_json::json->>'deal.dealType' as "deal.dealType", sale_type, * from sales.cdk_raw
where upper(new_or_used) not in ('NEW','USED', 'N', 'U', 'NEW VEHICLE', 'USED VEHICLE')
and created_ts > (now() - interval '10 DAY');

-- 3. QUERY SALES RAW FOR UNCLASSIFIED SALES EXCLUDING CDK, SERVICE, ON ORDER --
select new_or_used, vehicle_year, sale_type, * from sales.sales_raw
where upper(new_or_used) not in ('NEW','USED', 'N', 'U', 'NEW VEHICLE', 'USED VEHICLE')
AND vehicle_year !='' AND vehicle_year is not null
AND lower(left(source_document, 3)) != 'cdk'
AND lower(source_document) not like '%on order%'
AND lower(source_document) not like '%service%'
AND created_ts > (now() - interval '10 DAY');

-- =======================================================================
-- UPDATE UNCLASSIFIED
-- =======================================================================
-- 1. FIX WHERE new_or_used=B2B reason (value should be in sale_type instead)
UPDATE sales.sales_raw
    SET new_or_used = sale_type, reprocess_file = true,
        sale_type = CASE
                    WHEN TRIM(UPPER(new_or_used)) IN ('FLEET','F') THEN 'FLEET'
                    WHEN TRIM(UPPER(new_or_used)) IN ('WHOLESALE','W','WS') THEN 'WHOLESALE'
                    WHEN TRIM(UPPER(new_or_used)) IN ('C','COMMERCIAL') THEN 'COMMERCIAL'
                    WHEN TRIM(UPPER(new_or_used)) IN ('X', 'DT', 'TRADE', 'DEALERTRADE', 'DEALER TRADE') THEN 'DEALER TRADE'
                    END
where TRIM(UPPER(new_or_used)) IN ('FLEET','F','WHOLESALE','W','WS','COMMERCIAL','DEALER TRADE', 'TRADE', 'X', 'DT', 'DEALERTRADE')
and   (TRIM(UPPER(sale_type)) NOT IN ('FLEET','F','WHOLESALE','W','COMMERCIAL','DEALER TRADE', 'X') or sale_type is null)
and    created_ts >= CURRENT_DATE - INTERVAL '30 days';

-- 2. UPDATE UNCLASSIFIED SALES IN CDK RAW AUTOMATICALLY --
UPDATE sales.cdk_raw
SET reprocess_file = TRUE,
    new_or_used = CASE
     WHEN vehicle_year >= DATE_PART('Year', now())::varchar THEN 'New'                           --4 digit >= this year
     WHEN vehicle_year = right(DATE_PART('Year', now())::varchar,2) THEN 'New'         --2 digit this year
     WHEN vehicle_year = (right(DATE_PART('Year', now())::varchar,2)::int + 1)::varchar THEN 'New'   --2 digit next year
     ELSE 'Used'
     END
WHERE upper(new_or_used) NOT IN ('NEW','USED', 'N', 'U', 'NEW VEHICLE', 'USED VEHICLE')
AND created_ts > (now() - interval '10 DAY');


-- 3. UPDATE UNCLASSIFIED SALES IN SALES RAW AUTOMATICALLY EXCLUDING CDK, SERVICE, ON ORDER --
UPDATE sales.sales_raw
SET reprocess_file = TRUE,
    sale_type = CASE           -- Fix where Sale Type was actually put in New/Used
        WHEN upper(new_or_used) IN ('F', 'FLEET') THEN 'Fleet'
        WHEN upper(new_or_used) IN ('W', 'WS', 'WHOLESALE', 'DEMO') THEN 'Wholesale'
        WHEN upper(new_or_used) IN ('COMMERCIAL') THEN 'Commercial'
        WHEN upper(new_or_used) IN ('X', 'DT', 'TRADE', 'DEALERTRADE', 'DEALER TRADE') THEN 'Dealer Trade'
        ELSE sale_type
    END,
    new_or_used = CASE
        WHEN vehicle_year >= DATE_PART('Year', now())::varchar THEN 'New'                           --4 digit >= this year
        WHEN vehicle_year::int = right(DATE_PART('Year', now())::varchar,2)::int THEN 'New'         --2 digit this year
        WHEN vehicle_year::int = (right(DATE_PART('Year', now())::varchar,2)::int + 1) THEN 'New'   --2 digit next year
        ELSE 'Used'
     END
WHERE upper(new_or_used) NOT IN ('NEW','USED', 'N', 'U', 'NEW VEHICLE', 'USED VEHICLE')
AND lower(left(source_document, 3)) != 'cdk'
AND lower(source_document) not like '%on order%'
AND lower(source_document) not like '%service%'
AND vehicle_year !='' AND vehicle_year is not null
AND created_ts > (now() - interval '10 DAY');

Manual Process (Individual Sales)

To manually reclassify individual sales, run these statements in this order (1, then 2, then 3):

---------------------------------------------------
-----------RECLASSIFYING SALES---------------------
---------------------------------------------------
--1. query sales_raw by VIN & property ID--
select * from sales.sales_raw
where vin = '1C4HJXDG4NW191328'
and property_id = '50066';
-- then grab the source_document value for the next query --

--2. update new_or_used field--
update sales.sales_raw set new_or_used = 'NEW'
where vin = '1C4HJXDG4NW191328'
and property_id = '50066'
and source_document = '2022 03 - Castle CDJR Buick of Chesterton - Sales.csv';

--3. reprocess the file--
update sales.sales_raw set reprocess_file = true
where vin = '1C4HJXDG4NW191328'
and property_id = '50066'
and source_document = '2022 03 - Castle CDJR Buick of Chesterton - Sales.csv';

--that's it! the dag will find this row where we set reprocess_file to true & reprocess it--