+44 203 280 3665
How to Prevent Duplicate Accounts in Salesforce with This Free App

How to Prevent Duplicate Accounts in Salesforce with This Free App

Ever felt you’re seeing double when you look at Leads and Accounts in salesforce?

Or treble? Or even quadruple?

Like unwelcome guests at a party, duplicate records have probably sneaked into your salesforce system.

Or they arrived in droves and marched through the front door.

Like unwelcome guests, duplicates are there because nothing was done to stop them getting through in the first place.

But here’s a secret. You can prevent duplicate records from being created. Nip them in the bud.

And best of all you can do it for free. That’s right, you can prevent duplicates at no cost.

How? By deploying a must-have application from the AppExchange. It’s called DupeCatcher.

The Duplicate Problem Explained

Duplicate Accounts, Contacts and Leads in salesforce can be a big problem.

Imagine calling a Lead only for them to tell you that they were called earlier by your colleague. Or worse still, that they’re already a customer. It’s not the best way to make them feel valued.

Thinking about targeting your second-tier Accounts this month? Good idea. It’s been a while since Acme, Acme Ltd and Acme Limited all had a call.

So let’s break the problem into two separate challenges.

First, you need to prevent duplicate accounts and leads from being created in the first place. We recommend the free DupeCatcher app to do this. This blog explains why and the steps you should take to get the best out of DupeCatcher.

The second challenge is to merge existing duplicate records. Depending on the scale of the problem there are different ways to do this. We’ll explain all in separate blog post. (Tip: you can install DupeCatcher today which at least will stop the problem from getting any worse).

Here’s how duplicate records get created in salesforce

Duplicate records find their way into salesforce from a variety of sources.

  • Imported external databases.
  • Migrated data.
  • Web-to-Lead entries.
  • Manually entered by Users.

We often find it’s the last one that causes the most duplicates.

When a new Account, Contact or Lead is created there’s no standard feature that forces the User to search for existing records. So often, people don’t search.

And ownership can be a contentious thing. Sometimes duplicates get created because someone else already owns the Account, Contact or Lead.

These include obvious sources such as purchased databases but one of the common and discreet sources is data manually entered by users of salesforce.

Prevent duplicate Accounts, Contacts and Leads being created

DupeCatcher might only do one thing. But it does it very well.

It prevents duplicate records from being created.

DupeCatcher is a free app available on the AppExchange that prevents users from creating duplicate Leads, Accounts and Contacts at the point of entry.

It’s provided by Symphonic Source. They also sell a cracking paid-for application called Cloudingo which is great for merging existing duplicate records.

DupeCatcher is easy to configure and can prevent duplicate Leads, Accounts and Contacts each time a user attempts to create or edit these records. It also identifies possible duplicates from Leads to Accounts and from Leads to Contacts.

Prevent web-to-lead duplicates

DupeCatcher can also be set up to prevent duplicates from being created via your website web-to-lead forms.

This can be crucial to prevent the same person or company being duplicated in your database at different stages of your sales cycle.

But hang on. Can’t sales and marketing people just use the Find Duplicates button on Leads?

The short answer is yes, they can. The realistic answer is that they often don’t. The smart answer is why clean your dishes when there’s a perfectly good dishwasher to do it for you? Especially if that dishwasher is free to use!

Awesome Pipeline and Sales Performance Visibility

Download the FREE Dashboard from the AppExchange today

Here’s how DupeCatcher prevents duplicate accounts

DupeCatcher allows you to create a set of Filters to detect potential duplicates when manually creating or editing records.

To get you started, DupeCatcher even has several pre-built filters to cover several simple ways to detect duplicates. These include checking if any new Accounts match the Account Name of existing Accounts and if any new Leads match the email address of existing Leads.

Account filters in Dupecatcher define duplication types and actions.

Fundamentally this is how DupeCatcher works.

Each Filter checks for duplicate Accounts, Contacts or Leads (Filter Type) and performs an Action depending on whether the record is being created (Insert Action) or edited (Update Action).

Filters operate on an ‘OR’ basis, which allows us to have more than one way to detect duplicate Accounts, Contacts, or Leads.

