One of our clients on the Food & Drink team was been having a problem with monitoring the stock levels of products across their website, and so 4Ps decided to step in with some custom monitoring software and our data warehouse to sort the situation out.
Whilst their warehouse teams know which products are in and out of stock and the website is kept up to date with the stock levels, there was no way for their marketing team, our main contacts there, to easily see this information without visiting the website and manually checking levels for individual products.
Since their products sell out quite quickly and they get frequent deliveries in, this means that the list of available products on the website changes frequently, and therefore the PPC campaigns that we run for them also need to be updated frequently, to avoid wasting time and money advertising products that cannot be bought. They needed a way of quickly checking stock levels across the whole website, ideally something that we could also access, to minimise the communication time between us and the warehouse teams.
In order to solve this problem, we built a bespoke webapp that displays the stock level of each product on their website on a single page, as well an overview across the whole site and levels for some of our key products over time.
Since we already had a nice list of each of their products on their website and the associated URL in the form of the PLA feed, we decided that the best way to gather the stock levels was to visit the page for each product and extract the stock level from the content of the page. With a bit of PHP and a MySQL database this wasn’t too hard to set up – I’ve documented the process in more detail over on my blog.
Next we had to work out the best way of displaying this information – after all, there would be no point in us collecting all this data and then not being able access it! After some discussions with the PPC team members who manage the account, we decided that we should display the current stock level for each product on the site, as well as levels over time for some of our key products and an overview of the ratio of products that were in or out of stock. Using the excellent Google Charts API to handle the heavy lifting of drawing the graphs, and Bootstrap to create a responsive layout, we built a web-accessible dashboard to hold this information somewhere that could easily be viewed by both our client and the 4Ps team.
Below you can see an example of the dashboard, using the Google Charts API to display key product stock levels over time, and the ratio of in and out of stock products.
We have already had a number of ideas about how we can further use this information and additional features to add to the dashboard. Firstly, we are going to add a ‘Last in Stock’ column to the product table which will allow us to also see how long products have been out of stock for, and therefore when the next delivery is likely to come in.
Secondly, we are planning on linking the stock level data with the information we are already collecting from Google Analytics in our Data Warehouse. This will allow us to estimate the ‘lost revenue’ when a product goes out of stock by looking at traffic levels to the product page and applying the average conversion rate for when the product is in stock.
Finally, and certainly the most challenging, we are looking into linking this tool with the Adwords API to automatically pause campaigns, adgroups or ad copy when products go out of stock, and re-enable them once the product is available.
4Ps isn’t just another SEO agency. To discuss how content, data and advertising are evolving together in order to keep pace with new developments in user interaction and technology, give us a call on +44 (0)207 607 5650 for a no-obligation coffee and chat about marketing and behaviour across all inbound channels.