Export the Data You Want

Good afternoon, or good morning, perhaps if you’re outside of Pacific zone, but my name is Arlene Berkowitz. I’m a member of the training team here at DonorPerfect. And I have the pleasure of working with you on the webinar called export the data Read More

Good afternoon, or good morning, perhaps if you’re outside of Pacific zone, but my name is Arlene Berkowitz. I’m a member of the training team here at DonorPerfect. And I have the pleasure of working with you on the webinar called export the data you want. Alright, so we’re going to talk about exports. Well, first, what we’re going to do is explain what we mean, mean from the weather PDF. And then we’re going to talk about a word that is used in different places, the Word templates. But there is a very specific use of the word template in today’s webinar that we’ll talk about what that means. We’ll talk about the ones that come with on a perfect called the default templates. But really, what we’re going to focus on today are these that you and or your colleagues will create called User Defined templates. All right. To that end, we’ll also talk about the template types that you can build. And they very important, rather short list of best practices, things to keep in mind or stay away from. Alright, so that being said, Now, let’s dive right in. So from the weatherbeeta, what does export mean? It means to format data so that it can be used by another application or software. So however, it comes out whether it comes out as a text file, or a RTF, or whatever the case may be, well, it’s fine, it could be used by another application. Specifically, what we’re talking about is making a file from DonorPerfect in a way that another program can use it. So that DonorPerfect and excel are kind of different sets of eyes looking at the same data. All right. Now, in terms of what when we say Export, it’s implied that it’s going to be to Excel. So there are many places that you’ll see Excel mentioned in DonorPerfect or presented on the screen. You may see the Export to Excel as a drop down from running reports. In addition to that, we’ll have export to Word or Export as PDF. And then there is an option called Export Using template. Let me just briefly explain the difference between these two. When you see Export to Excel, it is literally taking a snapshot image of the report as is and putting it into an Excel file. If it’s a report that has a total at the end or a summit the end, keep in mind that if you were to use that and delete rows, your sum or count will be updated because it’s going to put this image in a file with XLS extension, but it will not bring over any formulas. The other option with some reports, you may have the ability to export using a template. This is what we’ll be talking about today in terms of what are these different templates. This gets you the data in a call its raw format, as what we would call a CSV file that you can still sort and use if you want to design a pivot table or charts should you want to go to that extent. Okay. In some places, you might see these buttons you want to necessarily all three together, but it might say Export to Excel that will also give you a CSV file. Export Mail Merge file will give you a CSV file and generate letter receipts found in the receiving area will export or download the data used for thank you letters or receipts. And that also will put the data in a CSV file. Who can tell What CSV stands for. So notice these three buttons, whatever they’re called, will do the same exact thing. Anybody familiar with that? acronym of CSV?

