mautic:campaign:trigger backlog

backlog.sql

/* Lead ingestion behind - Leads waiting to be processed by a campaign */
SELECT cl.campaign_id as campaign_id, count(cl.lead_id) as lead_count FROM campaign_leads cl WHERE (cl.manually_removed = 0) AND (NOT EXISTS (SELECT null FROM campaign_lead_event_log e WHERE (cl.lead_id = e.lead_id) AND (e.campaign_id = cl.campaign_id))) GROUP BY cl.campaign_id LIMIT 100;
-- Faster:
SELECT cl.campaign_id AS campaign_id, c.name as campaign_name, count(cl.lead_id) AS lead_count, c.is_published AS published
FROM campaign_leads cl
LEFT JOIN campaigns c
	ON c.id = cl.campaign_id
WHERE (NOT EXISTS (
    SELECT null FROM campaign_lead_event_log e
    WHERE
    	cl.lead_id = e.lead_id
    	AND e.campaign_id = cl.campaign_id
  ))
  AND cl.date_added > DATE_ADD(NOW(), INTERVAL -1 DAY)
GROUP BY cl.campaign_id
ORDER BY lead_count DESC;

/* Campaign events behind - Events within campaigns that have been scheduled but not yet processed */
SELECT el.campaign_id as campaign_id, COUNT(el.id) as event_count FROM campaign_lead_event_log el WHERE el.is_scheduled = 1 AND el.trigger_date <= NOW() GROUP BY el.campaign_id;

/* Specific events behind */
SELECT COUNT(el.id) as event_count, ce.*
FROM campaign_lead_event_log el
LEFT JOIN campaign_events ce
ON ce.id = el.event_id
WHERE el.is_scheduled = 1
AND el.trigger_date <= NOW()
GROUP BY el.campaign_id
ORDER BY event_count DESC;