magnifying glasses and crystal balls

04/08/2023 #bbcon 1@BI_JCA ~ bi@jcainc.com

Magnifying Glassesand Crystal Balls

Use your data to raise more today and predict the future

Bob Dillane, Director, Enterprise Information Systems

Lebanon Valley College

Cari Maslow, Senior Director, Donor Relations and Membership

Carnegie Museums of Pittsburgh

Teri Morrow, Membership Director

Appalachian Mountain Club

Stephanie Reyes, Manager, Business Intelligence Group

Steve Beshuk, Director, Business Intelligence Group

YOUR PANEL

• What is Business Intelligence?

• Case Studies

- Lebanon Valley College: The Power of a Data Warehouse

- Appalachian Mountain Club: Tracking Members Source Using BI

- Carnegie Museums of Pittsburgh: The Power of Cubes

• Predictive Analytics

• Q&A

SESSION AGENDA AND GOALS

WHAT IS BUSINESS INTELLIGENCE?

Business intelligence is a set of theories, methodologies, processes, architectures, and

technologies that transform raw data into meaningful and useful information for

business purposes.

BI IS…

Business intelligence is a set of theories, methodologies, processes, architectures, and

technologies that transform raw data into meaningful and useful information for

business purposes.

BI IS…

NON-TECH BI ARCHITECTURE

Warehouse

Data InformationKnowledge

DATA WAREHOUSE

PK id_product

id_client

id_client group

id_shop

id_supplier

id_date

id_country

id_suppliername

id_supplieraddress

id_shop

ANALYTIC CUBES

VISUALIZATION

TYPES OF VISUALIZATION

TABLES CHARTS & GRAPHS HEAT MAPS

TIME SERIESTREE GAUGES

• The Challenge- The data landscape has changed – there’s a lot more of it now

- Constant struggle to grow revenue and keep costs in check

- Today’s donors are demand results

- Unfortunately, many nonprofits are behind the curve

• The Opportunity- Today’s BI gives you the tools to leverage your untapped “data asset”

- Sophisticated BI does not have to mean expensive

- BI can pay for itself – results!

BI AND NON-PROFITS

CASE STUDY: THE POWER OF A DATA WAREHOUSE

LEBANON VALLEY COLLEGELebanon Valley College in Annville, Pa.,

welcomes 1,600 full-time undergraduates

studying more than 30 majors, as well as self-

designed majors. Founded in 1866, LVC has

graduate programs in physical therapy,

business, music education, and science

education. Annville is 15 minutes east of

Hershey and 35 minutes east of Harrisburg;

Philadelphia, Washington, D.C., and

Baltimore are within two hours.

FAST FACTS

• Guaranteed degree completion in 4 years

• 3 out of 4 LVC students choose to live in

excellent, safe, guaranteed campus housing

• Low student-to-faculty ratio of 13 students

to each professor allows for personal

interactions and a customizable education

• High-achieving students, 1/2 of whom were

in the top 20% of their high school class,

continually teach each other while learning

together

• 98 % of students receive some form of

financial assistance with an average aid

package of more than $24,500

• A transaction database is designed for efficient data entry

• A data warehouse is designed for ease and speed of reporting

• These are two very different design goals

TRANSACTION DATABASE VS. DATA WAREHOUSE

• Technical Concerns- Unless you have a large database, technology is not a big concern

• Practical Concerns- Can I get answers with a data warehouse that I can’t (efficiently) get without

one? (the answer is virtually always “yes”)

- Will those answers improve our ability to perform our mission?

DO YOU NEED A DATA WAREHOUSE?

• SQL code to query event participant attributes directly from the Raiser’s Edge database:

COMPLEXITY OF THE RAISER’S EDGE

• Same query against the warehouse:

SIMPLICITY OF A DATA WAREHOUSE

• Reports we can’t do, or can’t do easily in RE

• Reports that need data from outside RE

• Perform calculations and import data back to RE

• Create custom gift analysis warehouse generated from JCA Answers warehouse

GENERAL USES FOR OUR WAREHOUSE

• This is on fund categories, not funds. Our goals are by category instead of individual funds.

FUND REPORTING

DONOR GIVING SUMMARY

• The Gift Analysis table has information about gifts, not donors.

• ConstitGifts table has links donors and gifts, with a flag for the type of linkage (hard credit, soft credit, match credit, etc.)

• Using these tables, we can quickly answer questions such as:- “How much did each donor give to the Mund Buidling Fund, the Annual Fund

and any endowment funds (3 separate totals) between July 17th and September 12th?”

• For frequently asked amounts (annual fund, etc.), we generate totals nightly and import them to RE as constituent attributes. This extends the capability of query and export in RE.

USING GIFT ANALYSIS WAREHOUSE

