Hey All!
We are looking at creating a loyalty ranking in Tessitura, and was wondering if anyone had something in place that I could look at? Or; if you would be able to point me in the right direction.
The goal is to start simple. A general number based on subscription, single ticket purchased, and donations. Nothing crazy to start. I am hoping to keep the update out of the standard LP_CUSTOMER_RANK, and have it run as a custom nightly procedure as to avoid potential lags, and issues if there are ever any errors.
We have some simple constituency based rankings that we use for MOS switches, and early on sale access...but we would really love to add that layer of depth in to our constituent data.
Thanks everyone!
Jen
This is the procedure I shared with Jen offline. This code returns a simple message so the intended usage is via a utility/report.
CREATE PROC LP_UPDATE_DEV_RANKINGS
(
@fyear int
)
AS
/**********************************************************************************
Description: User enabled update of Development Planned Giving rankings.
Referenced By:
References:
Date Created: 01/22/2014
Created By: Matt Winchester
Mod Date Mod By Modification Description
------------- -------------- ------------------------------------------------------------------------------
*************************************************************************************************************
TESTING BLOCK
exec LP_UPDATE_DEV_RANKINGS 2014
************************************************************************************************************/
-- PG Loyalty Ranking
/*******************************************************************************************************************
Total FY Giving Range: total gifts and pledges per FY, all contribution campaigns except Auxiliary, soft credits included
$1-1,500 100
$1,501-25,000 125
$25,001-249,999 150
+ $$250,000 175
*******************************************************************************************************************/
--SET ENDING FISCAL YEAR
declare @prev_fyear int
set @prev_fyear = @fyear - 9
BEGIN TRY
BEGIN TRAN Update_Dev_Ranking
WITH MARK N'Update Dev Rankings';
--CLEAR RANKS
delete
T_CUST_RANK
where rank_type IN (13,8,9,10,11,12)
--GIVING SCORE
INSERT INTO T_CUST_RANK
(customer_no, rank_type,RANK)
SELECT
CN customer_no
,8
,SUM(POINTS) points
FROM ( SELECT CN
,Fyear
, CASE WHEN SUM(TPG) between 1 and 1500.99 THEN 100
WHEN SUM(TPG) between 1501.00 and 25000.99 THEN 125
WHEN SUM(TPG) between 25001.00 and 249999.99 THEN 150
WHEN SUM(TPG) >= 250000.00 THEN 175
END Points
FROM
(SELECT TCUS.customer_no [CN]
, fyear
, cont_amt TPG
FROM T_CUSTOMER TCUS
JOIN T_CONTRIBUTION TCON
on TCUS.customer_no = TCON.customer_no
Join T_CAMPAIGN TCAM
on TCON.campaign_no = TCAM.campaign_no and tcam.category not in (8, 15, 16, 17, 18) and TCAM.fyear between @prev_fyear and @fyear
WHERE TCUS.cust_type in (1,9) and TCUS.inactive = 1
UNION ALL
select creditee_no [CN]
JOIN T_CREDITEE TCRE
on TCUS.customer_no = TCRE.creditee_no
on TCRE.ref_no = TCON.ref_no
Where TCUS.cust_type in (1,9) and TCUS.inactive = 1 --and TCUS.customer_no = 324
) GR
GROUP BY
GR.CN
, GR.Fyear) GIFTS
GIFTS.CN
Order By
--PERFORMANCE ATTENDANCE SCORE
customer_no
,9
,sum(points) points
FROM (SELECT TCUS.customer_no
,fyear
,COUNT(LTH.perf_code) num_concerts
,CASE WHEN COUNT(LTH.perf_code) <=2 THEN 100
WHEN COUNT(LTH.perf_code) between 3 and 6 THEN 125
WHEN COUNT(LTH.perf_code) between 7 and 12 THEN 150
WHEN COUNT(LTH.perf_code) >= 13 THEN 175
END points
LEFT JOIN LT_TCK_HIST LTH
ON TCUS.customer_no = LTH.customer_no AND LTH.fyear >= @prev_fyear
WHERE TCUS.cust_type in (1,9) and TCUS.inactive = 1 and TCUS.customer_no != 0 and fyear IS NOT NULL
TCUS.customer_no
) TCK
TCK.customer_no
ORDER BY
--MEMBERSHIP RANK BY CATEGORY
,10
,SUM(points) points
FROM (SELECT
,TML.memb_level
,CASE TML.category WHEN 14 THEN 100
WHEN 3 THEN 125
WHEN 2 THEN 150
WHEN 1 THEN 175
LEFT JOIN TX_CUST_MEMBERSHIP TCM
ON TCUS.customer_no = TCM.customer_no
JOIN T_CAMPAIGN TCAM
ON TCM.campaign_no = TCAM.campaign_no AND TCAM.fyear >= @prev_fyear
JOIN T_MEMB_LEVEL TML
ON TML.memb_level = TCM.memb_level
WHERE TCUS.cust_type in (1,9) and TCUS.inactive = 1) MEMB
--AUXILIARY GIFTS
,11
FROM (SELECT TCUS.customer_no [CN]
,CASE WHEN SUM(cont_amt) between 1 and 1500.99 THEN 100
WHEN SUM(cont_amt) between 1501.00 and 25000.99 THEN 125
WHEN SUM(cont_amt) between 25001.00 and 249999.99 THEN 150
WHEN SUM(cont_amt) >= 250000.00 THEN 175
on TCON.campaign_no = TCAM.campaign_no and tcam.category IN (8, 15, 16, 17, 18) and TCAM.fyear between @prev_fyear and @fyear
group by
tcus.customer_no
Where TCUS.cust_type in (1,9) and TCUS.inactive = 1
creditee_no
) GIFTS
WHERE GIFTS.Points IS NOT NULL
--ENDOWMENT SCORE
--INDIVIDUALS
select
AFF.individual_customer_no customer_no
,12
,CASE WHEN AFF.inactive = 'N' THEN 8000
ELSE 4000
from T_AFFILIATION AFF
JOIN T_CUSTOMER CUST
ON CUST.customer_no = AFF.individual_customer_no and CUST.inactive = 1
where group_customer_no = 224658
--HOUSEHOLDS
ASSOC.customer_no
,CASE WHEN assoc.inactive = 'N' THEN 8000
from T_ASSOCIATION ASSOC
ON CUST.customer_no = ASSOC.customer_no and CUST.inactive = 1
where associated_customer_no = 224658
order by
---PG LOYALTY RANK
---AGGREGATES ALL SUB RANKS INTO "TOTAL RANK"
CUSTOMER_NO
,13
,SUM(RANK) RANK
FROM T_CUST_RANK
WHERE rank_type IN (8,9,10,11,12)
ORDER BY customer_no
COMMIT TRAN Update_Dev_Ranking;
SELECT 'Transaction was successful'
END TRY
BEGIN CATCH
-- There was an error
IF @@TRANCOUNT > 0
ROLLBACK
SELECT 'There was an error! ' + ERROR_MESSAGE()
--RETURNS THE ERROR TO A .NET APP
-- Raise an error with the details of the exception
--DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
--SELECT @ErrMsg = ERROR_MESSAGE(),
-- @ErrSeverity = ERROR_SEVERITY()
--RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
We look at the past 5 fyears. We have 5 scores that make up the PLI. Each one of those five scores is made up of many subs scores.
Example Ticketing Score:
For each of the pasted 5 fyears individually we score, number of single tickets, number of productions, num of subs packages, size of the sub packages. Add it all up and you have a ticketing score.
The five major scores are Donation Score, Ticketing Score, Non- Performance Score (Camp and stuff like that), Overall Household revenue, Volunteer Score. Again, all of these are made up of many subs scores (Too much to explain in a post). The scores are updated nightly if you exist and added if you do not exist. Everyone is scored no exceptions. There are list elements that are use to pull by a single score, or maybe by total PLI, or some other combinations that you may want. The total PLI is also displayed in the header. Hope it gives everyone some ideas.
Travis