Pro Tip: How to Calculate Paid Search Net Profit by SKU
I show you how to combine paid search cost AND product revenue AND cost of goods sold to give you a SKU by SKU report on net profit and margin.
If you’re managing an eCommerce store or paid search campaigns for clients then you understand the importance of measuring profit and not just top line revenue.
I’ve written previously on how to measure gross margins and net profit using a few nifty Google Analytics features but this has known limitations. It’s a nice compass but not quite good enough to be a map.
We work with clients that do really amazing things in Adwords and since we’re in eCommerce most have a strong reliance on shopping campaigns.
I see some accounts that have very robust campaigns setup with product groups down to SKU level and others that stick to the low/med/high priority level “hack” to manage bids for generic vs SKU vs brand terms. Oh and others where there is a single campaign and product group!
This how to guide is for those of you that rely on highly optimized paid search shopping strategies. I show you how to combine paid search cost AND product revenue AND cost of goods sold to give you a SKU by SKU report on net profit and margin.
Just imagine the insights that you can apply to your own paid search strategy 🙂
You can use the same principles if you’re just trying to see net profit by SKU with cost of goods sold. But there are better ways to automate this use case with custom metrics in Google Analytics.
A few notes:
- I’m using a Shopify store as an example but this same approach works for Magento, Big Commerce, Salesforce, WooCommerce, etc. Basically any eCommerce platform.
- I’m not pulling in other marketing costs – like Facebook, etc. But I recognize these exist as well and will be unique to your scenario. Trying to keep this as simple as possible.
- The paid search costs are NOT inclusive of bids on keywords; these are costs specific to shopping campaigns only.
- I’m not showing how to automate this…that’s what Elevar is for 🙂 but hopefully I inspire your own ideas on how to automate yourself.
Steps to Calculate Net Profit by SKU:
- Export a cost of goods report by SKU from your internal system (if you don’t have in GA)
- Export full product list by SKUs from your eCommerce platform
- Export a custom report in Adwords that contains your product ID and cost
- Export orders by SKU from your current time period from Google Analytics or your eComm platform
- Create a working Excel spreadsheet and drop steps 1-4 into their own tabs
- Create a “Rollup” tab with your profit report headers
- vLookup data from steps 1-4 into appropriate columns in the rollup tab
- Input custom formulas to calculate your data per SKU/row
- Sort, Filter, and Analyze!
It sounds like a lot of steps but the insights you glean are worth it!
Here we go…
Step 1: Export a COGS report by SKU
Depending on what eCommerce platform you’re on, you might have this with your order data (step 4). Otherwise you should have this in your ERP/OMS/PIM that you can easily download. It can be pretty basic: SKU, Cost per SKU, and a bonus column of Retail Price per SKU. Something like this:
Step 2: Export full product list by SKUs from your eCommerce platform
In this example from Shopify, I’m just going to my all products view and clicking the “Export” button which will email me my complete file:
Once I have the file I’ll grab my product name, brand, type, and SKU and delete the rest of the fields.
Step 3: Create and export a custom report in Adwords that contains product ID and cost
This can be done in Google Analytics as well but it’s a bit more difficult to work with the data. In AdWords you’ll want to:
- Go to the new version if you aren’t there already
- Click on the “Reports” option
- Create new custom report
- Add “Item ID”, “Cost”, and “Search Impr. Share” data points to the report
- I usually filter the cost column > $0
- Set your date range to the date range you’re evaluating
Step 4: Export orders by SKU for your time period
Two options for this step:
- Create a new custom report in Shopify that you can reuse in the future (if you’re using BigCommerce or Magento then you can create similar style reports).
- Create this report from Google Analytics to help with automation.
The reason I’m showing exporting from a platform is for use cases where you aren’t sending offline hits to GA and/or you have a large % of phone/admin initiated orders that don’t show up in GA.
Why is this important? When you have products included in paid search campaigns that result in more offline purchases then you want that revenue to “count” in your profit analysis.
Ultimately for this analysis you need a report that contains:
- Product Name
- Total Sales (or Net Sales)
In Shopify notice how I choose “None” in the dropdown of how to group the data:
Step 5: Create a working Excel spreadsheet and drop steps 1-4 into their own tabs
I’m going to try and prevent this from being a full excel training guide so you’ll have to fill in some of the blanks with the different data tabs 🙂
But the first step is to just copy your individual data files from steps 1-4 into their own tabs in your working spreadsheet. Hopefully your tabs look something like this:
Step 6: Create a “Rollup” tab with your profit report headers
This is the easiest step! Create a blank tab (“Working Sheet”) with the following headers (note that I have list price in column G which you may or may not care about depending on the amount of discounts you offer):
Step 7: Assign data from steps 1-4 to appropriate columns
This has several mini steps contained within.
a) Input a simple formula for your product name, brand, type, and SKU (columns A:D) into your working tab that grab your product list data:
b) In columns E & F you will need to use a vLookup formula that searches your SKU (column D) in your order data tab:
Repeat the vLookup for your revenue column as well.
c) Now we’re going to grab our cost of goods sold per SKU through the same vLookup process for our COGS tab:
d) Then we need a formula to account for products that sell > 1 quantity so we multiply our total quantity sold (column E) times the cost per item (column H):
e) Next up is Ad Cost! Once again we have a vLookup based on SKU to product ID in our Ad Cost tab
One thing to note: this example is using SKU to join data from orders + COGS + paid search. It’s very possible you might have SKU data for orders and a different ID in AdWords from your merchant center data. If so there are a few extra steps to mash the data together (not shown).
f) On to our net profit report column…a much easier formula! This is just taking our revenue (column F) and subtracting our total COGS (I) and Ad Cost (J):
Step 8: Input formulas to calculate your profit margin per SKU
The fun part! This is where you finally calculate your profit margin by sku. This is done by dividing your net profit column (K) by your total revenue (F):
I will usually fix all of the #N/As by find and replacing to 0 to try and clean the report up a bit.
You can then sort, filter, and do all kinds of fun stuff to extract insights:
You’ll notice the little goody “Search Impression Share” at the end. This is also done by a vLookup – I’m intentionally not showing you the exact formula so you can try for yourself!
This report produces tons of insights on where to grow and where to cut. When reviewing your search impression share against your margin by SKU you will come up with even more ideas on how to improve your own search strategy!
If you have questions leave them in the comments below – and please share if you found this useful!
Do you want me to help create a similar report like this for your business? Let’s talk – send me a chat.