All right. And we have an answer. Excellent. Thank you so much. Maybe it means Comma Separated Value, excellent Teresa to perfect. Now, essentially, the difference between Excel and CSV is the fact that it the data is stripped down to not have any formatting, whether it’s colors, whether it’s font size, or font type, or bold and or italics or underline, or anything like that. Okay, so Excel can read it, but it’s not exactly its best friend, because it says, Well, you’re not a true Excel file. No, it’s not. But we can manage it. And should you want to format anything, you can then save as an Excel, spreadsheet or workbook to then save any formatting that you want. All right. Now, in terms of an export template, this is what you might see. If it uses an export template, we’ll have a drop down with one, possibly two. Or if you’re in an organization that’s been using DonorPerfect for a while, there may be several. Alright, so now we get into what is an export template. So when export template equals the columns in Excel, remember that every spreadsheet is going to have two things, that columns and it will have rows, in terms of what columns you would like to build, or create or have in Excel, that will be defined by the export template that you use, or built. Now the nice thing about it, and why we call these user defined templates is that hopefully, you will use templates that have only the fields that you want, that you need in Excel. If you have happened to use what’s called the default financial or the default mail, merge, expert templates, that will work most of the time, it may not have some additional fields that have been added. But it will have approximately 275 columns. Now while that will be a spreadsheet, it probably has 200 or more columns that you do not need. So in stead of having extra columns, you’ll create templates. Why? Because the data will therefore download and it’ll create that CSV file faster than creating one that has 275 columns. And probably born that you’ll be familiar with is therefore you don’t have to remove 200 250 Whatever columns out of Excel. That sounds appealing, doesn’t it? Yes, it does. Okay, so if that sounds very appealing to you, great, we have the answer. So there are two places, essentially, to create these export templates. One of them is what I would call export templates, library or central location that’s going to be found with the top right, which is known as settings and simply go to the export templates in the menu. But in several places in the software, you have the ability to add an edit a template and that either has the button Add or Edit or there’s a plus sign or an A pencil, which mean add an edit perspective like either way, it doesn’t matter where you design them. It does matter how you design them and their purpose and all The Alcoa contents that go into it. So let’s explore this in a little more detail. All right, hopefully you can see this. When you build an export template, there are very various components to it. Number one, that is on the left is the name of the template. And we’ll talk about this in the best practices in terms of how you should name your template, but please give it a name. Then, to the right of that, you will identify what type of data will be in the report. Now, probably the majority of the export templates that you, your colleagues or your predecessors have created will be either main bio, or gift pledge, export. However, you could, as we’ll see on the next slide, create export templates to use with the other. When I say other the remaining entry screens. You’ll also choose the fields that will be used in ascending order for the sort. So in this case, it defaults to last name, you may leave it there to essentially get you an alphabetical list. Or if it was based on good information, maybe would want it sorted by the gift date as a different choice. And as you know, it doesn’t matter too much. Because if it goes into Excel, you can always change the sort there and give it a secondary or tertiary source while you’re at it. Then what you’re going to do is, depending on the type that you select in the drop down, that will determine the fields that are available. On the right will be the fields that will actually be included in your export template. On the left are the available fields to select from that you can add to your template. Once you have that, and there’s also a in the best practices, we’ll talk about rearranging the sequence here. But if you leave it as is perfect, you still have an export template will work. And then you just click Save and Close Up at the top. Alright, straight forward. And this is really going to be used a lot in your reporting. Okay, let’s now elaborate on the different types of export templates. I’ll go back. And what we’re talking about is right here, what are the different choices that you have? So if you pick or it selects for you main bio export, also known as mail barge. Well, that’s because it is used for mailings. But that’s not the only reason. It’s found in the listings, export to file for sure. And some financial reports allow you to export using an export template. And at a minimum, you want to use that type of template when doing a mail merge, whether you are sending it to an outside vendor to do the mailing for you, or you’re doing it in house. Okay, the way to remember that it’s for a mailing is that the first three letters of the word mail are the first three letters of the word main. That’s how I remember it. Another way to remember or to distinguish this from some of the others is that a main bio export will produce one row per constituent that meets your filter. Unlike unless you happen to have a duplicate records. Okay, but one row where as a gift pledge export, will give you one or more rows of the gifts or pledges that meet your filter. The main buyer will only give you one assuming you have no duplicates in your database. Okay, this type of export template is used when you are producing acknowledgments from the receipts area.

