Rock Lapsed Giver SQL with Campus Filtering

lapsed_giver_rock_by_campus.sql

-- Version 2.1.0.campus

-- 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 @CAMPUS_NAME VARCHAR(50) = 'Highlands';
DECLARE @ANNUAL_GIVING_THRESHOLD AS INTEGER = 3000;
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
DECLARE @CAMPUS_ID AS TINYINT;
--------------------------------------------------------------------------------------------------------------

SET @CAMPUS_ID = (SELECT [Id] FROM [Campus] WHERE [Name] LIKE @CAMPUS_NAME)

SELECT
p.[Id]
, p.[NickName]
, p.[LastName]
, p.[Email]
, c.[Name] AS [Campus]
, FORMAT(PastYear.[Amount], 'C', 'en-US') AS [Past 365 Days]
, FORMAT(SamePeriodLastYear.[Amount], 'C', 'en-US') AS [SamePeriodLastYear]
, FORMAT(RecentPeriod.[Amount], 'C', 'en-US') AS [RecentPeriod]
, 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
  GM.[PersonId]
  , Amount
  FROM (
    SELECT
    SUM(td.[Amount]) AS [Amount]
    , GM.[GroupId]
    FROM [FinancialTransaction] [t]
    INNER JOIN [FinancialTransactionDetail] [td]
       ON t.[Id] = td.[TransactionId]
    INNER JOIN [PersonAlias] PA
       ON t.[AuthorizedPersonAliasId] = PA.[Id]
    INNER JOIN [GroupMember] GM
      ON GM.[PersonId] = PA.[PersonId]
    INNER JOIN [Group] G
      ON GM.[GroupId] = G.[Id]
      AND G.GroupTypeId = 10
    WHERE
       t.[TransactionTypeValueId] != @EVENT_PAYMENT_TYPE
    AND
      t.[TransactionDateTime] > DATEADD(week, -1 * @RECENT_PERIOD_WEEKS, getdate())
    GROUP BY GM.[GroupId]
  ) [RecentPeriodFamilies]
  INNER JOIN [GroupMember] GM
    ON GM.[GroupId] = RecentPeriodFamilies.[GroupId]
) [RecentPeriodFamilyPersons] ON RecentPeriodFamilyPersons.[PersonId] = p.[Id]

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

WHERE PastYear.[Amount] IS NOT NULL
AND c.[Id] = @CAMPUS_ID
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 (RecentPeriodFamilyPersons.[Amount] < @MAXIMUM_RECENT_GIVING_DOLLARS OR RecentPeriodFamilyPersons.[Amount] IS NULL)
ORDER BY p.[LastName] ASC