Reprocessing Sales

1. Update rows in sales.sales_raw

-- REPROCESSING A SALES FILE --
UPDATE sales.sales_raw set reprocess_file=true
where property_id = ${PROPERTY ID} and source_document like '${SOURCEDOC}%';
-- If the file is a (multi) file, remove the property_id criteria
-- Run etl_sales_preprocessing_processing (or wait for it to run on the next scheduled run)

2. Run etl_sales_preprocessing_processing
3. Run sa_matching_reprocess DAG on demand with parameters
4. Run ClickTrails Refresh

Reprocessing Leads

1. Update rows in sales.leads_raw

-- REPROCESSING LEADS FILES --
-- (example: after you manually update/fix values in sales_raw - for instance changing a particular VIN's NEWUSED column from S to NEW) --
-- (if a file gets stuck and can't process due to a mapping issue and mapping is corrected, this won't work - need to rerun the dag instead) --
UPDATE sales.leads_raw SET reprocess_file=TRUE where property_id = ${PROPERTY ID} and source_document like '${SOURCEDOC}%';
-- If the file is a (multi) file, remove the property_id criteria
-- Run etl_leads_preprocessing_processing (or wait for it to run on the next scheduled run)
-- If reprocessing leads > 3 months ago, follow these steps:  https://clarivoy.atlassian.net/wiki/x/AwACKg

2. Run the DAG:
   1. IF Reprocessing Leads < 3 Months ago: Run etl_leads_preprocessing_processing
   2. IF Reprocessing Leads > 3 Months ago: 
      1. Follow the steps in Instructions for Reprocessing Leads with a start_time > 90 days ago to:
         1. Run etl_leads_preprocessing_processing DAG on demand with parameters 
         2. Run sa_matching_reprocess DAG on demand with parameters
         3. Run ClickTrails Refresh

See Also

Reprocessing Sales Code

• sales_document_reprocessing_list.ddl - This portion runs first.  It creates a view that Pentaho pulls from in subsequent steps. The following criteria are used to create the view:
  • documents that were updated in sales_raw after being processed
  • source_documents where reprocess_file=true
  • source_documents where delete_row=true
  • source_documents where the sale counts in raw, filtered, and standardized don't add up correctly (raw = standardized + filtered), implying that processing was interrupted
  • source_documents that were updated in sales_raw after being filtered
• clear_updated_sales.ktr - This portion runs next.  It uses the sales_document_reprocessing_list that was created above and for each row in that list:
  • Deletes them from all subsequent tables:
    • sales_filtered
    • sales_standardized
    • matched_cdc
    • sales_extract
  • Then sets reprocess_file=false on all rows in sales_raw
• 2_raw_sales_input.ktr - This portion identifies what rows in sales_raw to process.  It selects all rows where:
  • the source_document exists in sales_raw, but does not exist in (either sales_standardized or sales_filtered).  Since the “reprocess_file=true” records were previously deleted from both of these tables, they will get picked up for processing as if they were newly created rows.
  • AND the source_document was not entirely filtered (and never made it to sales_standardized)

Reprocessing Leads Code

• leads_reprocessing_list.ddl - This portion runs first.  It creates a view that Pentaho pulls from in subsequent steps. The following criteria are used to create the view:
  • Records that were updated in leads_raw after being processed (but only if updated in the last 3 weeks)
  • source_documents where delete_row=true
  • source_documents where process_file=true
• leads.kjb - This portion runs next.  It uses the leads_reprocessing_list that was created above and for each row in that list:
  • Deletes them from all subsequent tables:
    • matched_cdc
    • leads_filtered
    • leads_standardized
  • Then sets reprocess_file=false on all rows in leads_raw
• 2_raw_leads_input.ktr - This portion identifies what rows in leads_raw to process (as recent_raw_leads).  It selects:
  • rows in leads_raw where created_ts > the lookback months (default=3, but you can run etl_leads_preprocessing_processing with a specified lookback month parameter; see above)
  • EXCLUDING:
    • rows that already exist in both leads_raw and leads_standardized [by lead_id]
    • rows that already exist in both leads_raw and leads_filtered [by lead_id]
  • Since the “reprocess_file=true” records were previously deleted from both of these tables, they will get picked up for processing as if they were newly created rows.