Bloomerang + Zapier: Everything you can do with Google Sheets [Webinar]

Your Bloomerang database is the central donor management and fundraising platform for your organization, but Bloomerang likely isn’t the only app that you use day to day. We believe Bloomerang is your central system and that integrations supplement your Bloomerang database. Data entry, reporting, and automation should start from a lens that Bloomerang is core to your donor management and fundraising technology.

Bloomerang is now connected to Zapier and Zapier connects to 2,000+ other apps, including Google Sheets. The combination of Bloomerang and Zapier offers an all-in-one integration platform for automating your day to day workflows.

The goal of this webinar is to show you how Bloomerang integrations and workflow automation with Google Sheets can streamline processes, save time, and reduce effort.

During this webinar, you will learn:

  • How Bloomerang + Zapier work together to make integration with any app possible and why Zapier integration is a key feature of your Bloomerang system
  • Explore and demonstrate how you can use the Bloomerang Zapier integration to create Google Sheets automation for…
    • Real time lists and directories
    • Dashboards
    • Constituent mass updates

 

 
Webinar transcript (not including Q&A):

Good afternoon, and welcome to Bloomerang Academy! Thank you for joining us. My name is Diana Otero, and I am the Product Engagement Manager at Bloomerang. You might recognize me from attending Bloomerang Academy classes or listening to our release and help videos.

Today, we’re talking about “Bloomerang + Zapier: Everything you can do with Google Sheets.”

[SLIDE]

I’d like to introduce our presenter today, Jeff Haguewood. Jeff is a Zapier Certified Expert and Bloomerang Integrations Consultant at Sidekick Solutions. Sidekick Solutions is a Bloomerang Partner, specializing in system automations for Bloomerang. Jeff has been a Bloomerang user since 2013 and most recently was part of the Bloomerang team that launched the Bloomerang Zapier app, which is a key component of today’s webinar. We’re excited to have Jeff here today to share his expertise and explore how you can use the Bloomerang Zapier app with Google Sheets.

Thank you Diana. Hello everyone! It’s great to be here.

[SLIDE]

The goal of today’s webinar is to show how Bloomerang integrations and workflow automation with Google Sheets can streamline processes, save time, and reduce effort.

Our agenda today will…

  • Introduce Bloomerang + Zapier, how it works and why Zapier integration is a key feature of your Bloomerang system
  • Explore and demonstrate how you can use the Bloomerang Zapier integration to create Google Sheets automation for…
  • Real time lists and directories
  • Dashboards
  • Constituent mass updates

We’ve got three neat use cases that demonstrate Bloomerang automation with a spreadsheet!

As Diana mentioned, feel free to submit any questions during the presentation. We’ll take a couple of breaks to answer questions and then open up for more questions at the end.

Alright, let’s dive in.

[SLIDE]

First, a quick poll to see how many are using Zapier and using it with Bloomerang.

[POLL]

Are you currently using Zapier? Are you using Zapier with Bloomerang?

[SLIDE]

Thank you for that feedback. Since we have a few that are new to Zapier, I’ll give a high level overview of “what is Zapier” and why we believe it is a key feature of your Bloomerang system. For more information, I recommend watching our previous webinar on Bloomerang + Zapier. Diana and I will make sure you have a link to that following today’s webinar.

You might be wondering why we are talking about Zapier, when this presentation is about Bloomerang and Google Sheets. That’s a fair question and offers an exciting answer.

Zapier is the software that enables Bloomerang and Google Sheets integration. It’s the middleware that sits in between your Bloomerang database and Google Sheets, helping them “talk” to each other. Zapier is automation software. Zapier is an integration designer that builds integrations with blocks called “triggers” and “actions.” With Zapier, you can build one integration or many. With Zapier, you can automate hundreds of tasks around your Bloomerang system within a single platform.

So, why get excited about Bloomerang Zapier integration?

[SLIDE]

Three reasons…

  1. First and most important, Zapier connects to 2,000+ other apps. Any apps in Zapier’s directory can be connected to Bloomerang. If it’s on Zapier’s list it can be integrated with Bloomerang. That includes Google Sheets!
  2. Second, Zapier was designed for anyone to build integrations. You don’t need to know how to code to build Zaps. Anyone can build and maintain a Zap. We’re going to walk through some builds today so you can get a feel for what that looks like.
  3. Third, Zapier enables custom integrations. You aren’t limited to one-size fits all workflows, mapping, or formatting. You can build the integration that is perfect for your organization and your workflows. Flexibility is especially important for spreadsheet integrations because spreadsheets are flexible by nature and we want to leverage that flexibility via integration with Bloomerang.

