Solved! How to send annual tax-deductible donation summary for donations

[Ericworships]

My team just switched from a nonprofit-specific CRM to HubSpot and ran into issues when figuring out how to pull an itemized list of tax-deductible donations (filtering out any gift made via a Donor-Advised Fund) from the previous year, organize them into a table, and sum the total in an email. For context, we have over 700 individual donors, so really building a scalable solution is our only option. We also use FundraiseUp ("FRU") for our online donations, but this solution is not dependent on that, since we have various other donation payment methods (checks, ACH, ....)

We've figured out a solution and wanted to share it with the community in case others are struggling to figure this out as well.

To start, we're going to need to create several custom variables:

• Deal Property: "DAF Donation" - Single Checkbox (Label: Yes, No). This will act as a boolean response, so the internal values are true or false. Alternatively, you could make this "Tax-Deductible" or something. We're just using it to filter out donations that don't require a tax receipt. I would recommend bulk editing all your deals and make sure every deal has this specific value if your nonprofit needs this variable.
• Deal Property: "Project" - Single-line text. This is mapped to FRU's "designation" property, and then I have an automation that says if "Project" is unknown, wait 1 minute, and then set Project to "Where It's Needed Most." This is our "General Donations" category.
• Deal Property: "Year" - Calculation (Unformatted number - custom equation): if(Is Closed Won, year(Close Date)). It's important to use the closed won property, so you don't add in any failed or pending donations.
• Deal Property: "Tax Receipts" - Calculation (Unformatted number - custom equation): if(((!DAF Donation) && (Year == 2023)), 1, if((DAF Donation && (Year == 2023)), 0)). **This is really important to make sure to edit the year value to the tax year you're wanting to report on. In this example, it's for the year 2023. This property will be used to filter down your total tax-deductible donations value later. It may seem redundant, but this is required if you have a donor give both through a DAF and personally in the same calendar year.
• Contact Property: “2023 Total Donations” - Calculation: Sum (Currency). Associated record type: Deal and Amount. Additional Condition: Year == 2023. **You’ll need to change this each year or just duplicate it for each year.
• Contact Property: “2023 Tax-Deductible Donations” - Calculation: Sum (Currency). Associated record type: Deal and Amount. Additional Condition: Tax Receipts == 1. **You’ll need to change this each year or just duplicate it for each year.

Now that you have all these custom properties, you can go to Marketing —> Files and Templates —> Design Tools —> New File —> Module —> Email. Here’s the code: 

{% set recId = contact.hs_object_id %}
{% set filtered_deals = crm_associations(recId, "HUBSPOT_DEFINED", 4, "limit=100&year=2023&orderBy=closedate&daf_donation=false&dealstage__in=closedwon,131844427", "closedate,project,amount").results %}

<div data-hs-responsive-table="true" style="overflow-x: auto; max-width: 100%; margin-left: auto; margin-right: auto;">
<table style="width: 100%; border-collapse: collapse; border: 1px solid #99acc2; min-width: 400px;">
<thead>
<tr style="background-color: #f4f4f4; color: #333;">
<th style="padding: 12px 15px; border-bottom: 1px solid #dddddd;">Date</th>
<th style="padding: 12px 15px; border-bottom: 1px solid #dddddd;">Project</th>
<th style="padding: 12px 15px; border-bottom: 1px solid #dddddd;">Amount</th>
</tr>
</thead>
<tbody>
{% for deal in filtered_deals %}
<tr style="background-color: {{ loop.index0|divisibleby(2) ? '#f9f9f9' : '#FFFFFF' }};">
<td style="padding: 12px 15px; border-bottom: 1px solid #dddddd;">{{ deal.closedate }}</td>
<td style="padding: 12px 15px; border-bottom: 1px solid #dddddd;">{{ deal.project|safe }}</td>
<td style="padding: 12px 15px; border-bottom: 1px solid #dddddd;">{{ deal.amount }}</td>
</tr>
{% endfor %}
<tr style="font-weight: bold; background-color: #f4f4f4;">
<td colspan="2" style="padding: 12px 15px; border-bottom: 1px solid #dddddd; text-align: right;">Total 2023 Tax-Deductible Donations:</td>
<td style="padding: 12px 15px; border-bottom: 1px solid #dddddd;">{{ contact.n2023_tax_deductible_donations }}</td>
</tr>
</tbody>
</table>
</div>

You'll need to edit the deal stages in the "crm_associations(" function to match your internal names for your "closed" stages. In hindsight, you can probably just set "closedwon" to true instead.

Once you publish changes and turn on "Use module for programmable email" on that same page, you can find it in your email template and drag it into your own email! You'll find it in the "More" section of the email editor.

The final step is to create your email list. The filters should be "2023 Tax-Deductible Donations" is known AND Email is known AND Marketing emails bounced is unknown. I also created a "mail" list for those who we don't have their emails. The filters are: "2023 Tax-Deductible Donations" is known AND Email is unknown AND Street address is known OR "2023 Tax-Deductible Donations" is known AND Marketing emails bounced is greater than 0 AND Street address is known. What's great about that last filter is it will be dynamic, so after you send the emailed list, if anyone bounces, you'll have a more complete list to then manually create printable receipts to mail them. For the mailed receipts, I just manually created a template in Word since we had only 6 without any emails.

That's it! Just sent this out yesterday, and it all worked!

I hope this helps! It was a hard-fought solution, so hopefully, this can assist other nonprofits. Please comment with any questions you have, and I'll try to get back.

[LChandler2]

Thank you for sharing!  Did you migrate from Raiser's Edge?  We are in the process of migrating over to HubSpot from there now.  

[Jnix284]

This is an awesome solution @Ericworships, thank you for sharing!