The ability to customize Blackbaud® eTapestry® reports can only be taken so far.  The eTapestry reporting platform has some limitations.  It can group, sort, and filter data, but it cannot generate formulas, visualizations, or other more advanced reporting functions.

Sometimes eTapestry data and eTapestry reports need more polishing before real insight can be uncovered from the data.  As a result, if you want to “get more” from eTapestry reports, you may need to export to Excel and conduct your analysis in Excel.

There are four features in Excel that can transform basic eTapestry reports into in-depth management, dashboard, and benchmark reports.

1. Charts and Visualizations

Data visualizations, like charts and graphs, are the easiest way to convey complex information to a diverse audience. Charts and graphs are a great way to make data tangible. They give staff the opportunity to connect with data even if data analysis isn’t their expertise. In addition, Excel charts and graphs can be used to create performance scorecards and dashboards that aren’t available in eTapestry.

Excel’s chart features are robust and flexible. You can produce nearly any type of visualization in Excel using data exported from eTapestry.

Examples:

Charts and visualizations can be used with almost any data set, but here are a few examples.

• Export all transactions received from this year and produce a pie chart by percentage of received dollars generated from different campaigns or approaches.
• Export all transactions recieved from this year and produce a line chart showing total received dollars 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

You can group eTapestry reports by specific fields and collapse those groups into summaries. Grouping is a summary of raw data. For example, grouping can summarize total giving by account for the past five years without showing all gifts made in those five years. Pivot tables in Excel expand the grouping features in eTapestry’s reporting system.

Pivot tables 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 used with eTapestry data exported in a “component data” format. This means the data isn’t summarized or grouped in the eTapestry report. Pivot tables are created with raw, ungrouped data.

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

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.

Sadly formulas aren’t available in custom eTapestry reports. As a result, you may need Excel to produce formulas like an average, sum, or difference within a data set.  More complex formulas can be used in Excel as well like minimum, maximum, standard deviations, and if/then statements. Excel’s full formula toolbox is at your disposal with an eTapestry report exported to Excel.

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 data and transform it into actionable and useable information that is relevant to your strategic plan, goals, and benchmarks.

• Export all transactions received this year and take an average of the amount to find average gift size this year.
• Export all constituent accounts with their “Last Transaction Date” and run a formula to calculate the difference between today’s date and the last transaction date. This metric will show you the days between today and a constituent’s last gift.
• Export all transactions received 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

Queries are filters.  With standard, compound, and custom account eTapestry queries, you can filter on any field or combination of fields in eTapestry.  An eTapestry query selects the data you want to see in your report.  A query is a search. As a result, you can generally filter data coming out of an eTapestry report before 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 data within the context of your data analysis in Excel. For example, if you pulled all gifts 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 “Approach” or “Campaign” to show only totals each year by the specific strategy those gifts were attributed to.

Filters can also be used if you are manipulating data in raw form or need to setup 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 external to eTapestry.

Examples:

Filters in Excel duplicate the query tool in eTapestry, but here are some situations where you might filter data in Excel.

• Export all transactions received this year and produce a pivot table grouping total received dollars by state. Then filter by state to show only a certain batch of states in a geographic region.
• Export all transactions received 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.
• Export a mailing list and filter by mailing status (i.e. exclude people who shouldn’t be mailed).  This list could then be sent to your printer.

Using Excel and eTapestry Together

Excel should be seen as a supplement to eTapestry reports.  Most Excel reporting projects using eTapestry data 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.