ClickTrails data and matching is refreshed every night by the nightly processing run (matching/clicktrails DAGs) for the most recent 90 days.

To verify that the normal refresh ran successfully, check Slack #salesattribution channel for SA_Build_Clicktrails. Status should be PASS. (If it fails, it will automatically retry. Sometimes VP of Engineering has to kill the process & manually restart it if it gets stuck.) If ClickTrails refresh fails, anything that is loaded in the meantime won't get processed until the next successful run.

Offline Attribution and LiveRamp attribution will not show in Looker if a ClickTrails Refresh is needed.

To ensure matching when loading or reprocessing any data > 90 days ago, a manual process known as a ClickTrails Refresh must be completed.

Manual ClickTrails Refresh

This manual process would be run if you need to rerun a month where sales_date is >90 days back. The month you need a ClickTrails refresh for is based on the SALES month, not the leads months.

1. WAIT! - After loading leads and sales data, you must wait the appropriate timeframe:
   1. Sales - Wait 7-10 days after loading a manual Sales file for full Tapad/LiveRamp attribution to appear.  QA the data and verify you are satisfied with the Tapad and LiveRamp attribution percentages before proceeding.
   2. Leads -
      1. If you are reprocessing leads with a start_time > 90 days ago, you must follow this special process to ensure matching happens: Instructions for Running etl_leads_preprocessing_processing DAG on demand with parameters.  
         If you are simply loading new leads with a start_time > 90 days ago, no need to follow this process.
      2. Wait 1 day after loading a manual Leads file for full Offline Matching.  Check the Slack channel #salesattribution and ensure that SA_Matching (PROD and SA_Build_Clicktrails (PROD) have both had a successful run (status: PASS).  It will look like this:
         If there is a failure, do not proceed.  Wait until both DAGs have had successful runs with status=PASS.
          
2. Determine which DAGs you need to run - ClickTrails refreshes are now completed in 90 day blocks.
   See the ClickTrails Refresh Helper Google Sheet to help determine which DAG(s) you may need to run.
    
3. Run the appropriate Airflow DAGs:
   1. sa_clicktrails_etl_456
   2. sa_clicktrails_etl_789
   3. sa_clicktrails_etl_10111213

NOTE: Each DAG can take up to 90 minutes to complete.  
Do not start any of these DAGs before the nightly run is completed (SA_Build_Clicktrails PASS).  
Do not start any of these DAGs after approximately 4:30 PM EST as it may interfere with the nightly run kicking off.

See also

• Clicktrails Report Refresh - Process for CSLs to request updated Excel Clicktrails files.  This can be run the day after a ClickTrails refresh.
• Reprocessing Sales and Leads 
• SA Build Clicktrails - Confluence document explaining how matching and clicktrails processes work from a technical (Engineering) perspective