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

An Excel spreadsheet is not a database

For some time, I’ve been giving Charities guidance on procuring new CRM database systems, mainly in the fundraising area. I’m sure if you’re reading this, you have an idea of what a database is but just to be clear. Databases are a central repository for structured data. Normally they would have some type of validation (checking) on the data that you put into it. This is one of the main reasons why Excel spreadsheets are not databases! Excel can be used to collect data or maintain lists of data but very rarely do you see Excel spreadsheets that make sure that the right type of data is in the right cell or have special forms that let you enter data in a structured way.

So why do you need a database, you could just use a spreadsheet. For everything that is in this post there are of course exceptions or ways around what I’m suggesting.

Here are my 5 main reasons why I think every charity should have a database:

1.Collaboration

When I mention collaboration it can scare lots of people about who has access to “their” data. First thing that I suggest is that the data is the asset of the organisation, not your personal data, that’s something completely different and you having your own set of data means that a supporter is probably not getting the best from your organisation. Always put yourself in the position of the supporter/beneficiary – they have a relationship with the organisation and probably with you as an employee, but you should only ever use one voice when you speak to them. This is only possible when all data is in one place. The organisation has one version of the truth, this can never be a bad thing. Yes there will be some data that the supporter may not want you to share across the organisation, but sharing is caring. A database lets multiple users access it at once, something that is much harder in Excel and almost impossible beyond 2 users. 

2.Legislation

From a legislative process, having your data in one centralised accessible place is a good thing. First of all if your supporters are multi-faceted, you’ll know. Otherwise you’ll have to look through paper files / Excel spreadsheets or other places that you have data as both paper and electronic data are governed by the Data Protection Act. The other thing to think about here is that legislation is not just about the Data Protection Act, there’s also your requirements around PCI-DSS Compliancy, Gift Aid and privacy and electronic communications (PECR).

3. Consistency

Now here’s some stuff that you can do in an Excel spreadsheet but can be easier when you’re using a database. Think of things like titles in an Excel spreadsheet. Some people would put Mr or Mr. or leave it blank or put something else. Database consistency should help with things like drop down lists and options. Like I said this can be done with data validation in Excel but this won’t do formatting validation as easily. Some examples:

  • what do postcodes look like?
  • historically Towns were required to be in Uppercase by Royal Mail (now it isn’t required to be upper case)
  • Telephone numbers, if you’re using a system that recognises telephone numbers does it needs to have its international dialling codes how does it cope with spaces.

These things are things that should be picked up by a decent CRM system. In a perfect world there would be an internal description field set up for things like drop down lists so you know what type of data values you’re expecting in here for example Marathon Runners and their estimated completion times what would this look like in your database 3h 45, 03:45 hours, 225minutes

Obviously thought needs to go into how to structure your profiles/attributes when you set up your CRM system.

4. Efficiency

Why do users like Excel spreadsheets – because they are easy, we’re all comfortable with them. Most people can copy and paste from or to them. We know how to find and filter the data and get what we want from it. An Excel spreadsheet works with Microsoft Word so sending letters or printing labels is easy.

All of this ease has to migrate to CRM systems. Data collection shouldn’t be tedious, forms should be simple and easily understood, ideally self-populating where possible e.g. My gender is most likely male as my title is completed as Mr, this doesn’t mean it can’t be changed but it should be capable of working out the default which could be overwritten if needed. The same when processing income, I know that the last thing that you received was this appeal therefore when I process income, I should be able to guess that you’re responding to this. I shouldn’t have to retype all of the information again if it’s already in the system.

The one thing that is my biggest bug bear is users who are re-keying electronic data because import processes are too laborious or complicated. Importing data of any kind shouldn’t be difficult it should be as simple as a copy and paste into a template that does all the heavy lifting for you.

5. Measurement

Finally, there’s no point capturing data if you can’t report on it and count your successes. Yes of course you can do reporting in Excel but you’d have to create your own reports. Most of the CRM systems come with a number of reports as standard like income reports, new donors’ reports. Some of the new CRM systems are doing more intelligent reporting with things like letting you know that you’ve not been in touch with key supporters, reminders around when to get in touch with a supporter for their birthday, the anniversary of their gift, their potential change in taxpayer status. All of which help you craft your message at the right time to the right people.

So these are my 5 top reasons about why choose a database over a spreadsheet. What are your reasons for using a database?

Lastly, please don’t think that a database is out of your reach, there are solutions to fit all budgets, teams, processes. The key with all of it is keep it simple to get started and build on that once you’re up and running.

Actually Data can help you with embarking on a project like this, if you’d like to talk through how we can help, let’s grab a coffee.