KevMcCord

Raking Data

Yesterday I raked a lot of leaves.

Raking leaves and analyzing data share a number of similarities.

You don't rake leaves unless there are enough leaves to make it worth your while.  You don't analyze data unless you have enough of it to make it worth your while.

If you don't rake often you'll get blisters and you probably won't have the best tools.  You may even avoid the job.  If you don't analyze your data regularly you may find it hard to begin and painful to accomplish.

Gathering the data is hard work, but it isn't quite half the work.  You still have to do something with it.  Getting leaves in piles is not the end of the job.

If you have a lawn mower that bags debris you can cut your grass and get the leaves up at the same time.  If your information management systems are well designed analysis is built right in.

No one rakes leaves efficiently the first time.  There are tricks you learn from trail and error and from watching others.  Someone who rakes leaves for a living expends less energy and gets more done.  Analysis tools like Google Analytics are designed for websites with millions of hits a day.  You can leverage a tool like that even if you have a little church website.

There is a beautiful yard under there (you think).  You may find out the grass is dead when you pick up the dead leaves.  You always begin data analysis with an assumption.  Some people don't want to rake the data and find out the assumption is wrong.

In the end the analogy breaks down since data is useful stuff and dead leaves aren't very useful.  In both cases though you can get a sense of accomplishment when you look at the finished product and you bring order to chaos.

I have a lot of data to rake this next few weeks.  I need to know what I'm going to do with the data before I get it all piled up.  I need to list the questions that need answers, confront my assumptions, determine my goals, and mock up the final product before I start raking it together.

Contribution Statement Draft 1

This weekend I have been working on a re-do of the Oasis Church contribution statement.   The goal was to deploy the report to SQL Server Reporting Services.  This is a browser based reporting tool that provides quite a bit of interactivity for the person who needs to run reports.  The report is now independent of whatever ChMS we happen to use.    I just have to change the underlying code to go with the new system's database. 

Here's how draft #1 of this statement turned out. 

Download STMT.pdf

The most important goal here was to get the statement to our Report Server so that a staff person could easily run quarterly statements that included a personal letter from our pastor.  It is also important for that same staff person to be able to quickly print a one-off statement for someone down the road.

This sample does a poor job of showing some of the dynamic content you can add to the statement.

1. Capital Campaign Pledge recap section for the donor to see the balance of any active pledges.
2. Change the statement from English to Spanish based on donor giving patterns 
3. The concept of a single family statement for mom, dad, and children under 12 is introduced saving paper and postage.  The statement design is smart enough to honor a request by couples who wish to receive separate statements.

The statement is designed to fit one sheet of paper front and back.  I estimate only 1% of statements at year-end will exceed a single piece of paper.  They are printed and handled separately.  None of the mid-year statements exceeded a single piece of paper. 

Each time I create a report like this I learn tricks to speed things up, but this particular project took about 15 hours over 3 days.  I imagine if I had to do a different but equally complex report tomorrow the things I've learend would save me 5 of those hours!

The next step is to have software that can save the PDF file to each donor's record in such a way that this person can access it online.  That's my hope anyhow!

The Matrix-Not The Movie

The solution to making a Contribution Statement personal, easy to read, and easy to print turned out to be a Matrix element in SSRS.  The matrix is basically the same as a Pivot Table.  It dynamically creates the rows and columns you need for each statement's detailed list of gifts.

This is nice when there are 30 or 40 ways people COULD give to the church.  Each person typically gives to only 2 or 3.  A matrix assures each person can get the 2 or 3 columns they need with column headers that are very specific to how they gave.

This little bit of layout in the designer...

Returns a customized table for each contributor (personal detailed greyed out)...

Note that the family above had two individuals with recorded gifts, but only a single statement.

The down side is that some people do give to 6 or more things in a single year.  This results in 9+ columns and their statement can break to two pages because it exceeds the width of an 11 x 8.5 page.  This requires some careful thinking.  Not only do I need to know when someone will get an extra page because they gave more than 63 times, but now I need to know if they gave to support more than 7 different things.

I'm happy to say this wasn't as bad a problem as I at first thought.

