So I’ve seen discussion here on tools for displaying metrics data (Microsoft Power BI, Google Data Studio, Tableau, etc.), but I am wondering if anyone has had any experience with some sort of already-existing software for consolidating/storing all the metrics we might be tracking and a central user-friendly area for staff members to enter that data. For example, we might put attendance data into Church Metrics, but some ministry that wants to track “positive” feedback from a class or the number of “grads” from a class who return as a leader doesn’t really have a central area to do this. Does such a thing exist (without coding our own thing; some internet research isn’t making me optimistic), or should I just be looking towards the glorified Excel document or some database route?
Hey David,
I build multiple weekly dashboards using some of the visualization software you mentioned; but for the specific cases you mentioned, I think it will be difficult to beat the efficiency of a simple Excel workbook. The key will be setting up your workbook to track this data accurately over time.
Any time I’m working with a single dataset, I create an Excel workbook that has one worksheet for the raw data and an additional sheet for a pivot table. First ensure that your data is clean and that you have created all the additional features you may need. When you’re happy with the data, transform it into a Table (Ctrl + T), and from that table create a pivot table (Alt + N + V) on a separate worksheet. This setup works really well for tracking data over time because new data can always be pasted to the bottom of the dataset and Excel will automatically add the new data to the Table and all connections.
Do note that our church does not use Church Metrics, so I do not know if you may be able to find a built-in workflow that works better for you. Also, if the data set becomes too large (over 1M data points or 300k rows), I would recommend a python or SQL script instead…should only take a couple lines of code.
Hi David,
I’m familiar with a couple of tools - but they’re pricey. I’ve used Vena. It uses excel, but is basically a SQL database that integrates with excel via an Excel plugin - giving users an easy to use interface. The nice thing is that you’re able to pull data from multiple sources into a single excel sheet, have version control, access by multiple people at the same time, etc. However, you can expect to spend at least $15k per year on this.
The other tool I’ve got experience using as a data warehouse - specifically for pulling data into Tableau - is Alteryx. Again not cheap. $5195/per user per year.
@derrickg & @Jeff_Ward–thank you! This is very helpful and probably saved me tons of research hours. Looks like there’s a good chance I’ll just be fiddling around in Excel! Thanks for sharing your expertise.
As a follow-up, do either of you have a preference for visualization software? Any of the ones I mentioned or something else?
I haven’t really explored much so my preference is only based on my familiarity. I’ve used Tableau and therefore, it’s my preference. Maybe Derrick’s opinion would be more insightful since he’s used more.
@davideaton3 Honestly, your choice of viz software will likely come down to budget. If you have the budget, by all means go for Tableau as @Jeff_Ward mentioned–it is industry-standard for a reason and you will not have issues finding resources and training online. It also provides a bit more structure from the start compared to other options I’m going to mention.
For a minimal budget, I recommend PowerBI. It’s my daily-driver and the free version may be all you need. However, it is incredibly important (and not-so-simple) to figure out how to create and use a Date Table from the start, and you probably will not like the DAX language PowerBI uses for creating custom metrics.
If you have no budget and no time to spend learning new software, don’t underestimate the capabilities of Excel. Using the workflow I outlined in my previous comment, it is only one extra step to create a custom visual by adding a pivot chart to your pivot table sheet. Do this same workflow (data>pivot table>chart) for each visualization and copy/paste each chart to a summary/dashboard page. As long as you transformed your data to a table at the start, any new data that you add will automatically feed through the entire workflow, so you can paste new data, click Data>Refresh, and then go grab some coffee while your dashboard updates itself. 
TL:DR
I would use Excel until you find a need to learn macros/developer tools or it lags due to large data sets, then move to PowerBI. Use PowerBI until you find the need to learn DAX to create custom metrics, then move to Tableau or incorporate Python or R into your PowerBI workflow.
Great info! Thank you both again 