Advice on How to Get Specific Results

Gina,

From my point of view, I don’t know of a simple way to do, distinctly counted groupings like you need for your examples below.

However this is a fairly common request and I do know a way to do this. However, it does require that you create a manually edited list criteria.  To do this create a new list.  In your new list you should find a button marked [Manual Edit].  Click that button and paste the example query listed below into the window that just opened up.  (In this note the lines that go into manual edit screen begin with several lines that start with “--------------------------------- “ and end with the same.   I hope that this is clear… J)

In order to determine if you have done this correctly.  Click the Validate Query button to make sure that you have pasted the right bit into you fancy new list.  J

For Question Number 1 below.

Try this manually edited list in List Builder.  I’ve included some comments below that may help you and others to use this same recipe.

This assumes that every contribution campaign has a fiscal year correctly entered.  That is how we are going to determine which contributions are for which Fiscal years.

--------------------------- Begin Folks giving in more than 20 different fiscal Years by Campaign Fiscal Year---------------------------

------------------------------------------- Select Text Starting Below Here --------------------------------------------------------------------

Select      Distinct a.customer_no

From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)

            JOIN vs_contribution e (Nolock) ON e.customer_no = a.customer_no

            -- Added this bit to get Campaign FYear

            JOIN T_CAMPAIGN tca (Nolock)on e.campaign_no = tca.campaign_no

Where  IsNull(a.inactive, 1) = 1

-- Added this bit to do the grouping by customer_no and Campaign FYear

group by a.customer_no

-- Added this bit to look for customers that have more than 20 diffrent fiscal years of donations.

having COUNT(distinct tca.fyear)  >= 20

------------------------------------------- Select Text Ending Above Here -------------------------------------------------------------------

--------------------------- End Folks giving in more than 20 different fiscal Years by Campaign Fiscal Year ---------------------------

Once you have this list use whichever report you want to, to get data about these folks.  I would thing that the Bio Reports might be interesting on customers like this.  However there are all sorts of other wonderful reports that take a list as a parameter.

You can edit the number of years to be included in your list by changing the 20 at the end of the criteria above.

Further notes on the above… This query, is not based directly on the contribution date.  Rather it is based on the Campaigns fiscal year.  The Contribution date is not checked for this grouping.  Any Campaigns without a Fiscal year listed on the Campaign Screen will be counted as the same year.  In addition, this list will correctly deal with cases where customers give multiple gifts to the same campaign in the same fiscal year or different campaigns in the same fiscal year.  They all only count toward the fiscal year listed on their campaign.

For the second question you might try the following.

Basically the same… This one assumes that you have entered an event date on the elevated event campaign.    If not you can remove the “and tca.event_dt is not NULL” to get contributions from all elevated event campaigns.  This one assumes that if you have more than one elevated event in a year.  That each one counts as +1 for the customer.  Note the change of tca.fyear to the campaign number tca.campaign_no as the method of counting.

--------------------------- Begin Folks giving to more than 3 Elevated Events ---------------------------

------------------------------------------- Select Text Starting Below ---------------------------------------

Select      Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)

            JOIN vs_contribution e (Nolock) ON e.customer_no = a.customer_no

            -- Added this bit to get Campaign FYear

            JOIN T_CAMPAIGN tca (Nolock)on e.campaign_no = tca.campaign_no

Where  IsNull(a.inactive, 1) = 1

            -- You can use one or both of the following exclusions.

                  -- Added this bit to look for any elevated event campaigns

                  and camp_type like 'E'

                  -- Added this bit to look for elevated event campaigns with specific dates

                  and tca.event_dt is not NULL

-- Added this bit to do the grouping by customer_no

group by a.customer_no

-- Added this bit to look for customers that have more than 3 different elevated event campaigns.

having count(distinct tca.campaign_no) >= 3

------------------------------------------- Select Text Ending Above --------------------------------------

--------------------------- End Folks giving to more than 3 Elevated Events ---------------------------

