DIM_MARKETINGSEGMENT_STAGE
Contains information about marketing segments
Fields
| Field | Field Type | Null | Notes | Description |
|---|---|---|---|---|
| SEGMENTATIONSEGMENTSYSTEMID | uniqueidentifier | yes | dbo.[MKTSEGMENTATIONSEGMENT].[ID] | |
| SEGMENTATIONTESTSEGMENTSYSTEMID | uniqueidentifier | yes | dbo.[MKTSEGMENTATIONTESTSEGMENT].[ID] | |
| APPEALDIMID | int | yes | Reference key to the appeal dimension, derived from dbo.[MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] | |
| SEGMENTATIONSITEDIMID | int | yes | Reference key to the site dimension, derived from dbo.[MKTSEGMENTATION].[SITEID] | |
| SEGMENTATIONID | int | yes | dbo.[MKTSEGMENTATION].[IDINTEGER] | |
| SEGMENTATIONCODE | nvarchar(10) | yes | dbo.[MKTSEGMENTATION].[CODE] | |
| SEGMENTATIONNAME | nvarchar(100) | yes | dbo.[MKTSEGMENTATION].[NAME] | |
| SEGMENTATIONDESCRIPTION | nvarchar(255) | yes | dbo.[MKTSEGMENTATION].[DESCRIPTION] | |
| SEGMENTATIONACTIVE | bit | yes | dbo.[MKTSEGMENTATION].[ACTIVE] | |
| SEGMENTATIONBUDGETAMOUNT | money | yes | dbo.[MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT] | |
| SEGMENTATIONTYPE | nvarchar(25) | yes | dbo.[MKTSEGMENTATION].[MAILINGTYPE] | |
| SEGMENTATIONDATE | date | yes | If dbo.[MKTSEGMENTATION].[MAILDATE] is not null then dbo.[MKTSEGMENTATION].[MAILDATE] else dbo.[MKTSEGMENTATION].[ACTIVATEDATE] | |
| SEGMENTATIONDATEDIMID | int | yes | Reference key to the date dimension, derived from BBDW.[MARKETINGSEGMENT].[SEGMENTATIONDATE] | |
| HOUSEHOLDINGTYPE | nvarchar(40) | yes | dbo.[MKTSEGMENTATION].[HOUSEHOLDINGTYPE] | |
| SEGMENTNAME | nvarchar(100) | yes | dbo.[MKTSEGMENT].[NAME] | |
| SEGMENTDESCRIPTION | nvarchar(255) | yes | dbo.[MKTSEGMENT].[DESCRIPTION] | |
| SEGMENTCODE | nvarchar(10) | yes | dbo.[MKTSEGMENT].[CODE] | |
| SEGMENTTYPE | nvarchar(18) | yes | dbo.[MKTSEGMENT].[SEGMENTTYPE] | |
| SEGMENTCATEGORY | nvarchar(100) | yes | dbo.[MKTSEGMENTCATEGORYCODE].[DESCRIPTION] | |
| SEGMENTRESPONSERATE | decimal(5, 2) | yes | dbo.[MKTSEGMENTATIONSEGMENT].[RESPONSERATE] | |
| SEGMENTGIFTAMOUNT | money | yes | dbo.[MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] | |
| SEGMENTUSAGE | nvarchar(12) | yes | dbo.[MKTSEGMENTATIONSEGMENT].[USAGE] | |
| SEGMENTATIONSEGMENTNAME | nvarchar(203) | yes | dbo.[MKTSEGMENTATIONTESTSEGMENT].[NAME] | |
| SEGMENTATIONSEGMENTDESCRIPTION | nvarchar(255) | yes | dbo.[MKTSEGMENTATIONTESTSEGMENT].[DESCRIPTION] | |
| SEGMENTATIONSEGMENTCODE | nvarchar(10) | yes | dbo.[MKTSEGMENTATIONTESTSEGMENT].[CODE] | |
| PACKAGECODE | nvarchar(10) | yes | dbo.[MKTPACKAGE].[CODE] | |
| PACKAGENAME | nvarchar(100) | yes | dbo.[MKTPACKAGE].[NAME] | |
| PACKAGEDESCRIPTION | nvarchar(255) | yes | dbo.[MKTPACKAGE].[DESCRIPTION] | |
| PACKAGEUNITCOST | money | yes | dbo.[MKTPACKAGE].[UNITCOST] | |
| PACKAGECHANNEL | nvarchar(12) | yes | dbo.[MKTPACKAGE].[CHANNEL] | |
| PACKAGECATEGORY | nvarchar(100) | yes | dbo.[MKTPACKAGECATEGORYCODE].[DESCRIPTION] | |
| SEGMENTATIONPACKAGECODE | nvarchar(10) | yes | dbo.[MKTSEGMENTATIONPACKAGE].[CODE] | |
| SEGMENTATIONPACKAGEUNITCOST | money | yes | dbo.[MKTSEGMENTATIONPACKAGE].[UNITCOST] | |
| LETTERCODE | nvarchar(100) | yes | dbo.[LETTERCODE].[NAME] | |
| LETTERCODEDESCRIPTION | nvarchar(255) | yes | dbo.[LETTERCODE].[DESCRIPTION] | |
| LETTERINCLUDESRECEIPT | bit | yes | dbo.[LETTERCODE].[INCLUDESRECEIPT] | |
| ISTESTSEGMENT | bit | yes | If [SEGMENTATIONTESTSEGMENTSYSTEMID] is null then 0 else 1 | |
| SEGMENTATIONSEGMENTQUANTITY | int | yes | dbo.[MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY] | |
| SEGMENTATIONSYSTEMID | uniqueidentifier | yes | [dbo].[MKTSEGMENTATION].[ID] | |
| BASECURRENCYDIMID | int | yes | Reference key to the currency dimension, derived from dbo.[RECURRINGGIFTDEVELOPMENTFUNCTIONHISTORY].[BASECURRENCYID] | |
| SEGMENTATIONCOMMUNICATIONTYPE | nvarchar(25) | yes | dbo.[MKTSEGMENTATION].[COMMUNICATIONTYPE] | |
| SEGMENTSITEDIMID | int | yes | Reference key to the site dimension, derived using [dbo].[MKTSEGMENT].[SITEID] | |
| PACKAGESITEDIMID | int | yes | Reference key to the site dimension, derived using [dbo].[MKTPACKAGE].[SITEID] | |
| SEGMENTSYSTEMID | uniqueidentifier | yes | dbo.[MKTSEGMENT].[ID] | |
| MARKETINGSEGMENTATIONDIMID | int | yes | Reference key to the marketing effort dimension, derived from dbo.[MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] | |
| ASKLADDERNAME | nvarchar(100) | yes | dbo.[MKTASKLADDER].[NAME] | |
| SAMPLESIZE | int | yes | dbo.[MKTSEGMENTATIONSEGMENT].[SAMPLESIZE] | |
| SEGMENTATIONSEGMENTTOTALGIFTAMOUNT | money | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT] | |
| SEGMENTATIONSEGMENTRESPONSES | int | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES] | |
| SEGMENTATIONSEGMENTTOTALCOST | money | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST] | |
| SEGMENTATIONSEGMENTROIAMOUNT | money | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[ROIAMOUNT] | |
| SEGMENTATIONSEGMENTCOSTPERDOLLARRAISED | money | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[COSTPERDOLLARRAISED] | |
| SEGMENTATIONSEGMENTCOSTTOACQUIRE | money | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[COSTTOACQUIRE] | |
| SEGMENTATIONSEGMENTAVERAGEGIFTAMOUNT | money | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[AVERAGEGIFTAMOUNT] | |
| SEGMENTATIONSEGMENTRESPONSERATE | decimal(19, 4) | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSERATE] | |
| SEGMENTATIONSEGMENTCOSTPERTHOUSAND | decimal(22, 4) | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[COSTPERTHOUSAND] | |
| SEGMENTATIONSEGMENTGROSSPERTHOUSAND | decimal(22, 4) | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[GROSSPERTHOUSAND] | |
| SEGMENTATIONSEGMENTNETPERTHOUSAND | decimal(22, 4) | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[NETPERTHOUSAND] | |
| SEGMENTATIONSEGMENTROIPERCENT | decimal(19, 4) | yes | [dbo].[MKTSEGMENTATIONSEGMENTACTIVE].[ROIPERCENT] | |
| ISINCLUDED | bit | yes | Flag indicating when data should be included in results. | |
| ETLCONTROLID | int | yes | ID generated through the ETL process. | |
| SOURCEDIMID | int | yes | Source system used. | |
| FIXEDCOSTPERPIECE | numeric(28, 14) | yes | Total fixed cost for each piece in the segment. Calculated based on the dbo.[MKTSEGMENTATIONPACKAGE].[COSTDISTRUBUTIONMETHODCODE] and dbo.[MKTSEGMENTATIONPACKAGE].[UNITCOST] along with dbo.[MKTSEGMENATIONBUDGET].[FIXEDCOST] / dbo.[MKTSEGMENATIONACTIVE].[QUANTITY]. | |
| VARCOSTPERPIECE | numeric(28, 14) | yes | Total variable cost for each piece in the segment. Calculated based on the dbo.[MKTSEGMENTATIONPACKAGE].[COSTDISTRUBUTIONMETHODCODE] and dbo.[MKTSEGMENTATIONPACKAGE].[UNITCOST] along with the result of dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD]. | |
| RESPONSECOSTPERPIECE | numeric(28, 14) | yes | Total response cost for each piece in the segment. Calculated based on the dbo.[MKTSEGMENTATIONPACKAGE].[COSTDISTRUBUTIONMETHODCODE] and dbo.[MKTSEGMENTATIONPACKAGE].[UNITCOST]. | |
| BASEFIXEDCOSTPERPIECE | numeric(28, 14) | yes | Total fixed cost for each piece in the segment. Calculated based on the dbo.[MKTSEGMENTATIONPACKAGE].[COSTDISTRUBUTIONMETHODCODE] and dbo.[MKTSEGMENTATIONPACKAGE].[BASEUNITCOST] along with dbo.[MKTSEGMENATIONBUDGET].[BASEFIXEDCOST] / dbo.[MKTSEGMENATIONACTIVE].[QUANTITY]. | |
| BASEVARCOSTPERPIECE | numeric(28, 14) | yes | Total variable cost for each piece in the segment. Calculated based on the dbo.[MKTSEGMENTATIONPACKAGE].[COSTDISTRUBUTIONMETHODCODE] and dbo.[MKTSEGMENTATIONPACKAGE].[BASEUNITCOST] along with the result of dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD]. | |
| BASERESPONSECOSTPERPIECE | numeric(28, 14) | yes | Total response cost for each piece in the segment. Calculated based on the dbo.[MKTSEGMENTATIONPACKAGE].[COSTDISTRUBUTIONMETHODCODE] and dbo.[MKTSEGMENTATIONPACKAGE].[BASEUNITCOST]. |