From the Report Center listings, export to file, it is also available, and most of your financial reports because you want to get the CSV raw data about gifts or pledges. Alright, so these are the two types that you will use most of the time. But let’s say that you have information on the other info screen about volunteer hours. But say you have information on the contact screen about grants. Then we have other types that are available, or you want the emails that are on the address screen, you’re going to use the other address expert, right. And then there is one, which is really a report because this can’t be edited is the gift pledge summary. These options are only available in the Report Center in listings, export to file. Again, this one being used. When you have data on the other screen, like volunteer data, or classes that your constituents have attended the contact screen primarily you’ll you might want to export out data about brands. All right, one more slide. And then I’ll be working and Donna perfect for the remainder of the webinar. All right. So I want to thank you for coming to this webinar, not because we’re over, but because I want you to understand and know of the best practices and things to avoid. So one of them is named the template, what it is, what kind of data is in it, as opposed to what it’s being used for. In other words, it’s best not to use the filter as the name. Okay. So hopefully spring will be here. I mean, officially, it’s going to be coming here next week. If you are sending a spring appeal, then what I would keep I would suggest is you want to use a template that hopefully is already in your system called Basic mail mailing info, no need to create another template that is labeled spring appeal 2023. Because essentially, all you need to get that mailing out will be the basic mailing info. If it were information on the gift screen, maybe you were presented with the opportunity to get an Excel spreadsheet of gifts from board members in 2002 and 2020. Okay, that sounds like a great filter. If you created an export template and named it board members gifts 2022 Dash 2023. If you did that you probably would be I’ll say inclined to only use it once or twice as opposed to using it more often. Because if you name that name, date of gift, amount and solicitation that can be used in any time period. And for any group of donors, not just limiting it to board members. Okay, so those illustrations were either the filter or the specific name of the mailing that I that I intend to use. Alright, so try not to do that and said just generically, it’s this right. This is the file for the mail house, or this is the file for the executive directors stewardship review. Okay. Hopefully, if you don’t a perfect database was created recently in the last couple of years. There are two templates already included. One of them is receding. The other one is I just mentioned the basic mailing info. Those are the most widely used templates, because you’re doing acknowledgments, hopefully, through DonorPerfect and you’re hopefully also doing mailings. At some point during the year in your system, those should be named with it just at the very beginning. That way, they will float to the top and not appear in the letter B, or with the letter R for receding. So, not everything needs to have an asterisk, you could use it after it’s I’m sorry, instead of the asterisk. If that’s already used, you might want to put a one dash, male house data or a two dash, fine finance reconcilement. So that doesn’t go into the EFS and appears towards the top of the list. Okay, because otherwise, it will appear based on the alphabet and numbers and special characters for that part better special characters come first in the alphabet, then come numbers, then come both letters. And there is a primary sort field. So look at that, and see if the primary sort matters, for instance, will be suggested to sort on the last name, if you want to change it before DonorPerfect, great, or it doesn’t matter. And you can always sort it in Excel. But keep that in mind. All right. And what a great little benefit this is, is that let’s say the narrative from the main screen was one of the columns that you were exporting. Instead of doing this exercise in exporting it and always taking the narrative and moving it always over to the right, and the very last view if the right, organized or drag and drop the fields, so that the narrative is at the bottom of the list in the template in order to get to the right most position. All right, I’ll explain what I go in and show you about templates. So any questions on the terminology or anything that I presented so far on the slides, because the rest of the time I’m going to be working in DonorPerfect, and we might finish a little early today. Alright, so in divorce light. Here is the place in settings, export templates, where you can see all of the templates created. And you can create new templates, you can edit any of the user defined templates that were created. But there are other places as I mentioned, in mailings, mail merge, for instance. Wait for it, wait for it. There we go. Here you have the word add new template, here is the Edit Template. So you get the words and you get the pencil whichever one makes sense. Or when I go into reports and listings, you might want to make this a favorite. If you’re going to use this often or if you have been using it often. Make it a favorite under export to file. And notice the buttons here add new template or Edit Template. So it does not really matter which of these you use bike depends, just make sure that you know what kind of template you can create from each area. Alright, so just to see all of the templates, I’m going to go to the wheel export templates. I like working here because this gives me a better view of everything that’s out here. Okay. So, notice here we have numbers, that means they will float to the top of the list before starting the alphabet with a B etc. Okay, and the way we got here is under the wheel at the top right, Mail Merge templates. And this is the entire library of your export templates. You can review anything that is here. If as I mentioned, your organization has used DonorPerfect for a while. Maybe you don’t have to create another template because there might be something here already for you. Okay. So there may be multiple pages. This is something that might be in your system already. And I’m going to recommend that all you need to do you don’t have to Attach it other than put an asterisk at the beginning, Save and Close. And now it floats to the top in its respective type of main bio. All right. Okay. So I’ve got two exercises that I want to use as an export template, I want an export template, or I need an Excel spreadsheet. So when you know that you are the person who needs this output, they say they want an Excel spreadsheet. That should translate in your head to say, ah, Excel spreadsheet means export template, that’s the most direct way to get to the output in Excel. Okay. So let’s say the request comes in from your executive director and or development director. And I want to look at donors who maybe haven’t given and 2022. So they want to look at some statistics and determine, Okay, are we gonna call this donor, or maybe this donor, I’m going to send an email, maybe that donor, I’m going to do a video clip, by email. So there’s different things that your director or directors might do, depending on

