Categories
General Insights

Maths for Database People

While many database professionals are proficient in math, I’ve met a few who break out in a cold sweat when I ask them about it.

As with most things I teach, I always try to start simple. You’re a database administrator, not a financial modeller. It’s the same with statistics; once you’ve mastered the fundamentals, you can ignore the rest until you really need it.

So, to make things a little less scary, and especially if you’re doing a lot of work with reports, here are a couple of things that might help:

FunctionIn ExcelIn Power Bi (DAX)Notes
Sum (Adding Up)Cell + Cell or =Sum()Sum()This will combine values.

If you do this with cells, all of the mathematical operators (+,-,/,*) will work.
Mean Average=Average()Average()This will yield the mean average. (Total all the numbers and divide by the number count)
Mode Average=Mode()It’s complicatedIn Power Bi, you must calculate the frequency of the numbers that appear to take the one at the top. The Mode Average is the average based on how many times a value appears in a list.
Median Average=Median()=Median()The median average is the number in the middle of the range; it is similar to the mean but differs slightly.
Percentage=cell*x%

Or
=(small number / big number)*100
=DIVIDE( small number, big number, 0)

*The zero just removes the possible errors.
If you wanted to know what a percentage of a number is, for example, the GiftAid amount on a donation, you could multiply the donation amount by 25%. If you wanted to know how much Individual Giving income there was in comparison to the rest of the organisation, you would need to total (sum) the income for Individual Giving, sum the income for the organisation, and then divide the individual giving income by the total income and multiply the result by 100.

Essentially the way I remember it is small number divided by big number multiplied by 100.

There are many resources on the internet that can assist you with these functions and provide a more detailed breakdown, but hopefully this means that some of you who used to break out in a cold sweat now have a little more assistance.

As with previous posts, these are not exhaustive. As with the post on Excel Functions, I’m not here to rewrite the Microsoft help files; rather, I’m here to point you in the right direction.

Categories
General

Excel Functions for Database people

The ability to use Excel is a critical tool in a database professional’s toolbox. There are a few key formulas that you should be familiar with. Here are a few of my personal favourites.

But, before we get there, I thought I’d mention some of the other things Excel is good at and bad at.

I’ve used Microsoft Excel (full name) for a variety of purposes. I’ve used it to transform data and to create desktop apps with the help of Macros and Forms. I’ve used it for reporting as well as, dare I say, data capture. Please see my other post about Excel not being a database!

It can be a very flexible and powerful tool, and it is also an application that most businesses have, making it simple to roll out solutions to customers. Another great feature of Excel is that it includes Microsoft’s new(ish) power query tools. If you’re unfamiliar with Power Query, check out my post about how APIs have become much easier to use!

Right, I’m getting carried away, let’s get back to formulae or, as they’re sometimes called, Functions. Here is the official Microsoft function help: 5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb – https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb – Never be afraid to seek assistance (F1 is the shortcut key for help in all Microsoft Applications). If you get stuck, remember that Google is your friend.

So, to get you started, here are some formulas that I use frequently when working in Excel; these are the slightly more obscure ones rather than the standard SUM, AVERAGE, COUNT, and so on.

  1. =IF – This allows you to generate a Boolean logic value based on a set of criteria. =If (CELL>=50,”Top”,”Bottom”), for example, would identify if the value in a specific cell was greater than 50, in which case it would say top, otherwise it would say bottom.
  2. =LEN – This function returns the CELL’s length. When importing data into fields with a maximum length, such as telephone numbers, this feature comes in handy.
  3. =Substitute – This function allows you to replace values within a string, for example: =Substitute(“Anthony”,”hony”,”onia”) would change Anthony’s name to Antonia.
  4. =CountA – Counts the number of non-blank cells. This is something I’ve used a lot when creating custom reports for customers.
  5. CountIFs and SumIfs – I’ve combined these two because they are used in Reporting, where I need Count or Sum functionality based on a Logical Statement, for example: Count this row if the Town is London. Sum if does the same thing but adds a value, for example: What is the total income from London residents?
  6. NETWORKDAYS – This is a sometimes useful function that returns the number of working days between two dates, such as how long it takes to send a thank you letter after receiving a gift. I’d use networkdays based on the gift and acknowledgement dates. This could also be used for supporter journeys. How long does it take to turn a prospect into a participant?
  7. LEFT / RIGHT – This returns a number of characters from the left or right side of a string, for example: =Left(“Anthony”,3) returns “Ant” because these are the first three characters.
  8. ISDate – This checks to see if a value is a real date, which is useful when you have data that will not import into a system.
  9. ISBLANK, ISERROR, and ISNA are all used for error checking. All of these are extremely useful when working with reporting and data that may not meet your report’s criteria.
  10. Finally, all good database professionals will be well-versed in Vlookup. This is the ability to take data from another source and match it to your row using a common field. For example, if you want to add a postcode to your contact sheet but the address is in a different table, Vlookup is the way to go. It has some quirks that you’ll need to get used to, but once you do, you’ll be hooked. It reminds me of pivot tables, but that’s a story for another day….

