Change in Recording Sacrament Meeting Attendance

[greenwoodkl]

My stake requires a monthly statistical report and I've chosen to mirror the quarterly report format for my submission.

If I were in your shoes, I'd ask the stake how to create the report they're asking for. Let them figure out the mechanics as well as getting a feel for how much work they're asking from the ward clerks.

If you don't ask, there is always a risk that whatever you do on your own will be wrong according to them.

I started in November as a new Ward Assistant Clerk. I was provided an old Excel template titled "Member Progress Report" which I understand was the same title of the precursor to the Quarterly Report, though I don't have a copy of the old form to confirm it matched in format. It still was tracking home and visiting teaching instead of ministering. I created a new Excel template based on the quarterly report and have used that since. I felt for consistency I wanted to use the same format monthly and to not manage two different formats. I have received no feedback from my Ward Clerk or the stake either positive or negative on the change in format.

[drepouille]

In my 40--plus years of experience in the church and military, I have found that detailed statistical reports are filed away and ignored, because raw numbers are meaningless to leadership. The numbers only have meaning over time, to show trends, which can be displayed in graphs and charts. I have found that the most useful and meaningful reports we have are the Key Indicators and Trends Report and the Family History Activity Report, because leaders can see trends at a glance.

[scgallafent]

lajackson's understanding ("Potential is the number of membership records at the end of the quarter, i.e., March 31.") was correct through the end of 2019. Beginning with 2020Q1, the potential value is calculated by averaging unit members over the quarter. I'm omitting a lot of complicated detail, but that explanation is close enough.

I went back and looked at the November letter that announced the change and it didn't really address the fact that the baseline calculation was also changed. Lots of people have been very ardent in their emails explaining that we're doing it wrong, but the new calculations are correct. There were several questions that came up at the end of the quarter and my team has verified that all of our calculations and services are correct. I'm assuming that the other teams involved have finished their checks.

Unbaptized/unconfirmed members of record age 9 and older are not included in the statistics. There was a bug for not accountable members that was corrected part of the way through the quarter. There was also an issue that was causing out-of-unit members to be included in the baseline for the first couple of weeks of the quarter.

[lajackson]

Thank you Ryan for the additional context. I figured the potential number was somehow average-based since it didn't match the monthly Unit Statistics counts.

I am sorry to have misled you. Usually I am pretty much up to speed on things they have actually told us. [grin]

And my condolences that your stake requires a monthly report. There are so many issues with that requirement that I am so grateful to avoid. "Member Progress Report" was the title of the precursor to the Quarterly Report, and the reason you can no longer find it from the Church is because it was discontinued.

And if you have not been capturing weekly snapshots, I guess you will never be able to arrive at the previous potential number. You did well to get within 1.

[russellhltn]

Beginning with 2020Q1, the potential value is calculated by averaging unit members over the quarter. I'm omitting a lot of complicated detail, but that explanation is close enough.

From the "just checking" department - how is the calculation being done? One of the pitfalls I've seen is trying to average an average. Usually, that gives inaccurate results. Are you adding the attendance and potential separately and then doing the percentage, or are you taking the percentage for each week and then averaging them?

The two methods will give similar results for most wards (because the divisor remains mostly static for the quarter), but for highly dynamic wards (such as student ward, or a ward that split) the answers will diverge.

I myself am a bit torn in the right way to do this as one way gives equal weight for each week while the other weights to toward the larger ward.

[lajackson]

One of the pitfalls I've seen is trying to average an average.

With the usual caveat that I have no clue (always a safe assumption) and that I have no computer programming skills (some may disagree), how about this?

Each Sunday that an attendance number is reported, throw that number into Bucket A.
Each Sunday that an attendance number is reported, throw the number of membership records into Bucket B.

At the end of the quarter, add up the total of Bucket A and divide it by the number of numbers in the Bucket, giving an average attendance number for the quarter.
At the end of the quarter, add up the total of Bucket B and divide it by the same number of number of numbers, giving an average potential number for the quarter.
Divide the number from Bucket A by the number from Bucket B.
Print all three numbers on the Quarterly Report.

