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.