Out of 2,000+ statements in 2007 only about 30 were going to break onto a 2nd page due to either too many gifts (lines) or too many purposes (columns).  Some creative parameters allow the operator to print those statements separately.

Here's a picture of the parameters in use in my new CN Statement:

Effective Churches & Data

A client has given me simple request.  They want me to help them to understand how effective churches use their data and database systems.  I had two immediate thoughts.

• What a great open-ended question!
• What a scary thing to try and answer all by myself!

You don't get paid for wimping out.  So, I'm going to tackle this question.

So, on to topic of the effective church and its use of data.

Here are some of the principles that come to mind immediately. 

In regards to data, effective churches...

1. Understand what motivates people to give them data.
2. Get the right data to the right people.
3. Keep the data fresh.
4. Ask good questions about the data.
5. Understand the real world well enough to interpret the data.

In regards to database systems, effective churches...

1. Work hard with the systems they have even while looking for better tools.
2. Understand the true cost of ownership of the systems.
3. Copy proven strategies in similar churches when implementing new systems.
4. Document their approach and work towards organizational consistency
5. Prove something works and is needed before automating it.

In regards to data-related personnel, effective churches have...

1. Executive awareness of data
2. Ministry Leaders who examine and value the data
3. Central administration of data
4. De-centralized entry of data when appropriate (at the ministry level).
5. Outside perspective on their data

P.I.G. Reports Via Email

My friend Ginny often used the word "PIG" as short for "Pretty is Good" when discussing whether or not we would go the extra mile to make a report presentable.

These days my favorite way to make a report pretty is to deploy it through SQL Server 2005 Report Services (SSRS).  Earlier today I shared a free report what was just basically an SQL query listing next month's staff anniversaries.  Below I have a picture of that information as it appears on our report server.

The report contains personal info so I have to be limited in how much I can show, but after only 20 minutes that report looks nice enough and is set to be emailed automatically to the folks who need it.  Did you catch that last point?  I won't think about this report again until someone tells me it didn't arrive when they expected it to or unti they ask for something to be changed.

To be sure this could be a section on an Intranet page.  These folks could also access the report directly on the report server, but I would rather just set it to email them at this point.  My goal with this report was to spend as little time as possible since the audience is only 3 people.  There are any number of improvements possible, but I just need a version 1.

This is important:  The tough part is getting the query written.  Someone who has experience designing reports in another tool can fairly easily make the switch to SSRS.  A person who is comfortable learning new software programs and has access to YouTube and Google can work through making reports even if SSRS is their first environment.  Books that teach by example are easy to find.  Don't let the fact that you make the reports wtih Microsoft Visual Studio 2005 scare you. I made this one with the built-in wizard.

The Database Dilemma

How do we track the information we need for ministry when...

• The church isn't what it used to be.
• The family isn't what it used to be.

What thoughts popped into your head when I said the church isn't what it used to be?  Was it positive or negative?  How about with the family?

From the standpoint of our Church Management Systems (ChMS) it doesn't matter whether the changes are positive or negative.  The only question from the database's perspective is whether we are capturing the information we need to do the ministry we are called to do.

1. The church might be in many locations.
2. Ministry leaders now want direct access to information technology tools.
3. The nuclear family is now just a loose "household" (pun not intended, but intentionally left in)
4. People and churches both may have rejected the membership concept.

Ask yourself how your Church Management is making assumptions that are out of sync with your culture.

1. Does it present an ideal of what a famiy is rather than a reality?
2. Does it assume we meet once on Sunday morning for Bible Study then for Worship in just one location?
3. Does it make us call people members when we don't have membership?
4. Does it assume users will need 2 days of training?
5. Does it work best if the pastor just asks their secretary for information
6. Does it give you an infinte number of options for mailing, two options for emailing, and none for text messaging?

Let's face it.  There is probably a big disconnect between how you think about your ministry and how the folks who designed your software were thinking when they created it.

Your software may have an old perspective and your church may have more flavors of families than Baskin Robins.  Your ministries may refuse to get aligned around a single way to do things.  You may need a new ChMS, but I promise you the journey to that day and the switch to a new system will be easier if you simplify things in the ChMS you have now.

Let me make a few examples:

