I arrived at work today to get a mysterious message from one of my users. Opportunities, for some strange reason, were suddenly appearing in the system and being marked as already Won!
While this is great from a sales perspective (we all like Won opportunities!), it should not have been happening from a data perspective. Perhaps a bit of explanation is in order…
What it Does
My employer (a great Australia software company) sells software. Actually, since people can download and trial our software for free, it would be more accurate to say that we sell software licenses. Each year, we encourage our customers to renew their software maintenance so that they can get support and upgrades. So, our primary use for Salesforce.com is to track Renewal Opportunities.
These opportunities are perfect for automation. Our existing licensing system keeps track of customers, so I use the Salesforce.com Data Loader to import a list of products that have expired and we have dedicated Renewal Specialists who contact the customers to see if they would like to renew. We typically get around 70% renewing, so they must like our software! (Unlike other Enterprise Software companies, they can keep using our software even if they don’t pay annual maintenance.)
How it Works
The basic system is:
- An SQL view on our customer database looks for expired licenses
- The Data Loader uses this view to extract information from the database and load it directly into Salesforce as an Opportunity object
- I use an Upsert operation with my licensing system supplying an External ID. This means that I can load new and existing opportunities in one batch, without my external system even having to know about Salesforce. As long as I supply the same External ID, Salesforce will automatically update existing records or create new ones if they don’t exist.
- The data is reloaded overnight to show any information that has updated or any Opportunities that have closed. In fact, the only way to mark an Opportunity as Won is via the DataLoader — staff can’t claim a Closed/Won situation unless the external system says it has actually been sold.
So, why did we start getting Opportunities appearing long after expiry, when they had already been won?
Well, I tracked it down to the fact that I’m only creating Opportunities for customers in the Americas region, since that’s where our Renewal Specialist staff are located. It appears that some of our customers had incorrect address information so the system couldn’t understand their Country. My little SQL function that converts Country into Region didn’t realise that the Opportunities were in the Americas Region since they were missing the right country information.
Fortunately, we’ve had some staff manually cleaning our customer data. It just happened that they fixed the address information for a couple of customers who had expired licenses. This then caused the Opportunity to magically appear in Salesforce — later than should have happened. I managed to track this down from some ‘Modified date’ fields we’ve got on our data that I use for incremental loading (more on that topic another day). Plus, the automatic ‘Created by’ field on records gave great information on when records appeared.
The Bottom Line
- Clean data is good
- Automation is great, but can miss some things that humans would notice
- Audit tracking of fields and records can help ‘piece together’ clues in mysterious situations