Please note these simple manually edited lists can be confused by written off contributions.  Written Off Contributions will count as a real contribution for the customer.  Also note that this is looking at the contribution from the point of view of the actual donor and not from the point of view of the credited customer… As always this will be confused if customers have un-merged accounts each with only part of the customers history…  This may not deal with accounts that have some contributions on the household and some on the individual accounts.

Hope this is of a bit of help to some.  This kind of question is not infrequent.  I’m wondering if someone smarter than I has produced a simple general method for doing distinct group bys like this in standard list criteria…  Without building a custom view to do the calculation and grouping.   I’m thinking of something like “Giving Year Count”…

--Tom

…

718.724.8135

tbrown@BAM.org

From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Gina Wake
Sent: Thursday, May 08, 2014 2:43 PM
To: Thomas Brown
Subject: [Tessitura Development Forum] Advice on How to Get Specific Results

Hi all - I've been asked to pull two lists that I'm not quite sure how to get. 

1. Anyone who has given ANY gift to us for 20 years - does NOT have to be consecutive years.  Recently someone on the forum gave me great advice on how to get a list of people who had donated to a specific campaign for consecutive years, but that solution isn't really working here (running a list for each campaign - which are designated by year for example Annual Fund 13-14, and then combining those lists using the AND operator).  In this case though I think I need to somehow use the fiscal year rather than the campaigns, as we have so many campaigns in one year.  Or is there a Canned Report that I am completely missing that would give me this information?

2.  Anyone who has given a gift to a specific event (which are linked to campaigns, i.e. COCAcabana 13-14, COCAcabana 12-13) more than 3 times but not necessarily consecutively.

Can anyone give me some advice on these?  They aren't clicking with me for some reason!  Thanks in advance!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

I have essentially the same, but with two additional lines just below the line, Where  IsNull(a.inactive, 1) = 1

--to limit to AF or Endowment or whatever  (see TR_CAMPAIGN CATEGORY for category ids at your organization)

AND tca.category in (<whatever>)

--to filter out write-offs.  You could make this bigger to get over a certain contribution amount, but keep in mind this could bite you on multiple contributions to the same campaign (someone who gave 2 x $500 wouldn’t pass $1000 filter)

AND  e.cont_amt >=1.00

John Trimble
Data and Prospect Manager
Dallas Symphony Orchestra
Morton H. Meyerson Symphony Center
Schlegel Administrative Suites
2301 Flora Street
Dallas, Texas 75201

214-871-4041 - phone
214-981-2988 - fax

j.trimble@DalSym.com
www.dallassymphony.com

Gina,

From my point of view, I don’t know of a simple way to do, distinctly counted groupings like you need for your examples below.

However this is a fairly common request and I do know a way to do this. However, it does require that you create a manually edited list criteria.  To do this create a new list.  In your new list you should find a button marked [Manual Edit].  Click that button and paste the example query listed below into the window that just opened up.  (In this note the lines that go into manual edit screen begin with several lines that start with “--------------------------------- “ and end with the same.   I hope that this is clear… J)

In order to determine if you have done this correctly.  Click the Validate Query button to make sure that you have pasted the right bit into you fancy new list.  J

For Question Number 1 below.

Try this manually edited list in List Builder.  I’ve included some comments below that may help you and others to use this same recipe.

This assumes that every contribution campaign has a fiscal year correctly entered.  That is how we are going to determine which contributions are for which Fiscal years.

--------------------------- Begin Folks giving in more than 20 different fiscal Years by Campaign Fiscal Year---------------------------

------------------------------------------- Select Text Starting Below Here --------------------------------------------------------------------

Select      Distinct a.customer_no

From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)

            JOIN vs_contribution e (Nolock) ON e.customer_no = a.customer_no

            -- Added this bit to get Campaign FYear

            JOIN T_CAMPAIGN tca (Nolock)on e.campaign_no = tca.campaign_no

Where  IsNull(a.inactive, 1) = 1

-- Added this bit to do the grouping by customer_no and Campaign FYear

group by a.customer_no

-- Added this bit to look for customers that have more than 20 diffrent fiscal years of donations.

having COUNT(distinct tca.fyear)  >= 20

