Data Management Best Practices - April 2025

Our latest Social Club on April 10th was a deep dive into the world of data management in Sugar, with lots of practical takeaways and lessons learned from real-world use. Charmaine Morenoand Patrick McQueen shared their experiences and answered questions around duplicate data cleanup, archiving strategies, and data retention best practices. Here are a few of the topics that were covered and insights that were shared. Hopefully, this helps spark some ideas or reinforce what you’re already doing!

Keeping Your Data Clean: From Duplicates to Invalid Emails

Charmaine started by walking through how her team tackles duplicate contact records and the challenges that come with maintaining high-quality data. A few of the tips and insights shared during this portion:

• Sugar Market flags invalid emails, but there isn’t an easy way in Sugar to find Contacts that only have invalid addresses. Charmaine shared an Advanced Report that identifies these records, which can then be exported, updated via import, and cleaned up to improve list quality. Charmaine provided this query that she uses to check these contacts:
  
  Fullscreen

  1

  2

  3

  4

  5

  6

  7

  8

  9

  10

  SELECT a.id Account_ID, c.id Contact_ID, count(ea.id), sum(ea.invalid_email)

  FROM accounts a

  INNER JOIN accounts_contacts ac ON (ac.deleted = 0 AND ac.account_id = a.id)

  INNER JOIN contacts c ON (c.id = ac.contact_id AND c.deleted = 0)

  INNER JOIN contacts_cstm cc ON (cc.id_c = c.id)

  INNER JOIN email_addr_bean_rel eabr ON (eabr.bean_id = c.id AND eabr.deleted = 0 AND eabr.bean_module = 'Contacts')

  INNER JOIN email_addresses ea ON (ea.id = eabr.email_address_id AND ea.deleted = 0)

  WHERE a.deleted = 0

  GROUP BY a.id, c.id

  HAVING count(ea.id) = sum(ea.invalid_email)

  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

  SELECT a.id Account_ID, c.id Contact_ID, count(ea.id), sum(ea.invalid_email)
  FROM accounts a
  INNER JOIN accounts_contacts ac ON (ac.deleted = 0 AND ac.account_id = a.id)
  INNER JOIN contacts c ON (c.id = ac.contact_id AND c.deleted = 0)
  INNER JOIN contacts_cstm cc ON (cc.id_c = c.id)
  INNER JOIN email_addr_bean_rel eabr ON (eabr.bean_id = c.id AND eabr.deleted = 0 AND eabr.bean_module = 'Contacts')
  INNER JOIN email_addresses ea ON (ea.id = eabr.email_address_id AND ea.deleted = 0)
  WHERE a.deleted = 0
  GROUP BY a.id, c.id
  HAVING count(ea.id) = sum(ea.invalid_email)

• Her team uses a “status” field (e.g., active/inactive) to retain historical contacts while preventing them from being included in future communications. For example, if an Account is marked as inactive, its Contacts can be automatically updated via BPM to reflect that change.
• Rather than deleting duplicate Contacts outright, Charmaine discussed a “Duplicate Match” checkbox (populated by a custom logic hook) to flag potential duplicates based on matching email addresses on the same Account. Customer Success Managers then review these flagged records via reports and either update or remove them based on internal review.
• A reminder was shared that deleting a Contact in Sugar breaks relationship links to content in other modules, but doesn’t remove the related data, which highlights the importance of managing deletions carefully.
  • The idea of deleting parent-child record relationships together was discussed, but it was noted that custom logic hooks or support may be needed to do this, depending on the use case.

Archiving & Deletion: Managing Data Long-Term

Next, Patrick walked through the use of the Data Archiver in Sugar, which is available in the Admin panel and gives teams more control over how older or outdated records are handled. Some highlights include:

• You can choose to either Archive (moves records to separate database tables) or Hard Delete (permanently removes records).
• Hard Deletes can help reduce database size and improve performance, but proceed with caution: unlike soft deletes, there’s no way to recover those records unless backups exist.
• He recommended scheduling cleanups for high-volume modules (BPM tables tend to be some of the largest) to manage growth over time.
  • Targeting the PMSE_Inbox table is useful as it cascades through the other BPM tables
• A best practice: when using SugarBPM, avoid setting “Applies to: First Update.” Instead, use “All Updates” and use a field on the record (like a checkbox) that can be updated and used for a filter, avoiding unintentional re-processing.
• There’s a 10,000-record limit per archiving job, so if you’re not seeing that number drop over time, it could mean more records are being added than archived.

Looking Ahead

Our next Social Club session will take place on May 8th, Social Club: Take Your Sugar Reporting to the Next Level. We’ll be discussing some great advanced use cases for Reports in Sugar and also how to leverage Enhanced Forecasting to further improve how you view your data and how it changes over time.

Thanks to everyone who joined, and to Charmaine and Patrick for leading such a helpful discussion and sharing so many helpful insights. Hope to see you next time!