Call Cost Tracking – Foundations for a Client Dashboard – Step-by-Step Guide shows you how to automate call cost tracking using Make.com, Excel, and the VAPI API so you can build reliable client reporting without manual work. Henryk Brzozowski walks through the process on video and adds a fun cat cameo that traveled from Australia.
You’ll follow a clear sequence: set up the Excel sheet, configure Make.com triggers, perform API calls and iterate through data, and finalize the Google Sheet integration, with timestamps to skip to each section. This compact walkthrough is aimed at business owners and automation newcomers who want practical, ready-to-use steps for call cost analysis.
Project overview and goals
This project helps you build reliable call cost tracking for client dashboards by combining Make.com automation, a spreadsheet (Google Sheets or Excel), and the VAPI API. The goal is to produce accurate, auditable per-call cost data that you can present to clients, bill from, or analyze for cost optimization.
Define the primary objective of call cost tracking for client dashboards
Your primary objective is to capture raw call events, enrich them with pricing rules, calculate costs consistently, and surface those costs in a client-facing dashboard. You want to turn call logs into clear unit costs, aggregated summaries, and invoice-ready line items while maintaining traceability back to raw records.
Identify business questions the dashboard must answer
You should be able to answer questions like: What did each call cost and why? Which campaigns, users, or endpoints generate the highest costs? Are rates and surcharges applied correctly? How do costs trend daily, weekly, or by client? Which calls remain unbilled or need manual review?
Determine scope, success criteria, and constraints
Define scope by data sources (VAPI + internal logs), timeframe, and which clients or numbers are included. Success criteria include accuracy within an agreed tolerance, automated daily ingestion, and dashboard refresh times. Constraints might be API rate limits, spreadsheet row capacities, or privacy/regulatory rules for call data.
List stakeholders and their information needs
Stakeholders typically include finance (cost reconciliation, invoicing), operations (call routing and quality), account managers (client billing queries), and engineering (data integrity). Each will need specific views: finance wants invoice-ready line items, ops needs call-level drilldowns, and account managers want summarized client dashboards.
Clarify expected delivery timeline and milestones
Set milestones such as: design and schema finalized (week 1), spreadsheet and API integration prototype (week 2), Make.com automation and end-to-end tests (week 3), pilot with one client (week 4), stakeholder review and iteration (week 5). Build in buffer for API adjustments and user feedback.
Prerequisites and tools
You need the right accounts, credentials, and environment before building the system. This section lists the required access, software, and skills so you can avoid last-minute blockers and maintain a reproducible setup.
Accounts and access: Make.com, Google/Excel, VAPI API, email and client permissions
Ensure you have admin access to Make.com (or team account), edit rights to the client’s Google Sheet or Excel file, and VAPI API access for call records. Confirm email addresses for notifications and that clients consent to data sharing. If you work across multiple clients, set up separate workspaces or folders.
Required API keys, OAuth credentials, and service accounts
Collect API keys and OAuth credentials ahead of time: VAPI API key or token, Google Service Account for Sheets or OAuth credentials for your user, and any Excel Online OAuth details if using Microsoft 365. Store secrets securely in Make.com or a secrets manager and avoid embedding them directly in spreadsheets.
Software and environment: browsers, spreadsheet versions, developer tools
Work from modern browsers and ensure Excel is a version that supports online connectors if you choose it. Have Google Sheets enabled if you use that route. Keep developer tools (network inspector, JSON viewers) next to you for debugging requests and inspecting payloads during development.
Basic skills: spreadsheets, REST APIs, JSON, automation logic
You should be comfortable with spreadsheet formulas, parsing timestamps, and creating pivot tables. You’ll also need basic REST API knowledge, JSON parsing, and familiarity with automation concepts (triggers, iterators, error handling) to design robust workflows.
Recommended optional tools: external database, logging service, Postman
Consider optional tools like a small external database if row limits or concurrency are concerns, a logging service to capture events and errors, and Postman for testing API endpoints and building payload examples before automating them in Make.com.
Data modeling and schema
A clear data model reduces ambiguity and simplifies mapping from raw APIs into your spreadsheet. Design entities and fields to support calculations, deduplication, and future extensions.
Define core entities: call records, endpoints, rates, invoices
Your model should include call records (raw events), endpoints (numbers, gateways), rates (pricing rules per destination), and invoices (aggregated billed items). Relate calls to endpoints and rates, and link invoices to calls to build audit trails and reconciliations.
Essential fields for call records: timestamp, source, destination, duration, call ID
At minimum capture timestamp (UTC), source number, destination number, duration in seconds, and a unique call ID. These fields form the backbone for deduplication, cost calculation, and time-based aggregation.
Rate and pricing model fields: per-minute rate, billing increment, currency
Store per-minute or per-second rate, billing increment (e.g., per-second, per-30s block, per-minute), currency, and any effective date ranges. This allows back-dated pricing and correct cost application when rates change over time.
Metadata and enrichment fields: call type, tags, campaign, user ID
Enrich call records with call type (inbound/outbound), tags (campaign or project), user ID or agent, and any custom labels. These metadata fields enable filtering, reporting by campaign, and attribution for cost allocation.
Design for extensibility: optional fields and versioning strategy
Plan optional fields like codec, call quality metrics, or recording links. Add a versioning strategy for schema changes—include a schema_version field and maintain migration documentation so you can add fields without breaking existing automations or dashboards.
Excel and Google Sheet setup
Choose the spreadsheet platform that best fits your collaboration and automation needs, then design a clean layout that separates raw data from calculations and display logic.
Choose between Google Sheets and Excel based on sharing and automation needs
If you need real-time collaboration and easy API integration, Google Sheets is often simpler. If you require enterprise Excel features or offline work with Microsoft 365 connectors, Excel may be preferable. Decide based on client preferences, security policies, and connector availability.
Design sheet layout: tabs for raw data, rates, calculations, and dashboard
Create separate tabs for raw_data, rates, calculations, invoices, and dashboard. Keep raw_data immutable except for append operations. Calculations can reference raw_data and rates, while the dashboard tab contains summarized views and charts.
Standardize columns and headers to match API fields
Match spreadsheet headers to API field names where practical (timestamp, call_id, source, destination, duration_seconds, rate_id). Standardized names make mapping simpler in Make.com and reduce transformation errors.
Implement formulas and helper columns for duration parsing and cost formulas
Add helper columns to parse timestamps into date parts, convert duration strings into seconds, and compute billed units according to increments. Use formulas to compute preliminary cost per call and flag anomalies such as negative durations or missing fields.
Add data validation, drop-downs, and protections to prevent accidental edits
Use data validation on columns like rate_id and call_type to enforce allowed values. Protect the raw_data tab or lock important formula cells to avoid accidental changes. This reduces noise and accidental breaks during automation.
Make.com workflow design
Design a clear Make.com scenario that handles triggers, retrieves data, transforms it, and writes it to your spreadsheet while managing errors and retries.
Map out the scenario: trigger, data retrieval, transformation, write-back
Start by mapping the flow: what triggers ingestion (schedule or webhook), how you retrieve call records from VAPI, what transformations are needed (timestamp normalization, rate lookup), and which modules handle the write-back to the sheet and notifications.
Select appropriate trigger: webhook, schedule, or spreadsheet change
Use a webhook for near-real-time updates, a scheduled trigger for periodic batch ingestion, or a spreadsheet change trigger if you prefer event-driven recalculation. Choose based on volume, latency requirements, and API quotas.
Plan modules: HTTP requests, iterators, aggregators, Google/Excel connectors
Include an HTTP module to call VAPI, an iterator to process lists of calls, aggregators for grouping, and the Google Sheets or Excel modules to append or update rows. Add an error handler, and optionally an email or Slack module for alerts.
Define branching for success/failure and conditional paths
Create branches for success and failure: on success, update records and mark processed; on failure, log the error and send a notification. Implement conditional filters to skip malformed records or to route specific calls for manual review.
Document expected inputs/outputs for each module to ease debugging
For each module, document the expected input fields and output structure. Keep sample payloads and map fields explicitly. This documentation helps you debug runs quickly and onboard others to the scenario.
VAPI API integration
Understand the VAPI endpoints, authentication, and payloads you’ll use so your calls are efficient and resilient.
Overview of VAPI endpoints relevant to call data and cost estimation
Identify endpoints that return call logs, call details by ID, and any endpoints that provide pricing or rate lookup. You may also have endpoints for account metadata or usage summaries which can supplement call-level data.
Authentication method and token lifecycle management
Confirm whether VAPI uses API keys, bearer tokens, or OAuth. Implement token refresh if required, and store tokens securely. In Make.com, use the connection settings or a secure variable to avoid exposing credentials in scenario steps.
Understand request and response payload structures and example calls
Examine example requests and responses—note field names, nested structures, and arrays of call objects. Knowing where timestamps and IDs live and how durations are represented prevents mapping errors and miscalculations.
Be aware of API rate limits, quotas, and backoff recommendations
Check VAPI rate limits and design batching and throttling accordingly. Use Make.com sleep or bundle strategies to avoid rate limit errors and implement exponential backoff and retry policies when you face transient 429 or 5xx responses.
Plan for versioning and handling API changes or deprecations
Track VAPI versioning and subscribe to change notifications if available. Architect your integration so endpoint URLs and field mappings are centralized, making updates straightforward when VAPI changes field names or deprecates endpoints.
API call implementation details
Implement calls carefully to ensure completeness, reliability, and efficient retrieval of large datasets.
Constructing requests: headers, query parameters, body payloads
Build requests with proper headers (Authorization, Content-Type) and include query parameters to filter by date range, client IDs, or pagination cursors. Use body payloads for POST queries when supported to pass complex filters.
Handling pagination and retrieving full datasets
If the API returns paginated results, implement pagination loops: use cursors or page numbers until no more records are returned. Ensure you combine results in the correct order and detect when pagination fails or becomes inconsistent.
Batching strategies to reduce calls and respect rate limits
Batch requests by time window or client to reduce frequency. For example, pull hourly or daily batches rather than single-record requests. Use Make.com aggregators to assemble batches prior to downstream processing to minimize connector operations.
Implementing retries and exponential backoff for transient failures
On transient errors, retry with exponential backoff and jitter. Limit retry attempts to avoid infinite loops and log retries for auditability. Differentiate transient errors (network, 5xx) from permanent issues (4xx client errors) to avoid wasteful retries.
Parsing JSON responses and mapping to spreadsheet fields
Parse JSON carefully, handle optional fields, and map nested data to flat spreadsheet columns. Normalize timestamps to UTC and convert durations to seconds in a helper field. Validate that required fields exist before writing to the spreadsheet.
Iterating through data and mapping records
Process multiple call records efficiently while preventing duplicates and maintaining idempotency.
Use iterators to process multiple call records sequentially or in parallel
Use Make.com iterators to loop through each call object in a batch. For large volumes, consider parallel processing with controlled concurrency, but watch for API and spreadsheet write limits and avoid race conditions on the same rows.
Map API fields to spreadsheet columns reliably and consistently
Create a clear mapping table between API field names and spreadsheet headers. Include transformations like timestamp conversion, duration parsing, and tag extraction. Apply the same mapping logic for every run to maintain consistent historical data.
Implement deduplication checks using unique call IDs or timestamps
Before appending rows, check whether a call_id already exists. Use the unique call ID as a primary deduplication key, or combine timestamp+source+destination as a fallback. Mark duplicates and skip or update existing rows depending on your design.
Ensure idempotency so re-runs don’t duplicate or corrupt data
Design each operation to be idempotent: use update-if-exists logic, or write processing flags and last_processed timestamps. This prevents duplication when scenarios are retried and makes the pipeline safe to run repeatedly.
Track processing state and update rows with status flags or timestamps
Add status fields like processed_at, status (success, failed, pending), and error_message to each record. This gives you an operational view of what was handled, what failed, and what needs manual attention.
Cost calculation logic
Implement precise billing logic that matches your contracts and billing rules so costs can be confidently presented to clients and finance.
Define billing units: per-second, per-30s block, per-minute
Decide if billing is per-second, per-30-second block, or per-minute. This choice affects rounding, cost accuracy, and alignment with supplier invoices. Store billing_unit in your rate table so calculations are rule-driven, not hard-coded.
Implement rounding and billing increment rules in formulas or code
Apply rounding rules consistently—e.g., always round up to the nearest billing increment. Implement these rules in spreadsheet formulas or inside Make.com transformations to ensure the billed duration matches the rate policy.
Account for different rate types: peak/off-peak, inbound/outbound
Support rate variations such as peak vs off-peak and inbound vs outbound by storing conditional rules. When calculating cost, select the appropriate rate based on timestamp, call direction, or other attributes and apply any applicable multipliers.
Apply discounts, taxes, surcharges, and currency conversion
Include logic for discounts or client-specific adjustments, taxes, surcharges, and currency conversion. Maintain a clear precedence for calculator steps (base rate → surcharge → tax → discount) and keep fields for each component to aid auditability.
Validate calculations against sample invoices and manual checks
Test your calculation engine by comparing outputs to known invoices and running spot checks. Use sample datasets and edge cases (zero duration, very long calls) to confirm rounding and surcharges behave as expected and adjust formulas or code when discrepancies appear.
Conclusion
Wrap up the project with operational controls, security considerations, and a plan for continuous improvement so your call cost tracking remains accurate and useful over time.
Recap foundational steps to build reliable call cost tracking
You should now have a clear process: define objectives, prepare credentials and tools, design a data model, set up a spreadsheet, build a Make.com scenario that calls VAPI, parse and deduplicate records, calculate costs using robust rules, and surface results in dashboards ready for stakeholders.
Highlight key operational and security considerations to sustain system
Operationally, monitor for API failures, ensure idempotent processing, and implement alerting for errors. From a security standpoint, secure API keys, limit spreadsheet access, use service accounts where possible, and redact or protect sensitive PII contained in call data.
Next steps: pilot deployment, stakeholder review, and iteration
Start with a pilot for a small client or a limited timeframe to validate end-to-end behavior. Present results to stakeholders, gather feedback on views and SLA expectations, then iterate on schema, performance, and UI elements before scaling.
Resources for further learning and reference links
Review documentation for Make.com, your spreadsheet platform, and the VAPI API to deepen your integration knowledge. Practice using Postman or a similar tool to prototype API queries and maintain a small internal runbook that documents your mappings, formulas, and failure modes.
How to contact the maintainer or seek professional help
If you need help maintaining or extending your setup, prepare a concise handover with credentials, design docs, and sample runs so a consultant or team member can pick up work quickly. Keep an email or contact point within your organization for escalation and schedule periodic reviews to ensure the system remains aligned with business needs.
If you want to implement Chat and Voice Agents into your business to reduce missed calls, book more appointments, save time, and make more revenue, book a discovery call here: https://brand.eliteaienterprises.com/widget/bookings/elite-ai-30-min-demo-call