Analytics In Raiser's Edge NXT: Basic Queries With ProspectPoint Models
You have the ProspectPoint predictive models in Raiser’s Edge NXT and you’ve tried using 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 models using Query.
If this is your first time reading a post in the “Analytics in Raiser’s Edge NXT” blog series, I recommend that you start by reading:
- “The Best Reporting Tool” blog post--compares and contrasts the functionality of Lists, Query, and Export to group and analyze by Ratings
- “Basic Lists With ProspectPoint Models” blog post
If you are new to ProspectPoint predictive models--including Annual Giving Likelihood (AGL), Major Giving Likelihood (MGL), Planned Giving Likelihood (PGL), and Target Gift Range (TGR)--I recommend Blackbaud University’s Target Analytics: Basics of Modeling training session and the Knowledgebase articles on “What is a Target Analytics Likelihood Score” and “What are the Target Gift Range values”.
WHERE TO START
I always recommend beginning your Ratings analysis in the Raiser’s Edge NXT web view Lists tool because of its visually appealing view and accessibility for the average user. If you find that Lists did not meet your needs because you need to display additional fields or include more complex criteria, then head into database view Query next!
This blog post highlights my top 4 tips and tricks for using the ProspectPoint predictive models in Raiser’s Edge NXT Query.
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 RATINGS
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 (e.g. Blackbaud Analytics – WP) and Rating Category (e.g. Major Giving Likelihood – WP) combinations.
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, Target Analytics 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: ADDING CRITERA
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.
If you add a Likelihood Rating, then you will most likely use the operators “greater than or equal to,” “greater than,” or “between”. Here are a couple common examples:
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 Analytics – WP, you will commonly use the “equals”, “starts with”, or “one of” operators.
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:
|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
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].
This is a situation where you may be able export the query results into a spreadsheet directly from Query. The catch is that you can only display one-to-one fields (i.e. where there can only be one value in the field) not one-to-many fields (e.g. all of their constituent codes) or your results may display duplicates.
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! On the output tab, 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.
Using Query to group by and/or analyze your constituents’ ProspectPoint predictive models is a great choice when you need more control over how the Ratings display or to include complex criteria. View the ratings on the Query results tab or export them into a spreadsheet for further review.
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! Be aware that often Lists and Query provide the best view of Ratings with little to no spreadsheet cleanup. In my next blog post, "Export With ProspectPoint Models" I will share additional tips and tricks for analyzing the ProspectPoint predictive models using Export.
Comment below if you have additional questions or tips to share with the Community!