[SLIDE]

With the Bloomerang Zapier integration, you can build integrations with data flowing out of Bloomerang to Google Sheets and also build integrations with data flowing into Bloomerang from Google Sheets.

Let’s look at use cases for these two data flows.

[SLIDE]

Our first use case covers real-time directories and lists. This is a great place to start because it is foundational to spreadsheet integration with Bloomerang. The other use cases in this webinar start with some form of list building in a spreadsheet.

Zapier-enabled Bloomerang integrations with Google Sheets run in real time. A Zap is triggered when data is entered in Bloomerang, pushing the data through to your Google Sheet.

[SLIDE]

You may be asking why an organization would want to automate the generation of lists and directories in Google Sheets when you can run the same data in a Bloomerang report.

You might consider this type of automation if…

  • Non-Bloomerang users need access to data without a login
  • You need up-to-date lists in “real-time”
  • It is time efficient to have a real-time list instead of running a report
  • The list is a data set used for a dashboard or mass update (more on those two use cases later)

The possibilities for generating real-time lists in Google Sheets are extensive. Let’s look at two examples.

[SLIDE]

The first example generates a list of New Recurring Donation Schedules when they are created in Bloomerang.

For each new transaction in Bloomerang that is a recurring donation schedule, the constituent’s name and schedule details are written to a Google Sheet.

[DEMO / 4 SLIDES]

We set up the Google Sheet first. Then we proceed to build our integration.

We’ve added column headers that represent the data we want to write to the spreadsheet.

Then we jump into Zapier.

  • Select Make a Zap.
  • The workflow starts when a recurring donation schedule is created in Bloomerang, so that’s our trigger. Zaps start with a trigger, an event that happens in one app. That prompts the integration to run. Bloomerang’s New Transaction trigger is the best option of the available triggers in the Bloomerang Zapier app.
  • We only want to trigger on Recurring Donation Schedules, so customize the trigger by narrowing the criteria for Recurring Donation Schedules only.
  • For this workflow, we do not need to include the constituent’s cumulative giving history or household information so we can leave both of those fields blank.
  • Then, let’s test our trigger to generate sample data. This will return recurring donation schedules you’ve added recently. These are samples only and will not run through the integration because they were created before the integration is turned on. As a reminder, Zap triggers run in real time.
  • Based on the way I’ve formatted my Google Sheet, I want the Start Date of the Recurring Donation Schedule. Let’s look at the sample data to see what that looks like from Bloomerang. We notice the next installment date is a timestamp format and I would like that to be in MM/DD/YYYY format.
  • To get that format, I’ll add a Zapier formatter step to convert the date and time value to a date-only value of Month/Day/Year. Zapier formatter steps come in handy when you need to transform data in your Zaps. Formatter steps are a low code, user-friendly way to modify data as it flows through your Zap.
  • From there, we will create a new row in the Google Sheet for the constituent and recurring donation schedule details. Each new recurring donation schedule will add to the bottom of the Google Sheet as a new row.
  • Map the fields from Bloomerang and the Formatter step to the appropriate column in the Google Sheet.
    Select that you’re done editing and turn on the Zap.

I have a Zap built and ready to demonstrate, so let’s do that next.

[LIVE DEMO]

[SLIDE]

Let’s explore another example. This time we’re going to build a directory of constituents who have given enough in their lifetime to qualify for your organization’s Giving Circle.

For each new transaction in Bloomerang, the workflow will add eligible constituents to your Giving Circle directory. Eligibility is based on their lifetime revenue. Additionally, we will update a constituent’s lifetime giving total if the constituent is already in the Giving Circle.

[DEMO / 5 SLIDES]

Like our prior example, we need to start by setting up our Google Sheet and adding headers that match the data we want to populate in the sheet.

