Finding the Data You Want 102

Hey, we’re building off of that topic and selection filters 102. So in the very beginning, some of this is going to be a repeat everything builds on top of each other. It’s kind of like disassembling and reassembling car engine, it can only be done Read More

Hey, we’re building off of that topic and selection filters 102. So in the very beginning, some of this is going to be a repeat everything builds on top of each other. It’s kind of like disassembling and reassembling car engine, it can only be done one way. So everything new that we’re discussing is building off of yesterday’s topic. So yeah, let’s get into it. i My name is Sean Matera, I’m a training specialist here at DonorPerfect. And I am so happy to be doing this webinar with all of you. So let’s highlight everything that’s going to be discussed today. If you joined us yesterday, we discuss election filters one to one and today is going to be a continuation of that topic. And the first 10 or 15 minutes or so is going to be a quick review of key concepts previously discussed, because everything new that I’m bringing up today is going to be building off of old knowledge. Now the newest tool that you’re going to be learning about is compound filters, we’re gonna get into how to build them. And of course, you know, more importantly, when are you going to need a compound filter. But we’re also going to be getting into some simple calculations that you can use when you’re building a standard filter. And last but not least, have a little caveat here down at the bottom, when we’re in our live demo portion within DonorPerfect. It may contain features and fields that are different than your own. Everything within DonorPerfect is customizable. So my demo system might look a little bit different than yours. So to recap, what was discussed in selection filters, 101. Gonna go over some quick tips. First, before we even get started, it’s a good idea to know which records you want in the output, usually advise whether this is a verbal request or something that you got in an email, we’re going to have to translate that request for a data pool into a selection filter. So it’s usually a good idea to write everything out in plain English, before you even build your selection filter. And then while you’re building the selection filter, it’s going to be helpful to know the field name of interest that you’re looking for and the field type. What type of data is in there? Is it a date field? Is it a currency field isn’t a textbox, that just has words in it, it’ll be helpful to know that but there are hundreds of fields within DonorPerfect, maybe even more 1000s. I don’t know about that. But there are certainly a lot of them. I don’t have every single one puts in memory. And maybe you don’t either. But I’m going to actually show you today where you can find out more about those fields, just in case you don’t know it off the top of your head. And then once you select that field, we’re going to have to select how to compare it to the desired value. And we’re going to look into some examples for that. So first and foremost, describe what records you want in plain English. Maybe you want to see all of your board members and the results. Write it out. I want to see the constituent records identified as board members. Well, how is your organization identifying those board members? Well, most likely, it’s a flag. In that case, you would want to build a selection filter, where your flag is exactly equal to board members. Spell it out, it could have a lot of criteria in it. And putting in plain English on a little notepad or an empty word document it’s going to help. Secondly, you should know which fields that it is that you’re looking to identify and on which entry screen. You know, if you want to find everybody who volunteered four or more hours last last year, you’re going to need to know what fields are holding that information. And if you don’t happen to know where that information is being stored, the screen designer is going to be your best friend here you can find all the fields and DonorPerfect directly from the screen designer. And of course, we do have a field list in our knowledge base. This we didn’t cover yesterday, but let’s take a look at that real quick. Let’s hop in here. We’re gonna go to screen designer. From home, you can go to settings, the little gear icon at the top right and then go to screen designer. And then once you’re in here, you’ll have to pick the screen where that field is located. In this case, I wanted to maybe build a filter based off of volunteer hours. That one is on the other info screen. So I’m switching over to that screen and then from here. Here we go. It’s called hours work. worked in date worked, those are the fields of interest. If I were to pull such a report, I need to know what these fields are in order to build that filter. But, you know, screen designer has every field on every screen, including fields that you or someone else within your organization created. We also have within our knowledge base, a list of all the fields as well. So if you go to question mark knowledge base, it’ll take you to our knowledge base, where, right in this search box, you can search for any number of topics that will, you know, give you advice on what you need to do. But if you go to this little learn drop down menu, and then go to knowledge base, it’s going to break down everything in our knowledge base into little sections, including this little tab here for fields. That shows every single screen and all of the fields that are within that screen. So if you’re ever unsure, you can’t find it in screen designer, there is a little shortcut for it within our knowledge base. So once you have that field identified, you’re gonna have to do a little bit of basic math, a little bit of arithmetic. You know, in that earlier example, I want to find all the records where the flag is exactly equal to board member. Or maybe you find one, two finds all of your donors who have given a gift, where the amount is less than or equal to 1000, maybe you want to focus in on some of those smaller donors are greater than or equal to 1000. Or maybe you want to exclude, you would do a search to exclude, you know, maybe you want to do a mailing list, but you want to exclude everybody from a certain state, your selection filter would be state not equal to and then that state, all of this, we went over yesterday. But to bring in the new element that we have. Let’s talk about compound filters. Now imagine you need to run a report or create a mailing list. But your desired results would need two different selection filters. You’ve used those selection filters before, and they gave you the results that you want. But your end result is really to have a combination of two different filters. Now, you could run that report twice, with two different filters. But you would have to find a way to combine those results into one report outside of DonorPerfect. And there might even be some overlap between those two lists. You could do the same thing for a mailing list, you could use those two selection filters that contain the folks that you want for that mailing list. And you could do that separately, and you can get your separate lists. But again, there’s probably going to be overlap between those two lists. And instead of manually going in and removing any duplicates that you have there, there is a more efficient way of doing it by means of a compound filter. Oops, skipping ahead to some examples there. There we go. So In plain terms, simply stated, a compound filter is comprised of two save filters that you’ve already created, you’re then going to combine those two into one compound filter. And you have a few different ways that you could do that. Your first option would be to combine those two filters within and to find what records meets at that intersection. So you have your your two selection filters and you want to see everybody who applies to both filter one and filter two, you could create a compound filter, combining those two standard filters with an ends. Or perhaps you want a larger set of results. And you decide to combine two filters with or the compound filter you would build will result in records who apply to filter one, or filter to

