​
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