In this example, we’ll bring in some additional information like the date they were added to the Giving Circle and their lifetime giving amount, along with their Household information in case we want to contact this constituent directly.

  • Select Make a Zap.
  • Similar to our last example, this workflow starts when a new transaction is created in Bloomerang, so that’s our trigger.
  • We’ll also filter our New Transaction trigger to run when the transaction type is a donation, recurring donation, or pledge payment (not a pledge) because those are the transactions that contribute to Lifetime Revenue.
  • We also need to set our minimum transaction amount to $0.01 to exclude any in-kind donations.
  • Now, this is a bit different than our last example. We need to set the “Include Constituent’s Cumulative Giving History?” option to “True” to include the constituent’s cumulative giving history. This pulls in additional data fields that are needed to identify whether the constituent’s Lifetime Revenue meets the requirements for our Giving Circle.
  • Optionally, we can set the “Include Constituent’s Household Information?” option to “True” if we want to write the constituent’s household information to the Google Sheet.
  • Run the trigger to get a sample and check to make sure the sample you’re using has a Constituent Giving Summary and Household included, you’ll need that for mapping in the next steps of the Zap.
  • From there, add a filter step to only continue the Zap if the constituent’s Lifetime Revenue is greater than or equal to the giving required to join the Giving Circle. For this example, only constituents who have lifetime revenue of $10,000 or more will be added to our Google Sheet. The Zapier filter step only includes “greater than” or “less than” logic, so we’ll need to set our operator as “greater than $9,999.99”.
  • If the constituent’s lifetime revenue meets this criteria, the next step in the workflow is to first find an existing constituent on the Google Sheet or create one if none are found. This step adds new eligible constituents when they first meet the criteria. We will use the Constituent Account Number to match a duplicate row in the Giving Circle.
  • To ensure your directory remains up-to-date and accurate, add a second filter to continue if the constituent was found in your Giving Circle directory. The Find/Create Row action in Google Sheets returns a field of “Zap Data Was Found” with a true or false value. We’ll need to set the logic for the filter to only continue if Zap Data Was Found contains “true”.
  • If the constituent was found, we’re going to update the lifetime revenue and constituent and household profile details to ensure the data in our directory remains current. We’ll use the row ID for the found row in the prior Find/Create Row step to ensure the correct row is updated. This step overwrites what is in the row now to reflect the most up to date information.

Let’s take a look at this integration in real-time.

[LIVE DEMO]

[SLIDE]

Let’s take a quick poll to see which scenarios you find most valuable for directories and lists.

[POLL]

What scenarios do you find most valuable for lists and directories?

[SLIDE]

Before we move onto the next use case for Google Sheets, let’s take one or two questions.

[SLIDE]

Integration with a spreadsheet opens a world of possibilities for dashboards, custom formulas, grouping, sorting, and visualizations.

[SLIDE]

Your organization may want to consider building dashboards in Google Sheets if you…

  • Want to use visualizations to communicate key metrics
  • Want to transform and analyze data using spreadsheet-style formulas
  • Need to make data accessible to non-Bloomerang users
  • Need dynamic metrics to show progress toward goals in real-time

[SLIDE]

To demonstrate how you can build dynamic dashboards in Google Sheets, we are going to explore a Fund Activity Dashboard.

We are going to build a Fund Activity Dashboard similar to the Campaign Progress widget on your Bloomerang dashboard. The dashboard will show the total raised for each Fund and the progress toward a goal. Additionally, the Fund Activity Dashboard can go one step further and include the number of donations and progress toward a goal for this metric. A similar dynamic dashboard can be developed to track goal progress for Appeals as well.

[SLIDE]

To create a dynamic Fund Activity Dashboard, we will need to add transaction details for raised transactions to a Google Sheet as a raw data set. The analysis and progress tracking will occur in a separate tab in Google Sheets.

[DEMO / 7 SLIDES]

Like our prior examples, we start by setting up our Google Sheet, adding headers that match the data we want to populate in the sheet. This example actually uses two sheets: a Transaction List and a Dashboard. We are going to set up the Transaction List first and the Dashboard later.

We’ll bring in the donor name, transaction date, amount, and the fund, campaign, and appeal into our Transaction List. While the Dashboard sheet will update based on the fund and amount for the transaction, this additional data can be helpful if you want to build similar Appeal or Campaign dashboards in the same Google Sheet, or configure your Dashboard for a specific time period.

