Vendi+ Technical Brief.docx

A logo with black and blue letters Description automatically generated Vendi+ Technical Brief Cam Millen 26^th^ August 2024 [1 Overview 2](#overview) [1.1 Project Overview 2](#project-overview) [2 System & Deployment Architecture 3](#system-deployment-architecture) [2.1 Tech Stack & System Ov...

A logo with black and blue letters Description automatically generated Vendi+ Technical Brief Cam Millen 26^th^ August 2024 [1 Overview 2](#overview) [1.1 Project Overview 2](#project-overview) [2 System & Deployment Architecture 3](#system-deployment-architecture) [2.1 Tech Stack & System Overview 3](#tech-stack-system-overview) [2.2 Database Architecture & Schema 5](#database-architecture-schema) [2.3 Najax Integration 7](#najax-integration) [2.3.1 The Integration 7](#the-integration) [2.3.2 Sample SQS Message 8](#sample-sqs-message) [2.3.3 Mapping to Vendi Schema 11](#mapping-to-vendi-schema) [2.3 Datajam Integration 12](#datajam-integration) [2.3.1 Integration 12](#integration) [2.3.2 API Details 13](#api-details) [2.3.3 Datajam Portal Details 13](#datajam-portal-details) [2.3.3 Datajam Sample Request Response 13](#datajam-sample-request-response) [2.3.4 Mapping to Vendi Schema 14](#mapping-to-vendi-schema-1) [3 User Interface & User Experience 15](#user-interface-user-experience) [3.1 Key Pages -- User Facing 15](#key-pages-user-facing) [3.2 Key Pages -- Admin Facing 19](#key-pages-admin-facing) [3.3 Design Inspiration 21](#design-inspiration) [4 Technical Roadmap 22](#technical-roadmap) [3.1 Phase 1: Planning, Design and Initial Setup (1 week) 22](#phase-1-planning-design-and-initial-setup-1-week) [3.2 Phase 2: Development of Backend Data Pipeline (2 weeks) 22](#phase-2-development-of-backend-data-pipeline-2-weeks) [3.3 Phase 3: Full Stack Development of Final Product (4 weeks) 22](#phase-3-full-stack-development-of-final-product-4-weeks) [3.5 Rough Time Estimation 22](#rough-time-estimation) Overview ======== ### 1.1 Project Overview Vendi is a vending machine company that uses the latest vending technology to sell products and wants to leverage the data generated by these machines to provide valuable insights to our clients. This data can help optimize machine performance, tailor product offerings, and provide detailed sales analytics. Currently, we aim to develop a system called **Vendi+** that collects and integrates data from two key sources: Nayax and DataJam. - **Nayax** data is received via an Amazon SQS server, which is already set up (login details for the AWS account will be provided). - **DataJam** data is accessed through their customer portal API. The objective is to capture detailed information on each sale and other relevant metrics from our vending machines from both Nayax and DataJam. This data needs to be converted into a unified format that matches our pre-defined schema and then stored in a centralized database. The **Vendi+** system will enable us to: - Aggregate data from both sources into a single, cohesive dataset. - Ensure that data is accurately formatted, stored, and readily accessible. - Provide a user-friendly interface for displaying and manipulating this data, offering real-time insights and analytics to our clients. By developing this system, we aim to enhance our data analytics capabilities, improve client reporting, and support better decision-making based on accurate, real-time data. You will be responsible for integrating both data sources, formatting the data, storing it in our database, and ensuring it is ready for presentation and analysis on our user interface. System & Deployment Architecture ================================ This section outlines the system goals, including backend, frontend, and database objectives for Vendi+. ### 2.1 Tech Stack & System Overview  The Vendi+ system is designed to effectively collect, process, and present data from multiple sources to provide valuable insights to clients. The architecture of Vendi+ is structured to be scalable, secure, and efficient, utilizing modern technologies across the frontend, backend, database, and deployment environments. [Frontend:] - **Frameworks**: The frontend will be built using **Next.js** and **React**. - **Next.js** provides server-side rendering (SSR) and static site generation (SSG) to enhance performance and SEO. - **React** allows the creation of a dynamic, component-based user interface (UI) for a responsive user experience. - The frontend will not handle authentication or database management directly. All sensitive operations will be managed by the backend. [Backend:] - **Framework**: The backend will be developed using **Django**. - Django offers robust support for API development, database management, and authentication. - The backend will manage all API interactions, connect to third-party data sources (Nayax and DataJam), interface with the internal database, and send processed data to the frontend. - Django will handle authentication and authorization to manage access to the frontend. [Database:] - **PostgreSQL** will be used as the relational database management system (RDBMS). - PostgreSQL supports complex queries and advanced data types, which is suitable for managing the structured data generated by Vendi+. - It integrates seamlessly with Django, utilizing Django's ORM for efficient data manipulation and storage. - PostgreSQL ensures data integrity and supports ACID transactions for reliable sales and customer data management. [API Integrations:] - **Nayax Data Integration**: - Integrate Nayax data using **AWS Lambda** functions triggered by **Amazon SQS** messages. This setup allows automatic processing of incoming data in real time as it arrives in the SQS queue. - AWS Lambda for event-driven processing reduces latency and simplifies infrastructure management by eliminating the need for manual background jobs. - **DataJam Data Integration**: - Use the DataJam Customer Portal API to periodically fetch sales and machine data. The Django backend will make API requests to DataJam, retrieve the relevant data, and convert it into the format required by our PostgreSQL database. - All data fetched from DataJam will be validated, transformed, and normalized to match our database schema before storage. [Deployment:] - **Deployment Platform**: - **Primary Deployment**: Deploy the application on **Heroku** for initial deployment due to its simplicity, cost-effectiveness, and ease of scaling. Heroku supports both Django and PostgreSQL natively. - **Autoscaling**: Use Heroku's autoscaling add-ons, such as **HireFire** or **123 AutoScale**, to dynamically adjust the number of dynos based on traffic and workload demands. - **Alternative Deployment**: Consider **AWS** (Amazon Web Services) for deployment as Vendi+ grows. AWS offers more robust scaling, security, and management features, beneficial for increased traffic and data processing needs. - Start with Heroku for ease of use and quick setup, and consider migrating to AWS for more customization and control as the application scales. [Extras:] 1. **Continuous Integration/Continuous Deployment (CI/CD) Pipeline**: - Establish a CI/CD pipeline using tools like **GitHub Actions**, **Jenkins**, or **GitLab CI** to automate testing, building, and deployment processes. 2. **Enhanced Monitoring and Logging**: - Integrate monitoring tool **Sentry** for error tracking. ### 2.2 Database Architecture & Schema [2.2.1 Summary] The data schema for Vendi+ includes several key entities that represent different aspects of the vending machine business. The **BrandPartners** entity stores information about each brand partner associated with the vending machines, while the **Products** entity captures details about the products sold. Each product belongs to a specific category, which is managed by the **ProductCategories** entity. The **Machines** entity records details of each vending machine, including its geographical location, linked to the **Geographies** entity, which provides location details. The **MachineBrandPartners** join table captures the many-to-many relationship between machines and brand partners, allowing multiple machines to be associated with multiple brand partners. The **Sales** entity records each transaction, linking products to the specific machine where the sale occurred and includes additional details such as sale date and time. The **Impressions** entity tracks advertising metrics for each machine, such as exposure time, total impressions, and advert playouts. All these entities are interrelated to provide a comprehensive view of product sales, machine locations, brand partnerships, and advertising performance, enabling a detailed analysis of vending machine operations and business intelligence. [2.2.2 Diagram] A screenshot of a computer screen Description automatically generated [2.2.3 Schema] \-- Products Table CREATE TABLE Products ( product\_id INT PRIMARY KEY, product\_name VARCHAR(255) NOT NULL, category\_id INT, price DECIMAL(10, 2), created\_at TIMESTAMP, updated\_at TIMESTAMP, deleted\_at TIMESTAMP, FOREIGN KEY (category\_id) REFERENCES ProductCategories(category\_id) ); \-- Machines Table CREATE TABLE Machines ( machine\_id INT PRIMARY KEY, geography\_id INT, created\_at TIMESTAMP, updated\_at TIMESTAMP, deleted\_at TIMESTAMP, FOREIGN KEY (geography\_id) REFERENCES Geographies(geography\_id) ); \-- MachineBrandPartners Table (Join Table) CREATE TABLE MachineBrandPartners ( machine\_id INT, brand\_partner\_id INT, PRIMARY KEY (machine\_id, brand\_partner\_id), FOREIGN KEY (machine\_id) REFERENCES Machines(machine\_id), FOREIGN KEY (brand\_partner\_id) REFERENCES BrandPartners(brand\_partner\_id) ); \-- Brand Partners Table CREATE TABLE BrandPartners ( brand\_partner\_id INT PRIMARY KEY, brand\_partner\_name VARCHAR(255) NOT NULL, created\_at TIMESTAMP, updated\_at TIMESTAMP, deleted\_at TIMESTAMP ); \-- Sales Table CREATE TABLE Sales ( sale\_id INT PRIMARY KEY, product\_id INT, machine\_id INT, sale\_date DATE, sale\_time TIME, quantity INT, source\_system VARCHAR(50), source\_system\_id INT, created\_at TIMESTAMP, updated\_at TIMESTAMP, deleted\_at TIMESTAMP, FOREIGN KEY (product\_id) REFERENCES Products(product\_id), FOREIGN KEY (machine\_id) REFERENCES Machines(machine\_id) ); \-- Impressions Table CREATE TABLE Impressions ( impression\_id INT PRIMARY KEY, machine\_id INT, date DATE, total\_impressions INT, temperature INT, data rainfall INT, source\_system VARCHAR(50), source\_system\_id INT, created\_at TIMESTAMP, updated\_at TIMESTAMP, deleted\_at TIMESTAMP, FOREIGN KEY (machine\_id) REFERENCES Machines(machine\_id) ); \-- Geographies Table CREATE TABLE Geographies ( geography\_id INT PRIMARY KEY, geography\_name VARCHAR(255) NOT NULL, geography\_postcode VARCHAR(255) NOT NULL, created\_at TIMESTAMP, updated\_at TIMESTAMP, deleted\_at TIMESTAMP ); \-- Product Categories Table CREATE TABLE ProductCategories ( category\_id INT PRIMARY KEY, category\_name VARCHAR(255) NOT NULL ); \-- Error Logs Table CREATE TABLE ErrorLogs ( error\_id INT PRIMARY KEY, source\_system VARCHAR(50), error\_message TEXT, timestamp TIMESTAMP, resolved\_status BOOLEAN DEFAULT FALSE ); ### 2.3 Najax Integration ### 2.3.1 The Integration Najax provides transaction data. Long story short we need to get the data from our AWS SQS server using the Django backend, then convert the data to fit into our schema, then store it in our DB. AWS root user login details: backend\@venditech.com Dizzlestrizzle1 1. **Set Up AWS and Django Environment:** - Configure AWS credentials and ensure the necessary permissions are in place to access the SQS queue. Set up these credentials in a secure manner within the Django environment. 2. **Install Required Libraries:** - Install boto3 for AWS SQS interactions and Celery for handling background tasks in Django. 3. **Configure Celery for Background Task Management:** - Set up Celery in the Django project to handle background tasks. Configure Celery to work with a message broker such as Redis or RabbitMQ. 4. **Develop a Celery Task to Poll SQS Messages:** - Create a Celery task within Django that periodically polls the SQS queue for new messages. This task should be scheduled to run at regular intervals to check for incoming data. 5. **Process Messages from SQS:** - In the Celery task, parse each message retrieved from the SQS queue to extract relevant data points, such as transaction IDs, machine IDs, product details, and timestamps. 6. **Map Extracted Data to Django Models:** - Map the parsed data from the SQS messages to the appropriate Django models that represent the database schema. Ensure all data points are correctly aligned with the schema fields. 7. **Insert or Update Data in the PostgreSQL Database:** - Use Django ORM to insert new records or update existing records in the PostgreSQL database. This includes adding new sales records, updating machine or product information, and ensuring data integrity. 8. **Handle Errors and Edge Cases** - Implement error handling to manage any issues that arise during message processing, such as malformed messages or missing data. Log errors for future debugging and ensure that erroneous messages do not cause the system to fail. 9. **Delete Processed Messages from SQS:** - After successfully processing each message and storing the data in the database, delete the message from the SQS queue to prevent reprocessing. ### 2.3.2 Sample SQS Message { \"TransactionId\": 6862842766, \"RemoteStartTransactionId\": \"\", \"PaymentMethodId\": 1, \"SiteId\": 6, \"MachineTime\": \"2024-08-25T18:03:22.537\", \"Void\": false, \"MachineId\": 292004948, \"Data\": { \"Machine Name\": \"Vendi - Baracoa US - Leeds - 018\", \"Operator Identifier\": \"199936\", \"Machine AuTime\": \"2024-08-25T18:03:22.537\", \"Machine SeTime\": \"2024-08-25T18:03:38.760\", \"Currency\": \"GBP\", \"Card String\": \"5200 xxxx xxxx 6398\", \"Brand\": \"MASTERCARD\", \"CLI\": null, \"SeValue\": 6.9900, \"Extra Charge\": 0.0000, \"Payment Service(Mobile using Credit Card)\": \"Credit Card using Credit Card\", \"Payment Method ID (1)\": 1, \"Recognition Method ID (3)\": 1, \"Catalog Number\": \"\", \"Device Number\": \"0434334422199936\", \"Actor Hierarchy\": \"NAYAX UK / EEM Farming Solutions / VENDI VAPES LTD / Leeds / \", \"Payment Method Description\": \"Credit Card\", \"Recognition Description\": \"Credit Card\", \"Card First 4 Digits\": \"5200\", \"Card Last 4 Digits\": \"6398\", \"Card Type\": \"Near field communication\", \"Machine Group\": \"Install\", \"Transaction ID\": 6862842766, \"Site ID\": 6, \"Authorization Time\": \"2024-08-25T17:03:23.510\", \"Authorization Value\": 6.9900, \"PayServTransid\": \"2110000000241493839\", \"sePayServTransId\": \"2110000000241494383\", \"Settlement Time\": \"2024-08-25T17:03:38.760\", \"Cancel Type\": null, \"Is Revalue Transaction\": false, \"Preselection Status\": 0, \"Is Phone Registration\": false, \"Is Multivend\": false, \"Settlment Failed\": null, \"Sale ID\": 0, \"Sale Value\": 0.0000, \"Updated DT\": \"2024-08-25T17:03:38.760\", \"Constant Preauthorization Value\": null, \"Is Partial Confirmation\": null, \"Authorization Code\": \"R95646\", \"Authorization Date and Time\": null, \"Authorization RRN\": \"1870CB0CBE51E0E37D594C70EA4198EE\", \"Event Code\": 0, \"Guest Name\": null, \"Token\": null, \"Zip Code\": null, \"Billing Provider ID\": 44, \"AVS Only\": null, \"BOD Transaction Key\": null, \"Disable Debit Cards\": null, \"Force Transactions Terminal\": null, \"Use Phone Transaction\": false, \"License ID\": 0, \"Merchant ID\": \"\", \"Billing Site ID\": 0, \"Terminal ID\": 22199936, \"User Password\": null, \"With ZIP\": null, \"Use Phone Contactless\": false, \"Use Phone Contact\": false, \"Debit Card Prefix\": null, \"Actor Description\": \"Leeds\", \"Institute Description\": null, \"Location Code\": null, \"Location Description\": null, \"Operator Institute Code\": null, \"Area Description\": \"Leeds\", \"OP Button Code\": null, \"Barcode\": \"\", \"Cost Price\": 2.4000, \"Card Price\": 9.9900, \"Prepaid Price\": null, \"Machine Price\": 6.9900, \"Cash Price\": null, \"Default Price\": null, \"Actor Code\": 123, \"Display Card Number\": null, \"Card Holder Name\": null, \"User Identity\": null, \"Billing Provider Name\": \"SafeCharge\", \"Is Offline Transaction\": null, \"Is EMV Transaction\": true, \"Machine AuTime (Date only)\": \"2024-08-25\", \"Machine AuTime (Time only)\": \"18:03:22\", \"Machine SeTime (Date only)\": \"2024-08-25\", \"Machine SeTime (Time only)\": \"18:03:38\", \"Settlement Time (Date only)\": \"2024-08-25\", \"Settlement Time (Time only)\": \"17:03:38\", \"Updated DT (Date only)\": \"2024-08-25\", \"Updated DT (Time only)\": \"17:03:38\", \"Raw ENI Loyalty Num\": \"\", \"Customer Type\": 1, \"Actor ID\": 2001855352, \"Client Id\": \"\", \"Contract Name\": null, \"Payout Day\": null, \"Contract Id\": null, \"Airport Id\": null, \"Member Type\": null, \"Is Refund Card\": false, \"Contract Number\": null, \"Airport Code\": null, \"Payed Value\": 6.9900, \"Consumer ID\": 2327941512250601403, \"Discount Card ID\": null, \"Discount Card Number\": null, \"Discount Card User Identity\": null, \"Discount Card Physical Type ID\": null, \"Discount Card Activation Date\": null, \"Discount Card Expiration Date\": null, \"Is Money\": null, \"Prepaid Card Current Regular Credit\": null, \"Prepaid Credit Amount Charge\": null, \"Prepaid Credit Transaction Charge\": null, \"Prepaid Card Current Revalue Credit\": null, \"Prepaid Revalue Credit Amount Charge\": null, \"Is Revalue Reward Transaction\": null, \"Prepaid Card Monthly Amount Credit Usage \": null, \"Prepaid Card Monthly Transactions Credit Usage \": null, \"Loyalty Card Number\": \"\", \"Campaign ID\": 20016, \"Campaign Type\": 1, \"Card ID\": null, \"Machine Serial Number\": \"\", \"Machine ID\": 292004948, \"Transaction Status ID\": 12, \"Transaction Status Name\": \"Settled\", \"Device Transaction ID (DTID)\": 2868143860, \"Vendor Transaction UID (VUID)\": null, \"Card BIN\": \"52002090\", \"Batch Ref Number\": null, \"operator transaction id\": null, \"Retail Store Id\": null, \"Retail Pos Id\": null, \"Operator Data 1\": null, \"Credit Card Type\": \"CREDIT\", \"original authorization time\": null, \"Is Deferred Transaction\": null, \"Products\": \[ { \"Product(Product Name(MDB Code,PA Code=Price))\": \"ELUX Viva - SWB(12,12)\", \"Product Group\": \"Vapes\", \"Product Code in Map\": 12, \"Product PA Code\": \"12\", \"Product Volume Type\": null, \"Product Name\": \"ELUX Viva - SWB\", \"Product VAT Id\": null, \"Product Tax Value\": null, \"Product Tax Code\": null, \"Product Vat Amount\": null, \"Product Net Price\": null, \"Product External Prepaid Price\": null, \"Product Group Code\": null, \"Product Group Sub Code\": null, \"Product Retail Price\": null, \"Product Discount Percentage\": 0.00, \"Product Discount Amount\": 0.0000, \"Product Bruto\": 6.9900, \"Product Catalog Number\": \"\", \"Product Unit Of Measure\": 0, \"Product Quantity\": 0, \"Product ID\": 1365523285569 } \] } } ### 2.3.3 Mapping to Vendi Schema 1. **Sales Table:** - sale\_id: Derived from TransactionId in the SQS message. - product\_id: Derived from the nested Products array in the SQS message; specifically, the Product ID field. - machine\_id: Mapped from MachineId in the SQS message. - sale\_date: Extracted from MachineTime or Machine AuTime (date portion) in the SQS message. - sale\_time: Extracted from MachineTime or Machine AuTime (time portion) in the SQS message. - quantity: Derived from the nested Products array in the SQS message; specifically, Product Quantity. Note that this might need a logical check as the sample shows 0, which could be misleading. - source\_system: Hardcoded value \"Nayax\" since the SQS message originates from Nayax. - source\_system\_id: Mapped from TransactionId in the SQS message. - created\_at, updated\_at, deleted\_at: Derived from the current timestamp for created\_at and updated\_at. deleted\_at remains NULL unless the record is deleted. 2. **Products Table:** - product\_id: Derived from the nested Products array; specifically, the Product ID field in the SQS message. - product\_name: Mapped from Product Name in the nested Products array in the SQS message. - category\_id: Should be derived from a lookup or mapping process using Product Group from the nested Products array. If the category exists in ProductCategories, its category\_id will be used. If not, a new entry should be created in ProductCategories. - price: Derived from Product Bruto in the nested Products array in the SQS message. - created\_at, updated\_at, deleted\_at: Derived from the current timestamp for created\_at and updated\_at. deleted\_at remains NULL unless the record is deleted. 3. **Machines Table:** - machine\_id: Mapped from MachineId in the SQS message. - geography\_id: Mapped from a lookup or mapping process using Area Description (e.g., \"Leeds\") or Actor Description from the SQS message. This would require checking the existing entries in the Geographies table or creating a new entry if the geography does not exist. - created\_at, updated\_at, deleted\_at: Derived from the current timestamp for created\_at and updated\_at. deleted\_at remains NULL unless the record is deleted. 4. **ProductCategories Table:** - category\_id: Auto-generated if a new entry is needed. - category\_name: Mapped from Product Group in the nested Products array in the SQS message. 5. **Geographies Table:** - geography\_id: Auto-generated if a new entry is needed. - geography\_name: Derived from Area Description or Actor Description in the SQS message. [Example Mapping Process:] 1. When a message is received, parse the JSON to extract the necessary fields. 2. For each sale in the message: - Check if the machine\_id exists in the Machines table; if not, insert a new record. - Check if the product\_id exists in the Products table; if not, insert a new product. - Check if the category\_id for the product exists in the ProductCategories table; if not, create a new category entry. - Check if the geography\_id exists in the Geographies table; if not, insert a new geography. - Insert a new record into the Sales table with all relevant data. By following these steps, you ensure that all data from the SQS message is correctly captured and stored in the Vendi+ database, maintaining data integrity and consistency. ### 2.3 Datajam Integration ### 2.3.1 Integration ### Datajam provides our impression data. Long story short we need to gather all of the device IDs for Vendi machines from datajam, and then at the end of each day gather the new impression data for each machine and update our machine. The integration process consists of the following steps: 1. **Initial Setup and Device Identification**: - Retrieve the list of all Vendi machine device IDs registered in the Datajam portal. This will ensure that the system is aware of every active device that needs impression data collection. - This step is crucial as new devices may be added over time, and the integration process must dynamically adapt to these changes. 2. **Daily Data Retrieval**: - At the end of each day, a script or automated job will make a request to the Datajam API to retrieve impression data for all registered devices. The request will include the necessary parameters such as the project name, device ID, date range (set to the previous day), and data type (Impression). - For optimal performance, batch processing should be considered to handle multiple device data retrieval requests in parallel, reducing the total time required for data synchronization. 3. **Data Transformation and Mapping**: - The retrieved data from Datajam will then be transformed to match the Vendi schema. This involves mapping fields from the Datajam response to the appropriate fields in the Vendi database. Each piece of data, such as Device ID, Total Impressions, Temperature, etc., needs to be correctly mapped to ensure the data\'s integrity. - Any new devices or geographies not currently in our system will be added as new entries, maintaining a seamless integration of all data points. 4. **Database Update**: - The transformed data is then used to update the respective tables in the Vendi database. For each device\'s daily data, an entry is created or updated in the Impressions table. Similarly, any new or updated device information will be reflected in the Machines and Geographies tables. - Ensure transactional integrity during updates to handle any failures gracefully. Use error logging mechanisms to capture and report any inconsistencies or errors encountered during the process. 5. **Error Handling and Logging**: - The integration process will include robust error handling and logging mechanisms. Any errors in data retrieval, transformation, or database update processes will be logged to the Error Logs table. This ensures that all errors are documented for further investigation and resolution. - Implementing retries for temporary failures and alerts for persistent issues will ensure smooth operations with minimal downtime. 6. **Data Validation and Reconciliation**: - After the data update, a validation step will compare the updated records against the source data from Datajam to ensure that the data integration is accurate. Any discrepancies will trigger an alert and create an error log entry for immediate review. - This validation helps maintain data consistency and prevents data corruption due to integration errors. ### 2.3.2 API Details ### API url: Auth (Basic Auth): username: password: VendiT3ch.2024 Request body: { \"project\_name\": \"Vendi Tech\", \"device\_number\": \"JB001690\", \"start\_date\": \"2024-04-01\", \"end\_date\": \"2024-04-09\", \"type\_data\": \"Impression\" } ### ### 2.3.3 Datajam Portal Details ### URL: username: password: VendiT3ch.2024 ### ### 2.3.3 Datajam Sample Request Response [Curl (run from your terminal to test)] curl -X GET \"https://datajamportal.com/CustomerAPI/GetData/\" \\ -H \"Content-Type: application/json\" \\ -u \"cam\@venditech.com:VendiT3ch.2024\" \\ -d \'{ \"project\_name\": \"Vendi Tech\", \"device\_number\": \"JB001690\", \"start\_date\": \"2024-04-01\", \"end\_date\": \"2024-04-09\", \"type\_data\": \"Impression\" }\' [Data ] { \"device\_info\": \[ { \"Device\": \"JB001690\", \"Date\": \"01-Apr\", \"Day\": \"Mon\", \"dev\_loc\": \"7sins\", \"dev\_frameid\": \"None\", \"dev\_info\": \"Manchester\", \"dev\_temp\": 9, \"dev\_rain\": 0, \"Total\": 7710, \"hour0\": 1149, \"hour1\": 74, \"hour2\": 29, \"hour3\": 29, \"hour4\": 29, \"hour5\": 29, \"hour6\": 29, \"hour7\": 29, \"hour8\": 29, \"hour9\": 29, \"hour10\": 29, \"hour11\": 29, \"hour12\": 29, \"hour13\": 29, \"hour14\": 29, \"hour15\": 13, \"hour16\": 666, \"hour17\": 843, \"hour18\": 992, \"hour19\": 857, \"hour20\": 694, \"hour21\": 919, \"hour22\": 643, \"hour23\": 483 }, { \"Device\": \"JB001690\", \"Date\": \"02-Apr\", \"Day\": \"Tue\", \"dev\_loc\": \"7sins\", \"dev\_frameid\": \"None\", \"dev\_info\": \"Manchester\", \"dev\_temp\": 8, \"dev\_rain\": 3, \"Total\": 5850, \"hour0\": 150, \"hour1\": 26, \"hour2\": 26, ### 2.3.4 Mapping to Vendi Schema ### 1. **Impressions Table:** - impression\_id: Auto-generated upon insertion into the database. - machine\_id: Mapped from the \"Device\" field in the DataJam response (e.g., \"Device\": \"JB001690\"). This will need to be matched with the corresponding machine\_id in the Machines table. - date: Derived from the \"Date\" field in the DataJam response (e.g., \"Date\": \"01-Apr\"). This will need to be formatted into a DATE format (e.g., YYYY-MM-DD). - total\_impressions: Mapped from the \"Total\" field in the DataJam response (e.g., \"Total\": 7710). - temperature: Mapped from the \"dev\_temp\" field in the DataJam response (e.g., \"dev\_temp\": 9). - rainfall: Mapped from the \"dev\_rain\" field in the DataJam response (e.g., \"dev\_rain\": 0). - source\_system: Hardcoded value \"DataJam\" to indicate the source of the data. - source\_system\_id: Could be derived from the combination of \"Device\" and \"Date\" (e.g., \"JB001690\_01-Apr\"), or another unique identifier from the DataJam data if available. - created\_at, updated\_at, deleted\_at: Automatically set to the current timestamp for created\_at and updated\_at. deleted\_at remains NULL unless the record is deleted. 2. **Machines Table:** - machine\_id: Mapped from the \"Device\" field in the DataJam response (e.g., \"Device\": \"JB001690\"). - geography\_id: Mapped from a lookup or mapping process using the \"dev\_info\" field (e.g., \"dev\_info\": \"Manchester\"). This would require checking the existing entries in the Geographies table or creating a new entry if the geography does not exist. - created\_at, updated\_at, deleted\_at: Derived from the current timestamp for created\_at and updated\_at. deleted\_at remains NULL unless the record is deleted. 3. **Geographies Table:** - geography\_id: Auto-generated if a new entry is needed. - geography\_name: Mapped from the \"dev\_info\" field in the DataJam response (e.g., \"dev\_info\": \"Manchester\"). User Interface & User Experience ================================ ### 3.1 Key Pages -- User Facing **[1. Monthly Sales Summary]** This page provides a detailed overview of the sales performance for a specified date range. The key features include: - **Date Range Filter**: Allows users to select a specific period to view sales data. - **Total Quantity of Products Purchased**: Displays the total number of products sold within the selected date range. - **Product Split Pie Chart**: Visualizes the proportion of different products sold. - **Average Sales across all Machines**: Calculates the average number of products sold per machine. This is useful for identifying the average rate of sale across the fleet. - **Note**: Data is filtered to ensure brand-specific insights are provided without exposing competitor data (e.g., Elux vs. JTI). - **Sales per Week Graph**: Shows sales trends over weeks, have filter to segment individual products or all products by type/flavour. - **Frequency of Sales Over Time**: Analyzes how frequently sales are made over specific periods, helping in understanding peak sales times. - **Key Points Section**: understanding accrued position that brand has with Vendi (e.g current machines in market with brand inside, peak sales times compared to month prior, % of age verified sales in current month vs last month {any sale made before 10PM is an age verified sale}. [Data Requirements:] 1. **Sales Table**: - sale\_date: To filter sales data within a specific date range. Filter dates to start from August 2024. - Sale\_time: To filter the time that the purchase was made on a specific date - product\_id: To identify which products were sold. - machine\_id: To calculate sales per machine. - quantity: To compute the total quantity of products purchased. 2. **Products Table**: - product\_id, product\_name: To provide the names of the products sold for the product split pie chart. - category\_id: For sales per week split into products. 3. **MachineBrandPartners Table**: - machine\_id, brand\_partner\_id: To ensure sales data is filtered and displayed correctly for each brand partner. 4. **Product Categories Table**: - category\_id: To link products to their respective categories for the pie chart visualization. 5. **Key Points Table**: - category\_id: To link specific data points into the 'Key Points' summary table. **[2. Impressions Summary]** This page focuses on the advertising impressions data collected from the machines, detailing exposure and engagement: - **Date Range Filter**: Allows filtering of impression data by specific periods. - **Seconds of Exposure**: Indicates the total duration that ads were displayed to viewers. - **Note**: Calculated per brand partner, providing insight into their specific exposure time. Time can only be manually changed based on deal. Vendi need to input this manually on a case by case basis. - **Total Amount of Impressions Gained**: Shows the total number of ad impressions garnered by each brand: - Note: Amount of impressions are proportionate directly to seconds of exposure (15s of exposure is 25% of total impressions for that venue). - **Total Advert Playouts**: Represents the number of ad playouts multiplied by the operational hours of the machines. - **Impressions per Week Graph**: Visualizes impressions data on a weekly basis. - Note: Amount of impressions are proportionate directly to seconds of exposure (15s of exposure is 25% of total impressions for that venue). - **Brand Impressions vs. Total Impressions Pie Chart**: Compares a brand\'s impressions against the total impressions across all machines. - **Key Points Section**: understanding accrued position that brand has with Vendi (e.g current machines in market with brand inside, total weekly impressions for the brand across total sites the brand is in, average monthly impressions per venue for brands impressions). [Data Requirements:] 1. **Impressions Table**: - date: To filter impression data by specific periods. - machine\_id: To match impressions with specific machines. - total\_impressions: To calculate the total amount of impressions gained. - brand\_impressions: to calculate brand impressions based on seconds of exposure per venue - advert\_playouts: calculation drawn based on manual input for venue opening hours + hours active with data jam box to calculate hours of operation to sum total playouts for the brand. - source\_system: To ensure data is sourced correctly. 2. **Machines Table**: - machine\_id: To link impressions data with specific machines. 3. **MachineBrandPartners Table**: - machine\_id, brand\_partner\_id: To calculate total advert playouts specific to each brand partner. **[3. Product Breakdown]** Provides a detailed analysis of product sales data: - **Date Range Filter**: Allows users to view product sales data for specific periods. - **Amount of Each Product Sold**: Displays the total quantity of each product sold, without showing revenue. - **Product Split Pie Chart**: Visualizes the distribution of sales among different products. - **Product Category Breakdown**: - **Table and Pie Chart**: Show the number of products sold per category and revenue generated per category. - **Backend Requirement**: Categorizes products (e.g., Kids, Seasonal) to match backend mappings. - Note: Product categories are manual inputs [Data Requirements:] 1. **Sales Table**: - sale\_date: To filter product sales by date. - product\_id: To determine which products were sold. - quantity: To calculate the amount of each product sold. - machine\_id: To filter the right machines for the required product 2. **Products Table**: - product\_id, product\_name: To identify and display product details. - category\_id: To group products by their category for breakdown analysis. 3. **Product Categories Table**: - category\_id, category\_name: To display product categories and aggregate data accordingly. **[4. Site Category Breakdown]** Analyses sales data by geographical locations and site types: - **Date Range Filter**: Filters data to a specific time period. - **Amount of Each Product Sold in Each Site Category**: Displays sales data segmented by site categories (e.g., Adult, Family venues). - Note: Product categories are manual inputs - **Pie Chart and Tables**: Visualize sales data per site category and revenue generated per site category. - **Backend Requirement**: Assigns products to specific venue categories for accurate data segmentation. [Data Requirements:] 1. **Sales Table**: - sale\_date: To filter data by date. - quantity: To calculate the amount of each product sold per site category. - machine\_id: To link sales data to specific machines. 2. **Machines Table**: - machine\_id, geography\_id: To map sales data to specific geographies. 3. **Geographies Table**: - geography\_id, geography\_name: To categorize sales data by geography and site type. **[5.1 Interactive Map for impressions and/or sales]** Provides a geographical view of sales and impression data: - **Date Range Filter**: Allows selection of a specific period to analyze geographic data. - **% of Total Impressions per Geography**: Shows the percentage of total impressions received by each geographic location and the % of impression for each brand in their respective geography. - **Average Monthly Impressions per Machine per Geography**: Calculates the average impressions received by machines in different geographies for each brand. - **% of Total Sales per Geography**: Displays the contribution of each geography to overall sales. - **Number of venues per geography: Displays the amount of venues in each region for each brand** - **Average rate of products sold per geography: Displays average amount of sales in that geography for each brand.** - **Key Points Tab**: Highlights key data points and insights in a tabular format. [Data Requirements:] 1. **Impressions Table**: - machine\_id: To identify machines per geography. - total\_impressions: To calculate impressions per geography. - Brand\_impressions: To calculate average total impressions per % of exposure the brand has 2. **Sales Table**: - machine\_id: To identify sales data per geography. - quantity: To calculate total sales per geography. - Average: to calculate average sales per geography 3. **Machines Table**: - machine\_id, geography\_id: To link machines with their respective geographies. 4. **Geographies Table**: - geography\_id, geography\_name: To display geographic location data. **[6. Optimization Graph]** Compares the performance of each venue against the average performance across the fleet: - **Graph Visualization**: Displays forecasted versus actual sales for each venue. - **Comparison Metrics**: Compares average impressions and average sales rates across all machines. - **Data Points**: Amount of products sold per site, forecasted products based on average brand impressions per site, and total revenue per site. [Data Requirements:] 1. **Sales Table**: - quantity: To calculate the total amount of products sold per site. - machine\_id: To map sales data to specific venues. - Average: to calculate average impressions per brand per venue - Calculation: average total brand impressions divided by brand impression per venue multiplied by actual sales. - Note: Product categories are manual inputs 2. **Impressions Table**: - machine\_id: To calculate the average impressions per machine. - total\_impressions: To compare impressions against sales. 3. **Geographies Table**: - geography\_id, geography\_name: To display geographic location data. **[7. Month-on-Month Summary]** Provides a comparative view of sales and impressions data over different months: - **Date Range Filter**: Allows users to select specific months for comparison. - **Total Quantity of Products Purchased**: Shows the cumulative sales data. - **Product Split Pie Chart**: Visualizes consolidated and cumproduct sales distribution. - **Average Sales per Machine**: cumulative comparison of average sales data. - **Monthly Trends Graph**: Compares monthly sales and impressions. - **Product Sales Comparison**: Shows specific product sales month-by-month. - **APIs Needed**: A combination of all the APIs discussed, providing compounded monthly data. [Data Requirements:] 1. **Sales Table**: - sale\_date: To compare monthly sales data. - quantity: To compute total sales for the month. - product\_id: For product split comparison. - Average: average of the cumulative sales data for all of the machines within the brands 2. **Impressions Table**: - date: To compare impressions on a month-by-month basis. - total\_impressions: To compute the total impressions for the month. 3. **Machine Table**: - machine\_id, machine\_name: To get information from the machines **[8. Comprehensive Monthly Overview]** Summarizes all data points monthly: - **Date Filter**: Allows users to select a specific month. - **Tabulated Data**: Compiles all data from previous sections into a single, comprehensive view. - **Growth/Decline Indicators**: Uses arrows to show growth or decline from the previous month. - **APIs Needed**: Consolidates data from all relevant APIs discussed earlier. [Data Requirements:] 1. **Sales Table**: - All fields relevant to summarize monthly data points such as sale\_date, product\_id, machine\_id, and quantity. 2. **Impressions Table**: - All fields relevant to summarize monthly impression data such as date, machine\_id, and total\_impressions. 3. **Machine Table**: - machine\_id, machine\_name: To get information from the machines ### 3.2 Key Pages -- Admin Facing ### [Key Actions of the Admin Panel] 1. **Client Management:** - **Add/Remove/Edit Clients**: i. Full CRUD (Create, Read, Update, Delete) functionality for managing clients. - **Ability to assign machines to clients and view their performance.** 2. **Generate Client-specific reports (client and admin facing feature)**: Ensure that data shown to clients (e.g., JTI) only contains relevant machine/product data. - **This called export report -- essentially just plugging in numbers into a report.** 3. **Machine Management:** - **Edit Machines**: ii. Editing machine details iii. Nayax is the source of truth for machines -- deletion + addition of machines is through NAYAX. iv. TBC - **Machine Status**: Real-time monitoring of machine health and performance. v. Machine uptime/downtime tracking. vi. Fault/Error reporting from machines. - **Geography and Venue Data**: vii. Link machines to specific geographical locations (using geographies from the schema​), allowing breakdowns by city/venue. 4. **Product Management:** - **Edit Products**: viii. Edit product names. ix. TBC 5. **User Management** - We want to manually control email and password for client side users -- we do not need third party auth. 6. **Integration Management:** - **API Integration Monitoring**: Monitor API integrations with Nayax (for sales) and DataJam (for impressions). x. View data sync logs and any errors encountered during data fetching​​. - **Error Logging**: Real-time error monitoring and resolution for integration issues​. ### ### 3.3 Design Inspiration  Technical Roadmap ================= ### 3.1 Phase 1: Planning, Design and Initial Setup (1 week) During this phase, we will focus on planning and designing the Vendi+ platform, including finalizing requirements, architecture, and the database schema. We will also set up the development environment, including the integration of necessary tools and frameworks such as Django for the backend, React and Next.js for the frontend, and PostgreSQL for the database. The estimated time for this phase is 2 weeks. This phase will run concurrently with the design of the frontend. ### 3.2 Phase 2: Development of Backend Data Pipeline (2 weeks) In this phase, the focus will be on developing the backend data pipeline required to integrate and process data from external sources such as Nayax and DataJam. This involves setting up APIs for data retrieval, designing data processing workflows to handle incoming data streams, and ensuring that the data is correctly formatted and stored in the database schema established during Phase 1. Key components include writing scripts for data extraction, transformation, and loading (ETL) into the PostgreSQL database, as well as implementing error handling and logging mechanisms. The development team will also ensure that the pipeline is optimized for performance and scalability. Given the focused scope and clear data requirements, this phase is estimated to take about 1 week. This phase will run concurrently with the design of the frontend. ### 3.3 Phase 3: Full Stack Development of Final Product (4 weeks) This phase will encompass the complete development of the Vendi+ platform, integrating both backend and frontend components to deliver the final product. The backend team will focus on developing API endpoints, managing data persistence, and ensuring robust security measures are in place. Simultaneously, the frontend team will work on building a responsive user interface using React and Next.js, incorporating data visualization tools to display sales, impressions, and other analytics effectively. This phase is estimated to take approximately 4 weeks. ### 3.5 Rough Time Estimation - Design Phase (3 weeks) (runs concurrently with phase 1 and 2) - **Phase 1: Planning, Design and Initial Setup**: 1 week - **Phase 2: Development of Backend Data Pipeline**: 2 weeks - **Phase 3: Full Stack Development of Final Product**: 4 weeks per iteration Overall, the estimated total time from start to finish to go live with a basic MVP is approximately 8 weeks months. Please note that this is only an estimate and actual times may vary, I expect it to likely be less than this if we have 3 engineers working on the team -- the above estimations are just for one engineer (myself).