Revenue Operations & Inventory System

Pipeline Architecture

🏪

Google Sheets

POS + Inventory

→

⚙️

AppScript

doGet(?sheet=)

→

🐍

Python

Clean 2 tables

→

🗄️

SQLite

inventory_db.sqlite

→

🔍

sql.js

Browser query

→

📊

Dashboard

Real-time viz

Problem Statement

Managing 100 SKUs across a retail POS operation without dedicated inventory software creates stockout blind spots, margin erosion from untracked discounts, and zero visibility into cashier performance or product profitability.

Solution Built

A fully integrated retail operations system: POS transaction recording with auto stock deduction via AppScript onEdit() trigger, product catalog management with COGS-based margin calculation, and receipt PDF generation — all feeding into this analytical dashboard.

Data Collected

104 transaction line items across multiple invoices, 100 SKUs with real-time stock levels, COGS vs selling price per unit, per-cashier sales records, payment method distribution, and margin per product line.

Business Impact

Stock alert system (qty ≤ 10), margin percentage per SKU, top products by revenue and quantity, cashier performance benchmarking, inventory capital value vs potential selling value gap analysis.

Tech Stack

Google SheetsGoogle AppScriptPython 3.13SQLitesql.js (WebAssembly)Chart.jsGitHub ActionsLockService (race condition prevention)

Total Revenue

—

all sales

Total Profit

—

margin earned

Invoices

—

unique orders

Items Sold

—

total units

Avg Order Value

—

per invoice

Margin %

—

overall

Active SKU

—

stock > 0

Stockout SKU

—

qty = 0

Monthly Sales & Profit Trend

Top 8 Products by Revenue

Payment Method & Cashier

Payment

Cashier

Stock Alert (qty ≤ 10)

SKU	Product	Qty	Status

SKU Performance

SKU	Revenue	Profit	Margin

Recent Invoices

Invoice	Date	Items	Total

Stockout Transactions (104)

Product Stock (100 SKU)

Custom Query

No results

Pipeline Stats

—

Transactions

—

SKUs

Tables

—

Runs

Last Runs

Python Pipeline (pipeline_09.py)

# Fetches 2 datasets in 1 API call
payload["stockout"]  # 104 transaction lines
payload["products"]  # 100 SKU snapshots

# Derives margin_per_unit & margin_pct
margin = value_selling - value_capital
margin_pct = margin / value_capital * 100

# Validates stock consistency
if in_stock - out_stock != qty_stock:
    warnings.append(...)

AppScript Export (export.js)

// Two datasets, one Web App
// Supports ?sheet= parameter
GET /exec               → both tables
GET /exec?sheet=stockout → sales only
GET /exec?sheet=products → stock only

// Auto mode: onEdit() + LockService
const lock = LockService
  .getScriptLock();
lock.waitLock(250); // race prevention

GitHub Actions

# Scheduled 00:01 WIB daily
cron: '1 17 * * *'

# Or trigger manually:
workflow_dispatch:
  inputs:
    pipeline: pipeline_09

env:
  WEBAPP_URL_09: ${{secrets.WEBAPP_URL_09}}