Find unused segments in Mautic that could be churning uselessly.

unused_mautic_segments.sql

-- Find unused segments that can be unpublished to save processing time.
SELECT l.id, l.created_by_user, l.name, IF (l.date_modified IS NULL, l.date_added, l.date_modified) AS date_modified,
  (SELECT COUNT(*) FROM lead_lists_leads WHERE leadlist_id = l.id) AS lead_count
FROM lead_lists l
WHERE
  l.is_published = 1
  AND l.id NOT IN (
    -- Find segments in use.
    SELECT l.id
    FROM lead_lists l
    LEFT JOIN campaign_leadlist_xref cx
      ON cx.leadlist_id = l.id
    LEFT JOIN campaigns c
      ON c.id = cx.campaign_id
      AND c.is_published = 1
    LEFT JOIN campaign_events e
      ON e.type = 'lead.segments'
      AND e.event_type = 'condition'
      -- Comment out this next line if you do not have campaign event soft deletion in your mautic instance.
      AND e.is_published = 1
      AND e.campaign_id IN (SELECT id FROM campaigns WHERE is_published = 1)
      AND e.properties LIKE CONCAT("%\"segments\";a:1:{i:0;i:", l.id, "%")
    WHERE 
      l.is_published = 1
      AND (
        c.id IS NOT NULL -- feeding a campaign
        OR e.id IS NOT NULL -- used in a condition in an active campaign
      )
    GROUP BY l.id
  )
;