------------------------------------------- Select Text Ending Above Here -------------------------------------------------------------------

--------------------------- End Folks giving in more than 20 different fiscal Years by Campaign Fiscal Year ---------------------------

Once you have this list use whichever report you want to, to get data about these folks.  I would thing that the Bio Reports might be interesting on customers like this.  However there are all sorts of other wonderful reports that take a list as a parameter.

You can edit the number of years to be included in your list by changing the 20 at the end of the criteria above.

Further notes on the above… This query, is not based directly on the contribution date.  Rather it is based on the Campaigns fiscal year.  The Contribution date is not checked for this grouping.  Any Campaigns without a Fiscal year listed on the Campaign Screen will be counted as the same year.  In addition, this list will correctly deal with cases where customers give multiple gifts to the same campaign in the same fiscal year or different campaigns in the same fiscal year.  They all only count toward the fiscal year listed on their campaign.

For the second question you might try the following.

Basically the same… This one assumes that you have entered an event date on the elevated event campaign.    If not you can remove the “and tca.event_dt is not NULL” to get contributions from all elevated event campaigns.  This one assumes that if you have more than one elevated event in a year.  That each one counts as +1 for the customer.  Note the change of tca.fyear to the campaign number tca.campaign_no as the method of counting.

--------------------------- Begin Folks giving to more than 3 Elevated Events ---------------------------

------------------------------------------- Select Text Starting Below ---------------------------------------

Select      Distinct a.customer_no

 From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)

            JOIN vs_contribution e (Nolock) ON e.customer_no = a.customer_no

            -- Added this bit to get Campaign FYear

            JOIN T_CAMPAIGN tca (Nolock)on e.campaign_no = tca.campaign_no

Where  IsNull(a.inactive, 1) = 1

            -- You can use one or both of the following exclusions.

                  -- Added this bit to look for any elevated event campaigns

                  and camp_type like 'E'

                  -- Added this bit to look for elevated event campaigns with specific dates

                  and tca.event_dt is not NULL

-- Added this bit to do the grouping by customer_no

group by a.customer_no

-- Added this bit to look for customers that have more than 3 different elevated event campaigns.

having count(distinct tca.campaign_no) >= 3

------------------------------------------- Select Text Ending Above --------------------------------------

--------------------------- End Folks giving to more than 3 Elevated Events ---------------------------

Please note these simple manually edited lists can be confused by written off contributions.  Written Off Contributions will count as a real contribution for the customer.  Also note that this is looking at the contribution from the point of view of the actual donor and not from the point of view of the credited customer… As always this will be confused if customers have un-merged accounts each with only part of the customers history…  This may not deal with accounts that have some contributions on the household and some on the individual accounts.

Hope this is of a bit of help to some.  This kind of question is not infrequent.  I’m wondering if someone smarter than I has produced a simple general method for doing distinct group bys like this in standard list criteria…  Without building a custom view to do the calculation and grouping.   I’m thinking of something like “Giving Year Count”…

--Tom

…

718.724.8135

tbrown@BAM.org

From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Gina Wake
Sent: Thursday, May 08, 2014 2:43 PM
To: Thomas Brown
Subject: [Tessitura Development Forum] Advice on How to Get Specific Results

Hi all - I've been asked to pull two lists that I'm not quite sure how to get. 

1. Anyone who has given ANY gift to us for 20 years - does NOT have to be consecutive years.  Recently someone on the forum gave me great advice on how to get a list of people who had donated to a specific campaign for consecutive years, but that solution isn't really working here (running a list for each campaign - which are designated by year for example Annual Fund 13-14, and then combining those lists using the AND operator).  In this case though I think I need to somehow use the fiscal year rather than the campaigns, as we have so many campaigns in one year.  Or is there a Canned Report that I am completely missing that would give me this information?

2.  Anyone who has given a gift to a specific event (which are linked to campaigns, i.e. COCAcabana 13-14, COCAcabana 12-13) more than 3 times but not necessarily consecutively.

Can anyone give me some advice on these?  They aren't clicking with me for some reason!  Thanks in advance!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!