All sales are ingested first into the Sales Raw table, which contains the exact values from the sale file or sales feed (DV, CDK API, Tekion).  Data is then standardized for matching purposes and transferred into the Sales Standardized table.  During this transfer from Raw to Standardized, sales can get filtered out for multiple reasons.  Our Sales Processing will filter out sales that are considered B2B.  “Filtered” means the sale does not make it from sales_raw into sales_standardized.

How B2B Sales are Filtered

Identification of B2B Sales is done in multiple ways:

1. Sale Type and New or Used values:
   Our Sales Processing code looks at sale_type and new_or_used values. If either field contains a B2B Sale Type indicator, the record will be filtered out during preprocessing as a B2B Record.
   
   The list of Sale Types that are considered B2B can be found in this Google Sheet:
   
   sales_data_processing B2B Sale filtering - Universal B2B Sale Types sheet - used for all dealers
   sales_data_processing B2B Sale filtering - Custom B2B Sale Types sheet - custom filtering for specific properties only
   
   EXACT - If sale_type or new_or_used value matches exactly, the sale will get filtered as B2B
   PARTIAL - If sale_type or new_or_used value contains any of these keywords, the sale will get filtered as B2B
    
2. CDK API ONLY - Custom mappings and exceptions:
   See Google Sheet CDK Mappings
   Our CDK API integration has the ability to:
   1. Identify B2B sales using fields other than sale_type and new_or_used.  See sheets Universal Value Exceptions and Custom Value Exceptions.  For example, if deal.lienHolderName or crm.crmSalesMgr.employeeName or crm.crmSalesperson1.employeeName equal “WHOLESALE” then the CDK processing DAG will set sale_type = Wholesale, which will cause the sale to be filtered by Sales Processing as B2B.
       
   2. Custom map fields from the dealer's DMS to fields on our end.  See sheet Custom Mappings.  For example, if a specific property is using a custom field to identify the sale type (not the standard deal.dealType), then we can map that field to sale_type instead (for that property only).
       
3. (Customer) Name values:
   ONLY IF THERE IS NO Sale Type and NO New or Used value specified, our code looks compares the Name fields (full_name, first_name, last_name) to a table to attempt to filter out B2B records. 
   If a Sale Type value or a New Or Used value exists, this step is SKIPPED.
   
   The list of Names that are considered B2B can be found in this Google Sheet:
   sales_data_processing B2B Sale filtering - B2B Name Indicators sheet
   All values in that table with active=true will cause a sale to be filtered out if the value is found in any of the name fields combined (first, middle, last, full). 
   
   exact_match = true will only filter the sale out if the entire name matches exactly
   exact_match = false will filter the sale out if there is a partial match within the name
   
   **Note there are a lot of values in that table that are active=false and those will NOT get filtered.
   
   We use a lot of OEM names (Chrysler, Chevrolet, Nissan, etc.) in this filtering table, but we do not use partial matches for some brands, such as “Ford” as that is a common last name.

B2B Sale Filtering Code

⚠️ These links are only accessible by Engineering and Ops Team members who have a Github login:

• B2B Sales Filtering code lives here: sales_data_processing.py
• There also exists B2B Sync code.  As of Oct 2024, Ops has no idea what this code does:
  • etl_fix_b2b_sync.py
  • fix_b2b_sync.ddl