This recipe relies heavily on SQL queries and Lava Shortcodes. You can read more about my love story with Lava Shortcodes here.

But don't you worry if you are new to RockRMS, I'll provide all of the code and understanding below.

Thank You Process for First Time / New Scheduled Gifts from Harvest Bible Chapel Pittsburgh North on Vimeo.

Table of Contents

Click on the topics below to jump to that section:

• Intro
• Prerequisites
• Documentation
• Use Cases
• Let's Dive In!
  • PERSON ATTRIBUTE SETUP (First Time and New Scheduled Gift)
  • REPORT SETUP (First Time Gift)
  • REPORT SETUP (New Scheduled Gift)
  • SETTING THE "THANKED" DATE
  • MERGE DOCUMENT AND EMAIL TEMPLATE SETUP (First Time Gift & New Scheduled Gift)
  • LAVA SHORTCODES
  • EMAIL TEMPLATE CODE
• Final Thoughts

INTRO

Today do you manually create thank you emails or physical letters for First Time or New Scheduled Givers by hand? Wouldn’t you like a page that tracks these new givers, displays whether you’ve already thanked them before, and gives you a quick way to thank them in bulk? Read on to find out how!

PREREQUISITES

In order to set up these report pages in order to quickly thank these new givers you’ll need to use the following:

• New Person Attributes (Date to store when the gratitude was given)
• Page Parameter block
• Dynamic Data block
• SQL (everything provided below)
• Merge Document Template (Word Doc, example attached to this recipe)
• Email Communications Template (Wizard based template, code provided below)
• Lava Shortcodes with SQL queries (refer to my love story on Lava Shortcodes here)

DOCUMENTATION

Before we dive into it, let’s familiarize ourselves with some of the prerequisite items and the documentation on their use:

• Person Attributes: https://community.rockrms.com/documentation/bookcontent/5/216#personattributes
  (video) https://community.rockrms.com/rocku/individuals-in-rock/person-attributes
• Page Parameter Filter block: https://community.rockrms.com/documentation/bookcontent/6/220#pageparameterfilterblock
• Dynamic Data block: https://community.rockrms.com/documentation/bookcontent/6/220#dynamicdatablock
• SQL Command (great for testing results prior to implementing): https://community.rockrms.com/documentation/bookcontent/6/220#sqlcommand
• Merge Documents: https://community.rockrms.com/documentation/bookcontent/9/224#mergedocuments
  (video) https://community.rockrms.com/rocku/individuals-in-rock/merge-documents
• Email Templates: https://community.rockrms.com/documentation/bookcontent/34/230
  (video) https://community.rockrms.com/rocku/communication/communication-templates
• Lava Shortcodes RockU video overview: https://community.rockrms.com/rocku/lava/lava-shortcodes
• Here’s the official Rock documentation on adding lava shortcodes: https://community.rockrms.com/Rock/BookContent/33#addinglavashortcodesinrock
• Authoring Shortcodes: https://community.rockrms.com/Rock/BookContent/33#authoringshortcodes
• CMS Shortcodes: https://community.rockrms.com/Rock/BookContent/14#lavashortcodes

USE CASES

This recipe can obviously be utilized beyond the scope of First Time and New Scheduled givers. Any time you have a repeated need for thanking a set of individuals, you can set up a new person date attribute, modify these reports, and communication templates for your needs. Some of those possibilities are:

• Church volunteers or staff that have served 1, 5, 10, 15+ years
• Top givers above a $$,$$$ amount

LET'S DIVE IN!

PERSON ATTRIBUTE SETUP (First Time and New Scheduled Gift)

1. Go to Admin Tools - General Settings - Person Attributes
2. Create 2 new Person attributes
   1. Name: First Single Gift - Thanked
      1. Description: Used to set a date when the giver has been thanked via Physical Letter or Email in order to exclude them if they happen to show in this report again, we don't want to send another first time gift letter if true.
      2. Category: Finance Internal
      3. Key: FirstSingleGiftThanked
      4. Field Type: Date
   2. Name: New Scheduled Gift - Thanked
      1. Description: Used to set a date when the new scheduled giver has been thanked via Physical Letter or Email in order to exclude them if they happen to show in this report again, we don't want to send another first time gift letter if true.
      2. Category: Finance Internal
      3. Key: NewScheduledGiftThanked
      4. Field Type: Date
   3. Take note of each new attribute's Id as you’ll need these for the SQL queries later.
      
      

REPORT SETUP (First Time Gift)

1. Go to Admin Tools - CMS Configuration - Pages
2. Create a new child-page under the Finance tree
   Pro Tip! This is a good time to create a page section called "Reports" or "Stewardship" to put these report pages under.
3. Name the newly created child page First Time Givers with a suggested Icon CSS Class of fa fa-medal
4. Add 4 blocks onto the new page: 1 Page Parameter and 3 Dynamic Data blocks keeping the Page Parameter block on the top of the block list.
5. Edit the Page Parameter block settings and add 2 new DATE field attributes.
   1. Block Title with a value of Date Range Selection, you can choose to turn off the Block Title
   2. Start Date with a key of StartDate and field type of DATE
   3. End Date with a key of EndDate and field type of DATE
   4. Optional: Top tighten up the size of the Page Parameter Filter block In the Advanced Settings Pre-HTML of this block enter:
      <div class="row">
      <div class="col-sm-6">
      In the Advanced Settings Post-HTML of this block enter:
      </div>
      </div> <!-- end of row -->
