Inbound Calls Into the CRM: A Home Services Case Study
A Virginia landscaping company lost leads because call transcripts never reached JobNimbus. See how Make automations closed the gap over 3.5 years.
How a Multi-Office Senior Home Care Company Automated 42 Weekly CRM Reports into One Live Dashboard with Pipedream + BigQuery
How a multi-office senior home care company automated 42 weekly CRM reports into BigQuery and one live Looker Studio dashboard using Pipedream.
TL;DR: A growing senior home care company stopped hand-loading weekly CRM reports into spreadsheets. A hands-off Pipedream pipeline now pulls 42 office reports a week into Google BigQuery and feeds a single Looker Studio dashboard. It kept up as the company grew from 3 offices to 7, and it kept going as more offices opened.
The situation
The client is a multi-office senior home care company that provides in-home personal care, companionship, and transportation for older adults. When the work started, it had three offices. Within about six months it had seven, and it was opening more.
Each office runs on the same home care CRM (AxisCare), which holds the core of the business: client leads, intakes, caregiver applicants, billing, payroll, and care notes. The company's finance lead owned reporting. He was building a custom BI dashboard in Google Data Studio (now Looker Studio) so leadership could see leads, intakes, and new clients starting care, broken down by office and by week.
The problem: the data existed, but it was locked behind a login
The CRM could send scheduled reports, and it did — every week, one email per report, per office. But the emails didn't attach the file. They carried a private link, and that link only opened after you logged into the CRM. Each link was also specific to one office.
So every week, someone had to open each email, log in, download the Excel file, clean it up, and paste it into the spreadsheet behind the dashboard. At three offices and five report types, that was already 15 reports a week. Each new office added five or six more.
The finance lead put it plainly in the first working session: the hours he would save "from manually uploading data is just gonna be critical." Later, when the weekly run had to be ready for an 8 a.m. Wednesday review, he described the fallback: about 30 minutes of building the reports by hand, which he had no interest in repeating every week.
He also wanted a safety net. If the CRM ever changed a report's columns, he needed to know before bad data reached the dashboard, not after leadership had already seen the numbers.
What they'd already tried
The finance lead already knew Zapier. If the CRM had attached the CSV to the email, a simple Zap could have read it and pushed it into Google Sheets. It didn't, and no off-the-shelf trigger could get past the login screen to fetch the file.
He'd also looked at the CRM's API documentation, but didn't have the technical background to judge what it could actually do for an automated pull. As a Connex consultant told him in the first session, getting past that login is "one step beyond" what you can do even when you're proficient in Zapier.
What we built to automate the CRM reports
Over about 15 working sessions between September 2024 and March 2025, a Connex consultant and the finance lead built the system together, one layer at a time. The finance lead stayed hands-on throughout, checking the data against his own numbers and learning how the workflow schedules were set so he could adjust them himself.
1. Automated report retrieval. An email forwarding rule sends every scheduled CRM report email to a Pipedream workflow. The workflow reads the email, picks out the office's site number, signs in to the CRM with a dedicated login, downloads the Excel file behind the private link, and converts it to CSV. The first session proved this step works, so the hardest part of the project was solved first.
2. A central data warehouse. The spreadsheet approach would slow down as data grew, so the reports land in Google BigQuery instead. Each report type (client leads, caregiver applicants, billing, payroll, notes) gets its own table, and every row is tagged with the office it came from.
3. Data cleanup on the way in. The billing and payroll reports have a lot of columns and mixed data types. The workflow standardizes dates, numbers, and text, fixes column names BigQuery won't accept, and removes duplicate records by keeping the newest version of each one. That way a re-sent report never double-counts a client.
4. Guardrails. The workflow stores the expected column list for each report and checks every incoming file against it. If a column is added or removed, the known columns still load and an alert goes out, so a CRM change can't silently break the dashboard. A success or failure email confirms each run. The cleanup step is scheduled to finish before the 8 a.m. Wednesday review, with a simple check: all 42 reports in, deduplication done.
5. Phone data in the same place. Call and text activity from the company's business phone system (OpenPhone, since renamed Quo) flows into BigQuery in real time through webhooks. A lookup table maps each office's phone number to the office name, so the dashboard can show calls received per office per day next to leads and intakes.
6. One dashboard for every office. Looker Studio reads straight from BigQuery. Leadership can filter by office and date range and see leads, intakes, and clients starting care week by week.
7. Client satisfaction surveys on autopilot. Once the start-date data was flowing, the same pipeline powered a follow-up workflow. It texts each client a personalized survey link a set number of days after care begins, sent from their own office's phone number. Responses collect in a shared Google Sheet every office can see.
The results
- Report volume nearly tripled with no added manual work. The pipeline started at 15 reports a week (3 offices × 5 report types). By February 2025 it was handling 42 a week across 7 offices, and the company opened two more offices after that. New offices were added by extending the office mappings, not by rebuilding the pipeline.
- The weekly review stopped depending on a person. The data the Wednesday 8 a.m. review relies on is loaded, cleaned, and deduplicated before anyone logs in. The finance lead no longer spends the morning building reports by hand.
- Checked against the manual numbers. During the build, the finance lead compared the automated dashboard with his own hand-built tracking sheet for the same date range. Leads and intakes matched; the one remaining discrepancy, a single client record, was tracked down in the same session.
- Report changes get caught. CRM format changes now trigger an alert instead of quietly corrupting the dashboard.
- One source of truth beyond the CRM. CRM reports, phone activity, and survey responses now sit side by side in one warehouse, so office performance can be compared like for like.
The volumes, timings, and quotes above come from the client's own statements during working sessions. We haven't attached a dollar figure because the client never measured how long each report took to handle by hand.
Why the project kept growing
The finance lead came in wanting a dashboard, not a second job as a data engineer. Once the first automated download worked, he called the possibilities "really exciting." That's why the project kept going after the dashboard shipped: first phone data, then client surveys.
FAQ
Our CRM emails report links instead of attaching files. Can that still be automated?
Usually, yes. If a person can log in and download the file, a workflow can often do the same with a dedicated login. In this project, that step was proven in the first session, before anything else was built.
Do we need a data warehouse, or is Google Sheets enough?
Sheets is fine for a few reports. Once you're loading dozens of reports a week across several locations, a warehouse like BigQuery stays fast and keeps history clean. Looker Studio can then read from it directly. This project started on Sheets and moved to BigQuery as the volume became clear.
What happens when the CRM changes a report?
The workflow compares every file against the expected columns. Known columns still load, and you get an alert about anything added or missing, so you fix it on your schedule instead of finding out from a wrong number.
What access did this need?
An email forwarding rule, a CRM login for the workflow, a Google Cloud project with a service account for BigQuery, and webhook access to the phone system. The data lives in the client's own Google Cloud project, and the CRM stayed exactly as it was.
Stuck with data you can see but can't automate?
If your reporting still depends on someone downloading files every week, we can help you map the fastest path to a dashboard that updates itself. In a ZoomFlow session, a Connex consultant builds the hardest step with you live, the same way this project started. Book a call with Connex.
Related Posts
Automate monthly Salesforce exports from S3 + Google Sheets
Automate monthly Salesforce exports from S3 + Google Sheets: pull daily files from S3, calculate member status in Sheets, and generate a weekly CSV in Drive.
Automate OneDrive Excel loads to SQL Server with Power Automate
A Power Automate checklist for ingesting quarterly Excel files from OneDrive into SQL Server, with validation, upserts, run logging, and safe reruns.
