Add Donor Calculated with airSlate SignNow

Add donor calculated

we're going to look at the retention and acquisition calculation in two parts part one will be considered like retention acquisition 101 and part two will be more like a mile say 301 or 401 level of retention acquisition calculation for my first example I've gotten loaded onto my screen here 1,000 donors and I can see that if I go down to the bottom that stops at row 1,001 the first row being the headers that gives me 1,000 full records makes my math a little bit easier to do at the moment to find my retention what I'm going to do is I'm just going to make a simple count of the donors that who donated in 2012 who also donated in 2013 the way I'm going to do that is to use the excels filtering command for this so I'm going to go to data and filter now what I'm going to filter for when I select the down arrow is I'm gonna select all those that gave more than 0 in other words I'm going to select everything but 0 when I do that on the bottom of the screen it tells me 863 863 so what I'm gonna do is go down to the bottom right now and I'm just gonna make a note that I had 863 donors in 2012 continuing over to my 2013 column again I'm looking for those that I have retained and so once again I'm looking for those with everything but zeros click OK and my number now goes to 618 so don't watch that corner number down here that'll tell you how many you had 618 so my retention is equal to 618 divided by 863 or about 72% if I come to my home and select the percent symbol and maybe even expand the decimal or so I show my 70 1.6% for retention well let's go see what I've got for acquisition in this same table I will return all records to my screen by going to data and clear the criteria that'll put all 1000 records back on my screen now acquisition that would be those people who donated in 2013 again selecting everything but zero 2013 and for this I've got seven hundred and fifty five of those I need to add to this criteria those who did not give last year so I'm going to select only the zeros from last year and Niska now gives me a number of 137 so down in the corner here again 137 I would have 1,000 records so I've actually almost got my calculation done for me here 137 my acquisition is equal to 137 divided by the 1,000 current donors that I have in my database or 13.7% if you'd like to make a quick graph out of this I'll highlight these four cells I'll go to insert and I will choose a column chart and I can make it two-dimensional or three-dimensional effect and like 3d I might want to add data labels to this so I'll right mouse click and say add data labels pull these up just a little bit if you'd like to see the chart in its own window right mouse click the chart and find the move chart command I'm going to say new chart and you can name it whatever you like I can say retention acquisition abbreviation I'll click OK it'll create its own new tab for retention and acquisition next to my in my case the 1000 tab showing my chart at which point of course I can enhance this chart in many many ways which will not be covered in this video in front of you right now I've got a spreadsheet loaded up with donor transactions that have occurred over numerous years and the way you'll see I've got it laid out is I've got a transaction ID in the column a a donor ID in column B the last name first name column C and D the date donation date and the donation amounts in columns D and F now the important part of this is that you have some sort of a unique identifier that will break the ties between first and last names in other words two different John Smith's in your database you do not want it to consider them to be the same John Smith so a donor ID is used here in order to distinguish one from another you could use address or anything else that might make it unique between one donor and another in my case the donor ID so the most important columns that I've got listed here at columns b c d e and f this is the source of our data this will be the basis of our calculation and in the end we're going to see the retention and the acquisition rates for each year laid out side-by-side i've added a couple of extra columns here that will help us in the analysis part of this the first one is a concatenation of the name and ID so you'll see last name first name and the ID column is concatenated into this field here and i've highlighted the formula for how i did that in this text box so if i place my cursor here you'll see that it's the exact same formula you'll also notice that as i move from cell to cell the references to each row change when i'm on row four it's referring to the cells in row four and so on I've done the same thing to extract the Year because I want to do my analysis on a year-by-year basis so again I've done another formula over here in column n which extracts just a year portion of the date so in cell m2 I'm referring to the year value of cell e2 and I've indicated that formula down here in this text box as well because the next thing we're going to do is do a pivot table off of columns M and M and the way we do that is by going to the insert menu which I've already got highlighted go to the pivot table choose pivot table now assuming my cursor was somewhere in this table it's already selected the right block in fact I'll make an I'll state that again assuming my cursor was somewhere in this table anywhere in this table the pivot table will already select the correct block of information that I'll be using if your cursor is somewhere outside of that when I choose pivot table it really doesn't know what to pick as the source so I'll return to pivot table it's chosen the right block already I'll click the ok button it'll bring over the list of possible columns to be used in my in my pivot analysis what I'm going to be using is the concatenation of the name ID and the year I'll bring the name ID down to my row labels so by dragging the name ID down into the row labels area I'm creating a unique list of the combination of first name last name and donor ID the next thing I want to do is bring bring the year across the top which I've already calculated that in the previous tab and I'll place that there now I have my transactional years one from 2000 to 2012 and the last thing I'm going to do is bring a amount down donation amount will be tabulated in here now I'll close this window and I'll expand these columns out or set them to the same width there we go now I can see that Martha gave in 2008 she gave in to again in 2011 and 2012 and the grand total dollars are listed here going to the bottom of the list I can see the grand totals down here for all the contributions that were made in each of those years the next thing I need to do is to get my count for the number of donors I have in each year as my little service my denominator for my retention and acquisition rates well I do that by coming to the bottom of this table and I'll just label this row as count and I'll begin a formula now the formula will be count but I don't know what the starting point and the ending point exactly is actually I can see the ending point the ending point is going to be this B 1465 so let's go back and to the top and get the starting point well the starting point is B 5 in the year 2000 it's B 5 and that's the bottom is 1465 so let's start the formula again and say equals count B 5 : b 14 6 and 5 and there it is I have 86 donors in the year 2000 if I copy this formula across and I can do that pretty quickly by just highlighting these cells and then doing a ctrl R and it will calculate the number of donors I have in each year I'm gonna start referring to cells inside this pivot table up here and as I do that it's going to create some very ugly looking formulas so I'm going to have to convert this area to two values first and here's what I mean by that let's just say that I wanted to refer to this value up here in cell K 1463 well when I do that it creates this long get pivot data from here that gets to be a very confusing formula to it to try to read later on so here's what I'm going to do first I'm gonna go all the way back up to the corner of the spreadsheet up here I'm going to right mouse click and say copy I'm gonna then go back to that same spot and say right mouse click paste special and what I'll be choosing is values and what that does is it converts all my formulas and everything else into a straight value file so there's no formulas out there remaining when I choose okay I lose some of my formatting as well which at the moment doesn't matter to me I don't care if I have bold cells or not but now when I come back here and say refer back to this cell up here it's now just got a straight cell reference without the get pivot information it'll make your formulas much easier to read and much cleaner in the long run so now that we've completed that step now we can start building our retention and acquisition formulas over on the right hand side I'm going to begin with acquisition and I'm going to start to near 2001 because I'll be comparing 2001 to the previous year of 2000 and my formula begins like this equals and an open parenthesis is blank now I'm going to check to see is the cell in year 2000 blank and is the cell in 2001 not blank which is what I'm entering in right now so it's not s blank and that'll be cell for the cell for 2001 and it comes back with the word false now I would prefer to use zeros and ones so I'll just modify that formula to put an asterisk x 1 in here and if it's true it'll evaluate to a 1 if it's not it'll evaluate to 0 and I will be getting the numbers and I'll be looking for so really all I'm after is a whole lot of zeros and ones about 2000 let's say let's do this for each year so bring this out to the year 2012 and I'll copy this across and you'll see that in 2008 this was an acquisition this was considered an acquisition because if I come back into looking at Martha in 2008 she gay but she had not given the year before that's considered an acquisition of course we see she was reacquired that's a different conversation we can see she was reacquired in 2011 all right let's now we're gonna copy this all the way down for the rest of the cells when these are all completed I can then see what my acquisitions were for each particular year by feeling by looking for the number ones I want to add up those number ones and divide them by the number of donors we had in the previous year so for instance in the year 2000 we had 86 donors well let's see how many acquisitions we had I'll put a formula here that adds up cell Q 5 through Q 1465 so I have 10 new donors in the year 2001 if I want to get the rate on that I'm going to divide this by the number of donors I had in year 2000 that gives me a rate of about 11.6 percent I'll convert that to a percentage expand the decimal out one and I'll copy this across to 2012 next we'll move to the calculation for retention for that we'll come to the top and again we'll add some more columns in here starting from 2001 and this time the retention is calculated where the previous year had a donation and this year as a donation I'll add the retention title and I'll begin my formula equals and not is blank and I'll scroll all the way back here support wrote or sell 2000 was not playing and not this blank wrote cell 2001 and again I get that false reading again which I'll modify the formula and add the times one so that I get just zeros and ones let's fill out the data and so looking at Martha's record our very first person here we see that she was retained she would be retained in 2012 because she gave in the previous year and that bears out in the formula in my retention columns were the only one value for Martha is in 2012 all right let's go add our totals like we did before for acquisition I'll move to the bottom of the column for the first year I will sum up ad 5 through 80 1465 and I find I got 69 new donors in 2001 I will divide that by the number of donors that I had in the year 2000 and I find that my retention rate was 80% you so the calculation for retention and acquisition is now complete one thing I like to do is get it in a format that makes it'll mix it a little bit easier to read so I've copied this data from my from this tab here to another tab and I've already completed it so I've got my years 2001 through 2012 I've transferred over my acquisition and retention rates for each year and then I decided to put a graph in here as well so that I could see visually how the numbers compare from one year to the next and my retention rates were pretty good big drop-off in 2009 and 10 backup in 2011 again 2012 years not over and so my acquisition and retention rates for the current year are less meaningful in fact I may even choose to drop those off of the graph you