Analytics April: Labels and sections for user-friendly reports

Welcome to... let's call it the May edition of Analytics April. We have a lot of things to say about reporting in Salesforce, so we're doing a short series of posts on the topic. Today, we're writing about how to keep your report types clean and logical for your end users.

Cleaning Up

Field labels

We'll save the longer rant about Custom Report Types for another time -- for now, let's just make sure we're giving our users the best/cleanest experience possible.

Do you have any reports in your account that look something like this?

so many layers!

If you've built a custom report type that involves pulling in fields from related records via a long series of lookups, you're probably familiar with the result: column headers that go on forever. Thankfully, this is something you can fix! (We won't say it's easy because it's a pretty annoying process, frankly, but at least it's possible.)

Go to the Edit screen of the CRT and find the field with the too-long label (yeah, good luck with that) and double-click on it -- you'll be able to edit it!

before, terrible:

after, much better!

Do that for all the fields-via-lookup in your report type, and you'll end up with a much nicer end result.

christine & john smith sure do buy a lot of tickets...

Field sections

While you're at it, if you're making a custom report type that people in your org are going to use all the time, it's worth being a little careful with the sections (or folders, as they display in the report builder itself) -- when you're adding fields related via lookup, make a new section for them so it's easy to find what you're looking for:

the ridiculous back-end UI for custom report types

what your users will see!

Analytics April: Tips and Tricks for Faster Report Creation

Welcome to Analytics April! We have a lot of things to say about reporting in Salesforce, so we're doing a short series of posts on the topic. Today, we're writing about  some specific tricks for running reports, from secret right-click menus to sneaky ways to drag and drop fields.

On the Reports Tab

Add more columns

The default view of the reports tab doesn't tell you much, but it's easy to add some more columns with useful information (like Created Date or Last Modified By)! Just click on the arrow hidden on any column header, you'll see all the other columns that are available to add. We usually add them all!

so many columns

Jump to Edit mode

When you find the report you want, click the little arrow on the left in the Action column to find a menu with some secret options. This is particularly helpful if you know a report has a lot of data and is going to take a very long time to run -- if you know you'll need to make changes, you can open it directly into Edit mode and save yourself a few minutes.

action!

Speedy Shortcuts

Collapsing folders

The Fields pane is organized into folders by Object (sort of*), and the folders can be opened and closed! If you're looking for a particular field but you're not sure of its name, it might be helpful to hide all the fields from other objects to narrow down what you see.

*Well, by object, yes, but also split up by things like custom fields, “general,” whatever that means, and of course, if you’re working with custom report types, the groupings can be really funky if you've added a lot of fields that come from lookup relationships.The point is that collapsing folders is a good step to take if you can’t find what you’re looking for because it might be hiding at the end of a really long list of fields.

Field list filtering

The "Quick Find" section at the top of the Fields pane offers some great shortcuts to finding fields. One of our favorite tricks is to toggle between the field types (the four little buttons for text, number, and date) -- if you're looking for Close Date in a Donations report, it's faster to just hit the "date" toggle and find it towards the top of the list than it would be to type the word "Close" into the box.

toggle!

Drag and drop!

So many things in the Report Builder can be dragged around from one pane to another! You might already know that you can drag fields from the Fields pane to the Preview pane, but did you know you can also drag from Fields to the Filters pane, or even from Preview to Filters??

magic

Also, our favorite trick of all for adding a bunch of fields at once: If you've got the Preview pane set up to hide details (so you're only seeing summary groupings), you can Ctrl-click to select multiple "number" fields at once in the Fields pane, and when you drag them into the Preview pane to add them as columns in your report, you'll get the chance to summarize them all at once.

even more magic

Analytics April: The Reporting Process

Welcome to Analytics April! We have a lot of things to say about reporting in Salesforce, so we're doing a short series of posts on the topic. Today, we're stepping back to look at the big picture: How do you approach creating a report from scratch?

We’ve hosted a conference for our customers for the last three years, and we keep track of the attendees using Campaigns, Contacts, and Accounts in Salesforce.

We're in the planning process for the 2016 Community Meeting right now, so a bunch of us were working together to pull some data about repeat vs. unique attendance. Nathan, Claire, and Michelle were trying to find out the number of people who had attended the conference in 2014 and 2015, and how many of those people had actually come to both (so, people who were part of the Campaigns for 2014 or 2015 conferences, or both).

Our first pass at running a report for this resulted in hilariously incorrect numbers. Even though we knew there were a bunch of people who had attended both prior conferences, our report made it seem as if the number of repeat attendees was zero.

Luckily, we were able to spot the wrong-ness of the results right away… and while we worked on fixing it together, and brought in Alli for some extra assistance, we realized our experience would be useful to share on this blog!

"i don't need the report, i need you to remember the experience"

Here are some thoughts about running reports, and the process of checking you own work as you go:

Think ahead about what an accurate report might look like

Before you even run your report, take a moment to visualize what the outcome should be. In our case, we didn't exactly think ahead, but we were paying enough attention to notice that we were not successfully reporting on unique attendance because the total number of people in the report was too high.