or maybe you’ve identified a group that you want to exclude entirely from your results. You have maybe using a mailing list again, as an example. You have a great mailing list with everybody that you want to see. But you’ve identified based off those results, you know, as some of these folks I don’t want in here and you can narrow those down with a second filter. That second filter contains everybody that you You want to exclude, you can put those two filters together and have filter one, subtract filter two, so that you only have the records that you want in the results. Now, since a standard filter can only ever combine your multiple criteria with AND, OR, or sometimes you’re going to find yourself in a situation where a combination of two separate filters is required. And let’s take a look at that. And example here. So, if you are creating a list, what you want is a constituents who are board members, and your volunteers. So you could run these separately, you could run a report to show all of your volunteers. And you could run another one to find all of your board members. But there’s a little bit of overlap between these two. So you can combine them into one single compound filter, take your board members filter, combine it with and your volunteers filter, and the result isn’t going to have any duplicates in it. This light blue guy is in both of these results. But if you put them together into a compound filter, you’re only going to see the one that applies to both of them. And it will remove everybody else who doesn’t apply. Now, what if you wanted and another list a bigger group, you want to see all of your annual appeal donors who gave in 2020, who also live in Pennsylvania, or you want to include your board members. Now we have one person who falls into both of those categories, and we put them together into a compound filter. So we have one set of results, we’re not going to have any duplicates. We could have done this twice as two different reports. But there is overlap here, you can combine those two filters together into one so that you only get the results that you need. Now, let’s say in a slightly similar version, we want to see all of your annual appeal 2020 donors and your PA donors. But from that list, what you’d like to do is subtract all of your annual appeal donors in 2021 that live in PA. We want to take those folks who gave in 2020 and subtract anybody who also gave in 2021. So we’re looking at some lapse donors here. And you can subtract the results of the one filter from the results of the other. And just to get those folks that you want to narrow in on. So let’s take a look ups, I had some highlighting that was done here earlier. Let’s remove that. So let’s get into an actual example that we have here. We want to see, we want to use the history list and build a compound filter for all donors who have given at least $50 or more to last year’s appeal, but do not want any donor who has been contacted in the last six months. So this is a perfect example. We already have it written out in plain English here what our desired outcome is. I’m going to highlight the report that was requested for this. But in context of this webinar, what report or what data we see in the end result isn’t as report isn’t as important. What we’re going to focus on is how to get those results. So for our compound filter, we want to see all donors who have given at least $50 or more to last year’s appeal. But we do not want do not want any donor who has been contacted in the last six months. So looking at this request, I can actually break this down into two separate filters. We have one filter for anyone who has given at least $50 or more to last year’s appeal, but we do not want and any donor who has been contacted in the last six months donors who has who have been contacted in the last six months, that’s going to be our second filter. So essentially, what we’re going to be doing is we’re going to be building one filter, and then we’re going to subtract from those results. Any donor who has been contacted in the last six months, let’s do it, let’s get into it. And again, like I was doing yesterday, especially for a compound filter, what I’m going to do to build this is go to Settings, and then go to Filters. This is where all of the filters reside, you can find any filter that’s been created before, as long as it was given a name, it will be saved. But in this case, what I’d like to do is create a brand new filter, a compound involves two separate filters. So I’m going to start by creating one of those two. So let’s take a look at our example. Again, my first filter, I’m going to want to see anybody who has given at least $50 or more. So that’s a gift amount of $50, or more gift amount greater than or equal to 52. Last year’s appeal, last year’s appeal, we use the solicitation code for that. So that’s going to be my first filter. Alright, so looking for gift amount that’s on the gift pledge screen. And our amounts is let’s see greater than $50. But and a gift amount greater than $50 to last years appeal code, will the appeal is a solicitation so I can stay on the gift pledge screen. And then select solicitation. And let’s find it’s going to be exactly equal to. And let’s find our annual appeal code for 2021. It’s probably an active since it’s an old code. Here we go.

