Written by Kestryl Lowrey and Stephen Earheart
When you migrate to Salesforce, you become deeply familiar with your organization’s data—the volume, the quality, and the insights it can provide. You carefully analyze and map your legacy data, populating your new system until it’s full of your organization’s history. After you’ve become deeply familiar with your data and dedicated the time, strategy, and planning that goes into a successful CRM implementation, you might be surprised to learn that ALL of your data doesn’t belong in Salesforce.
Your CRM is a key tool for engagement and can drive your fundraising strategy, moves management, campaign analytics, and operational reporting. But it also has key limits when it comes to parsing large volumes of data, analyzing trends over time, or supporting complex queries across multiple entities and engagement points. For these things, you might be better served by integrating a data warehouse into your technical systems landscape.
The Role of a Data Warehouse
A data warehouse is a structured database that supports reporting, segmenting, and analytics needs. It is typically populated with data from the CRM and enriched with other data sources from your organization. These sources may include data from marketing, event attendance, sometimes even legacy data for long term, strategic analytics. The data warehouse is structured to support queries and reporting for your core key performance indicators (KPIs) and analytics use cases. Data might be captured or stored in a data lake or cleaned via data quality tools, or structurally transformed before being pushed to a data warehouse. A well-managed data warehouse will serve your organization’s data analytics needs and feed your analytics tool of choice with consistent and logical queries across your valuable data assets.
In some cases, you may consider breaking down the use cases of your data warehouse for better performance, cloning read-only analytics, and segmenting data mart from the writeable warehouse. This approach can increase flexibility and prevent long-running queries or updates from locking up the data warehouse for other users.
Host Locally or in the Cloud
There are numerous tools and platforms for data warehousing depending on your nonprofit’s requirements and capacity to support the infrastructure. While some organizations opt to keep their data warehouse hosted locally, there are significant advantages to cloud-based data warehouse options such as Redshift or Snowflake. Implementing a cloud data warehouse provides flexibility as your organization’s data strategy shifts and scales without needing to manage on-site infrastructure.
Snowflake is a particularly good option for organizations getting started with data warehousing, offering platform support that simplifies hosting and maintenance while supporting advanced data management needs and highly scalable storage and compute resources. Automated scaling can be enabled, ensuring that your organization has the resources that you need when you need them.
Connecting Your Data Warehouse and CRM
In addition to the data warehouse to store, query, and analyze the data, you’ll also need to plan for how your warehouse will ingest data from your Salesforce CRM or other systems. This is where ETL (extract, transform, load) and middleware tools enter the landscape. Tools like Jitterbit or Informatica connect to Salesforce’s APIs to query data and load it to the data warehouse on a scheduled basis.
More detailed change tracking can be supported via Change Data Capture, allowing you to construct a history of crucial data points for more longitudinal analysis. When extracting data from your CRM to your data warehouse, think about the level of granularity you’ll need for reporting and make sure that your data integration approach supports that.
When a Nonprofit Should Use a Data Warehouse
Now that we’ve covered the fundamentals of what data warehouses are and how they fit into your broader technical architecture, let’s consider some common nonprofit use cases that a data warehouse can support.
- Donor Giving Summaries
Your organization has a base of committed donors who have been supporting your organization for years, decades in some cases. All of that giving history is valuable but tracking transactional records from 15 years ago can clutter your CRM. Keeping older giving transactions can consume extra data storage capacity and impact your Salesforce report performance. What you really need to know might be a segment of that data, like someone’s lifetime and annual giving totals. You’re able to use that pertinent data for segmenting and donor strategy. Consider archiving gifts older than five years to your data warehouse and generating summaries of prior giving via the warehouse. The transactional data will still be available if you need more granular analytics, without needing to keep every bit of history in Salesforce. This also can support the computation of more advanced or selective summaries that are easily supported in the NPSP Customizable Rollups tool.
- Campaign Segmentation
Direct mail is a key part of your fundraising strategy. You plan specific segments based on prior donor behavior and other traits to optimize the success of your campaign, analyzing a broad range of data points. Salesforce CRM’s reporting capabilities have limits in creating the joins and filters you need to build your segments, and encounter performance limitations when parsing large data volumes. By replicating your CRM data to your data warehouse, you can execute your advanced segmentation queries, track segment assignments, and correlate donor segments with gifts when received to analyze campaign performance. Leveraging a data warehouse for campaign segmentation allows you to move high-volume campaign membership records out of Salesforce, conserving data storage while supporting better donor segmentation.
- Cross Channel Analytics
Your data warehouse isn’t limited to storing data from your Salesforce CRM. It could also ingest data from your email marketing tool or web and social analytics. These engagement channels generate a lot of data—granular data that is difficult to leverage in your CRM but can be vital to understanding your constituents. Bringing engagement data into your data warehouse and joining it with CRM data can allow you to get a broader view of your supporters and how they interact with your organization—as well as a deeper understanding of which interactions are most likely to move supporters up your engagement ladder.
These are just a few places to get started with incorporating a data warehouse into your nonprofit’s overall technical landscape and data strategy. Data warehousing can allow you to better leverage the data that you are collecting to provide better experiences for your donors and drive more effective fundraising. This supports a lean, performant CRM that helps your staff move your organization forward, without stashing your legacy data in an inaccessible local archive. The warehouse can become a record of your historical data, as well as a tool for analyzing current outcomes and predicting future trends.
