As a member of a PPC management company that specializes in e-commerce SEO, we often have to deal with big data. This is why I love using Excel for SEO, as it's a great tool that helps me streamline my work processes, and work more efficiently.
Very recently, we started working on a large e-commerce PPC campaign for a store with just under 10,000 products (active). Needless to say, this has been a fun project to work on, especially with a budget that exceeds $200,000 per month! But with great campaigns, comes great responsibility as Spider Man once said. :)
I am going to break this down into two blog posts. First, I wanted to write about how I used Excel and the client’s product feed to create custom labels for their product listing campaign. For those of you who don’t know, Google announced that Shopping Campaigns are going to be forced upon us by AdWords in August. So if you are using the traditional Product Listing Ads campaign, you might want to think about making the switch before it’s too late and you get forced into it.
Proactive PPC management companies do it better!
For this project, we decided to implement the custom labels (aka AdWords groups) to this PLA campaign and then make the switch to Shopping. The campaign was being managed by a former marketing company that did not use custom labels or Ad grouping. So, we immediately saw an opportunity to increase the profitability of the campaign, being that 70% of the budget was being spent in the PLA campaign and the ROI was not high enough (roughly 200-250% as shown below).
The snapshot below shows the last 30 days of data from an AdWords click report in Google Analytics that shows ROI.
Even when the data is viewed from a first interaction analysis point of view, the campaign still shows a lower than average ROI from what we found in our preliminary analysis.
Below is a snapshot of the multi-channel attribution report from Google Analytics. It shows ROAS (return on Ad spend) data and allows you to view your conversion data in many ways. For example, you can use the first interaction attribution model which will attribute all conversions (and the revenue) to the original source/medium that was used to bring the visitor to your website on the first visit. As you all know, most e-commerce visitors will make multiple visits before converting to check prices and explore other options. The first interaction attribution model allows you to see some really insightful data this way as shown below. To learn more about these types of reports I encourage you to read my blog post about ROAS data.
The client explained he would be much happier with a 450% ROI for the entire ad spend (all campaigns average). So our goal was to increase the ROI of the campaign with the biggest spend in an attempt to reach that target ROI of 450%.
Using Excel to optimize your data feed with custom labels
The first thing we did was ask for a product feed that included profit margins. Believe it or not, this was something the previous marketing company did not ask for. When dealing with such a large campaign, having profit margins for each product is critical in my opinion; you are about to understand why if you don’t already know or agree.
Thanks to the 50 billion dollar-per-year company Google, we have some pretty darn sophisticated marketing software to work with. With Google Adwords, we have the ability to optimize product feeds so we can add labels to products that are more profitable than others, and we can then use those labels to bid more strategically.
I like to organize the products by profitability, and add a label for different levels of profitability. Having these types of labels in different forms such as a profit-intrinsic or brand-intrinsic labeling system can allow you to maximize your ROI. The labels will appear in Google Adwords and allow you to make bids based on the labels.
Creating a sound labeling system that is easy & effective
In this example, the goal is to create a series of 10 custom labels. One for each level of profitability in increments of 10 as shown below:
custom label 1: 90-100+ percent profitability
custom label 2: 80-90 percent profitability
custom label 3: 70-80 percent profitability
custom label 4: 60-70 percent profitability
custom label 5: 50-60 percent profitability, etc.
So the first thing I did was pull the data feed from Google Merchant Center and begin to add new columns for each of the 10 custom labels. Unfortunately, with over 10,000 products, this can be quite a daunting task if you don’t know your way around Excel. Needless to say, I referred to my handy Excel for SEO blog post that I made a while back, which shows several Excel formulas and functions that can be applied for SEO purposes. I used IF, Vlookup, Simple concatenation and more to optimize the entire spreadsheet.
Tagging each product in your data feed
The first thing I wanted to do was to add a column as an identifier to see which bracket of profitability the product fell into. I created an extra column and used an IF function to pull out any products that had a profit margin greater than 90 percent. I simply entered “=IF(D2>=90,1)” which basically told Excel to check the profit margin in column E to see if it’s more than 90 percent. Every row that had a profit margin in column E greater than 90 percent got a “1” in the cell of the newly added column. Doing the same thing over 10 times until I had a value for each of the levels of profitability allowed me to quickly add a tag to over 9,000 rows of product data.
This screenshot illustrates how I used the IF And function to add tags to those products with margins that were greater than X AND less than Y. For example, as shown below, the formula “=IF(AND(D2<=50,D2>=40),6)” tags each product that is between 40-50 percent profitability with the number “6” to show the product falls into the 6th level of profitability group. The goal here is to simply tag each product with a label that groups each product into levels of profitability.
Formatting your data feed prior to uploading
Next, I used a Vlookup function to pull the tags over to the sheet with the product feed, so I could begin to optimize the data feed with the custom labels. Google explains you need to follow certain guidelines in order for the data feed to be read correctly. I recommend you keep this Google support link handy, which explains how to optimize your data feed.
After I created a column for each level of profitability, I used simple concatenation to fill in the rows that needed to be filled in. For example, in the first new column I placed a “1” in all the cells which had products with a profit margin of 90% or above but the other cells in that row were left blank. You only want to have a value in the cells that meets the criteria; the rest should be left blank as shown below.
Once your feed is 100 percent ready for upload, you can begin to upload it and check for errors. Depending on if you are using shopping or PLA campaigns, you will have to change your column header titles slightly. For the most part, you should be good to go from here. If your data feed is approved, you can then see your labels in your AdWords account under your PLA or Shopping campaign. The best part is you can now make bid adjustments based on your labels!
We are going to be making adjustments on the products based on their profitability to see if we can quickly bring up the ROI for the entire PLA campaign. If our theory pans out, we should have one happy client on our hands and a great case study coming up soon!
Keep in mind, you can always use labels to group up your products in different ways. I'm going to roll out and test this theory, so stay tuned for the next post which is going to show the improvement in ROI after implementing these changes.
Sergio Aicardi is the owner of The Miami SEO Company and works with the No Risk SEO Team. His responsibilities include developing business relationships and optimizing internal processes to ensure the business is constantly growing, while constantly enhancing the quality of services rendered. He has been in the industry for over six years, and his core competency is in analyzing traffic data to enhance campaign performance. Sergio’s last article for SEMrush was “How to Use Excel for Data Collection on E-Commerce URLs."