Query to output a supporters total giving history

Van Tam Ho · June 2019

Hi All,

I am new to Raiser's Edge and was wondering if anyone was able to help me with a query I have. I am looking to output data that gives me the total "giving" history of a supporter, so for example - 2019/20 for month 1, 200 people gave their first gift to appeal 1, of the 200, 50 gave a second gift to appeal 2 and 20 gave to appeal 3 and 130 have never given a second gift.

Is this possible?

TIA

Mark Guncheon 2 · June 2019

Are you looking for specific appeals or funds or types of gifts? If you are talking about any gifts, you can do this with an export. For your criteria, choose all gifts, put them in descending order and enter the maximum number of gifts you'd like reported on. Add appeals and funds and campaigns and whatever else you need data on.

Van Tam Ho · June 2019

Hi Mark,

Many thanks for coming back to me. In answer to your question, it would be based on specific "campaign", its just I wanted to show the journey a supporter has been on in terms of their giving as in the example, we can have 200 people giving for the first time for a certain month, it then split outs the 200 people who then gave a second gift and to which campaign id.

Thanks

Mark Guncheon 2 · June 2019

I would use a query that pulls all gifts to that campaign and then use that query to filter out each gift given by donors to that campaign and place them in gift date ascending order to show which gifts came in first. That would show all gifts to that campaign.

If you want to show the effectiveness of a campaign at bringing in donors who then give to other campaigns, I would have a "gift" column that shows "first gift" to that campaign and then a new "gift" column that shows all gifts in ascending order. Then you'll have to do the rest in Excel.

I like this idea of looking at a campaign/appeal's effectiveness at bringing in donors to other campaigns/appeals.

Van Tam Ho · June 2019

Your reply seems perfect and that's what I am after, the effectiveness at bringing in donors to other campaigns/appeals. Are you able to provide a screenshot of an example of the query if possible as I am slightly confused as to what I would put in the "Criteria" or "output" fields.?

Aaron Rothberg · June 2019

Hi Van Tam Ho-

I might suggest a different approach albeit my approach is a lot more work. I prefer to see giving data like this in a more visual format.

Let's imagine in your Campaign you have the following appeals and dates.

Appeal 1, July 1

Appeal 2, August 12

Appeal 3, September 6

Appeal 4, December 1

etc...

I would create an Export with gift summary fields for each Appeal and I would arrange them by date in ascending order so you can see a donor's progression. The output would look something like this:

1be952b5f380427bb22be95702367e02-huge-ex

For the reader, you could add another line below the headers (after the Export has been Exported) and include a date for each appeal.

EDIT: I just saw your response about Criteria. For the criteria, you only need to say Gift Type Is One Of (include all Gift Types except pledges and write offs) and Appeal Id Is One Of and add all of the appeals that make up your Campaign.

Van Tam Ho · June 2019

Hi Aaron,

That could also work, but what if I didn't want to break it down by constituents and just by campaign id's?

I am trying to explore the different avenues as I need to give a total number of donors that have donated a first gift within a timeframe and then given a second gift, but the second gift could have been given on a different campaign code. Once the top line total has been done, I then wanted to break it down by constituent id as in the screenshot you provided below.

Aaron Rothberg · June 2019

If you want something drill-able, as in you review the aggregated Campaign data over periods of time and then want to drill-down to see the individual constituents, you'll need to build something in Crystal.

Another idea is to create an Export detailing giving in each Campaign with the supporting details for each constituent, add in summary totals at the bottom (in Excel after the Export), and then hide the individual constituent data. If someone wants to see the individuals they can simply unhide that data in Excel.

Mark Guncheon 2 · June 2019

If you are basing it on a campaign, here is the suggested criteria in your query:

373f8e19813005f64b4b137ee2c79691-huge-un

Pick your campaign and a start date for when gifts are coming in. Decide on whether you want to include those with no valid address, inactive and deceased. Then save and use for your export.