All right. So this is our main group here, folks who gave at least $50 To the last year’s appeal code, we can combine them with and, and something else that I’m going to do to make life easier for me too, is I’m going to put in the name part one, this is going to be part one of two. And this would be let’s make this 2021 $50 Plus to our AE code, we also have the option of sharing this filter amongst some of our users. I’m going to select it just so anybody can use this if they need to. And for my filter folder, I have one with my name on it. So that’s where I’m going to store it. So that will be our first standard filter one of two for the compound that we’re going to need. Let’s take a look at this request again. So we have the first part who has given at least $50 or more to last year’s appeal. And we’re going to be subtracting anybody contacted in the last six months, contacted in the last six months. So that would be anybody who has a contact transaction in the last six months, we can build that filter, going to click add new filter, we’re looking at contact activity. So anybody who has had a contact date in the last six months, so that would be greater than or equal to a date six months in the past, it would be March 8 of 2022. Now if I was to run this filter on a report on its own, it’s gonna give me anybody who has been contacted on or before, or I’m sorry, on or after March 8 2022. This is going to be the group that I exclude

our rights, I’m going to put it in my folder, I’m going to share it and I’m going to save it. So now we have our two constituents standard filters that we’re going to be working with and our compounds. So we can click on add new compound. Now when you get to building these compound filters, these drop down menus where you select it’s gonna be a big ol list because it Every single selection filter that you could choose from starting alphabetically first by the filter folder. But here we have the folder with my name on it. So I could find part one. Part 120 21. Perfect. And then how are we going to compare it to the second one will, we’re going to be subtracting. So the second drop down menu has our different operators here, minus any rows that are also in, subtract, that’s the one I want. And then in the third drop down menu, I can select my other filter. So in total, we have a little preview down here, this is going to be our master list here, part one gift amount greater than $50. And that gift was to the 2021 solicitation code. And we’re going to be minus Ng, anybody that applies to our second filter, anybody who has been contacted in the last six months. And now we have our mailing list. And I’ll put it in my folder again.

All right, there was a lot of clicking that was going on there. But it was a lot quicker for me to build it within the filters home or all of them live, the end result here was to run this on the history list. So let’s go over to that report. Report Center, let’s find our history list.