I’ll say this specific gifts of the donor, alright. So that being the stage, either they you might have, these are some things that you could use. So what I’m going to suggest this, we add a new template, okay, probably not something that is going to be used on a daily or weekly basis. This might be once or twice a year. Therefore, I’m not going to proceed it with an asterisk or a number. But instead of giving it the name of donors who did not donate in 2022, that is my filter that I will be using, along with this export template, please click in the name field and give it either its purpose, as I mentioned, mail House file, or in this case, through word also known as cultivation of prospect, stewardship or cultivation data for Edie. Okay, got it. That’s what its purpose is. But I’m this does not say, who shows up on the spreadsheet. The type? Well, assuming that this needs to be one line or one row per donor, then it’s going to be a main bio export. I don’t need to see every single gift that these donors gave. We want to see some summary data, like the annual totals, or the lifetime giving or their largest gift. Those are what we call statistics, or calculated fields. Notice here calculated fields that live in the main file. All right, for now, sorting by last name. Okay, that will work for me, and you should share it with anyone else in your organization. Okay, so what do I start with? On the right side are the fields that come out kind of by default? So I do recommend keeping the donor ID that way. Should you or your IDI need to look up that constituent because no, that number can’t be right i I’m not following this. Instead of typing the name. They can you use the Quick Search with the ID number. Okay, title, first name, last name, suffix. Okay, that’s fine. But professional titles salutation I don’t need so I’m going to send them over or remove them. From the right side, send them back to the left side. Okay. We need the address and city state and zip for this actually. I believe not remove them as well. But let’s leave some phone numbers because we don’t know with what phone numbers they have. And if My ad wants to call somebody and set up a meeting, then we want to leave the phone numbers. This field is whether it’s an organization or not, that would be a yes a Wyvern N. I don’t for this purpose, I need it. But for mailings, you will need it. Okay. I have some things in mind, but anybody want to give me some suggestions as to my EDS gonna look at my laps, they don’t hurt, what kinds of fields what data would help him or her? Anyone want to give me some suggestions? This is not going to be complete. And they’re gonna come back to me and say, Okay, that’s great, but I need some additional info. All right. Thank you, Nadine. She’s suggesting last gift date. So what I like to do with this kind of type what I think the field name is, what is last last gift date. Okay. move that over. How about last gift amount? Do you want that to maybe? Oh, a zoom? Yes. Let’s bring that over. Sure. Okay, Teresa says first gift aid. Okay, how long have they been a donor? Ah, okay, this one was used for a webinar. So that will be in your system. So first, isn’t there? Now we gotta spell it. Or twist it another way? If I look for initial, uh huh. Initial gift date, I want to know how long it’s been since they first gave. Oh, Molina, thank you. That’s just give. If I look for largest gift found, this is something that would be filled in if there is a wealth screening, I believe from donor cert. So instead of that, because not everybody would have it. Again, this is where, okay, what’s another one? Another word instead of saying large? Mac? Some gift the male? Okay, great. Now, that’s good. I’m also going to suggest, what about the annual totals, I don’t need to go back all seven years, but don’t have perfect keeps the current year and six each of the six years in the past. For fiscal total fiscal year total, and calendar year total, though both of those fields exist. But if your fiscal year begins in January, then the data would be identical. So what do I mean by that? Okay, I want let’s just say for argument’s sake, two calendar years ago, and let’s just pull three calendar years. Okay, I don’t need to go back into four or five or six years ago, but I went one year ago, which is found in here. As if I scroll down. Yes, I’m going somewhat fast. I’ve got a half year it is last year. Now with case you have not yet been exposed to this. What is the difference between a C YTD and a regular YTD This is a calendar year total giving without the see before it means fiscal year, total given. All right. So I am intentionally pulling calendar year. And then what if I want this year? I think it’s up in the seas for calendar view ups I guess. Calendar. Here we go. That’s how we spell it calendar. Check it and move it over. Okay. Now keeping in mind that right now I’m going to use this for donors who have not yet who haven’t given in 2022 or possibly even 2023. But maybe I could use this for some other exercise as in filter, like donors whose largest gift is $10 or more. Okay, so in that respect, think outside the box so you don’t have one template for every filter. Okay. Now what I mentioned on the slide, and I said I wanted to illustrate I got two three last and this and that’s not right. I’m gonna leave calendar last year. Okay, let me take two and move it down and drag it. Beautiful. Okay, so I can move things around. And I think what I want is all. And Chris, Cece, that’s how you pronounce and I apologize if I am not saying it properly. But you identified lifetime giving, I love it. Your system might have it as lifetime gift total, or just gift total. Check it and move it over that I definitely want to be all the way at the end on the right. Unless, oh, you know what? Maybe we have some things in the narrative about donors, health, or their have to move out of the area, or there’s some thing going on in their home life that prohibits them from making a donation. Maybe I want that narrative. Okay. All right. With that I’m going to save and close. I can always edit it to remove columns, or add columns. That’s what’s so great about the pencil. Okay. Also keep in mind, not that we would hope that this happens. But if you see two or more export templates that have the same name, and if you look at the fields by editing, and they have the same exact columns, then there’s no reason to have duplicates. So use the trash can to clean it up, as in deleted. All right, so if I want to do my stewardship,