1. Store just one address per person/family.
2. Store just one email per person.
3. Don't let anyone create any types of phone numbers other than home, cell, work, fax, pager.
4. Implement new functionality on the basis of a proven approach in use by a real church, not on the basis of a sales demo or a vague mandate from leadership.  Get comfortable pushing back for clarity!
5. Don't worry about the odd person in the family who isn't really realted to them, but attends with them.
6. Leave 18-year-olds in the household until someone says they need to be moved.
7. Don't purge people unless you have crazy performance issues.
8. Don't try to track the results of a church-wide survey your pastor threw together on a whim.
9. Don't try to manually inventory every aspect of a person's life then spit out three jobs (sorry ministry positions) they might like.  It won't work nearly as well as just calling the person and emailing them a few jobs you actually need people to do this week.
10. Inactivate and ignore ALL records that show no signs of activity in a long time.  If they show activity not so long ago, print off their names and tell the pastors you will have to inactivate them if you don't hear back from someone they are still involved.  Do this monthly for 3 years and I bet something might eventually happen.
11. Focus on the acuracy and consistency of the records of people who are ACTUALLY attending.
12. Come up with just one simple way to define participation in ministry.
13. Let one ministry create a single group for EVERYONE they serve and don't sweat the fact that in real life they are breaking into small groups or teams.
14. Sit people down and ask them to look at the data with you.  Ask them to tell you what has meaning for them and how they use it now.  Focus on what has meaning to them.

Sometimes we spend our time working on the exceptions or causing people to follow complex procedures that just don't have that much added value.  It isn't that our goal is bad. It's just that it is nearly impossible to accomplish the goal given the assumptions of the tool and the reality of our changing ministry environment.

Sometimes we can push for a comprehensive and uniform process, other times we just need to push for adoption of basic standards.  Don't let the exceptions frustrate the bigger picture of getting things done.  Don't let the exception that rules your thinking scare the user away from the tool.

I'm not saying anything goes, I'm saying that we ask for the least amount of compliance possible and focus our efforts on oversight, auditing, and documentation.  Let the issues and exceptions surface.  Champion consistency and simplicity in your database.

This is worth repeating...

Give up some of the nuances you think might be important when you believe they may diminish the acceptance you need to succeed.  You may need a new ChMS, but I promise you the switch will be easier if you simplify things in the ChMS you have now.

SQL Tips

I have several friends out there that write custom reports for Shelby v5.  This post is for those who are venturing into the world of SQL.  The query below has little value in itself, but it contains some useful buildng blocks I use time and again for outputing information so it is ready to print.

Download sql_tips.txt

Included tips:

1. Printing the individual photo file name.
2. Stripping time from date fields
3. Getting age from date of birth
4. Getting  just the primary email
5. Translating gender letter to word (M = Male)
6. Translating Marital Status letter to word (M = Married)
7. Determining if a record is an organization
8. Translating Record Status from number to word (0 = Active)
9. Translating Record Type (Module) from number to word (0 = Membership)
10. Translating Family Position from number to word (1 = Spouse)
11. Joining tables so spouse or head will print as "married to"
12. Printing couple names together (John & Jane Smith)
13. Printing the business phone with extension

How often do people give these days?

Sometimes I make up questions to apply new things I need to learn as a Database Administrator.

So, I came up with  a question no one is paying me to answer.

My goal is to determine the frequency with which people at Oasis Church give.  Now, don't panic, I was just looking for macro trends.  I'm not going to be all big-brother with the data.  I don't know names here, just numbers.  I'll refer to "giving units" since sometimes this is one person, sometimes a family, and sometimes an organization.

To be considered, the giving unit had to have made 4 gifts in the past 365 days one of which was within the past 90 days.  This filtered out most occasional givers.

This particular report depended on setting an average days between offerings for each giving unit then categorizing them by their frequency. 

The rest of this info is only for the DBA's out there.  I used a Common Table Expression (CTE) in my query which is only available in SQL Server 2005.  It allowed me to create a temporary result set that I can reference inside a query.  The CTE also used a SQL Server 2005 function called RowNumber() which allowed my query to compare the date of each gift to the gift immediately before it determining the number of days between gifts.  I then averaged the period of time between gifts for each giving unit to come up with an expected frequency of giving.