One of the more exciting projects I’ve been working on recently has been a simple webapp for one of my clients at work. I’m a part of the Leisure team at 4Ps Marketing, and one of our Food & Drink clients has been having a problem with staying on top of the products that are in or out of stock on their website, and this has had a knock on effect on the performance of the PPC campaigns we run for them.
The teams in their warehouses know what’s in stock, and of course this is fed into the website which is always up to date, but there was no way for the marketing team, our main contacts there, to easily see what was going on without checking the status of each product on the website manually. 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 are live also need to change frequently, otherwise we are 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, and so this is what I set out to build.
In essence, the finished product is quite a simple setup. There are 2 PHP scripts, a data collection script that runs each morning, checks the status of each product on the website and writes the results into a MySQL database, and a dashboard script that reads this database and outputs it onto a webpage for easy viewing by us or by their team.
Surprisingly, the data collection script was the easier of the two to write. Since we already had a nice list of each product they sold and the corresponding product page URL in the form of their PLA feed, I was able to export this to a CSV file and upload it straight into a table in my database. Keeping this list of products in a separate table also allows new products to be added and discontinued products to be removed really easily, which was one of the more important features I was looking to have.
All I had to do then was to visit the URL for each product, read the stock level from the page and write the corresponding row into my results table along with the date so that, in the future, we can view historical data. Initially, I used PHP’s file_get_contents() to read the source code for each product page as a string, and then look to see if that string contained the og:product_availability meta tag which is automatically populated by their CMS, Magento. However, this only allowed me to determine if a product was in or out of stock and we quickly realised that it would be far more useful to return the actual number of products that were in stock (or 0 if out of stock), which would let us also monitor how quickly products were selling and give us an early warning if a product was running low.
To accomplish this, I used DOMDocument to load the HTML of the page as an XML object and XPath to traverse the elements of this object and return the contents of the div which held the number of products that were in stock. There were a couple of problems I ran into, namely the page structure being slightly different when products were out of stock, but overall this was a much more elegant solution and also seems to run a little faster as well. Once I had that data it was a simple process to write the status for each product into the database and set up a cron job to run the script each morning.
Building most of the dashboard also didn’t take a huge amount of time, using Bootstrap to handle the layout of the page and a couple of lines of PHP to display the stock level of each product for that day in a table. However, we also wanted to be able to view the stock levels of products over time as well as have a quick overview of the ratio of in stock to out of stock products. The Google Charts API is ideal for this as their library helpfully deals with the difficult part, actually drawing the chart, and so all I needed to do was supply the data that the chart is based on. Not so helpfully, the example code that Google has in their tutorial for the API doesn’t actually work but after a lot of troubleshooting and a bit of help from my coworkers I did manage to get it to show what I wanted.
Using the Google Charts API to display key product stock levels over time, and the ratio of in and out of stock products
What made this a really satisfying project to work on was that I achieved everything we had set out to do at the start and over not too long a time period, although it wasn’t all easy to do and I learnt quite a few new things along the way. Of course, no project is ever quite finished and I do already have a couple of things in mind to extend this. The first is to add a ‘Last in Stock’ column to the table, which shouldn’t be too difficult as we’re already collecting all the data required for that and so just needs a bit of extra processing.
We can also link the stock level data here to the Google Analytics data that we already collect for the 4Ps Data Warehouse and use this to provide an estimate for the revenue that’s lost when these products are out of stock, by looking at the traffic to product pages and applying the conversion rate that we measure when products are in stock.
Finally, as a longer term project, I want to link this data to the Adwords API so that we can automatically pause campaigns, adgroups or ad copy when the relevant product goes out of stock, and bring them back live once a new shipment comes in. This is what I imagine to be the final goal for this project, but is going to take a good amount of work, both on the side of the data and scripting but also the Adwords account structure, as this will need to be consistent with the way we measure the data and feed it into the API.
Overall, I’m very happy with the way that this project turned out. It only took around a week to put together from start to finish and it’s had a direct impact on my job as well as the rest of my team, which is really what I was aiming for when I started with it. Our client has also gained a number of benefits from both the extra information as well as the additional agency time that they effectively have now. I’m really looking forwards to seeing where this project goes in the future, as well as working on other similar things as they come up.