Speed of Lead Flow Reporting

This method is no longer being maintained. Please use the following QuickSight dashboard

Definition of Speed of Lead Flow

Speed of lead flow is defined as the time taken for a lead to enter the platform, be fully processed and delivered to the integration end point.

What can impact Speed of Lead Flow

There are many factors that can impact the speed of processing:

Actions that happen after the lead is created within Convertr, but prior to being processed:
- Value Transforms
- Lead Holding
  - Publisher Over Delivery
  - Twilio Connected App
  - Double Opt-in
- Customers using Batch integration (from 8th June 2021 onwards)
Manual Intervention
- Leads which were processed and were initially Invalid or Caution, but subsequently manually marked as Valid and integrated
- Leads which were manually reprocessed for any reason
Bulk imports take more time to process compared to a steady flow of leads
How busy the Convertr network is at the time the lead came in

Report Criteria

The following query can be used to provide the raw data for a “speed of lead flow” breakdown for customers.

Report contains a breakdown of all leads which have been integrated
It excludes leads which have been deleted
The report excludes leads which have been “marked as delivered” as no ProcessrJobLead record will exist for this action because it doesn’t run the integration job (see HAVING clause for exclusion)

The biggest factor on the average processing time will be leads which have required manual intervention. I have included this in the report to highlight campaign management efficiencies as this may be useful.

Leads which have a manualIntervention=1 column will be one of the following:

A lead which has been manually reprocessed and thus not an accurate reflection of the automated time to process and deliver the lead
Leads which were reprocessed

Query

SELECT 
	l.id as leadId,
	c.advertiserId,
	l.campaignId,
	l.publisherId,
	l.createdTs,
	l.integration,
	(
		SELECT 
			createdTs 
		FROM ProcessrJobLead pjl 
		JOIN CampaignProcessrJob cpj ON (cpj.id = pjl.campaignJobId) 
		WHERE 
			cpj.stage = 50 
			AND pjl.leadId = l.id 
			AND pjl.visible = 1 
			AND pjl.passed = 1 
		ORDER BY pjl.id ASC 
		LIMIT 1
	) as firstDeliveredTime,
	
	IF (
		l.leadFlagId IS NULL 
		AND 
		(SELECT 1 FROM ProcessrJobLead pjl WHERE pjl.leadId = l.id AND pjl.visible = 0 ORDER BY pjl.id ASC LIMIT 1) IS NULL
	, 0, 1) as manualIntervention,
	TIMESTAMPDIFF(MINUTE, l.createdTs, (SELECT createdTs FROM ProcessrJobLead pjl JOIN CampaignProcessrJob cpj ON (cpj.id = pjl.campaignJobId) WHERE cpj.stage = 50 AND pjl.leadId = l.id AND pjl.visible = 1 AND pjl.passed = 1 ORDER BY pjl.id ASC LIMIT 1)) processingInMins,
	TIMESTAMPDIFF(SECOND, l.createdTs, (SELECT createdTs FROM ProcessrJobLead pjl JOIN CampaignProcessrJob cpj ON (cpj.id = pjl.campaignJobId) WHERE cpj.stage = 50 AND pjl.leadId = l.id AND pjl.visible = 1 AND pjl.passed = 1 ORDER BY pjl.id ASC LIMIT 1)) processingInSecs
FROM Lead l 
JOIN Campaign c ON (l.campaignId = c.id)
WHERE l.visible = 1
	AND l.integration = 1
HAVING 
	firstDeliveredTime IS NOT NULL
ORDER BY l.id DESC;

Redshift version:

SELECT 
	l.id as leadId,
	c.advertiserId,
	l.campaignId,
	l.publisherId,
	l.createdTs,
	l.integration,
	(
		SELECT 
			createdTs 
		FROM "default_db"."demandscience_prod"."processrjoblead" pjl 
		JOIN "default_db"."demandscience_prod"."campaignprocessrjob" cpj ON (cpj.id = pjl.campaignJobId) 
		WHERE 
			cpj.stage = 50 
			AND pjl.leadId = l.id 
			AND pjl.visible = 1 
			AND pjl.passed = 1 
		ORDER BY pjl.id ASC 
		LIMIT 1
	) as firstDeliveredTime,
	
	CASE (l.leadFlagId IS NULL AND (SELECT 1 FROM "default_db"."demandscience_prod"."processrjoblead" pjl WHERE pjl.leadId = l.id AND pjl.visible = 0 ORDER BY pjl.id ASC LIMIT 1) IS NULL)
		WHEN 1 THEN 0 ELSE 1 END as manualIntervention,
	DATEDIFF(minute, l.createdTs, (SELECT createdTs FROM "default_db"."demandscience_prod"."processrjoblead" pjl JOIN "default_db"."demandscience_prod"."campaignprocessrjob" cpj ON (cpj.id = pjl.campaignJobId) WHERE cpj.stage = 50 AND pjl.leadId = l.id AND pjl.visible = 1 AND pjl.passed = 1 ORDER BY pjl.id ASC LIMIT 1)) processingInMins,
	DATEDIFF(second, l.createdTs, (SELECT createdTs FROM "default_db"."demandscience_prod"."processrjoblead" pjl JOIN "default_db"."demandscience_prod"."campaignprocessrjob" cpj ON (cpj.id = pjl.campaignJobId) WHERE cpj.stage = 50 AND pjl.leadId = l.id AND pjl.visible = 1 AND pjl.passed = 1 ORDER BY pjl.id ASC LIMIT 1)) processingInSecs
FROM "default_db"."demandscience_prod"."lead" l 
JOIN "default_db"."demandscience_prod"."campaign" c ON (l.campaignId = c.id)
WHERE 
l.createdTs >= '2023-11-27'
AND
l.visible = 1
	AND l.integration = 1

GROUP BY l.id, c.advertiserid, l.campaignid,l.publisherId,l.createdTs,l.integration,l.leadflagid
HAVING 
	firstDeliveredTime IS NOT NULL
ORDER BY l.id DESC;