Prevent Account duplication with filter names in Dupecatcher

Each Filter then contains a set of Rules. Rules are the next level of granularity and state what fields are being matched and how they are matched (Matching Approach).

Rules operate on an ‘AND’ basis which means that all Rules must be true before the Filter will say it’s a duplicate.

Prevent Account duplicates with Name Rules in Dupecatcher

The best part is that Filters and Rules can be customized so you control how possible duplicates are detected and say what should happen depending on whether you are creating or editing a record.

Let’s take the example of Dave Apthorp who’s eager to set up an Account for GenePoint so he can start working on his latest deal. The trouble is that Dave never checks if the Account is already in salesforce and always tries to create a new one. Not only is GenePoint already an Account, but it’s owned by his colleague Shaun Yates.

Genepoint Account duplicated.

Here’s where DupeCatcher comes to the fore.

When Dave attempts to create the Account he is receives the following message.

Error message when preventing duplicate accounts using Dupecatcher.

In this scenario DupeCatcher stops the Account from being created and provides a list (and links) of the potential duplicates identified. From here Dave can go directly to our existing GenePoint Account and confirm if he is creating a duplicate or not.

If Dave thinks he’s creating a duplicate then should stop creating the Account and go have a friendly chat with Shaun about sharing his Account.

If after checking the GenePoint Account Dave decides that he’s not creating a duplicate, then we have given him the ability to ‘Override DupeCatcher’ and create the Account by clicking New Account.

As with the Filter Actions, the Override DupeCatcher feature is optional and can be turned on or off in the ‘Dupcatcher Application Settings’.

This is power of the DupeCatcher. Choose the type of Filter. Define how duplicates are detected. Adapt the available set of subsequent actions to the scenario.

There’s also a nice set of options including whether you wish to bypass record visibility (see the example below). This provides a great level of flexibility and control throughout the entire process.

Here’s how to get started with DupeCatcher

Don’t hold off and let the issue of duplicates snowball. Every day that duplicates are allowed to enter your salesforce will only add to the data clean up job later on.

Head over to the salesforce AppExchange and install DupeCatcher today. If you’re not 100% sure if it’s right for your business, then install it into a Sandbox first and test it more rigorously.

DupeCatcher can be downloaded from here:


On the details page you’ll also find handy links to DupeCatcher’s Info Sheet and FAQ’s.

Related Blog Posts


Why You Need To Compare Average Closed Won Opportunity Size


How to use opportunity conversion reports for superior results


How To Stop ‘Closed Lost’ Screwing Up Salesforce Dashboards


5 Easy Tips That Will Make Opportunity Probability Your Trusted Friend

10 Tips to Prepare Salesforce Import Data

10 Tips to Prepare Salesforce Import Data

“Where on earth do I start?”

That’s a common reaction when contemplating how to import data to Salesforce or improve existing data quality.

And it’s true. There’s a lot to do. Much hard work can be necessary when it comes to importing data. Combined with a certain level of attention to detail.

But relax. Whether you’re importing data for the first time or improving your existing salesforce data, use our tips and best practice guide to make the process a lot easier and achieve a better data quality outcome.

After all, high quality data is a proven driver of salesforce user adoption and accurate report and dashboards. And that means all salesforce users have better insight into sales performance and the sales pipeline.

And remember, if you’re importing new data to salesforce then download our Account and Contact template to get off to a winning start.

1. Spreadsheet for importing data

You can’t just import data from any old spreadsheet into Salesforce. The spreadsheet itself has to be properly formatted and structured. Downloading our Account & Contact template is a good way to start.

Collate your data from the various sources. Existing CRM systems, accounting packages, users’ own spreadsheets, business cards stored in desk drawers are all common sources of existing data. Pull it all together and start populating your spreadsheet.

If you’re improving existing salesforce data here’s what you need to do.  Create a tabular report containing the Account and Contact data you want to improve. Don’t forget to include the Account ID and Contact ID – you’re going to need these when you import your changes. Export your report to CSV format and then follow the tips below to improve your data. When you’re finished, use the Data Import Wizard to update your existing data.

2. Account Name

