Using Google Analytics to track revenue lost to poor stock control
Keeping track of stock levels can be a challenge if your stock control is not automated, particularly when you stock thousands of products or SKUs.
When it's left for humans to manually handle stock levels, products will inevitably fall out of stock at times and you'll lose sales when customers land on pages in which the virtual shelves are bare.
If you're using paid search to acquire customers, you're also throwing money away and the customers are much more likely to bounce when they land on a page containing products they can't purchase.
I'm told that not many companies measure this and unfortunately Google Analytics doesn't measure this sort of thing by default. However it's quite easy to add this functionality yourself via a relatively straightforward hack.
You'll need to do a spot of coding, but the results are really useful and it's a quick and easy job for the average developer. I implemented this on three e-commerce sites in just a few hours.
Once it's up and running, you'll be able to see which products have fallen out of stock, examine the products that you regularly run out of, and also see which ones are being seen the most by your customers - indicating the greatest loss of revenue.
You could set up automated reporting so the data gets emailed to you every day or week, allowing you to beat your stock control manager with a stick for dropping the ball and losing you sales.
With a bit more effort you can also create some code which allows you to record the actual value of sales you lost because you sold out of certain items, which can be a really useful KPI to help drive improvements in stock management.
Step 1: Querying the database
First, you'll need to create a database query to determine whether the item on your product page has fallen out of stock.
Everyone's database will be different, but basically, you need to run the query and then get a true or false value indicating whether the product is in-stock or out-of-stock.
Your query might look a bit like this:
SELECT stock_level, product_name, sku, price FROM products WHERE id = '1234'
If your query returns zero, indicating that the product is out of stock, you'll need to get the product name, SKU and price and write the information to the Google Analytics tracking code (GATC).
Step 2: Modify your GATC
Precisely how you need to modify your GATC will really depend on how much you've already pimped up your Google Analytics implementation.
On the average site in which you're just using the default tag the Google Analytics Tracking Code (GATC) will just be included in the bottom of the page.
For more sophisticated sites, the GATC will be being created dynamically and the server will be writing in additional data into extra variables depending on the page being viewed.
Within your GATC, you'll need to write the following trackEvent code and push it to the Google Analytics server. It will add the data to a category called "Out of stock items", along with the product name, the SKU and the price.
_gaq.push(['_trackEvent','Out of stock items','Apple iPad 2','2528',399]);
Oddly, Google Analytics won't allow you to use a decimal point in a value passed in the trackEvent, so you'll need to round up the price of the product to the nearest whole number.
You can do this easily using a ceil() type function in your server-side language or the database query itself.
Step 3: Accessing the raw data
Every time a page is viewed, the server will check to see whether it's in stock. If it's out of stock a line will be written to the GATC which sends the additional data to Google.
This obviously works most reliably when you have a single product or SKU per page. You could use it with multiple SKUs per page, but the data might not be so clear and it's going to be trickier to interpret.
To access the data, using the older version of Google Analytics go to Content > Event Tracking > Categories > Out of stock items.
The basic report will show you Total Events (the number of times a page with an out of stock product has been viewed), Unique Events (the number of times those pages have been viewed by unique users), Event Value (the price of each product viewed multiplied by the number of page views) and the Average Value (the average value of each product viewed).
Step 4: Calculating the approximate total value of lost sales
The quick and dirty way to calculate the total value of sales lost due to running out of stocks of the products you sell is fairly easy to do.
Take the total event value, say £1,234,567 and divide it by 100 and then multiply the value by your overall site average conversion rate, say 5%, to get the total estimated value of lost sales, which is £61,728.35.
This really only works accurately on sites in which there's a single product/SKU per page, and doesn't take into account differing conversion rates for different products, but it's interesting nonetheless.
Step 5: Calculating the precise value of total lost sales
If you want greater precision, you'll need to use the actual conversion rate for each product, and not the overall site average conversion rate.
This is because many products differ greatly in their conversion rates. Those items which are on sale might be selling quickly with high conversion rates, while other items might be less keenly priced and sell more slowly.
Complicated products which require lots of prior research also tend to have lower conversion rates than cheaper commodity items.
For example, let's say we're looking at an iPod Touch, which has an average value on our site of £184. 744 visitors viewed it over our selected period, and it was out of stock several times for quite long periods, resulting in total event value of £175,904.
The product has a 2.3% conversion rate, so we can therefore see that by running out we lost around £4045.79.
Click the product name in the Content > Event Tracking > Categories > Events tab and you should see a report which shows how many times over your selected time period your chosen product has fallen out of stock.
We can also see from the individual product's charts that it was in stock for several weeks, then sold out, then came back in and sold out after a few days, and did the same thing several times.
If a product is falling out of stock regularly, customer demand might be exceeding your supply, which suggest you need to increase your stock levels, which should allow you to sell more. You definitely shouldn't be running out of stocks of your best selling lines.
Step 6: Automating it
If you're happy with an approximation, you can set up Google Analytics to send you an automated report every day or week to let you know which products are out of stock, and to report the total value of lost sales. You'll need to manually calculate the estimated revenue loss by using your average conversion rate.
If you want to get more detailed results, you'll need to write an application using a server side language, such as PHP, which queries the Google Analytics server to retrieve the data via the API, and then uses the conversion rate for each product to determine the actual value of lost sales.
That's a bit of a time consuming task and too lengthy to explain here, but it's probably worthwhile if you've got stock control issues that need to be monitored carefully.
There are a few KPIs I've found useful to report with this data:
1. Total visits encountering stock-outs
2. Total revenue lost per visit due to stock-outs
3. Percentage of visits encountering stock-outs
4. Total revenue lost
1. Of course, this will only work when products that are out of stock are viewed. If you've got some obscure products that have fallen out of stock without being noticed, and their product pages don't get viewed for a while, there's going to be a delay in spotting the trend via GA.
2. It's a hack and GA wasn't designed to do this, and when you view the data in GA it doesn't mean that much, particularly when you view the colour coding when different time periods are compared.
3. Using a site-wide average conversion rate is risky and only gives a rough approximation. To get detailed figures you'll need to use the individual product conversion rates, which requires much more effort.
4. This won't tell you when a product is going to sell out, or make any predictions on how you need to adjust stock levels. If you want to do that you'll need to build the functionality into your ecommerce platform.
Published: Matt Clarke Sunday 25 September 2011, 3:08 pm
Views: 9,645 times
Filed under: stock control Google Analytics hacks
Comments on this item
Login to leave your comments