So if you're trying report on the progress of this year’s annual fund, you should already have a ballpark estimate of what that number should be. If your organization has never raised more than $500k in a year before, and your report shows $2m, that might be awesome? But you also might have a reporting problem.

Or if your venue has 538 seats and you know your average ticket price is $30, a report that shows you 200 tickets sold for a total of $500 in revenue should catch your eye. Check your reporting work first, before freaking out at the box office manager or accusing the marketing manager of putting out too many discount codes in the field!

Know your data structure

In order to troubleshoot an inaccurate report, you have to understand how the objects relate to each other for the specific scenario. In our case, we had to recognize that we weren't just looking for Campaign Members of those Campaigns -- we needed to filter on the Campaign Member Status as well, and also make sure the Contacts belonged to an Account that was actually a PatronManager client org, and lastly use a "power of 1" formula to get at the number of unique attendees across both campaigns.

For fundraising reporting, the more you know about the relationship between Contacts, Contact Roles, and Donations, the better off you'll be. (Look at the Schema Builder if you need help visualizing how all the pieces fit together.)

connections!

And don't forget about Report Types!  You could be missing data entirely if you aren’t using the right report type. Most Contact-based reports, for example, use the standard Accounts and Contact report type -- so if for some reason you have Contacts that are not associated with Accounts, you’ll be completely leaving them out of the picture -- which might be the right thing to do! But only if you know that you're doing it on purpose.

Collaboration is always good

Again, we knew our report was wrong right away -- we know there were repeat attendees in there somewhere. But beyond that, we didn’t really have an immediate educated guess as to what was going on. It was only by staying on the phone and talking to each other that we realized each of the fixes.

As an admin, you know a lot about the working of your Salesforce/PatronManager account, but you might not always know all the details of the work itself. Alli isn't part of the core conference committee, so they didn't have the same real-life understanding of how we were tracking attendance. And Nathan, Claire, and Michelle knew that part, but needed Alli's fresh eyes in order to construct a report that took everything into account.

Salesforce Spring Cleaning, Part 3: Data Cleanup

It's officially springtime here in New York… so it's time for some spring cleaning!

As the admin of your PatronManager account, you're probably pretty busy most of the time, creating reports, refining processes, answering questions for your users… there's plenty to do. Outside of those day-to-day things, though, there are lots of other tasks that don't need your constant attention but still shouldn't be forgotten about forever. In this series of three posts, we'll make some suggestions for tasks you might include in a quarterly cleanup of your Salesforce account.

Data cleanup

Time for some more decluttering! Last week we talked about how to clean up templates and reports in your account; this week we'll move on to cleaning up your data itself. Bad data is worse than no data, so you'll want to make sure you're giving these things attention as part of your quarterly checklist.

1) Deduping!

If you've got your users following best practices at least MOST of the time (searching for accounts/contacts before creating new ones, and regularly qualifying ticket orders/donations/signups if you're using PatronManager), you might have some duplicate records that snuck their way in to your account, but you probably don't have tons and tons of them.

Still, it only takes one or two encounters with duplicate data for your users to feel confused and disgruntled. You might be hearing complaints from your colleagues who are more directly involved in data entry or reporting.

A good way to start understanding the scope of your (possible) problem is by running a report -- a simple All Contacts report, grouped by Full Name or by Email and then sorted by Record Count descending will let you just spot check to get an overall sense of the quality of your data. Any grouping that has more than one record is worth investigating as a possible dupe. You might notice hundreds and hundreds of problem records right away, or you might just catch one or two.

i think we have a problem

From there, you can decide how to address the problem. Merge the one or two dupes that you found and move on, or call in the big guns and use a tool that will help you tackle a larger set. We use DemandTools when we're helping our clients; you might prefer something a little more lightweight like Cloudingo if you're doing this yourself.

2) Field audit!

Look, we're pretty sure about this one: You have fields that aren't being used enough to justify their existence. Account, Contact, and Opportunity/Donation tend to be the worst offenders. Either you created a bunch of fields a long time ago with the best of intentions but they turned out not to actually be important to your business, or you created one-time-use fields specifically to get through a particular project, but never actually nuked the field after you were done using it.

Time to clean those up!

The Field Trip app is the fastest and easiest way to identify these fields where you don't have good usable data. This tool can be used to analyze your entire database or just certain segments of it, and it will tell you what percentage of records have values in each field for that object.

field trip!

Then call a meeting with your colleagues and get their buy-in about proceeding with the cleanup. One good way to ease people into the idea of parting with a field they might think is still important: Start by removing it from page layouts, then hide it with field-level security. Give your colleagues some time to get used to not having the field around, and if you can get through til the next quarter without ever needing the field, go ahead and get rid of it. (This is basically this "outbox" concept, just with fields instead of stuff in your home!)

We've called this series of posts "spring cleaning," but make it a habit! Each quarter, take a day or two to run through all the items in each of these three posts. (In fact, go right now and schedule this in your calendar for each quarter! You'll thank yourself later when your Salesforce account is sparklingly clean and organized.)