I could work from mailings, but I’m gonna say you probably would go bleep works. Center listings? Let’s go. Let’s thing. I’m gonna refresh this. There we go. All right. I want my export to file. Now keep in mind the export template, which created as a main bio, therefore, make sure you have a main bio. Because if you don’t, you won’t find it. Ah, wah wah, it is here. Okay, great. So I’ve selected the right type of data to come out. With my next My next exercise will be gift pledge transaction. So I will talk about that. Okay. Um, Megan’s asking, Will the deceased person’s record show up? Or will it need to be filtered out? That’s a good question. Maybe. Hopefully, if your deceased constituents are Mark, do not mail then they would most likely not be here. Okay, I’m hoping they’re not here. However, and I’m not going to get into the particulars. Good, good, good. But maybe there are donors who don’t want to get snail mail. But it’s okay if your ED calls them and or has a meeting. So maybe you do want to include the no mailings. And you can go over your individual situation with support, if you’re not familiar with how to build the precise filter. But here I’m going to go up by and I’m going to go to add new filter. And while I could say find the donors whose calendar year giving last year was zero, and this year is zero, that possibility, the fastest way of doing it as different option is from the main screen, find the donors whose last gift gave meaning the last time we got a contribution from them was in the year 2021. How do I say that? I say between and then I like to tell you that you can click on the calendar

