When I work with my clients on any issues related to data management, whether it’s selecting a new AMS, or just making the current system more effective for the association, one of the most common complaints I hear is something along the lines of: “Our querying/reporting is all messed up” or “Our queries/reports are wrong” or “Our queries/reports don’t work correctly.”
In almost all cases, the real issue is a fundamental understanding of how queries (and ultimately, reports) work within the database. Here are some of the most common complaints I hear, and how they can be addressed:
- “There are too many queries and reports. I don’t know which one to use.” Or “I don’t know what this query does.” This is a very common complaint, especially if the database in question has been in use for more than five years.The reality of queries and reports is they accrete over time. Different queries and reports are created for different needs, and staff changes over the years also contribute to new queries and reports being created.
The solution to this problem is twofold: First, catalog all of the queries and reports, identifying specifically what they do (i.e., what is being queried) and how those queries/reports should be used (i.e., their purpose).
Second, identify all of the queries and reports that you believe are no longer being used or are not useful/accurate, and hide them from all users. I often advise my clients to hide 80% or more of the queries and reports, without telling staff, and wait to see which reports or queries staff complains about missing. In many cases, there are no complaints at all, because staff hasn’t been using those queries or reports.
- “These queries don’t work/these queries are wrong.” This is one I hear quite often, and typically means one of two things:Either the staff person doesn’t understand what the query is really supposed to do, or the underlying query is actually set up incorrectly.
I see this most often with queries and reports that ostensibly are supposed to count members. Very often two queries will both be labeled “membership count” but are actually querying on different sets of data. As a result, the two queries do not match (i.e., result in different counts) and staff is befuddled and angry.
The solution to this lies in the cataloging outlined in #1 above. When the cataloging is done, it should include a review of the underlying criteria for each query or report and the description of the query or report should include what the query does and how it is reaching that result. So if it’s a membership count report, the catalog would explain that this query counts membership based on the membership flag, or the membership transaction, or whatever data element is being counted.
- “Our data is bad.” This is another common complaint, and is often supported by the claim that two queries that are ostensibly the same are returning different results (see #2 above). Digging deeper, we often find that the two queries are actually querying different data points. Thus it’s not the data that’s bad, but the misunderstanding of the two queries.However, it is often the case that the data is, in fact, bad. That could be a result of neglect (e.g., data points haven’t been updated in many years) or human error (staff or customer entered incorrect data).
In the first case (neglect) the association needs to establish business practices for keeping the data up-to-date, assuming there’s value in doing that. In the second case, data integrity reports, business rules programmed into the database, and documentation and training can address these issues.
The true value of a database is not the data it collects, but the ability to extract that data and apply it to something (e.g., improved marketing or communications). But if the data is bad, or the staff does not know how to effectively use the querying tools available, that value is dramatically decreased. Understanding your queries is critical to your long-term data management success.