And because we had just built the selection, filter, and selection filters, filters are sticky, it traveled over with me from where I built it. And now I can run the report with our mailing list. Now, in this example, I’m working in a demo system. So we might not necessarily have any results that apply here. But that’s how Oh, here we go. You know what it was, I didn’t include no male names, something to be aware of here. And here we go. It’s taking its time generating it, this is a good sign. The bigger the list, the longer it’s going to take to generate. So we’ll let it do its thing. And while that report is thinking, Oh, here we go. There we go. That’s a good look, unless now. And here we go. We have some of the annual appeal folks that were given before, but haven’t haven’t been contacted this year. So that was one way of creating a compound filter. And I’ll show you why it’s a little bit trickier to do it within a report, but it’s still doable. It’s just a little bit more work. So in this second example, what we’re looking for is to use the donors names, addresses and phone numbers report and build a compound filter for all donors who have given 100 or more gifts since the beginning of 2020, or they have volunteered since the beginning of 2019. Perfect This is in standard English, we want to use the donors names, addresses and phone numbers report. That’s the one that we’re going to run it on. And who do we want to include, for all donors to have given $100 or more gift since the beginning of 2020, or this is if we’re working with two different standard filters here, this is going to be Hey, how we combine the two or they have volunteered since the beginning of 2019. So for our two filters that we’re going to need here. The first one is going to be for anybody who has given $100 or more gift since the beginning of 2020. And our second filter is going to be any of those folks who have volunteered since the beginning of 2019. So let’s hop back into DEP here. Let’s find the report that was requested. Gonna go to Reports and Report Center. Do you want to save any of the options here? No, we can leave it blank for next time. And here we go donors names, addresses and phone numbers

are right, and let’s remove everything that was here previously, we’re going to start fresh, we’re going to click on Clear values are going to click on this little blue X next to the selection filter that was saved there last time. And now we can start fresh with our new standard filters, and put them together into a compound. So for our selection filters, let’s build our first one, we’re going to click on Apply, and we’re going to click on add new filter. So let’s see here, who have given $100 or more since the beginning of 2020. Okay, pretty straightforward. Let’s build that filter.

And it always comes in a little pop up, here we go. So we’re going to find the gift amount that’s on the gift pledge screen. And we’re looking for greater than or equal to $100. That’s our one criteria for the first filter. And for our date range, since the beginning of 2020. So let’s find date of gifts greater than or equal to beginning of 2020.

Alright, so this will be the first group of folks that we’re looking for. Again, I’m going to call this part one. And let’s see 100 plus cents, beginning of 2020. Make sure it’s organized, put it in my right folder. So that is part one of two, we’re going to need a second filter here. But in order to create it, what I’m going to have to do is remove the one that I just built. So next to that standard filter that I just built, I’m going to click on the little blue x there are you sure you want to remove it, I can tell it Yes. And then I can apply. And then I can add my new filter. So for this one, we’re looking for anybody who has volunteered since the beginning of 2019, all of our volunteer information is going to be on the other screen. And we’re looking for the date worked fields, which isn’t that one, it’s not one of my favorite fields. So I’m going to select all fields. And then here’s the one date worked. And we’re looking for anybody who has volunteered since the beginning of 2019, aka greater than or equal to 2019. And this will be my part two.

Gonna give it a short name, volunteered since 19. And we’ll put it in a good folder. So I know where to find it later. And I’ll make sure to share it. So now we have our two base filters that are going to go into our compound, we still have to combine them together. So let’s remove that one. Are you sure? Yes, it has a name. So it will be saved. That I can click on apply, add new compound filter. And then we have our three familiar drop down menus. The first drop down menu, I’m going to select my first filter. Anybody who gave greater than or equal to 100 since the beginning of 2020, or I want to see those volunteer folks. So I’m going to select union or, or my second filter get those volunteers in here as well.

