InsightBridge AI — WhatsApp-to-BigQuery with Vertex AI

Your data plumber + data analyst in one

Comment

Inspiration

Support teams drown in chat transcripts and screenshots. Valuable signals (late deliveries, repeat complaints, churn risk) are trapped in raw text. We asked: Could a “data plumber + AI analyst” turn messy chats into daily retention actions in minutes? That became InsightBridge AI.

What it does

Ingests a WhatsApp-style .txt export. Parses messages into a structured table. Aggregates a 14-day “negative signal” score in BigQuery and flags churn risk. Uses Vertex AI (Gemini 2.5 Flash) to produce three short, actionable retention suggestions per customer.

How we built it

Connector (Fivetran SDK): We prototyped a custom source for WhatsApp-style text and verified it locally with the SDK tester. Storage & Warehouse: Raw files in Cloud Storage, modeled in BigQuery with a view that computes message counts and negativity over a rolling window. AI Layer: Gemini 2.5 Flash converts metrics into concise, on-point actions for support agents. UI & Hosting: Streamlit front-end deployed from source to Cloud Run as a public service.

Challenges we ran into

Windows dev quirks: venv activation and CLI recognition (solved by calling the venv’s fivetran.exe directly). Dependency conflicts: resolved by keeping deploy-time requirements.txt lean (no local SDK pins). Regional consistency: standardized on US for BigQuery/Storage to avoid location errors. Public access changes: new Cloud Run Security → Allow public access toggle replaced older IAM flows.

Accomplishments that we're proud of

End-to-end live URL: Users get a public Cloud Run app that runs on real BigQuery features and Vertex AI suggestions—no mock UI. Public access configured in the new Security → Allow public access flow. Google Cloud Documentation Connector SDK validated: We used fivetran debug and fivetran reset to simulate production behavior locally and confirm deterministic upserts into a test warehouse. Crisp design + minimal deps: A lean requirements.txt for deploy-time stability reduced build conflicts and sped up source-based deployment. Grounded AI: Instead of generic LLM advice, Gemini’s prompts are anchored to concrete warehouse signals (negativity score, 14-day window, message counts), yielding concise, targeted retention actions. Google Cloud Documentation

What we learned

The Fivetran Connector SDK (managed retries/state) drastically simplifies custom ingestion. Gemini 2.5 Flash is a sweet spot for fast, grounded guidance layered on top of warehouse metrics. Cloud Run from-source keeps sharing a live URL trivial for judges.

What's next for InsightBridge AI

Auto-ingest: event-driven updates on every new upload. Multi-channel: extend to Slack/Email; unify identity. Human-in-the-loop: one-click creation of Jira/Zendesk tasks with feedback loops. Screenshot understanding for chats: Many support chats include screenshots of tracking pages, invoices, or error pop-ups. We’ll accept image uploads alongside text and extract structured facts (e.g., order status, error codes, invoice totals) to enrich the customer timeline. On Google Cloud, this uses Gemini’s multimodal image understanding (Vertex AI) to describe/parse screenshots, then maps findings into BigQuery features. PII redaction by default: Before we store anything, we’ll run Sensitive Data Protection (DLP) to detect and redact sensitive text inside images (e.g., addresses, card PANs) and text logs. This keeps demos and trials safe by default. Zero-click refresh: A Cloud Storage trigger will kick off parsing whenever a new chat export or screenshot lands—no manual button presses—so the dashboard always reflects the latest interactions. Google Cloud Documentation Multi-channel: Extend the connector pattern to Slack, email, and CRM notes; unify identity to a single customer profile in BigQuery. Ops actions: One-click creation of support tickets with suggested playbooks; capture outcomes to continuously improve prompts.

Built With

Share this project:

Updates