pivot swivet

As many of you know, I’ve spent the last week recovering from surgery, but that hasn’t stopped me from dreaming and scheming about how spreadsheets (and puns) can heal inefficiencies and frustrations for activists and changemakers. In this installment of #DearSpreadsheetWhisperer, Pivot Swivet calls in support with prepping data for a mail merge. The solution? A lil pivot, a lil vlookup, a lil mail merge, and a LOT of saved time + a better donor acknowledgement letter. Not too shabby!

Dear Spreadsheet Whisperer,

My organization wants to send new donor acknowledgement letters where we summarize each household’s giving across a few categories (donations, event tickets, sponsorships, etc). We also need to show our donors their total giving amount and fair market value for tax purposes. The problem is that when we export the data for the mailmerge, we can’t easily organize it by household and category – and that’s creating all sorts of problems for our mail merge. Is this even possible? Help!

Sincerely, Pivot Swivet

Dear Pivot Swivet,

First of all, a congratulations is in order!! From what you told me, your organization is using Salesforce effectively to handle your donor’s giving history. You’ve already mastered gift entry and running reports. (For other readers, if you want to work on Salesforce reporting, I really recommend this free Trailhead superbadge.) Also, it’s really cool that you want to improve how you do donor acknowledgement! That is no small feat! This shit’s complicated and our organizations and movements are stronger when we have people like you sorting out operational details. I appreciate you!

Let’s walk through some of the steps we took together.

One order of data, coming right up!

First thing’s first! We gotta export our data. Here’s a tip that can save you some cringing later – when you open your Salesforce report in Excel, immediately create another tab. In that tab, copy the URL of the Salesforce report you used (if you saved it) AND in WORDS write out the criteria you used to filter down your data. You’ll need those if you ever need to recreate the report!

Since we are exporting the data from Salesforce into a spreadsheet, it’s better to not have any groups or summaries. Yes, Salesforce can handle groups and summaries, but they can get messed up when we export the data. So for this project, we’ll need to export your data as a CSV file (not Printable View or .XLS). We want to strip down all of the formatting so that you start with a blank canvas 🙂

Row, row, row your boat

Your data will come out of Salesforce (and presumably other database systems, too) in a big ole spreadsheet with lots of columns and LOTS of rows. Ask yourself, “what does each row represent?” In our case, each row represents ONE donation. Each column represents an attribute related to that donation (date, amount, etc). In some reports, each row might represent one PERSON affiliated with a donation or one PAYMENT (bigger gifts are sometimes paid in installments), therefore if two people or two payments were affiliated with the donation, the donation would appear TWICE.

Pivot Perfect

Now that we know what each row represents, we can start grouping. In this case, we want to group by household so that each household gets ONE letter regardless of which household member made the donation. In some databases, you can group by address (watch out for co-op houses or housemates who don’t merge their giving!) or by Household Name. Watch out when you’re grouping by Household Name … you might end up with one Diaz Household, Schwartz Household, or Smith Household when you should have many. I recommend grouping by “unique Household ID.” In Salesforce, another name for that is the Account or Organization ID. The best practice is to use the 18 digit version, because the 15 digit version is NOT case sensitive.

The best way to group and summarize data in Excel OR Google Sheets is by using Pivot Tables. Since this blog post is more of a recipe and less of a tutorial, we’re not going to delve super deep into Pivot Tables here, but trust me, they are AWESOME! (See demo gif below!)

Using Pivot Tables, we can group our rows (donations) based on Household ID and we can group our columns by donation types. We can direct Excel or Google Sheets to summarize the donation amount based on “fair market value” or “actual donation amount” and we can summarize based on count (number of donations) or sum (total $$). Pivot tables are brilliant!

One more step!

Now that we’ve modified our pivot table with our row and column preferences, we have a new table.

• What do the rows represent? A household’s giving history, summarized for the entire year!
• What do the columns represent? Donation types, summarized for the entire year!

We grouped our data based on Household ID, but we are going to have to go back and replace the Household ID with the mailing info, names, and any other information we want to ultimately merge into the letters. The best way to do this is with a VLOOKUP. If this function isn’t in your comfort zone, don’t fear! I KNOW you can learn it – and you can always ask for help.

My work here is done!

You’ve taken your source data, summarized it in a pivot table (with row groupings and column groupings), appended donor household information, and organized everything so that each row is ready to be mail-merged into a letter. Hooray! (Btw, there are systems that enable you to do these types of merges directly in Salesforce but they tend to be labor intensive at first and a bit expensive. You can still ask me about Conga if you want to learn more!).

Pivot Swivet, you totally got this & I’m rooting for you. Thanks for reaching out so that we could solve your problem and blog about it!

With love, discipline, and solidarity,

Spreadsheet Whisperer