Inventory data model
Various actions can lead to changes in product stock. Some actions such as purchase orders and retail returns result in stock inflow. Other actions such as retail sales and consumption result in stock outflow. This data source brings in all the actions that result in a change of stock for both retail and consumable products. It is organized in a ledger format, calling out both inflows and outflows. Cost of goods sold and Cost of inventory on hand are also calculated. Two methods of costing are supported:
First-In First-Out: Assumes that any sale or consumption of a product is from the oldest batch on hand.
Weighted Average: Maintains a pool of cost for all units of a particular product. Any purchase is added to the pool of cost, and the pool of cost is divided by all units on hand to arrive at an average cost per unit.
Important
The Inventory data source in Analytics is only visible if the setting to restrict edits of past financial and inventory transactions is enabled. After the setting is enabled, it takes a few days for the data to be synced into the analytics data source. For information on how to enable this setting, refer Restrict Edits to Financial and Inventory Data: Overview.
This data source is organized in a ledger format calling out both inflows and outflows. With every inflow and outflow, the available stock is calculated. Due to this model, edits to past financial and inventory transactions cause all the data post that date to get updated.
Example use cases
Stock on hand of a particular product over time
Stock changes of a particular product in a given interval of time
Cost of goods sold as per 2 methods of costing: First-In First-Out and Weighted Average methods
Cost of goods on hand as per 2 methods of costing: First-In First-Out and Weighted Average methods
Value lost or gained during audits
Granularity of data
Every action that impacts stock is recorded as a separate row. Every product is also recorded as a separate row. If the same product is both a retail and a consumable, rows are further split out to capture this granularity.
Example 1
The table below lays out how the data is populated when various actions are done on product ‘Kit-3B’. This product is both a retail and consumable product and the changes in stock are tracked separately for both. The first 2 rows establish that the available stock or Cumulative Stock of this product both as a retail and consumable were 0 as of 02/01/2020. Then, on 08/12/2020, a Purchase Order ‘123’ was delivered from a vendor for 10 units of the retail and consumable products at a Delivery Price Per Unit of 50. FIFO Stock shows the available stock of the product against the corresponding Purchase Order. Cumulative Stock is Purchase Order agnostic- it just tracks the available stock of the corresponding product. Both FIFO Stock and Cumulative Stock show 10 units on 08/12/2020 for the retail and consumable products. At this point, the Cumulative Cost of both the retail and consumable ‘Kit-3B’ stock in hand is 50*10= 500. This brings the Perpetual Avg to 500/10=50. Perpetual Avg is the weighted average cost per unit of the stock available in hand, this keeps changing as there is more stock inflow. Then at a later time on the same day, another Purchase Order ‘456’ was raised from a different vendor for 10 units of the same retail and consumable product at a Delivery Price Per Unit of 100. FIFO stock which shows the available stock against the corresponding Purchase Order now shows 10 for the retail and consumable product. However, Cumulative Stock which just shows the total available stock of the corresponding product is now updated to 10+10=20. The Cumulative Cost is now 500+(10*100)=1500. This brings the Perpetual Avg to 1500/20=75.
Then on 08/13/2020, there was a sale of 2 units of the retail product. Going by FIFO logic, this sale would be against Purchase Order ‘123’, hence this is populated in the PO Number field. The FIFO stock against this PO is 10-2=8 and since the Delivery Price Per Unit earlier was 50 for this PO, the FIFO Cost is 50*2=100. Cumulative Stock is Purchase Order agnostic and it records available stock as 20-2=18. COGS would be calculated against the latest Perpetual Avg for this product and hence comes to 75*2=150. The Cumulative Cost gets updated to 1500-150=1350. Similar logic follows for the consumption of 3 units of the consumable product.
Stock available in hand:
To get the stock available in hand for the retail product ‘Kit-3B’ as of 8/15, we would look for the latest action date against that product which happens to be 8/13/2020 13:00 . We would then look at Cumulative Stock corresponding to the row which is 18. This is the total stock available in hand for this product. Similarly, the total stock available in hand for the consumable product ‘Kit-3B’ is 17.
Cost of stock available in hand as per Weighted Average costing method:
To get the cost of the stock available in hand for the retail product ‘Kit-3B’ as of 8/15, we would look for the latest action date against that product which happens to be 8/13/2020 13:00 . We would then look at Cumulative Cost corresponding to the row which is 1350. This is the cost of the total stock available in hand for this product. Similarly, the cost of the total stock available in hand for the consumable product ‘Kit-3B’ is 1275. Note that this is the cost calculated as per Weighted Average costing method.
Example 2
The below example lays out how data gets impacted during audits. Initially, an audit is done and the Cumulative Stock of ‘Shampoo’ gets established as 0 as of 3/14/2020. Then on 3/15/2020, a PO ‘ABC’ was delivered for 1 unit at Delivery Price Per Unit of 50. This brings the FIFO Stock and Cumulative Stock to 1 and the Cumulative Cost to 50. Then another audit is done where the business inputs that there are actually 0 available units of ‘Shampoo’. To account for this, Action Quantity gets set to -1 and the FIFO Stock, Cumulative Stock, and Cumulative Cost all get updated to 0. This deduction of 1 quantity during the audit is associated with a PO as per FIFO logic and hence ‘ABC’ gets populated in the PO Number field. Then on 3/16/2020, there is another PO ‘XYZ’ delivered for 1 unit a Delivery Price Per Unit of 100. This brings the FIFO Stock and Cumulative Stock to 1 again and the Cumulative Cost to 100.
Group | Description | Field | Definition | Examples/Notes | Data Type |
|---|---|---|---|---|---|
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | action date | The date on which the action happened. This date is null for rows that are populated when a PO is created. | For Audits: action date is the date when the audit is completed. For Purchase Orders: action date is the date when the stock is delivered. For Transfer Orders: action date is the date when the stock is dispatched for the transfer-out center and it is the date when the stock is delivered for the transfer-in center. | timestamp without time zone |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | action quantity | The quantity corresponding to the action. | Example: If during an audit, the customer reconciles stock by adding 3 to a product, then the quantity would be 3. Similarly, if the customer reconciles stock by decreasing 4 from a product, then the quantity would be -4. | double precision |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | action type | The activity that resulted in the increase or decrease of product stock from a center. | Possible Actions are as below: PO: Purchase Order placed from an external vendor PO - Return: Return of items to an external vendor Transfer - In: Transfer refers to the movement of products between centers. Transfer-In is when products move in to a center. Transfer-Out: Transfer refers to the movement of products between centers. Transfer-Out is when products move out from a center. Transfer - Return: Transfer refers to the movement of products between centers. Transfer-Return is when products are returned to the origin center. Sale: Sale of a product to a customer Retail Refund: Return of a product from a customer to the business Consumption: Product used as part of a service Conversion: Products converted from retail to consumable or vice versa Checkout: Movement of products from store to floor Audit or Adjusted Audit: Reconciling of actual stock vs. expected stock Bundle: The bundled product formed from individual products as a result of a bundling action Bundle-Product: The individual products combined to form a bundle as a result of a bundling action Unbundle: The product from which individual products were separated out as a result of an unbundling action Unbundle-Product: The individual products separated out from a bundle as a result of an unbundling action | varchar(1600) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | center city | The city where the center is located. | varchar(128) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | center company name | The name under which the center is registered. | Businesses, especially in a franchisee model, may register each center as a different company - such details are tracked in the Centers > General tab. | varchar(200) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | center name | The center corresponding to the action that impacts product stock. This is the center where the 'action type' occurs. | varchar(128) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | center state name | The state where the center is located. | varchar(1024) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | center wid | A unique identifier for the center in analytics. This is generated by Analytics and does not exist in the core system. | bigint | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | center zip | Zipcode of the center's location. | varchar(32) | |
Cost | First-In First-Out: Assumes that any sale or consumption of a product is from the oldest batch on hand. | cogs | Current 'perpetual avg' of the product * 'Action Quantity' This is the cost of goods sold as per Weighted Average costing method. | In case of refund, this value is populated based on the value in the initial sale record. | float |
Cost | cogs with tax | Current 'perpetual avg with tax' of the product * 'Action Quantity' This is the cost of goods sold with tax as per Weighted Average costing method. | In case of refund, this value is populated based on the value in the initial sale record. | float | |
Product and Action | consumable alert inventory | Inventory level of consumable at which status is set to "alert" in the product | This is set in the core product. | float | |
Product and Action | consumable desired inventory | Inventory level of consumable at which status is set to "desired" in the product | This is set in the core product. | float | |
Product and Action | consumable order inventory | level at which purchase order should be raised for consummable product | This is set in the core product. | float | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | consummable | This flag indicates whether the product is consumed as part of a service. - True indicates that the product is a consumable. - False indicates that the product is not a consumable. | boolean | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | country name | The country where the center is located. | varchar(1024) | |
Cost | cumulative cost | The total cost of the stock available in hand as per Weighted Average costing method. | float | ||
Cost | Weighted Average: Maintains a pool of cost for all units of a particular product. Any purchase is added to the pool of cost, and the pool of cost is divided by all units on hand to arrive at an average cost per unit. | cumulative cost with tax | The total cost with tax of the stock available in hand as per Weighted Average costing method. | float | |
Cost | Two methods of costing are supported: First-In First-Out and Weighted Average. | cumulative stock | The total stock available in hand (agnostic of Purchase Order) corresponding to the product and center associated with that row. | To get the current Cumulative Stock for a product, look at the latest entry against that product and center. | float |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | deliver price pu with tax | The final price at which the corresponding product is delivered per unit including tax. | float | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | delivery price per unit | The final price at which the corresponding product is delivered per unit. | float | |
Product and Action | desired inventory | desired level of retail product quantity in inventory | This is set in the core product | float | |
Cost | fifo cost | 'Delivery price per unit' of the associated PO * 'Action quantity' This is the cost of goods sold as per FIFO costing method. The 'poid' field indicates the Purchase Order from where the delivery price is taken. | float | ||
Cost | First-In First-Out: Assumes that any sale or consumption of a product is from the oldest batch on hand. | fifo cost with tax | 'Deliver price pu with tax' of the associated PO * 'Action quantity' This is the cost of goods sold with tax as per FIFO costing method. The 'poid' field indicates the Purchase Order from where the delivery price is taken. | float | |
Cost | Two methods of costing are supported: First-In First-Out and Weighted Average. First-In First-Out: Assumes that any sale or consumption of a product is from the oldest batch on hand. Weighted Average: Maintains a pool of cost for all units of a particular product. Any purchase is added to the pool of cost, and the pool of cost is divided by all units on hand to arrive at an average cost per unit. | fifo stock | The stock available in hand corresponding to the Purchase Order and product associated with that row. This is as per FIFO method. | To get the current FIFO stock for a product against a purchase order, look at the latest entry against that purchase order and product. | float |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | in transit date | The date on which the product is considered to be in transit. | This can be different for different products in a Purchase Order. | timestamp without time zone |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | inventory action id | An identifier that provides more details about the action. This gets populated differently depending on the action type. This field can be used to join this data source with the Sales data source. | The list below shows how this field gets populated for the various action types. - For purchase order, transfer order, and their returns: the POID gets populated here. - For sale: the Invoice Item ID of the sale transaction gets populated here. - For retail refund: the Invoice Item ID of the associated sale transaction gets populated in case sale and refund happen in the same center. The Invoice Item ID of the refund transaction gets populated in case sale and refund happen in different centers. - For consumption: the Appointment ID of the service sale gets populated here. - For other action types: an identifier associated with the action gets populated here. | varchar(36) |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | is product inactive | This flag indicates whether the product is inactive. A product is considered inactive if it has been removed from the system. - True indicates that the product is inactive. - False indicates that the product is active. | boolean | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | is transfer center operating | A flag that indicates if the transfer center is operational. - True: Operational - False: Not operational | boolean | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | item delivery date | The date on which the product is delivered. | This can be different for different products in a Purchase Order. | timestamp without time zone |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | item delivery other charges | Other charges recorded in the PO for the corresponding product per unit. | This field is only populated for action types corresponding to Purchase Orders and Transfer Orders. | float |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | item discount | The discount for the corresponding product per unit. | This field is only populated for action types corresponding to Purchase Orders and Transfer Orders. | float |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | item shipping cost | The shipping charges for the corresponding product per unit. | This field is only populated for action types corresponding to Purchase Orders and Transfer Orders. | float |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | item tax | The tax for the corresponding product per unit. | This field is only populated for action types corresponding to Purchase Orders and Transfer Orders. | float |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | operating center | A flag that indicates if the center is operational. - True: Operational - False: Not operational | boolean | |
Product and Action | order inventory | inventory level at which purchase order should be raised for retail product | This is set in the core product | float | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | order quantity | The quantity ordered through the corresponding Purchase Order or Transfer Order. | This field is only populated for action types corresponding to Purchase Orders and Transfer Orders. | integer |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | order raised date | The date on which the order is raised. | This field name has a space at the beginning. The field name is ' order raised date'. Consider this while creating your queries. | timestamp without time zone |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | order type | This indicates if the order is internal or external. - External: Orders raised from external vendors. - Internal: Orders raised between centers of same organization. | varchar(800) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | organization account name | The domain name of the organization in Zenoti. | 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. | varchar(256) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | organization name | The formal name of the organization. | 'Massage Spa Express' is the formal name of an organization. | varchar(512) |
Cost | Two methods of costing are supported: First-In First-Out and Weighted Average. | perpetual avg | 'Cumulative Cost'/'Cumulative Stock' This is the per unit cost of the total stock available in hand as per Weighted Average costing method. | float | |
Cost | perpetual avg with tax | 'Cumulative Cost with Tax'/'Cumulative Stock' This is the per unit cost with tax of the total stock available in hand as per Weighted Average costing method. | float | ||
Product and Action | po invoice number | Invoice number of Purchase Order raised | float | ||
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | po number | The Purchase Order number corresponding to the action. This maps to the PO number in Core. In case of stock outflow, this is the PO that the outflow is associated with as per FIFO logic. | bigint | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | poid | A unique identifier for the Purchase Order. This is a non-blank value that gets populated for every purchase order. In case of stock outflow, this is the PO that the outflow is associated with as per FIFO logic. | varchar(36) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | product business unit | The business unit to which the product belongs. | varchar(800) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | product category | The category of the product. This is setup as sub-category in core. | varchar(65535) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | product color name | The color name associated with the product. | varchar(2048) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | product has variant | This flag indicates whether the product has a variant. - True indicates that the product has a variant. - False indicates that the product does not have a variant. | boolean | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | product name | The name of the product. | varchar(1024) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | product parent category | The parent category of the product. This is setup as category in core. | varchar(65535) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | product size name | The size name associated with the product. | varchar(2048) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | product tags | The tags associated with the product. | Tags can be created for a product in the Organization view. These will be comma separated values if there are multiple tags. | varchar(65535) |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | product type | This indicates if the order quantity is that of a retail or consumable product. | varchar(1200) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | product wid | Unique identifier of the product. This is generated in Analytics, it does not exist in the core system. | bigint | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | retail | This flag indicates whether the product is sold as a retail product. - True indicates that the product is sold as a retail product. - False indicates that the product is not sold as a retail product. | boolean | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | transfer center city | The city in which the transfer center is located. | varchar(128) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | transfer center company name | The name under which the transfer center is registered. | Businesses, especially in a franchisee model, may register each center as a different company - such details are tracked in the Centers > General tab. | varchar(200) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | transfer center country name | The country in which the transfer center is located. | varchar(1024) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | transfer center name | The center corresponding to a product transfer. This is populated for action types related to transfers. | For every 'Transfer- In' action type there is a corresponding 'Transfer- Out' action type. If products are transferred from Center A to Center B, then: - For 'Transfer- In' action type: -- Center Name: Center B --Transfer Center Name: Center A - For 'Transfer- Out' action type: -- Center Name: Center A -- Transfer Center Name: Center B | varchar(128) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | transfer center organization account name | The domain name of the organization of which the transfer center is a part. | 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. | varchar(256) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | transfer center organization name | The formal name of the organization of which the transfer center is a part. | 'Massage Spa Express' is the formal name of an organization. | varchar(512) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | transfer center state name | The state in which the transfer center is located. | varchar(1024) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | transfer center wid | A unique identifier for the transfer center in analytics. This is generated by Analytics and does not exist in the core system. | bigint | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | transfer center zip | The postal code in which the transfer center is located. | varchar(32) | |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | transfer center zone name | The zone to which the transfer center belongs. | varchar(800) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | vendor name | The name of the vendor corresponding to the Purchase Order. | varchar(1024) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | volume per item | The volume per unit of the product. For example, if the sold product is a 150 ml shampoo, this field would display '150'. The unit of measure of the volume is recorded in the 'volume type name' field. | double precision | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | volume type name | Unit of measure of the volume populated in the 'volume per item' field. For example, if the product sold is a 150 ml shampoo, this field would display 'ml'. | Example: ml, grams | varchar(1200) |
Center, Zone, Organization | An organization can be divided into zones, which can be further divided into centers. Here we refer to the center where the action that impacts product stock occurred. | zone name | The zone to which the center belongs. | varchar(800) | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | action org po number | PO number for various activites like buy, transfer and return | bigint | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | alert inventory | alert level of retail product quantity in inventory | double precision | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | fact inventory wid | unique identifier for the row in the table | bigint | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | org po number | The Purchase Order number corresponding to the action, in the organization where the order originated | bigint | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | audit reconciliation date | Date on which an audit is reconciled | timestamp | |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | po status | Status of Purchase order | Status: Created, Raised, In Transit, Delivered | varcahr(100) |
Product and Action | These fields detail the attributes of the product whose stock has changed and the details of the action that caused this change. | po creation date | Date on which Purchase Order is created | timestamp |