Sort the spreadsheet by Account Name. Scroll down the list, correcting any obvious spelling mistakes. Check for inconsistencies. If you have three Contacts you’re going to have three rows in your spreadsheet. So make the Account Names consistent within each row.

3. Street Address

Many organisations will have several components to their Street Address (we’re talking about all parts of the address before the City).

It’s essential that the Street Address for each Account is contained in a single cell. Likewise for the Street Address for Contacts. In our case, for example, “3rd Floor, 9 Devonshire Square” goes into one cell, with the two components separated by a comma.

Whilst you’re at it, strip out all the zip codes, city data, phone numbers or anything else lurking in the Street column. Cut and paste that data into the relevant column in the spreadsheet.

4. City, State and Country

Sort the spreadsheet by each column in turn. Scroll down the spreadsheet, making sure the data is consistent within each column. The City column should contain only town and city data for example. State, zip codes (and any other data for that matter) belong in their respective columns.

While you’re at it, correct any obvious spelling mistakes. And make abbreviations and values consistent. CA or California across all data values, for example. UK or United Kingdom. Not a mix of both.

5. Phone numbers

First things first – don’t lose your leading zeros. In your spreadsheet, format all columns containing numbers (US readers, that includes the zip code column) to TEXT. You don’t want 0203 280 3665 beginning with 203 in Salesforce.

Then look for cells with more than one number. The company Switchboard number belongs in the Account Phone column and a direct dial number in the Contact Phone.

Look for any obvious inconsistencies. All UK mobile phone numbers, for example, begin with 07. Cut them out of the Account Phone column and paste them into the Mobile column for the Contact.

Awesome Pipeline and Sales Performance Visibility

Download the FREE Dashboard from the AppExchange today

6. Email Addresses

The scourge of data import!

More problems arise from incorrectly formatted email addresses than everything else put together.

Here are the most common errors:

– Punctuation marks at the beginning or end of the email address.
– Missing @.
– No period mark.
– A character space within the email address.

Here’s how to test for these problems.

  • From the Data menu within your spreadsheet, select Filter. This will add the Filter dropdown function to each column.
  • Click the filter down arrow in the column containing the email addresses.
  • Hover over the Text Filters menu option.
  • Select ‘Begins With’.
  • In the top dialogue box, type the comma mark and then click OK.

This will return all the emails that start with a comma. Go ahead and fix the problem.

Then do the same for the various other potential issues using the same process. Check for character spaces within the email addresses by placing your cursor in the ‘Select’ dialogue box and then pressing the space bar once.

7. Contact Names

Here again you have a choice in how you format your spreadsheet. Place the First Name and Last Name in the same column. Or split them into separate columns. Unfortunately you can’t mix and match.

Remember that whichever approach you choose, each Contact needs to be on a separate row. So if you have five Contacts at the same Account, you’ll have five rows.

Make sure the Account Name and Address are the same in each row. That way, when you import the data, Salesforce will de-duplicate the Accounts so that you’re left with one Account and five attached Contacts.

Check for any obvious errors by sorting the spreadsheet by Name (or First Name and then Last Name if you’re using two columns for Contact name). Make sure names are properly capitalised, that way when you send a mass email you’ll be addressing Gary and not gary. If you’re confident in fixing any spelling mistakes then go ahead and do so, remembering names can be legitimately spelled in different ways (Gary and Garry, for example).

8. Type and Industry Fields

Now is also a good time to set the correct values for the Type and Industry fields. The chances are that if you don’t do it now, then it’s probably not going to happen in the future.

You may need to change the standard salesforce picklist values for these fields. The typical values you’re going to need for Type include Customer, Prospect, Supplier and so on.

There is also a wide ranging set of standard values in the Industry field. Customise these to the specific needs of your business. If you operate in a particular market niche then you may want to change the values to be appropriate to that niche. Be careful not to overlap with the Type field. The two sets of picklist values should be mutually exclusive.

Then sort the spreadsheet by Account Name. Populate (or get someone who knows the data to populate) the Type and Industry values for each record. Laborious, but if it doesn’t happen now then the chances are it’s not going to happen any time soon after you’ve imported the data to Salesforce.