• We also use the warehouse to calculate the alumni/parent year code that is used with constituent names.

• We analyze:- What degrees and graduation years the individual has- What undergraduate degrees and graduation years the person’s children

have- We calculate an alumni/parent code and import it back to RE as an Add/Sal

OTHER WAREHOUSE USE IN SUPPORT OF RE DATA

• SQL Server Reporting Services is our formatted report writer• Entrinsik Informer as our primary add hoc query tool• Crystal Reports for some formatted reports, but we are phasing this

out• Excel with Microsoft Query to access the warehouse• JCA Answers as our primary data warehouse- Warehouse rebuild runs at 8:00 p.m.- Various calculations are run and exported from the warehouse- Calculations are imported into The Raisers Edge- Warehouse is rebuilt again at 7:00 a.m.

REPORTING TOOLS USED AT LEBANON VALLEY COLLEGE

CASE STUDY FROM APPALACHIAN MOUNTAIN CLUB: TRACKING NEW MEMBERS BY SOURCE USING BUSINESS INTELLIGENCE

• Helps people protect, enjoy, and understand the mountains, forests, waters, and trails from Maine to Washington, DC.• 86,000 members; 16,000 volunteers; 20,000 advocates• 12 chapters• Backcountry huts, camps & campsites, and front country lodges• Maintain over 1,800 miles of trail; 350+ miles of the AT• 8,000 volunteer- and staff-led activities

ABOUT APPALACHIAN MOUNTAIN CLUB

• Implemented in 2010• Reporting- Appeals- Member number - Member retention- New members- To Finance

BUSINESS INTELLIGENCE AT AMC

Leaving Madison Spring Hut Photo by Chris Lawrie, AMC New Hampshire Chapter

THE “OLDEN DAYS”

SourceNew

M’brshps RevenueAvg. Dues Payment

Total Cost to Acquire

Cost/ Member

Net/ Member

Collective Buying 351 $10,118 $28.83 $15,515 $44.20 ($15.38)

Direct Mail 7,742 $250,936 $32.41 $619,509 $80.02 ($47.61)

Email/Web 4,641 $242,555 $52.26 $21,693 $4.67 $47.59

Gift Memberships 659 $35,048 $53.18 $5,374 $8.15 $45.03

Miscellaneous 988 $31146 $31.52 $27,897 $28.24 $3.29

Reservations 852 $52,277 $61.36 $10,651 $12.50 $48.86

Telemarketing to Formers 1,374 $65,841 $47.92 $66,906 $48.69 ($0.78)

Total 16,607 $687,921 $41.42 $767,544 $46.22 ($4.79)

• Standard 2010 new member report by source

• What is new member retention by source?• What is retention rate, added giving, cost to renew?• Incorporated BI data into long-term reporting• Appeals coded with type and channel (AQDM, RNWB)• Type and channel split in BI—track at the channel level

THE UNKNOWNS

2011 RAW BI DATA

Source1st Year

Retention Pool1st Year

Retained%

Retained1st Year

Retained $Avg. Dues Payment

Collective Buying 277 74 27% $2,150 $29.05

Direct Mail 7,742 3,367 47% $113,566 $31.23

Email 174 49 28% $2,331 $47.57

Miscellaneous 646 236 37% $9,851 $41.74

Reservations 979 324 33% $19,166 $59.15

TM to Formers 1,900 602 32% $28,865 $47.95

Web 4,459 1,828 41% $87,939 $48.11

Total 16,177 6,750 42% $263,866 $39.09

ADDING CONTEXT WITH BI

Source2011 #

RetainedM’brshp +

Annual Fund $2012 #

RetainedM’brshp +

Annual Fund $Avg. Dues Payment

Collective Buying 74 $2,328 33 $1,542 $37.24

Direct Mail 3,367 $135,693 2,443 $124,894 $39.82

Email/Web 1,877 $106,114 1,120 $79,875 $53.49

Gift Memberships 100 $5,254 85 $5,291 $50.51

Miscellaneous 236 $13,684 141 $11,541 $46.27

Reservations 324 $22,766 207 $15,981 $62.14

TM to Formers 602 $41,651 269 $22,496 $53.93

Total 6,850 $327,489 4,298 $261,620 $45.74

• Re-calculated retention as of 2012 and re-calculated ROI.

FILLING IN THE DETAILS

SourceYear 3

RetentionTotal Cost

M’brshp Revenue

Total Ann. Fund

GivingNet/New Member

Year 3 M’brshp

% Ann Fund

Giving

Collective Buying 9% $17,825 $13,497 $491 ($10.93) $0.78 4%

Direct Mail 32% $657,151 $461,779 $49,744 ($18.81) $0.78 11%

Email/Web 24% $46,681 $392,731 $35,813 $82.28 $9.18 9%

