Rock Lapsed Giver SQL

lapsed_giver_rock.sql

-- Version 2.3.0

-- This SQL searches your Rock database for "recent lapsed givers". The definition of a recent lapsed giver is 
-- someone who gives $3,000 / yr, who has given less than $100 in the past 8 weeks, __who was giving normally this same 8-week period last year__.
-- That last clause is key, because many people give a lot, but can have 8 week non-giving periods. Maybe they take the summer off. Maybe the winter off.
-- Maybe they give a large amount in December, then don't start again til March. Without comparing their giving to the same 8-week period last year
-- it's hard to know.

-- You can tweak the varibles for your church below. Just change the 3000, 100, and 8

-- To turn this into a DataToolKit Plug SQL statement (see Rock Shop, you can use this plugin to turn the People you find on this list into a Dataview),
-- delete all the SELECT fields at the top except for p.[Id] (p.Nickname, p.Email, etc.)

-- For questions email [email protected]
-- Slides from RX2019: https://www.dropbox.com/s/2wucaiazqoenvlo/joshcrews_rx2019_lapsed_givers.pdf?dl=0

--------------------------------------------------------------------------------------------------------------
DECLARE @ANNUAL_GIVING_THRESHOLD AS INTEGER = 3000; -- The giving threshold
DECLARE @MAXIMUM_RECENT_GIVING_DOLLARS AS INTEGER = 100; 
DECLARE @RECENT_PERIOD_WEEKS AS TINYINT = 8;
DECLARE @EVENT_PAYMENT_TYPE AS INT = 54; --Event Payment Defined Value, from Defined Types > Transaction Type
--------------------------------------------------------------------------------------------------------------

SELECT
p.[Id]
, p.[NickName]
, p.[LastName]
, p.[Email]
, c.[Name] AS [Campus]
, FORMAT(PastYear.[Amount], 'C', 'en-US') AS [Past 365 Days]
, FORMAT(RecentPeriod.[Amount], 'C', 'en-US') AS [RecentPeriod]
, FORMAT(SamePeriodLastYear.[Amount], 'C', 'en-US') AS [SamePeriodLastYear]

, FinancialScheduledTransactions.[Count] AS [Active Scheduled Transactions]
FROM [Person] p

LEFT JOIN (
  SELECT
  SUM(td.[Amount]) AS [Amount]
  , PA.[PersonId]
  FROM [FinancialTransaction] t
  INNER JOIN [FinancialTransactionDetail] td
    ON t.[Id] = td.[TransactionId]
  INNER JOIN [PersonAlias] PA
    ON t.[AuthorizedPersonAliasId] = PA.[Id]
  WHERE
    t.[TransactionTypeValueId] != @EVENT_PAYMENT_TYPE
  AND
    t.[TransactionDateTime] < DATEADD(year, -1, getdate())
  AND
    t.[TransactionDateTime] > DATEADD(week, -1 * @RECENT_PERIOD_WEEKS, DATEADD(year, -1, getdate()))
  GROUP BY PA.[PersonId]
) [SamePeriodLastYear] ON SamePeriodLastYear.[PersonId] = p.[Id]

LEFT JOIN (
  SELECT
  SUM(td.[Amount]) AS [Amount]
  , PA.[PersonId]
  FROM [FinancialTransaction] t
  INNER JOIN [FinancialTransactionDetail] td
    ON t.[Id] = td.[TransactionId]
  INNER JOIN [PersonAlias] PA
    ON t.[AuthorizedPersonAliasId] = PA.[Id]
  WHERE
    t.[TransactionTypeValueId] != @EVENT_PAYMENT_TYPE
  AND
    t.[TransactionDateTime] > DATEADD(week, -1 * @RECENT_PERIOD_WEEKS, DATEADD(year, -1, getdate()))
  GROUP BY PA.[PersonId]
) [PastYear] ON PastYear.[PersonId] = p.[Id]

LEFT JOIN (
  SELECT
  SUM(td.[Amount]) AS [Amount]
  , PA.[PersonId]
  FROM [FinancialTransaction] t
  INNER JOIN [FinancialTransactionDetail] td
     ON t.[Id] = td.[TransactionId]
  INNER JOIN [PersonAlias] PA
     ON t.[AuthorizedPersonAliasId] = PA.[Id]
  WHERE
     t.[TransactionTypeValueId] != @EVENT_PAYMENT_TYPE
  AND
    t.[TransactionDateTime] > DATEADD(week, -1 * @RECENT_PERIOD_WEEKS, getdate())
  GROUP BY PA.[PersonId]
) [RecentPeriod] ON RecentPeriod.[PersonId] = p.[Id]

LEFT JOIN (
  SELECT
  COUNT(fst.[Id]) AS [Count]
  , PA.[PersonId]
  FROM FinancialScheduledTransaction [fst]
  INNER JOIN PersonAlias [PA]
     ON fst.[AuthorizedPersonAliasId] = PA.[Id]
  WHERE fst.[IsActive] = 1
  GROUP BY PA.[PersonId]
) [FinancialScheduledTransactions] ON FinancialScheduledTransactions.[PersonId] = p.[Id]

-- What if one person stopped giving but their spouse has given the minimum in the past 8 weeks? Let's exclude those too
LEFT JOIN (
  SELECT
  SUM(td.[Amount]) AS [Amount],
  p.GivingGroupId
  FROM FinancialTransaction [t]
  INNER JOIN FinancialTransactionDetail [td]
     ON t.[Id] = td.[TransactionId]
  INNER JOIN [PersonAlias] PA
     ON t.[AuthorizedPersonAliasId] = PA.[Id]
  INNER JOIN [Person] p
     ON PA.PersonId = p.Id
  WHERE
     t.[TransactionTypeValueId] != @EVENT_PAYMENT_TYPE
  AND
    t.[TransactionDateTime] > DATEADD(week, -1 * cast(@RECENT_PERIOD_WEEKS as integer), getdate())
  GROUP BY p.GivingGroupId
) [RecentPeriodGivingGroups] ON RecentPeriodGivingGroups.[GivingGroupId] = p.[GivingGroupId]

LEFT JOIN [Campus] c ON c.[Id] = p.[PrimaryCampusId]

WHERE PastYear.[Amount] IS NOT NULL
AND PastYear.[Amount] > @ANNUAL_GIVING_THRESHOLD
AND (RecentPeriod.[Amount] < @MAXIMUM_RECENT_GIVING_DOLLARS OR RecentPeriod.[Amount] IS NULL)
AND (SamePeriodLastYear.[Amount] / PastYear.[Amount]) > ((@RECENT_PERIOD_WEEKS * 1.0) / 60) -- and last year this period they gave a normal amount for them
AND FinancialScheduledTransactions.[Count] IS NULL
AND (RecentPeriodGivingGroups.[Amount] < @MAXIMUM_RECENT_GIVING_DOLLARS OR RecentPeriodGivingGroups.[Amount] IS NULL)
ORDER BY p.[LastName] ASC