9. Record Owner

This refers to the User that will own the Account & Contact records when you import them to Salesforce. Every record needs to have an Owner, albeit the Account Owner and Contact Owner don’t necessarily need to be the same person.

If you don’t specify the Owner then the person that performs the data import will be set as the Owner. Which may be fine. You can always re-assign the records later using the Mass Transfer function in Salesforce. However, if you’re getting ready for go-live, then ensuring the data is assigned to the correct Owners will often avoid lots of unproductive argument and discussion during the training!

10. Field Length

Nothing is more frustrating than spending lots of time preparing your data then doing the import, only to find some data didn’t load because the field length was too long.

Every field in Salesforce has a maximum length. This is invariably large enough for any well formatted set of data. The problems that occur typically arise due to problems that weren’t picked up in preparing the data, for example, two email addresses in the same spreadsheet cell.

To check for this use the LEN function in a blank column in your spreadsheet. The maximum character length of some of the most important fields is:

– Account Name: 255
– Street: 255
– Phone: 40
– Email: 80
– Last Name: 80

Remember spaces count as characters when you test your data.

Now it’s time to import your data! If you’ve just finished doing the data preparation then have a coffee break before you move onto the import process. You don’t want to mess it up now after all that hard work!

And finally, if you have any difficulty get in touch and we’ll give you some guidance by email or over the phone.

Additional data import resources:

Detailed instructions for using the data import template

  1. Enter Account (i.e. company or organisation) data in columns A to M.
  2. Enter Contact (i.e. person) data in columns N to AB.
  3. If you have custom fields in salesforce and need to import that data, simply create additional columns in the spreadsheet. Use the custom field name as the column header name. It doesn’t matter where you insert the new columns, just locate them in either the Account or Contact sections of the spreadsheet.
  4. If you use multi currency, enter the relevant Currency value in Column B for the Account. Copy this across into Column R for the contacts.
  5. If your salesforce environment uses record types for Accounts (or Contacts) then create a new column. Remember that if you’re using the Data Loader to import the data, you need to enter the record type id not the name.

12 Must-Have Charts For Your Salesforce Dashboard

Download the FREE eBook from our website today

Related Blog Posts


Why You Need To Compare Average Closed Won Opportunity Size


How to use opportunity conversion reports for superior results


How To Stop ‘Closed Lost’ Screwing Up Salesforce Dashboards


5 Easy Tips That Will Make Opportunity Probability Your Trusted Friend

10 tips to merge salesforce environments

10 tips to merge salesforce environments

It can be a tricky business to merge Salesforce environments.

But actually it’s a surprisingly common activity. One company purchases another and both use Salesforce.com, for example. And in many large companies, different regions or divisions within the same business have historically had their own Salesforce environments.

Merging Salesforce environments provides a single, consolidated view of the sales pipeline and sales performance. It means global clients can be managed in a holistic fashion. And there may be a strong imperative to integrate a single Salesforce environment with an ERP or marketing automation system.

We’ve successfully merged Salesforce environments (also known as Salesforce instances or Salesforce orgs) for five different global businesses. In each case there was a mountain of data to migrate. Pipeline and sales values had to balance to the penny. Configuration and code needed to be transferred from one environment to another. Conflicts and contradictions in functionality and data had to be resolved. And users needed minimum disruption before, during, and after the switch.

Based on that success, here are our 10 tips on how to merge Salesforce environments or instances.

1. Create a detailed merge plan and checklist


  • There is a logical order for migrating data and functionality. Use this to create a detailed merge plan.
  • Include user and stakeholder communication within this plan.
  • Include sufficient time for testing and validation of data in the merged Salesforce environment.
  • Create a checklist of tasks that need to be done at the point of cut-over.


  • Merging Salesforce environments is a non-trivial undertaking.
  • Migration of data and functionality needs to be undertaken in a logical and methodical manner.
  • Cut-over of Users needs careful handling in order to avoid confusion and down-time.
  • Success requires planning and preparation, particularly if there is a short cut-over window.

