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.)
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_DELETED_LEAD table, which you can use to see the correct deleted status of your leads:
SELECT lead.*, CASE WHEN activity_deleted_lead.lead_id IS NULL THEN false ELSE true END as deleted FROM lead LEFT JOIN activity_deleted_lead on lead.id=activity_deleted_lead.lead_id;