One of Bloomerang’s strengths is its user-friendly reporting system. Bloomerang’s reporting system is straightforward and intuitive. You are empowered to run reports and use data to make informed decisions about your fundraising, marketing, communications, and engagement activities. However, Bloomerang’s reporting system has its limitations.

Bloomerang’s reporting system can group, sort, and filter data, but the reporting system cannot generate formulas, visualizations, or other more advanced reporting and analytics functions. As a result, if you want to get the most from your Bloomerang system you may need to export data from Bloomerang reports to Excel and conduct your report modifications, manipulations, and analysis in Excel.

There are four primary features in Excel that can transform basic data exports from Bloomerang into in-depth management, dashboard, and benchmark reports.

1. Charts and Visualizations

Data visualizations, like charts and graphs, are the easiest way to make information relatable and relevant to a diverse audience. Charts and graphs are a great way to make data tangible. They give your staff an opportunity to connect with data even if data analysis isn’t their expertise. Charts and graphs also offer your organization dashboard style metrics and information that can be used in organizational scorecards.

Excel’s chart and visualization features are robust and flexible. You can produce nearly any type of visualization in Excel using raw data exported from Bloomerang. While the creation of charts and visualizations may take time, the opportunity for others to connect with data and make that data actionable can overcome the costs of creating visualizations in Excel.

Examples:

Charts and visualizations can be used with almost any data set exported from Bloomerang to Excel, but here are a few examples of where charts and visualizations might be impactful.

• Export all transactions from this year and produce a pie chart by percentage of revenue produced with different campaigns or approaches.
• Export all transactions from this year and produce a line chart showing total revenue by month.
• Export all new donors this year with the first transaction date for each account and produce a bar chart counting the number of new donors by month.

2. Pivot Tables

Bloomerang’s reporting system allows you to group data by specific fields and collapse groups of data into a single row. Grouping summarizes data by removing component data and showing only aggregated totals of a group. For example, grouping can summarize total giving by account for the past five years by only showing one row per account rather than showing all gifts made in those five years as separate rows. Pivot tables in Excel expand the grouping and summarizing features in Bloomerang’s reporting system.

Pivot tables are a data processing tool in Excel that summarize data and display that data in a matrix format. Pivot tables in Excel can be summarized by multiple fields at a time and can sum, count, and take averages among other formulas for data grouped in the table.

Examples:

Pivot tables can be produced with any data set from Bloomerang that is in a “component data” format. This means the data isn’t summarized or grouped. Pivot tables are created with raw, ungrouped data.

• Export all donations for the past five years and produce a report showing total giving by year by account.
• Export all donations for the past five years and produce a report showing total giving by year by fund.
• Export all transactions for the current fiscal or calendar year. Then group by month in the rows of your pivot table. Then group by revenue and raised in the columns of your pivot table. Then use a chart to visualize the twelve months of progress for revenue and raised.

3. Formulas

Many times a report calls for a formula or a set of formulas. Formulas produce metrics. These metrics are tangible pieces of information that can be used to take strategic action.

Bloomerang’s reporting system cannot calculate formulas. As a result, you may need Excel to produce formulas like an average, sum, or difference among a data set. You may even need to combine different features of Excel like pivot tables to produce these formulas. More complex formulas can be used in Excel as well like minimum, maximum, standard deviations, and if statements. The entire set of formulas in Excel can be used to manipulate the data exported from Bloomerang to match your reporting requirements.

Note: Click the “fx” (add formula) button next to the text bar in Excel to view a full list of formulas available in Excel.

Examples:

Formulas can be used to manipulate exported data from Bloomerang as well as transform it into actionable and usable information that is relevant to your strategic plan, goals, and benchmarks.

• Export all donations this year and take an average of the amount to find the average gift size this year.
• Export all constituent accounts with their “Latest Transaction Date” and run a formula to calculate the difference between today’s date and the latest transaction date. This metric will show you the days between today and a constituent’s last gift.
• Export all donations for the past two years and produce a report showing total giving by year by account (using a pivot table). Then create an if/then formula in Excel to find those donors that upgraded, downgraded, or remained the same in their giving levels year over year.

4. Filters

Bloomerang’s reporting engine is built on filters. You can filter on any field or combination of fields in the Bloomerang database to produce a report. As a result, you can generally filter data coming out of Bloomerang before ever needing to filter exported data in Excel.

However, if you are using pivot tables or are exporting large amounts of component data (i.e. many rows of data in a raw form), it may be necessary to filter this data within the context of your data analysis in Excel. For example, if you pulled all donations in the last five years and grouped by account in the rows and the year in the columns then you might also want to filter by “Appeal”or “Campaign” to show only totals each year by the specific strategy those donations were attributed to.

Filters can also be used if you are manipulating data in raw form or need to set up different views of data in Excel. Filtering can be used as a search function to narrow the set of data in Excel so it is manageable. You may need lots of data to run multiple analyses in Excel; so filtering can narrow your data set when working outside the Bloomerang user interface.

Examples:

Filters in Excel duplicate the filter function already available in Bloomerang’s reporting system, but here are some situations where you might filter data in Excel.

• Export all donations this year and produce a pivot table grouping total revenue by state. Then filter by state to show only a certain batch of states in a geographic region.
• Export all donations this year and filter by campaign and appeal.
• Export all constituents and filter by geographical indicators (i.e. city, state, postal code) or filter by custom fields like volunteer status or organization type.

Excel should be seen as a supplement to your Bloomerang reporting system. Excel can expand your data analysis to produce valuable information and metrics that are relevant to your strategic plan. Most Excel reporting projects using Bloomerang data exports will involve multiple Excel features. The beauty of Excel is that each feature can build on the other to increase the power of your reporting.

Excel is a tool that can expand how your organization uses data by making it visual, uncovering hidden insights, and producing metrics and measurements that are relevant to your goals and strategies.