When I cook, I usually start with chopping onions and garlic. Hot oil, simmer, shake. Enter, spoonrest. Enter, dishtowel – toss over my shoulder. Sprinkle of salt. Shake and stir. Layer ingredients, roughly chopped but thoughtfully assembled. Aiming for caramelized, but not soggy. Sometimes it works out, sometimes it doesn’t. Too much liquid. Too little heat. Or something else? More salt. Spoonrest is piling up with taster spoons. Aw, who am I kidding? I use the same spoon every time. Take me for what I am! The mess is accumulating, oh yes – the sink is full, but not yet precarious. What does it need? What does it need? A sprinkle of lemon? A dash of toasted sesame oil? (Ah, I love the ridged texture of the glass bottle in my hand). I never cook with pepper. Y’all can add it on your own.
Apparently, inspiration from Samin Nosrat continues in this blog post…
Yesterday, I was presented with an interesting data challenge, which seems appropriate to write about the day after Giving Tuesday (see what I wrote on the subject last year!). One of the organizations that I support wanted to amp up their systems for internal fundraising notifications. Let’s dig into the problem, and then the solution! There was no recipe to follow, so I had to make it up as I went along, combining features and flavors that have worked well in the past.
Problem
Generous, sustaining donors have committed to making a 3-year recurring donation. When these payments come in, we need to notify the fundraising team so that they can thank and cultivate the donor.
Bonus – it would be super helpful to tell the fundraising team WHICH installment the payment was… for example, if it is payment 3/3, it might be time to ask the donor to commit to another 3 year cycle.
Solution
Well, this would be pretty easy if the donations were already numbered sequentially… but guess what… they weren’t! BOO! But I didn’t despair! I tied up my apron and got to work. “I’ve done hard stuff before,” I told myself. “Let’s get to work!”
Let’s pretend that the diagram above isn’t labeled as Customers and Orders. Instead, it’s Donation and Payments. You can see that there’s one Donation connected to 3 different installment payments. This is a good model for how we set up the data for recurring gifts. Each payment has a scheduled date, and when that date comes, our payment processor will charge the person’s card on file. Good? Good. Add salt and pepper to taste.
Because of the way the Payments are connected to the Donation in Salesforce (our database), I can summarize them using a feature called a Roll-Up Field. So, Salesforce can automatically calculate metrics like “how many payments?” (3!) “how many paid payments?” (lets say… for this example… 1!).
Ok, so out of three payments, one has been paid. And the second one is going to be processed today! We need an email that will tell my fundraising team that payment 2 of 3 has been paid. Here’s how I did that.
- First, I made a Roll Up field on the Donation to tell me how many total payments there are. I removed the “payments” that were just “credit card fees” which come over separately in our system.
- Next, I made a Roll Up field on the Donation to tell me how many Payments are marked as Paid. This should help me meet the “2 of 3” request!
- Next, I made a Formula Field on the Payment to copy the data from #1 above (remember, Donations and Payments are separate records in the system!)
- Next, I made a Formula Field on the Payment to copy the data from #2 above (this is necessary because I need to merge the data from the Payment into the email, not the Donation)
- Next, I made an email template with those merge fields (#3 and #4)
- Next, I turned on the automation and did some testing.
Feelin pretty good about these ideas so far, I was surprised that my test emails showed me the wrong payment installment.
My emails were off by one! When I marked the “first” payment as Paid, the email said that “Payment 0 of 3” had been paid, rather than “Payment 1 of 3.” At first, I thought this was an “Index from zero” problem (I learned in my college Computer Science class that some programming languages start counting at “zero” instead of “one”). But… I’ve never had that problem before, so why would I encounter it now?
Since I knew that Salesforce was evaluating the formula the right way, but coming up “minus 1” every time, I created another Formula field… “Payment Installment +1” so that I could get the number that I expected, and then I updated the email template merge field with the new field. This might not be the most elegant solution, but it worked right away! (The sauce has splattered a bit… time to wipe the counter)
Nerdette that I am, I wasn’t satisfied with this solution. Even though it technically WORKED (whew!), I still wanted to understand the root cause. I started thinking through all of the steps… marking the transaction as paid, calculating the Roll Up/Formula fields, sending the email, etc. And I remembered that Salesforce has a particular “order of execution” — perhaps the email was being sent before the Roll Up field finished calculating, which is why I needed to come up with a different way to arrive at the number I needed. It turns out… that is exactly right! In the image below, you’ll see that Step 10 Execute Process Builder (which is where the email automation “lived”) happens before Step 13 Evaluate Roll Up Summaries. I guess this is why it’s important to add some ingredients before others when following a recipe…
Reflection
While I was cooking a batch of Mushroom Barley soup on Sunday, I listed to the latest episode of On Being with Richard Blanco, and one line in the beginning of the episode particularly grabbed me. It went something like this: “You don’t know what you’re thinking until you begin writing.” It immediately rang true for me – which is one of my favorite things about blogging here!
Expanding on that theme of uncertainty, I *also* didn’t know how I was going to figure out this installment notification situation before I started experimenting.
I had never internalized the Order of Execution … just glanced at it when reviewing for certification exams.
Every data puzzle is a chance to unleash my creative energy and learn something new in the process!
If you’re faced with a spreadsheet quandary, and you feel overwhelmed… or your first idea doesn’t work out … or you don’t know where to start … I encourage you to keep experimenting and iterating! We’re in this together – and I’ll always help you out if you get stuck!