All right. The last time we heard from them in terms of a donation was this. Now you might need to narrow it down in that you’re only looking at individuals maybe we’re not looking at foundations, etc. But I’ll just keep it simple. Ken, you don’t need to save the filter right now. But you could and I click Done. Okay. And depending on whether you want your do not mail. If that were the case, Nadine. I would probably add the do not mail column And the no mail reason, so that and hopefully your IDI would know, oh, this donors to see. So let’s not reach out to them. Or this donor, we don’t have an address, it’s not a good address, but maybe the phone numbers work. Okay. So again, determine whether you want to include the do not mail or not. And then the most important thing is choose the right export template. So click on the drop down, I find or if you had not created it in settings, export templates, here’s your chance to add a new template to create here right in this exercise. Here’s my stewardship. Perfect. Teresa’s asking, is there a field that shows do not male? Yes, there actually is a field called no male and Oh, M A, I L, it’ll be a capital Y or a capital N, Teresa. And you can add that to your template, you got it. Alright, here’s another little insight tip or best practice. Not that you have to. But if you click Preview, what that will do is it’s faster than waiting for the CSV file to be made. It also allows you to see, okay, do I have the right donors approximately? Or? Uh, oh, if I had zero donors, then there’s something wrong with my filter. Okay. And then you can also glance here and say, oh, yeah, I like these, I like these. Cool. And I do have to give total over here on the right. If something’s wrong, or you want to add a column, like the no nail column, or rearrange from left to right, you can do that before you export. Otherwise, if this is exactly what you want. And how many do we have my database? D 500. Oh, we got some work to do. We’re going to export the file. Okay, open it up. Save it as if you want to save as donors with last gift in 2021. Maybe you want to save really with the gift in descending order? Because the donors with the largest lifetime giving are probably at attention sooner and in a different manner from a donor whose lifetime giving was $25. All right. So that is a main bio. And notice here, hopefully, we only see one row unless we had duplicate donors that need to be combined. All right, looking good. Looking good. And I’m just going to close out of this and not save it from preview, you can export. Otherwise, you can go directly to export. But if you’re just not sure, do I have the right columns, or maybe my filter is not exactly final, use the Preview as like a sneak peek. Okay. Now again, main bio gives you one row per donor with data that is from the main or bio screens, or the calculated fields that may not necessarily be visible on the main screen, but are in the main file. Okay. Now let’s switch gears. Let’s choose gift pledge transactions. Gift date from let’s say my finance person wants gifts from two 120 23 through 228 2023. He or she is doing a reconcilement of DonorPerfect to the accounting software. This last filter is not going to work for my exercise. I remove it. Okay. Do you need a filter? Do we need a filter to say every gift? No, you will get every gift in that time period. This is in essence your filter. But if you’re only looking at unrestricted gifts, for instance, or you don’t want to see gifts that have a tribute fields. Not that that would work for my purpose here, but you could apply a filter in addition to the date, okay, and make sure with gift pledge. I’m gonna say 99 percent of the time, if not 100, you do want to make sure to include the do not mail. All right? Keep in mind with gift by transactions, if one donor gave you more than one gift in this time period, you will see one row per gift matches your filter. In this case, my filter is the date range, okay. And when it says gift pledge, you get gift or pledge data, in addition to fields from the main or bio screens or database. Same thing can be said for contact transactions, you get contact fields that you can create in a template. In addition to the main bio fields, this gives you info on the other info screen, or columns on the other info screen, as well as my bio. And last but not least, the other address, which is the very last tab for most of you. In terms of the entry screens, this will get you an address data, as well as main bio data. Okay, let’s go back to our gift pledge. No filter in my situation? And could I use the default receding template for my purpose? Yes, I could. But then I still have to sit and delete columns, or whatever the case may be. Instead, I’m gonna look for this may be it but I’m gonna, for this exercise. Assume that that’s not it. And I’m looking for something that says, finance reconcilement? Well, I don’t see it. I’ll see reconcilement here. So I’m going to add a new template here. I don’t have to do it in the settings. Because I selected gift pledge it already pre determined the type here, notice that? Okay, this is going to be my finance reconcilement. Reconciliation matcher. And the difference between the two words. Do I want to sort by last name? Actually, no, I think I want to sort by my gift date, which is date Oh, for this, okay, fantastic. And I do want to make sure I share this. If I need a backup to do this, when I’m not here, donor ID, etc. Does my finance person need all of this stuff? Maybe optional line, address, state, zip, all of this stuff is not needed. I don’t need phone numbers, et cetera, et cetera. Remove it from the selected fields. Okay. Sorry, what I do need are going to be the gift. Now what I can do is narrow this down to say I need gift pledge fields to pick from feel free to go down here. Do you want the campaign maybe? Do you want VA? date of gift? Definitely. Do you want the Gift Amount? Death finit Lee and my accountant also need something called General Ledger, which I think I missed here it is general ledger, which is the income account on the accounting software. And if they are using the class field. See QuickBooks class. Okay, anything else? I think I’m gonna leave it at that. All right. If I got campaign here before that date, I don’t want it there. I’m going to drag it down and move it here. And maybe we do need the gift. Now retic there might be special restrictions or something that was written in there. Alright, save and close. Sweet. So now I have a very nice, precise, concise export template that has only the columns that my finance person my accounting person needs. Again, I can preview it and make sure on the first go around. Do I have the columns okay? But notice we get the description as well as the code. Great, it’s in date order. The oldest one is at the bottom Um, I don’t know, the newest is at the bottom, the old this is at the top when I saw it on the date field, love it, love it. exe file and then send that to your account. Pretty awesome. Okay, so this is the way to get Excel data out when you know exactly that the person who wants the output is looking for an Excel spreadsheet. All right, do we have any questions

I’ll throw in one tip related to this, but not on a slide if you are doing a mailing, and one of the fields in that letter is your the donors last gift date, for instance, or you’ve been a donor and you’ve contributed a sum of y dollars. If you want that data to be in your letter, make sure that you edit your mailing template to include that piece of data as well. Alright, making that something that in terms of the reports and the filters and templates, you can reach out to support or if you have some customized training our purchase, then that is something that you can review with a member of our training department like myself. Alright, so with that, if there’s no other questions, everybody, I am going to end the webinar now I want to thank you so much. I hope this is helped you understand the power of this tool, which is getting data out of DonorPerfect and putting it into it. No. So you are so welcome, everybody. And yes, Teresa. We’re going to update our website with the recordings. And thank you so much, everybody.