6. Create 3 Dynamic Data Blocks:
   1. Dynamic Data block #1: Name this block "New First Time Givers - Physical Letters for Merge Template"
      1. Parameters Field: @StartDate=,@EndDate=
      2. Columns (Hide): GiverId, Id
      3. Selection URL:
         

      ~/Person/{Id}/Contributions

      1. Settings: Check the following options Person Report, Excel Export, Merge Template, Show Grid Filter
      2. Query: 

         -- Mad props to Josh Crews @ Simple Donation for the initial query
         -- Find 1st/2nd Time Givers, Grouped by Household
         -- You can find 3rd time givers by changing RowNumber = 2 to RowNumber = 3
         
         -- Reason we are Grouped by Household is to avoid identifying the spouse on their second gift, when it's the households 20th gift.
         -- Query in english
         -- > Searching the past 18 months, Get me
         -- > the 1st/2nd gift made per family. Pull the 1st adult male from the household,
         -- > or any adult female if no male. Only show
         -- > me the first/second gifts that have occurred in the past month
         -- > You can change t.GivingGroupId to t.AuthorizedFamilyId if you want it to work based on families rather than giving groups - effectively disregarding whether or not they have combined giving turned on
         
         DECLARE @AdultRoleId int = ( SELECT TOP 1 CAST([Id] as varchar)  FROM [GroupTypeRole] WHERE [Guid] = '2639f9a5-2aae-4e48-a8c3-4ffe86681e42' )
         
         SELECT
             Giver.Id [GiverId],
             Person.Id [Id],
             Giver.FirstName,
             Giver.LastName,
             Giver.Email,
             CONCAT(Address.Street1,' ',Address.Street2,' ',Address.City,', ',Address.State,' ',Address.PostalCode) [FamilyAddress],
             Phones.NumberFormatted [Phone],
             FORMAT(b.Amount,'C') [GiftAmount],
             b.PublicName [AccountName],
             CAST (b.TransactionDateTime AS DATE) [TransactionDate],
             av.Value [Thanked Date]
         FROM (
           SELECT
           a.*,
           Head.Id [HeadPersonId]
             FROM (
             SELECT
             t.*,
             fa.PublicName,
             ROW_NUMBER() OVER (PARTITION BY t.AuthorizedFamilyId ORDER BY t.TransactionDateTime) AS RowNumber
               FROM AnalyticsFactFinancialTransaction t
             INNER JOIN FinancialAccount fa
               ON fa.Id = t.AccountId
             -- remove children
               INNER JOIN PersonAlias pa
                 ON pa.Id = t.AuthorizedPersonAliasId
               INNER JOIN GroupMember
                 ON GroupMember.PersonId = pa.PersonId AND GroupMember.GroupRoleId = @AdultRoleId
               INNER JOIN [Group] ON [Group].Id = GroupMember.GroupId AND [Group].GroupTypeId = 10
             WHERE t.TransactionDateTime > DATEADD(month, -18, GETDATE())
                 AND t.EntityTypeName != 'Registration'
                 AND t.ScheduleType = 'Non-Scheduled'
                 AND fa.IsTaxDeductible = 'True' --ensures that these are only Tax-Deductible gifts
                 --AND t.IsFirstTransactionOfType = 1 -- 1 is True 0 is False
                 --AND t.IsScheduled = 'False'
                 --AND t.TransactionFrequency = ''
                 --AND fst.IsActive = 0
                 -- AND fa.CampusId = 4 -- used for manually restricting to a specific campus
                 -- AND fa.ParentAccountId = 5 -- used for manually restricting to a specific top-level account
           ) [a]
           OUTER APPLY (
               SELECT TOP 1 p.[Id]
                 FROM [Person] p
                 INNER JOIN [GroupMember] gm ON gm.[PersonId] = p.[Id]
               WHERE
                 gm.GroupRoleId = @AdultRoleId
                 AND gm.[GroupId] = a.AuthorizedFamilyId
               ORDER BY p.[Gender]
           ) [Head]
           WHERE a.RowNumber = 1 --(first gift ever, set to 2 for 2nd, set to 3 for 3rd)
           AND a.TransactionDateTime >= CAST (@StartDate AS DATE)
           AND a.TransactionDateTime < DATEADD (day, 1,CAST(CONVERT(datetimeoffset,@EndDate,127) AS DATE))
         ) b
         JOIN Person
           ON Person.Id = b.HeadPersonId
         JOIN PersonAlias [GiverAlias]
           ON b.AuthorizedPersonAliasId = GiverAlias.Id
         JOIN Person [Giver]
           ON Giver.Id = GiverAlias.PersonId
         LEFT JOIN AttributeValue av
           ON av.EntityId = Person.Id
           AND av.AttributeId = 6958 --First Time Gift Thankyou Letter Sent Date (!IMPORTANT! THIS ID WILL NEED TO BE UPDATED IN YOUR ROCK INSTANCE BASED ON THE PERSON ATTRIBUTE CREATED)
         OUTER APPLY (
             SELECT TOP 1
             *
             FROM PhoneNumber
             WHERE PersonId = Giver.Id
             ORDER BY NumberTypeValueId
         ) [Phones]
         OUTER APPLY (
           SELECT TOP 1
           loc.*
           FROM GroupLocation gl
           LEFT JOIN [Location] loc
           ON loc.Id = gl.LocationId
           WHERE gl.GroupId = Person.PrimaryFamilyId
           AND loc.IsActive = 'True'
           AND gl.IsMailingLocation = 1
         ) [Address]
         WHERE av.Value is null -- ignores potential First Time Givers that already have had a letter sent to them
             AND Address.Street1 is not null
             --AND Giver.RecordStatusValueId = 3 --'Active'
         ORDER BY b.TransactionDateTime desc -- Person.LastName
         

      3. Advanced Settings (Pre-HTML):
         

         <h5>New First Time Givers - Letters for Merge Template</h5>
         <p>Check the list below to ensure they have not already received an appreciation letter.</p>

   2. Dynamic Data Block #2: Name this block "New First Time Givers - Emails (no Address available)"
      1. Parameter Field: @StartDate=,@EndDate=
      2. Columns (Hide): GiverId, Id
      3. Selection URL:
         

      ~/Person/{Id}/Contributions

      1. Settings: Check the following options Person Report, Communicate, Excel Export, Show Grid Filter
      2. Query: 

         -- Mad props to Josh Crews @ Simple Donation for the initial query
         -- Find 1st/2nd Time Givers, Grouped by Household
         -- You can find 3rd time givers by changing RowNumber = 2 to RowNumber = 3
         
         -- Reason we are Grouped by Household is to avoid identifying the spouse on their second gift, when it's the households 20th gift.
         -- Query in english
         -- > Searching the past 18 months, Get me
         -- > the 1st/2nd gift made per family. Pull the 1st adult male from the household,
         -- > or any adult female if no male. Only show
         -- > me the first/second gifts that have occurred in the past month
         -- > You can change t.GivingGroupId to t.AuthorizedFamilyId if you want it to work based on families rather than giving groups - effectively disregarding whether or not they have combined giving turned on
         
         DECLARE @AdultRoleId int = ( SELECT TOP 1 CAST([Id] as varchar)  FROM [GroupTypeRole] WHERE [Guid] = '2639f9a5-2aae-4e48-a8c3-4ffe86681e42' )
         
         SELECT
             Giver.Id [GiverId],
             Person.Id [Id],
             Giver.FirstName,
             Giver.LastName,
             Giver.Email,
             CONCAT(Address.Street1,' ',Address.Street2,' ',Address.City,', ',Address.State,' ',Address.PostalCode) [FamilyAddress],
             Phones.NumberFormatted [Phone],
             FORMAT(b.Amount,'C') [GiftAmount],
             b.PublicName [AccountName],
             CAST (b.TransactionDateTime AS DATE) [TransactionDate],
             av.Value [Thanked Date]
           FROM (
           SELECT
           a.*,
           Head.Id [HeadPersonId]
             FROM (
             SELECT
             t.*,
             fa.PublicName,
             ROW_NUMBER() OVER (PARTITION BY t.AuthorizedFamilyId ORDER BY t.TransactionDateTime) AS RowNumber
               FROM AnalyticsFactFinancialTransaction t
             INNER JOIN FinancialAccount fa
               ON fa.Id = t.AccountId
             -- remove children
               INNER JOIN PersonAlias pa
                 ON pa.Id = t.AuthorizedPersonAliasId
               INNER JOIN GroupMember
                 ON GroupMember.PersonId = pa.PersonId AND GroupMember.GroupRoleId = @AdultRoleId
               INNER JOIN [Group] ON [Group].Id = GroupMember.GroupId AND [Group].GroupTypeId = 10
             WHERE t.TransactionDateTime > DATEADD(month, -18, GETDATE())
                 AND t.EntityTypeName != 'Registration'
                 AND fa.IsTaxDeductible = 'True' --ensures that these are only Tax-Deductible gifts
                 AND t.ScheduleType = 'Non-Scheduled'
                 --AND t.IsFirstTransactionOfType = 1 -- 1 is True 0 is False
                 --AND t.IsScheduled = 'False'
                 --AND t.TransactionFrequency = ''
                 --AND fst.IsActive = 0
           ) [a]
           OUTER APPLY (
               SELECT TOP 1 p.[Id]
                 FROM [Person] p
                 INNER JOIN [GroupMember] gm ON gm.[PersonId] = p.[Id]
               WHERE
                 gm.GroupRoleId = @AdultRoleId
                 AND gm.[GroupId] = a.AuthorizedFamilyId
               ORDER BY p.[Gender]
           ) [Head]
           WHERE a.RowNumber = 1 --(first gift ever, set to 2 for 2nd, set to 3 for 3rd)
           AND a.TransactionDateTime >= CAST (@StartDate AS DATE)
           AND a.TransactionDateTime < DATEADD (day, 1,CAST(CONVERT(datetimeoffset,@EndDate,127) AS DATE))
         ) b
         JOIN Person
           ON Person.Id = b.HeadPersonId
         JOIN PersonAlias [GiverAlias]
           ON b.AuthorizedPersonAliasId = GiverAlias.Id
         JOIN Person [Giver]
           ON Giver.Id = GiverAlias.PersonId
         LEFT JOIN AttributeValue av
           ON av.EntityId = Person.Id
           AND av.AttributeId = 6958 --First Time Gift Thankyou Letter Sent Date (!IMPORTANT! - THIS ID WILL NEED TO BE UPDATED IN YOUR ROCK INSTANCE BASED ON THE PERSON ATTRIBUTE CREATED)
         OUTER APPLY (
             SELECT TOP 1
             *
             FROM PhoneNumber
             WHERE PersonId = Giver.Id
             ORDER BY NumberTypeValueId
         ) [Phones]
         OUTER APPLY (
           SELECT TOP 1
           loc.*
           FROM GroupLocation gl
           LEFT JOIN [Location] loc
           ON loc.Id = gl.LocationId
           WHERE gl.GroupId = Person.PrimaryFamilyId
           --AND loc.IsActive = 'True'
           --AND gl.IsMailingLocation = 1
         ) [Address]
         WHERE av.Value is null -- ignores potential First Time Givers that already have had a letter sent to them
             AND Address.Street1 is null -- finds email only givers
             --AND Giver.RecordStatusValueId = 3 --'Active'
         ORDER BY b.TransactionDateTime desc -- Person.LastName

         
         
      3. Advanced Settings (Pre-HTML): 

         <h5>New First Time Givers - Emails (no Address available)</h5>
         <p>Check the list below to ensure they have not already received an appreciation letter or email below.</p>

   3. Dynamic Data block #3: Name this block "Letters/Emails Previously Sent to First Time Givers"
      1. Parameters Field: @StartDate=,@EndDate=
      2. Columns (Hide): GiverId, Id
      3. Selection URL:
         

      ~/Person/{Id}/Contributions
      

      1. Settings: Check the following options Person Report, Communicate, Excel Export, Merge Template, Show Grid Filter
      2. Query:
         

         -- Mad props to Josh Crews @ Simple Donation for the initial query
         -- Find 1st/2nd Time Givers, Grouped by Household
         -- You can find 3rd time givers by changing RowNumber = 2 to RowNumber = 3
         
         -- Reason we are Grouped by Household is to avoid identifying the spouse on their second gift, when it's the households 20th gift.
         -- Query in english
         -- > Searching the past 18 months, Get me
         -- > the 1st/2nd gift made per family. Pull the 1st adult male from the household,
         -- > or any adult female if no male. Only show
         -- > me the first/second gifts that have occurred in the past month
         -- > You can change t.GivingGroupId to t.AuthorizedFamilyId if you want it to work based on families rather than giving groups - effectively disregarding whether or not they have combined giving turned on
         
         DECLARE @AdultRoleId int = ( SELECT TOP 1 CAST([Id] as varchar)  FROM [GroupTypeRole] WHERE [Guid] = '2639f9a5-2aae-4e48-a8c3-4ffe86681e42' )
         
         SELECT
             Giver.Id [GiverId],
             Person.Id [Id],
             Giver.FirstName,
             Giver.LastName,
             Giver.Email,
             CONCAT(Address.Street1,' ',Address.Street2,' ',Address.City,', ',Address.State,' ',Address.PostalCode) [FamilyAddress],
             Phones.NumberFormatted [Phone],
             FORMAT(b.Amount,'C') [GiftAmount],
             b.PublicName [AccountName],
             CAST (b.TransactionDateTime AS DATE) [TransactionDate],
             av.Value [Thanked Date]
           FROM (
           SELECT
           a.*,
           Head.Id [HeadPersonId]
             FROM (
             SELECT
             t.*,
             fa.PublicName,
             ROW_NUMBER() OVER (PARTITION BY t.AuthorizedFamilyId ORDER BY t.TransactionDateTime) AS RowNumber
               FROM AnalyticsFactFinancialTransaction t
             INNER JOIN FinancialAccount fa
               ON fa.Id = t.AccountId
             -- remove children
               INNER JOIN PersonAlias pa
                 ON pa.Id = t.AuthorizedPersonAliasId
               INNER JOIN GroupMember
                 ON GroupMember.PersonId = pa.PersonId AND GroupMember.GroupRoleId = @AdultRoleId
               INNER JOIN [Group] ON [Group].Id = GroupMember.GroupId AND [Group].GroupTypeId = 10
             WHERE t.TransactionDateTime > DATEADD(month, -18, GETDATE())
                 AND t.EntityTypeName != 'Registration'
                 AND t.ScheduleType = 'Non-Scheduled'
                 AND fa.IsTaxDeductible = 'True' --ensures that these are only Tax-Deductible gifts
                 --AND t.IsFirstTransactionOfType = 1 -- 1 is True 0 is False
                 --AND t.IsScheduled = 'False'
                 --AND t.TransactionFrequency = ''
                 --AND fst.IsActive = 0
                 -- AND fa.CampusId = 4 -- used for manually restricting to a specific campus
                 -- AND fa.ParentAccountId = 5 -- used for manually restricting to a specific top-level account
           ) [a]
           OUTER APPLY (
               SELECT TOP 1 p.[Id]
                 FROM [Person] p
                 INNER JOIN [GroupMember] gm ON gm.[PersonId] = p.[Id]
               WHERE
                 gm.GroupRoleId = @AdultRoleId
                 AND gm.[GroupId] = a.AuthorizedFamilyId
               ORDER BY p.[Gender]
           ) [Head]
           WHERE a.RowNumber = 1 --(set to 2 for 2nd gift, set to 3 for 3rd gift)
           AND a.TransactionDateTime >= CAST (@StartDate AS DATE)
           AND a.TransactionDateTime < DATEADD (day, 1,CAST(CONVERT(datetimeoffset,@EndDate,127) AS DATE))
         ) b
         JOIN Person
           ON Person.Id = b.HeadPersonId
         JOIN PersonAlias [GiverAlias]
           ON b.AuthorizedPersonAliasId = GiverAlias.Id
         JOIN Person [Giver]
           ON Giver.Id = GiverAlias.PersonId
         LEFT JOIN AttributeValue av
           ON av.EntityId = Person.Id
           AND av.AttributeId = 6958 --First Time Gift Thankyou Letter Sent Date (!IMPORTANT! - THIS ID WILL NEED TO BE UPDATED IN YOUR ROCK INSTANCE BASED ON THE PERSON ATTRIBUTE CREATED)
         OUTER APPLY (
             SELECT TOP 1
             *
             FROM PhoneNumber
             WHERE PersonId = Giver.Id
             ORDER BY NumberTypeValueId
         ) [Phones]
         OUTER APPLY (
           SELECT TOP 1
           loc.*
           FROM GroupLocation gl
           LEFT JOIN [Location] loc
           ON loc.Id = gl.LocationId
           WHERE gl.GroupId = Person.PrimaryFamilyId
           AND loc.IsActive = 'True'
           AND gl.IsMailingLocation = 1
         ) [Address]
         WHERE av.Value is not null -- ignores potential First Time Givers that already have had a letter sent to them
            --AND Giver.RecordStatusValueId = 3 --'Active'
         ORDER BY b.TransactionDateTime desc -- Person.LastName
         

         
         
      3. Advanced Settings (Pre-HTML):
         

         <h5>Letters/Emails Previously Sent to First Time Givers</h5>

         
         

