Skip to content

Owner's Dashboard

This workflow creates a comprehensive daily business intelligence dashboard for Lee Supermarket by processing transaction data from Excel files stored in Google Drive, generating multiple analytical reports, and delivering insights via email and Google Sheets.

Purpose

No business context provided yet — add a context.md to enrich this documentation.

Based on the workflow structure, this appears to serve supermarket owners and managers who need daily operational insights including revenue tracking, profit analysis, top-performing categories/items/staff, hourly sales patterns, and payment method preferences. The automated dashboard eliminates manual reporting and provides actionable business intelligence for decision-making.

How It Works

  1. Data Retrieval: When triggered by a webhook (dashboard page load), the workflow searches Google Drive for files containing "supermarket" in the name
  2. Data Processing: Downloads and reads the Excel file containing transaction data with fields like item names, categories, profit margins, cashier information, and timestamps
  3. Analytics Generation: Processes the raw data through multiple analysis nodes that calculate:
    • Daily KPIs (total revenue, profit, transactions, average basket size)
    • Top categories, subcategories, and items by profit
    • Sales staff performance metrics
    • Supplier contribution analysis
    • Hourly sales patterns
    • Payment method preferences
  4. Data Storage: Writes each analysis to separate sheets in a Google Sheets dashboard
  5. Email Reporting: Combines all analyses into a formatted HTML email and sends a daily summary to the specified recipient
  6. Response: Returns confirmation to the webhook caller

Workflow Diagram

graph TD
    A[Dashboard Page Opened] --> B[Find Supermarket Data Sheet]
    B --> C[Download Supermarket Data]
    C --> D[Read Excel Sheet]

    D --> E[Daily KPIs]
    D --> F[Top Categories By Profit]
    D --> G[Top Sub Categories By Profit]
    D --> H[Top Items By Profit]
    D --> I[Top Sales People By Profit]
    D --> J[Top Suppliers By Profit]
    D --> K[Hourly Sales Analysis]
    D --> L[Payment Method Analysis]

    E --> M[Daily KPIs sheet]
    F --> N[Top Categories sheet]
    G --> O[Top Sub Categories sheet]
    H --> P[Top Items sheet]
    I --> Q[Top Sales People sheet]
    J --> R[Top Suppliers sheet]
    K --> S[Hourly Sales Analysis sheet]
    L --> T[Payment Method sheet]

    M --> U[Respond to Webhook]
    M --> V[Merge]
    N --> V
    O --> V
    P --> V
    Q --> V
    R --> V
    S --> V
    T --> V

    V --> W[Format Email]
    W --> X[Send a message]

Trigger

Webhook: POST /dashboard-page-onload - Triggered when a dashboard page is loaded - No authentication required - Returns response after processing

Nodes Used

Node Type Purpose
Webhook Receives dashboard page load requests
Google Drive (List) Searches for supermarket data files
Google Drive (Download) Downloads the Excel data file
Spreadsheet File Reads Excel data into workflow
Code (JavaScript) Processes and analyzes transaction data (8 analysis nodes)
Google Sheets Writes analysis results to dashboard sheets (8 output nodes)
Merge Combines all analysis outputs for email formatting
Gmail Sends formatted daily summary email
Respond to Webhook Returns confirmation to webhook caller

External Services & Credentials Required

Google Drive

  • Purpose: File storage and retrieval
  • Permissions: Read access to files containing "supermarket"
  • Credential Type: Google OAuth2

Google Sheets

  • Purpose: Dashboard data storage
  • Document ID: 1b1H2aB-PcfUbIHVk2Zunb3OmL7JfD2SFlw6f-SR0Nwk
  • Permissions: Write access to multiple sheets
  • Credential Type: Google OAuth2

Gmail

  • Purpose: Email delivery
  • Permissions: Send email capability
  • Credential Type: Google OAuth2

Environment Variables

No environment variables are explicitly configured in this workflow. All configurations are hardcoded within nodes.

Data Flow

Input Data

  • Source: Excel files from Google Drive
  • Expected Fields:
    • transaction_id, transaction_time
    • item_name, item_id, category, subcategory
    • quantity_sold, line_total, selling_price
    • profit_margin, discount_amount, unit_tax_amount
    • cashier_id, cashier_name
    • supplier_id, supplier_name
    • payment_method

Output Data

  • Google Sheets: 8 separate analysis sheets with aggregated metrics
  • Email: HTML-formatted daily summary report
  • Webhook Response: Processing confirmation

Key Calculations

  • Revenue: line_total - discount_amount
  • Profit: (profit_margin × quantity_sold) - discount_amount
  • Tax: unit_tax_amount × quantity_sold

Error Handling

The workflow includes basic error handling through: - Null checks in JavaScript code nodes - Default values for missing data fields - Try-catch blocks for date parsing operations

No explicit error notification or retry mechanisms are implemented.

Known Limitations

  • Workflow is currently archived and inactive
  • Hardcoded email recipient (danielnjoroge10050@gmail.com)
  • Fixed Google Sheets document ID limits portability
  • No data validation or quality checks
  • Single file processing (latest file matching "supermarket")
  • No historical data retention or comparison capabilities

No related workflows are mentioned in the available context.

Setup Instructions

  1. Import Workflow

    • Import the JSON into your n8n instance
    • Activate the workflow (currently archived)

  2. Configure Credentials

    • Set up Google OAuth2 credentials for Drive, Sheets, and Gmail
    • Ensure proper permissions for file access and email sending

  3. Prepare Data Source

    • Upload Excel files to Google Drive with "supermarket" in the filename
    • Ensure data includes all required fields listed in Data Flow section

  4. Configure Google Sheets

    • Create or update the target spreadsheet with appropriate sheet names
    • Update document ID if using a different spreadsheet

  5. Customize Settings

    • Update email recipient in the Gmail node
    • Modify analysis logic in Code nodes as needed
    • Adjust webhook path if required

  6. Test Workflow

    • Trigger via webhook: POST https://your-n8n-instance.com/webhook/dashboard-page-onload
    • Verify data appears in Google Sheets
    • Check email delivery

  7. Schedule Automation (Optional)

    • Replace webhook trigger with Schedule trigger for automated daily runs
    • Configure appropriate timing for business needs