Calculate the Customer Acquisition Cost (CAC) for Shopify
Knowing the cost to acquire a single customer (Customer Acquisition Cost or CAC) is important for any business but especially e-commerce sites using Pay Per Click (PPC) advertising. If you run a Shopify store and use Google Ads or Facebook Ads, this tutorial will show you how to calculate an overall CAC and easily create a simple dashboard for sharing using Lido, the leading low-code platform based on spreadsheets that lets you build powerful internal applications and dashboards without needing developers.
In this tutorial we’ll show you how to:
Connect your Shopify order data
Connect your Google Ads data
Connect your Facebook Ads data
Create a combined table of daily orders, ad spend, and a blended customer acquisition cost
Create a simple visual dashboard for sharing
Step 1: Connect Shopify Order data
If you don’t already have an account, first set up a free Lido account.
Goto www.lido.app and click on “Try for Free” (Lido is currently completely free to use )
Either sign up with your Google account by clicking “Sign up with Google”, or create an account with a username and password.
Click on Create New Account
It will bring you to your new Lido account where you will see a “Starter File”. Click on the “Starter File” and it will bring you into a new sheet.
Opening the Starter File
Next, connect to your Shopify order data as a datasource in Lido:
Click on the blue "Data" button on the top left of the formatting bar. This will open the Data sidebar. Scroll down and click on “Shopify”
Then, click on “orders” and you’ll be asked to connect your Shopify store. Enter the first part of your Shopify store URL in the box and click Connect. For example, enter “lido-test-store” into the box. And the full URL will read: “lido-test-store.shopify.com”
Log into Shopify with your Shopify username and password. When you’ve successfully logged in, you’ll see the “Dataset Editor”.
From the left side choose the date range you want to include from Shopify Orders.
Then, from the menu of available fields, choose “Day”,”Order Number” and “Gross Sales”. You should see a preview of the records you have selected.
When all three columns show correctly in the preview pane, click on “Save dataset and Create View”
We'll find ourselves back in the sheets view looking at our new live-connected Shopify data table! Our new Sheet is named “View~ShopifyOrders1~1”. Don’t change the name. (you can collapse the sidebar now by clicking on the “x”).
Lets take a closer look. MAKETABLE creates a named table that can be manipulated, attached to dashboard components, filtered and more. Because MAKETABLE can be connected to a datasource such as Shopify and records will update in realtime, it's not possible to rely on static cell ranges (such as A1:C50) to make sure you have all of the data. With MAKETABLE, all you need to do is reference the name of the table.
What the function above says is "Make a table out of the Shopify datasource I have called "ShopifyOrders1" and we'll name this table "View~ShopifyOrders1~1", and just use the same column names as the datasource has already.
The function documentation is:
We'll run into MAKETABLE again later, but for now lets move on!
Step 2: Group Shopify orders by day
For the simple blended CAC we’re calculating, we’ll use the # of orders a day as our measurement. Blended means that we'll simply add our Google Ads and Facebook spend together and divide the sum by the number or Shopify orders for that day.
To do that, we need to group our Shopify by day and have a count of the # of orders.
Another one of Lido's powerful extensions to the standard set of Excel formulas is GROUPBY. Let's use the visual helper to use this formula.
Select any cell inside the table of Shopify orders on your new tab.
Click on the "Group" button on the toolbar (it only appears when you click inside of a table). The “Group” dialog will open.
Make the following choices:
Click “Update”. You should now see a summary table of the number of orders per day in place of the original table.
Quick fix: You’ll notice the dates have been changed to the underlying numbers that spreadsheets store dates as (# of days since 1900), and they no longer look like dates. This is a current bug being worked on, with a quick fix:Select the left column of dates (that display as numbers), starting at one cell below where the table ends. Once they’re selected, use the format drop-down in the toolbar to change it to a “Date” format.
Our Shopify orders are now grouped by day with a count of the orders.
Step 3: Sort Orders by Day Ascending
The orders are sorted from most recent to oldest. We want to reverse that order so that our chart will look correct later on. Because of the GROUPBY function, we need to do a little extra work to sort it properly. We're working on an update to Lido to make this process more simple, but for now, do the following:
1. Strip the header columns off of the table.
We'll return to our MAKETABLE function and use another Lido function, SLICE, to remove the header row. Looking again at cell A1, the current MAKETABLE function looks like:
The SORT formula says "Sort whatever I'm given by the 1st column in ascending order".
3. Finally, add back the columns at the MAKETABLE level. With MAKETABLE, the last parameter is an optional list of column names. You'll also have to select and format the date cells as in step 1 above.
Now, you have a sorted and grouped table of Shopify orders that is updated in realtime.
Step 4: Connect your Google Ads data
Next, we need to connect our Google Ads data to understand how much we spent to get those Shopify orders:
Click on the "Data" button on the left of the formatting toolbar and the Data sidebar will open.
Scroll down and click on “Google” and then choose “Ads”. Click on “Connect”
Go through the login flow, and you’ll end up in the Dataset Editor again.
Choose the date range of Google Ads data that you’d like to match up with Shopify sales. The Google Ads data should be the same date range as you earlier selected for Shopify.
Scroll down and choose the fields “Campaign Name”, “Segments Date”, and “Total Spend”.
The Dataset viewer should update with a preview of your data.
Click “Save dataset and create view”.
You should now see a new sheet created named “View~GoogleAds1~1” with your Google Ads data.
Your Google Ads data will refresh automatically each time the Lido file is opened.
Step 5: Group Google Ads by Day
To match up with our Shopify data we’ll need to also group our Google ad spend by day. We’ll do that using the same method as we did for Shopify data.
Select any cell inside the daily Google Ad spend on the table you just created.
Click on the "Group" button on the toolbar (it only appears when you click inside of a table).
The “Group” dialog will open.
Make the following choices:
Click “Update”. You should now see a summary of the Google Ad spend by day.
Select the cells in the date column as in step 2 above, and format as "Date" so they show correctly.
You should now have a table of your Google Ads spend per day:
Step 6: Connect to Facebook Ads
We now need to connect your Facebook Ads account if you also spent money on Facebook ads to drive Shopify orders.
Click on the "Data" button on the top left of the Lido toolbar.
Scroll down and click on “Facebook” and then click on “Connect”
Go through the login flow, and you’ll end up in the Dataset Editor for Facebook Lead Ads.
Choose the date range you want to include. This should be the same date range as you’ve chosen for your Shopify orders and Google Ads.
Scroll down and select the fields “Day”,”Spend” and “Campaign ID”.
You will see a preview of your data.
Click “Save dataset and create view”
You’ll see your new sheet named “View~FacebookAds1~1” with your Facebook Ads data.
Each time you open your Lido file the latest Facebook Ads data will load.
Step 7: Create a combined table of ad spend and orders
We will create a spreadsheet table that combines the live-data we have from Google Ads, Facebook Ads, and Shopify to calculate the daily Customer Acquisition Cost (CAC).
We'll do this by adding additional columns to the Shopify Orders table we already have. Currently, It has two columns - Day and Order Count:
Lets add another column with the amount of Facebook Ad Spend per day.
Its easy to do this with another one of Lido’s powerful extensions to standard spreadsheet functions - COMPUTEDCOLUMN. COMPUTEDCOLUMN lets you automatically compute the value of a new column for every row in a table that grows as the table grows. We can use the helper button to get started.
Select any cell inside of the Shopify order table you've created and click on the "Computed Column" button on the top toolbar:
It will create a new blank column in your table with a header of “ComputedColumn1”
Lets look at the formula in cell C1:
=COMPUTEDCOLUMN("Formula in C1", "View~ShopifyOrders1~1", "ComputedColumn1")
What this formula says is: "For every row in the table 'View-ShopifyOrders1~1', create a new column called "ComputedColumn1", and calculate the value by the spreadsheet formula - currently the placeholder text 'Formula in C1' ".
The function signature is:
RAW COMMAND can be any spreadsheet value or function. For now, replace the contents of the cell with:
For this, we’re just using good old XLOOKUP to lookup the current record's Day column in the View~GoogleAds1~1 table and return the total spend (the SUM column). You can think of XLOOKUP as the spreadsheet way to do a join in a relational database.
You'll see some new Lido syntax to reference table columns. Remember, MAKETABLE gives you a name for a specific table you can reference elsewhere in a sheet and that is what we're doing here.
View~ShopifyOrders1~1 is the name of the Shopify orders table that we created above with the MAKETABLE command.
@Day refers to the current row's value in the Day column.
View~GoogleAds1~1 is the name we've given to the Google Ads table
[Segments Date] is the way to refer toa whole column (without the '@') in another table.
So the way to read this XLOOKUP command is:
"Search in the Segments Date column of the View~GoogleAds1~1 table for the current row's Day value. If you find it, return the value of the SUM column. If you don't find it, set the value to 0"
When you’re done, you should have a table with a new column that brings in the Google Ads spend that looks something like this:
Remember, in Lido these tables are updated with live data, so if you have selected the last 30 days of sales, the tables will automatically update with the appropriate window of data.
Add the remaining three columns
To round out this table, add three more computed columns. Just copy and paste each of the three formulas into the next three column header cells.
You now have a table with six columns and you’ve calculated the Customer Acquisition Cost (CAC) in the last column. Note, we'll have to reformat the Day column as a Date as we've done above using the formatting toolbar.
Our last step is to make a simple visual dashboard to share.
Step 8. Create and Share Dashboard
Now that we have a table automatically calculating the daily CAC, let's make a simple dashboard that we can share with our team.
Click on “Dashboard” at the top of your Lido file. It will open the dashboard canvas.
Drag the “Chart Component” onto the dashboard: From the dashboard, click on the "Components" button on the top, right side of the screen.
3. Connect the chart to your Shopify orders:
Open the component sidebar if it's not already open by clicking on the upper right corner of the Chart component, and choosing “edit”.
In the component sidebar click on the “Selected Data” dropdown, and choose the name of the table where you’ve calculated the CAC, which is “View~ShopifyOrders1~1”.
Click on the “Chart Type” dropdown and choose “Line Chart”
Under “X-Axis” choose “Day”
Under “Y-Axix” choose “cac”
You should now see a line chart with “Days” along the X-axis and the CAC in dollars along the Y axis:
4. Drag a table component from the component sidebar onto the dashboard below the chart.
Set the Table component properties:
under data source, select “View~ShopifyOrders1~1”
Expand it to take up more width
5. Clean up and preview dashboard
Add a header, resize chart, and then click the “Preview Button”
Finally, to share it - click on "Share" in the upper right corner, and "Share Dashboard".
Congratulations! You've created a dashboard showing the Customer Acquisition Cost (CAC) that is automatically updated as your data changes.
If you want to add your own branding to it - add a logo and share with your clients!