REPORT SETUP (New Scheduled Gift)

1. Go to Admin Tools - CMS Configuration - Pages
2. Create a new child-page under the Finance tree
   Pro Tip! This is a good time to create a page section called "Reports" or "Stewardship" to put these report pages under.
3. Name the newly created child page New Scheduled Giifts with a suggested Icon CSS Class of fa fa-recycle
4. Add 4 blocks onto the new page: 1 Page Parameter and 3 Dynamic Data blocks keeping the Page Parameter block on the top of the block list.
5. Edit the Page Parameter block settings and add 2 new DATE field attributes.
   1. Block Title with a value of Date Range Selection, you can choose to turn off the Block Title
   2. Start Date with a key of StartDate and field type of DATE
   3. End Date with a key of EndDate and field type of DATE
   4. Optional: Top tighten up the size of the Page Parameter Filter block In the Advanced Settings Pre-HTML of this block enter:
      <div class="row">
      <div class="col-sm-6">
      In the Advanced Settings Post-HTML of this block enter:
      </div>
      </div> <!-- end of row -->
6. Create 3 Dynamic Data Blocks:
   1. Dynamic Data block #1: Name this block "New Scheduled Gifts - Physical Letters for Merge Template"
      1. Parameters Field: @StartDate=,@EndDate=
      2. Columns (Hide): GiverId, Id
      3. Selection URL:
         

      ~/Person/{Id}/Contributions

      1. Settings: Check the following options Person Report, Excel Export, Merge Template, Show Grid Filter
      2. Query: 

         -- Mad props to Josh Crews @ Simple Donation for the initial query
         -- Find 1st/2nd Schedules, Grouped by Household
         -- You can find 3rd Schedule by changing RowNumber = 2 to RowNumber = 3
         
         -- Reason we are Grouped by Household is to avoid identifying the spouse on their second gift, when it's the households 20th gift.
         -- Query in english
         -- > Searching the past 18 months, Get me
         -- > the 1st/2nd schedule made per family. Pull the 1st adult male from the household,
         -- > or any adult female if no male. Only show
         -- > me the first/second gifts that have occurred in the past month
         -- > You can change t.GivingGroupId to t.AuthorizedFamilyId if you want it to work based on families rather than giving groups - effectively disregarding whether or not they have combined giving turned on
         
         DECLARE @AdultRoleId int = ( SELECT TOP 1 CAST([Id] as varchar)  FROM [GroupTypeRole] WHERE [Guid] = '2639f9a5-2aae-4e48-a8c3-4ffe86681e42' )
         
         SELECT
             Giver.Id [GiverId],
             Person.Id [Id],
             Giver.FirstName,
             Giver.LastName,
             Giver.Email,
             CONCAT(Address.Street1,' ',Address.Street2,' ',Address.City,', ',Address.State,' ',Address.PostalCode) [FamilyAddress],
             Phones.NumberFormatted [Phone],
             FORMAT(b.Amount,'C') [GiftAmount],
             CAST (b.Start AS DATE) [StartDate],
             CAST (b.Next AS DATE) [NextDate],
             b.IsActive,
             b.PublicName [AccountName],
             CAST (b.TransactionDateTime AS DATE) [TransactionDate],
             av.Value [Thanked Date]
         FROM (
           SELECT
           a.*,
           Head.Id [HeadPersonId]
             FROM (
             SELECT
             t.*,
             fa.PublicName,
             fst.StartDate [Start],
             fst.NextPaymentDate [Next],
             fst.IsActive [IsActive],
             ROW_NUMBER() OVER (PARTITION BY t.AuthorizedFamilyId ORDER BY t.TransactionDateTime) AS RowNumber
               FROM AnalyticsFactFinancialTransaction t
             INNER JOIN FinancialAccount fa
               ON fa.Id = t.AccountId
             -- schedule details
             LEFT JOIN FinancialTransaction ft
                 ON ft.Id = t.TransactionId
             LEFT JOIN FinancialScheduledTransaction fst
                 ON fst.Id = ft.ScheduledTransactionId
             -- remove children
               INNER JOIN PersonAlias pa
                 ON pa.Id = t.AuthorizedPersonAliasId
               INNER JOIN GroupMember
                 ON GroupMember.PersonId = pa.PersonId AND GroupMember.GroupRoleId = @AdultRoleId
               INNER JOIN [Group] ON [Group].Id = GroupMember.GroupId AND [Group].GroupTypeId = 10
             WHERE t.TransactionDateTime > DATEADD(month, -18, GETDATE())
                 AND t.EntityTypeName != 'Registration'
                 AND t.ScheduleType = 'Scheduled'
                 --AND t.IsFirstTransactionOfType = 1 -- 1 is True 0 is False
                 AND t.IsScheduled = 'True'
                 AND t.TransactionFrequency != ''
                 AND fst.IsActive = 1
                 AND fa.IsTaxDeductible = 'True' --ensures that these are only Tax-Deductible gifts
                 -- AND fa.CampusId = 4 -- used for manually restricting to a specific campus
                 -- AND fa.ParentAccountId = 5 -- used for manually restricting to a specific top-level account
           ) [a]
           OUTER APPLY (
               SELECT TOP 1 p.[Id]
                 FROM [Person] p
                 INNER JOIN [GroupMember] gm ON gm.[PersonId] = p.[Id]
               WHERE
                 gm.GroupRoleId = @AdultRoleId
                 AND gm.[GroupId] = a.AuthorizedFamilyId
               ORDER BY p.[Gender]
           ) [Head]
           WHERE a.RowNumber = 1 --(set to 2 for 2nd schedule, set to 3 for 3rd schedule)
           AND a.TransactionDateTime >= CAST (@StartDate AS DATE)
           AND a.TransactionDateTime < DATEADD (day, 1,CAST(CONVERT(datetimeoffset,@EndDate,127) AS DATE))
         ) b
         JOIN Person
           ON Person.Id = b.HeadPersonId
         JOIN PersonAlias [GiverAlias]
           ON b.AuthorizedPersonAliasId = GiverAlias.Id
         JOIN Person [Giver]
           ON Giver.Id = GiverAlias.PersonId
         LEFT JOIN AttributeValue av
           ON av.EntityId = Person.Id
           AND av.AttributeId = 6959 --First Scheduled Gift Thankyou Letter Sent Date (!IMPORTANT! THIS ID WILL NEED TO BE UPDATED IN YOUR ROCK INSTANCE BASED ON THE PERSON ATTRIBUTE CREATED)
         OUTER APPLY (
             SELECT TOP 1
             *
             FROM PhoneNumber
             WHERE PersonId = Giver.Id
             ORDER BY NumberTypeValueId
         ) [Phones]
         OUTER APPLY (
           SELECT TOP 1
           loc.*
           FROM GroupLocation gl
           LEFT JOIN [Location] loc
           ON loc.Id = gl.LocationId
           WHERE gl.GroupId = Person.PrimaryFamilyId
           AND loc.IsActive = 'True'
           AND gl.IsMailingLocation = 1
         ) [Address]
         WHERE av.Value is null -- ignores potential First Time Givers that already have had a letter sent to them
             AND Address.Street1 is not null
             --AND Giver.RecordStatusValueId = 3 --'Active'
         ORDER BY b.TransactionDateTime desc -- Person.LastName
         

      3. Advanced Settings (Pre-HTML):
         

         <h5>New Scheduled Gifts - Letters for Merge Template</h5>
         <p>Check the list below to ensure they have not already received an appreciation letter.</p>

   2. Dynamic Data Block #2: Name this block "New Scheduled Gifts - Emails (no Address available)"
      1. Parameter Field: @StartDate=,@EndDate=
      2. Columns (Hide): GiverId, Id
      3. Selection URL:
         

      ~/Person/{Id}/Contributions

      1. Settings: Check the following options Person Report, Communicate, Excel Export, Show Grid Filter
      2. Query: 

         -- Mad props to Josh Crews @ Simple Donation for the initial query
         -- Find 1st/2nd Schedules, Grouped by Household
         -- You can find 3rd Schedule by changing RowNumber = 2 to RowNumber = 3
         
         -- Reason we are Grouped by Household is to avoid identifying the spouse on their second gift, when it's the households 20th gift.
         -- Query in english
         -- > Searching the past 18 months, Get me
         -- > the 1st/2nd schedule made per family. Pull the 1st adult male from the household,
         -- > or any adult female if no male. Only show
         -- > me the first/second schedules that been created in the past month
         -- > You can change t.GivingGroupId to t.AuthorizedFamilyId if you want it to work based on families rather than giving groups - effectively disregarding whether or not they have combined giving turned on
         
         DECLARE @AdultRoleId int = ( SELECT TOP 1 CAST([Id] as varchar)  FROM [GroupTypeRole] WHERE [Guid] = '2639f9a5-2aae-4e48-a8c3-4ffe86681e42' )
         
         SELECT
             Giver.Id [GiverId],
             Person.Id [Id],
             Giver.FirstName,
             Giver.LastName,
             Giver.Email,
             CONCAT(Address.Street1,' ',Address.Street2,' ',Address.City,', ',Address.State,' ',Address.PostalCode) [FamilyAddress],
             Phones.NumberFormatted [Phone],
             FORMAT(b.Amount,'C') [GiftAmount],
             b.TransactionFrequency [Schedule],
             CAST (b.Start AS DATE) [StartDate],
             CAST (b.Next AS DATE) [NextDate],
             b.IsActive,
             b.PublicName [AccountName],
             CAST (b.TransactionDateTime AS DATE) [TransactionDate],
             av.Value [Thanked Date]
           FROM (
           SELECT
           a.*,
           Head.Id [HeadPersonId]
             FROM (
             SELECT
             t.*,
             fa.PublicName,
             fst.StartDate [Start],
             fst.NextPaymentDate [Next],
             fst.IsActive [IsActive],
             ROW_NUMBER() OVER (PARTITION BY t.AuthorizedFamilyId ORDER BY t.TransactionDateTime) AS RowNumber
               FROM AnalyticsFactFinancialTransaction t
             INNER JOIN FinancialAccount fa
               ON fa.Id = t.AccountId
             -- Schedule Details
               LEFT JOIN FinancialTransaction ft
                 ON ft.Id = t.TransactionId
               LEFT JOIN FinancialScheduledTransaction fst
                 ON fst.Id = ft.ScheduledTransactionId
             -- remove children
               INNER JOIN PersonAlias pa
                 ON pa.Id = t.AuthorizedPersonAliasId
               INNER JOIN GroupMember
                 ON GroupMember.PersonId = pa.PersonId AND GroupMember.GroupRoleId = @AdultRoleId
               INNER JOIN [Group] ON [Group].Id = GroupMember.GroupId AND [Group].GroupTypeId = 10
             WHERE t.TransactionDateTime > DATEADD(month, -18, GETDATE())
                 AND t.EntityTypeName != 'Registration'
                 AND t.ScheduleType = 'Scheduled'
                 --AND t.IsFirstTransactionOfType = 1 -- 1 is True 0 is False
                 AND t.IsScheduled = 'True'
                 AND t.TransactionFrequency != ''
                 AND fst.IsActive = 1
                 AND fa.IsTaxDeductible = 'True' --ensures that these are only Tax-Deductible gifts
           ) [a]
           OUTER APPLY (
               SELECT TOP 1 p.[Id]
                 FROM [Person] p
                 INNER JOIN [GroupMember] gm ON gm.[PersonId] = p.[Id]
               WHERE
                 gm.GroupRoleId = @AdultRoleId
                 AND gm.[GroupId] = a.AuthorizedFamilyId
               ORDER BY p.[Gender]
           ) [Head]
           WHERE a.RowNumber = 1 --(set to 2 for 2nd schedule, set to 3 for 3rd schedule)
           AND a.TransactionDateTime >= CAST (@StartDate AS DATE)
           AND a.TransactionDateTime < DATEADD (day, 1,CAST(CONVERT(datetimeoffset,@EndDate,127) AS DATE))
         ) b
         JOIN Person
           ON Person.Id = b.HeadPersonId
         JOIN PersonAlias [GiverAlias]
           ON b.AuthorizedPersonAliasId = GiverAlias.Id
         JOIN Person [Giver]
           ON Giver.Id = GiverAlias.PersonId
         LEFT JOIN AttributeValue av
           ON av.EntityId = Person.Id
           AND av.AttributeId = 6959 --First Scheduled Gift Thankyou Letter Sent Date (!IMPORTANT! - THIS ID WILL NEED TO BE UPDATED IN YOUR ROCK INSTANCE BASED ON THE PERSON ATTRIBUTE CREATED)
         OUTER APPLY (
             SELECT TOP 1
             *
             FROM PhoneNumber
             WHERE PersonId = Giver.Id
             ORDER BY NumberTypeValueId
         ) [Phones]
         OUTER APPLY (
           SELECT TOP 1
           loc.*
           FROM GroupLocation gl
           LEFT JOIN [Location] loc
           ON loc.Id = gl.LocationId
           WHERE gl.GroupId = Person.PrimaryFamilyId
           --AND loc.IsActive = 'True'
           --AND gl.IsMailingLocation = 1
         ) [Address]
         WHERE av.Value is null -- ignores potential First Time Givers that already have had a letter sent to them
             AND Address.Street1 is null -- finds email only givers
             --AND Giver.RecordStatusValueId = 3 --'Active'
         ORDER BY b.TransactionDateTime desc -- Person.LastName
         

         
         
      3. Advanced Settings (Pre-HTML): 

         <h5>New Scheduled Gifts - Emails (no Address available)</h5>
         <p>Check the list below to ensure they have not already received an appreciation letter or email below.</p>

   3. Dynamic Data block #3: Name this block "Letters/Emails Previously Sent to New Scheduled Givers"
      1. Parameters Field: @StartDate=,@EndDate=
      2. Columns (Hide): GiverId, Id
      3. Selection URL:
         

      ~/Person/{Id}/Contributions
      

      1. Settings: Check the following options Person Report, Communicate, Excel Export, Merge Template, Show Grid Filter
      2. Query:
         

         -- Mad props to Josh Crews @ Simple Donation for the initial query
         -- Find 1st/2nd Schedules, Grouped by Household
         -- You can find 3rd Schedule by changing RowNumber = 2 to RowNumber = 3
         
         -- Reason we are Grouped by Household is to avoid identifying the spouse on their second gift, when it's the households 20th gift.
         -- Query in english
         -- > Searching the past 18 months, Get me
         -- > the 1st/2nd schedule made per family. Pull the 1st adult male from the household,
         -- > or any adult female if no male. Only show
         -- > me the first/second schedules that been created in the past month
         -- > You can change t.GivingGroupId to t.AuthorizedFamilyId if you want it to work based on families rather than giving groups - effectively disregarding whether or not they have combined giving turned on
         
         DECLARE @AdultRoleId int = ( SELECT TOP 1 CAST([Id] as varchar)  FROM [GroupTypeRole] WHERE [Guid] = '2639f9a5-2aae-4e48-a8c3-4ffe86681e42' )
         
         SELECT
             Giver.Id [GiverId],
             Person.Id [Id],
             Giver.FirstName,
             Giver.LastName,
             Giver.Email,
             CONCAT(Address.Street1,' ',Address.Street2,' ',Address.City,', ',Address.State,' ',Address.PostalCode) [FamilyAddress],
             Phones.NumberFormatted [Phone],
             FORMAT(b.Amount,'C') [GiftAmount],
             b.TransactionFrequency [Schedule],
             CAST (b.Start AS DATE) [StartDate],
             CAST (b.Next AS DATE) [NextDate],
             b.IsActive,
             b.PublicName [AccountName],
             CAST (b.TransactionDateTime AS DATE) [TransactionDate],
             av.Value [Thanked Date]
           FROM (
           SELECT
           a.*,
           Head.Id [HeadPersonId]
             FROM (
             SELECT
             t.*,
             fa.PublicName,
             fst.StartDate [Start],
             fst.NextPaymentDate [Next],
             fst.IsActive [IsActive],
             ROW_NUMBER() OVER (PARTITION BY t.AuthorizedFamilyId ORDER BY t.TransactionDateTime) AS RowNumber
               FROM AnalyticsFactFinancialTransaction t
             INNER JOIN FinancialAccount fa
               ON fa.Id = t.AccountId
             -- Schedule Details
               LEFT JOIN FinancialTransaction ft
                 ON ft.Id = t.TransactionId
               LEFT JOIN FinancialScheduledTransaction fst
                 ON fst.Id = ft.ScheduledTransactionId
             -- remove children
               INNER JOIN PersonAlias pa
                 ON pa.Id = t.AuthorizedPersonAliasId
               INNER JOIN GroupMember
                 ON GroupMember.PersonId = pa.PersonId AND GroupMember.GroupRoleId = @AdultRoleId
               INNER JOIN [Group] ON [Group].Id = GroupMember.GroupId AND [Group].GroupTypeId = 10
             WHERE t.TransactionDateTime > DATEADD(month, -18, GETDATE())
                 AND t.EntityTypeName != 'Registration'
                 AND t.ScheduleType = 'Scheduled'
                 --AND t.IsFirstTransactionOfType = 1 -- 1 is True 0 is False
                 AND t.IsScheduled = 'True'
                 AND t.TransactionFrequency != ''
                 AND fst.IsActive = 1
                 AND fa.IsTaxDeductible = 'True' --ensures that these are only Tax-Deductible gifts
                 -- AND fa.CampusId = 4 -- used for manually restricting to a specific campus
                 -- AND fa.ParentAccountId = 5 -- used for manually restricting to a specific top-level account
           ) [a]
           OUTER APPLY (
               SELECT TOP 1 p.[Id]
                 FROM [Person] p
                 INNER JOIN [GroupMember] gm ON gm.[PersonId] = p.[Id]
               WHERE
                 gm.GroupRoleId = @AdultRoleId
                 AND gm.[GroupId] = a.AuthorizedFamilyId
               ORDER BY p.[Gender]
           ) [Head]
           WHERE a.RowNumber = 1 --(set to 2 for 2nd schedule, set to 3 for 3rd schedule)
           AND a.TransactionDateTime >= CAST (@StartDate AS DATE)
           AND a.TransactionDateTime < DATEADD (day, 1,CAST(CONVERT(datetimeoffset,@EndDate,127) AS DATE))
         ) b
         JOIN Person
           ON Person.Id = b.HeadPersonId
         JOIN PersonAlias [GiverAlias]
           ON b.AuthorizedPersonAliasId = GiverAlias.Id
         JOIN Person [Giver]
           ON Giver.Id = GiverAlias.PersonId
         LEFT JOIN AttributeValue av
           ON av.EntityId = Person.Id
           AND av.AttributeId = 6959 --First Scheduled Gift Thankyou Letter Sent Date (!IMPORTANT! - THIS ID WILL NEED TO BE UPDATED IN YOUR ROCK INSTANCE BASED ON THE PERSON ATTRIBUTE CREATED)
         OUTER APPLY (
             SELECT TOP 1
             *
             FROM PhoneNumber
             WHERE PersonId = Giver.Id
             ORDER BY NumberTypeValueId
         ) [Phones]
         OUTER APPLY (
           SELECT TOP 1
           loc.*
           FROM GroupLocation gl
           LEFT JOIN [Location] loc
           ON loc.Id = gl.LocationId
           WHERE gl.GroupId = Person.PrimaryFamilyId
           --AND loc.IsActive = 'True'
           --AND gl.IsMailingLocation = 1
         ) [Address]
         WHERE av.Value is not null -- ignores potential First Time Givers that already have had a letter sent to them
            --AND Giver.RecordStatusValueId = 3 --'Active'
         ORDER BY b.TransactionDateTime desc -- Person.LastName
         

         
         
      3. Advanced Settings (Pre-HTML):
         

         <h5>Letters/Emails Previously Sent to New Scheduled Givers</h5>

         
         

