Helen Brown Group

  • Home
  • Our Services
    Overview
    • A la carte research
    • Customized News Alerts
    • Data Insight
    • Dedicated Consulting
    • Research department audits and training
    • Wealth Screening Assistance
  • Resource Library
    • Learning Media Library
    • Wealth Lists
    • Prospect Research Links
    • Prospect Research for Fundraisers
  • About
    • What We Believe
    • Helen Brown
    • Meet the Team
    • HBG Privacy Policy
  • Blog
    • Archive
  • Contact

July 13, 2017 By Helen Brown

Pivot Tables – step by step

At the most recent NEDRA conference, Meghan Hakanson and Amy Begg of Harvard University delivered a session to a packed room on how they created pivot tables to sort through mountains of data to identify reunion prospects. What had session participants clamoring for more was the nitty-gritty details of how Meghan and Amy actually did it. So I asked them to share their project with us – step by step – so that we can all try it, too!
Huge thanks to both of them for their generous willingness to share, and to Meghan especially for writing this article.

Have you ever found yourself sorting through mountains of prospects and thought, “There must be a better way to do this!” and at the same time, “but I don’t have a lot of time!”? We found ourselves saying and thinking the same things to each other as we embarked on a new project for our Reunion and Annual Campaigns team here at Harvard.

This group of roughly 25 fundraisers shepherd all of the College’s reunions each year. They are in charge of spearheading events, coordinating volunteers, soliciting donors in each and every class, and engaging with each member of each class. This monumental task challenges each fundraiser because they are assigned to 1,500 to 1,700 people per class. Needless to say, it was difficult for fundraisers to sort through who had wealth.

We decided to help them sift through the haystack and find the shiny needles. How? Two ways:

  1. We sorted through each class to find wealth points such as: wealthy zip code, six year giving, largest one time gift, rating amount, gift of securities, on behalf of giving, and prestigious job title. We then highlighted these names and provided them in an excel spreadsheet.
  2. We created pivot tables that provided ballpark ask amounts. We pulled the past five years to go through the 5th, 10th, 15th reunions and looked at their most recent gift. We then filtered the list by job title and company.

Below is an in-depth look on how we created these pivot tables.

GETTING STARTED – Pulling and standardizing data

First, we pulled all of the class data from our database. Once we did that we had to clean or standardize the data. In our database we found that many people spell company names differently. If someone works at Goldman Sachs, for example, we might see it in the system as simply “Goldman” or listed in all caps, “GOLDMAN SACHS.” In order for the pivot table to really work we needed to make sure that all of the company names were uniform. You can see below how we standardized the company name to Goldman Sachs & Co.:

 

STEP TWO – Creating the pivot table

Next, we inserted a pivot table into the excel spreadsheet we were working in. We clicked into any cell in the main data section and then selected Insert->pivot table.

This window will appear. Click OK.

A new tab will appear and you are ready to begin!

From the pane on the upper right-hand side of the sheet, you can start dragging items to the “PivotTable Fields” boxes (see below).

STEP THREE – Rearranging and Customizing

Pivot tables are incredibly forgiving and you can easily pull items from the top box and move them around to the lower box. If you decide that something doesn’t look the way you want you can easily click and drag it to a different box. Below are images of the finished product that we delivered to the fundraisers.

 

We received positive feedback from the fundraisers who reported that they often use this tool to help them create ballpark ask amounts.

Regardless of the volume of your prospect population, using two simple factors – wealth indicators and past giving – can help you quickly prioritize groups of prospects and create a tool everyone can use.

Filed Under: Campaign Success, Prospect identification, Research Department Success Tagged With: Amy Begg, Harvard, Meghan Hakanson, NEDRA, pivot tables, prospect identification, prospect research

  • Get the Intelligent Edge

    Our weekly update provides insight and commentary on breaking philanthropy news and the latest innovations in fundraising intelligence.

  • HBG Privacy Policy

    By sharing your information you give us permission to send you our newsletter. You may unsubscribe at any time.

