LeadConduit: Fix Last Step Reason to now consider errors if not needed

[jesusitd]

I.e.

https://next.leadconduit.com/events/5f3b01665debe167f2de2523/5f3b01695debe167f2de2541

[jesusitd]

Added Last Event Key and Outcome fieds to the leads table and REPLICATE statement.

OLD Statement backup:

REPLICATE [leads] SELECT [api_data_json], [description] AS [last_event_description], [reason] AS [last_event_reason], [event_id] AS [last_event_id], [start_timestamp] AS [last_event_start_timestamp], [vars.anura.outcome] AS [anura_outcome], [vars.contact_center_compliance_dnccom.outcome] AS [contact_center_compliance_outcome], [vars.flow.id] AS [flow_id], [vars.flow.name] AS [flow_name], [vars.form_post.outcome] AS [form_post_outcome], [vars.lead.campaign_source] AS [campaign_source], [vars.lead.campaign_sub_id_1] AS [campaign_subid], [vars.lead.email.normal] AS [email], [vars.lead.first_name] AS [first_name], [leads].[lead_id], [vars.lead.landing_page] AS [landing_page], [vars.lead.last_name] AS [last_name], [vars.lead.phone_1.normal] AS [phone], [vars.source.id] AS [source_id], [vars.source.name] AS [source_name], [vars.submission.timestamp] AS [submission_timestamp], [vars.suppressionlist.query_item.outcome] AS [suppression_list_query_item_outcome], [vars.suppressionlist.is_unique.outcome] AS [suppressionlist_is_unique_outcome], [vars.telesign.live.outcome] AS [telesign_outcome], [vars.trustedform.outcome] AS [trustedform_outcome], [vars.whitepages_pro.phone_intelligence.outcome] AS [whitepages_pro_outcome], [vars.anura.reason] AS [anura_reason], [vars.contact_center_compliance_dnccom.reason] AS [contact_center_compliance_dnccom_reason], [vars.form_post.reason] AS [form_post_reason], [vars.suppressionlist.is_unique.reason] AS [suppressionlist_is_unique_reason], [vars.suppressionlist.query_item.reason] AS [suppressionlist_query_item_reason], [vars.telesign.live.reason] AS [telesign_live_reason], [vars.trustedform.reason] AS [trustedform_reason], [vars.whitepages_pro.phone_intelligence.reason] AS [whitepages_pro_phone_intelligence_reason] FROM [leads] JOIN ( SELECT [lead_id], MAX([start_timestamp]) AS [max_timestamp] FROM [leads] WHERE [start] = '{env:last2hours}' GROUP BY [lead_id] ) [MAX_LEAD] ON [MAX_LEAD].[lead_id] = [leads].[lead_id] AND [leads].[start_timestamp] = [MAX_LEAD].[max_timestamp] WHERE [leads].[start] = '{env:last2hours}'

New statement:

REPLICATE [leads] SELECT [event_id] AS [last_event_id], [key] AS [last_event_key], [description] AS [last_event_description], [outcome] AS [last_event_outcome], [reason] AS [last_event_reason], [start_timestamp] AS [last_event_start_timestamp], [vars.anura.outcome] AS [anura_outcome], [vars.contact_center_compliance_dnccom.outcome] AS [contact_center_compliance_outcome], [vars.flow.id] AS [flow_id], [vars.flow.name] AS [flow_name], [vars.lead.campaign_source] AS [campaign_source], [vars.lead.campaign_sub_id_1] AS [campaign_subid], [vars.lead.email.normal] AS [email], [vars.lead.first_name] AS [first_name], [leads].[lead_id], [vars.lead.landing_page] AS [landing_page], [vars.lead.last_name] AS [last_name], [vars.lead.phone_1.normal] AS [phone], [vars.source.id] AS [source_id], [vars.source.name] AS [source_name], [vars.submission.timestamp] AS [submission_timestamp], [vars.form_post.outcome] AS [form_post_outcome], [vars.suppressionlist.query_item.outcome] AS [suppression_list_query_item_outcome], [vars.suppressionlist.is_unique.outcome] AS [suppressionlist_is_unique_outcome], [vars.telesign.live.outcome] AS [telesign_outcome], [vars.trustedform.outcome] AS [trustedform_outcome], [vars.whitepages_pro.phone_intelligence.outcome] AS [whitepages_pro_outcome], [vars.anura.reason] AS [anura_reason], [vars.contact_center_compliance_dnccom.reason] AS [contact_center_compliance_dnccom_reason], [vars.form_post.reason] AS [form_post_reason], [vars.suppressionlist.is_unique.reason] AS [suppressionlist_is_unique_reason], [vars.suppressionlist.query_item.reason] AS [suppressionlist_query_item_reason], [vars.telesign.live.reason] AS [telesign_live_reason], [vars.trustedform.reason] AS [trustedform_reason], [vars.whitepages_pro.phone_intelligence.reason] AS [whitepages_pro_phone_intelligence_reason], [api_data_json] FROM [leads] JOIN ( SELECT [lead_id], MAX([start_timestamp]) AS [max_timestamp] FROM [leads] WHERE [start] = '{env:last2hours}' GROUP BY [lead_id] ) [MAX_LEAD] ON [MAX_LEAD].[lead_id] = [leads].[lead_id] AND [leads].[start_timestamp] = [MAX_LEAD].[max_timestamp] WHERE [leads].[start] = '{env:last2hours}'

[jesusitd]

Report was adjudted to not give the last event reason if not stuck, stopped or error.

[jesusitd]

CC @fedegarza Closed.