[Simon Stacey]

I'm doing some segment/package slicing queries. I've only been using RE for the last 2 months, haven't yet had any formal training on it, and have a couple of queries about the way it works. That's the muppetry disclaimers done with, on to the meat (or tofu) of the issue:

I'm using queries built on queries, so there's one query that identifies all Constituents who aren't excluded by any of the various exclusions for this appeal. Then, there's a second query, drawing from this dataset, which identifies the donors who have given a cash gift of between £5 and £5,000 to one of certain funds and who don't have an active regular giving gift. This provides me with my basic "Cash Donors" dataset.

However, if I run a constituent query based on this "Cash Donors" dataset (to slice the segments by last gift), is there any way to limit this so that it only looks at the gifts that were in the £5-£5,000 range and to one of the specified funds when identifying the last gift? As I understand it, it would otherwise look at the "last gift" of any type, and applying a "Fund ID is one of ..." criteria would exclude those donors who had given to a different fund, but who had given to one of the relevant funds before that.

Erm - I'm not sure whether I've explained myself very clearly.

I want to pull off all cash donors whose last cash gift to a specific fund list was within value range x and time period y, and I want to be able to see the value of that gift. Even if they've since made a gift to a different fund, not on the list.

(I have a background in Access and SQL, and find RE's 'helpful' distinction between 'gift' and 'constituent' queries, and ne'er the twain shall meet, quite frustrating sometimes! For much of RE, I find myself having to output the data and Pivot it myself, using Excel.)

[DrewAllen]

Unfortunately the "Last Gift" in query really does mean the very last gift, so it won't work for what you want. However, people often use "last" as a shortcut for "they have given any gift in the time period that meets the criteria, and no gifts in the time period since then that meet the criteria." They might also mean "total giving" instead of any gift.

For example, if we're talking about FY07. If I gave a gift of £5 in January 07 and a £1 gift in March 07 and haven't given a gift to that fund since, they want to include me because I have a gift that meets the criteria even though my truly last gift isn't large enough to qualify. If this is what they mean, then you can use summaries.

The one summary would be count of gifts (or total amount) = 0 since the period in question. If you are looking at any one gift, you don't need a summary for the gift in period, but if you want to look at totals, you do.

Drew

[ggs]

You may also want to try an export from one of your queries. In export, when you select "Last Gift" and give a criteria (ie dollar amount or date range) it will pick the last gift based on that criteria, unlike query which will give you the absolutely LAST gift. See if this helps.

I may be way off from what you are trying to do, but from how i read your post, this may work for you.

G

[Simon Stacey]

Quote:

Originally Posted by DrewAllen

The one summary would be count of gifts (or total amount) = 0 since the period in question. If you are looking at any one gift, you don't need a summary for the gift in period, but if you want to look at totals, you do.

Drew

I'd been experimenting with the Count of Gifts summary, but how would I be able to identify the largest relevant gift, so that it could be used for a variable donation prompt? Is that even possible?

Oh yes, and also, thanks!

[Simon Stacey]

Quote:

Originally Posted by ggs

You may also want to try an export from one of your queries. In export, when you select "Last Gift" and give a criteria (ie dollar amount or date range) it will pick the last gift based on that criteria, unlike query which will give you the absolutely LAST gift. See if this helps.

G

Ah! That sounds like the kind of thing I want - being able to set criteria from which it picks the last gift, rather than criteria which look at the very last gift only.

Thanks. I'll investigate Exports more ...

[Melissa Siobhan]

The trick is to think of this as two steps. First to identify the constituents you want with your query. Then to export the fields you want about those constituents.

If summary information fields will insure that you are getting the people you know you want then you can use export. Export allows you to export not just the very last gift but the last one that meets a certain criteria.

One Hint: If you are using the amount as a prompt for a variable donation ask you may want to use largest gift and not last. If I gave $50 in January but in June gave only $20 wouldn't you want to ask them for $50 again not $20? When I export this field for our mailings I use the largest gift in the past 3 years as the base for my variable gift array.

[Simon Stacey]

Quote:

Originally Posted by Melissa Siobhan

If summary information fields will insure that you are getting the people you know you want then you can use export. Export allows you to export not just the very last gift but the last one that meets a certain criteria.

One Hint: If you are using the amount as a prompt for a variable donation ask you may want to use largest gift and not last. If I gave $50 in January but in June gave only $20 wouldn't you want to ask them for $50 again not $20? When I export this field for our mailings I use the largest gift in the past 3 years as the base for my variable gift array.

Thanks. I was having a look at Export, but it doesn't provide the segmentation and de-duping functions that Mail offers, which was what I used last time (when the criteria were somewhat different). Thanks for the tip regarding largest gift!

[Melissa Siobhan]

I use both mail and export. First I run my queries through Mail and use the feature to have RE assign the appeal and package to the constituent records.

Then I use a query of everyone who was assigned the query(ies) in the export and also export the appeal and package codes.

It is frustrating that mail does not give you the functionality that export does and you have to do this in 2 steps - but it works.

[DrewAllen]

Also, if you are comfortable using SQL queries, you can do the segmentation in SQL and then run a fake "update" using import and select "create query of updated records" to create your segment queries.

Another option is to use export to get the last/largest gift that you want, manipulate it in Excel, then use the import to create the segment queries.

Drew

[Simon Stacey]

Quote:

Originally Posted by Melissa Siobhan

I use both mail and export. First I run my queries through Mail and use the feature to have RE assign the appeal and package to the constituent records ...

Aha! Cunning!

[Simon Stacey]

Quote:

Originally Posted by DrewAllen

Also, if you are comfortable using SQL queries, you can do the segmentation in SQL and then run a fake "update" using import and select "create query of updated records" to create your segment queries.

Another option is to use export to get the last/largest gift that you want, manipulate it in Excel, then use the import to create the segment queries.

Drew

I've not attempted to unleash SQL on RE yet, but that's another cunning workaround. I'm getting used to the "export and tweak" approach of RE, rather than the "get the data you want first time" approach. [sigh]