Client Pipeline & Lead Tracking

Pipeline Architecture

📋

Google Sheets

Form entry + DB

→

⚙️

AppScript

doGet() → JSON

→

🐍

Python

Clean + load SQLite

→

🗄️

SQLite

leads_db.sqlite

→

🔍

sql.js

Browser query

→

📊

Dashboard

Real-time viz

Problem Statement

Tracking sales leads across multiple channels without a CRM system leads to missed follow-ups, unclear pipeline health, and no visibility into which sources convert best. Manual spreadsheet management creates data silos with zero automation.

Solution Built

A fully automated lead tracking system using Google Sheets as the operational UI, AppScript as the automation engine, Python for ETL processing, SQLite as the analytical layer, and this dashboard for BI visualization — all integrated end-to-end.

Actions Automated

Lead entry form with auto-ID generation (LRK format), status update workflow with VLOOKUP restoration, JSON export API via AppScript Web App, daily Python pipeline with validation, and SQL-powered dashboard with live filtering.

Business Impact

Real-time pipeline visibility, conversion rate tracking from lead source to signed contract, automated follow-up scheduling, lead score distribution analysis, and budget forecasting from signed contract values.

Tech Stack

Google Sheets Google AppScript Python 3.13 SQLite sql.js (WebAssembly) Chart.js GitHub Actions REST / JSON API

Total Leads

—

all records

Qualified

—

status = qualified

Signed Clients

—

contract signed

Conversion Rate

—

signed / total

Avg Lead Score

—

0–100 scale

Signed Budget

—

total contract value

Leads by Month

Lead Source

Status Breakdown

Top Qualified Leads

Name	Company	Score	Stage

High Priority (Unsigned)

Name	Status	Score	Budget

Upcoming Follow-ups (7d)

Name	Priority	Follow-up

Custom Query

No results yet

Pipeline Stats

—

Total Records

Columns

—

Pipeline Runs

Last Pipeline Runs

Python Pipeline (pipeline_02.py)

# 1. Fetch JSON from AppScript Web App
response = requests.get(WEBAPP_URL, timeout=30)
payload  = response.json()

# 2. Clean & validate each record
for raw in payload["data"]:
    record, warnings = clean_record(raw)
    if record: cleaned.append(record)

# 3. Upsert to SQLite (idempotent)
cur.execute(UPSERT_SQL, record)

# 4. Print validation summary
print_validation_summary(DB_PATH)

AppScript Export (export.js)

function doGet(e) {
  const output = exportLeadsAsJSON();
  return ContentService
    .createTextOutput(
      JSON.stringify(output))
    .setMimeType(
      ContentService.MimeType.JSON);
}

// Reads Database Leads sheet
// Maps headers to snake_case keys
// Normalizes dates & null values
// Returns { data: [...], total_rows }

Automation (GitHub Actions)

# .github/workflows/daily_pipeline.yml
on:
  schedule:
    - cron: '1 17 * * *'
      # = 00:01 WIB every day

steps:
  - run: pip install requests
  - run: |
      cd 02-client-pipeline
      python pipeline_02.py \
        --url $WEBAPP_URL_02