V_QUERY_MKTSEGMENTATIONTESTSEGMENT
Provides the ability to query a marketing effort segment's test segments.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| SEGMENTATIONID | uniqueidentifier | yes | Segmentation ID |
| SEGMENTID | uniqueidentifier | Segment ID | |
| ID | uniqueidentifier | System record ID | |
| ASKLADDERID | uniqueidentifier | yes | Ask ladder ID |
| NAME | nvarchar(203) | yes | Name |
| DESCRIPTION | nvarchar(255) | Description | |
| CODE | nvarchar(10) | Code | |
| TESTSEGMENTCODE | nvarchar(10) | Test segment code | |
| PACKAGEID | uniqueidentifier | Package ID | |
| SAMPLESIZE | int | Sample size | |
| SAMPLESIZETYPE | nvarchar(8) | yes | Sample size type |
| SAMPLESIZEMETHOD | nvarchar(6) | yes | Sample size method |
| SOURCECODE | nvarchar(50) | yes | Source code |
| SEQUENCE | int | Sequence | |
| OVERRIDEBUSINESSUNITS | bit | Override business units | |
| BUSINESSUNITS | nvarchar(max) | yes | Business units |
| QUANTITY | int | yes | Quantity |
| RESPONDERS | int | yes | Responders |
| RESPONSES | int | yes | Responses |
| VARIABLECOST | money | yes | Variable cost |
| FIXEDCOST | money | yes | Fixed cost |
| TOTALCOST | money | yes | Total cost |
| COSTPERDOLLARRAISED | money | yes | Cost/dollar raised |
| TOTALGIFTAMOUNT | money | yes | Total gift amount |
| AVERAGEGIFTAMOUNT | money | yes | Average gift amount |
| RESPONSERATE | decimal(23, 8) | yes | Response rate |
| LIFT | decimal(19, 4) | yes | Lift |
| ROIAMOUNT | money | yes | ROI (Amount) |
| ROIPERCENT | decimal(23, 8) | yes | ROI (Percent) |
| EXPECTEDCOSTPERDOLLARRAISED | money | yes | Expected cost/dollar raised |
| EXPECTEDRESPONDERS | int | yes | Expected responders |
| EXPECTEDGIFTAMOUNT | money | Expected gift amount | |
| EXPECTEDTOTALGIFTAMOUNT | money | yes | Expected total gift amount |
| EXPECTEDRESPONSERATE | decimal(9, 6) | yes | Expected response rate |
| EXPECTEDROIAMOUNT | money | yes | Expected ROI (Amount) |
| EXPECTEDROIPERCENT | decimal(23, 8) | yes | Expected ROI (Percent) |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| CURRENCYEXCHANGERATEID | uniqueidentifier | yes | Currency exchange rate ID |
| ORGANIZATIONVARIABLECOST | money | yes | Variable cost (organization currency) |
| ORGANIZATIONFIXEDCOST | money | yes | Fixed cost (organization currency) |
| ORGANIZATIONTOTALCOST | money | yes | Total cost (organization currency) |
| ORGANIZATIONCOSTPERDOLLARRAISED | money | yes | Cost/dollar raised (organization currency) |
| ORGANIZATIONTOTALGIFTAMOUNT | money | yes | Total gift amount (organization currency) |
| ORGANIZATIONAVERAGEGIFTAMOUNT | money | yes | Average gift amount (organization currency) |
| ORGANIZATIONROIAMOUNT | money | yes | ROI (Amount) (organization currency) |
| ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED | money | yes | Expected cost/dollar raised (organization currency) |
| ORGANIZATIONEXPECTEDGIFTAMOUNT | money | Expected gift amount (organization currency) | |
| ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT | money | yes | Expected total gift amount (organization currency) |
| ORGANIZATIONEXPECTEDROIAMOUNT | money | yes | Expected ROI (Amount) (organization currency) |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:14:10 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MKTSEGMENTATIONTESTSEGMENT AS
select
[MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
[MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID],
[MKTSEGMENTATIONTESTSEGMENT].[ID],
[MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID],
dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_BUILDNAME]([MKTSEGMENTATIONTESTSEGMENT].[PREFIXCODE], [MKTSEGMENTATIONTESTSEGMENT].[NAME], [MKTSEGMENT].[NAME], [MKTSEGMENT].[CODE]) as [NAME],
[MKTSEGMENTATIONTESTSEGMENT].[DESCRIPTION],
[MKTSEGMENTATIONTESTSEGMENT].[CODE],
[MKTSEGMENTATIONTESTSEGMENT].[TESTSEGMENTCODE],
[MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
[MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZE],
[MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZETYPE],
[MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZEMETHOD],
(case when [MKTSEGMENTATION].[SOURCECODEID] is null
then /* No source code specified for the mailing so don't return anything */
''
else
dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTATIONTESTSEGMENT].[ID], default, default)
end) as [SOURCECODE],
[MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE],
[MKTSEGMENTATIONTESTSEGMENT].[OVERRIDEBUSINESSUNITS],
dbo.[UFN_MKTSEGMENTATIONTESTSEGMENTBUSINESSUNIT_GETBUSINESSUNITDESCRIPTION]([MKTSEGMENTATIONTESTSEGMENT].[ID]) as [BUSINESSUNITS],
/* Calculation fields */
[MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY],
[MKTSEGMENTATIONSEGMENTACTIVE].[RESPONDERS],
[MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES],
[MKTSEGMENTATIONSEGMENTACTIVE].[VARIABLECOST],
[MKTSEGMENTATIONSEGMENTACTIVE].[FIXEDCOST],
[MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST],
[MKTSEGMENTATIONSEGMENTACTIVE].[COSTPERDOLLARRAISED],
[MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[AVERAGEGIFTAMOUNT],
([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSERATE] / 100) as [RESPONSERATE],
[MKTSEGMENTATIONSEGMENTACTIVE].[LIFT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ROIAMOUNT],
([MKTSEGMENTATIONSEGMENTACTIVE].[ROIPERCENT] / 100) as [ROIPERCENT],
[MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDCOSTPERDOLLARRAISED],
[MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDRESPONDERS],
[MKTSEGMENTATIONTESTSEGMENT].[GIFTAMOUNT] as [EXPECTEDGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDTOTALGIFTAMOUNT],
([MKTSEGMENTATIONTESTSEGMENT].[RESPONSERATE] / 100) as [EXPECTEDRESPONSERATE],
[MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDROIAMOUNT],
([MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDROIPERCENT] / 100) as [EXPECTEDROIPERCENT],
/* System fields */
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[MKTSEGMENTATIONTESTSEGMENT].[DATEADDED],
[MKTSEGMENTATIONTESTSEGMENT].[DATECHANGED],
[MKTSEGMENTATIONTESTSEGMENT].[TSLONG],
[MKTSEGMENTATIONSEGMENT].[CURRENCYEXCHANGERATEID],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONVARIABLECOST],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONFIXEDCOST],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONCOSTPERDOLLARRAISED],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONAVERAGEGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONROIAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED],
[MKTSEGMENTATIONTESTSEGMENT].[ORGANIZATIONGIFTAMOUNT] as [ORGANIZATIONEXPECTEDGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONEXPECTEDROIAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[BASECURRENCYID]
/*#EXTENSION*/
from dbo.[MKTSEGMENTATIONTESTSEGMENT]
left outer join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] and [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
left outer join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
left outer join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[CHANGEDBYID]