2. Transfer merge data incrementally


  • Do the bulk of the data migration before the cut-over date.
  • Transfer new and modified records incrementally.
  • Do this several times as the cut-over date approaches.
  • At the point of cut-over, migrate only new and modified records from the previous day.


  • It’s not realistic to migrate ALL data on a single weekend or evening.
  • Migrating large volumes of data takes time.
  • Manual effort is required to extract the data from the source instance, prepare the import file and physically upload the data to the target Salesforce environment.
  • The greater the quantity of data associated with a migration, the more opportunity there is for errors to occur.
  • Importing the bulk of the data beforehand means that it can be thoroughly checked. If errors are subsequently introduced with the incremental transfers, then it’s much easier to identify the source of the problem and apply corrective action.
  • Migrating in steps allows the project team to practice and refine the migration process. This significantly reduces the risks associated with the final cut-over.

3. Enable Created and Last Modified dates to be editable


  • In the target environment, create a Case with Salesforce for ‘create audit fields’ to be enabled.
  • During the data import, this feature allows the ‘Created Date’ and ‘Last Modified Date’ for each record in the target Salesforce environment to be set to the same values as the source environment.
  • The ‘Created By’ and ‘Last Modified By’ Users can also be specified during the import process.


  • Without this feature, the Created Date, Last Modified Date, Created By and Last Modified By fields will be set according to the date and person performing the data import.
  • This impacts key reports e.g. number and value of Opportunities created by User each month.
  • It also makes it more difficult for Users to understand the history of each record.

However, bear in mind that the Last Modified Date and Last Modified By are only be set at the point of record creation. If records are subsequently updated during the data transfer process (e.g. to add Opportunity Product Line Items to Opportunities) then the original Last Modified Date and User is overwritten.

This article in the Salesforce.com success community explains in more detail on how this feature works and the Case that needs to be created.

4. Arrange courtesy licenses for both Salesforce environments


  • Arrange for access to the source environment to be maintained for a period of time following cut-over. Do this by requesting courtesy licenses from Salesforce.
  • In the target environment, arrange for temporary courtesy licenses to be available for Users that are no longer active in the source environment.


  • Any problems identified by Users after the cut-over may require going back to the source environment to retrieve or check data.
  • In the source environment, Opportunities and other records are likely to have been created by Users that no longer work for the company and who no longer have active licenses.
  • However, in the target environment, data can only be loaded against active Users.
  • Assigning temporary courtesy licenses to these Users in the target environment means records can be associated with the employees that created the data.
  • This protects key reports (e.g. Opportunities ‘Created By’) and preserves the integrity of the migrated data.

5. Consolidate the source Salesforce environment

Often it’s necessary to migrate configuration and code in addition to data. Use these tips to simplify the migration process.


  • Avoid migrating configuration, records and reports that are not required.
  • Install Field Trip by Qandor from the AppExchange into the source environment. This FREE tool automatically generates reports to show the record count and % of records that have each field populated. Use it to identify any fields that can be deleted.
  • Create a “Report on Reports” and (with agreement from Users) delete reports that are no longer used or required.


  • Obsolete fields can clutter up your nice new, merged Salesforce environment.
  • Fields in the source environment may have been added incrementally over time.
  • Remove those fields that are no longer required (e.g. the field is populated in less than 1% of records).
  • Migrating reports takes time and has many trip hazards. Minimise your work by removing those reports that are not regularly used.

Awesome Pipeline and Sales Performance Visibility

Download the FREE Dashboard from the AppExchange today

6. Use the case sensitive Vlookup to link records


  • Create a custom External Id field in the target environment for every object for which you will be transferring records. Set this as Unique and Case Sensitive.
  • Populate this External ID field with the 18 digit salesforce record ID that is output by the Data Loader when you extract your source data.
  • Use a case sensitive Vlookup formula in MS Excel to link records (see below).


Records need to be related to each other. Opportunities need to be linked to Accounts. Opportunity Product Line Items need to be linked to Opportunities. And so on.

This means, for example, that before Opportunities can be migrated to the target environment, the Accounts records first need to be inserted. Then, to link the Opportunities to the new Accounts, the newly created Account IDs need to be retrieved. These IDs need to be linked to the historic Account IDs from the source environment in order to match up the Opportunities. This means using the Vlookup function in Excel.

