)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Calculating CAC requires sophisticated formulas that can handle data from both QuickBooks marketing expenses and QuickBooks customer acquisition records. The challenge isn’t just division – it’s matching time periods, attributing spend correctly, and handling different data structures.
Here are the essential formulas that give you accurate CAC calculations across different scenarios and attribution models.
Master these CAC formulas using Coefficient
Coefficient enables sophisticated CAC formulas by seamlessly combining QuickBooks financial data with HubSpot customer metrics. You can create dynamic calculations that automatically adjust time periods, handle attribution windows, and segment by marketing channels.
How to make it work
Step 1. Build your basic CAC formula.
Start with: =SUMIFS(QuickBooks_Expenses[Amount], QuickBooks_Expenses[Category], “Marketing”, QuickBooks_Expenses[Date], “>=”&StartDate, QuickBooks_Expenses[Date], “<="&EndDate) / COUNTIFS(HubSpot_Contacts[CreateDate], ">=”&StartDate, HubSpot_Contacts[CreateDate], “<="&EndDate). This formula sums marketing expenses within a date range and divides by the count of new customers in the same period.
Step 2. Create channel-specific CAC calculations.
Use: =SUMIFS(QB_Expenses[Amount], QB_Expenses[Account], “Google Ads”) / COUNTIFS(HubSpot_Deals[Source], “Google Ads”, HubSpot_Deals[CloseDate], “>=”&MonthStart). This matches specific QuickBooks expense accounts with corresponding HubSpot lead sources for accurate channel attribution.
Step 3. Add attribution window formulas.
Build rolling attribution with: =SUMIFS(QB_Marketing[Amount], QB_Marketing[Date], “>=”&(TODAY()-30)) / COUNTIFS(HubSpot_Customers[AcquisitionDate], “>=”&(TODAY()-30), HubSpot_Customers[Status], “Customer”). This creates a 30-day rolling window that automatically adjusts as time passes.
Step 4. Implement cohort-based analysis.
Use SUMPRODUCT for cohort CAC: =SUMPRODUCT((QB_Expenses[Month]=”Jan2024″)*(QB_Expenses[Category]=”Digital Marketing”)*QB_Expenses[Amount]) / SUMPRODUCT((HubSpot_Contacts[AcquisitionMonth]=”Jan2024″)*1). This calculates CAC for specific customer cohorts acquired in particular time periods.
Step 5. Create multi-touch attribution formulas.
Weight different channels: =(SUMIFS(QB_Spend[Amount], QB_Spend[Channel], “Paid Search”)*0.4 + SUMIFS(QB_Spend[Amount], QB_Spend[Channel], “Content”)*0.6) / COUNT(HubSpot_NewCustomers[ID]). This distributes marketing spend across multiple touchpoints based on your attribution model.
Calculate CAC with precision and automation
These formulas transform disconnected data into actionable CAC insights that update automatically. You’ll get attribution accuracy that’s impossible with QuickBooks reporting alone. Start building your automated CAC formulas today.
