Membership user redemptions value fact table (Redshift)

factsegmentwid

BIGINT IDENTITY

Surrogate key for the membership value fact record

PK

factsegmentid

BIGINT

Business/natural key for the fact segment

centerwid

BIGINT

Foreign key to the center dimension

Bi_DimCenter

centerwid

userwid

BIGINT

Foreign key to the guest/customer user dimension

Bi_DimUser

userwid

salebywid

BIGINT

Foreign key to the employee who made the sale

Bi_DimUser

userwid

closebywid

BIGINT

Foreign key to the employee who closed the invoice

Bi_DimUser

userwid

createdbywid

BIGINT

Foreign key to the employee who created the record

Bi_DimUser

userwid

organizationid

VARCHAR(36)

Unique identifier (GUID) for the organization

itemtypewid

BIGINT

Foreign key to the item type dimension

itemwid

BIGINT

Foreign key to the item dimension

membershipvaluerecordtype

INT

Record type code indicating the nature of the transaction

membershipversionwid

BIGINT

Foreign key to the membership version dimension

usermembershipwid

BIGINT

Foreign key to the user-membership association dimension

membershipstartdate

DATE

Start date of the membership

membershipenddate

DATE

End date of the membership

membershipcancellationdate

DATE

Date the membership was cancelled

graceperiod

DATE

Grace period end date for the membership

membershiptype

INT

Type code of the membership (recurring, non-recurring)

servicewid

BIGINT

Foreign key to the service dimension

categoryid

VARCHAR(36)

GUID of the service category

productwid

BIGINT

Foreign key to the product dimension

quantity

FLOAT

Total quantity of benefits available

quantityredeemed

FLOAT

Quantity of benefits redeemed

saleprice

FLOAT

Sale price of the membership

discount

FLOAT

Discount amount applied

tax

FLOAT

Tax amount applied

finalsaleprice

FLOAT

Final sale price after discount and tax

benefitsaleliability

FLOAT

Liability amount from the benefit sale

membershipirr

FLOAT

Membership initially recognized revenue

membershipmrr

FLOAT

Membership monthly recognized revenue

membershipscr

FLOAT

Membership service credit recognized revenue

membershipirrwithorder

FLOAT

Membership initially recognized revenue with order

membershipmrrwithorder

FLOAT

Membership monthly recognized revenue with order

membershipscrwithorder

FLOAT

Membership service credit recognized revenue with order

benefitlistprice

FLOAT

List price of the benefit item

itemlevelapportionfactor

FLOAT

Apportion factor used for item-level revenue allocation

memberserviceprice

FLOAT

Price of the membership service

redemptionamount

FLOAT

Monetary amount of the redemption

convertedcreditamount

FLOAT

Credit amount converted during benefit usage

remainingcreditamount

FLOAT

Remaining credit amount after conversion

serviceredemptionvaluepercredit

FLOAT

Service redemption value per unit of credit

totalserviceredemptionvalue

FLOAT

Total service redemption value

redemptionvaluewithoutrecognizedrevenuepercredit

FLOAT

Redemption value excluding recognized revenue per credit

totalredemptionvaluewithoutrecognizedrevenue

FLOAT

Total redemption value excluding recognized revenue

convertedserviceredemptionvalue

FLOAT

Converted service redemption value

remainingserviceredemptionvalue

FLOAT

Remaining service redemption value after usage

suspense

FLOAT

Suspense (deferred revenue) amount

iscreditamount

BOOLEAN

Flag indicating if this is a credit amount record

creditbenefitpk

BIGINT

Primary key of the credit benefit record

creditamount

FLOAT

Total credit amount

servicecreditamount

FLOAT

Service credit amount included in the membership

productcreditamount

FLOAT

Product credit amount included in the membership

othercreditamount

FLOAT

Other credit amount included in the membership

adjustedcreditvalue

FLOAT

Adjusted total credit value

adjustedservicecreditvalue

FLOAT

Adjusted service credit value

adjustedproductcreditvalue

FLOAT

Adjusted product credit value

adjustedothercreditvalue

FLOAT

Adjusted other credit value

balancecreditamount

FLOAT

Remaining balance of total credit

balanceservicecreditamount

FLOAT

Remaining balance of service credit

balanceproductcreditamount

FLOAT

Remaining balance of product credit

balanceothercreditamount

FLOAT

Remaining balance of other credit

refundedcreditamount

FLOAT

Total credit amount refunded

refundedservicecreditamount

FLOAT

Service credit amount refunded

refundedproductcreditamount

FLOAT

Product credit amount refunded

refundedothercreditamount

FLOAT

Other credit amount refunded

saledatetimeincenter

TIMESTAMP

Sale date and time in center local timezone

saledateincenter

DATE

Sale date in center local timezone

Bi_DimDate

datewid

closeddatetimeincenter

TIMESTAMP

Invoice closed date and time in center local timezone

closeddateincenter

DATE

Invoice closed date in center local timezone

Bi_DimDate

datewid

createddatetimeincenter

TIMESTAMP

Record created date and time in center local timezone

createddateincenter

DATE

Record created date in center local timezone

Bi_DimDate

datewid

redemptiondatetime

TIMESTAMP

Date and time of the benefit redemption

redeemedinvoiceitemid

VARCHAR(36)

GUID of the redeemed invoice item

accrueddate

DATE

Date when revenue was accrued

benefitexpirydate

DATE

Date when the benefit expires

invoiceid

VARCHAR(36)

Unique identifier (GUID) of the invoice

invoiceitemid

VARCHAR(36)

Unique identifier (GUID) of the invoice item

invoicestatus

INT

Status code of the invoice

groupinvoiceid

VARCHAR(36)

GUID of the group invoice

groupinvoiceorder

INT

Order sequence within the group invoice

invoice_no

NVARCHAR(320)

Human-readable invoice number

receipt_no

NVARCHAR(320)

Human-readable receipt number

refundinvoiceid

VARCHAR(36)

GUID of the refund invoice

refundinvoiceitemid

VARCHAR(36)

GUID of the refund invoice item

totalrefundvalue

FLOAT

Total monetary value of the refund

totalrefundqunatity

FLOAT

Total quantity refunded

itemsalecenterwid

BIGINT

Foreign key to the center where the item was sold

Bi_DimCenter

centerwid

itemredemptioncenterwid

BIGINT

Foreign key to the center where the item was redeemed

Bi_DimCenter

centerwid

userbenefitpk

BIGINT

Primary key of the user benefit record

benefitconversionpk

BIGINT

Primary key for the benefit conversion record

benefitservicename

NVARCHAR(2048)

Name of the benefit service

benefitcategoryname

NVARCHAR(2048)

Name of the benefit category

benefitservicecode

NVARCHAR(2048)

Code of the benefit service

benefitcategorycode

NVARCHAR(2048)

Code of the benefit category

redeemeduserwid

BIGINT

Foreign key to the user who redeemed the benefit

Bi_DimUser

userwid

redeemedusertype

INT

Type code of the redeemed user

redeempostexpiry

BOOLEAN

Flag indicating if redemption is allowed post-expiry

ismanualupdate

BOOLEAN

Flag indicating if the record was manually updated

void

BOOLEAN

Indicates whether the record has been voided

etlcreatedby

BIGINT

ID of the ETL process/user that created this record

etlcreateddate

TIMESTAMP

Timestamp when the ETL process created this record

podname

VARCHAR(200)

Name of the pod (database partition)

groupid

BIGINT

Identifier for the organizational group