Your Church’s CCB Zip Code Dashboard

I’m back this week with a tutorial that is sure to get the wheels spinning in your head about your church and where its attendees and members live.

The purpose of today’s tutorial is to show you how to create two simple dashboards containing the following:

  • Number of people and percentage based on Zip Code
  • Number of people and percentage based on City, State (next week)

Why is this information important? Well, thanks for asking. 🙂

If your church is a growing church, no matter the size, then such reporting gives greater insight as to where attendees and members live and travel from to reach your church.

In addition, such reporting can be combined with your church’s surrounding city or region population growth based on city or zip code. This type of reporting can help assist your church in a number of ways, such as the following to name a few:

  • Visually put together a map of your community group locations compared to attendee/member locations
  • Determine location(s) where there may be a life group or community group need
  • Identify area(s) for expanding ministry or church planting

If you’re church uses paid advertising, then this tutorial may also help to determine where, location wise, your church to place your ad.

But enough future possibility talk.  Let’s get started in the present. 😉

Getting everyone’s data into a database

To begin this tutorial, we’ll use a previous tutorial: How To Backup Your CCB Data.

I’m not going to spend much time diving into the details of this section only because you have the tutorial to read and guide you through completion. I’ll highlight the changes you’ll need to make and provide you with the code examples.

This tutorial simply retrieves data from your respective CCB account and places it into a database. This action is performed so that you don’t exceed the daily API limit.

However, you may need to contact CCB to increase your daily API call maximum should you have more contacts than the API call maximum.

Nevertheless, you’ll build using the code based on the backup data tutorial. You’ll need to make some slight modifications to account for including the address, city, state and zip code fields when making CCB API call and the database table where you’ll store data.

Below is the updated code for creating the database table ccb_profile_data.

The following code below is the updated PHP code for backing up CCB data (notice it includes the add, city, state, and zip code fields):

Okay, so now you have your in a database/table and you’re ready for the next step.

Before moving on, you may want to spend some time learning how to automate this script. Automating reports using cron jobs allows for timely synchronization of CCB data for up to data reporting.

Retrieving and grouping zip code data from database

The last section taught you how to retrieve data from CCB and store it in a database.

This section will teach you how to retrieve data from the database table you stored your CCB data.

To retrieve data from a database table, we’ll use MySQL Select method.

One of the great features about MySQL is that it gives you the ability to sort your data programmatically.

What would take a bit of time and manual elbow grease to produce using Excel, MySQL can do it in style and with ease should you know what you’re doing.

You’ll unlock the MySQL secrets and fall head over heels in love with the gain efficiency (it’s my hope for you).

To create the zip code dashboard, we’ll use MySQL methods count, group by, and order by.

This is about all you need to know to start.

Retrieving the total number of CCB records from database

To retrieve the total number of CCB records used to create the zip code dashboard were going to use the following MySQL methods: mysql_query and mysql_num_rows.

And a reminder: you may have to use mysqli_query and mysqli_num_rows methods for PHP versions greater than 5.5.

Let’s start coding. Open a new php file in the text editor of your choice, naming and saving the file as zipcode-dashboard.php.

Don’t forget to add the necessary comments and includes at the beginning of the file.

Next, establish the SQL query for selecting all of the records in the database and returning the sum or total number of selected records.

After this step, establish another SQL query for selecting all records grouped by zip code in descending order, returning the distinct zip code and count of each distinct zip code grouping.

Now create a reportData variable and assign it’s value the following following text: <h2>Attendee &amp; Member Zip Code Dashboard</h2>.

Next, create a if else statement using the peopleNum variable.

If there are records present, then using the while loop prepare the zip code, and total number of records and percentage of total for respective zip code. The while loops through all records returned from the select query statement.

To determine the percentage, divide the zip code’s total count (i.e., $data[‘total’]) by the totalPeopleNum variable, multiplying it by 100 and then rounding to 2 decimal places using the round method.

Else if no records are present, then return the reportData variable’s assigned text value: No data to report.

Finally, echo the reportData and that’s it with this tutorial. It’s time to put it all together.

Put the code together and time to test

The following is what your code should look like in its entirety.

Save the file and view it via a web browser. That’s it! You’re now ready to view where attendees and members live as well as travel from to attend your church (see image below).

Church Zip Code Dashboard

Let me know if you have questions. If not, then I’ll see you next week for the second part of this tutorial:  Your Church’s City, Zip Code Dashboard.

Please comment if this tutorial has helped you.