Now, we need to go to Zapier and set up a new Zap to populate our Transaction List.

  • Select Make a Zap.
  • The workflow starts when a new transaction is created in Bloomerang, so that’s our trigger.
  • We only want raised transactions in our Dashboard, so we will customize our New Transaction trigger to only include donations, recurring donations, and pledges (not pledge payments or recurring donation schedules).
  • We’re also going to set our minimum transaction amount to $0.01 to exclude any in-kind donations.
  • For this workflow, we do not need to include the constituent’s cumulative giving history or household information so we can leave both of these fields blank.
  • Then, we’ll test the trigger to generate sample data.
  • From there, we’ll add a Google Sheets action step to create a new row in the Transactions List worksheet for each new transaction that meets our criteria. If you use split payments in Bloomerang, pay attention to the Google Sheets action you select. You will want line item support enabled.
  • We need to select the appropriate fields from the Bloomerang trigger to map to the appropriate column in the Google Sheet.
  • Select that you’re done editing and turn on the Zap.

Now let’s build the Dashboard.

  • Create a second sheet in the same Google Sheet to build the Dashboard.
  • Create a table with each Fund’s name and add columns for total raised, net to go, the goal, and, optionally, a column to calculate the progress toward the goal.
  • Then, create a second table with the same columns except instead of total raised, we’ll total the number of donations.
  • To get the total raised amount to populate dynamically from your transaction list, use a SUMIF formula with the data from the Transaction List.
    • Use Fund column as the range;
    • Type the specific Fund name in quotations as the criterion (ex: “Unrestricted”); and
    • Use the Amount column as the sum range.
  • Net to go is a simple subtraction equation where the total raised is subtracted from the goal amount.
  • The percent toward the goal is a simple division equation where the total raised for the fund is divided by the goal amount.
  • To get the number of donations to populate dynamically from your transaction list, use a COUNTIF formula with the data from the Transaction List.
    • Use Fund column as the range and
    • Type the specific Fund name in quotations as the criterion (ex: “Unrestricted”).
  • The net to go and percent toward the goal are the same subtraction and division equations we used in the raised total table.

Now we need to make the bar charts to show our progress.

  • Go to the menu and select Insert, and then Chart.
  • In the Setup pane, select a Chart Type of Stacked Bar Chart.
  • Then, under Series, add a Series for Raised by selecting the Raised column and clicking “ok”. Repeat with Net to Go.
  • We’ll need to repeat these steps to create the second chart showing progress based on the Number of Donations.

I have a Zap built and ready to demonstrate this scenario, so let’s do that next.

[LIVE DEMO]

[SLIDE]

Before we move onto the next use case for Google Sheets, let’s take one or two questions about dashboards.

[SLIDE]

Data quality clean-up and updating custom fields on constituents are a pain point for many organizations. The amount of time spent manually correcting or updating data in Bloomerang can be substantial. Performing a constituent mass update using Google Sheets can help streamline this process and allow for easier updating across many constituents.

[SLIDE]

Your organization may want to consider a constituent mass update using Google Sheets if you need to…

  • Save time on manual data clean-up for data entry inconsistencies
  • Assign data in new constituent custom fields for existing accounts in Bloomerang
  • Batch update custom field coding for a segment of your constituents

[SLIDE]

In this use case, we are going to build our list for constituents with a transaction of $1,000 or more and no value in our Solicitor or Prospect Status fields. This will allow us to assign values in these constituent custom fields and push the assignments back to Bloomerang to update the constituent.

[DEMO / 7 SLIDES]

Like our other use cases, we’ll start by setting up our Google Sheet, adding headers that match the data we want to write to the sheet. However, there is one difference than the other Google Sheet structures.

We need to add a column either on the far left (column A) or far right (last column) to serve as our “trigger” column. After making updates to a row, we’ll add an “X” to this column to indicate the constituent record and updates should be pushed to Bloomerang. We’ll leave this column blank for now.

