BEST PRACTICES FOR AD-HOC QUERY MANAGEMENT IN BLACKBAUD CRM

Tight resources, short deadlines, and continuous improvement all present challenges to maintaining clean and orderly ad-hoc queries in Blackbaud CRM. Here are some guidelines to provide structure to a daunting task - pick and choose what will help you best in your organisation!

1. Naming conventions

Stick with simple naming conventions if you can. For queries with dynamic selections, I use:

Record type - Query name

If the query is a static selection, I append (static) to the name.

This results in something like

Constituents - Known age and over 80yrs (static)
Constituents - All donors
Constituents - All donors (static)
Revenue - First one-time gifts
Revenue - First regular gift
Marketing Efforts - Newsletter emails

In this way, if you need both a static and dynamic selection for a query, you can create the dynamic selection first, and then use that selection as the criteria in the (static) selection -- and they sort so that they are displayed adjacently. It is particularly useful having (static) selections available, as they will be faster than dynamic selections in most cases, but you will need to remember to add them to a Queue to be refreshed on a daily basis.

If you want to get really fancy and ensure that all of your static ad-hoc queries are being refreshed on a daily basis, you can do this by creating two Datalists. The first will show all static ad-hoc queries which refreshed today; and the second will show all static ad-hoc queries in the system. If there is a discrepency between the two numbers, you know that one of your ad-hoc queries is not refreshing daily.

Source view of type Business Process Status:

Source view of type Ad-hoc Queries:

2. Folder structure

Before deciding that you will use folders to manage your queries, acknowledge first that you can also view queries by category:

Whatever method you use and whatever folder structure you decide on will likely be influenced by your organisation and what is already existing -- if it ain't broke, don't fix it!

However, you will need to manage your selections, and it is very important to categorise these generically, as the same selection may be reused time and time again in multiple areas of the system. Consequently, it doesn't make sense to group selections by the purpose they were originally created for, whether it be the Universe of a Marketing Effort, an Exclusion, or some segmentation criteria. Selections are supposed to be generic.

Therefore, I recommend a structure similar to the following:

  • Selections (top level folder separating selections from rest of the queries)
    • Constituents
    • Revenue
    • Appeals
    • Marketing Efforts
    • ...

But for your selections that are used in Marketing Efforts, it is really important to tag this clearly in the Description of the query, because then you know that when you edit that query, the constituents who fall into your communications and marketing efforts will change.

3. Archiving queries

There will be a time when you no longer need to use a query, but it has been used in an activated Marketing Effort and consequently can not be deleted. There are other times that you will want to keep the filter criteria as reference, but mark the query in a way that clearly indicates it has been deprecated.

You can meet these needs by

  1. Creating an Archived category, and placing these queries into it.
  2. Tagging them with ARCHIVED - at the beginning of their name.

This will also mean that it won't show up in the top-level Selections folder in the Query designer, and instead will only exist under the Archived category -- preventing you from accidentally using old queries:

4. Temporary queries

Mark temporary queries as TEMP - at the beginning of the name, and place them in a TEMP folder or TEMP category. If you need to create a selection from these queries, then note where you have used that selection in the Description of the query, so that when you go to delete it, you know where it is referenced.

I hope these tips prove useful to you!

Addendum - gotchas

Query management has some gotchas that you may or may not be aware of.

  1. Selections can be created from queries, and referenced in other queries and other areas of the system. When you reference that selection somewhere (eg. in another ad-hoc query), then you cannot delete the original ad-hoc query until you have removed those references. If you use the selection in a Marketing Effort and then activate it, you cannot then delete that selection at all.
  2. Two types can be created: dynamic and static. They are not interchangable. If you need to add a selection to a Marketing Effort, for example, you need to use a static selection. These selections are refreshed on demand -- every time 'Calculate segment counts' is run on the Marketing Effort. If you reference a static selection in an ad-hoc query, it may not be up to date. However, if you reference a dynamic selection, then it will always be up to date, as it is refreshed every time it is used (but they cannot be used in Marketing Efforts or some other areas of the system).
  3. Ad-hoc queries can be of multiple record types, and therefore selections can also be of multiple record types. For example, you usually create selections of Constituents - but you can create selections of Revenue, Packages, Marketing Efforts, and many other record types.
  4. If you rename a selection and it is used in an Export Definition, then the Export Definition will throw an error next time it is used. The Export Definition just needs to be opened and re-saved, but be aware that the column order will change when this happens, so re-order the columns as necessary.
  5. Generally speaking, do not use square brackets for naming records in CRM, including queries, as they are special characters. If you tried tagging static selections with [static], you would have trouble selecting them later.