Forked from heathdutton/mautic-campaign-delays-lite.sql
Last active
November 3, 2018 14:21
-
-
Save dbhurley/9d9f5eef5cff4f460b9da0e94d239dee to your computer and use it in GitHub Desktop.
List all Mautic campaign delays
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- All mautic campaign delays merged. Two queries (the first is important). Takes under 10s. | |
-- Depends on the PR of soft-deleted campaign events. | |
SET @@group_concat_max_len = 10000000000000; | |
SELECT * | |
FROM ( | |
SELECT NULL as campaign_id, | |
NULL as campaign_name, | |
NULL as event_id, | |
NULL as event_name, | |
NULL as lead_count, | |
NULL as type, | |
NULL as avg_delay_s | |
FROM ( | |
SELECT @campaigns := ( | |
SELECT GROUP_CONCAT(c.id SEPARATOR ',') | |
FROM campaigns c | |
WHERE c.is_published = 1 | |
) | |
UNION ALL | |
SELECT @events := ( | |
SELECT GROUP_CONCAT(ce.id SEPARATOR ',') | |
FROM campaign_events ce | |
WHERE FIND_IN_SET(ce.campaign_id, @campaigns) > 0 | |
-- Use the following if you have the Soft Delete camapign event PR merged/patched. | |
-- AND ce.is_published = 1 | |
) | |
) vars | |
UNION ALL | |
SELECT | |
cl.campaign_id AS campaign_id, | |
c.name as campaign_name, | |
NULL as event_id, | |
NULL as event_name, | |
count(cl.lead_id) AS lead_count, | |
'kickoff' as 'type', | |
ROUND(AVG(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(cl.date_added))) as avg_delay_s | |
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 HOUR) | |
AND FIND_IN_SET(cl.campaign_id, @campaigns) > 0 | |
GROUP BY cl.campaign_id | |
UNION ALL | |
SELECT | |
el.campaign_id, | |
c.name as campaign_name, | |
el.event_id, | |
ce.name as event_name, | |
COUNT(el.lead_id) as lead_count, | |
'scheduled' as 'type', | |
ROUND(AVG(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(el.trigger_date))) as avg_delay_s | |
FROM campaign_lead_event_log el | |
LEFT JOIN campaigns c | |
ON c.id = el.campaign_id | |
LEFT JOIN campaign_events ce | |
ON ce.id = el.event_id | |
WHERE | |
el.is_scheduled = 1 | |
AND el.trigger_date <= NOW() | |
AND FIND_IN_SET(el.event_id, @events) > 0 | |
GROUP BY el.event_id | |
) combined | |
WHERE avg_delay_s > 0 | |
ORDER BY avg_delay_s DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment