How Can I Identify Deleted Lead Records?

Question

There is no fivetran_deleted column for the LEADS table. How can I identify deleted lead records?

Environment

Connector: Marketo

Answer

We use Marketo's Get Deleted Leads API endpoint to fetch the deleted leads. See our Marketo sync notes for more information.

To identify deleted leads, view the ACTIVITY_DELETED_LEAD table. To see the correct deleted status of your leads, use the sample query below:

    SELECT lead.*,
    CASE
        WHEN activity_delete_lead.lead_id IS NULL THEN false
        ELSE true
    END as deleted
    FROM lead
    LEFT JOIN activity_delete_lead on lead.id=activity_delete_lead.lead_id;

For more information on why we added the ACTIVITY_DELETED_TABLE and how to check if leads are deleted, see our Marketo documentation.