All right, and now we have our second compound filter that we could use. But you could if you didn’t want to build a compound filter. There are some other ways that you can get creative with your selection filters. So when you’re building your selection filters, it’s always going to land you on this screen here where it shows you the interpretation of that selection filter and it puts it into DonorPerfect speak, which is in the coding language of C SQL. And it’s always going to look a little something like this. In this case, in this example, we have a city that is exactly equal to Horsham. I mean, if we needed to, we could go in here. And we could change this to be a different city if we needed to. But we could also do a little bit of arithmetic. With some of these, let’s say, we wanted to find everybody whose combined totals for last fiscal year, let’s highlight some of this. This is the field for last, or this is the field for last fiscal years total. And we want to add that to the current fiscal years total. And we want to find everybody whose total combined for those two fiscal years is greater than or equal to 500, you could do that directly within the filter criteria by putting in a little mathematical symbol depending on the way that you want them to combined for your output. So let’s take a take a look at another example here, if you want to find donors whose last year is giving last year’s giving total has increased over two years ago. Over two years ago. So how could we do that? Well, there’s already field in your system that are adding up the cumulative totals for different date ranges, we have one for last fiscal year. And we want to say that last fiscal year, was greater than what they gave two fiscal years ago. So they started giving a little bit more, how could we do that within a single selection filter, because when we’re looking through those different boxes, there isn’t a way or at least there isn’t an apparent way for you to compare those two fields together when you’re building that filter. But you can trick it and add those criteria at the very end. And I’m actually going to use a different report for that so that we can take a look at those results. And spot check what the outcome is. In this case, I’m going to use the export to file report. And again, we have our sticky filter that’s traveling over here. And let’s remove it. And we’ll create a new one. So we’re trying to find donors who last year giving total has increased over what they gave in the previous fiscal year. So we’re going to start by including a little bit of a placeholder, we’re going to find the field for last fiscal year, which is on the main screen. So let’s find let’s see last fiscal year, doesn’t look like that is part of my favorites. So let’s look at our all fields.

Here we go last year y t d. And let’s say that that’s greater than, I mean, ultimately, what we need it to be is last year YTD greater than two years ago YTD that’s what we’re looking to generate here. But when I’m building this filter at this stage, at the very least, once I’m here, I’m not going to be able to just pop in in box number for the other field that I want to compare it to it just won’t allow it at this stage, at the very least. So what I’m gonna do is I’m going to put in a little placeholder, I’m entering in zero, it could be anything there. But in this case, I’m going to put in a zero that’s going to serve as a placeholder that will eventually be replaced with that other fields. But I still need to add another value here. Because what if two fiscal years ago, they didn’t give anything? Well, then anything that they gave in the following fiscal year would count as gains. But really, we just want to focus on folks who did give two fiscal years ago and then went on to give more, those are the folks I want to focus in on right now. So we want to make sure that they actually did give in that one fiscal year. So we’re going to include that as well. Alright, so now once I’m at this step, these aren’t read only values, I can actually click directly into this box. And I can remove that zero that I just put in there as a placeholder. And now I can tell it that I want to see an increase in giving.

And if you’re ever having trouble coming up with a good name, sometimes I’ll just put the criteria right in there, so I know exactly what it is that I’m working with. And then we’re going to include no male names in this one. And I’m also going to include an export template that has some of these fiscal year totals in the results, just to spot check my accuracy here, export templates, this is going to be covered in more detail at a later time. But I know that this particular one fiscal year giving, this one has the fields that I’m interested in.

And what we’ll see that all of these records in the results have in common is that their previous fiscal year total, as represented by this column, is greater than what they gave two fiscal years ago. So that’s one way that you can get fancy with some of these selection filters when you’re building them. Let’s remove our highlighter here. Take a look at another example that we could do. So, again, we’re ultimately going to be running reports here. But in context of this webinar, really just the filter that I’m mainly worried about building. So in this example, use the giving history with complete donor profile report, and build a selection filter for donors whose last year giving total has increased over two years ago, that one we’ve already done. Using that same report, build a selection filter for donors, whose largest gifts, is the same as the most recent gifts. And their count of gifts is more than one. We can actually do this right within a standard filter.

And hopping back into my report here, we can remove the last one that was used and create a new one. All right, so we’re looking for the largest gift amount, that field is maximum gift amount that’s on the main screen. Let me check all fields, it’s not one of my favorite IDs. Let’s find maximum gift amounts. And I’m looking for it to be equal to exactly equal to the last gift amount. Now I can just type in the field for last gift amount. So again, I’m going to just enter in a placeholder of zero. And the second half of this request was for to only have folks who have given more than one gifts. So there is a nother field. Let’s all it depends on what system you’re in. It might be count of gifts, it could be gift counts. It could be number of gifts. Let’s see here’s the one number of gifts and number of gifts is greater than one. All right, then we can click on continue. We have our to base criteria here. And what we’re looking for is the maximum gift amount is represented by the field name here is equal to their most recent gift amounts. And the field for the most recent gift amount is last contrib AMT, bit of an abbreviation for last contribution amount

