MOVE FROM PAPERWORK TO RELATIONSHIPS

Stop clicking your screen and start clicking with people
by creating a custom automated system in Rock.

All of the details from the RX2021 presentation are located at https://oneandall.church/rx2021takeaway

The Problem

How do we connect new guests effectively?

  • Lost physical paperwork
  • Dead-end Rock connections
  • Time-consuming follow up
  • Low accountability for follow-through

The Solution

A system that handles the routine tasks and tracks progress enabling team members to focus on meaningful interactions and ensure that people do not fall through the cracks.

New Guest fills out connection card via workflow

Automated Tasks

  • Added to follow-up group
  • Email from Guest Experience Team
  • Emails from Campus Pastor
  • Texts from Campus Pastor
  • Connections for post cards
  • Connections for phone call

Meaningful Interactions

  • Phone call from Campus Pastor
  • Postcard from Team Member
  • Replies to emails
  • Replies to text messages

How Do I Use It?

  • New Guests
  • Baptism/Salvation 
  • Prayer
  • New Family/Child 
  • Event Follow Up
  • Lead Up To Event
  • New To Serve Team
  • New To Community Group
  • Any process where you would like multiple routine steps performed over the course of several days

The Details

The system has multiple components: Groups & Group Type, Content Channels, Child Content Channels, Content Channel Types, Group Viewer Page, Workflow, SQL, and a Job.

Group Type

Create a Group Type that has a Member Attribute of “Active Date” of type Date.
This is used to contain the people who are in the automated system. The active date is used to track when the automation is to start. NOTE: The Attribute Id of this attribute will be used later in the SQL

image7.png

We use Active Date rather than Date Added because with some groups we want to control what day of the week the automation starts so we always know what day it is when content is created/sent. All our First Time guests have an Active Date of the Sunday they first visited.

Group(s)

Create Groups of the Group Type created above and populate the groups with profiles that need automated. Make sure that the Member Attribute: ActiveDate is set.

Content Channel Types

Create 2 different Content Channel Types:

  1. Follow Up Queue
    1. No Dates
    2. Item Attribute: Guest Experience Group (NOTE: Write down the Attribute Id. It will be used in the SQL)
    3. Field Type: Group
  2. Follow Up Action
    1. Single Date
    2. Channel Attribute: Follow Up Type (NOTE: Write down the Attribute Id. It will be used in the SQL)
      • Field Type: Single Select
      • Values: SMS^Text Message,Email^Email Message,Connection^Connection Opportunity Request
    3. Item Attribute: Days Since Active (NOTE: Write down the Attribute Id. It will be used in the SQL)
      • Field Type: Integer

Content Channels

  1. Create Content Channels of Type “Followup Action”
    1. Create Connection
      1. Set Follow Up Type to “Connection Opportunity Request”
      2. Create Item Attribute: Connection Opportunity
        • Field Type: Connection Opportunity
      image4.jpg
    2. Send Email
      1. Set Follow Up Type to “Email Message”
      2. Create Item Attributes:
        • Subject - Text
        • Body - HTML
        • System Communication - System Communication
          If this is set, ignore the subject and body
        • From Email Address - Type: email
      image8.jpg
    3. Send SMS
      1. Set Follow Up Type to “Text Message”
      2. Item Attributes:
        • SMSText - Text
      image1.jpg
  2. Create a Content Channel of Type “Followup Queue”
    • Add Child Content Channels for each Content Channel created in step 1
    image6.jpg
  3. Add Content Channel Items
    • Map out the timing of all your automation and create the appropriate Actions as children under the appropriate Follow Up Queue
    image3.png image2.png

Workflow

Import the Workflow from the attached json file “Automated Follow Up Action Template_202109010841.json”. The workflow doesn’t require any extra modifications. Make sure to note the Workflow Type Id that the imported workflow assigns.

SQL

The SQL is the meat of the whole system. There are many variables that will be different based on your instance. It resides inside the Job that runs as scheduled once a day.

DECLARE @QueueContentChannelGroupAttributeId INT, @ActionContentChannelDaysSinceAttributeId INT,
@GroupMemberActiveDateAttributeId INT, @ChurchTimeZone VARCHAR(50);

-------------------------------------------

