Principles, tips and approaches for extracting insights from your database or CRM system

July 2023

Welcome to TechSoup Connect London Chapter

We've changed our name, but not our focus...

​

Providing a space for those working with digital in or with charities/not for profits to

• share knowledge or experience
• network/discuss challenges with others in similar roles

@TSC_London

techsoup-connect-london

https://events.techsoup.org/techsoup-connect-london-chapter/

TechSoup Connect London Organisers

Agenda

​

• Welcome/Introduction to TechSoup Connect London
• Break out session - what are the challenges you are facing (15 mins)
• Presentation + Q&A (45 mins)
• Claire Bénard - Meaningful Data
• Harrison Brown - Suru Partners
• Break out/Networking session - what you learned/what you will try (10 mins
• Next meeting/Ideas for future sessions
• Community shout outs
• Wrap: Feedback form
• Close (13:30)

​

​

Keeping in touch / Resources

• Share details in chat
• Who you are
• Any useful resources

​

​

Initial breakout

• What are the challenges you face in accessing and using the data within your CRM?
• Breakout: 7.5 mins
• Feedback session: 7.5 mins
• Post in chat
• Contribute directly

​

​

Where are we going?

Where are we going?

On a data adventure!

The 4 types of analytics

The 4 types of analytics

Descriptive

What happened?

Information

How many people did we work with last quarter?

How much did we spend?

The story of a successful bar chart

The story of a successful bar chart

% of people attending

Session number

The story of a successful bar chart

% of people attending

Session number

1

80%

0

100%

The 4 types of analytics

Descriptive

What happened?

Why did it happen?

Diagnostic

Information

How many people did we work with last quarter? How much did we spend?

How can we improve adherence to our programme by 10%?

Why adherence in one center worse than in another?

Why adherence in one center worse than in another?

• Too much to process visually

​

​

• Hard to quantify

Why adherence in one center worse than in another?

Probability of early dropout

Age < 18

Center A

Referral source 1

Referral source 2

Female

Support need 1

Support need 2

Support need 3

The 4 types of analytics

Optimization

Predictive

What will happen?

Descriptive

What happened?

Why did it happen?

Diagnostic

Information

How many people did we work with last quarter? How much did we spend?

​

How can we improve adherence to our programme by 10%?

​

Based on onboarding information, what’s the recommended route for this person to increase their chance of success?

Streamlining processes

Very long onboarding process to define the next best step

​

​

Only the right questions to get the next best step as fast as possible

​

​

The 4 types of analytics

Prescriptive

How can we optimise our actions?

Predictive

What will happen?

Descriptive

What happened?

Why did it happen?

Diagnostic

Information

Optimization

How many people did we work with last quarter? How much did we spend?

​

How can we improve adherence to our programme by 10%?

​

​

Based on onboarding information, what’s the recommended route for this person to increase their chance of success?

Running what ifs scenarios to model the impact of a change in budget on an outcome.

What if scenarios for a foodbank

Forecasting supply and demand to ensure the right food is at the right time and at the right place

What you need to go on an adventure

​

• An actionable problem that is part of a strategy

​

​

• Enthusiasm from the people who collect the data

​

​

• Some ethics framework in place

​

​

• A good CRM based on the right principles

Questions

Providing nonprofits with the right digital tools to change the world.

Specialists in apps & digital service development, CRM selection & implementation, systems integrations and user experience design for forward-looking nonprofits.

​

info@surupartners.com

We partner with impactful organisations to take their digital strategy from early ideas to well-managed, working software.

Making it happen — what should the tech look like?

Technology should be led by use-case, not the other way around.

Understand the context in which stakeholders will access and use the data insights, and the sources of data, before* you start looking at tools. Without this you’ll fall back onto rough heuristics and projects either bloat or fail.

* “We need a data visualisation tool” is not good enough to base a project on.

Map out your data flow, connecting sources to use-cases

Source(s)

Where is data captured?

Is it captured directly into your CRM? Is it spread across multiple systems & spreadsheets? External sources?

Schema

What shape is the data in?

Use-cases

What questions need answering? What are you going to do with the answers, and how/when?

Analysis tools

What tools will help you transform, interrogate & share data?

Would you need to merge multiple record types together to get the answers you need? Is the source data sufficiently structured?

Integration

How will your analysis tools get hold of your data?

Are there direct integrations or will you need to build a data pipeline?

Where on the spectrum from Excel to full BI platform are you?

What’s the business case? Who needs it? What context do they want the answers in? How often will they access it? Are the questions stable over time?

Start from the end

Source(s)

Where is data captured?

Schema

What shape is the data in?

Use-cases

What questions need answering? What are you going to do with the answers, and how/when?

Analysis tools

What tools will help you transform, interrogate & share data?

Integration

How will your analysis tools get hold of your data?

Data flow

Design direction

Don’t start here!

Understanding the jargon

• BI: ‘Business intelligence’: Think charts, graphs and KPIs. BI systems let you visualise data (but not do anything with it).
• AI: ‘Artificial intelligence’: Right now, a catch-all term referring to machine learning, language models, generative tools, or anything sufficiently ‘magic’ that marketers decide to label it AI
• Big data: data that’s too large for standard tools and approaches to handle. (Most organisations don’t deal with this)
• Structured data: Curated data you could imagine living in a spreadsheet
• Unstructured data: A firehose of ‘raw’ data without an obvious schema such as PDFs, notes, images, audio call recordings, videos, Tweets

Understanding the jargon

• ETL: ‘Extract, transform, load’. The process of pulling data from one system, transforming it (redaction, aggregation, changing scheme) and storing a copy of it in another database (g, a data warehouse) with a predefined ‘shape’ (schema-on-write). Normally refers to one-way rather than two-way sync.
• ETL pipeline: The pieces of technology that conduct the ETL process, normally pulling data from source systems into a data warehouse.

ETL

• Many organisations don’t need the heavyweight data transformation capabilities of enterprise systems
• However, you still need to think about how you’ll transform data
• Simple options: formula field, vlookup, joins, concatenation, ‘bucketing’
• Transformation decouples the design of your source systems from the needs of downstream data systems.
• Example: a charity had to report beneficiaries’ age to funder A as “child, adult, elderly” but to funder B as “16–24, 25–30, 31–45, ...” — without a means of doing data transformation they had to get grant applicants to report their beneficiary data in both bucket schema.
• Solution: capture date of birth and transform to funder-specific schema

Transformation

Understanding the jargon

• Data warehouse: A central database that stores a copy of structured data from various sources, both internal (your own systems) and external (public data sets, etc.) An old idea (circa. 1980s). Good for structured data, can’t natively handle unstructured data.

Understanding the jargon

• Data lake: Like a data warehouse but housing raw unstructured data without defining the ‘shape’ (schema) of that data beforehand.
• ELT: ‘Extract, load, transform’: Like ETL, but approaching with the mentality of “store first, transform it later on-demand” (schema-on-read) rather than ETL where you have to decide the ‘shape’ of the data first.

Understanding the jargon

• Lakehouse: Refers to technology that adds a layer of structure, categorisation and governance on top of a centralised data lake to make large amounts of raw data more useful and manageable

Possible approaches

1. Within-system analytics

• Most modern CRM systems have built-in dashboard and charting tools to visualise the data it holds
• In this context, no “E” or “L” but the “T” (transformation) might be achieved by formula fields, rollups, etc.

2a. Manual export to BI tool or Excel

• For exploratory purposes, data doesn’t need to be 100% up to date
• A monthly CSV export into a BI tool or a pre-built Excel file can still provide useful visualisation dashboards for decision-making and may cost less in staff time over time than the cost of automating
• Gives access to the visualisation and analysis tools of a BI platform without the technical complexity of direct integration
• Make sure it’s repeatable & operationalised. If insights and action from your data rely on a human remembering to do something regularly and reliably doing it correctly your foundations are weak. Better to have a simple but reliable organisational system than fancy tools that you don’t get value from.

📆

Shop

Manual export

Manual upload

Bad:

• You export from source, then have to manually rename columns, change data formats, remove certain known ‘problem’ records, then do a vlookup against another spreadsheet
• It relies on a ‘database administrator’, so grinds to a halt when they go on holiday

Good:

• You can define target schema in the source system, mapping source field names/format to destination field names/format as well as pre-built, meaningful filters and selection criteria
• The process doesn’t rely on memory (scheduled exports, regular reminder tasks, etc.)

Good vs. bad extraction processes

2b. Direct integration with BI tool

• Some CRMs have out-of-the-box integrations with BI tools such as PowerBI, Zoho or Tableau.
• The feasibility of such approach could be considered (carefully — hands-on testing!) as a selection criteria when evaluating the market for CRMs. For some organisations it’s important enough to switch CRM for.

Direct integration

2c. Simple ETL to something that a BI tool can read

• A number of lightweight tools are available now to pull data from various source systems into simple file-based storage such as CSV files or Google Sheets, and most BI tools can then read those files as a data source.
• The trick is finding the ones that have integrations with your particular source systems.
• A more robust solution can be to pull data into a database as more BI tools can read from databases, and it might mean you can use cheaper BI tools.

Shop

Shop

Data extraction tool

Direct integration

Direct integration

Manual upload

3. BI tool on top of data warehouse

• For total control, pull data from multiple sources into a data repository
• That central data store can then be plugged into a BI tool, but also useful for other activities such as moving data between systems to enable other activities
• Common example: Data from multiple sources is aggregated into a data warehouse from which a complete picture of a donor can be built up to allow more advanced personalisation of the comms you send them
• Can be a simple (a visualisation tool plugged into a SQL database) or a complex all-in-one system

Shop

Shop

Data extraction tool

Direct integration

Direct integration

Direct integration

Shop

Shop

Shop

Shop

4. Integrated data warehousing & BI tool

• Lots of companies offer products that can extract data from your various systems, transform that data and visualise it all in one place. They act as both the destination database and visualisation tool.
• Can simplify the technical skills required to make the whole thing work, but tend to be towards the expensive / complex end of the spectrum.

What technology do you need?

Answer: It’s actually not that important

ETL services

A simple ETL solution — this is a redacted version of one in production costing just £10/month

Shop

Shop

Principles

Principles

• Make it repeatable & operationalised�If insights and action from your data rely on a human remembering to do something regularly and reliably doing it correctly your foundations are weak. Better to have a simple but reliable organisational system than fancy tools that you don’t get value from. Ideally, make it self-serve to avoid overhead.
• Data preparation�Simple BI tools let you work with the data you give it, but more powerful ones let you stage data and design transformations to apply to your data. Valuable as avoids slow, error-prone manipulation of data outside of your systems. Where possible, cleanse data at source!

Principles

• Each component can be simple or complex�Most data architectures need a data pipeline, a transformation process and something to analyse the data, but these don’t need to be expensive tools — a carefully thought-through Excel-based workflow is better than powerful tools that are under-utilised.
• Incentivise data collection�Make it easy (requires effort & design) and keep re-communicating why data collection is valuable (so talk about what you’re using it for, champion it)

Manual CSV export every month

Excel macro dedupe + vlookup transformation

Excel charts

Principles

• Consider the use case�The appropriate tools and architecture depend on the end goal. Trying to give stakeholders self-serve access to overview data? You’ll want a cloud-based reporting platform (sending round charts by hand every week is a pain). Trying to do a deep-dive analysis of a recent campaign? Use simpler tools you have at hand and focus on the questions and insight that will move the needle.
• Don’t confuse data analysis with operational “reporting”�Charities often talk about a need for “reports” when actually they mean user interfaces that help them do their job. If stakeholders ask for “A grant report” sometimes they actually need “A list of all grant applications that I need to review and approve”. For these use-cases, BI tools are terrible; instead, develop capabilities within the system that work gets done.
• Multiple systems complicate, so consider doing more with core systems�We advise many charities to try and do more with their CRM (sometimes switching to a more capable one) to co-locate more of their data. E.g., use the CRM not just for contacts and marketing but also for grant applications, programme management, volunteer mobilisation and more.

Principles

‘Reports’ ≠ user interface

Principles

• Don’t forget the people�Like any technology, the tech itself isn’t magic — if people don’t adopt it then it has zero value. Break down the divide between “data people” and everyone else by including them in the design, and treat your data leads as enablers, not operators, to increase ROI. Avoid gatekeepers! They’re normally a sign of things going wrong — issues like data quality can be handled by much more effective strategies (ask me afterwards!)
• Think about data when selecting source tools such as CRM�Some CRM systems make it difficult to (a) capture & store well-structured data, and (b) do anything with the data they do hold. Consider both sides (ingress and egress) when evaluating the market for CRMs, and conduct effective hands-on testing to avoid being taken in by vendor hyperbole.

Product

Rigid, but easy to use for simple use cases

Focus on the right market when selecting your CRM

Platform

Flexible, but takes more to get going

• Pure-play�Does one thing well
• Unified�Bundle multiple business areas together
• Multi-product suite�Powerful but expensive, and can end up lagging behind best-in-class

… Dynamics 365 Salesforce Beacon Donorfy Access CRM …

Key points to take away

• Start with use-cases and don’t over-analyse the technology. Keep it simple and be led by outcomes, not the tech.
• It’s much more affordable to engage outside technical expertise if you have a clear view of what you need, so focus on getting clarity there rather than searching for tech on Google.
• Avoid enterprise-level tools unless you really need them. Your data pipeline should be well thought-out, but doesn’t necessitate expensive tools.
• Pay attention to the context of those in your organisation who will use the data. An Excel report that takes a week to prepare doesn’t work for a marketing manager who needs to see daily campaign performance throughout the year.
• Movement and transformation of data should be repeatable, not relying on humans to do the right thing over and over.
• Don’t confuse reporting with operational interfaces.

Next steps & getting help

Suru Partners can help with:

• Designing and developing your data pipeline, and evaluating the market for the right tools for your context.
• Automations and integrations between systems to bring your data together.
• Developing reports, data structures and automations within your existing CRM.
• If your current CRM doesn’t make it easy to capture data or export it in a way that’s actionable, or doesn’t fit your needs well as the moment, Suru’s CRM team can partner with you to help with evaluation and implementation of a new CRM, or the realignment of your CRM to work better for you.

Contact Harrison on harrison@surupartners.com for free advice and recommendations.

Q & A

• What didn’t we cover that you want to hear about?
• What do you want to hear in the next one?

​

Network session

• Break out groups (7 mins)
• What particularly interested you?
• What will you try out?
• What support do you require
• Post thoughts/questions/takeaways to Mentimeter
• On return
• Review Mentimeter

​

​

Next meetings:

• Summer break
• Sept (date TBC: “People and culture”)
• Why important for digital success
• From change resistance to change curiosity - a work session
• Would you attend?
• If you have ideas for later this year, please let us know
• Do you want to help us plan these sessions?

​

​

Community Shout Outs

Tell us about:

• events,
• resources,
• networks
• something about you
• or anything….

​

that is helpful for this community

​

Wrap up / further networking

​

• Feedback survey
• WhatsApp group

​

• Please stay on if you have further feedback, questions, ideas or would like to get involved with organising TechSoup events with us!