And maybe this is a one off, if you don’t give it a name, if you’re only using it once, you don’t necessarily have to save it. But you can if you want by just giving it a name. And then the results would be reflective of that. But you can also include mathematical expressions within these filter criteria as well. So let’s say that you want to see the sum of a donors current year giving their last year’s giving, and two years ago giving, if it’s going to be 1000 or more, well, within one standard filter, you can take all of those different Calculated Fields, all of those totals, and add them together and only show the results. If they’re greater than or equal to 1000. You’re not gonna be able to do this when you’re building the filter. But in the end result, you’ll have that opportunity there to make your edits, insert those other fields, and then just get the results that you want. Let’s take a look at that. And again, this is going to involve a little bit of knowledge about your fields and what their field names are. When you’re entering this SQL criteria, it will need you to know some of those field names, but you can always go to screen designer. So let’s find our year to date fields.

See, fiscal year today, that’s the one. And ultimately, we’re looking for a combination that’s going to be greater than or equal to 1000. So we’re going to have a little bit of empty room that we’re going to have to work with here.

But what we’re looking for is a combination of this fiscal year, plus the total from last fiscal year. That’s the field for last fiscal year and two fiscal years ago.

So we’re going to be adding this fiscal years total plus last fiscal year, so it’ll plus two fiscal years ago. And we only want to see those folks whose total of those three is greater than or equal to 1000. And then, when we preview it. And this report, the export template has all of those fiscal years included in it, which is always best practice, if you’re filtering for a specific field, just a spot, check your results, make sure that you have those fields in the results like we have here. And what all of these have in common is if you were to add up the totals from these columns, all of them are going to be over 1000 At the very least.

Our rates, let’s see, we’ve done this one already add together three different fiscal years and only show folks who gave 1000 or more. But let’s do another one here. Let’s use the giving history with a complete donor profile report and build a selection filter for donors whose current giving has increased by at least 5% over last year’s giving. So we’re going to be comparing two different fiscal year totals, but we want one of them to be at least 5% greater. How can we do that? Well, you’re not going to be able to do that typically, if you’re just building it from scratch, but in the final step, you will be able to make some adjustments there to account for the arithmetic. So we’re gonna leave that report. Let’s go to the other one.

Then we’ll remove that old filter that we don’t need anymore, let’s create a new one.

Or right, and we’re looking for last fiscal year, or last year YTD to be greater than. Or you know what? We’re looking for it to be greater than what it was versus the previous year. So again, we’re going to use a little placeholder here. And what we’re looking for is last year, greater than 5% more. So we’re looking for this to be 5% more than the current fiscal year to do that, and simply put in an asterix, which is read as a multiplication here, and to times this by 5% 1.05.

Now when we run this, that’s what everybody will have in common. Last fiscal year was 5% greater than the previous fiscal year. And it’s looking like, this is going to be a pretty big list. It’s taking its time it’s thinking. Our array right, so just to recap everything that we discussed today. Compound filters are essentially joining two of your save filters, so that you can see them in one list. You can use those selection filters to compare and contrast, you can put in different multiplication signs in there, you can divide them, you can add them together, or you can subtract them. All right, and all of these options will select the targeted data that you’re looking for. Now, a lot of the times in these webinars, we’ll do a poll right off the bat, but I saved it for the end, just in case, if anybody has any examples that they can think of any filtering questions, any compound filters that you’ve run across, you can pop those into the question box, we can work them out together.

All right. It doesn’t look like anything’s coming in today. So that’s good. That’s good. But if you do you have any questions moving forward, feel free to reach out to the Support Department. If you’re ever having any issues with filters or reports, and check in with the training department. See if you have any training hours available. All right. Thanks, everybody, for joining us today. This has been selection filters. 102. And we’re not going to be doing a webinar next week. Usually we do them Tuesday, Wednesday and Thursday. We’re going to be skipping next Tuesday. So our next one will be Wednesday. Hope to see you guys there. Thanks.