But, there’s a problem. Salesforce record IDs are case sensitive. The standard Vlookup function in Excel is not case sensitive. So instead use the custom case sensitive Vlookup formula below.

=IF(EXACT(D2,VLOOKUP(D2,$A$2:$B$2000,1,FALSE))=TRUE,VLOOKUP(D2,$A$2:$B$2000,2,FALSE),”No match”)

– D2 contains the data item you want to lookup
– Column A contains the records that you want to lookup against
– Column B contains the corresponding values you want to return.

Note that the formula assumes the spreadsheet has column headings and that the returned result is populated in Column C.

7. Watch out for trip hazards when importing data


  • Make a conscious decision on whether you want workflow rules, assignment rules and validation rules to apply when the data is migrated to the target salesforce environment.
  • Switch off rules that should not be applied.
  • Where appropriate, configure data appropriately to avoid validation rule errors, or adapt rules to skip the new records.
  • Don’t forget to switch on de-activated rules post migration.


  • Many large existing salesforce environments contain multiple validation rules, workflow rules and assignment rules.
  • Tripping over validation rules is a major source of errors when importing data.
  • Triggering workflow or assignment rules upon can have unintended consequences on the imported data if not switched off prior to import.

8. Use the Apex Data Loader efficiently and effectively


  • Data Loader settings can be modified from the standard values.
  • In particular, pay attention to the Time Zone setting.
  • Set the Time Zone in the Data Loader to reflect the time zone of your User record in the appropriate environment. If the User records in the source and target environments are set to different Time Zones you will need to change the Time Zone setting in the Data Loader between imports and exports. It’s easier to ensure both users are in the same Time Zone rather than do this.
  • Set a smaller batch size (even down to 1) when the records that are being imported need to fire code in the target environment.
  • Use multiple Data Loader sessions, running concurrently, for very large data sets or where the batch size is small.


  • Inconsistent Time Zone settings may result in dates in the target environment being set incorrectly.
  • Any record that triggers code may require a batch size of 1 in order to fire the code and be imported successfully. The results in a large import file taking many hours to complete processing.
  • Running multiple concurrent Data Loader sessions significantly reduces the processing elapse time.

9. Merging salesforce environments is a task for more than one person


  • For large data transfers, have two people working on the migration, particularly at the point of cut-over.
  • Ideally have a third person with responsibility for reviewing, checking and validating the data transfer results.


  • There is scope for human error in migrating large volumes of data. Collaboration between two people in identifying any errors is more effective than a single person working alone, particularly if there is time pressure on the cut-over date.
  • Regularly checking that the reports and dashboards are producing the expected results is an essential method of validating the data transfer results. The third person should constantly be checking the output whilst the others are migrating the data.
  • Having one person working on the next upload file in MS Excel while the other is loading the previous file will substantially reduce the cut-over time.

10. Provide immediate post-merge support


  • Ensure support is available for one to two days immediately after cut-over to resolve any issues.
  • Include system administrators with expertise in the source environment in the support team.
  • If possible, locate the support team in one physical location.


  • Any inconsistencies in data or functionality in the merged salesforce environment need to be addressed quickly to avoid adverse impact on Users.
  • This may require specific knowledge and understanding of the source environment as well as the merged salesforce environment.
  • Experience shows that problems are more quickly resolved if the support team are physically together. This is particularly the case where the problem relates to low-level data issues.

Merging Salesforce Environments

Many businesses gain significant benefits by merging salesforce environments. Our experience shows that success requires detailed planning and preparation, combined with a logical and methodical approach. But it’s very achievable, especially if you use our 10 tips!

And of course, if you’d like to find out how The Gary Smith Partnership can help your company to merge salesforce environments, then please, get in touch.

12 Must-Have Charts For Your Salesforce Dashboard

Download the FREE eBook from our website today

Related Blog Posts

Why You Need To Compare Average Closed Won Opportunity Size

How to use opportunity conversion reports for superior results

How To Stop ‘Closed Lost’ Screwing Up Salesforce Dashboards

5 Easy Tips That Will Make Opportunity Probability Your Trusted Friend