This use case is also different from the others we’ve showcased today as it needs two Zaps in Zapier. Let’s jump into Zapier and set up the first Zap. The first Zap we need to build is going to populate our constituent list in Google Sheets for assigning Solicitor and Prospect Status values.

  • Select Make a Zap.
  • The workflow starts when a new transaction is created in Bloomerang. We will use the New Transaction trigger to start our workflow and set the minimum amount to $1,000 as we only want to write constituents to the Google Sheet if they’ve had a donation of $1,000 or more.
  • We’re also going to select to include the constituent’s cumulative giving history because we’re going to map the Lifetime Revenue amount to the Google Sheet for additional context when assigning a Solicitor and Prospect Status.
  • We don’t need to include the household information for this workflow so we’ll leave that blank.
  • Let’s test the trigger to generate sample data.
  • From there, we add a filter step to only continue if the constituent has a blank Solicitor or Prospect Status field. I’m going to select Solicitor from the Bloomerang trigger in the dropdown on the left and set the logic operator to “Does not exist”.
  • Then, I’m going to click “OR” to add my second criteria. We’ll select Prospect Status from the Bloomerang step in the dropdown on the left and set the logic operator to “Does not exist”. These two criteria will filter out any constituents who have both a Solicitor and Prospect Status, but continue in the Zap if one or both of these fields are blank.
  • The next step in the workflow is to find the constituent on the Google Sheet using the Bloomerang Constituent ID as our lookup key. We want to try and find the constituent first as we only want a constituent on this list for updating once. If a row for the constituent isn’t found, we’re going to configure this step to create a new row and map Bloomerang data to the appropriate Google Sheet columns.

Now we need to set up our second zap for this workflow. This second workflow is the push of an update to Bloomerang when the trigger column in our Google Sheet is marked with an “X”.

  • Select Make a Zap.
  • This workflow will trigger when a Google Sheet row is updated. We are going to customize this step to only trigger if the “trigger” column we added to our Google Sheet changes. Changes to any other columns in our Google Sheet won’t cause the Zap to trigger.
  • Let’s test the trigger to generate sample data.
  • From there, we will add a step to update the constituent in Bloomerang, using the Constituent ID as our duplicate match key. The integration will update the constituent’s profile with the data in the Google Sheet row for that constituent.
  • Map the Solicitor and Prospect Status data from the Google Sheet to the constituent custom fields in Bloomerang.
  • Select that you’re done editing and turn on the Zap.

I have a Zap built and ready to demonstrate, so let’s do that next.

[LIVE DEMO]

[SLIDE]

To wrap up today’s presentation, I’ll leave you with a handful of other ways you might use Google Sheets with Bloomerang.

  • Membership directory
  • Monthly donor roster
  • Year-over-year revenue dashboard
  • Major gift opportunity dashboard
  • Transaction mass update
  • Create tasks for follow-up actions

[SLIDE]

  • Data quality clearinghouses
  • Lookup tables for custom coding
  • Deduplication checks
  • Transforming line items into single entries

The possibilities for Bloomerang automations using Google Sheets are endless.

[SLIDE]

With the flexibility of Google Sheets, it could be interpreted that we are advocating for using spreadsheets to replace data entry and direct navigation within Bloomerang.

We believe Bloomerang is your central system and that integrations supplement your Bloomerang database. Data entry, reporting, and automation should start from a lens that Bloomerang is core to your donor management and fundraising technology.

Google Sheets supplements Bloomerang and extends Bloomerang, it doesn’t replace it.

[SLIDE]

Next step is getting started.

  1. Sign up for a Zapier account, it’s free with a trial and free up to certain limits, nonprofits get a discount
  2. Connect your Bloomerang and Google Sheets accounts to Zapier
  3. Grab a Bloomerang template and customize it to match your workflow and mapping requirements
  4. Turn on your Zap and monitor it, watch as tasks queue in your task history, resolve errors and high-five for successes

[SLIDE]

Thank you for attending today.

Again, my name is Jeff Haguewood. My email is here and my door is always open.

[SLIDE]

If you’d like to work with a consultant to set up Zaps or develop a custom Bloomerang integration to address a unique use case, please don’t hesitate to reach out.

I will hand it off to Diana for a quick poll before we open up for more questions.

Thank you so much, Jeff, and thank you to everyone for joining us today. We, at Bloomerang, are very excited for this Zapier integration and we’re very excited to see where all of you will take it. Zapier has an integration with over 2,000 different apps and we’ve only just scratched the surface of what you can do with Google Sheets. We hope that you can find a way to automate your processes and work better with Bloomerang using Zapier.

[POLL]

Would you like a follow-up about consulting services to set up integrations with Bloomerang and Google Sheets?

[SLIDE]

Free Download

Bloomerang
Automation
Playbook

  • 5 automation use cases that save time
  • Integration ideas for top apps like Eventbrite, Mailchimp, and Gmail
  • New ways to think about your Bloomerang experience

Join the Bloomerang users that already got their playbook!

DOWNLOAD NOW