Overview
MyVenue is a modern mobile-first initiative, it frees venues from traditional POS terminals by integrating food & beverage ordering and payment directly from a patron's phone to the production, delivery, and inventory management systems.
KORE’s MyVenue data integration strives to synchronize data from MyVenue into KORE’s data warehouse product (DWA) in an accurate, stable manner without affecting business operations. The focus of this integration is on all F&B POS data related to Sales and Customers. This is a mono-directional integration in which KORE ingests data from the source.
KORE ingests the F&B POS data from MyVenue via the AWS Message Queue into KORE’s DWA with near real-time data refresh frequency.
Setup
This section describes each step required to implement this integration from start to finish, including the owner of the task and a link to how they can complete it.
- TEAM: Notify KORE of your intention to enable this integration
-
TEAM: Provide Kore with the below mention information required for source connectivity (Vendor should be able to get these details):
- AWS Message Queue credentials -
1) SQS URL
2) Access key
3) Secret key
4) Region. - KORE DWA: Setup integration and confirm data is loaded correctly, provide the user guide and a walk-through of the integration to the Client Team.
ERD and data dictionary
(This information is provided on a best-effort basis without guarantees. For clarity, most column names are omitted from this ERD.)
Table Description
sales
This table stores information about high-level details of all the sales transactions during the match. Key Information includes id, orderId, posId, receipNumber, menuId, venueId, state, tableServedId, attendantId etc.
The data in these tables are updated incrementally near realtime. You can check the warehouse status report on Tableau to view the exact time the update happens each day.
items
This table stores information about item-level details pertaining a sales transaction during the match. Key Information includesid, isCombo, locationId, menuId, originalPrice, posId, productId, productName, productPrice, productSKU, quantity, posName etc.
The data in these tables are updated incrementally near realtime. You can check the warehouse status report on Tableau to view the exact time the update happens each day.
item_taxes
This table stores information about item-level tax details of all the items pertaining to every sales transaction during the match. Key Information includes itemId, isSingleQuantity, name, tax, taxDetailId, taxExAmount, taxId, taxIncAmount, type.
The data in these tables are updated incrementally near realtime. You can check the warehouse status report on Tableau to view the exact time the update happens each day.
payments
This table stores information about payment details of all the sales transactions during the match. Key Information includes amount, id, isOffline, locationId, posId, posReference, processorName, quantity, state, tenderId, tipAmount etc.
The data in these tables are updated incrementally near realtime. You can check the warehouse status report on Tableau to view the exact time the update happens each day.
Example queries
Report on revenue across various POS for a particular day.
- Start with the
salestable to get high level sales details. - Join with the
itemstable to get item level details. - Join with the
paymentstable to get payment details. - Edit the condition to pass the desired date.
SELECT
s.posid ,
i.posname ,
s.locationid ,
s.venueid ,
COUNT(DISTINCT s.id) Number_of_Orders ,
COUNT(i.id) Distinct_Items_Sold,
SUM(i.quantity) Total_Items_sold,
SUM(p.amount) Actual_Sale_Total
FROM my_venue.sales s
LEFT JOIN my_venue.items i
ON s.id = i.sales_id
LEFT JOIN my_venue.payments p
ON s.id = p.sales_id
WHERE CAST(s.completedat as DATE) = '2022-07-19' --input the required date --input the required POS id
GROUP BY s.posid, i.posname, s.locationid, s.venueid