Supply Chain Automation Pipeline

An automated ETL pipeline that eliminates manual data entry in supply chain management. Previously, sales reports were manually retrieved from Gmail CSV attachments and updated in the database; this workflow automates that entire lifecycle from end-to-end.

Table of Contents

• Overview
• Architecture
• Data Model
• Prerequisites
• Workflow Configuration
• Database Logic
• Quadratic Dashboard
• Troubleshooting

---

Overview

Previously, sales reports were manually retrieved from email attachments and updated in the database. This pipeline automates that entire lifecycle end-to-end.

Core objectives:

• Manual effort reduction — automates the extraction and loading of daily sales data from Gmail CSV attachments, eliminating the need for manual database updates
• Data consistency — date fields are standardised, with additional field mapping and formatting applied in Quadratic before visualisation
• System integrity — implement upsert (MERGE) logic to prevent duplicate records
• Visibility — provides a clean, real-time data feed into BigQuery, enabling faster reporting and decision-making through the Quadratic dashboard

Pipeline summary:

• Ingestion — listens for emails with the daily sales subject via the Gmail API
• Filtering — validates attachments by checking the binary filename includes the expected identifier (e.g. fact_order_line)
• Loading — executes a MERGE statement into the BigQuery fact_aggregate and fact_order_line tables
• Synchronization — ensures that re-running the pipeline with the same data produces the same result — no duplicates, no corruption

---

Architecture

Gmail (CSV attachments)
        │
        ▼
   n8n Workflow  ◄── Schedule Trigger
   ├── Branch 1: Order Line
   │   Extract → Edit Fields → Aggregate → Build MERGE Query → BigQuery
   └── Branch 2: Aggregate
       Extract → Edit Fields → Batch → Build MERGE Query → BigQuery
        │
        ▼
    BigQuery (atliq_mart)
    ├── fact_order_line
    └── fact_aggregate
        │
        ▼
  Quadratic Dashboard
  └── Python + Plotly visualisations

---

Data Model

Key relationships:

• fact_aggregate — stores high-level order information (order_id, otif scores, in_full status)
• fact_order_line — stores granular line-item data linked to each order (product_id, delivery_qty)
• Cardinality: 1:N — one order in the aggregate table corresponds to multiple rows in the order line table

---

Prerequisites

System Requirements

• n8n instance with access to the internet (for Gmail API)
• Google Cloud project with BigQuery enabled
• A BigQuery dataset (e.g. atliq_mart) with fact_aggregate and fact_order_line tables created
• Quadratic account with AI features enabled

External Services & Authentication

• Google Cloud Console — required to generate OAuth2 Client ID and Client Secret for Gmail API access
• Gmail API — must be enabled in the Google Cloud Console library
• BigQuery permissions — ensure the service account has BigQuery Data Viewer, BigQuery Job User and BigQuery Metadata Viewer roles
• BigQuery → Quadratic connection — requires a JSON secret key generated from a Google Cloud service account. Download the key from Google Cloud Console under IAM & Admin → Service Accounts → Keys and use it to authenticate the BigQuery connection in Quadratic

---

Workflow Configuration

All nodes must be configured in the sequence listed below.

Step	Node	Configuration Focus
01	Schedule Trigger	Set desired interval (e.g. daily)
02	Gmail — Get Many Messages	Filter by subject: daily sales. Enable download attachments
03	Extract Order Line / Extract Aggregate	Dynamically selects the correct CSV attachment by matching the expected filename
04	Edit Fields — Order Line / Aggregate	Convert dates: order_placement_date, agreed_delivery_date, actual_delivery_date
05	Aggregate / Batch Aggregate	Consolidate rows before query construction
06	Build MERGE Query	JavaScript node that constructs the BigQuery MERGE statement
07	BigQuery — Order Line / Aggregate	Execute the MERGE query via executeQuery

---

Database Logic

A MERGE (upsert) operation is used as a safeguard. If a row already exists it is skipped; if it is new it is inserted. This prevents duplicate entries on re-runs.

fact_aggregate

• Match key: order_id
• On match: updates in_full, on_time, otif
• On no match: inserts full record

See sql_queries/fact_aggregate_merge.sql for the full query.

fact_order_line

• Match key: order_id + product_id (composite key)
• On match: updates all delivery and fulfilment fields
• On no match: inserts full record

See sql_queries/fact_order_line.sql for the full query.

---

Quadratic Dashboard

The BigQuery tables feed directly into a Quadratic spreadsheet where Python and Plotly are used to generate interactive visualisations. A fact_summary table is created in Quadratic by joining fact_order_line with the dimension tables (dim_products, dim_customers, Exchange Rate).

Charts included:

• In Full rate by category
• Top 5 customers by order value
• Revenue loss by category
• OTIF% vs OT% vs IF% for top 5 customers

See Prompts.md for the AI prompts used to build each chart in Quadratic.

See Supply_Chain_Automation.grid to open the full dashboard in Quadratic.

---

Key Insights & Recommendations

The following insights were surfaced from the dashboard analysis.

Headline Metrics:

• ₹1.33 Crore revenue loss from unfulfilled orders
• 20.4% of orders delivered late
• Only 51.6% OTIF — half of all orders meet both conditions
• Dairy accounts for 68% of total revenue loss (₹91 lakh)
• Average delivery delay of 1.15 days

Supply Chain Bottlenecks by Category:

• Dairy — highest revenue loss (68%) driven by cold chain requirements and perishability; even a 1-day delay impacts product quality
• Beverages — lowest In Full rate (61.8%); likely strained by demand spikes, refrigeration gaps, and limited storage life
• Packaged Foods — moderate perishability with storage space constraints and supplier variability
• Personal Care — lower perishability but susceptible to inventory mismanagement and supplier capacity issues

Stakeholder Recommendations:

1. Invest in cold chain logistics for perishable categories
2. Diversify suppliers to reduce risk of shortages and capacity shortfalls
3. Implement real-time inventory tracking and demand forecasting
4. Share forecasts with suppliers for better planning alignment
5. Monitor OTIF and In Full metrics by supplier and category to address issues early

---

Troubleshooting

Issue	Cause	Fix
BigQuery connection fails	Service account credentials missing or expired	Re-authenticate in n8n and verify the service account key in Google Cloud Platform
Gmail fetch stops	OAuth2 token expired	Re-authenticate Gmail credentials in n8n; confirm Gmail API scope is still active in Google Cloud
Duplicate rows appearing	MERGE ON clause mismatch	Verify the composite key (order_id + product_id) correctly identifies existing records