V_QUERY_MKTSEGMENTATIONSEGMENT
Provides the ability to query a marketing effort's segments.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| SEGMENTATIONID | uniqueidentifier | Segmentation ID | |
| ID | uniqueidentifier | System record ID | |
| MKTSEGMENTID | uniqueidentifier | yes | Segment ID |
| ASKLADDERID | uniqueidentifier | yes | Ask ladder ID |
| NAME | nvarchar(100) | yes | Name |
| DESCRIPTION | nvarchar(255) | yes | Description |
| CODE | nvarchar(10) | Code | |
| TESTSEGMENTCODE | nvarchar(10) | Test segment code | |
| PACKAGEID | uniqueidentifier | yes | Package ID |
| SAMPLESIZE | int | Sample size | |
| SAMPLESIZETYPE | nvarchar(7) | yes | Sample size type |
| SAMPLESIZEMETHOD | nvarchar(6) | yes | Sample size method |
| SAMPLESIZEEXCLUDEREMAINDER | bit | Exclude remaining records from the marketing effort? | |
| SOURCECODE | nvarchar(50) | yes | Source code |
| SEGMENTTYPE | nvarchar(18) | yes | Segment type |
| TESTSEGMENTCOUNT | int | yes | Number of test segments |
| 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 |
| EXCLUDE | bit | Exclusion |
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_MKTSEGMENTATIONSEGMENT AS
select
[MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
[MKTSEGMENTATIONSEGMENT].[ID],
[MKTSEGMENTATIONSEGMENT].[SEGMENTID] as [MKTSEGMENTID],
[MKTSEGMENTATIONSEGMENT].[ASKLADDERID],
[MKTSEGMENT].[NAME],
[MKTSEGMENT].[DESCRIPTION],
[MKTSEGMENTATIONSEGMENT].[CODE],
[MKTSEGMENTATIONSEGMENT].[TESTSEGMENTCODE],
[MKTSEGMENTATIONSEGMENT].[PACKAGEID],
[MKTSEGMENTATIONSEGMENT].[SAMPLESIZE],
[MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPE],
[MKTSEGMENTATIONSEGMENT].[SAMPLESIZEMETHOD],
[MKTSEGMENTATIONSEGMENT].[SAMPLESIZEEXCLUDEREMAINDER],
(case when [MKTSEGMENTATION].[SOURCECODEID] is null
then /* No source code specified for the mailing so don't return anything */
''
else
dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTATIONSEGMENT].[ID], default, default)
end) as [SOURCECODE],
[MKTSEGMENT].[SEGMENTTYPE],
(select count(*) from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]) as [TESTSEGMENTCOUNT],
(case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then
0 /* Public media segments have no defined sequence in the effort */
else
[MKTSEGMENTATIONSEGMENT].[SEQUENCE]
end) as [SEQUENCE],
[MKTSEGMENTATIONSEGMENT].[OVERRIDEBUSINESSUNITS],
dbo.[UFN_MKTSEGMENTATIONSEGMENTBUSINESSUNIT_GETBUSINESSUNITDESCRIPTION]([MKTSEGMENTATIONSEGMENT].[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],
[MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] as [EXPECTEDGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDTOTALGIFTAMOUNT],
([MKTSEGMENTATIONSEGMENT].[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],
[MKTSEGMENTATIONSEGMENT].[DATEADDED],
[MKTSEGMENTATIONSEGMENT].[DATECHANGED],
[MKTSEGMENTATIONSEGMENT].[TSLONG],
[MKTSEGMENTATIONSEGMENT].[CURRENCYEXCHANGERATEID],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONVARIABLECOST],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONFIXEDCOST],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONCOSTPERDOLLARRAISED],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONAVERAGEGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONROIAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED],
[MKTSEGMENTATIONSEGMENT].[ORGANIZATIONGIFTAMOUNT] as [ORGANIZATIONEXPECTEDGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONEXPECTEDROIAMOUNT],
[MKTSEGMENTATIONSEGMENTACTIVE].[BASECURRENCYID],
[MKTSEGMENTATIONSEGMENT].[EXCLUDE]
/*#EXTENSION*/
from dbo.[MKTSEGMENTATIONSEGMENT]
left outer join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left outer join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] is null
left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [MKTSEGMENTATIONSEGMENT].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [MKTSEGMENTATIONSEGMENT].[CHANGEDBYID];