SETTING THE "THANKED" DATE

There are 2 ways to set the date for the First Time or New Schedule Gift and it’s entirely up to you as to how’d you’d like to go about it.

1. You may enable the new Person Attributes in the Bulk Updates block and set any date that way. This block is found on your ~/BulkUpdate page.

   Note: If you do not want to expose the Finance Internal attributes to others within your organization be sure to adjust the security on the two newly created Person Attributes.

   1. Edit the Bulk Update block properties on this page by adding the Finance Internal attribute category to the available person attributes to allow bulk updating for.
   2. This method enables you to quickly back-date the "thanked" gift dates. Process the Bulk Update "thanked" date just like any other bulk update activity
      
      
      
      
      
2. You may import the provided Workflows and set the date for now “today” whenever you select persons from the report grid and click the “Launch Workflow” action from the grid.
   1. Follow the next set of instructions in order to import and set up the “thanked” date workflow.
   2. This method enables you to quickly set the “thanked” date of now/today.
   3. Refer to next section for setting up the workflow to set the "thanked" date.
      
      

WORKFLOW SETUP (First Time Gift & New Scheduled Gift)

1. Go to Admin Tools - Power Tools - Workflow Import/Export
2. Select a Category (I typically put this under one for Finance) and then upload and click the Import button for each of these files attached below (you may need to unzip them first):
   SetFirstGiftThankedSentDate.json
   SetNewScheduledGiftThankedSentDate.json
