​
Example Use Cases

  • Find contacts with more than 5 page hits:

    SELECT 1 FROM page_hits ph WHERE ph.lead_id = :contactId HAVING COUNT(ph.id) > 5

  • At least one email read in the last 12 months:
    Returns true if the contact has read at least one email in the past year.

    SELECT 1 FROM email_stats es WHERE es.lead_id = :contactId AND es.is_read = 1 AND es.date_sent > NOW() - INTERVAL 1 YEAR

    Use this as a segment filter or campaign condition to target contacts who have engaged with at least one email in the last 12 months.

  • Check if contact exists in an external table:

    SELECT 1 FROM vip_customers ext WHERE ext.mautic_contact_id = :contactId LIMIT 1

  • Update an external table when a campaign step is reached:

    UPDATE crm_sync SET campaign_step_reached = NOW(), last_campaign_id = :campaignId WHERE mautic_contact_id = :contactId

  • Find contacts who have not opened any email in the last 6 months:

    SELECT 1 FROM email_stats es WHERE es.lead_id = :contactId AND es.is_read = 1 AND es.date_sent > NOW() - INTERVAL 6 MONTH

    Use this as a negative segment filter: contacts NOT matching this query have not opened any email in 6 months.

  • Find contacts with at least 3 form submissions:

    SELECT 1 FROM form_submissions fs WHERE fs.lead_id = :contactId HAVING COUNT(fs.id) >= 3

  • Check if contact is assigned to a specific owner:

    SELECT 1 FROM leads l WHERE l.id = :contactId AND l.owner_id = 42