Mark Guncheon 2 · June 2019

And if you are looking at first gifts to a campaign, you could change the query to that same criteria but use First gift, not just gift:

de4716307f548953ba6ef99b4959ae42-huge-un

John Heizer · June 2019

Mark Guncheon:

And if you are looking at first gifts to a campaign, you could change the query to that same criteria but use First gift, not just gift:

That's not entirely accurate. When you Query against "First Gift" RE will only look at the very first gift given by the Constituent to see if it meets the criteria. In the example you gave RE will only returns Constituents whose first gift was to that Campaign and given after that date. If their earliest gift in RE is to any other Campaign then they won't be in the results.

Export, on the other hand, will allow you to specify criteria and return the first gift given that matches the criteria, regardless of whether earlier gifts have been given that don't match the criteria.

Van Tam Ho · June 2019

Aaron Rothberg:

If you want something drill-able, as in you review the aggregated Campaign data over periods of time and then want to drill-down to see the individual constituents, you'll need to build something in Crystal.

Another idea is to create an Export detailing giving in each Campaign with the supporting details for each constituent, add in summary totals at the bottom (in Excel after the Export), and then hide the individual constituent data. If someone wants to see the individuals they can simply unhide that data in Excel.

Aaron Rothberg‍ I have never used the export feature, so would it just be a case of creating the export the same way as creating a query?

Aaron Rothberg · June 2019

Yes, query is very similar to Export. This link might help. The Export chapter in that manual starts after page 128.

Van Tam Ho · June 2019

Many thanks Aaron Rothberg‍ for your answer.

Christine Cooke 2 · June 2019

All good and interesting approaches to this question.

First off let me say to the newbie -- query is a grouping tool, not a report.

And second, my suggestion would be a query that asks for all of the gifts that you want to see, either by campaigns, dates or both.

Then use Export to see that info in the breakdown you are looking for. Use the Summary fields multiple times and/or you can include columns that hold detail for each gift. Each area can be filtered to only show the particular gift types you are looking for, and by gift types I mean actual Gift Type, Campaign, Appeal, Fund, etc.

Van Tam Ho · June 2019

Christine Cooke:

All good and interesting approaches to this question.

First off let me say to the newbie -- query is a grouping tool, not a report.

And second, my suggestion would be a query that asks for all of the gifts that you want to see, either by campaigns, dates or both.

Then use Export to see that info in the breakdown you are looking for. Use the Summary fields multiple times and/or you can include columns that hold detail for each gift. Each area can be filtered to only show the particular gift types you are looking for, and by gift types I mean actual Gift Type, Campaign, Appeal, Fund, etc.

Hi,

Many thanks for your suggestion, so could I just the create a query for gift date and specify the criteria, then create an export using the query to output all campaigns and the gifts that were given to that campaign?

What do you mean by "Summary fields"??

Christine Cooke 2 · June 2019

yes -- that is what you can do -- use the query for the gift date and whatever other specific criteria may group the donors and then use export to see everything you want to see.

Larry Wheeler · June 2019

Van Tam Ho:

Christine Cooke:

All good and interesting approaches to this question.

First off let me say to the newbie -- query is a grouping tool, not a report.

And second, my suggestion would be a query that asks for all of the gifts that you want to see, either by campaigns, dates or both.

Then use Export to see that info in the breakdown you are looking for. Use the Summary fields multiple times and/or you can include columns that hold detail for each gift. Each area can be filtered to only show the particular gift types you are looking for, and by gift types I mean actual Gift Type, Campaign, Appeal, Fund, etc.

Hi,

Many thanks for your suggestion, so could I just the create a query for gift date and specify the criteria, then create an export using the query to output all campaigns and the gifts that were given to that campaign?

What do you mean by "Summary fields"??

The Summary fields you will find at the bottom of the Constituent branch in the Available Fields to Export tree. There are fields for Total Given, Total number of Gifts, etc...

Query to output a supporters total giving history

Comments

Categories