Finally, some new developments with Excel functions, which, if you’re still reading, must mean you’re interested.

  1. XLookup – A new variation on the Vlookup function that allows you to go forwards and backwards in time. It has a search mode, which is very useful.
  2. Excel 2021 is a new version of Excel that was released in October 2021.
  3. Finally, many people who are far more nerdy than I am seem to be getting excited about Excel and Lambdas: https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67 This appears to me to be a solution looking for a problem at the moment, but I’m confident that once I understand it or have used it, I’ll be raving about it.

So, what have I overlooked? Excel has so many functions that I’m sure you have favourites as well. As previously stated, this list is by no means exhaustive of all the functions available in Excel; rather, it focuses on the ones that I use the most frequently. Please leave a comment if you believe I have missed any.

Categories
Fundraising Databases

Engaging Networks Integration

Tired of manually transferring data from your Engaging Networks platform to your fundraising CRM system? Working with Actually Data as your Engaging Networks partner can help to streamline this process, saving you time and effort while allowing you to focus on what’s most important – your fundraising efforts.

Our experienced data professionals have extensive experience with Engaging Networks integration and can collaborate with you to develop a customised solution that uses Power Query as the ETL tool. With our assistance, you will be able to transfer data between your Engaging Networks platform and your CRM system easily and seamlessly, ensuring that your data is always up to date and accurate.

However, the advantages of working with Actually Data do not end there. Actually Data Analytics, our sister company, can assist you in visualising your Engaging Networks data using Power BI. You’ll be able to quickly and easily understand your data and make informed decisions about your fundraising efforts with interactive dashboards and reports.

We can provide training and support in addition to data integration and visualisation to help you get the most out of your Engaging Networks platform. We’re here to help with campaign setup, reporting, and any other aspect of your Engaging Networks usage.

Don’t waste time manually transferring data between systems; instead, work with Actually Data as your Engaging Networks partner to regain control of your data today. Please contact us to learn more about how we can assist you.

Categories
General

Welcome to ADRFM

I’ve really enjoyed working in the sector over the years; there are great people and causes, but one thing that I’ve always struggled with is a lack of data understanding.

While typing this, I recall a train conversation I had with the head of Individual Giving and another fundraiser early in my role at VSO where I was waxing lyrical about data and its usage and how animated I got and was told to calm down and you’ll go far, but I also remember thinking to myself that I need to bridge the gap between the database and the fundraising team. As Database Manager, I assumed this was my responsibility.

15 years later, I’m still trying to bring fundraisers closer to their data. Over the years, I’ve learned that fundraisers prefer colours to a wall of numbers – I’ve tried both – they prefer simplicity to having to write queries and are mostly comfortable in excel. Some of the new fundraisers, on the other hand, and the ones I believe will excel (see what I did there?) in the coming years, are those who are curious about their supporters and want to get closer to their data.

Enter ADRFM

ADRFM is a report suite that covers the key reports and metrics that fundraisers have requested from me over the years. ADRFM (Actually Data’s Really Fantastic Metrics) was born.

Delivering training sessions is something I’ve always enjoyed. When a user understands what you’re trying to show them, such as queries in Raiser’s Edge. There’s no better feeling than making someone self-sufficient. I’ve worked with organisations that requested reports from CRM systems, as well as organisations that had no reporting in place or had no idea how well they were performing other than what was in the management accounts.

The level of detail in your CRM system should allow you to answer questions like: Who are my best supporters, where do they live, what products do they buy, when do they buy them, and what will they buy next. None of it of these will come from your management accounts.

Working with a number of organisations where monthly reports were required, as well as doing mailing selections for clients. I’ve spent a lot of time considering what these organisations, large and small, have in common. Simple things like RFV (Recency, Frequency, Value) to single givers and multi givers seemed to be at the forefront of a fundraiser’s mind.

Then there were the average gift amounts and the number of communications received by a supporter. We have over 20 metrics that we can calculate on your data that you can use to select supporters or review what they’ve done, some of which may be things you’d like to test in the future in a mailing or supporter journey.

I hope that having a suite of reports that cover all aspects of fundraising and are easy to use and explore your data brings fundraisers closer to their supporters and helps them understand what they do and when they do it. It’s colourful, so it should be more engaging than a crystal report, and it provides a starting point for organisations that haven’t reviewed their reporting requirements. One of the clients used it in this manner; they needed reporting but weren’t sure what they wanted, so they started with ADRFM and discovered that it meets the majority of their needs.

ADRFM is a subscription-based, system-independent product. So it doesn’t matter if you’re using thankQ, Raiser’s Edge, D365, Salesforce, or any of the other CRM systems on the market; our system will work with it. Customers use data from multiple systems together rather than going through a full data warehouse solution.

As with most projects I’ve worked on, I believe that reporting is a journey. If you’re just getting started, we have a reporting suite that will get you and your organisation started. This is ADRFM in a nutshell; it includes reporting and KPIs.

If you are a larger organisation that is already embracing data and looking to take the next step with your reporting, we have the Pro version, which includes reporting, KPI’s, metrics, and our very own segmentation tool. This enables fundraisers (or anyone else?) to create their own segments for analysis.

We’ve also created bespoke reports based on our data model and data from Google Analytics and Email Marketing Platforms like Dot Digital.

