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.’
This will launch Google Workspace Marketplace. In the search bar at the top, search for “Coefficient” and select the Coefficient app.
Grant Coefficient access to your Google account by pressing ‘Allow.’
Then, return to your Google Sheets menu and click on Extensions -> Coefficient -> Launch.
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.
Scroll down the sidebar to select Snowflake as your data source.
Click ‘Create a custom SQL Query.’
Write your query and click the ‘Import’ button at the top right corner of the window.
You will be prompted to name your import. In this example, “Marketing Spend.” Click the ‘Import’ button once more.
Your live data will automatically populate in your Google Sheet like below.
We’ll repeat this process to import your Hubspot data.
Return to the Coefficient menu -> ‘Import from..’ -> ‘Hubspot.’ Click ‘Connect.’
Now click ‘Authorize’ to grant Coefficient permission to access your HubSpot account.
Click ‘From Objects & Fields.’
Select ‘Deals’ from the list of objects.
Click ‘Next’ and select your desired fields into the search bar: ‘Deal ID,’ ‘Amount,’ ‘Close Date,’ and ‘Original Source Type.’
Name your import and click ‘Import.’
Your deal data will automatically import to your spreadsheet in a separate 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.’
Select ‘Pivot Builder.’
Select the data range for the table.
Now describe the pivot you want the Pivot Builder to create: “Sum the cost for each channel.”
Click the ‘Build’ button.
Insert your pivot table into the existing sheet by selecting an empty cell.
Wait a few seconds and your pivot table will automatically populate your spreadsheet.
We’ll repeat this process for the deal data.
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.’
Insert your new pivot table in the Marketing Spend tab beside your first pivot table.
The output should like this:
Now you can calculate the customer acquisition costs.
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.’
Customer Acquisition Cost is calculated by cost divided by the number of deals made: “=F4/H4”
Apply the formula down the column.
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.’
Describe the Pivot you want to build from each data set: “Sum the cost per month.”
Insert the table in the existing sheet.
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.”
Your output will look like this.
Finally, you’ll make a combo table: “create a combo chart of cost and deals over time.”
Insert your chart into an empty cell in the existing sheet.
Your chart will appear in your sheet.
Finally, you’ll edit your chart to make it easier to read.
Click the top right corner -> ‘Edit chart.’
Select the Customize tab.
Click the ‘Series’ drop-down.
Choose ‘Deals’ in the ‘Apply to all series’ menu.
Scroll down and change the axis to ‘Right axis.’
Your output should look something like this.
Scroll back up to the ‘Apply to all series’ drop-down and select ‘Cost.’
Click ‘Format’ -> Type -> ‘Line’
And just like that, you have a clear visualization of 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!
