We recently held a Sugar Market Academy webinar on managing your Sugar Market database. You can check it out here.
The first reason to review Sugar Market records is to ensure you have good data and high quality Leads. Over time, it’s worth investigating whether Leads are interacting with your marketing efforts. If recipients are not opening or clicking on links in emails, visiting web pages, registering for events, etc. these might not be high quality Leads.
Sugar Market customers have subscriptions for Contacts/Leads in blocks of 10,000, scaling up per their business needs. However, only deliverable records are counted toward the Sugar Market subscription. Deliverable contacts must meet one of the criteria below:
- They have an email address.
- They have not unsubscribed.
- Their email address has not been marked as invalid/bounced.
If a customer exceeds their subscription count, they might wish to review their Contacts/Leads to see if any are no longer needed. Records with incomplete information (missing names, emails, titles, etc.), or that have not interacted with marketing efforts, could be trimmed from Sugar Market.
The first consideration should be whether records exist in CRM as well as Sugar Market. If so, then clean up should be done in CRM so that the effort only has to be made once. Sugar Market has a ‘recycle bin’ feature that maintains integrity between both systems. If a record is deleted in CRM, it will consequently be deleted in Market as well.
Creating a custom report will allow us to review Leads/Contacts most efficiently. To do so, navigate to Analytics > Custom Reports as shown below.
Select Create > Advanced Report. For this example, please use the following settings.
- The Data Source should be Standard Views.
- The Table/View should be viewAccountContact.
- The Report type should be Tabular.
- The Report Name and Description should be descriptive for your needs.
- Checkmark if the Report should be Shared with other users.
- Select a Folder for storing the report.
- Click Save when all report information has been filled in.
On the next screen, select fields for inclusion in the report. Commonly used fields include Email, ContactID (Sugar Market's unique identifier), FirstName, LastName, Source, SalesfusionLastActivity, CRMType, OpOut (subscription status), DelStatus (Delivery Status), CreatedDate, ExternalCRMCreatedDate, and ID (from any integrated CRM). Other fields can be added as desired.
Select the Filter tab. Filter criteria should be as follows:
- Email that contains @ (Any valid email must contain an @ symbol.)
- OpOut not equal to Y (The OpOut field is used to identify unsubscribed records.)
- DelStatus not equal to 2 (Delivery Status 2 means invalid. The user-friendly version is used when editing the filter criteria, as pictured below.
This filter will result in a list of all deliverable Leads and Contacts.
At this point, let’s review the report. Click on the Run Report button in the top right of the report. Use the Run Preview option to preview up to 2,000 results.
If you notice records missing information such as firstname, lastname, email, title, etc., these might be records that could be trimmed from the Sugar Market Contacts. You can duplicate the report from the list view as shown below and update the filter to isolate these records. We’ll talk a bit more about editing report filters shortly.
The Source field might contain values like Import, SugarCRM (or the integrated CRM), Campaign (Lead created by adding a Manual Include on a campaign), etc. If there is no Source value then the record was created in Sugar Market. This might be a result of form submission, event registration, or manual creation by an end-user in the user interface. This can be useful information when reviewing Leads and Contacts.
The SalesfusionLastActivity field gets updated with any Mail Open, Mail Click, Web Hit, Form submission, Page Visit, and Event Registration. This field demonstrates if marketing recipients are interacting with your marketing efforts. If you’re seeing records where there are no SalesFusionLastActivity dates or they’re long in the past, these might be records you’d consider trimming from Sugar Market.
You might wish to duplicate the report and add a filter on CRMType if you wish to view Leads and Contacts in separate reports.
Let’s discuss updating report filters now. Click on the Edit button on the Filter tab to change the filter criteria. If we’re adding criteria using ‘AND’ then we can continue in the same grouping, such as adding a filter criterion on CRMType as in the example below.
If we’re going to use an OR clause, that’s where it’s time to build another filter grouping using the larger AND/OR buttons as highlighted below.
In the example above, we’re trying to identify Leads/Contacts that are not interacting with your marketing efforts, that have a SalesfusionLastActivity in the past, or that have no SalesfusionLastActivity date. * (See note below about the SalesfusionLastActivity date field.)
The way the filter groupings are designed here, the report will first identify all the deliverable records using the first 3 criteria and then compare them with the records from the 2nd grouping related to the SalesfusionLastActivity criteria.
Once a report filter has been updated to identify records that are no longer needed, there are functions such as Mass Delete Contacts that can be used to remove them from Sugar Market.
Reports used for mass deletion must include the "Contacts.ContactId" column when reporting on viewAccountContact. (That’s why it was included in this example report.) Only reports that include this field will be available for selection using Mass Delete Contacts by Report. Further information on Mass Deleting Contacts can be found here.
Custom reports can also be used as Auto Deletion/Post-Sync Cleanup reports. Further information on Auto Deletion reports can be found here.
* Note:
At the time of this post, there is an Issue in Sugar Market where we can't filter on blank SalesFusionLastActivity date fields. The status of the Issue can be seen when logged into the Sugar Support portal.
Report Builder does not allow blank/null values in date fields when using the Equals/ Does not equal operators
https://portal.sugarondemand.com/#supp_Bugs/93991
If you run into this issue, please open a support case. We have a couple of approaches that can be used to work around this issue.