Analytics in Raiser's Edge NXT: Basic Queries With ProspectPoint™ Models
Published
You've tried using your the ProspectPoint™ custom predictive models in Blackbaud Raiser’s Edge NXT® Lists, but it did not quite meet your needs. Now what? This blog will review tips and tricks on how to group by and report on these ratings using Query.
Note: This post was updated to incorporate the Raiser's Edge®-ResearchPoint™ integration enhancements releasing in Q2 2023.
ProspectPoint™ custom predictive models are a great way to identify and prioritize key prospects for annual, mid-level, major, principal, and planned giving fundraising efforts. The most common models are Annual Giving Likelihood (AGL), Major Giving Likelihood (MGL), Planned Giving Likelihood (PGL), and Target Gift Range (TGR). These models are commonly bundled in the Advanced Wealth Insights package with your Blackbaud Raiser’s Edge NXT subscription, but it can also be purchased separately. Refer to this Knowledgebase article or the Blackbaud University on-demand “Basics of Modeling” training for more information about custom modeling.
This blog post highlights my top 4 tips and tricks for using the ProspectPoint predictive models in Blackbaud Raiser’s Edge NXT Query.
TO QUERY OR NOT TO QUERY
Reporting on Ratings has its benefits and challenges in Lists, Query, and Export, which I summarize in “The Best Reporting Tool” blog post. The Query tool may be your database administrator’s go to tool, but before you head into the database view, start by determining if web view Lists will meet your grouping and reporting needs. With its succinct display of specific Ratings columns and easy filtering, web view Lists is one of the best ways to report on Ratings in Blackbaud Raiser’s Edge NXT! Learn more about these tips and tricks in the “Basics of Lists with ProspectPoint Models” blog post.
Tip: If your Likelihood model ratings criteria does not have the proper operator to use in List filters, it is because the rating is formatted as a text data type. To fix these ratings, consider re-syncing your ratings from ResearchPoint to Raiser's Edge after the May 2023 integration enhancements to append new ratings with updated naming conventions and correct data types.
If Lists either doesn’t have the right columns available to display or you need more robust criteria, then it is time to head to Query!
BENEFITS & CHALLENGES
Let’s review some of the key highlights of Query.
Criteria
Query is a robust grouping tool to use practically any Raiser’s Edge field in the criteria. The sky is the limit as the criteria you can include! You can also create complex criteria with AND statements, OR statements, and parentheses, where we may be more limited in Lists. Also unlike Lists, a query’s criteria options are not limited by the Rating’s data type. This can be especially helpful when querying on Ratings formatted as text fields, for instance Likelihood ratings synced prior to the May 2023 Raiser’s Edge-ResearchPoint integration enhancements.
Output
Let me remind you that Query is a grouping tool and is not intended as a reporting tool, however when it comes to reporting on Ratings, we may be able to stretch the functionality beyond its purpose.
In Lists and Query, you can choose to display a specific Rating in the output. For example, you may want to see each of the custom predictive model ratings in their own, unique column for easy sorting and filtering in Raiser’s Edge NXT or a spreadsheet. Let me caution you about your query output. If you add one-to-many fields (e.g. constituency codes), then your results may display duplicate constituents, causing you more manual clean up in Excel. Make sure to keep your output to one-to-one fields (e.g. first name, primary constituency code, etc.) as much as possible.
After you sync your ratings from ResearchPoint after May 2023, you will not need to rely on Query for your ratings output to download into a spreadsheet because Export will give you control to extract specific ratings with the new naming conventions. We will cover more on this subject in the "Basics of Export with ProspectPoint Models" blog post.
TOP 4 TIPS YOU NEED TO KNOW
Tip #1: Static vs. Dynamic Query Format
In normal circumstances, I always recommend starting with a dynamic query. Depending on your goal or purpose, you may decide to create a static query in order to start a new web view List with this group of records. A good example of this is when your Likelihood Rating is formatted as a text data type field and you cannot include the correct operator (e.g. greater than or equal to) in the List. I discuss this scenario in the “Basic Lists With ProspectPoint Models” blog post.
Remember you can always start your query as dynamic and save as a static query later.
TIP #2: Finding the Right Rating
I remember struggling to find the right database field on the criteria and output tabs when I first learned Query. In my experience, once you know where to find the field, the query is not as challenging as it first appears.
Start by reviewing a constituent record’s Ratings tile or tab. Look for the rating(s) you want to include in your query. Make a note of the Rating Source and Rating Category combinations. Use the table below for examples of the naming conventions after Raiser's Edge-ResearchPoint integration enhancements in Q2 2023:
If you have similar ratings from a different year, for instance Major Giving Likelihood from 2019 and 2020, then also make a note of the most recent Rating Date to include in the criteria.
Note: As a best practice, Blackbaud recommends only keeping the most recent ProspectPoint predictive model ratings in Raiser’s Edge. This will also make grouping and analyzing your ratings much easier!
Now create a new Constituent query. To add the Rating to the criteria and/or output tabs, navigate to Prospect (if you have the RE:Search module) > Ratings > Specific Sources > [choose the Source name] > [choose the Specific Source and Rating Category combination]. Here is an example:
Tip #3: Criteria Selections Vary By Rating Configuration
Once you navigate to the specific Rating (see Tip #2) on the criteria tab, double-click on the field. You will be prompted for the operator and value next.
Likelihood Ratings
If you add a Likelihood Rating, then you will most likely use the operators “greater than or equal to,” “greater than,” or “between”.
If your Likelihood rating is configured as a text data type, you will need an extra line in your criteria to include constituents with a rating value of 1000. These constituents will not appear in the results if you only use “greater than or equal to” a value. Your criteria would look like this: Major Giving Likelihood – WP greater than or equal to 701 OR Major Giving Likelihood – WP equals 1000.
Synced ratings after the May 2023 integration enhancements and imported ratings using the TA Admin import tool are formatted as number data type ratings so you can simply use the criteria Major Giving Likelihood greater than or equal to 701.
Target Gift Range Rating
This criteria window will vary based on how your Target Gift Range Rating is configured in your database. If the Rating is configured as a text field, which is common with the Rating Source of “Blackbaud – TGR” or “Blackbaud Analytics – WP,” you will use the “equals”, “starts with”, or “one of” operators then type the score value(s).
Tip: Save the list of Target Gift Range values where you can quickly refer to them when building Lists and Queries.
Note: Target Gift Range $100,001+ may have a space between the 1 and the +. Spot check your results to confirm that you have the exact value as it was synced to the constituent record.
If the Rating is configured as a table, which is common for the Rating Source of Blackbaud Analytics’ Custom Modeling Service, you will be able to select from a table of options.
Put It Together
Once you know how to add Likelihood and Target Gift Range Ratings criteria, you can combine them with AND and OR operators and parentheses to make your queries more complex.
Here are some examples of common queries to create in your database or at least give you an idea for where to start:
For even more complex queries, consider adding some of the following fields into the criteria:
Tip #4: Display Ratings in Output/Results with Custom Headings
I saved my favorite tip for last! You may be aware that you can customize column headers in Export, but did you know you can in Query too?! My mind was blown the first time I saw this!
Add any Rating to your results by navigating to the output tab and follow the same process as on the criteria tab: Prospect (if you have the RE:Search module) > Ratings > Specific Sources > [choose the Source name] > [choose the Specific Source and Rating name combination]. Now, right-click on the Rating Category name and choose “Column Heading…”. Type in a clear and concise column header so you (and your colleagues) have a nice header when you view the results of the query or download it into a spreadsheet.
SUMMARY
Using Query to group by and/or analyze your constituents’ ProspectPoint custom predictive models is a great choice when you need complex criteria. If your ratings continue to have the same Source, consider using Query to also extract select ratings into a spreadsheet when required.
If Query still does not quite meet your output or reporting needs, such as you would like to include one-to-many fields in the results, then you should try Export next. I recommend using a query with an export parameter file to extract ratings if you synced your models from ResearchPoint after May 2023. Check out "Export With ProspectPoint Models" for additional tips and tricks for analyzing the ProspectPoint custom predictive models using Export.
Comment below if you have additional questions or tips to share with the Community!
This blog post highlights my top 4 tips and tricks for using the ProspectPoint predictive models in Blackbaud Raiser’s Edge NXT Query.
TO QUERY OR NOT TO QUERY
Reporting on Ratings has its benefits and challenges in Lists, Query, and Export, which I summarize in “The Best Reporting Tool” blog post. The Query tool may be your database administrator’s go to tool, but before you head into the database view, start by determining if web view Lists will meet your grouping and reporting needs. With its succinct display of specific Ratings columns and easy filtering, web view Lists is one of the best ways to report on Ratings in Blackbaud Raiser’s Edge NXT! Learn more about these tips and tricks in the “Basics of Lists with ProspectPoint Models” blog post.
Tip: If your Likelihood model ratings criteria does not have the proper operator to use in List filters, it is because the rating is formatted as a text data type. To fix these ratings, consider re-syncing your ratings from ResearchPoint to Raiser's Edge after the May 2023 integration enhancements to append new ratings with updated naming conventions and correct data types.
If Lists either doesn’t have the right columns available to display or you need more robust criteria, then it is time to head to Query!
BENEFITS & CHALLENGES
Let’s review some of the key highlights of Query.
Criteria
Query is a robust grouping tool to use practically any Raiser’s Edge field in the criteria. The sky is the limit as the criteria you can include! You can also create complex criteria with AND statements, OR statements, and parentheses, where we may be more limited in Lists. Also unlike Lists, a query’s criteria options are not limited by the Rating’s data type. This can be especially helpful when querying on Ratings formatted as text fields, for instance Likelihood ratings synced prior to the May 2023 Raiser’s Edge-ResearchPoint integration enhancements.
Output
Let me remind you that Query is a grouping tool and is not intended as a reporting tool, however when it comes to reporting on Ratings, we may be able to stretch the functionality beyond its purpose.
In Lists and Query, you can choose to display a specific Rating in the output. For example, you may want to see each of the custom predictive model ratings in their own, unique column for easy sorting and filtering in Raiser’s Edge NXT or a spreadsheet. Let me caution you about your query output. If you add one-to-many fields (e.g. constituency codes), then your results may display duplicate constituents, causing you more manual clean up in Excel. Make sure to keep your output to one-to-one fields (e.g. first name, primary constituency code, etc.) as much as possible.
After you sync your ratings from ResearchPoint after May 2023, you will not need to rely on Query for your ratings output to download into a spreadsheet because Export will give you control to extract specific ratings with the new naming conventions. We will cover more on this subject in the "Basics of Export with ProspectPoint Models" blog post.
TOP 4 TIPS YOU NEED TO KNOW
Tip #1: Static vs. Dynamic Query Format
In normal circumstances, I always recommend starting with a dynamic query. Depending on your goal or purpose, you may decide to create a static query in order to start a new web view List with this group of records. A good example of this is when your Likelihood Rating is formatted as a text data type field and you cannot include the correct operator (e.g. greater than or equal to) in the List. I discuss this scenario in the “Basic Lists With ProspectPoint Models” blog post.
Remember you can always start your query as dynamic and save as a static query later.
TIP #2: Finding the Right Rating
I remember struggling to find the right database field on the criteria and output tabs when I first learned Query. In my experience, once you know where to find the field, the query is not as challenging as it first appears.
Start by reviewing a constituent record’s Ratings tile or tab. Look for the rating(s) you want to include in your query. Make a note of the Rating Source and Rating Category combinations. Use the table below for examples of the naming conventions after Raiser's Edge-ResearchPoint integration enhancements in Q2 2023:
| Rating Source (Before) | Rating Category (Before) | Rating Source (After) | Rating Category (After) |
|---|---|---|---|
| Blackbaud Analytics - WP | Target Gift Range - WP | Blackbaud - TGR | Target Gift Range |
| Blackbaud Analytics - WP | Annual Giving Likelihood - WP | Blackbaud - AGL | Annual Giving Likelihood |
| Blackbaud Analytics - WP | Major Giving Likelihood - WP | Blackbaud - MGL | Major Giving Likelihood |
| Blackbaud Analytics - WP | Planned Giving Likelihood - WP | Blackbaud - PGL | Planned Giving Likelihood |
If you have similar ratings from a different year, for instance Major Giving Likelihood from 2019 and 2020, then also make a note of the most recent Rating Date to include in the criteria.
Note: As a best practice, Blackbaud recommends only keeping the most recent ProspectPoint predictive model ratings in Raiser’s Edge. This will also make grouping and analyzing your ratings much easier!
Now create a new Constituent query. To add the Rating to the criteria and/or output tabs, navigate to Prospect (if you have the RE:Search module) > Ratings > Specific Sources > [choose the Source name] > [choose the Specific Source and Rating Category combination]. Here is an example:
Tip #3: Criteria Selections Vary By Rating Configuration
Once you navigate to the specific Rating (see Tip #2) on the criteria tab, double-click on the field. You will be prompted for the operator and value next.
Likelihood Ratings
If you add a Likelihood Rating, then you will most likely use the operators “greater than or equal to,” “greater than,” or “between”.
If your Likelihood rating is configured as a text data type, you will need an extra line in your criteria to include constituents with a rating value of 1000. These constituents will not appear in the results if you only use “greater than or equal to” a value. Your criteria would look like this: Major Giving Likelihood – WP greater than or equal to 701 OR Major Giving Likelihood – WP equals 1000.
Synced ratings after the May 2023 integration enhancements and imported ratings using the TA Admin import tool are formatted as number data type ratings so you can simply use the criteria Major Giving Likelihood greater than or equal to 701.
Target Gift Range Rating
This criteria window will vary based on how your Target Gift Range Rating is configured in your database. If the Rating is configured as a text field, which is common with the Rating Source of “Blackbaud – TGR” or “Blackbaud Analytics – WP,” you will use the “equals”, “starts with”, or “one of” operators then type the score value(s).
Tip: Save the list of Target Gift Range values where you can quickly refer to them when building Lists and Queries.
$1-$50
$51-$100
$101-250
$251-$500
$501-$1,000
$1,001-$2,500
$2,501-$5,000
$5,001-$10,000
$10,001-$25,000
$25,001-$50,000
$50,001-$100,000
$100,001+
$51-$100
$101-250
$251-$500
$501-$1,000
$1,001-$2,500
$2,501-$5,000
$5,001-$10,000
$10,001-$25,000
$25,001-$50,000
$50,001-$100,000
$100,001+
Note: Target Gift Range $100,001+ may have a space between the 1 and the +. Spot check your results to confirm that you have the exact value as it was synced to the constituent record.
If the Rating is configured as a table, which is common for the Rating Source of Blackbaud Analytics’ Custom Modeling Service, you will be able to select from a table of options.
Put It Together
Once you know how to add Likelihood and Target Gift Range Ratings criteria, you can combine them with AND and OR operators and parentheses to make your queries more complex.
Here are some examples of common queries to create in your database or at least give you an idea for where to start:
| Name | Query Criteria |
|---|---|
| Annual Giving Likelihood (AGL) >= 501+ | Annual Giving Likelihood >= 501 |
| Annual Giving Likelihood (AGL) >= 701+ | Annual Giving Likelihood >= 701 |
| Annual Giving – Best Prospects | Annual Giving Likelihood >= 701 AND Target Gift Range one of $1-$50; $51-$100; $101-$250; $251-$500; $501-$1,000 |
| Major Giving Likelihood (MGL) >= 701+ | Major Giving Likelihood >= 701 |
| Major Giving – Best Prospects | Major Giving Likelihood >= 701 AND Target Gift Range one of $1,001-$2,500; $2,501-$5,000; $5,001-$10,000; $10,001-$25,000; $25,001-$50,000; $50,001-$100,000; $100,001+ |
| Planned Giving – Best Prospects | Planned Giving Likelihood >= 701 |
| Dual Major and Planned Giving Best Prospects | Major Giving Likelihood >= 701 AND Planned Giving Likelihood >= 701 |
For even more complex queries, consider adding some of the following fields into the criteria:
- Assigned Solicitor (assigned vs. not assigned)
- Latest/Greatest Gift Details (compared to their Target Gift Range)
- Total Giving (compared to their Target Gift Range)
- Proposal Status or Classification
Tip #4: Display Ratings in Output/Results with Custom Headings
I saved my favorite tip for last! You may be aware that you can customize column headers in Export, but did you know you can in Query too?! My mind was blown the first time I saw this!
Add any Rating to your results by navigating to the output tab and follow the same process as on the criteria tab: Prospect (if you have the RE:Search module) > Ratings > Specific Sources > [choose the Source name] > [choose the Specific Source and Rating name combination]. Now, right-click on the Rating Category name and choose “Column Heading…”. Type in a clear and concise column header so you (and your colleagues) have a nice header when you view the results of the query or download it into a spreadsheet.
SUMMARY
Using Query to group by and/or analyze your constituents’ ProspectPoint custom predictive models is a great choice when you need complex criteria. If your ratings continue to have the same Source, consider using Query to also extract select ratings into a spreadsheet when required.
If Query still does not quite meet your output or reporting needs, such as you would like to include one-to-many fields in the results, then you should try Export next. I recommend using a query with an export parameter file to extract ratings if you synced your models from ResearchPoint after May 2023. Check out "Export With ProspectPoint Models" for additional tips and tricks for analyzing the ProspectPoint custom predictive models using Export.
Comment below if you have additional questions or tips to share with the Community!
News Blackbaud Raiser's Edge NXT® Blog
12/16/2020 10:00am EST
Leave a Comment