USP_MKTSEGMENTATION_MATCHBACK
Matches donor ids in a marketing effort with their list record if the record was originally from a list.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATION_MATCHBACK]
(
@SEGMENTATIONID uniqueidentifier
)
as
set nocount on;
declare @IMPORTEDLISTCURSOR cursor;
declare @SEGMENTID uniqueidentifier;
declare @TESTSEGMENTID uniqueidentifier;
declare @SEGMENTSOURCECODE nvarchar(255);
declare @LISTID uniqueidentifier;
declare @LISTMATCHBACKTABLE nvarchar(128);
declare @CONSTITUENTSEGMENTTABLE nvarchar(128);
declare @RECORDSOURCEID uniqueidentifier;
declare @GIFTVIEWNAME nvarchar(255);
declare @GIFTVIEWDONORIDFIELD nvarchar(255);
declare @GIFTVIEWAPPEALIDFIELD nvarchar(255);
declare @GIFTVIEWAPPEALSYSTEMIDFIELD nvarchar(255);
declare @GIFTVIEWMAILINGIDFIELD nvarchar(255);
declare @GIFTVIEWSOURCECODEFIELD nvarchar(255);
declare @GIFTVIEWFINDERNUMBERFIELD nvarchar(255);
declare @GIFTVIEWPRIMARYKEYFIELD nvarchar(255);
declare @APPEALID nvarchar(100);
declare @APPEALSYSTEMID nvarchar(36);
declare @SQL nvarchar(max);
declare @PARAMDEF nvarchar(255);
declare @DATATABLE nvarchar(128);
declare @MAILINGID nvarchar(36);
declare @TEMPTABLENAME nvarchar(128);
declare @ISBBEC bit;
declare @ACTIVE bit;
declare @HASVENDORMANAGEDSEGMENTS bit;
declare @FINDERNUMBER bigint;
begin try
set @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);
select
@MAILINGID = (case when @ISBBEC = 1 then convert(nvarchar(36), [ID]) else convert(nvarchar(36), [IDINTEGER]) end),
@DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID]),
@ACTIVE = [ACTIVE]
from dbo.[MKTSEGMENTATION]
where [ID] = @SEGMENTATIONID;
--Always start from scratch so we get the most accurate gift information. If the appealID changed
--on a gift, then that needs to be reflected here by removing the matchback from this mailing.
exec dbo.[USP_MKTSEGMENTATION_MATCHBACKDELETE] @SEGMENTATIONID, 1;
/******************************************************************************/
/* Imported list matchback */
/******************************************************************************/
--Create a table of all imported lists in the mailing...
create table #IMPORTEDLISTS (
[QUERYVIEWCATALOGID] uniqueidentifier not null,
[RECORDSOURCEID] uniqueidentifier not null,
constraint [PK_IMPORTEDLISTS] primary key clustered ([QUERYVIEWCATALOGID] asc)
);
set @SQL = 'insert into #IMPORTEDLISTS ([QUERYVIEWCATALOGID], [RECORDSOURCEID])' + char(13) +
' select distinct' + char(13) +
' [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],' + char(13) +
' [MKTLIST].[RECORDSOURCEID]' + char(13) +
' from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
' inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = [DONORS].[DONORQUERYVIEWCATALOGID]' + char(13) +
' inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]';
exec (@SQL);
set @PARAMDEF = '@SEGMENTATIONID uniqueidentifier, @RECORDSOURCEID uniqueidentifier, @APPEALSYSTEMID nvarchar(36), @APPEALID nvarchar(100)';
--Loop through each record source in the mailing...
declare RECORDSOURCECURSOR cursor local fast_forward for
select distinct [RECORDSOURCEID]
from #IMPORTEDLISTS;
open RECORDSOURCECURSOR;
fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
while (@@FETCH_STATUS = 0)
begin
--Gather some info about the gift view to use...
select distinct
@GIFTVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
@GIFTVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
@GIFTVIEWDONORIDFIELD = [MKTGIFTRECORDSOURCE].[DONORIDFIELD],
@GIFTVIEWFINDERNUMBERFIELD = [MKTGIFTRECORDSOURCE].[FINDERNUMBERFIELD],
@GIFTVIEWAPPEALIDFIELD = [MKTGIFTRECORDSOURCE].[APPEALIDFIELD],
@GIFTVIEWAPPEALSYSTEMIDFIELD = [MKTGIFTRECORDSOURCE].[APPEALSYSTEMIDFIELD],
@APPEALID = isnull([MKTSEGMENTATIONACTIVATE].[APPEALID],''),
@APPEALSYSTEMID = isnull([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID],''),
@LISTMATCHBACKTABLE = dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([MKTGIFTRECORDSOURCE].[ID])
from dbo.[MKTGIFTRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTGIFTRECORDSOURCE].[ID]
where [MKTGIFTRECORDSOURCE].[ID] = @RECORDSOURCEID;
--Update any gifts that may already be matched back for a different mailing, because they would not have gotten deleted above...
--Insert matched records into the list matchback table...
--Delete any old records that were previously matched to this mailing but are not anymore...
set @SQL = 'merge into dbo.[' + @LISTMATCHBACKTABLE + '] t' + char(13) +
'using (' + char(13) +
' select' + char(13) +
' [MKTSEGMENTLISTDATA].[ID],' + char(13) +
' [GIFTS].[' + @GIFTVIEWDONORIDFIELD + '] as [DONORID],' + char(13) +
' [GIFTS].[' + @GIFTVIEWPRIMARYKEYFIELD + '] as [GIFTID]' + char(13) +
' from dbo.[' + @GIFTVIEWNAME + '] as [GIFTS]' + char(13) +
' inner join dbo.[' + @DATATABLE + '] as [DONORS] on [DONORS].[FINDERNUMBER] = [GIFTS].[' + @GIFTVIEWFINDERNUMBERFIELD + ']' + char(13) +
' inner join dbo.[MKTSEGMENTLISTDATA] on [MKTSEGMENTLISTDATA].[ID] = [DONORS].[DONORID]' + char(13) +
' where [DONORS].[DONORQUERYVIEWCATALOGID] in (select [QUERYVIEWCATALOGID] from #IMPORTEDLISTS where [RECORDSOURCEID] = @RECORDSOURCEID)' + char(13) +
' and [GIFTS].' + (case when len(@APPEALSYSTEMID) > 0 then '[' + @GIFTVIEWAPPEALSYSTEMIDFIELD + '] = @APPEALSYSTEMID' else '[' + @GIFTVIEWAPPEALIDFIELD + '] = @APPEALID' end) + char(13) +
') s on s.[GIFTID] = t.[GIFTID]' + char(13) +
'when matched and (s.[ID] <> t.[ID] or s.[DONORID] <> t.[DONORID] or t.[SEGMENTATIONID] <> @SEGMENTATIONID) then' + char(13) +
' update set [ID] = s.[ID], [DONORID] = s.[DONORID], [SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
'when not matched then' + char(13) +
' insert ([ID], [DONORID], [GIFTID], [SEGMENTATIONID])' + char(13) +
' values (s.[ID], s.[DONORID], s.[GIFTID], @SEGMENTATIONID)' + char(13) +
'when not matched by source and t.[SEGMENTATIONID] = @SEGMENTATIONID then' + char(13) +
' delete;';
exec sp_executesql @SQL, @PARAMDEF,
@SEGMENTATIONID = @SEGMENTATIONID,
@RECORDSOURCEID = @RECORDSOURCEID,
@APPEALSYSTEMID = @APPEALSYSTEMID,
@APPEALID = @APPEALID;
fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
end;
close RECORDSOURCECURSOR;
deallocate RECORDSOURCECURSOR;
--Clean up the temp table...
drop table #IMPORTEDLISTS;
/******************************************************************************/
/* Vendor managed list matchback */
/******************************************************************************/
set @HASVENDORMANAGEDSEGMENTS = (case when exists(
select top 1 1
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = (case when @ACTIVE = 0 then [MKTSEGMENT].[CURRENTSEGMENTLISTID] else [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] end)
where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENTLIST].[TYPECODE] = 1)
then 1 else 0 end);
if @HASVENDORMANAGEDSEGMENTS = 1
begin
--Loop through each vendor managed segment/test segment in the mailing and insert donor matchback info...
declare VENDORMANAGEDSEGMENTCURSOR cursor local fast_forward for
select
[TEMP].[ID],
[TEMP].[TESTID],
dbo.[UFN_MKTSOURCECODE_BUILDCODE](isnull([TEMP].[TESTID], [TEMP].[ID]), default, default),
[MKTLIST].[ID],
[MKTLIST].[RECORDSOURCEID]
from (
select [MKTSEGMENTATIONSEGMENT].[ID], [MKTSEGMENTLIST].[LISTID], [MKTSEGMENTATIONSEGMENT].[SEQUENCE], null as [TESTID], null as [TESTSEQUENCE]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = (case when @ACTIVE = 0 then [MKTSEGMENT].[CURRENTSEGMENTLISTID] else [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] end)
where [SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENTLIST].[TYPECODE] = 1
union
select [SEG].[ID], [MKTSEGMENTLIST].[LISTID], [SEG].[SEQUENCE], [TESTSEG].[ID] as [TESTID], [TESTSEG].[SEQUENCE] as [TESTSEQUENCE]
from [MKTSEGMENTATIONSEGMENT] as [SEG]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [SEG].[SEGMENTID]
left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [SEG].[ID]
inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = (case when @ACTIVE = 0 then [MKTSEGMENT].[CURRENTSEGMENTLISTID] else [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] end)
left join [MKTSEGMENTATIONTESTSEGMENT] as [TESTSEG] on [TESTSEG].[SEGMENTID] = [SEG].[ID]
where [SEG].[SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENTLIST].[TYPECODE] = 1
) as [TEMP]
inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [TEMP].[LISTID]
group by [TEMP].[ID], [TEMP].[LISTID], [TEMP].[TESTID], [TEMP].[SEQUENCE], [TEMP].[TESTSEQUENCE], [MKTLIST].[ID], [MKTLIST].[RECORDSOURCEID]
order by [TEMP].[SEQUENCE], [TEMP].[TESTSEQUENCE];
open VENDORMANAGEDSEGMENTCURSOR;
fetch next from VENDORMANAGEDSEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID, @SEGMENTSOURCECODE, @LISTID, @RECORDSOURCEID;
while (@@FETCH_STATUS = 0)
begin
--Gather some info about the gift view to use...
select distinct
@GIFTVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
@GIFTVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
@GIFTVIEWDONORIDFIELD = [MKTGIFTRECORDSOURCE].[DONORIDFIELD],
@GIFTVIEWAPPEALIDFIELD = [MKTGIFTRECORDSOURCE].[APPEALIDFIELD],
@GIFTVIEWAPPEALSYSTEMIDFIELD = [MKTGIFTRECORDSOURCE].[APPEALSYSTEMIDFIELD],
@GIFTVIEWMAILINGIDFIELD = [MKTGIFTRECORDSOURCE].[MAILINGIDFIELD],
@GIFTVIEWSOURCECODEFIELD = [MKTGIFTRECORDSOURCE].[SOURCECODEFIELD],
@APPEALID = isnull([MKTSEGMENTATIONACTIVATE].[APPEALID],''),
@APPEALSYSTEMID = isnull([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID],''),
@LISTMATCHBACKTABLE = dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([MKTGIFTRECORDSOURCE].[ID]),
@CONSTITUENTSEGMENTTABLE = dbo.[UFN_CONSTITUENTSEGMENT_MAKETABLENAME]([MKTGIFTRECORDSOURCE].[ID])
from dbo.[MKTGIFTRECORDSOURCE]
inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTGIFTRECORDSOURCE].[ID]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTGIFTRECORDSOURCE].[ID] = @RECORDSOURCEID;
--Create a global temp table because we need to use it in the dynamic sql below...
set @TEMPTABLENAME = '##TEMP_' + replace(cast(newid() as nvarchar(36)), '-', '_');
--Select the list donor ID and the source system donor ID into a temp table...
set @SQL = 'select' + char(13) +
' newid() as [ID],' + char(13) +
' [' + @GIFTVIEWDONORIDFIELD + '] as [DONORID],' + char(13) +
' [' + @GIFTVIEWPRIMARYKEYFIELD + '] as [GIFTID]' + char(13) +
'into dbo.[' + @TEMPTABLENAME + ']' + char(13) +
'from dbo.[' + @GIFTVIEWNAME + ']' + char(13) +
'where ' + (case when len(@APPEALSYSTEMID) > 0
then '[' + @GIFTVIEWAPPEALSYSTEMIDFIELD + '] = @APPEALSYSTEMID'
else '[' + @GIFTVIEWAPPEALIDFIELD + '] = @APPEALID'
end) + char(13) +
'and [' + @GIFTVIEWMAILINGIDFIELD + '] = @MAILINGID' + char(13) +
'and [' + @GIFTVIEWSOURCECODEFIELD + '] = @SEGMENTSOURCECODE';
exec sp_executesql @SQL, N'@APPEALSYSTEMID nvarchar(36), @APPEALID nvarchar(100), @MAILINGID nvarchar(36), @SEGMENTSOURCECODE nvarchar(255)', @APPEALSYSTEMID = @APPEALSYSTEMID, @APPEALID = @APPEALID, @MAILINGID = @MAILINGID, @SEGMENTSOURCECODE = @SEGMENTSOURCECODE;
--Update the temp table so that donors that gave multiple gifts will have the same ID...
set @SQL = 'update dbo.[' + @TEMPTABLENAME + '] set' + char(13) +
' [ID] = (select top 1 [T1].[ID] from dbo.[' + @TEMPTABLENAME + '] as [T1] where [T1].[DONORID] = [' + @TEMPTABLENAME + '].[DONORID])';
exec (@SQL);
--Remove any rows from the CONSTITUENTSEGMENT table that may already be matched back for a different mailing, because they would not have gotten deleted above.
--If we don't do this here, then we will end up with orphaned rows in the CONSTITUENTSEGMENT table until the other mailing is refreshed.
set @SQL = 'delete from dbo.[' + @CONSTITUENTSEGMENTTABLE + ']' + char(13) +
'from dbo.[' + @CONSTITUENTSEGMENTTABLE + '] as [CS]' + char(13) +
'inner join dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTDONORS] on [LISTDONORS].[SEGMENTATIONID] = @SEGMENTATIONID and [LISTDONORS].[DONORID] = [CS].[CONSTITUENTID]' + char(13) +
'inner join dbo.[' + @TEMPTABLENAME + '] as [TEMP] on [TEMP].[GIFTID] = [LISTDONORS].[GIFTID]' + char(13) +
'where [CS].[FINDERNUMBER] = 0';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;
--Update any gifts that may already be matched back for a different mailing, because they would not have gotten deleted above...
--Insert matched records into the list matchback table...
--Delete any old records that were previously matched to this mailing but are not anymore...
set @SQL = 'merge into dbo.[' + @LISTMATCHBACKTABLE + '] t' + char(13) +
'using dbo.[' + @TEMPTABLENAME + '] s on s.[GIFTID] = t.[GIFTID]' + char(13) +
'when matched and (s.[ID] <> t.[ID] or s.[DONORID] <> t.[DONORID] or t.[SEGMENTATIONID] <> @SEGMENTATIONID) then' + char(13) +
' update set [ID] = s.[ID], [DONORID] = s.[DONORID], [SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
'when not matched then' + char(13) +
' insert ([ID], [DONORID], [GIFTID], [SEGMENTATIONID])' + char(13) +
' values (s.[ID], s.[DONORID], s.[GIFTID], @SEGMENTATIONID);';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;
--Get smallest finder number to count back from
set @SQL = 'select @FINDERNUMBER = isnull(min([FINDERNUMBER]),0) from dbo.[' + @DATATABLE + '];';
exec sp_executesql @SQL, N'@FINDERNUMBER bigint output', @FINDERNUMBER = @FINDERNUMBER output;
if @FINDERNUMBER > 0
set @FINDERNUMBER = 0;
--Insert the donors into the mailing activated data table...
set @SQL = 'insert into dbo.[' + @DATATABLE + '] ([FINDERNUMBER], [SEGMENTID], [TESTSEGMENTID], [DONORID], [DONORQUERYVIEWCATALOGID], [SOURCECODE], [ASKLADDERID]' + (case when @ISBBEC = 1 then ', [CONSTITUENTAPPEALID])' else ')' end) + char(13) +
' select' + char(13) +
' @FINDERNUMBER - row_number() over(order by [TEMP].[ID]),' + char(13) +
' @SEGMENTID,' + char(13) +
' ' + (case when @TESTSEGMENTID is null then 'null' else '@TESTSEGMENTID' end) + ',' + char(13) +
' [TEMP].[ID],' + char(13) +
' null,' + char(13) +
' @SEGMENTSOURCECODE,' + char(13) +
' (select [ASKLADDERID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @SEGMENTID)';
if @ISBBEC = 1
set @SQL = @SQL + ',' + char(13) +
' (select top 1 [ID]' + char(13) +
' from dbo.[CONSTITUENTAPPEAL]' + char(13) +
' where [CONSTITUENTID] = [TEMP].[DONORID]' + char(13) +
' and [MKTSEGMENTATIONSEGMENTID] = @SEGMENTID' + char(13) +
' and [MKTSEGMENTATIONTESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + char(13) +
' and ([FINDERNUMBER] = 0' + char(13) +
' or [FINDERNUMBER] = (select min([FINDERNUMBER])' + char(13) +
' from dbo.[CONSTITUENTAPPEAL]' + char(13) +
' where [CONSTITUENTID] = [TEMP].[DONORID]' + char(13) +
' and [MKTSEGMENTATIONSEGMENTID] = @SEGMENTID' + char(13) +
' and [MKTSEGMENTATIONTESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + ')))';
set @SQL = @SQL + char(13) + ' from (select distinct [ID], [DONORID] from dbo.[' + @TEMPTABLENAME + ']) as [TEMP]';
exec sp_executesql @SQL, N'@FINDERNUMBER bigint, @SEGMENTID uniqueidentifier, @TESTSEGMENTID uniqueidentifier, @SEGMENTSOURCECODE nvarchar(255)', @FINDERNUMBER = @FINDERNUMBER, @SEGMENTID = @SEGMENTID, @TESTSEGMENTID = @TESTSEGMENTID, @SEGMENTSOURCECODE = @SEGMENTSOURCECODE;
--Drop the global temp table...
set @SQL = 'drop table dbo.[' + @TEMPTABLENAME + ']';
exec (@SQL);
fetch next from VENDORMANAGEDSEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID, @SEGMENTSOURCECODE, @LISTID, @RECORDSOURCEID;
end
close VENDORMANAGEDSEGMENTCURSOR;
deallocate VENDORMANAGEDSEGMENTCURSOR;
end
--Rebuild all the indexes on the mailing activated data table to clean up fragmentation...
exec dbo.[USP_MKTCOMMON_REBUILDINDEX] @DATATABLE, null, 100, 1;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;