How many queries is too many queries?

Options
Sara Burtis
Sara Burtis ✭
Second Anniversary Name Dropper Photogenic First Reply
in Blackbaud Raiser's Edge NXT®: General Discussion

I'm new to RE/RENXT, but not new to database management. My current org is in the process of pulling lists for our year-end appeal. I'm currently looking through the instruction manual for this project, and I am being asked to run 70 separate queries to compile three segmented mailing lists.

Is this… normal? Am I in over my head to suggest that there is a much easier/less time consuming way to get the information we need? I would love to know what your organizations do, and what your year-end queries look like.

Thanks!

Tagged:

Comments

  • Alex Wong
    Alex Wong ✭✭✭✭✭
    Ninth Anniversary Facilitator 4 Name Dropper Photogenic

    @Sara Burtis
    First thought is no, there shouldn't be any reason why 70 queries are needed to do this.

    But will need more detail critiera of your pull to be exact

  • Sara Burtis
    Sara Burtis ✭
    Second Anniversary Name Dropper Photogenic First Reply

    @Alex Wong
    I don't know that I could even summarize what we're trying to accomplish with the instructions I already have! I can send the instructions to you if that would be helpful?

  • Alex Wong
    Alex Wong ✭✭✭✭✭
    Ninth Anniversary Facilitator 4 Name Dropper Photogenic

    @Sara Burtis
    probably not a good idea to send me.

    what you need to look at is from a experience database person perspective on their instruction.

    For example. if you have 1 constituent query that criteria on records that has gave $XXX in 2022, then another query of $XXX or $YYY in 2021, etc. Then it does not need to be 2 queries, just 1 query playing with AND and OR condition in the query screen.

  • Joe Moretti 3
    Joe Moretti 3 ✭
    Facilitator 2 Second Anniversary Name Dropper Photogenic

    @Sara Burtis Sounds COMPLETELY INSANE, much be a upper management request who know so little about databases. In my 20 years I have never heard of such a thing that would require that many queries. Sounds like a complete time waster created by a complete time waster. My two cents.

  • Marie Stark
    Marie Stark ✭✭✭✭✭
    Ancient Membership Facilitator 3 Name Dropper Photogenic

    @Sara Burtis That seems very excessive! Are you using an export, or trying to export from all these queries one by one?

  • Sara Burtis
    Sara Burtis ✭
    Second Anniversary Name Dropper Photogenic First Reply

    @Marie Stark

    The process starts with creating a query that separates constituents from one area of our state.

    The first query is to pull all constituents who live in that area and have a specific constituent code (AB1)

    The next query is created to remove staff from the first query… (Staff)

    Then the next query is used to subtract all staff from the first query… (AB2)

    Then the next query is used to create a list of specific industry codes… (Industry Codes)

    Then the next query is used to subtract those codes from the second query… (AB3)

    You can kind of see where I'm going here. The procedure, as it is written out for this specific group of constituents, requires 33 queries.

    Would I be completely off base to assume that there are also data entry issues that would cause someone to create a process this time-consuming?

  • Dariel Dixon 2
    Dariel Dixon 2 ✭✭✭✭✭
    Seventh Anniversary Facilitator 4 Name Dropper Photogenic

    @Sara Burtis This is absolutely ridiculous. And a complete waste of time. I think you need to determine what is really being asked for in a way that makes sense.

    The most queries I've ever used to run something was 6 or 7 at most. And to be honest, that was just to get a technical limitation.

    EDIT: Looking at the directions you have been given, there is clearly a better way. Queries can be written with more than one criteria at a time. This is very elementary to say the least.

    But I would imagine after whittling these queries down to the group you are looking for, maybe you should create an attribute and just search for that. Because what you are asked to do is crazy. I sure hope the mailing is successful after so many steps.

  • Valerie Nguyen
    Valerie Nguyen ✭
    Ninth Anniversary Name Dropper Photogenic First Reply

    @Sara Burtis

    My approach would be to ask for the description of the “target population or audience” that they want to obtain, then come up with your own logics to get the list of constituent IDs, knowing where the data gets stored. The output details can be generated/pulled after you have the population.

    In general, use Queries to pull population, then Exports to obtain output. Or, this can be done in NXT.

    Many queries can be consolidated by either rearranging the “criteria“ clauses, or temporarily code a group with an attribute. From your description of the queries, they mirror steps someone would manually take if it’s done in Excel.

    Technical constraints by design is not helpful for sure ?

    Hope all goes well. Best.

  • Elizabeth Johnson
    Elizabeth Johnson ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic

    @Sara Burtis I think the most I've ever done in my 20 years is 10 and it has probably been a good 15 years since I've done more than 4. I'm expected to segment our appeals between 20 and 26 different ways (we are a school and have different challenges for parents and alumni) The only thing that differs in the letters (aside from the ones translated to different languages) is the P.S. The easiest way I have found to do this is to build an export that looks at all the variables to take into considerations and set the packages from that document. It takes a while to run I usually start it before a meeting or lunch. After I set the packages I import it into RE. Works like a charm.

  • Madeleine Holdsworth
    Madeleine Holdsworth ✭
    Tenth Anniversary Facilitator 2 Name Dropper Photogenic

    @Sara Burtis
    Agree with Dariel and others here. Ridiculous. If you are new to the organization this is probably not the time to question their appeal methodology but either the queries can be streamlined or the process by which the appeal segments are determined needs review. Are you being instructed to just run all of these queries, or has there been a discussion about the year end appeal segmentation? Also with that many queries there must be a lot of deduping needed. Situations like this lead me to conclude that the database manager is not being involved in these discussions. I think you need to find out the segmentation goals for the appeal and write your own queries and then use Export. You definitely need to have a discussion with someone and take control of the situation going forward.

  • Catherine Burns
    Catherine Burns ✭
    Tenth Anniversary Facilitator 2 Name Dropper Photogenic

    @Sara Burtis
    It sounds like the number of queries is largely for segmentation purposes. I personally prefer to do most of the segmentation in Excel using the active constituent code(s), and summary fields on number or amount of gifts in recent years. You can put criteria on gift type, coding etc. on these summaries.

    If you are confident using Excel for segmentation then you should be able to reduce the number of queries so you are using them mostly for selection. My most complicated mailings are usually two inclusions queries merged where I am unable to use the OR criteria to combine them. And then make one exclusions query which I subtract from the merged inclusion query with another merge.

    Then I export the result using Export and segment in Excel. Further exclusions can be made in Excel if they were not simple to add to your exclusion query.

    Then import the appeals/packages for the selected people onto their records.

    If you find you still need a lot of queries and merged queries and do not need to edit the criteria each time then you can create a queue to refresh the static queries so you do not need to open and refresh them individually.

Categories