Oct
8
During RefreshCache, Jon showed a lot of the stuff we’ve been doing with eRA (estimated regular attendees). Here is how we capture that data…
dtmt_estimated_regular_attendees
This is the table that stores all the data. To track the eRA wins and losses, we need to know at any point in time, whether a family is considered an eRA.
CREATE TABLE [dbo].[dtmt_estimated_regular_attendees](
[weekend_date] [datetime] NOT NULL,
[family_id] [int] NOT NULL,
[times_attended_last_16_weeks] [int] NULL,
[first_attended] [datetime] NULL,
[last_attended] [datetime] NULL,
[times_gave_last_6_weeks] [int] NULL,
[times_gave_total] [int] NULL,
[last_gave] [datetime] NULL,
[regular_attendee] [bit] NOT NULL,
[regular_attendee_c] [bit] NOT NULL,
[regular_attendee_g] [bit] NOT NULL,
CONSTRAINT [PK_dtmt_estimated_regular_attendees] PRIMARY KEY CLUSTERED
(
[weekend_date] ASC,
[family_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[dtmt_estimated_regular_attendees] ADD CONSTRAINT [DF_cust_ccv_estimated_regular_attendees_regular_attendee] DEFAULT ((0)) FOR [regular_attendee]
GO
ALTER TABLE [dbo].[dtmt_estimated_regular_attendees] ADD CONSTRAINT [DF_dtmt_estimated_regular_attendees_regular_attendee_c] DEFAULT ((0)) FOR [regular_attendee_c]
GO
ALTER TABLE [dbo].[dtmt_estimated_regular_attendees] ADD CONSTRAINT [DF_dtmt_estimated_regular_attendees_regular_attendee_g] DEFAULT ((0)) FOR [regular_attendee_g]
GO
cust_ccv_sp_dtmt_loadEstimatedRegularAttendees
Every day we run this SQL proc (configured as an agent job) to load the most recent weekends numbers. When looking at attendance, we include attendance from children’s check-in that occurs at the same time that an adult service takes place (which is why we exclude high school attendance).
ALTER PROC [dbo].[cust_ccv_sp_dtmt_loadEstimatedRegularAttendees]
@CurrentDate datetime
AS
BEGIN
--DECLARE @CurrentDate datetime
--SET @CurrentDate = GETDATE()
DECLARE @SaturdayDate DATETIME
SET @SaturdayDate = dbo.util_funct_saturday(@CurrentDate)
DELETE dtmt_estimated_regular_attendees
WHERE weekend_date = @SaturdayDate;
----/****************************************************************************
---- ATTENDANCE
---- Calculate the number of times each family has attended in the last 16
---- weeks and when they last attended based on children's attendance records.
----*****************************************************************************/
INSERT INTO dtmt_estimated_regular_attendees (
weekend_date,
family_id,
times_attended_last_16_weeks)
SELECT
@SaturdayDate,
fm.family_id,
COUNT(DISTINCT O.service_time)
FROM core_occurrence_attendance OA
INNER JOIN core_v_occurrence_service O ON O.occurrence_id = OA.occurrence_id
INNER JOIN core_family_member FM ON FM.person_id = OA.person_id
WHERE O.is_service = 1
AND OA.attended = 1
AND O.occurrence_type_id <> 6 -- High School
AND service_time <= @SaturdayDate
AND service_time >= DATEADD(week, -15, @SaturdayDate)
GROUP BY fm.family_id
/****************************************************************************
GIVING
Calculate the number of times each family has given in the last 6 weeks,
total times they've given and when the last time they gave was
*****************************************************************************/
DECLARE @FamilyContributionCount TABLE (
family_id [int] not null,
total_count [int] null,
recent_count [int] null,
last_gave [datetime] null
);
with cte
as
(
SELECT
weekend_date,
family_id,
case when weekend_date >= DATEADD(week, -5, @SaturdayDate)
then 1 else 0 end as recent
FROM cust_ccv_v_family_giving_weekend
WHERE weekend_date <= @SaturdayDate
AND weekend_date > '1/1/2005'
)
-- Insert Total Count into temporary table
INSERT INTO @FamilyContributionCount
SELECT
family_id,
COUNT(*),
SUM(recent),
MAX(weekend_date)
FROM cte
GROUP BY family_id
--Update Existing Records
UPDATE RA
SET
times_gave_last_6_weeks = FCC.recent_count,
times_gave_total = FCC.total_count,
last_gave = FCC.last_gave
FROM @FamilyContributionCount FCC
INNER JOIN dtmt_estimated_regular_attendees RA
ON RA.family_id = FCC.family_id
AND RA.weekend_date = @SaturdayDate
-- Create New Records
INSERT INTO dtmt_estimated_regular_attendees (
weekend_date,
family_id,
times_gave_last_6_weeks,
times_gave_total,
last_gave)
SELECT
@SaturdayDate,
FCC.family_id,
FCC.recent_count,
FCC.total_count,
FCC.last_gave
FROM @FamilyContributionCount FCC
LEFT OUTER JOIN dtmt_estimated_regular_attendees RA
ON RA.family_id = FCC.family_id
AND RA.weekend_date = @SaturdayDate
WHERE RA.family_id IS NULL
-- Calculate Attendance
UPDATE CW
SET
CW.first_attended = dbo.cust_ccv_funct_family_first_attended (CW.family_id),
CW.last_attended = dbo.cust_ccv_funct_family_last_attended (CW.family_id, CW.weekend_date),
CW.regular_attendee =
CASE WHEN LW.regular_attendee IS NULL OR LW.regular_attendee = 0
THEN
-- In ?
CASE WHEN (CW.times_gave_total >= 3 AND CW.times_gave_last_6_weeks >= 1)
OR CW.times_attended_last_16_weeks >= 8
THEN 1 ELSE 0 END
ELSE
-- Out ?
CASE WHEN DATEADD(week, 8, CW.last_gave) < CW.weekend_date
AND DATEADD(week, 4, CW.last_attended) < CW.weekend_date
AND CW.times_attended_last_16_weeks < 8
THEN 0 ELSE 1 END
END
FROM dtmt_estimated_regular_attendees CW
LEFT OUTER JOIN dtmt_estimated_regular_attendees LW
ON CW.family_id = LW.family_id
AND CW.weekend_date = DATEADD(week, 1, LW.weekend_date)
where CW.weekend_date = @SaturdayDate
-- Calculate Giving/Checking component of regular attendees
UPDATE dtmt_estimated_regular_attendees
SET
regular_attendee_g =
CASE WHEN DATEADD(week, 8, last_gave) >= weekend_date
THEN 1 ELSE 0 END,
regular_attendee_c =
CASE WHEN DATEADD(week, 4, last_attended) >= weekend_date
OR times_attended_last_16_weeks >= 8
THEN 1 ELSE 0 END
WHERE weekend_date = @SaturdayDate
AND regular_attendee = 1
END
Initial Load
To initially load this table with past data we ran this script (and let it run for a while).
DECLARE @WeekendDate datetime
SET @WeekendDate = dbo.util_funct_saturday('01/01/2006')
WHILE @WeekendDate < GETDATE()
BEGIN
EXEC [cust_ccv_sp_dtmt_loadEstimatedRegularAttendees] @WeekendDate
SET @WeekendDate = DATEADD(week, 1, @WeekendDate)
END
Loss Report
Jon mentioned the report that shows us each week who the eRA losses were. This proc will list families that became an eRA loss during any two dates.
CREATE PROC [dbo].[cust_ccv_sp_dtmt_loss_report]
@FromDate datetime,
@ToDate datetime
AS
SET @FromDate = dbo.util_funct_saturday(@FromDate)
SET @ToDate = dbo.util_funct_saturday(@ToDate)
select p.*,
tw.last_attended as [Last Attended],
tw.last_gave as [Last Gave]
from dtmt_estimated_regular_attendees tw
inner join core_v_merge_fields p on p.person_id = dbo.core_funct_familyHead2(tw.family_id)
left outer join dtmt_estimated_regular_attendees lw
on tw.family_id = lw.family_id
and lw.weekend_date = @FromDate
where tw.weekend_date = @ToDate
and lw.regular_attendee = 1
and tw.regular_attendee = 0
Trending View
This is a view that feeds some of our charts. It groups totals by weekend and shows the difference between the current weekend and the weekend 4 weeks prior. (four week rolling average).
CREATE VIEW [dbo].[cust_ccv_v_dtmt_era_trend]
AS
with cte
as
(
select
tw.weekend_date,
tw.family_id,
case when lw.regular_attendee = 1 then 1 else 0 end as eRA_1,
case when tw.regular_attendee = 1 then 1 else 0 end as eRA_2,
case when tw.regular_attendee = 1 and (lw.regular_attendee is null or lw.regular_attendee = 0)
then 1 else 0 end as eRA_add,
case when lw.regular_attendee = 1 and (tw.regular_attendee is null or tw.regular_attendee = 0)
then 1 else 0 end as eRA_loss,
case when lw.regular_attendee_g = 1 then 1 else 0 end as eRAg_1,
case when tw.regular_attendee_g = 1 then 1 else 0 end as eRAg_2,
case when tw.regular_attendee_g = 1 and (lw.regular_attendee_g is null or lw.regular_attendee_g = 0)
then 1 else 0 end as eRAg_add,
case when lw.regular_attendee_g = 1 and (tw.regular_attendee_g is null or tw.regular_attendee_g = 0)
then 1 else 0 end as eRAg_loss,
case when lw.regular_attendee_c = 1 then 1 else 0 end as eRAc_1,
case when tw.regular_attendee_c = 1 then 1 else 0 end as eRAc_2,
case when tw.regular_attendee_c = 1 and (lw.regular_attendee_c is null or lw.regular_attendee_c = 0)
then 1 else 0 end as eRAc_add,
case when lw.regular_attendee_c = 1 and (tw.regular_attendee_c is null or tw.regular_attendee_c = 0)
then 1 else 0 end as eRAc_loss,
case when tw.times_attended_last_16_weeks is not null
then 1 else 0 end as attended,
case when tw.times_attended_last_16_weeks >= 13 and tw.times_attended_last_16_weeks <= 16
then 1 else 0 end as attendance_grade_a,
case when tw.times_attended_last_16_weeks >= 9 and tw.times_attended_last_16_weeks <= 12
then 1 else 0 end as attendance_grade_b,
case when tw.times_attended_last_16_weeks >= 5 and tw.times_attended_last_16_weeks <= 8
then 1 else 0 end as attendance_grade_c,
case when tw.times_attended_last_16_weeks >= 1 and tw.times_attended_last_16_weeks <= 4
then 1 else 0 end as attendance_grade_d
from dtmt_estimated_regular_attendees tw
left outer join dtmt_estimated_regular_attendees lw
on tw.family_id = lw.family_id
and tw.weekend_date = DATEADD(week, 4, lw.weekend_date)
WHERE DATEADD(week, -4, tw.weekend_date) IN (
SELECT DISTINCT weekend_date
FROM dtmt_estimated_regular_attendees
WHERE regular_attendee = 1 )
)
select
weekend_date,
sum(eRA_1) as eRA_1,
sum(eRA_2) as eRA_2,
sum(eRA_2) - sum(eRA_1) as eRA_gain,
cast((sum(eRA_2) - sum(eRA_1)) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRA_gain_percent,
sum(eRA_add) as eRA_add,
cast(sum(eRA_add) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRA_add_percent,
sum(eRA_loss) as eRA_loss,
cast(sum(eRA_loss) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRA_loss_percent,
sum(eRAg_1) as eRAg_1,
sum(eRAg_2) as eRAg_2,
sum(eRAg_2) - sum(eRAg_1) as eRAg_gain,
cast((sum(eRAg_2) - sum(eRAg_1)) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAg_gain_percent,
sum(eRAg_add) as eRAg_add,
cast(sum(eRAg_add) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAg_add_percent,
sum(eRAg_loss) as eRAg_loss,
cast(sum(eRAg_loss) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAg_loss_percent,
sum(eRAc_1) as eRAc_1,
sum(eRAc_2) as eRAc_2,
sum(eRAc_2) - sum(eRAc_1) as eRAc_gain,
cast((sum(eRAc_2) - sum(eRAc_1)) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAc_gain_percent,
sum(eRAc_add) as eRAc_add,
cast(sum(eRAc_add) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAc_add_percent,
sum(eRAc_loss) as eRAc_loss,
cast(sum(eRAc_loss) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAc_loss_percent,
sum(attended) as attended,
sum(attendance_grade_a) as attendance_grade_a,
cast(sum(attendance_grade_a) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_a_percent,
sum(attendance_grade_b) as attendance_grade_b,
cast(sum(attendance_grade_b) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_b_percent,
sum(attendance_grade_c) as attendance_grade_c,
cast(sum(attendance_grade_c) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_c_percent,
sum(attendance_grade_d) as attendance_grade_d,
cast(sum(attendance_grade_d) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_d_percent
from cte
group by weekend_date
--order by weekend_date desc
GO
First/Last Attended
These functions for determining when a family first and last attended are used by the proc above.
CREATE function [dbo].[cust_ccv_funct_family_first_attended] (@FamilyID int)
returns datetime
as
begin
DECLARE @FirstAttended datetime
SET @FirstAttended = (
SELECT MIN(O.occurrence_start_time) AS first_attended
FROM core_family_member FM
INNER JOIN core_occurrence_attendance OA ON OA.person_id = FM.person_id
INNER JOIN core_occurrence O ON O.occurrence_id = OA.occurrence_id
INNER JOIN core_occurrence_type OT ON OT.occurrence_type_id = O.occurrence_type
WHERE FM.family_id = @FamilyID
AND OA.attended = 1
AND OT.is_service = 1
AND OT.occurrence_type_id <> 6 ) -- High School
IF @FirstAttended IS NOT NULL
SET @FirstAttended = dbo.util_funct_saturday(@FirstAttended)
RETURN @FirstAttended
end
CREATE function [dbo].[cust_ccv_funct_family_last_attended] (@FamilyID int, @WeekendDate datetime)
returns datetime
as
begin
DECLARE @LastAttended datetime
SET @LastAttended = (
SELECT MAX(O.occurrence_start_time) AS last_attended
FROM core_family_member FM
INNER JOIN core_occurrence_attendance OA ON OA.person_id = FM.person_id
INNER JOIN core_occurrence O ON O.occurrence_id = OA.occurrence_id
INNER JOIN core_occurrence_type OT ON OT.occurrence_type_id = O.occurrence_type
WHERE FM.family_id = @FamilyID
AND dbo.util_funct_mergedDate(O.occurrence_start_time,6) <= @WeekendDate
AND OA.attended = 1
AND OT.is_service = 1
AND OT.occurrence_type_id <> 6 ) -- High School
RETURN @LastAttended
end
Also need this view…
CREATE VIEW [dbo].[cust_ccv_v_family_giving_weekend]
AS
SELECT DISTINCT
[dbo].[util_funct_saturday](C.contribution_date) as weekend_date,
family_id
FROM ctrb_contribution C
INNER JOIN core_family_member FM ON FM.person_id = C.person_id
WHERE C.currency_amount > 0
Thanks Austin.