Search
Twitter

Entries in regular expression (2)

Monday
Oct042010

Maintaining Data Quality in Salesforce.com

Salesforce data quality is an important topic. One of the most frustrating things that can happen to a business is needing data from Salesforce only to realize that all data up to point X is useless. Let me give you a simple example from personal experience. Years ago, I was working with a sales team to help them pinpoint customers that were likely to cancel before they actually did so. They needed to find ways to maximize their time and resources by proactively following-up with accounts that statistically fit the profile of an account that was about to cancel. The first place I started was with the data.

As any database administrator knows, the data is only as good as the people who are entering it. The old adage "garbage-in-garbage-out" comes to mind. In my case, I was really excited to find out that the accounting department had been giving cancelling customers a brief survey before they closed out the account. I went into the database and started to run some queries. What I quickly found was that the number of questions answered on each survey depended largely on which account rep was closing the account. Some either never asked the questions or didn't take the time to fill in some of the key details. In the end, I was able to get the data but I had to pull it in from 5 different sources and even then I wasn't confident in what I had.

This is a classic example of when data is needed to make good business decision and the data just plain doesn't exist or is in such poor quality it cannot be counted on. While there is not much you can do about it after the fact, the lesson to take away is that NOW is the time to collect good quality data. Companies waste countless marketing dollars each year pursuing bad prospects. With a little bit of effort up front, these problems can be shelved.

As a system administrator, part of your job is to realize that your priorities are not going to sync up with those of the end user many times. Making a field required is a single click for you, whereas it could mean as extra 15-20 per call for the CSR who uses that form 200 times a day (66 extra minutes of work). To do your job effectively you need to walk that line of what is best for the user and what is best for the company. In my opinion you always lean toward the company but communicate why this is so important to the user. When you lay out a business case for why something needs to be done it helps. In addition, it helps a lot to listen to the concerns of the end user and do what you can to make them feel a part of the decision making process.

So, what are some ways to ensure data quality within Salesforce.com?

Design and implement a data policy that ensures your users fill out all fields in an accurate manner. In addition, the users should be consistent. Accuracy combined with consistency creates data integrity and reliability. A good Salesforce.com data policy should also include standardization. This means creating naming conventions that are instilled in all users. In addition, these data standards must be enforced in Salesforce.com by validating the data as it is typed in. As an admin, you'll want to take away as many variables as possible. Create pick-lists in your Salesforce.com forms whenever possible. Also, make sure that you know where your data is coming from. If someone is getting ready to do a mass import, make sure to analyse and cleanse the data before it gets imported into Salesforce.com. Here are some simple examples of standardization:

  • Country/State: use validation to standardize AZ vs Arizona, USA vs U.S.
  • Account names: Inc vs Incorp., Ltd vs LTD, Limited

Track data quality. Tracking data quality on an on-going basis is very important. In order to know that you are making progress and maintaining the data standards you've outlined, it is important to get some baseline metrics. Here are some things I recommend you start to look at (feel free to add your own):

  • Prospect Accounts Missing # of Employees (last 60 days)
  • Opportunities with Close Date (last 60 days)
  • Lead Rating on Converted Leads (monthly %). If 90% of the converted leads are classified as cold, then what are hot leads?
  • Lead Source (last 60 days)
  • Leads with a status of "Qualified" that have not been converted (monthly %)
  • Contacts without valid email addresses
  • Accounts without Industry specified (last 60 days)
Monday
Sep202010

Regular Expressions in Apex Code

Every once in a while it's very helpful to use regular expressions in your code. The problem is that resources and examples are often scattered. I wanted to give two simple examples of how you can write and test regular expressions in your Apex code inside Salesforce.com.

Return all the numeric characters in a string via regular expression

For the sake of quickly writing and testing this, I've just created a new Lead trigger that runs before insert. I've then set a test input string "input" and I'm using a Pattern object to return the matches in a new string "test". I then print a system debug statement so that I can look in the debug logs to see my results.

trigger myLeadTrigger on Lead (before insert) {

    string input = 'a3f45qq456';

    for (Lead l : Trigger.new)
    {
        //instantiate new Pattern object        
        Pattern p = Pattern.compile('[^0-9]');
        

        //return a string that contains only numeric values from 0-9 from my original input
        String test = p.matcher(input).replaceAll('');
        

        //print a debug statement with my test results
        System.debug(test);

    }

}

Ouput from the debug log shows us that this is working:

16:5:8.981|USER_DEBUG|[12,9]|DEBUG|345456

As you can see, "345456" are the alpha chars from the original string "a3f45qq456".

 

Return all the alphabetical characters in a string via regular expression

For the sake of quickly writing and testing this, I've just created a new Lead trigger that runs before insert. I've then set a test input string "input" and I'm using a Pattern object to return the matches in a new string "test". I then print a system debug statement so that I can look in the debug logs to see my results.

 

trigger myLeadTrigger on Lead (before insert) {

    string input = 'a3f45qq456';

    for (Lead l : Trigger.new)
    {
        //instantiate new Pattern object        
        Pattern p = Pattern.compile('[^a-zA-Z]');
        

        //return a string that contains only numeric values from 0-9 from my original input
        String test = p.matcher(input).replaceAll('');
        

        //print a debug statement with my test results
        System.debug(test);

    }

}

 

Ouput from the debug log shows us that this is working:

6:46:21.762|USER_DEBUG|[14,9]|DEBUG|afqq

As you can see, "afqq" are the alpha chars from the original string "a3f45qq456".