Coverage Ratio

Good morning!

I've been asked to get the Coverage Ratio for the past four fiscal years and I'm finding myself a bit stuck. I had never heard of this metric before but here is how it was described to me:

**Coverage ratio = (New + Recovered)/Lapsed

• New = First gift ever
• Recovered = Last gift before previous fiscal year
• Lapsed = Donors who gave by this time in previous fiscal year but not yet this year (i.e. YTD LYBUNTs)

I've gotten a few pieces, but as I've been muddling through using Analytics and SQL, I wondered if there was a better way. Our NRR values are not entirely reliable (it's on the To-Do list), so I've been using Initiation dates, Expiration dates, not really contribution dates since we our memberships can be achieved by cumulative giving, not just a one time membership gift. 

Has anyone made something to calculate Coverage Ratio?

Jenny O'Neill-Englund