A law firm was running ads on Google and Bing, taking leads via phone, contact form, and live chat — and had absolutely no way to see all of it together, let alone trace any of it back to ad spend. We built a lightweight, low-cost hub in Google Sheets that consolidated every source, let the client manage his own data, and fed real revenue back to both ad platforms every morning at 5am.
3 Sources
CallRail, Gravity Forms & live chat in one view
1 Sheet
Single place to review, delete, and add revenue
5am Daily
Automated offline conversion upload to Google & Bing
The Problem
The client ran a law firm and advertised on both Google and Bing. Enquiries arrived through three channels: inbound phone calls tracked through CallRail, enquiry forms built in Gravity Forms on the WordPress site, and live chat conversations handled through Client Chat Live.
Each of these systems had its own interface and its own data — and none of them talked to each other. If the client wanted to know how many enquiries he'd received this week, he had to log into three separate tools and add up the numbers manually. If he wanted to know which ad campaigns were driving which type of lead, that answer simply didn't exist.
What the client really wanted was simple: one place to see every lead that came in, the ability to delete the ones that weren't relevant (spam, wrong-number calls, tyre-kickers), and a way to add the revenue value when a case was signed so he could see which channels were actually worth the money.
He didn't want to pay for a CRM or a proper analytics platform. He wanted something lean, something he could understand and manage himself, and something that would give both ad platforms the data they needed to optimise toward real results.
"The leads were coming in. He just couldn't see them all in one place — and he definitely couldn't tell which ones came from which ad platform, let alone which keyword."
The Wrinkle
CallRail has a well-documented API — pulling call records with timestamps, caller details, and the tracking number that received the call is straightforward. Gravity Forms supports webhooks natively, meaning it can fire the form submission data directly to any endpoint the moment someone hits Submit. Both of those were clean, direct connections.
Client Chat Live was a different story. There was no API to speak of. No webhook configuration. No native integration pathway that would let us pull chat transcripts or lead data into an external system automatically.
The only way to get the data out was to log in, navigate to the chat history, apply a date filter, and download the export — exactly as a human would do it. So that's what we automated.
This is a common situation with live chat tools
Many lower-cost live chat providers prioritise the chat experience over data portability. If getting your conversation data out of your chat tool is important for your business, check what export or API options exist before committing to a platform. The absence of an API doesn't have to be a dead end — but it does add complexity.
The Architecture
The core idea was to make Google Sheets the single source of truth — simple enough for the client to use without training, and structured enough to feed the offline conversion upload that both ad platforms needed.
CallRail API
New call record → append row to sheet
Gravity Forms Webhook
Form submit → fires directly to sheet
Headless Browser — Nightly
Logs into Client Chat Live, downloads last 24h chats
Google Sheets — Unified Lead Hub
All leads in one view: source, date, name, contact, GCLID / MSCLKID, and a revenue column the client fills in when a case is won
Data clean & filter
Normalise fields, deduplicate, filter to rows with a revenue value and a click ID — these are the upload-ready conversions
Google Ads upload — 5am
Offline conversion import — GCLID + revenue value
Bing Ads upload — 5am
Offline conversion import — MSCLKID + revenue value
CallRail's API lets you retrieve call records in real time. We set up an integration that fires each time a new call is logged — capturing the caller's number, call duration, the tracking number that received it (which maps back to the ad campaign or source), and the Google or Bing click ID that was stored on the session when the caller first landed on the site. Each call becomes a new row in the sheet.
Gravity Forms has native webhook support — no middleware needed. We configured each relevant form to fire a POST request directly to a Google Apps Script web app endpoint the moment a submission comes in. The script parses the payload and appends the row. The form had hidden fields capturing the GCLID and MSCLKID from the URL on landing, so those values travelled with the submission automatically.
With no API and no webhook support from Client Chat Live, we built a headless browser script that ran on a 24-hour schedule. Every night it launched a headless browser instance, logged into the Client Chat Live dashboard using the client's credentials, navigated to the chat history section, applied a date filter to the previous 24 hours, and downloaded the export file.
The downloaded data was then cleaned — stripping system messages, normalising the name and contact fields into the same column format used by the other two sources — and appended to the sheet. From the client's perspective, chat leads just appeared in the sheet overnight alongside the calls and form submissions.
The Google Sheet was designed around what the client needed to do, not what was technically possible. Every row represented one lead — the date, the source (call/form/chat), the contact's name and number or email, and the click ID columns. There was one extra column: Revenue. That column was intentionally left for the client to fill in.
When someone became a client and their case was valued, he opened the sheet, found the row, and typed in the number. If a lead was clearly irrelevant — a wrong number, spam, a call that lasted three seconds — he could just delete the row. No dashboards, no admin panel, no training required.
Each night at 5am, an automated script scanned the sheet for rows that had both a revenue value entered and a click ID present. Those rows were the upload candidates. The script formatted them as offline conversion records — each containing the GCLID or MSCLKID, the conversion action name, the timestamp of the original lead, and the revenue value — and uploaded them to Google Ads and Bing Ads respectively.
Both platforms then had real case revenue attributed back to the individual click that generated the enquiry. For the first time, ROAS in the ad platforms reflected what the advertising was actually producing, not just how many people had filled out a form.
The Result
Before this system existed, the client had three tools, no shared data, and no way to connect ad spend to case revenue. Every budget decision was instinct. After the build, every lead that came in — regardless of which channel it arrived through — landed in one place he could actually look at.
The revenue data flowing back into Google Ads and Bing meant that both platforms were now optimising toward case value rather than raw enquiry volume. Campaigns that were generating a lot of calls but few cases got less budget. Campaigns generating fewer but higher-value leads got more. The bidding algorithms finally had the signal they needed.
The client goes into the sheet himself when a case is confirmed and adds the value. It takes him less than a minute. The upload handles the rest automatically overnight. He's already talking about automating the revenue entry step as his case management workflow matures — but for now, the manual entry suits him and he prefers to keep that control.
The same setup can be adapted for any service business with multiple enquiry channels and a need to close the loop between ad spend and real revenue — without the cost or complexity of a full CRM or analytics platform.
All Sources
Calls, forms, and chat in one view — no platform-hopping
Zero Training
Client managed it from day one — it's just a spreadsheet
Real ROAS
Revenue attributed to the click, not just the lead
If your leads are scattered across a call tracker, a form tool, and a live chat platform — and none of them talk to your ad platforms — this kind of build is almost always simpler and cheaper than you'd expect. Get in touch and tell us what your current setup looks like.
Written by
Conversion tracking specialist and marketing automation consultant with 10+ years connecting ad platforms to real revenue data for service businesses across the US, UK, and EU. 200+ projects delivered. Founder of Extra Large Marketing Digital, based in Rio de Janeiro.