SET @QueueContentChannelGroupAttributeId = 20074; -- When editing Content Channel Queue "Guest Experience Group"
attribute value this would be listed as "Content Item Attributes Id: 20074" in the header.
SET @ActionContentChannelDaysSinceAttributeId = 20030; -- When editing Content Channel Action "Days Since" attribute
value this would be listed as "Content Item Attributes Id: 20030" in the header.
SET @GroupMemberActiveDateAttributeId = 20072; -- When editing Group Types Member attribute of "Active Date" this would
be listed as "Group Member Attributes Id: 20072" in the header.
SET @ChurchTimeZone = 'Pacific Standard Time'; -- Other Common in USA = Mountain Standard Time, Central Standard Time,
Eastern Standard Time. Find all options by running "SELECT * FROM sys.time_zone_info;"

-------------------------------------------

DECLARE @ContentChannelEntityTypeId INT, @DateAtChurchTimeZone DATE;
SET @ContentChannelEntityTypeId = (SELECT [ID] FROM EntityType WHERE [Name] = 'Rock.Model.ContentChannel');
SET @DateAtChurchTimeZone = CAST(GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE @ChurchTimeZone AS DATE);

SELECT gm.Guid GroupMember, cciAction.Guid ActionItem
FROM [GROUP] g
INNER JOIN AttributeValue avGroup ON avGroup.AttributeId = @QueueContentChannelGroupAttributeId AND
TRY_CAST(avGroup.[Value] AS uniqueidentifier) = g.Guid
INNER JOIN ContentChannelItem cciQueue ON cciQueue.Id = avGroup.EntityId -- AND cciQueue.ContentChannelTypeId = 19
INNER JOIN ContentChannelItemAssociation cia ON cia.ContentChannelItemId = cciQueue.Id
INNER JOIN ContentChannelItem cciAction ON cciAction.Id = cia.ChildContentChannelItemId -- AND
cciAction.ContentChannelTypeId = 21
INNER JOIN AttributeValue avDaysSince ON avDaysSince.AttributeId = @ActionContentChannelDaysSinceAttributeId AND
cciAction.Id = avDaysSince.EntityId
INNER JOIN GroupMember gm ON g.Id = gm.GroupId AND gm.IsArchived = 0 AND gm.GroupMemberStatus != 0
INNER JOIN AttributeValue avActiveDate ON gm.Id = avActiveDate.EntityId AND avActiveDate.AttributeId =
@GroupMemberActiveDateAttributeId AND DATEDIFF(DAY,avActiveDate.ValueAsDateTime, @DateAtChurchTimeZone ) =
avDaysSince.ValueAsNumeric
INNER JOIN Person p ON gm.PersonId = p.Id
AND NOT EXISTS
(
SELECT 1
FROM Interaction i
INNER JOIN PersonAlias pa ON i.PersonAliasId = pa.Id AND pa.PersonId = gm.PersonId
INNER JOIN InteractionComponent ic ON i.InteractionComponentId = ic.Id AND ic.EntityId = cciAction.Id
INNER JOIN InteractionChannel ich ON ic.InteractionChannelId = ich.Id AND ich.ComponentEntityTypeId =
@ContentChannelEntityTypeId
)
WHERE g.IsActive = 1 AND g.IsArchived = 0 -- AND g.GroupTypeId = 110 --AND Interact.Id IS NULL

Job

The job runs daily at any specified time you feel is appropriate to have your communications sent. You need to install a plugin that launches a workflow for every entity in a SQL. You can find the plugin here: https://www.rockrms.com/rockshop/plugin/85/workflow-launcher

image9.png