3. Edit each of the workflows. Find the last Action called Set Person Letter Date Attribute and select the Person Attributes you previously made for each workflow. Choose the First Gift and New Scheduled Gift Person Attributes accordingly for each Workflow. Click Save for each workflow.
   
   
   

MERGE DOCUMENT AND EMAIL TEMPLATE SETUP (First Time Gift & New Scheduled Gift)

The following sections document an example of how to set up your Physical Letter Merge Template and Email Communication Template. You may use or modify for your needs including all language, graphics, etc.

Filling in the First Gift Date and Amount from the Dynamic Reports into a Merge Template and Email template can be tricky. I decided to use Lava Shortcodes to make this much easier to manage and allow for editing of the communication on the fly while still maintaining the results of the gifts.

1. Go to Admin Tools - CMS Configuration - Lava Shortcodes
2. Create 2 new shortcodes for inserting into the Merge Template and the Email Template. One for First Time Gift Date the other for First Time Gift Amount. These also work for New Scheduled Gifts.
3. Go to Admin Tools - Communications - Communication Templates
4. Create 2 new Email Wizard templates for a First Time Gift and a New Scheduled Gift as the language between these will likely vary for your purposes.
5. Go to Admin Tools - General Settings - Merge Templates
6. Ceate 2 new Templates for sending out physical thank you letters to those with addresses. If you need to create a new Merge Template Category of Finance (which I highly recommend) simply select the “Add Category” button before creating your new Templates.
   1. Upload your modified Word documents accordingly (see attached sample below to get started quickly)
