rate of growth

Hi Randall,

How are you defining new customer? You mention season, so are you wanting a query based upon first production purchased?

Edit to clarify a bit, is the new customer based on ticketing history, gift history, account creation date, etc.

Cheers!

I agree with Ryan - it depends on how you define "customer".  Is it purely someone that purchases a ticket or do you include web site registrants without a ticket purchase?  I suspect that once that definition has been made, the code itself to determine numbers is relatively early.

Accuracy of numbers could be influenced by how diligently you do your deduping!

Martin

Speaking with Box Office, the account creation date would be the determining factor.

Well it looks like the earliest created date is retained even after merging an older account into a newer one so you should be able simply make multiple lists based upon 'Constituent Create Date' in List Manager, however I would double check to make sure they don't want this based upon a transaction date as that would probably be a little more handy and possibly more accurate to the Box Office.
If they do want it based upon first production date it's fairly simple to create a SQL View with that information.

Why not something like this:

 select count(*),'6 Years Ago' from t_customer where create_dt < dateadd(year, -6,GETDATE())

union

select count(*),'1 Years Ago' from t_customer where create_dt < dateadd(year, -1,GETDATE())

union all

select count(*),'0 Years Ago' from t_customer where create_dt < dateadd(year, -0,GETDATE())

You may have to play around with where you want the dates.  This produces 5 years ago as of the date you run the query.  You might want to align this on your fiscal year or calendar year.

Then use this data ans say you were at this count 6 years ago.  Subtract the 6 year ago count from 5 years ago count. The remainder is the 5 year ago growth.  That will give you the number of folks you added 5 years ago.  Then do this for 4year ago count - 5 year ago count.  This will get you the growth for 4 years ago growth and so on.

This could be cleaned up by adding an additional where clause item to remove inactive accounts or merged accounts.  You could also clean this up to do all of the arithmetic to give you the differences not the totals as of a particular year.

Hope this helps.

Thanks Ryan for your insight. The numbers I need to generate are not department specific. We are trying to determine a gross number of "new" people added to Tessitura on an annualized basis. Regardless if the account was created due to donation, ticket, information, web, etc, the "reason" why the account was created is not of issue - the request was more global than that - so using account creation would suffice. What then I am hoping to get is a sum of the accounts "created" during each season for the past five seasons. Even in that, there will probably be 10-15% of dups, entry errors, etc, but it will still be a global metric that can be used. Generating a list will produce more verbose detail that we need, and I really dont want to burn up that many clock cycles for this. I wish I knew SQL enough to write this, but I simply dont. Is tehre a fairly quick way to obtain the summary totals on an annualized basis for the past five years?

Hmm, well you could take a look at what Tom suggests above but making five lists with one criterion each would be pretty simple and fast, but I might be missing something that you are trying to do.

Well, if all you want is strictly by calendar year, you could try the following.  Happy to help - email me directly if you like.

select datepart(yy,create_dt), count(*) 

from T_CUSTOMER

where datepart(yy,create_dt) >= 2006

group by datepart(yy,create_dt)

order by datepart(yy,create_dt)