The smart people at headquarters probably call the buckets registers, and they probably have a way of going back through their sandbox, which they probably call a database, at the end of the quarter to gather all of the numbers used to calculate the Quarterly Report. If they do that, they avoid the need of keeping buckets around all the time.

But what do they do with the shovels?

[russellhltn]

With the usual caveat that I have no clue (always a safe assumption) and that I have no computer programming skills (some may disagree), how about this?

Each Sunday that an attendance number is reported, throw that number into Bucket A.
Each Sunday that an attendance number is reported, throw the number of membership records into Bucket B.

At the end of the quarter, add up the total of Bucket A and divide it by the number of numbers in the Bucket, giving an average attendance number for the quarter.
At the end of the quarter, add up the total of Bucket B and divide it by the same number of number of numbers, giving an average potential number for the quarter.
Divide the number from Bucket A by the number from Bucket B.
Print all three numbers on the Quarterly Report.

This is something best done on a spread sheet. See attached.

Hopefully the labels are explanatory. The average reported on the right uses your method. The average shown underneath is an average of each week's percentages. Based on what I'm seeing here, I think an average of each meeting is the best way to go.

In a different situation, such as calculating Miles Per Gallon for a car would use your method.

Each group is "live" so you can substitute any data you wish to see what happens. Just note that this spreadsheet doesn't calculate missing meetings correctly unless you blank out all three numbers for the week.

[lajackson]

Interesting. So my direct number calculation method returns a better percentage than your average of the individual weekly percentages. I am not a student of statistics, but is one method more statistically correct than the other? I was quite surprised at the size of the difference.

[russellhltn]

Interesting. So my direct number calculation method returns a better percentage than your average of the individual weekly percentages.

In this particular example. I could make it worse. What's happening is the larger potential size is having a larger impact on the results. So, if you make the first two weeks "worse" then then that method will result in a worse number.

I am not a student of statistics, but is one method more statistically correct than the other? I was quite surprised at the size of the difference.

In this case, i think the average of the individual meetings is more valid. I picked round numbers. You'll see that the number underneath is exactly halfway between the two sets of weekly numbers. That would be the result I'd expect. I suppose you could make an argument that you're more interested in weighting the stats to the larger potential - even if it's against the trend.

[scgallafent]

From the "just checking" department - how is the calculation being done?

My team didn't write the code, but I believe it is something like this:

Each Sunday that an attendance number is reported, throw that number into Bucket A.
Each Sunday that an attendance number is reported, throw the number of membership records into Bucket B.

At the end of the quarter, add up the total of Bucket A and divide it by the number of numbers in the Bucket, giving an average attendance number for the quarter.
At the end of the quarter, add up the total of Bucket B and divide it by the same number of number of numbers, giving an average potential number for the quarter.
Divide the number from Bucket A by the number from Bucket B.
Print all three numbers on the Quarterly Report.

From your spreadsheet, that is the equivalent of "Average from columns." Using that number is somewhat like asking "given the number of opportunities for ward members to attend sacrament meeting, how many of those opportunities were taken."

Hypothetical example for a quarter with four sacrament meetings:

Week 1 52 / 92 = 57%
Week 2 84 / 122 = 69%
Week 3 81 / 122 = 66%
Week 4 84 / 122 = 69%

Average attendance is (52 + 84 + 81 + 84) / 4 = 75.25 rounds to 75.
Average potential is (92 + 122 + 122 + 122) / 4 = 114.5 rounds to 115.
Percent attendance is 75 / 115 = 65%.

Looking at it another way, there were 458 ward-member-opportunities to take the sacrament; of those, 301 were taken. In this case, 66% of the opportunities that were available to take the sacrament were taken. There is a 1% difference due to intermediate rounding, but that can't really be avoided because of the way values are reported.