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.)

Salesforce Spring Cleaning, Part 2: Decluttering

It's not quite astronomical spring here in New York yet, but it is meteorological spring… 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. (Here's a link to part 1.)

Decluttering

Once you've been using your PatronManager/Salesforce account for a while, it's likely that you'll have accumulated a bunch of clutter -- things that live on beyond their usefulness. Spending a few minutes deleting old records, files, reports, or apps can help make sure that you and your users are able to find the things that actually matter.  

alli: "i 'marie kondo'ed' my sock drawer last weekend!" (and yes, I have three pairs of cloud socks)

1) Email Templates

Maybe you created some templates for a big fundraising campaign last summer, but the project is over now and you'll never need to send that exact message again. Or maybe a few colleagues were collaborating on a draft, and ended up saving multiple copies as different "versions."

You can choose to delete these old templates entirely (you'll still have all the records where the template was actually used!); or, if you think there might be some value in referring back to them later (maybe you'll want to crib some language for next year's fundraising campaign), you can "archive" them by creating a new folder called something like Archived Templates and letting them live there, separate from places that users might look for an active template in the future.

neither eric nor emily work here anymore

2) Reports

Did you know that you can run reports ABOUT reports? You can! And you can include columns like Folder, Report Type, Created By, and, our favorite, "Last Run [date/time]". That means it's SUPER EASY to run a quick report and see exactly which reports in your account are gathering dust.

Think about how much time you and your users will save when you can see that the report you need already exists and isn’t hidden in the clutter of your reports from 5 years ago!

Once you've run your Reports-report and confirmed the criteria for the reports you're ready to purge, go to Setup | Data Management | Mass Delete Records | Mass Delete Reports and use the criteria fields there to go ahead and clear that clutter.

"not THAT stupid, pt. 1"

3) Installed apps

Go to Setup | Installed Packages and see what's in the list there. Are you actively using everything that's listed there? That's important to know… especially if you're paying for them! Have you let free trials expire? Did someone else install apps that you don’t even know exist?

While you're at it, if you're a PatronManager client, you might take this opportunity to browse through our Recommend Apps in the PatronManager Help tab to see what's new or if you can find a better solution than you might currently be using.

there are at least 4 apps that shouldn't be here any more

PS: The list of Installed Packages is infuriatingly always a mess because you can’t sort or filter it in any way. Vote up this idea to fix that!

(On to part 3!)

Compact Layouts, De-Mystified

We've been studying for our Platform App Builder Transition Exam, and one of the topics listed in the study guide (and in SalesforceBen's helpful post) is Compact Layouts.

In short, according to the Salesforce Help site, "Compact Layouts are used in SF1 and Lightning to display a record's key fields at a glance."

We were already familiar with these to some degree, as we actively use them in our own Salesforce org:

opp name, stage, close date, amount

...but in studying further and trying to learn all the details, we got a little confused! Now that we're un-confused, we are here to share our newfound knowledge.

We read the documentation (about 47 times) and noticed something strange, so we asked The Internet:

Compact layouts question -- the 1st 4 show up in SF1, the 1st 5 in Lightning Exp. But why can we add 10? What happens to the rest? #askforce

— Allison Klein (@nyalli) February 22, 2016

All the documentation talks about adding 10 fields to the Compact Layout, but all the explanations of how they get used seemed to only talk about fields 1 through 5.

Turns out we kept missing a key line in all of those help materials, which The Internet helpfully pointed out for us:

@pchittum @nyalli They drive what appears in the feed item when a record is created via publisher action: https://t.co/XKp4Vs0hZB #AskForce

— Rob Alexander (@RobAlexander) February 22, 2016

Right there in the "overview" lesson (and in the release notes), it says "In the full Salesforce site, compact layouts determine which fields appear in the Chatter feed item that appears after a user creates a record with a publisher action."  

We're still not sure how we missed that sentence every time we came back to this page! We think it's because the "definition" of these layouts is always described as being about mobile and Lightning, so we weren't expecting the fact that they get used in SF Classic as well; also, we started learning about these from the SF1 Mobile lesson in Trailhead, which doesn't talk about their application in SF classic for obvious reasons.

We tested this and sure enough, it's true:

4 fields...

...more than 4 fields! (though you still need to click "more" to see all of these)

So! There's the answer to why compact layouts support up to 10 fields!

If you're not using them yet, you totally should! It's the closest thing we have right now to this popular Idea in that it allows you to bring certain fields to the top of a record in SF1 that might not need to be at the top in SF Classic. For example, our Opportunity page layout are huge, and organized into sections that make sense and are helpful when you're using a browser on the desktop, but there are important fields scattered all over the page that we can pull together into a compact layout so they're still visible at a glance on our phones.