Customer Acquisition Cost Analysis with Multiple Data Sources

Last Modified: September 6, 2023 - 5 min read

Julian Alvarado
learn how to perform a CAC from multiple sources in this guide

Understanding your Customer Acquisition Cost (CAC) is not only best practice, it’s also a game-changer for any business. It provides insights into your marketing efforts’ efficiency and customer relationships’ profitability so you know where to invest.

However, accurately calculating CAC can be complex, especially when the data you need resides in multiple systems, native reporting limitations arise in SaaS systems, manual spreadsheet refreshes are embedded into your workflow, or advanced spreadsheet skills are required.

In this blog, we’ll provide a step-by-step guide on how to perform a customer acquisition cost analysis using multiple data sources.

You can pull data from any of your source systems – but in this specific example, we’ll pull data from Snowflake and HubSpot, demonstrating how to calculate CAC and visualize costs and deals over time  – all within the familiar environment of your spreadsheet.

Walkthrough: How to Calculate Customer Acquisition Cost in Google Sheets

Before we dive into the analysis, the first step is to install Coefficient, a free Google Sheets add-on that seamlessly connects your favorite business solutions to your spreadsheet.

Open Google Sheets. Click Extensions on the menu bar and select ‘Add-ons’ -> ‘Get add-ons.’

before you start your CAC analysis, install coefficient

This will launch Google Workspace Marketplace. In the search bar at the top, search for “Coefficient” and select the Coefficient app.

search for the coefficient data connector in the google workspace marketplace

Grant Coefficient access to your Google account by pressing ‘Allow.’

grant coefficient permission to access your google account

Then, return to your Google Sheets menu and click on Extensions -> Coefficient -> Launch.

launch coefficeint through extensions in the top nav

Coefficient will appear on the sidebar of your Google Sheet. Now you can import your live data into Google Sheets.

We’ll start with your Snowflake Data, to import your marketing cost by channel data.

Click ‘Import from…’ on the Coefficient sidebar menu.

import your live data google sheets

Scroll down the sidebar to select Snowflake as your data source.

select snowflake as your data source

Click ‘Create a custom SQL Query.’

click create a custom SQL query

Write your query and click the ‘Import’ button at the top right corner of the window.

write your query

You will be prompted to name your import. In this example, “Marketing Spend.” Click the ‘Import’ button once more.

name your query before importing

Your live data will automatically populate in your Google Sheet like below.

your live data will automatically populate your spreadsheet

We’ll repeat this process to import your Hubspot data.

Return to the Coefficient menu -> ‘Import from..’ -> ‘Hubspot.’ Click ‘Connect.’

import data from hubspot

Now click ‘Authorize’ to grant Coefficient permission to access your HubSpot account.  

grant coefficient permission to access your hubspot account

Click ‘From Objects & Fields.’

select which objects and fields you want to import

Select ‘Deals’ from the list of objects.

select deals as your object

Click ‘Next’ and select your desired fields into the search bar: ‘Deal ID,’ ‘Amount,’ ‘Close Date,’ and ‘Original Source Type.’

select deal ID, amount, close data and original source type as fields

Name your import and click ‘Import.’

name your import and click import

Your deal data will automatically import to your spreadsheet in a separate tab.

hubspot data will now populate your spreadsheet in a new tab

Next, we’ll walk through calculating CAC using two pivot tables: one to sum the cost for each channel, and another to count the number of deals from each channel source.

Return to the Coefficient menus and click ‘GPT Copilot.’

return to the main menu and choose gpt copilot

Select ‘Pivot Builder.’

select pivot builder from the menu

Select the data range for the table.

choose the data range for analysis

Now describe the pivot you want the Pivot Builder to create: “Sum the cost for each channel.”

Click the ‘Build’ button.

describe the pivot you want to build

Insert your pivot table into the existing sheet by selecting an empty cell.

import the pivot into your existing sheet

Wait a few seconds and your pivot table will automatically populate your spreadsheet.

now we'll create a new pivot table

We’ll repeat this process for the deal data.

select the data range for analysis once again

After selecting the data range, describe the formula you want to build: “count the number of deals for each original source type where the close date is after January 1, 2023.”

Click ‘Build.’

describe the next pivot as before

Insert your new pivot table in the Marketing Spend tab beside your first pivot table.

insert this pivot table besides the first

The output should like this:

you should now have two pivots next to one another

Now you can calculate the customer acquisition costs.

Coefficient Excel Google Sheets Connectors
314,000 Pros Sync Live Data from Their Business Systems into Spreadsheet

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

Create a new column next to your two pivot tables and label it ‘Customer Acquisition Cost.’

calculate CAC by dividing the cost by the number of deals made

Customer Acquisition Cost is calculated by cost divided by the number of deals made: “=F4/H4”

apply the previous formula to each cell

Apply the formula down the column.

create a third pivot table

Finally, we visualize costs and deals over time with a combo chart, providing a clear visual representation of your CAC over time.

Return to the GPT Copilot menu and select ‘Pivot Builder.’ 

select pivot builder from the menu

Describe the Pivot you want to build from each data set: “Sum the cost per month.”

describe the pivot to build

Insert the table in the existing sheet.

insert the new pivot into an empty cell below the other two

Return to the pivot builder and describe the last pivot table, this time using your deal data: “count the number of deals per month close data starting in January 1, 2023.”

build your fourth pivot table next to the third

Your output will look like this.

create a combo chart

Finally, you’ll make a combo table: “create a combo chart of cost and deals over time.”

describe the chart you wish to build

Insert your chart into an empty cell in the existing sheet.

insert the chart into the existing spreadsheet

Your chart will appear in your sheet.

the chart will automatically populate your spreadsheet

Finally, you’ll edit your chart to make it easier to read.

Click the top right corner -> ‘Edit chart.’

edit the chart to make it easier to interpret

Select the Customize tab.

select the customize tab

Click the ‘Series’ drop-down.

open the series dropdown

Choose ‘Deals’ in the ‘Apply to all series’ menu.

select deals in the drop down

Scroll down and change the axis to ‘Right axis.’

change the axis to right axis

Your output should look something like this.

deals will now appear as a line

Scroll back up to the ‘Apply to all series’ drop-down and select ‘Cost.’

now select cost

Click ‘Format’ -> Type -> ‘Line’

change the format to line

And just like that, you have a clear visualization of costs and deals over time!

you will now be able to see costs and deals over time

Simplify CAC Analysis from Multiple Sources With Coefficient

Coefficient makes it easy for marketers to get a comprehensive view of customer acquisition costs by easily integrating their data directly into Google Sheets.

Install Coefficient for free and start optimizing your spend today!

Set Spreadsheet Data on Refresh

Try the Spreadsheet Automation Tool Over 300,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 300,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Julian Alvarado Content Marketing
Julian is a dynamic B2B marketer with 8+ years of experience creating full-funnel marketing journeys, leveraging an analytical background in biological sciences to examine customer needs.
Google icon
300,000+ users on Google Marketplace
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies