Nonprofits and Data

last person joined: 5 days ago 

This group is for those interested in learning and sharing about all things data-related for nonprofits. The Nonprofits and Data group is for people using data to serve a mission, either directly or by improving nonprofits and the nonprofit sector. That includes everything from collecting data and managing databases to analytics, data visualization and data mining. Here are some examples of topics we discuss: using data to improve organizational effectiveness, measuring impact, using data for storytelling, tools for data management and analysis, figuring out the “right” data to collect, and learning skills to help us use data better.

Bridging the technical gap towards a relational database system

  • 1.  Bridging the technical gap towards a relational database system

    Posted Oct 03, 2018 07:13
    Edited by Leah Hoogstra Oct 03, 2018 07:14

    My organization is considering making the switch to a relational database model for our data architecture. We're a small nonprofit with program management and evaluation data currently built on a complexifying ecosystem of spreadsheets. I'm aware that we could set up a managed relational database in the cloud for free through services like Azure or AWS, but the issue is that we do not have anyone on staff with the skills of a DBA. (Similarly, we do not have an IT department.) How does a small organization like ours make the switch from Google Sheets to SQL Server or something similar?

    Are there any tools or tutorials that make setup and management straightforward for someone who is at least familiar with running queries in SQL? Any services that we could access for free or low cost to help with setup, security, and maintenance?

    Any suggestions you have would be greatly appreciated!



    ------------------------------
    Leah H
    ------------------------------


  • 2.  RE: Bridging the technical gap towards a relational database system

    Posted Oct 03, 2018 14:36
    Leah,

    It depends greatly on what it is you're trying to accomplish and how you use GSheets right now.

    Unless you are willing to get some technical expertise in-house, then you're going to want to move your data into a system that sits on top of an RDMS and gives you a user friendly interface to manage your information.  What system you use will depend greatly on how you plan to use the data.

    If you're looking for a free solution, you might try investigating some of the open source CRM packages.  That would give you a more donor-centric view to your data, assuming that's what data your talking about.

    If you can fill in some details about how you use your data and how you plan to use your data, I'm glad to offer some cheap / free solutions that I've run across.

    BTW...  I know what you mean by "a complexifying ecosystem of spreadsheets".  Been there.  Done that.  :-)

    Kind Regards,


    ------------------------------
    Scot Catlin
    UNC-TV
    Research Triangle Park, NC
    ------------------------------



  • 3.  RE: Bridging the technical gap towards a relational database system

    Posted Oct 04, 2018 09:29
    Building your own DB from scratch is something you should only take on if you have the skills and organizational maturity to pull it off. For many small orgs, the problem in going from the ecosystem of spreadsheets to a database isn't really that nobody has the DBA skills. It's that the org has never sufficiently defined and structured its data collection and its metrics sufficiently to make a database work. A database doesn't allow for the (sometimes highly desirable) ambiguity that unrelated spreadsheets allow.

    My advise is to work with an expert who can help identify and set up the kind of system you need. Airtable has been a good tool for us in some of the work we've done in this space.

    ------------------------------
    Isaac Shalev
    http://www.sage70.com
    Stamford CT
    @Sage70
    isaac@sage70.com
    ------------------------------



  • 4.  RE: Bridging the technical gap towards a relational database system

    Posted Oct 04, 2018 15:08
    Edited by Medha Nanal Oct 04, 2018 20:39
    (Post edited to remove incorrect references to previous posts. Apologies).
    Hi Leah,

    I agree with Isaac's comment that working directly with a database is not the best strategy. I've spent about 20 years in Silicon Valley developing products off RDBMS technology to help manage various types of data, and have worked with bare-bone database servers like MySQL, as well as applications, and CRM Systems.

    Working directly off a relational database will not inherently support Collaboration that you have in Google Sheets (because collaboration feature is usually supported at the application layer) -- and using a shared document system with collaboration built in, may not necessarily give you a strong querying capability. Not to mention, managing own database server needs an intense commitment of resources, and you will need to develop your own features to suit your purpose.

    The problem you describe, i.e. managing program specific data, is a fairly standard one, and therefore, any of the available "applications" that run off a Relational Database System would work.

    Regarding the suggestion of using a CRM system: it is true that a CRM system with an ability to create flexible structures would work, but every available nonprofit CRM has this capability to a varying degree, therefore, my word of caution would be to evaluate the system carefully before you take the leap :-)

    Hope this helps!


    ------------------------------
    Medha Nanal
    Strategic Data/Database Consultant for Nonprofits (Fundraising, Operations, Programs)
    www.topcloudconsult.com
    medhananal@topcloudconsult.com
    650.600.9374
    ------------------------------



  • 5.  RE: Bridging the technical gap towards a relational database system

    Posted Oct 05, 2018 10:18
    Edited by Nandini Narula Oct 05, 2018 10:18
    Hi Leah - Agree completely w/everything said and also wanted to add another vote for Airtable. We've used Airtable too to help small orgs make the move from spreadsheets to a CRM - it's low cost, facilitates collaboration, and we've used it to help orgs think through and even test out their data model and some use cases before making a bigger leap to a larger scale system. Worth a look!

    ------------------------------
    Nandini Narula
    CRM & Data Management Consultant
    www.sertuspartners.com
    Montclair, NJ
    ------------------------------



  • 6.  RE: Bridging the technical gap towards a relational database system

    Posted Oct 05, 2018 16:03
    Hi Leah,

    I recently worked with a client, also a small non-profit organization, that had a similar information management challenge - they had decades of important data stored in complex spreadsheets, which were getting too big to be useful. Consequently, I am familiar with the problem you describe.

    While I agree that taking on a data conversion project that translates spreadsheet data into a relational database is not to be taken lightly, there are cases where this solution works very well. My client now has a free, single user license version of SQL Server that also provides a reporting service. They do not have an IT department, so found volunteers to provide the occasional database support needed.

    My suggestion is having an information management specialist evaluate your data needs and limitations to determine viable options available to your organization. This type of resource may be someone in your community willing to donate their efforts, or charge a non-profit level fee. The valuable outcome of this effort is being armed with the knowledge needed to make a decision regarding how to proceed, or not to proceed at all.

    ------------------------------
    Rebecca Caudill
    Information Management Consultant
    ------------------------------



  • 7.  RE: Bridging the technical gap towards a relational database system

    Posted Oct 08, 2018 12:29
    The challenge of information management within an organization is prolific and certainly not unique to nonprofits.

    I would be really interested to hear stories from the community about what companies with really good information architecture and practices look like. I imagine it is a combination of good standardization and education, well defined systems, and lots of buy in.

    I expect that most organizations are somewhere between two models when it information management: The one person does it all model (centralized) and the everyone does their own thing model (distributed). Figuring out where to be in between those ends depends on factors like how much of your information gets used by everybody vs some individuals? How much of it is used to make organizational decisions? How does everyone currently get on the same page when it comes to your data? Does everyone have to explain their own spreadsheets, or does everyone pretty much understand how the ecosystem works? Would just streamlining the ecosystem with some forms and fewer sheets be a viable option?

    With those thoughts out of the way, I expect making the switch from a heap of Sheets to a database would be something like this:
    1. Be ready to try a lot of new things, learn a lot, and maybe break some stuff (an often necessary part of learning in the digital world I think). Make A LOT of backups.
    2. Get stakeholders and all the sheets together and map out the information. Where does it come from, when does it get updated, and how do people want it reported to them. Figure out what works and doesn't work with the current sheets. Figure out what are must haves for people and the organization.
    3. Have one or several people start getting cozy with how SQL works. Try things like Intro to SQL: Querying and managing data | Khan Academy or SQLZOO 
    4. Go on a big Googling adventure and research free and paid SQL products. See what looks most usable for you and your situation. YouTube has helped me out a lot. 
    5. Sketch out a plan of what the new system would look like, who would enter what, how people would get reports. Chat with everyone involved and see if that seems like a solid plan. If not, you've learned a lot and you can always come back to it and try again. If everyone is bought in, start working on it.
    6. Document everything! If there is anything I have learned so far, it is that good documentation goes a long way. Make notes about what you are doing and why. Make guides with screenshots or videos so anyone can get up to speed on the new system. I have found that folks tend to use the platforms they want to use (often because they are more comfortable with them). Documentation can help folks become and remain comfortable with new software.

    Be sure to come back and let us know where you are headed and what you find! I'm sure there are other organizations thinking about the same questions.

    Colin

    ------------------------------
    Colin Roberts
    Rainier Scholars
    Seattle, WA
    ------------------------------



  • 8.  RE: Bridging the technical gap towards a relational database system

    Posted Oct 14, 2018 08:32
    Edited by Leah Hoogstra Oct 14, 2018 08:43
    Thanks everyone for your inputs and perspectives! I think this has helped me realize that the pause, re-evaluation, and regrouping that's needed before making a move like this can be something useful. It is not only a software change but a significant change in how people can use and access data across the organization, so it needs to be thought through and planned out carefully.

    For now, I think we'll be sticking with Google Sheets but doing what we can to control access to sheets that need to maintain their integrity. Those sheets are already restricted in that data are entered via standardized applications and not directly into the sheet itself, but there are several silos where the same information is being taken down in multiple places. My team is working to improve how these tables operate in relation to one another and improve accuracy for reporting and analysis down the line. This particular subset of the spreadsheet ecosystem might be able to function just as well in SQL Server, but moving there is not a simple process and perhaps not something we are ready for just yet.

    If Access were able to integrate into our workflow, that might've been a good solution for this particular set of tables. If you know of other software solutions that fall into that intermediate space, I still would love to hear about them. I will be checking out what's been mentioned here, and I am especially interested in solutions more general than those targeting the CRM model (since we do use that on the donor side, but the data I am referencing here are more operational).

    Meanwhile, I appreciate the advice on the broader scope as well! This question has sparked some useful conversations about where we hope to grow and what kinds of things we need to be thinking about in that process. I've been finding useful resources on NTEN's archives about approaching that broader conversation too. I'm glad my research led me here!

    Thank you!

    ------------------------------
    Leah Hoogstra
    ------------------------------



  • 9.  RE: Bridging the technical gap towards a relational database system

    Posted Oct 15, 2018 14:25
    This is likely way out of scope for the original problem, but it's neat and worth a share.

    In the past few years, I've tried to find ways to use GoogleSheets as a database of sorts. Many of those methods require the individual sheets to be technically publicly accessible via URL. Because that's never been an option for me, I've never done it. The method in the link below shows a way to keep them private while being able to read and write to them using Python. By itself this definitely isn't a solution to your problem (as I think the biggest problem you'll be solving is procedural rather than technical), but there is some solution that would use this technique. (Plus, it is just a cool way of using GSheets!)

    Google Spreadsheets and Python
    Twilio Blog remove preview
    Google Spreadsheets and Python
    So if you're building out a quick CRUD app for e.g. internal use, Google Docs as a backend (consumed via JSON) is *surprisingly* powerful. - Patrick McKenzie (@patio11) July 5, 2014 In this tutorial, we'll use Anton Burnashev's excellent Python package to read, write, and delete data from a Google Spreadsheet with just a few lines of code.
    View this on Twilio Blog >

    After writing this I also found an article about a service called Sheetsu that might also be able to help without being public, but it's a little unclear to me:
    The complete guide on how to use Google sheets as a database - Coding is Love
    Coding is Love remove preview
    The complete guide on how to use Google sheets as a database - Coding is Love
    Let's talk about the mighty Google sheets. Mighty? Yes! It is one the best spreadsheet apps on par with Microsoft Excel. Actually, Google sheets is better than Excel in few cases because of its features like Apps script, Timed triggers, Form triggers, Google sheets API, importXML etc.
    View this on Coding is Love >


    ------------------------------
    Colin Roberts
    Rainier Scholars
    Seattle, WA
    ------------------------------