Gift Card and Prepaid Card Liability Data Model
This data source brings in all details of gift cards and prepaid cards- sale of a gift card or prepaid card, refund of a gift card or prepaid card, and redemption using a gift card or prepaid card.
Examples of Use-Cases
Sales of gift cards over time
Refund of gift cards over time
Cross center redemptions
Gift card liability at any point of time
Granularity of the Data
At the most granular level, data is organized at an invoice item id level. This means that every line item in the invoice is captured as a separate row in this data source. Only invoice items ids which have either a sale, redemption, or refund of a giftcard or prepaid card are brought in to this data source. In the below example, Tom purchased a prepaid card worth $2000 at a price of $1000 on 12/22/2019 at Center A. Using the prepaid card, he redeemed five items on 12/25/2019 at Center B. Two of the five items were other prepaid cards. On 12/27/2019, he redeemed the left over value at Center A for a service. The below table shows how all the fields are populated:
 |
Group | Description | Field | Definition | Examples/Notes | Data Type |
|---|---|---|---|---|---|
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. 1) Center: Center where the sale invoice for the gift card or prepaid card is generated 2) Redemption Center: Center where the gift card or prepaid card is redeemed for other items. In some situations, it is possible that these two centers are different. The fields in this group detail the various attributes of the center and redemption center. | organization name | The formal name of the organization where the sale invoice for the gift card or prepaid card is generated. | 'Massage Spa Express' is the formal name of an organization. | varchar(256) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. | organization account name | The domain name of the organization in Zenoti. This is the organization where the sale invoice for the gift card or prepaid card is generated. | If you use this URL to log in to Zenoti 'https://massagespa.zenoti.com', then "massagespa" is your account name. The formal name and domain name may be different. | |
Center, Zone, Organization | 1) Center: Center where the sale invoice for the gift card or prepaid card is generated | center name | The center where the sale invoice for the gift card or prepaid card is generated. | ||
Center, Zone, Organization | 2) Redemption Center: Center where the gift card or prepaid card is redeemed for other items. In some situations, it is possible that these two centers are different. The fields in this group detail the various attributes of the center and redemption center. | zone name | The zone to which the center belongs. | ||
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. | center city | The city where the center is located. | ||
Center, Zone, Organization | 1) Center: Center where the sale invoice for the gift card or prepaid card is generated | center state name | The state where the center is located. | varchar(128) | |
Center, Zone, Organization | 2) Redemption Center: Center where the gift card or prepaid card is redeemed for other items. In some situations, it is possible that these two centers are different. The fields in this group detail the various attributes of the center and redemption center. | country name | The country where the center is located. | ||
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. | center zip | The ZIP code to which the center belongs. | ||
Center, Zone, Organization | 1) Center: Center where the sale invoice for the gift card or prepaid card is generated | minutes to add | The number of minutes that need to be added to UTC time to get the current time at the center. Timestamps that are generated by Tableau are in UTC [E.g. if you use filters such as Today, Tomorrow, etc.], hence we require this field to arrive at the center time. This is not needed for timestamps that are in the datasource directly; they are already in the center time zone. | int4 | |
Center, Zone, Organization | 2) Redemption Center: Center where the gift card or prepaid card is redeemed for other items. In some situations, it is possible that these two centers are different. The fields in this group detail the various attributes of the center and redemption center. | operating center | A flag that indicates if the center is operational. - True: Operational - False: Not operational | boolean | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. | center wid | A unique identifier, in Analytics, for the center. It does not exist in the core system. | bigint | |
Center, Zone, Organization | 1) Center: Center where the sale invoice for the gift card or prepaid card is generated | redemption organization name | The formal name of the organization where the gift card or prepaid card is redeemed for other items. | 'Massage Spa Express' is the formal name of an organization. | varchar(256) |
Center, Zone, Organization | 2) Redemption Center: Center where the gift card or prepaid card is redeemed for other items. In some situations, it is possible that these two centers are different. The fields in this group detail the various attributes of the center and redemption center. | redemption organization account name | The domain name of the organization in Zenoti. This is the organization where the gift card or prepaid card is redeemed for other items. | If you use this URL to log in to Zenoti 'https://massagespa.zenoti.com', then "massagespa" is your account name. The formal name and domain name may be different. | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. | redemption center name | The center where the gift card or prepaid card is redeemed for other items. | ||
Center, Zone, Organization | 1) Center: Center where the sale invoice for the gift card or prepaid card is generated | redemption zone name | The zone to which the redemption center belongs. | ||
Center, Zone, Organization | 2) Redemption Center: Center where the gift card or prepaid card is redeemed for other items. In some situations, it is possible that these two centers are different. The fields in this group detail the various attributes of the center and redemption center. | redemption center city | The city where the redemption center is located. | ||
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. | redemption center state name | The state where the redemption center is located. | varchar(128) | |
Center, Zone, Organization | 1) Center: Center where the sale invoice for the gift card or prepaid card is generated | redemption country name | The country where the redemption center is located. | ||
Center, Zone, Organization | 2) Redemption Center: Center where the gift card or prepaid card is redeemed for other items. In some situations, it is possible that these two centers are different. The fields in this group detail the various attributes of the center and redemption center. | redemption center zip | The ZIP code to which the redemption center belongs. | ||
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here, we refer to two types of centers. | redemption minutes to add | The number of minutes that need to be added to UTC time to get the current time at the redemption center. Timestamps that are generated by Tableau are in UTC [E.g. if you use filters such as Today, Tomorrow, etc.], hence we require this field to arrive at the redemption center time. This is not needed for timestamps that are in the datasource directly; they are already in the center time zone. | int4 | |
Center, Zone, Organization | 1) Center: Center where the sale invoice for the gift card or prepaid card is generated | redemption operating center | A flag that indicates if the redemption center is operational. - True: Operational - False: Not operational | boolean | |
Center, Zone, Organization | 2) Redemption Center: Center where the gift card or prepaid card is redeemed for other items. In some situations, it is possible that these two centers are different. The fields in this group detail the various attributes of the center and redemption center. | redemption center wid | A unique identifier, in Analytics, for the redemption center. It does not exist in the core system. | bigint | |
Guest | These fields detail the attributes of the two types of guests: 1) User: The guest who purchased the gift card or prepaid card 2) Redemption user: The guest who redeemed the gift card or prepaid card. | user id | A unique identifier for the guest who purchased the gift card or prepaid card. This is a non-blank value that gets populated for every guest. | varchar(36) | |
Guest | These fields detail the attributes of the two types of guests: 1) User: The guest who purchased the gift card or prepaid card 2) Redemption user: The guest who redeemed the gift card or prepaid card. | user first name | First name of the guest who purchased the gift card or prepaid card. | varchar(160) | |
Guest | These fields detail the attributes of the two types of guests: 1) User: The guest who purchased the gift card or prepaid card 2) Redemption user: The guest who redeemed the gift card or prepaid card. | user middle name | Middle name of the guest who purchased the gift card or prepaid card. | varchar(128) | |
Guest | These fields detail the attributes of the two types of guests: 1) User: The guest who purchased the gift card or prepaid card 2) Redemption user: The guest who redeemed the gift card or prepaid card. | user last name | Last name of the guest who purchased the gift card or prepaid card. | varchar(256) | |
Guest | These fields detail the attributes of the two types of guests: 1) User: The guest who purchased the gift card or prepaid card 2) Redemption user: The guest who redeemed the gift card or prepaid card. | user base center | Base center of the guest who purchased the gift card or prepaid card. This is where the guest's profile was created. | ||
Guest | These fields detail the attributes of the two types of guests: 1) User: The guest who purchased the gift card or prepaid card 2) Redemption user: The guest who redeemed the gift card or prepaid card. | redemption user id | A unique identifier for the guest who redeemed the gift card or prepaid card. This is a non-blank value that gets populated for every guest. | varchar(36) | |
Guest | These fields detail the attributes of the two types of guests: 1) User: The guest who purchased the gift card or prepaid card 2) Redemption user: The guest who redeemed the gift card or prepaid card. | redemption user first name | First name of the guest who redeemed the gift card or prepaid card. | varchar(160) | |
Guest | These fields detail the attributes of the two types of guests: 1) User: The guest who purchased the gift card or prepaid card 2) Redemption user: The guest who redeemed the gift card or prepaid card. | redemption user middle name | Middle name of the guest who redeemed the gift card or prepaid card. | varchar(128) | |
Guest | These fields detail the attributes of the two types of guests: 1) User: The guest who purchased the gift card or prepaid card 2) Redemption user: The guest who redeemed the gift card or prepaid card. | redemption user last name | Last name of the guest who redeemed the gift card or prepaid card. | varchar(256) | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | record type name | This field indicates whether the transaction was a sale, refund, or redemption. | Possible values: - Sale - Refund - Redemption | varchar(36) |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | item type code | If 'record type name' = 'Sale', this will be populated as 'Prepaid/ Gift Card'. If 'record type name' = 'Redemption', this is the item type of the item redeemed using the gift card/ prepaid card. If 'record type name' = 'Refund', this will be populated as 'Prepaid/ Gift Card Refund'. | Possible values: - Service - Product - Prepaid/ Gift Card Refund - Prepaid/ Gift Card - Package - Membership Freeze Fee - Membership - Day Package | varchar(128) |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | item name | The name of the gift card or prepaid card sold. | varchar(512) | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | is giftcard | A flag indicating if the card sold is a gift card or a prepaid card. -True: Gift card - False: Prepaid card | boolean | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | item code | The unique code of the gift card or prepaid card sold. Every gift card or prepaid card sold has a unique code at the center level. | varchar(256) | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | gift card code | Unique code of the gift card or prepaid card sold. | varchar(1024) | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | gift card id | Unique identifier for the gift card or prepaid card. This is a non-blank value that gets populated for every gift card or prepaid card. | varchar(36) | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | gift card user id | Unique identifier for the gift card or prepaid card and user combination. This is a non-blank value that gets populated for every gift card or prepaid card and user combination. | ||
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | item tags | The tags associated with the gift card or prepaid card sold. | Tags can be created for a gift card or prepaid card in the Organization view. These will be comma separated values if there are multiple tags. | varchar(65535) |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | item business unit name | The business unit associated with the gift card or prepaid card sold. | ||
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | sale price | Sale price of the gift card or prepaid card. | FLOAT | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | discount | Discount applied during sale of the gift card or prepaid card. | float | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | tax | Tax applied during sale of the gift card or prepaid card. | ||
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | giftcard value | The value assigned to the gift card or prepaid card at the time of purchase. | A gift card or prepaid can be priced at $100 and carry a value of $200. | FLOAT |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | factortoprice | The ratio between sale price and giftcard value. | A gift card or prepaid can be priced at $100 and carry a value of $200. In this case, the factortoprice is 0.5 | float |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | gift card validity days | Number of days for which the gift card or prepaid card is valid. | integer | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | expiry date | Expiry date of the gift card or prepaid card sold. | date | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | redemption item name | The name of the item redeemed using the gift card or prepaid card. | varchar(512) | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | redemption item code | The unique code of the item redeemed using the gift card or prepaid card. | varchar(256) | |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | redemption item tags | The tags associated with the redemption item. | Tags can be created for a service, product, package, or membership in the Organization view. These will be comma separated values if there are multiple tags. | varchar(65535) |
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | redemption item business unit name | The business unit associated with the redemption item. | ||
Item | These fields contains attributes of two types of items: 1) Item: The gift card or prepaid card sold 2) Redemption item: The items redeemed using the gift card or prepaid card (service, product, package, membership, gift card, prepaid card). | redeemed amount | The value redeemed from the gift card or prepaid card . | This redeemed amount is by Value. You can look at it by Price by multiplying it with 'factortoprice' | float |
Invoice | This group of fields detail out the invoice associated with the transaction. | invoice id | A unique identifier, within the organization, for the corresponding invoice. | varchar(36) | |
Invoice | This group of fields detail out the invoice associated with the transaction. | InvoiceNo | Invoice number that helps in identifying and tracking the invoice. | ||
Invoice | This group of fields detail out the invoice associated with the transaction. | invoice item id | The unique identifier for each line item within the invoice. | ||
Invoice | This group of fields detail out the invoice associated with the transaction. | sale date | The date on which the corresponding invoice was created. | date | |
Invoice | This group of fields detail out the invoice associated with the transaction. | redemption date | The date of redemption of items using the gift card or prepaid card. | ||
Invoice | This group of fields detail out the invoice associated with the transaction. | closed date | The date when the status of the invoice is marked as 'closed' |