Creating Mailing Labels

Here is probably one of the more common and mundane tasks that a user of salesforce.com has to do every so often - create and print mailing labels of customer addresses. Both marketing users and sales reps will occasionally need to take a list of contacts and print mailing labels from them. The task is fairly straightforward, but it contains quite a few steps and can be confusing. To make it a little easier, I've laid out the basic steps below and made an example video so that you can watch me do it.

The Basic Steps of Printing Mailing Labels

using

Salesforce.com

1) Create a report in Salesforce.com that contains all the contacts or accounts that you want to mail to.

- There are address fields in both contacts and accounts as well as shipping and billing addresses in both areas. I mention that so that you'll be sure to get the correct address into your report.

- When you make your report, be sure to include the first and last name, and all the address fields you need such as street, city, state, zip, etc.

- Set the criteria to filter only the contacts you need. You might want only a certain area code or all the contacts that are receiving the newsletter.

2) Export that Report to MS Excel using the export function given on the report.

- Save that report in the excel file format (it might give you the option of saving as a web page).

- Save that report to your hard drive. Word will need to load it from the hard drive.

3) Prepare Labels in MS Word using Mail Merge function.

- You'll be using both Excel and Word to complete this process. Excel contains the list of addresses, Word formats the labels and prints them. So we'll use the Mail Merge function in Word to prepare and print the labels.

- The Mail Merge wizard is located by clicking on Tools/Letters and Mailings/Mail Merge. It does a very good job of taking you through all the steps. Basically, in Word you have to choose a label type, load the recipients list that is in the excel file, format the label, merge the list, then print it.

There are a lot of individual steps to this whole process, but once you grasp what is going on, you won't have any difficulty. Watch the associated video for all the details. If you have any questions and need assistance with this process, you can reach me by clicking here.

Pulling Clients from the Black Hole

Do you ever have clients that you lose in a black hole? You know, the "what ever happened to the xyz account?" question that comes up every now and then. Salesforce.com has done an amazing job of helping companies keep track of existing clients, but there is one report type that I haven't seen inside salesforce that can be solved using excel instead.

I call this kind of report the "without" report. Salesforce has built-in functionality to show you items that are linked; for example, it can show you all the contacts that have upcoming activities. It can show you all the Accounts with open Opportunities. But what if you want to see all the Contacts "without" an open Activity or all the Accounts "without" any Opportunities associated with them - you can't - because you are asking salesforce to show you what doesn't exist rather than what does exist.

A client asked me for just such a report this week. He was worried about losing prospects in a "black hole"; meaning, he wanted to know which Contacts had no open Opportunities or open Activities. He knew he had dozens of Contacts that had no upcoming activities - these clients wouldn't be hearing from him because he had never set a task for some kind of followup in the months to come. These clients were in a black hole and he wanted to pull them out.

Here is how we found those contacts. We created two reports. One called "All Contacts" and the other called "All Open Activities." We exported both reports to MS Excel using "Printable View." We then copied the second report into a second worksheet on the first report and renamed the tabs. So to sum up what we've got so far, we had one MS Excel workbook containing two sheets, the first worksheet is called "All Contacts" and the second worksheet is called "All Open Activities." There isn't much on these worksheets. The first one has one column called Contacts. The second worksheet has two columns: Contacts and Activities.

Essentially, the way we solve this problem now is to copy onto the first worksheet the activity from the second worksheet associated with each contact. So for each contact on the first worksheet, a lookup will be made onto the second worksheet. If that contact exists on the second worksheet, we'll copy the activity over to the first worksheet. Then we'll see all the contacts that have an open activity and all the ones that don't - which is the answer we're looking for. Now by simply sorting the list, you'll have a block of contacts with no upcoming open activity.

The excel function we used to lookup the activity on the second worksheet is called VLOOKUP. This formula will be located in the column next to the contact name on the first worksheet. It has 4 operators. They are:

Lookup_Value: This is the field containing the value that you want to search the second sheet for. So we'll put the cell containing the contact name in there. It will be the cell just to the left of where you're putting the VLOOKUP formula.

Table_Array: This is the table containing all the contacts and their open activities. For us, it is the contacts and activities columns on the second worksheet. Highlight that entire table, both columns. Be sure to fix the value with $ signs so it does not slide as you copy VLOOKUP into the rest of the column. It will look something like this: "All_Open_Activities!$A$1:$B$35"

Col_Index_Num: This is simply the column that contains the answer (the activity) that we're copying from the second sheet into the first. For our purposes, it is column 2 - so just put a 2 there.

Range_Lookup: Just put FALSE. This has to do with how closely excel tries to match the contact names. In our case we want an exact match. If it doesn't find an exact match it will put "NA" for the answer.

The formula will look like this: =VLOOKUP(A2, Sheet1!$A$1:$B$35, 2, FALSE)