7. Use the following details to create the Lava Shortcodes, Email Template, and Merge Template:

Merge template (Word doc):

Tag Name: FirstGiftAmount

Tag Type: Inline

Tag Description: Used for inserting SQL into the First Time Gift Email Communication and Merge Template.

Documentation: Add to an email or merge template so that SQL can return First Gift Amount in $. This SQL assumes the communication or report column has a Person Id field.

Enabled Lava Commands: SQL

Shortcode Markup: Refer to lavaShortcode_FirstGiftAmount.sql file in attached zip

Tag Name: FirstGiftDate

Tag Type: Inline

Tag Description: Used for inserting SQL into the First Time Date Email Communication and Merge Template.

Documentation: Add to an email or merge template so that SQL can return First Gift Date. This SQL assumes the communication or report column has a Person Id field.

Enabled Lava Commands: SQL

Shortcode Markup: Refer to lavaShortcode_FirstGiftDate.sql file in attached zip

FINAL THOUGHTS

Did you know that the vast majority (75-90%) of church members do not tithe in an average congregation? How much more important it is to acknowledge and value those that do! It is important that a thank you comes from your organization personally (from your Stewardship Pastor, Finance Leader, or Pastor) and timely (within the first week, two at the latest). If a personal letter or email is given to new donors a relationship is established and they are more likely to give again enabling them to become a "plugged in" regular attender.

This also gives you an opportunity to show-case your small/home/care groups in your church in order to begin the process of connection with these new givers.

Some interesting thoughts on thanking new givers:

Automate First Gift Thank-you Email (RockRMS Recipe)

Donors Need to be Thanked: Outrageous Donor Service, Part 4

Seven Ways to Thank Donors to the Church

Donor Thank-You Letter Templates