Attrition rate in period i = (donors who gave in period i and not i+1)/(donors who gave in period i)... but you will need some substantial querying to get these numbers.
Our organization uses an Access database (IM is based on Access) and use 2 different method for calculating attritition. For both, you will need to be reasonably well-versed with Access/SQL/query building.
First Method:
First, I stup queries of all donations in Year1, Year2, and Year3. I use the SQL "GROUP BY" clause to ensure that donors who made repeat donations are not double counted in that year. Then I include a calculation- even though I don't need it, such as Sum of donation amounts. I include a where clause to filter the query for year i (where i is either year1, year2, or year3).
In our case, I've setup a seperate database for reporting- donors and donations are included in the same table (ie: donor information is repeated throughout the table - this is OK since we use it exclusively for analysis). The SQL code to build the first query (in our case) is:
Our organization uses an Access database (IM is based on Access) and use 2 different method for calculating attritition. For both, you will need to be reasonably well-versed with Access/SQL/query building.
In the first, I setup queries of all donations in Year1, Year2, and Year3. I use the SQL "GROUP BY" clause to ensure that donors who made repeat donations are not double counted in that year. Then I include a calculation- even though I don't need it, such as Sum of donation amounts. I include a where clause to filter the query for year i (where i is either year1, year2, or year3). In our case, I've setup a seperate database for reporting- donors and donations are included in the same table (ie: donor information is repeated throughout the table
SELECT dw.DonorId, Sum(dw.Received) AS SumOfReceived
FROM dw
WHERE (((dw.PaymentDate) Like '*2007'))
GROUP BY dw.DonorId;
I repeat this query for 3 different years.
Next, I build a query based on these 3 queries. Instead of selecting tables such as donors or donations, I choose the 3 queries I setup. I link them all using the donor's ID number and specify that Access should select all records from year 2, and only records from year 1 that match (and likewise, select all records from year 2 and only records from year 3 that match). I like to setup an expression that returns "NEW" if the donor did not give in year 1, and "Attrited" if the donor did not give in year 3- although this may not be totally necessary. In our case, this query would look like:
SELECT [Level A: 2006 donations].DonorId AS 06id, [Level A: 2007 donations].DonorId, [Level A: 2008 donations].DonorId AS 08id, IIf(IsNull([06id]),"New") AS 07new, IIf(IsNull([08id]),"Attrited") AS 07attrite, 2007 AS [Year]
FROM ([Level A: 2006 donations] RIGHT JOIN [Level A: 2007 donations] ON [Level A: 2006 donations].DonorId=[Level A: 2007 donations].DonorId) LEFT JOIN [Level A: 2008 donations] ON [Level A: 2007 donations].DonorId=[Level A: 2008 donations].DonorId;
The last query I do is also based on a query (the query I just described) and counts the number of instances of "NEW", "ATTRITED", and the number of rows in the previous query. I "GROUP BY" year only because I have to (SQL queries that include aggregates, such as counts, must be grouped by something). In our case, it looks like this:
SELECT [Level A: 07 new and attrited].Year AS Expr1, Count([Level A: 07 new and attrited].[07new]) AS CountOf07new, Count([Level A: 07 new and attrited].[07attrite]) AS CountOf07attrite, Count([Level A: 07 new and attrited].DonorId) AS CountOfDonorId
FROM [Level A: 07 new and attrited]
GROUP BY [Level A: 07 new and attrited].Year;
The output of this last query is based on the second query I described, which is based on the first query I described. I end up with three numbers: total number of donors, number of new donors, and number of attrited donors. Attrition rate is just attrited donors/total donors.
I like this method for forcasting and projections, since I get a sense of who will donate next year.
Other method:
If you want to expand the time periods your including (ie: if your definition of attrition means not giving in the next 5 years, as opposed to the next year as I did) then the three queries you start with should not be year1 donations, year2 donations and year3 donations... rather is should be donations before year 2, donations at year 2, and donations after year 3.
Read up on subqueries and aggregate queries for Microsoft Access if this is unclear- it does require some fairly substantial data management skills.
Report (0) (0) |
earlier