New Articles

  • Don’t miss these 3 things – International Research, Justice, and Seismic Fundraising Shifts
  • Philanthropy and prospect research in an (almost) post-Covid world
  • Fundraising data privacy goes before the Supreme Court next month
  • Face to Face With An Ethics Conundrum
  • What the heck are NFTs (and why should you care)?

Keep Updated

  • Facebook
  • LinkedIn
  • RSS
  • Twitter

Get the Essential Handbook

Get Your Copy

Learn More

Categories

  • Campaign Success
  • Career development
  • Due Diligence
  • Effective searching
  • Fundraising Analytics
  • Fundraising Ethics
  • HBG Book Club
  • International prospect research
  • Most popular
  • News
  • Non-profit trends
  • Podcast
  • Prospect Development 101
  • Prospect identification
  • Ratings
  • Relationship management
  • Research Department Success
  • Researching Companies
  • Researching Individuals
  • Social Media
  • Strategic planning
  • Trust & Foundation Research
  • Uncategorized
  • Wealth screenings

ABOUT HBG

The Helen Brown Group was formed in 2002 with a goal to be different.

Allow me to explain my personal philosophy to you.

  • Learn More About HBG

Latest Intelligent Edge Articles

  • Don’t miss these 3 things – International Research, Justice, and Seismic Fundraising Shifts
  • Philanthropy and prospect research in an (almost) post-Covid world
  • Fundraising data privacy goes before the Supreme Court next month
  • Face to Face With An Ethics Conundrum
  • What the heck are NFTs (and why should you care)?

Privacy Quick Links

  • Privacy Policy
  • Privacy Tools

Copyright © 2021 The Helen Brown Group LLC.

David’s career in prospect research began in 2009, as a graduate research assistant at the Shippensburg University Foundation. In 2011, He became a development researcher for the University of Virginia. In 2015, David became assistant director of prospect research at the University of Baltimore, serving for 3 years. Recently, he was the director of development for Trees Forever. David Joined the Helen Brown Group as a research assistant in January 2020. He earned a B.A. in Theater at Indiana University of Pennsylvania and a M.A. in Applied History at Shippensburg University. David is a member of APRA and APRA Great Plains.

Kenny has worked in development since 1999 and has been involved in prospect research since 2002.

Prior to joining The Helen Brown Group, he was the director of donor and prospect research at the United Way of Massachusetts Bay. Kenny is a member of APRA and NEDRA.

Tara first began her career in development in 2002 supporting the Major Gifts department at Simmons College, and ultimately went on to serve as Assistant Director of Prospect Research. Since that time, she has also worked as a Senior Research Analyst at MIT, as Associate Director of Prospect Management and Research at the Harvard Graduate School of Education, and as Director of Development Research at Combined Jewish Philanthropies (CJP).

Tara originally joined the Helen Brown Group team in 2007 and served as a Research Associate and ShareTraining coordinator until 2008 – she rejoined the company as a Senior Researcher in 2013 and was promoted to her current role in 2018.

She has been an active volunteer with NEDRA for many years and served on the board of directors from 2010-2016. During her time on the NEDRA board, she served in many different roles, including terms as Vice President, Secretary, Chair of the Website and Technology Committee, Chair of the Volunteer Committee, and as Chair and Editor of NEDRA News. She is currently a member of the NEDRA Bootcamp faculty. In addition, Tara has also been involved as a volunteer with Apra, serving stints on the Membership Committee, Chapters Committee, and Bylaws Task Force.

Angie began her career in development in 1999 at Virginia Tech in Corporate and Foundation Relations and later in prospect research at the University of Connecticut Foundation.

A graduate of the University of Tennessee at Martin, her experience includes grants management at the University of South Carolina, program evaluation for South Carolina Research Authority and human resources analysis for Nissan North America.

She returned to development in 2007 and worked in various prospect research positions at Vanderbilt University, including Associate Director. She was named Director for Vanderbilt University Medical Center’s research office in 2015, and joined The Helen Brown Group in 2016.

