V4 - dailySalesDataCollection

This workflow collects and processes daily sales data from entrepreneurs, calculating profit margins and storing the information in a PostgreSQL database. It handles both real business data and practice entries, automatically computing profit by factoring in sales revenue against various costs including stocking, transport, and fixed daily expenses.

Purpose

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

This workflow appears to be designed for tracking entrepreneurial business performance on a daily basis. It serves entrepreneurs or business coaches who need to monitor daily sales metrics, costs, and profitability. The system supports both real business tracking and practice mode for training purposes.

How It Works

1. Data Reception: The workflow receives daily business data including sales figures, various cost categories, and micro-actions taken
2. Database Storage: All data is inserted into a PostgreSQL table called dailyProfitTracking
3. Profit Calculation: The system automatically calculates profit by subtracting all costs (stocking, transport, other, and daily fixed costs) from sales revenue
4. Fixed Cost Lookup: Daily fixed costs are retrieved from a separate youthEntrepreneursReal table based on the phone number
5. Data Formatting: Key metrics (profit, date, sales) are extracted and formatted for downstream use
6. Conflict Resolution: If data for the same phone number and date already exists, it updates the existing record

Workflow Diagram

graph TD
    A[When Executed by Another Workflow] --> B[Insert rows in a table]
    B --> C[Edit Fields]

    style A fill:#e1f5fe
    style B fill:#f3e5f5
    style C fill:#e8f5e8

Trigger

Execute Workflow Trigger: This workflow is designed to be called by other workflows, not triggered directly. It expects the following input parameters:

• date - Business date
• phoneNumber - Entrepreneur's phone number
• sales - Daily sales amount (number)
• stockingCost - Cost of inventory/stock (number)
• transportCost - Transportation expenses (number)
• otherCost - Miscellaneous costs (number)
• Micro actions - Actions taken during the day
• isPractice? - Boolean flag for practice vs. real data

Nodes Used

Node Type	Node Name	Purpose
Execute Workflow Trigger	When Executed by Another Workflow	Receives input data from calling workflows
PostgreSQL	Insert rows in a table	Stores daily sales data and calculates profit
Set	Edit Fields	Formats output data with key metrics

External Services & Credentials Required

• PostgreSQL Database: Requires database credentials with read/write access to:
  • dailyProfitTracking table (main data storage)
  • youthEntrepreneursReal table (for fixed cost lookup)

Environment Variables

No specific environment variables are documented in this workflow. Database connection details are handled through n8n's credential system.

Data Flow

Input Data

{
  "date": "2024-01-15",
  "phoneNumber": "+1234567890",
  "sales": 1000,
  "stockingCost": 600,
  "transportCost": 50,
  "otherCost": 25,
  "Micro actions": "Visited 5 new customers",
  "isPractice?": false
}

Output Data

{
  "profit": 275,
  "dateOfBusiness": "2024-01-15",
  "sales": 1000
}

The profit is calculated as: Sales - Stocking Cost - Transport Cost - Other Cost - Daily Fixed Cost

Error Handling

The workflow includes an error workflow reference (cuHEGQjAfvuGwIOD) that will be triggered if any node fails during execution. The PostgreSQL node uses COALESCE to handle cases where daily fixed costs might not be found for a phone number, defaulting to 0.

Known Limitations

• Phone numbers are trimmed of trailing commas, suggesting potential data quality issues in the source
• The workflow assumes daily fixed costs are stored in a separate table and may not exist for all users
• No validation is performed on input data types or ranges
• The workflow only handles single-day entries, not batch processing

Related Workflows

This workflow is designed to be called by other workflows but no specific parent workflows are documented. The error workflow cuHEGQjAfvuGwIOD is referenced for error handling.

Setup Instructions

1. Import the Workflow: Import the JSON into your n8n instance

2. Configure PostgreSQL Credentials:

   • Create PostgreSQL credentials in n8n
   • Ensure access to both dailyProfitTracking and youthEntrepreneursReal tables

3. Database Schema Requirements:

   -- Ensure these tables exist with appropriate columns
   CREATE TABLE IF NOT EXISTS "dailyProfitTracking" (
     "phoneNumber" TEXT,
     "dateOfBusiness" DATE,
     "sales" NUMERIC,
     "stockingCost" NUMERIC,
     "transportCost" NUMERIC,
     "otherCost" NUMERIC,
     "dailyFixedCost" NUMERIC,
     "profit" NUMERIC,
     "microActions" TEXT,
     "isPractice" BOOLEAN,
     PRIMARY KEY ("phoneNumber", "dateOfBusiness")
   );
   
   CREATE TABLE IF NOT EXISTS "youthEntrepreneursReal" (
     "phoneNumber" TEXT PRIMARY KEY,
     "dailyFixedCost" NUMERIC
   );
   

4. Configure Error Handling: Set up the error workflow referenced in settings

5. Test the Workflow: Create a parent workflow that calls this one with sample data to verify functionality

6. Activate: Set the workflow to active status once testing is complete