Sample Queries

Run these sample queries in your destination to determine correct lead IDs for merged leads and to check if a lead is deleted.

Determine correct lead IDs for merged leads

When you merge leads, they are combined under a single lead ID. This can create problems if we have already synced activities data for these merged leads, since their original lead IDs may no longer exist.

We have added a MERGED_LEAD table that you can use to quickly determine the correct lead ID for your activity records. (In earlier versions of our Marketo connector, we rescanned activities data to update activity records with the new merged lead IDs. The rescan significantly slowed down data syncs.)

Join the MERGED_LEAD table with your desired activity table to see your records’ correct lead ID:

    SELECT <activity_table>.*,
    CASE
        WHEN merged_lead.into_lead IS NULL THEN <activity_table>.lead_id
        ELSE merged_lead.into_lead
    END AS winner_lead
    FROM <activity_table>
    LEFT JOIN merged_lead on <activity_table>.lead_id=merged_lead.merged_lead;

Check if lead is deleted

Our Marketo connector uses priority-first sync to fetch your activities data. Sometimes, we pull recent lead data that is overwritten by outdated records when we sync your older data.

For example, let’s say you created a lead three months ago and deleted it one day before Fivetran’s data sync. We start our sync by fetching your most recent data, so we sync the deleted lead record first. When we sync deleted records, we look for the record in the destination and, if it exists, mark it as _fivetran_deleted = TRUE. However, if we encounter the delete update before the record exists in the destination, there is nothing to update. We don’t write deleted records from the source to Fivetran.

We then sync your older data. When we pull the create lead activity record from three months prior, we upsert that record, making it look like that lead activity has not been deleted. Even if we created deleted records, the later upsert would still overwrite them, and there would be no practical benefit to creating the deleted records.

To track deleted records, we have added the ACTIVITY_DELETE_LEAD table, which you can use to see the correct deleted status of your leads:

    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;