jdsahjhDJDJHJSH

Josh began his career in development as the Phonathon Coordinator at Keene State College. He then worked at non-profit consulting firm Schultz & Williams in Philadelphia.

He started his research career at the University of Pennsylvania as a Research Assistant in 2005. He then moved over to the Wharton School of Business, where he became the Associate Director, Research and Prospect Management. Josh joined the Helen Brown Group in 2016.

Josh is also a Colorado licensed Realtor and graduate of Lehigh University.

In March 2017, Kristina joined the Helen Brown Group as a Research Associate. Before joining HBG, she was the Research Manager at Pratt Institute in Brooklyn and an Associate Manager of Prospect Research at City Harvest, a food rescue organization. Kristina started her non-profit career as a legal assistant at the Metropolitan Museum of Art in 2004.  She is a member of Apra and Apra Greater New York. She was Apra Greater New York’s Director of Programming from June 2014 to May 2016. Kristina graduated from The University of Chicago and the Bard Graduate Center.

Grace began her career in development in 2001 as Executive Assistant to the Chief Development Officer with Brigham and Women’s Hospital (BWH), a Harvard Medical School-affiliated academic medical center.

In 2003, she became a prospect researcher for the BWH principal and major gifts team and spent the next 11 years in various research positions with BWH, culminating as Assistant Director of Prospect Research. She has been affiliated with The Helen Brown Group since January 2014.

Heather began her career in 2002 as a prospect research coordinator for the Rocky Mountain Elk Foundation and then moved to Carroll College in 2004.

In 2005, Heather began working on her own as a freelancer and eventually started her own consulting firm, Willis Research Services, in 2007. She joined The Helen Brown Group in 2012.

Heather is a member of the Association of Professional Researchers for Advancement and the Montana Nonprofit Association.

Jennifer began her career in development at her alma mater, Wheaton College, where she was an administrative assistant for the major gifts department.

She joined The Helen Brown Group in March 2008. She earned a master’s degree in library science from the Southern Connecticut State University in May 2009. Jennifer is a member of APRA and NEDRA.

Rick has been a member of the Helen Brown Group team since 2005. Prior to joining HBG, Rick was director of research at St. Paul’s School in Concord, New Hampshire. Rick has worked in development since 1996, both in prospect research and major gifts fund raising. His experience includes the University of Vermont, Phillips Exeter Academy and St. Paul’s School.

Rick is past president of NEDRA and is a member of and frequent volunteer for APRA.

Josh began his career in development as the Phonathon Coordinator at Keene State College. He then worked at non-profit consulting firm Schultz & Williams in Philadelphia.

He started his research career at the University of Pennsylvania as a Research Assistant in 2005. He then moved over to the Wharton School of Business, where he became the Associate Director, Research and Prospect Management. Josh joined the Helen Brown Group in 2016.

Josh is also a Colorado licensed Realtor and graduate of Lehigh University.

Mandi has worked in prospect research and management since 2006. She began her development career as a research analyst in development research at City of Hope, an NCI-designated comprehensive cancer center in Los Angeles. From there, she became the manager of prospect development at Huntington Memorial Hospital, a community hospital in Pasadena, CA. Most recently, she was the associate director of prospect research and management at Occidental College, a private liberal arts college in LA.

Mandi has a BA degree in print journalism from Southern Methodist University and a master’s degree of library and information science from UCLA.

She joined the Helen Brown Group in May 2019.

Kelly began her career in development in 2008 as an administrative assistant in Major Gifts at Wheaton College.

In 2010, she became a research analyst at Dana-Farber Cancer Institute in the Division of Development & Jimmy Fund as part of the prospect identification team. Kelly joined The Helen Brown Group in 2013.

She is a member of APRA and NEDRA.

Jayme began her career in development in 2008 at the Rutgers University Foundation, where she spent the next seven years, first in prospect management and then prospect research. She spent several years at Monmouth University as their senior prospect research analyst, working with the fundraising staff, university president, and top leadership. She has worked as both a volunteer and consultant for non-profits in the areas of research and writing.

