Connecting CiviCRM to Trackers and Data Tools (guest post by Harlem Lacrosse)

[We’re delighted to present this guest post by Jill Rodde, Chief Operating Officer at Harlem Lacrosse. Jill came up with a great solution for getting her team more engaged with their CiviCRM database, and we suggested sharing it here for other organizations to consider adopting. Enjoy!]

While Civi is our system of record for all constituent data, we use a bunch of other trackers and data tools to help us run our programs, fundraising, and operations on a daily basis. These external tools help us to be nimble and responsive, but can also create discontinuities with Civi when the data in these systems are getting updated every day while Civi is…maybe not so much. This year, we have focused on getting these systems to “talk to each other” a bit better to bring them into better sync, so I wanted to share one process we’ve used that you may find helpful if you’re running into these same challenges.

Our goal: Make Civi feel a little less like a black box of data that’s totally separate from the day-to-day information we use to manage our work. By bringing Civi data into the tools that we use every day, staff can easily see what is out of date or out of sync, and thus have constant reminders to make the updates in the system of record, CiviCRM.

What we made: We now have a report of all active student participants in our program that updates once per day from Civi, and is stored on our Google Drive. It forms the backbone of many other data tools, including each Program Director’s participation tracker, each regional leader’s management clipboard, and our organizational program roll up. So, every day, all of our trackers and documents are re-populated with the most up-to-date information from Civi — and if it doesn’t sync with what they have on file, they are prompted to log into Civi to correct the mismatch.

How we did it:

Schedule a regular report export
We started by creating a report of all of our active student participants in Civi — the backbone data that we wanted to use in all of our external data tools. Using the Scheduled Jobs function, I set up this report to be automatically emailed to me every morning. (If that’s not already in your skillset, your BackOffice Thinking support partners can assist you with that.)

For my own ease, I also set up a filter in Gmail to place this email in a specific folder each day, bypassing my inbox.

• Set up a Zap to automate replacing the file on our Google Drive each day

Zapier is an application that connects apps to each other and automates workflows. We use Zapier for lots of different processes within Harlem Lacrosse, and it’s been a huge time saver!

Each “Zap” is basically a workflow that has a trigger and an action. In this process, the trigger is my receiving the daily report email, and the action is that Zapier finds the old report on our Drive and replaces it with the updated version. Voila, at 3AM every morning, the report on our Drive is updated while I’m still asleep!

(One neat thing about Zapier is that you can share the “recipes” for Zaps you create; you can see my Zap for this process here.)

Create the connections

Now that we have all the data we need in one place, I use the QUERY and IMPORTRANGE functions in Google Sheets to import the relevant data from that central spreadsheet out to all of the other tools. This only needs to be set up once in each tool; then, every day, when the central spreadsheet updates, all of the other sheets update too.

An example in practice: the central spreadsheet includes all of the current active participants in every program. Each Program Director has a tab in their participation tracker that uses QUERY and IMPORTRANGE to bring in only the rows for students who participate in that particular Director’s program, giving them a ready-made roster of all of their registered participants that automatically updates each day.

Just a little bonus: We then use QUERY and IMPORTRANGE to import data from each participation tracker back into an organization-wide roll up, so that we can aggregate and analyze our program metrics across the country without needing program leaders to submit information to a separate spot. We’ve just got all of our data systems talking to each other, letting our program staff spend more time with our students and less time with spreadsheets and databases.

Closing Out
We’ve only been using this system since August, but by bringing Civi data a little bit closer to where our staff are working every day, it’s much easier to keep both Civi and our external trackers up to date. This process has also opened up all kinds of doors for analysis, now that we can aggregate the data all in one place. We have created tools that allow development leaders to quickly pull down data points for solicitations, tools that allow program leaders to track key participant engagement metrics over time, and tools that help our executive leadership team track the overall health of our programming–all without needing to constantly run and update reports.

If you are interested in learning whether this process will work for you or if you need assistance implementing something similar, please reach out to us via your support email or contact us. We’re here to provide further assistance.