Gift Memberships 13% $8,225 $43,670 $1,923 $56.70 $5.54 4%

Miscellaneous 14% $33,212 $47,521 $8,850 $23.44 $1.70 19%

Reservations 24% $15,039 $84,305 $6,718 $89.18 $6.05 8%

Telemarketing to Formers 20% $75,938 $109,212 $20,776 $39.34 $1.71 19%

Total 26% $854,071 $1,152,714 $124,315 $25.47 $1.50 11%

• 2013- Dropped Groupon/Living Social - Reduced cost of direct mail acquisition- Hired staff to boost non-direct mail/non-telemarketing efforts

• Growth in web/email sales slow• New members from guest reservations up nearly 300%• Memberships purchased by visitors at AMC huts, camps, and lodges

fewer than 100 in 2010 now up to nearly 900 in 2013.

• Tracking 2011 and 2012 new members• Guest stay data not available• No volunteer data in database

IN THE MEANTIME…

• Cut direct mail acquisition by 20% • Use Google Grant (awarded 8/2013) • Develop plan to boost retention and Annual Fund support from gift

memberships• Assess non-Membership department sales for 2014 growth potential.

PLAN FOR 2014

Crisp ColorsPhotograph by Nicholas Gagnon, AMC New Hampshire Chapter

CASE STUDY: THE POWER OF CUBES

•Founded in 1895•4 Distinct Museums

– Carnegie Museum of Art– Carnegie Museum of Natural History – Carnegie Science Center– The Andy Warhol Museum

•Serve 1.3 million people annually

CARNEGIE MUSEUMS OF PITTSBURGH

• Pressure to increase revenue with shrinking resources• Mountains of data in Raiser’s Edge but no easy way to get answers to

questions• Answers we had weren’t actionable and instead just led to more

questions• No easy way to measure strategy performance which led to a

culture of adding initiatives but not discontinuing any already implemented

THE NEED FOR BUSINESS INTELLIGENCE

• Projecting revenue was the impetus for taking what seemed to be a simple question further

• With an on-going, multi-hit renewal series, we needed to estimate not only what percentage would renew but when they would renew

• Knew the makeup of the population would impact the results but had no way of knowing which characteristics were most impactful

THE QUESTION OF RETENTION RATE

The retention rate is 63%

# of members x average gift x 63% =

Overall Renewal Revenue Projection

BUT:• Makeup of population impact both

the monthly and the year-to-year results

• Needed to predict when in the solicitation cycle renewals would happen

WHY DOES IT MATTER?

Multi vs. First Year Members• Use different renewal strategies• Run monthly query off an attribute set

for segmentation of the first direct mail hit

• Didn’t answer the “when” question • Confirmed theory that the makeup of

the monthly populations mattered

BEFORE JCA ANSWERS: THE EVOLVING RETENTION SPREADSHEET

TimingNext we started running multiple queries and plugging the counts into this spreadsheet data table

The retention rate at each time point then calculated into summary tables

• It worked but…• Labor Intensive• Risk of human error high• Not an easily transferable task• Technical: had to make friends in IT

• No clarity around the month to month variations- Large Variances- Not Consistent Year over Year

• Multi vs. First was not enough population segmentation

• Still left us without answers when asked “Why…”

• AND then we changed our upgrade solicitation method which further skewed the model

BUT…

• How many times do I have to get a member in the door?• Does renewal rate change if I have an active email address?• Does it matter whether they visit one, two or all three museum sites?• Are the increasing number of member events making a difference?• Retention gains in one year don’t appear to hold, why not?

Everything we did to try to impact retention was still just an educated stab in the dark.

magnifying glasses and crystal balls

expensive bi

bi isbusiness intelligence

opportunity todays bi

raw data

client id

business purposes

data landscape

data warehouse122014

Business

instructions for use contact glasses & magnifying...

suggested makerspace items he › wp-content › uploads ›...

microscopy and literature -...

magnifying aids, magnifiers, magnifying glasses, and low...

brian - bmjhooke r. micrographia, or somephysiological...

magnifying aids, magnifiers, magnifying glasses, … gv100 -...

resolution enhancement techniques in microscopy · 1682,...

trent associates report · hooke, robert. micrographia: or...

microscope studies in primary science: following the ... ·...

visiting vellamo - · pdf filevisiting vellamo maritime...

magnifying glass

describing images copyrighted material€¦ ·...

igneous rock textures - webs new folder... · igneous rock...

seeing through a lens. we see the world through lenses. ...

(1665) robert hooke micrographia, or some physiological...

magnifying your message

refraction and its uses done by: andrew ong. content uses of...

super stem sleuths: 2...magnifying glasses are single lenses...

the people who first investigated cells. zacharias janssen:...

univet loupes...every loupes user in the world can...