Client Pipeline & Lead Tracking System
Client Pipeline & Lead Tracking System - Developed a lightweight Google Sheets CRM with Apps Script automation
Services
CRM, Leads Pipeline, Leads Tracking
Tools
Google Sheets, Apps Script, Advanced Formula
Value
Data Management, CRM, Automations Data, high performance
Timeline
4 weeks
Problem / Trigger
Freelancers and solo service providers often manage leads, follow-ups, and client conversion using scattered notes, chat history, inbox searches, and basic spreadsheets that are not designed for customer relationship management. As the number of inquiries increases, it becomes harder to maintain lead quality, track communication history, identify high-potential clients, and monitor which opportunities are moving toward a signed contract. This creates common business problems such as missed follow-ups, inconsistent qualification, weak sales visibility, and poor pipeline control.
The trigger behind building this tool was the need to create a lightweight CRM system that fits the workflow of a freelancer or small service business without requiring expensive subscription software. The objective was to turn Google Sheets into a practical operational tool for managing inbound leads, organizing client information, monitoring deal progress, and tracking contract opportunities in a structured and measurable way.
Solution
I designed and developed a Google Sheets based mini CRM for freelancers that centralizes lead intake, lead qualification, follow-up activity, and contract tracking into one connected workflow. Instead of using spreadsheets only as static records, I structured the file as a process-driven application with dedicated sheets for new lead entry, lead status updates, database management, and dashboard-ready monitoring.
The system enables users to register new leads through a guided input form, assign automatic lead IDs, categorize sources, maintain lead status, track interaction count, record qualification scores, define service interest, prioritize opportunities, and manage contract related fields such as project budget, contract start, and contract end dates. It also supports follow-up planning through fields such as last contact date and next follow-up date, which are essential for freelancers who rely on timely communication to convert prospects into paying clients.
To make the workflow more practical, the tool combines spreadsheet formulas with Google Apps Script automation. This allows input forms and update forms to write directly into the structured lead database, reducing manual editing of raw data and improving consistency across sheets. The database design was extended to support CRM-specific monitoring dimensions such as preferred contact method, decision stage, priority level, budget range, and lead ownership, making the tool far more useful than a basic contact list.
A fully automated lead tracking system built with Google Sheets, Google Apps Script, Python, SQLite, and SQL — demonstrating real data engineering skills across every layer, from operational data entry to analytical visualization.
Google Sheets | Apps Script | Python 3.13 | SQLite | SQL | GitHub Pages |
01. Background & Problem Statement
Why Was This Portfolio Built?
Most data candidates only showcase the end result — a polished dashboard or a clean Jupyter notebook. What is rarely shown is how the data got there: the entry process, automation, cleaning, storage, and querying. This portfolio is designed to answer that question transparently and comprehensively.
Business Problems Solved
Pipeline Visibility Without a centralized system, leads are scattered across emails, notes, and separate spreadsheets. There is no single view showing where each prospect stands in the sales funnel. Missed Follow-Ups Without automated scheduling, follow-ups depend on human memory — causing lost opportunities due to delayed responses or no follow-through at all. | Subjective Lead Qualification Lead quality is assessed intuitively without a consistent scoring system, making team time allocation inefficient and unpredictable. No Conversion Reporting It is difficult to know which lead source is most productive, what the actual conversion rate is, and what the total value of signed contracts amounts to. |
02. Why Google Sheets + Apps Script?
Google Sheets was chosen not because of limitations, but because it represents the reality of small-to-medium business operations. Most real business workflows start with spreadsheets — not enterprise databases. By building on this platform, this portfolio demonstrates the ability to work with data in real conditions, not ideal ones.
01 | Google Sheets — Operational Layer Lead entry form with auto-ID generation (LRK + year + month + sequence). Status update workflow with automatic VLOOKUP restoration after every update. |
02 | Google Apps Script — Automation Engine The kirimDataLeads() function reads data from the form, injects a timestamp, and appends to the Database Leads sheet. updateLeads() finds the row by ID and updates 22 columns at once, then restores all VLOOKUP formulas. |
03 | Web App Export — JSON API The doGet() function is deployed as a public Apps Script Web App. Every GET request returns the entire Database Leads sheet as clean JSON with snake_case keys — ready for Python consumption. |
03. Why Python Is Needed
Raw data from Google Sheets is not immediately ready for analytics. The exported JSON still contains inconsistent data types, varied date formats, uneven null values, and number strings that need conversion. Python serves as the transformation layer that cleans, validates, and loads the data into SQLite.
What pipeline_02.py Does:
Fetch & Parse Retrieves JSON from the Apps Script Web App URL using the requests library. Validates the response and handles connection errors with informative messages. Type Normalization Converts all date formats to ISO 8601 (YYYY-MM-DD). Converts number strings to float. Standardizes null values to None. | Enum Validation Validates status, priority_level, and contract_status column values against permitted sets. Every anomaly is logged as a warning with row number and lead ID. Idempotent Upsert Uses INSERT ... ON CONFLICT(id_leads) DO UPDATE — the pipeline can run repeatedly without creating duplicate data. Every run is recorded in the pipeline_runs table. |
04. Why SQL + SQLite?
SQLite was chosen because it is serverless, file-based, and portable — no database server installation required. The .sqlite file can be committed directly to GitHub and read by the browser using sql.js (WebAssembly). This means anyone opening the dashboard can execute SQL queries in real-time directly in their browser, with no backend whatsoever.
20 | 7 | 76.7 | $24,290 |
Total Leads | Signed Clients | Avg Lead Score | Total Budget |
Key SQL Query — Conversion by Source:
SELECT source, COUNT(*) AS total,
SUM(CASE WHEN contract_status='Signed' THEN 1 ELSE 0 END) AS signed,
ROUND(AVG(lead_score), 1) AS avg_score
FROM leads
GROUP BY source ORDER BY total DESC;
This query shows the performance of each lead source — how many came in, how many converted to signed, and the average quality score. In the dashboard, recruiters can modify this query directly and watch the results change in real-time.
05. End-to-End Architecture & GitHub Integration
L1 | Google Sheets + Apps Script Data entry via form → Database Leads sheet → doGet() export as public JSON API. |
L2 | Python ETL Pipeline Fetch JSON → clean & validate → upsert to SQLite. Run manually for validation, then automated via GitHub Actions every day at 00:01 WIB. |
L3 | SQLite + SQL Queries .sqlite file committed to GitHub. 19 SQL queries produce KPIs, chart data, and analytical insights. |
L4 | GitHub Pages Dashboard index.html uses sql.js (WebAssembly) to load and query SQLite directly in the browser. Interactive filters, SQL editor, raw data viewer, and pipeline history — all with no backend server. |
L5 | GitHub Actions Automation Scheduled workflow (cron: 1 17 * * *) runs pipeline_02.py automatically each day, updates the SQLite in the repo, and the dashboard refreshes with no manual intervention. |
06. Impact & Portfolio Value
This portfolio is not merely a technical demonstration — it is proof of a comprehensive understanding of how data moves through a real business organization.
For Recruiters & Hiring Managers Every pipeline layer can be traced directly: Apps Script code is available, Python script is documented, SQL queries can be edited and re-run in the browser, and raw data can be inspected in the Raw Data tab. Skills Demonstrated Data modeling, ETL pipeline design, SQL analytics, REST/JSON API design, automation (cron/GitHub Actions), and dashboard development — all in one integrated portfolio. | Industry Relevance This stack simulates real company data architecture: Google Sheets as the operational source (like CRM), Python as the ETL layer (like dbt), SQLite as the warehouse (like BigQuery), and the dashboard as the BI layer (like Looker). Publicly Verifiable All code is available in a public GitHub repository. The dashboard is accessible to anyone. Data can be queried directly. Nothing is hidden — this is full transparency as proof of competence. |
