V_QUERY_MKTSEGMENTATION
Provides the ability to query marketing efforts.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(255) | Description | |
| MAILINGTYPE | nvarchar(25) | yes | Type |
| SITE | nvarchar(250) | yes | Site |
| CODE | nvarchar(10) | Code | |
| MARKETINGPLANPATH | nvarchar(max) | yes | Marketing plan path |
| STATUS | varchar(13) | Status | |
| ACTIVE | bit | Active | |
| ACTIVATEDATE | datetime | yes | Activate date |
| MAILDATE | datetime | yes | Mail date |
| SOURCECODENAME | nvarchar(100) | yes | Source code name |
| AUTOCALC | int | Auto calc? | |
| MAILINGID | int | Effort ID | |
| DATEREFRESHED | datetime | yes | Date refreshed |
| HOUSEHOLDINGTYPE | nvarchar(40) | yes | Include type |
| HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD | bit | Include qualifying individuals who are not members of any household | |
| HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS | bit | Include qualifying households which do not have any members | |
| HOUSEHOLDINGONERECORDPERHOUSEHOLD | bit | Send to one person per household | |
| OVERRIDEBUSINESSUNITS | bit | Override business units | |
| BUSINESSUNITS | nvarchar(max) | yes | Business units |
| SEGMENTCOUNT | int | yes | Number of segments |
| PACKAGECOUNT | int | yes | Number of packages |
| SEEDCOUNT | int | yes | Number of seeds |
| TESTMAILINGCOUNT | int | yes | Number of test marketing efforts |
| TESTMAILINGID | uniqueidentifier | Test effort ID | |
| PARENTSEGMENTATIONID | uniqueidentifier | yes | Parent effort ID |
| ISTESTMAILING | int | Is test marketing effort? | |
| TESTMAILINGSAMPLESIZE | int | yes | Test marketing effort sample size |
| TESTMAILINGSAMPLESIZETYPE | nvarchar(7) | yes | Test marketing effort sample size type |
| BUDGETAMOUNT | money | yes | Budget |
| 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 |
| ROIAMOUNT | money | yes | ROI (Amount) |
| ROIPERCENT | decimal(23, 8) | yes | ROI (Percent) |
| EXPECTEDCOSTPERDOLLARRAISED | money | yes | Expected cost/dollar raised |
| EXPECTEDRESPONDERS | int | yes | Expected responders |
| EXPECTEDTOTALGIFTAMOUNT | money | yes | Expected total gift amount |
| EXPECTEDAVERAGEGIFTAMOUNT | money | yes | Expected average gift amount |
| EXPECTEDRESPONSERATE | decimal(23, 8) | yes | Expected response rate |
| EXPECTEDROIAMOUNT | money | yes | Expected ROI (Amount) |
| EXPECTEDROIPERCENT | decimal(23, 8) | yes | Expected ROI (Percent) |
| INDIRECTRESPONDERS | int | yes | Indirect responders |
| INDIRECTRESPONSES | int | yes | Indirect responses |
| INDIRECTTOTALGIFTAMOUNT | money | yes | Indirect total gift amount |
| INDIRECTAVERAGEGIFTAMOUNT | money | yes | Indirect average gift amount |
| UNRESOLVEDRESPONDERS | int | yes | Unresolved responders |
| UNRESOLVEDRESPONSES | int | yes | Unresolved responses |
| UNRESOLVEDTOTALGIFTAMOUNT | money | yes | Unresolved total gift amount |
| UNRESOLVEDAVERAGEGIFTAMOUNT | money | yes | Unresolved average gift amount |
| KPICONTEXTRECORDID | nvarchar(100) | yes | KPIs context record ID |
| 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 |
| ORGANIZATIONBUDGETAMOUNT | money | yes | Budget (organization currency) |
| 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) |
| ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT | money | yes | Expected total gift amount (organization currency) |
| ORGANIZATIONEXPECTEDAVERAGEGIFTAMOUNT | money | yes | Expected average gift amount (organization currency) |
| ORGANIZATIONEXPECTEDROIAMOUNT | money | yes | Expected ROI (Amount) (organization currency) |
| ORGANIZATIONINDIRECTTOTALGIFTAMOUNT | money | yes | Indirect total gift amount (organization currency) |
| ORGANIZATIONINDIRECTAVERAGEGIFTAMOUNT | money | yes | Indirect average gift amount (organization currency) |
| ORGANIZATIONUNRESOLVEDTOTALGIFTAMOUNT | money | yes | Unresolved total gift amount (organization currency) |
| ORGANIZATIONUNRESOLVEDAVERAGEGIFTAMOUNT | money | yes | Unresolved average gift amount (organization currency) |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| ORGANIZATIONCURRENCYEXCHANGERATEID | uniqueidentifier | yes | Budget organization currency exchange rate ID |
| SITEID | uniqueidentifier | yes | Site ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:50 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MKTSEGMENTATION AS
select
[MKTSEGMENTATION].[ID],
[MKTSEGMENTATION].[NAME],
[MKTSEGMENTATION].[DESCRIPTION],
[MKTSEGMENTATION].[MAILINGTYPE],
[SITE].[NAME] as [SITE],
[MKTSEGMENTATION].[CODE],
dbo.[UFN_MKTMARKETINGPLANITEM_PATH]([MKTSEGMENTATION].[MARKETINGPLANITEMID],1) as [MARKETINGPLANPATH],
(case when [MKTSEGMENTATION].[ACTIVE] = 1 then 'Activated' else 'Not activated' end) as [STATUS],
[MKTSEGMENTATION].[ACTIVE],
[MKTSEGMENTATION].[ACTIVATEDATE],
[MKTSEGMENTATION].[MAILDATE],
[MKTSOURCECODE].[NAME] as [SOURCECODENAME],
0 as [AUTOCALC],
[MKTSEGMENTATION].[IDINTEGER] as [MAILINGID],
[MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED],
[MKTSEGMENTATION].[HOUSEHOLDINGTYPE],
[MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
[MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
[MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
[MKTSEGMENTATION].[OVERRIDEBUSINESSUNITS],
dbo.[UFN_MKTSEGMENTATIONBUSINESSUNIT_GETBUSINESSUNITDESCRIPTION]([MKTSEGMENTATION].[ID]) as [BUSINESSUNITS],
(select count(*) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [SEGMENTCOUNT],
(select count(*) from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [PACKAGECOUNT],
(select count(*) from dbo.[MKTSEGMENTATIONSEED] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [SEEDCOUNT],
(select count(*) from dbo.[MKTSEGMENTATION] as [TEST] where [TEST].[PARENTSEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [TESTMAILINGCOUNT],
/* Test mailing fields */
[MKTSEGMENTATION].[ID] as [TESTMAILINGID],
[MKTSEGMENTATION].[PARENTSEGMENTATIONID],
(case when [MKTSEGMENTATION].[PARENTSEGMENTATIONID] is null then 0 else 1 end) as [ISTESTMAILING],
(case when [MKTSEGMENTATION].[PARENTSEGMENTATIONID] is null then null else [MKTSEGMENTATION].[SAMPLESIZE] end) as [TESTMAILINGSAMPLESIZE],
(case when [MKTSEGMENTATION].[PARENTSEGMENTATIONID] is null then null else [MKTSEGMENTATION].[SAMPLESIZETYPE] end) as [TESTMAILINGSAMPLESIZETYPE],
/* Calculation fields */
[MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT],
[MKTSEGMENTATIONACTIVE].[QUANTITY],
[MKTSEGMENTATIONACTIVE].[RESPONDERS],
[MKTSEGMENTATIONACTIVE].[RESPONSES],
[MKTSEGMENTATIONACTIVE].[VARIABLECOST],
[MKTSEGMENTATIONBUDGET].[FIXEDCOST],
[MKTSEGMENTATIONACTIVE].[TOTALCOST],
[MKTSEGMENTATIONACTIVE].[COSTPERDOLLARRAISED],
[MKTSEGMENTATIONACTIVE].[TOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[AVERAGEGIFTAMOUNT],
([MKTSEGMENTATIONACTIVE].[RESPONSERATE] / 100) as [RESPONSERATE],
[MKTSEGMENTATIONACTIVE].[ROIAMOUNT],
([MKTSEGMENTATIONACTIVE].[ROIPERCENT] / 100) as [ROIPERCENT],
[MKTSEGMENTATIONACTIVE].[EXPECTEDCOSTPERDOLLARRAISED],
[MKTSEGMENTATIONACTIVE].[EXPECTEDRESPONDERS],
[MKTSEGMENTATIONACTIVE].[EXPECTEDTOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[EXPECTEDAVERAGEGIFTAMOUNT],
([MKTSEGMENTATIONACTIVE].[EXPECTEDRESPONSERATE] / 100) as [EXPECTEDRESPONSERATE],
[MKTSEGMENTATIONACTIVE].[EXPECTEDROIAMOUNT],
([MKTSEGMENTATIONACTIVE].[EXPECTEDROIPERCENT] / 100) as [EXPECTEDROIPERCENT],
[MKTSEGMENTATIONACTIVE].[INDIRECTRESPONDERS],
[MKTSEGMENTATIONACTIVE].[INDIRECTRESPONSES],
[MKTSEGMENTATIONACTIVE].[INDIRECTTOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[INDIRECTAVERAGEGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[UNRESOLVEDRESPONDERS],
[MKTSEGMENTATIONACTIVE].[UNRESOLVEDRESPONSES],
[MKTSEGMENTATIONACTIVE].[UNRESOLVEDTOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[UNRESOLVEDAVERAGEGIFTAMOUNT],
cast([MKTSEGMENTATION].[ID] as nvarchar(100)) as [KPICONTEXTRECORDID],
/* System fields */
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[MKTSEGMENTATION].[DATEADDED],
[MKTSEGMENTATION].[DATECHANGED],
[MKTSEGMENTATION].[TSLONG],
/* Multicurrency fields */
[MKTSEGMENTATIONBUDGET].[ORGANIZATIONBUDGETAMOUNT],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONVARIABLECOST],
[MKTSEGMENTATIONBUDGET].[ORGANIZATIONFIXEDCOST],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALCOST],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONCOSTPERDOLLARRAISED],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONAVERAGEGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONROIAMOUNT],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONEXPECTEDAVERAGEGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONEXPECTEDROIAMOUNT],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONINDIRECTTOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONINDIRECTAVERAGEGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONUNRESOLVEDTOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[ORGANIZATIONUNRESOLVEDAVERAGEGIFTAMOUNT],
[MKTSEGMENTATION].[BASECURRENCYID],
[MKTSEGMENTATIONBUDGET].[ORGANIZATIONCURRENCYEXCHANGERATEID],
[MKTSEGMENTATION].[SITEID]
/*#EXTENSION*/
from dbo.[MKTSEGMENTATION]
left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
left outer join dbo.[MKTSEGMENTATIONACTIVE] on [MKTSEGMENTATIONACTIVE].[ID] = [MKTSEGMENTATION].[ID]
left outer join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
left outer join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
left outer join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENTATION].[SITEID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [MKTSEGMENTATION].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [MKTSEGMENTATION].[CHANGEDBYID]
where [MKTSEGMENTATION].[COMMUNICATIONTYPECODE] < 2