Analytics April: Troubleshooting and Checking Your Work

Welcome to the continuing June 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 tricks for troubleshooting and checking your work.

Troubleshooting and Checking Your Work

Filter Flips

Whenever we create a new report that's filtered to include or exclude a certain set of records, we like to "flip" our filters to check our work before we complete the report. Often a "bad" report with wrong information is really caused by bad data, and performing this kind of check can prevent you from wasting your time agonizing over the report itself.

For example: If you're running a report of donors who live in "NY" state (Billing State equals NY), trying flipping the filter to be (Billing State not equal to NY) and check for BLANK state fields, or records where the Billing State is entered as "New York" or "N.Y." instead. You might find someone who would otherwise have slipped through the cracks due to inconsistent data entry (and now you have the chance to fix it!).

Donors who live in NY.

Donors who DON'T live in NY... or do they?!

Filtering to cut down a long list

Once in a while you'll need to run a report that returns a lot of records -- more than the 2000 you're allowed to view within Salesforce itself. In a lot of cases, that won't matter too much -- you're probably more interested in a summary-level analysis most of the time. But what if you're trying to poke around at your data… for example, working on the spring cleaning project we mentioned a few weeks ago? If your report has lots of rows, you won't be able to sort by grouping like we suggest.

We have a pretty silly trick for getting around that limit -- come up with a totally arbitrary filter to cut down the results so you can go through them bit by bit. Add a filter that says " Full Name | starts with | a,b,c,d,e,f,g,h,i,j,k,l " -- you'll cut the list in half, but know exactly what you're cutting out. Then you can proceed with " Full Name | starts with | m,n,o,p,q " and so on. (Just remember that you might want to end with something like "x,y,z,1,2,3,4,5,6,7,8,9,0,@" -- watch out for those non-alphabet characters that snuck into your record names!)

A through F

G through O

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.