Now just copy the formula all the way down the second column and sort the list based on the activities in the second column which will put all the NAs together. The NAs are the contacts that do not have any upcoming activities scheduled.

This same methodology can be used for any "without" report. It can show you the records of any object that do not show up on the related list of another object. Accounts without Opportunities, Leads without Activities, Accounts without Contacts, etc.

Sales System or Business Management System?

When most businesses think about transitioning to Salesforce.com, it is normally with the intent of streamlining the activity of the sales reps. Management starts to realize how many sales have been lost because a rep just forgot to call someone back or forgot a detail that cost the company business. Salesforce is for most companies a way to clean up the business of sales. In their mind it is a system for the sales team. Other companies have made the leap to salesforce because they need more visibility into the sales process and the pipeline. In both cases, management is thinking about the sales process, either cleaning it up or getting more information from it.

Once a business gets started with salesforce.com however, they start seeing the potential to manage aspects of the business that are not sales related. Before long they say, "Hey, what is this cases stuff? Could our support reps be using that?" The next thing you know, you've got the technical support team using salesforce.com. Salesforce built this functionality into the system already with Cases/Solutions.

Campaigns are another early extension to the sales related functions. Marketing is often closely associated with sales and tracking the effectiveness of marketing campaigns is a natural progression from tracking the progress of a sale.

You'll see another built-in part of salesforce.com that many companies start using soon is "Contracts" - don't confuse that with "Contacts!" Once a sale is complete, a contract is typically signed. Why not track the contract right in salesforce?

I think the next logical function that many companies move to next is some kind of tracking of the production/fulfillment of the product. When a sale is complete, somebody in the company gets working on delivering to the customer whatever product or service just got sold. How nice would it be to manage this process in salesforce as well? After all, all the information about the customer and their needs is already in the system. An opportunity fully details exactly what they need, why not just ad a section to salesforce that contains more data about the details of the fulfillment of the deal. In this way many companies begin their foray into custom objects.

Maybe HR and Finance want to get into the act. I've seen an "Employees" tab on one client's system. HR used that custom object to keep employee records up to date. For finance, there are plenty of ways to integrate. In my last post I wrote about a custom object I helped a client create called "Invoices". Others use products from the AppExchange to fully integrate salesforce.com with quickbooks.

The possibilities are truly limitless. The challenge is simply "Salesforce.com Vision". If you can "see" it, there is probably a way to do it. I guess that is where I come in for my clients - I have seen so many neat ideas as I work for multiple clients, that I'm able to recommend extensions to salesforce functionality that my clients may not have thought of. It is a cool position to be in.

I think salesforce.com is reaching the point (I haven't heard them say this, I just suspect it) where they might wish they had called the product something else. The name kind of has them pigeon-holed as a "sales system" when it is really so much more than that.

Custom Objects: Customer Lifetime Value

Today I helped a client create a mechanism for determining the lifetime value of a given client. His problem is not unique. Essentially, this client does not know how valuable a given customer will be when the sale is won. In their business, they have an initial setup fee and a monthly service fee, but the length of time that the customer will continue to use their services is unknown, sometimes it's for 3 months, sometimes much longer. So without knowing how much a client will earn for the company, the "Amount" field of the opportunity is just a guess.

We discussed possibilities. One idea was to update the opportunity on a monthly basis to show the accurate total value. This is problematic since one Account might have multiple opportunities as the company upsells other services - which will confuse the total value. In addition, returning to the same opportunity month after month and updating the value is labor intensive.

We also tossed around the idea of creating a total value field right on the account page that could be updated. This solved the multiple opportunities issue but not the labor issue and it isn't very clean in the sense that you have no feel for what services makes up that total value.

We ended up opting for a custom object called Invoices. The custom object would contain the amount of each invoice and other fields directly from the invoice such as invoice number, description, type of service, etc. One of the fields is a lookup field to the associated Account. The Invoice object was added as an account related list and as a tab next to the opportunities tab.

The custom object really harnesses the power of salesforce.com. An object is the term that salesforce.com uses to classify all records used for a given purpose. For example, "Contacts" is an object, "Accounts" is an object, "Opportunities" is an object. They will usually have their own tab, fields, and page layouts. Custom object records can link to other objects - (just like a contact links to an account) and once linked can show up on the related list of the other object. Once a custom object is deployed, users can start creating new records of that object type.

So for my client with the new Invoices object - now, on each Account record, he has a list of all invoices for that client. The records can be imported en-mass using any of several tools or entered each month by someone in finance as part of their normal invoicing process.

This now makes possible any number of reports that show total value of any customer, thus allowing Return-On-Investment calculations, etc to be made.This was a clean solution, easy to implement, and added a great deal of visibility value to this client.