Relative Year SQL

I wrote this SQL code recently and wanted to share it here (in a modified form to simplify). This isn’t a “typical” SQL SELECT statement, because of how each row is checking the rest of the table relative to its own fiscal year value. (If you hover over the code, a menu will appear at the top with a button toward the right that allows you to open it in another window for better viewing.)

So in the Donations table, there is one row per donor per gift. There can be multiple gifts in a fiscal year, but as soon as the first gift is made, the donor can then be given a FY_DonorType using a CASE statement. If the donor also gave last year, then the donor is “Retained”. If they have given in the past, they’re “Reactivated”. I separated out those reactivated in the last 2-5 years and those that hadn’t given for longer, since once a donor had not given for 5 years, they are considered “Long Lapsed” and much harder to reactivate. If the donor had never appeared in the table before, he or she is a New donor and marked as “Acquired”. We are not looking at how many people gave last year but not this year (“Lost”), but only the breakdown of who this year’s donors are.

Since the code groups by fiscal year and donor ID, and the case statements include selects that look at previous years relative to each gift’s fiscal year, you can look at how the “current year” donors break down every year. Each year is relative to the previous years.

This allows us to make visualizations that give some insight into how the fundraising team performed each year. Some years, the fundraising organization was particularly good at reactivating lost donors. Some years they retained donors from the previous fiscal year at a high rate, but the ones that hadn’t given for more than a year must not have been targeted well and fell off more than usual. This is likely a result of how often they solicited each group, and what type of solicitations were used. (I purposely hid the Y-axis and other info since I’m using this for illustrative purposes and not trying to give away details related to the fundraiser.)

donor_retention_illustration

What database tables do you have that could be analyzed in this way?

4 Comments

  1. Ellie Kesselman
    Jun 9, 2015

    This is great! It might be good for Socrata’s political campaign donation data. Socrata can be used to query by donor and recipient, and various other criteria, using the web app but I’m sure it would be easier to work with the raw data directly.

    Do you know Thomas Levine? He likes playing with relational data on Socrata. I think he knows SQL :) I will share this with him.

    I was just reading about SAP HANA. It is supposed to be good for I/O and analyzing columnar data in the same way that Oracle SQL is good for rows of data. It is very expensive and expensive to maintain though. Were you familiar with it at all in your previous life doing SQL, Renee?

    • Renee
      Jun 10, 2015

      No, I don’t know Thomas, but thanks for introducing us on twitter and sharing the post!

      I’m not familiar with SAP HANA… also, I don’t consider SQL to be my previous life, it’s very much a part of my current life :) Thanks for the comment & info!

  2. Felipe Hoffa
    Mar 22, 2016

    Hey Renee! Have you tried BigQuery? Would love to see your becoming a data scientist queries there :).

    Quick start: https://www.reddit.com/r/bigquery/comments/3dg9le/analyzing_50_billion_wikipedia_pageviews_in_5/

    (I’m looking into loading the donations dataset into BigQuery too)