So, if you want to learn more about ADRFM or discuss your specific reporting needs and how it might help your organisation, get in touch and we can have a virtual coffee and talk about it in more detail, though you may need to tell me to “calm down” ;). Please Contact Us

Categories
General Training

Database Team Structures

I’ve been involved in analysing database team arrangements across organisations as a follow-up to my article on database personnel. When building fundraising teams, one element that is frequently overlooked is the infrastructure required to support the expansion. So I decided to discuss how I attempt to determine what is necessary.

Understanding the team’s requirements, both present and future, should be your first priority. Hopefully, your company has a list of routine duties, such as: Weekly or monthly events could include processing Direct Debits or importing JustGiving data. This is what I would refer to as “business as usual” (BAU) material. Once you get the list, you must indicate how long each task takes so that you can determine how much time is spent on BAU work each month. As part of this, I would also include monthly team meetings, mailing choices (if they are regular, like once a month’s worth of Enews), training sessions, and support.

Then there is project work, whether it be an analysis, a new product, an innovation, or a study of your CRM system that requires improvements. Once more, you should keep a track of how much time the team spends on this kind of work. This is what I would consider project work, and I would also include scheduled yet sporadic mailings, such as if you send out appeal mailings on a quarterly or biennial basis. Additionally, I would advise you to regularly check in with system users to make sure everything is still going well. I advise creating a super user group that can discuss how their respective teams use the database. and anything that irritates them or prevents them from doing their job.

You now have a rough idea of how long projects that you are aware of are taking.

Last but not least, there is Emergency Response work. Either something has gone wrong and needs to be fixed, or Blackbaud is withdrawing the office integration, in which case a patch needs to be found. The organisation won’t be aware of this kind of situation, but there is no way around the need to have a response. You’ll need to be aware of how much time you spend on this kind of work, just like you would with the other kinds.

In an ideal world, it should look something like this:

BAU – 50-60%

Project work – 25-30%

Emergencies -5-10%

The amount of real days that people can work must then be taken into consideration, along with any holidays. I assume there are 200 working days in a year because I am a consultant.

With these estimates, you ought to now have a better idea of what the team’s requirements are.

Most organisations with fundraising revenues exceeding £5 million should require more than one individual to work on their database team, in my opinion (So long as the database team is just responsible for the database and not the whole IT infrastructure stuff) Just to be clear, I’ve worked with several organisations that rely on one or two superusers to fill the role of database management because they don’t have one. With the information above, hopefully it was clear why having a database person was necessary.

So, let’s go back to business. You’ll have a better idea of the activities that are necessary and, consequently, what you’ll need in a team, based on the time split required between BAU and projects. I’m assuming you’ll need someone to handle imports, mailing selections, insight and report production, as well as project-related deliverables. Now, just as you don’t want knowledge silos in your database, you also don’t want them in your teams, as this can cause issues with things like succession planning.According to the size of your organisation, you may need project managers who collaborate with the database team or business analysts who can translate user needs into briefs for the database team to implement change. In an ideal world, all database teams would have at least two members so there is coverage for things like holidays and illness.

Digital strategy is one of the topics I’m hearing about increasingly frequently. I understand that many organisations are still embracing digital technology, and I believe that the impact of digital strategy on database teams appears to be broad. A new strategy of any kind should involve your database team early on so that the database can be set up in a way that allows you to report on the success of your programmes. It also appears to have different success metrics than what the majority of people are used to. Every strategy should include a list of specific success metrics. As always, start small. Consider the top 3 factors that contribute to success and grow from there.

And now that I’m just looking ahead, I would love to see more heads of data sitting on leadership teams. Although the database team’s primary responsibility is to serve its users, having a voice at the table at the beginning of strategic discussions and being aware of how data is affected by process, governance, or insight means that we are on board for the journey and have a clearer understanding of the WHY behind what we are doing.

Instead than just being a place to store things or a liability, organisations need to view their data as an asset. If you’re interested in learning more, I recently read a book called Leverage that goes into this topic in further detail.

I therefore expect that this will be helpful to individuals who are having trouble figuring out new team structures, and I anticipate seeing more database professionals on leadership teams in the industry. Reach out to other database managers in the industry as much as possible. Our industry is great at sharing information; long may it continue.

There are a variety of different types of structures that database teams in the UK charity sector may use. Some common structures include:

  1. Centralised database team: In this structure, all of the organization’s databases are managed by a single database team. This group may be in charge of data modelling, analysis, integration, and storage.
  2. Decentralized database teams: In this organisational structure, there are various database teams, each of which is in charge of managing a particular collection of databases. These teams may be set up according to location or function (such as financing, programme delivery, or fundraising).
  3. Hybrid structure: In this structure, centralised and decentralised database teams are combined; some databases are managed by a central database team, while others are managed by decentralised teams.
  4. Database team is provided by an external vendor or service provider rather than being handled internally in this arrangement.

The appropriate structure for a database team in the UK charity sector will be determined by the organization’s size and complexity, as well as its specific needs and goals. Depending on their changing needs and priorities, organisations may choose to adopt different structures at different times.