She earned a bachelor of arts degree from Drew University and a master of communication and information sciences from Rutgers University. She is a member of APRA.

Jayme joined The Helen Brown Group in April 2019.

Julie has managed finances for The Helen Brown Group since its founding.

In her spare time, she is an editor for the PBS series Masterpiece at WGBH. Julie was nominated twice for an Emmy award for her work on the PBS show Zoom.

Heather began her career in development in 2001 as a prospect researcher for National Wildlife Federation (NWF). She was with NWF for more than thirteen years, including nearly five years as director of research and analytics. Heather is a former secretary of the board of directors of APRA-Metro DC.

She joined The Helen Brown Group in October 2014.

David began his career in development at The Gunnery school in northwest Connecticut in 2011, where he worked in database management and prospect research. Subsequently, he joined the College of Saint Rose as a development research analyst before leading Albany Medical Center Foundation’s prospect research efforts as Associate Director of Prospect Research. He has a Bachelor’s Degree in Sociology from Siena College and is a member of APRA and CASE.

Michele began her career in development in 2012 when she joined the UC Berkeley corporate and foundation relations team as a development analyst. She spent a year and a half at Cal before returning to UC Davis as a prospect analyst. She was with the prospect management and relations team at UC Davis for almost three years prior to joining the research and relationship management team at George Washington University as a Senior Prospect Analyst in 2016.

Michele received her BA in creative writing from Florida State University and her MA in higher education leadership from CSU Sacramento. She currently resides in Northern Virginia, is a member of Apra International, and serves as the social media chair for Apra Metro DC. Michele joined The Helen Brown Group in July 2018.

Angie has worked in development since 2002, partnering with a wide range of nonprofit institutions. She began her professional career at Vanderbilt University in research and prospect development.

She has also worked with a number of community nonprofits in front-line fundraising, grant-writing, and event management. Angie holds an MPA in Nonprofit Management from the Indiana University Lilly Family School of Philanthropy and a BS in Journalism from Middle Tennessee State University. She resides in Nashville, Tennessee, and is a member of AFP Nashville and APRA MidSouth, where she has been active on the executive team.

She joined The Helen Brown Group in October 2015.

Maureen has been a part of the non-profit world since 1991. She started out in annual giving at Harvard Law School and continued her career as director of annual/special gifts at UC Santa Cruz.

In 1999 she made the switch from front-line fundraising to serve as director of prospect research/management at Bentley University and in 2001 began her role as administrator for the North American Foundation for the University of Manchester. She became part of the HBG team in September of 2011.

Helen has been a development professional since 1987. Her previous experience includes The University of North Carolina at Chapel Hill, the Albert Einstein Institution, Boston College, the Harvard School of Public Health and Northeastern University.

Currently she works with a variety of clients to establish, benchmark and re-align research departments; identify major gift prospects; and train researchers and other fundraisers through on-site and web-based training services.Helen is a former member of the board of the Association of Professional Researchers for Advancement (APRA) and is past president of the New England Development Research Association (NEDRA). In 2006 she received the NEDRA Ann Castle Award for service to the prospect research community.

Helen is Special Advisor on Fundraising to the North American Foundation for the University of Manchester and is a member of the board of directors of Factary Ltd. (Bristol, UK). She is a member of NEDRA, APRA, the Association of Independent Information Professionals (AIIP), Women In Development, the Association of Fundraising Professionals (AFP) and Researchers in Fundraising (UK).

Helen is a frequent speaker and has led seminars for a number of professional associations, including Action Planning, AFP, APRA, the Council for Advancement and Support of Education (CASE), NEDRA, RIF, the Planned Giving Council of Central Massachusetts, the Georgia Center on Nonprofits, the International Fundraising Congress and Resource Alliance.

Helen is also co-author (with Jen Filla) of the book, Prospect Research for Fundraisers (Wiley & Sons, 2013).

We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it.OkPrivacy policy