BONUS: Followup Group Viewer

  1. Duplicate the page used for Group Viewer and the Group Member Detail
  2. On the new Group Viewer Page:
    1. Modify the Block properties in the main zone named GroupDetailRight.
      • Select the Group Type for the Followup Group and make sure it is the only Type selected in the Group Type Includes field
    2. Modify the Block properties in the main zone named Group Member List.
      1. Select the Page copied in step 1 as the Group Member Detail Page for the Detail Page on the block.
      2. On the Custom Grid Options tab, create custom columns in any order desiered:
        • Age - {{ Row.Person.Age }}
        • Days Active - {% assign midnight = 'Now' | Date:'MM/dd/yyyy 00:00:00' %}{{ Row | Attribute:'ActiveDate' | DateDiff:midnight,'d'}}
        • Week - {% assign midnight = 'Now' | Date:'MM/dd/yyyy 00:00:00' %} {% assign week = Row | Attribute:'ActiveDate' | DateDiff:midnight,'d' | DividedBy:7 | Ceiling %} {% if week > 0 %}Week {{ week }}{% endif %}
        • Next -
          {% contentchannel where:'ContentChannelTypeId == 21 && GuestExperienceGroup == "{{Row.Group.Guid}}"' Iterator:'Items' %}
              {% assign offsetlow = 100 %}
              {% assign midnight = 'Now' | Date:'MM/dd/yyyy 00:00:00' %}
              {% assign offset = Row | Attribute:'ActiveDate' | DateDiff:midnight,'d' | AsInteger %}
              {% for channel in Items %} 
                  {% for item in channel.Items %} 
                      {% assign showitem = 0 %}
                      {% for pitem in item.ParentItems %}
                            {% assign parentgroup = pitem.ContentChannelItem | Attribute:'GuestExperienceGroup','RawValue' %}
                            {% if parentgroup == Row.Group.Guid %}
                                {% assign showitem = 1 %}
                            {% endif %}
                      {% endfor %}
                      {% if showitem == 1 %}
                          {% assign days = item | Attribute:'DaysSinceSunday' | AsInteger %}
                          {% if days > offset and days < offsetlow %}
                              {% assign type = item.ContentChannel | Attribute:'FollowUpType','RawValue' %}
                              {% assign offsetlow = days %}
                          {% endif %}
                      {% endif %}
                  {% endfor %}
              {% endfor %}
          {% endcontentchannel %}
          {% if offsetlow < 100 %}
              {{ type }} on Day {{ offsetlow }}
          {% else %}
              {% if offset == null %}
          Not Active
              {% else %}
          Complete
              {% endif %}
          {% endif %}

        Note: You may need to modify attribute names and ContentChannelTypeId == statement in this lava

    3. Modify the Block properties in the sidebar1 zone named GroupViewLeft
      1. Select the Group Type for the Followup group and make sure it is the only type selected in the Group Type Includes field
      2. Select the Parent Group of your Follow Up groups in the Root Group field
      3. Select the page copied in step 1 as the Group viewer Page for the Detail Page
    4. Create a new block in the sidebar1 zone named Queue List and make it a Dynamic Data Block
      • Block Properties
        • Query:
          select g.Id as [Group], avoffset.Value as [Days], cciaction.Title as Action, cciaction.Id as ccId, cciqueue.id as ccQueue
              from ContentChannelItemAssociation ccia
              join ContentChannelItem cciaction on ccia.ChildContentChannelItemId = cciaction.Id
              join ContentChannel cc on cciaction.ContentChannelId = cc.Id
              join AttributeValue avtype on avtype.EntityId = cc.Id and avtype.AttributeId = 20075 -- (find the Attribute Id of your “Follow Up Type” in the Follow Up Action Content Channel)
              join AttributeValue avoffset on avoffset.EntityId = cciaction.Id and avoffset.AttributeId = 20030 -- (find the Attribute Id of your “Days Offset” in the Follow Up Action Content Channel)
              join ContentChannelItem cciqueue on ccia.ContentChannelItemId = cciqueue.Id
              join AttributeValue avgroup on avgroup.EntityId = cciqueue.Id and avgroup.AttributeId = 20074 -- (find the Attribute Id of your “Follow Up Group” in the Follow Up Queue Content Channel)
              join [Group] g on avgroup.Value = g.Guid
              where cciaction.ContentChannelTypeId = 21 and g.Id = {{GroupId}}
              order by cciaction.StartDateTime
        • Formatted Output:
          <h3>Guest Experience</h3>
          <table class="table">
              <thead><tr><th>Days</th><th>Action</th></thead>
              <tbody>
                  {% for row in rows %}
                      <tr>
                          <td>{{row.Days}}</td>
                          <td><a href="/ContentChannelItem/{{row.ccId}}">{{row.Action}}</a></td>
                          {% assign queue = row.ccQueue %}
                      </tr>
                  {% endfor %}
              </tbody>
          </table>
          <a href="/ContentChannelItem/{{queue}}" class="btn btn-primary">Guest Queue</a>
  3. On the New Group Member Detail Page:
    1. Create 5 blocks in the Main Zone
      1. Group Member Detail (Group Member Detail)
      2. Member Details (HTML Content)
      3. Interactions Title (HTML Content)
      4. Follow Up Interactions (Dynamic Data)
      5. Notes (Notes)
    2. In Member Details Block:
      {% stylesheet id:'home-feature' %}
      
      .metric {
          border: 1px solid #ccc;
          padding: 12px;
          margin-bottom: 12px;
      }
      
      .metric h5 {
          font-size: 24px;
          margin-top: 0;
          margin-bottom: 0;
          width: 100%;
          white-space: nowrap;
          overflow: hidden;
          text-overflow: ellipsis;
      }
      
      .metric .value {
          font-size: 48px;
          font-weight: 800;
          line-height: 1em;
      }
      
      .metric .value small{
          display: block;
          font-weight: 300;
          font-size: 14px;
          line-height: 1em;
      }
      
      .metric .icon {
          float: right;
          opacity: .3;
          font-size: 65px;
          border-radius: 0;
          width: 85px;
          height: 65px;
      }
      
      {% endstylesheet %}{% assign groupmember = PageParameter.GroupMemberId %}
      {% groupmember id:'{{groupmember}}' %}
      {% assign Person = groupmember.PersonId | PersonById %}
      {% assign Member = groupmember %}
      {% endgroupmember %}
      {% assign sundaydate = Member | Attribute:'ActiveDate' %}
      {% assign midnight = 'Now' | Date:'MM/dd/yyyy 00:00:00' %}
      {% if sundaydate != null %}
      
      Days In Queue
      {% assign days = sundaydate | DateDiff:midnight,'d' %}
      {% if days %}{{ days }}{% else %}N/A{% endif %} 
      Current Week
      {% assign week = sundaydate | DateDiff:midnight,'d' | DividedBy:7 | Ceiling %} {% if week %} {% if week >= 0 %} {{ week }} {% endif %} {% else %} N/A {% endif %} 
      Next Interaction
      {% contentchannel where:'ContentChannelTypeId == 21' Iterator:'Items' %} {% assign offsetlow = 100 %} {% for channel in Items %} {% for item in channel.Items %} {% assign showitem = 0 %} {% for pitem in item.ParentItems %} {% assign parentgroup = pitem.ContentChannelItem | Attribute:'GuestExperienceGroup','RawValue' %} {% if parentgroup == Member.Group.Guid %} {% assign showitem = 1 %} {% endif %} {% endfor %} {% if showitem == 1 %} {% assign days = item | Attribute:'DaysSinceSunday' | AsInteger %} {% assign offset = sundaydate | DateDiff:midnight,'d' | AsInteger %} {% if days > offset and days < offsetlow %} {% assign type = item.ContentChannel | Attribute:'FollowUpType','RawValue' %} {% assign offsetlow = days %} {% endif %} {% endif %} {% endfor %} {% endfor %} {% endcontentchannel %} {% if offsetlow < 100 %} {{ type }}on Day {{ offsetlow }} {% else %} {% if offset == null %} N/A {% else %} Complete {% endif %} {% endif %}
      {% endif %}

      Be sure to change any Attribute Names  and ContentChannelTypeId different from ones listed.

    3. In Interactions Title: <h3>Past Interactions</h3>
    4. In Follow Up Interactions:
      • Query:
                    select pa.PersonId, comp.Name, i.CreatedDateTime Date, cci.Id ContentItemId
                    from Interaction i 
                    join InteractionComponent comp on i.InteractionComponentId = comp.Id
                    join InteractionChannel chan on comp.InteractionChannelId = chan.Id
                    join PersonAlias pa on pa.Id = i.PersonAliasId
                    join GroupMember gm on pa.PersonId = gm.PersonId
                    join [Group] g on gm.GroupId = g.Id
                    join ContentChannelItem cci on comp.EntityId = cci.Id
                    join ContentChannelItemAssociation cca on cca.ChildContentChannelItemId = cci.Id
                    join AttributeValue av on av.EntityId = cca.ContentChannelItemId and av.AttributeId = 20074 -- (find the AttributeId of the Group in the Followup Queue Content Channel)
                    where chan.Id in (22,23,24) and gm.Id = {{ GroupMemberId }} and av.Value = g.Guid
                    order by i.CreatedDateTime desc
                    (Find the Ids of the 3 follow up action content channels)
      • Formatting:

        1. Hide Columns: PersonId, ContentItemId
        2. Selection URL: /ContentChannelItem/{ContentItemId}
image10.png