I help an organization that is squarely in the center of groups the president elect will be sure to attack. Here’s one way that I helped them this week.
problem
There are over 1,000 Opportunities (aka Donations in the NPSP/Nonprofit Success Pack) with a Stage of Prospecting and an Amount of $0. This left over from some old fundraising methodologies that we tried many years ago. Now, the organization is doing a big, end of year fundraising drive and it is useful to make “Prospecting” oppties again. The problem is, we have 1,000+ old records that need to be taken to the dumpster. And worse yet, some of these Oppties had Tasks (Activities) attached to them, which we didn’t want to lose.
terminology note
In some places, I use the following terms interchangeably. This is mostly because these things do have multiple names in SF, and also because this is just how my writing flowed this time. So, I wanted to make this part clear early on and try to avoid some confusion.
- Donation = Opportunity = Oppty
- Task = Activity
- “Related To” field = WhatId, Contact field = WhoId
- [Bulk] Upload = Import = Update
Learn more about the Activities data model here!
how I helped
First, I used reports with filters and cross filters to help me understand the scope of the problem. I think a lot of Admins and bloggers either don’t do this step, or do it but don’t talk about it. Well, let’s talk about it!
I wanted to understand how many Oppties there were in this situation. Perhaps some of them were new and should be preserved. Perhaps some had tasks attached to them (part of our criteria for keeping), but the tasks were meaningless (so maybe we *should* delete??).
I ran a report of all Oppties with the Stage of Prospecting. I noticed that some had the Amount ($$) filled in, which means that the fundraisers were already working to develop an appropriate donor ask. I wanted to deal with the low-hanging fruit first, which in this case were Oppties with NO $amount and NO tasks recorded. There were about 80 of these, and I quickly exported their Id numbers and Bulk Deleted them using Dataloader.io.
I took a closer look at the Tasks. There were many Tasks that were Open and due 3+ years ago, created via upload. Disclosure: I created them. Yikes. They did not need to be preserved, so I simply deleted those using an Upload.
Then, there were the interesting tasks. I decided I had two options here. I could mark the related Oppty as “Closed Lost” since some attempt to cultivate a donation had been made. However, storing a Closed-Lost donation for $0 does not give future colleagues much useful information. I think the tasks history made more sense on the Contact than the Oppty. The best option available to me was to MOVE the Tasks and relate them to the Contact instead of the Oppty.
I learn a LOT by doing data cleaning manually and this process was no different. I opened dozens of records to visually review them. I manually “rehomed” Tasks by deleting the “Related To” data value (this is where a Task is linked to an Oppty) and ensuring that the Contact was populated, instead. This rendered more Oppties in the state of, “no tasks or other meaningful info attached,” so I could delete them. However, using the ><30 rule for myself, I knew that I needed to automate this to deal with the remaining 900+ records.
In my practice rounds, I noticed that if I deleted an Oppty, the related Task was also deleted. This would lead to us losing important data touchpoints, and I knew I wanted to keep the Tasks. So, how to unlink the Task and the Oppty? I ran an Update using Dataloader.io where WhatId was blank, ensuring that I mapped the field properly to the empty column, but WhatId (Related To) field was still populated afterwards. “There must be a different method to do this…” I thought.
Activities in Salesforce (aka Tasks and Events) are super funky. They have a variety of special properties that set them apart from other Standard Objects. One particularly interesting feature is that they sport two “polymorphic” fields, meaning they can link to any (or most) objects (Standard or Custom) without needing a designated Lookup Field. This is why you can take a Task record and connect it to a Donation OR a Campaign OR a Household.
In the case of my Tasks, I wanted to wipe out the “WhatId” field (which connected the Task to an Oppty) and, separately but simultaneously, populate the WhoId field (which connects the Task to a Contact).
So, I ran a “Oppties with Activities” report to pull the Id and Primary Contact from the Oppty and Activity Id, WhatId, WhoId, Status, DueDate from the Activity.
Next, I uploaded the report results to my Dataloader.io task, and “mapped” the Primary Contact (from Oppty) to the WhoId field (on Task). This did not change the Oppty data at all, but allowed me to basically copy the Oppty data to the Task data in one, simple step. However, the first time I tried this, I also wanted to clear out the data in the Related To field on the Task and this part of my operation did not work.
I did some research and I learned that there is a special checkbox in Dataloader.io that must be selected if you want to override existing data in a field, and instead make the field blank or “null.” This checkbox is under the “Advanced” drawer on the “Run” page of an Upload. Once I had this checked off, and ran my Upload, I was able to achieve my goal!
After running this successful operation, I went back to Salesforce to re-run my reports. I ran an Oppty report with a cross-filter for Oppties WITH Tasks. There shouldn’t be any, now that I unlinked the data. Indeed, my report came up empty. Then, I checked some individual Contacts that I knew were represented in the Activity data. I made sure that Tasks showed up in their Activity Feeds the way I expected them to, and there they were.
Now, I had true, empty Oppties that I could Batch delete using an upload.
I always run reports after uploads to confirm that everything looks as it should. This time was particularly interesting. There were about 200+ Oppties remaining with a Stage of Prospecting. Looking at this list with my colleagues, we could very quickly tell that some were stale, some were meaningful, and some were badly needing updates. This was a much more manageable and realistic list, and it enabled my colleagues at the org to meaningfully plan and track their fundraising efforts.
reflection
I enjoyed this project because I was able to learn some new tricks in Dataloader, cleanup a mess of my own making, and leave an org better than I found it. Asking staff to go through 1000+ oppties (when most/many were ultimately meaningless) is a recipe for burnout. There is so much at stake right now for social movement orgs, let’s at least make sure … The Data Are Alright.