Category Archives: Retail

M Shed shop refit ChangeLog 2019

We’ll be writing a short update each day about how M Shed shop refit is progressing. Ahead of the refit the wider team used Basecamp to discuss the new shop design and for all decision approval. Lots of time has been spent recently on the finer details including bay depth, lighting of bays, till location and opportunities for adding value in the design. Let’s go!

Pssst you can read about our last refit changelog at Bristol Museum & Art Gallery in 2018 which has since sales increase 51% in 12months

Friday 5th July

Day 5 – voilà

install Trim, slat, glass doors, locks, first lit bay, clean, merchandise…open 16:30 and took our first sale (#236160 for £29.44 to tourists).

Thursday 4th July

Photo showing how the shop looks at the end of day 4 with glass in bays and power to the till
End of day 4 showing bays with glass shelving, power to the till and only minor work remaining

At the beginning of the day I was quietly confidently telling folks we’ll be done by end of Friday. Not 100% all singing and dancing and not a minimum viable product (MVP) but “good enough” to trade. Im not sure anyone believed me though (chuckles) as on the surface it did look far off. However much of that was due to the tools/cutting areas giving the impression of being far off.

Our electrician did a stellar job of getting power running to the till area and completing the wiring of the bay lighting and TVs. The bay lighting isn’t hooked up yet but this will be completed early next week and doesn’t pose an issue to trade over the weekend.

The shop fitters completed the final bays and slat wall. At the same time a few helpful hands (THANKS!) added the glass shelving. We only had a few book shelves on-site but ARJ-CRE8 super kindly have arranged for the rest to be delivered on Day 5. We started to pack down the temporary pop-up shop and transfer products to their new homes. By the end of the day we were all tidying up ready for a deep clean tomorrow morning. I was even reunited with my favourite tool, the pallet truck (long standing joke!) which we put to good use – a pallet truck is the workhorse of moving “stuff” with ease.

Helen and I will make a call about re-opening the shop tomorrow (end of the day) but I can’t see why we won’t be in that position by lunchtime… last big push which is mainly going to be getting product out.

Thanks to everyone who has been giving words of support, lending a hand or making jokes!

See you at 7am to help us move products right?

Wednesday 3rd July

Day 3 was very productive!

The shop fitters were able to complete the perimeter bays as shown below. Above the bays we’ve decided to make frames to hold graphics and tighten up the brand. I originally wanted to have digital screens but the cost didn’t outweigh the benefits and we can use some of our historic photos from around the harbour. Also the team didn’t like my idea which is fine!

Day 3 showing the shop with all perimeter bays in place and the till point area.
Day 3 photo showing the shop with all perimeter bays in place and the till point area.

In addition to some more decoration our brilliant in-house electrician had his first day on the project. He has the task of working out how best to power the LED bay lights, till area and our digital screens that will be behind the till. With the welcome news that the shop fitters expect to be finished early, our timeline has moved up. I asked Rob to focus first on powering our till as I have my fingers crossed we can then trade Friday. Once the till (shown below) has power we “can” be in business. After the till lighting the bays is the second priority then finally our digital screens.

Photo of our new till area.
Our new till area is now in place – woot!
Photo of a giant hole in the ground…well that was unexpected…

In the above photo there is a giant hole in the floor! This was under a bit of wall we removed and that needs to be “made good” so thankfully our ever awesome operations team arranged for a steel plug to be made today to save the day. Snags are always going to crop up and having professionals all around you makes them disappear pretty quickly. The hole needed to be filled as 1/2 of a bay unit was due to be installed at that spot.. thanks everyone who kicked into action to resolve today.

Our graphics have also gone to the printers today and will be installed Monday. In a project like this I’d much rather add new elements one by one rather than have tried to push too hard for all elements and contractors to work around each other. Having the project be ahead gives even greater buffer. Onwards!

Tuesday 2nd July

Day 2 has been rapid and already has enough done to see it coming together in real life as opposed to doodles and words! One set of contractors set about decorating and also building a new room to support our back of house function for the museum. Our design team chose the colour scheme which is designed to reinforce the brand and make the products shine.

Photo showing progress from day 2 which includes decorating and installing window bays
Day 2 progress which included wall bays and decorating. Photo by ARJ-CRE8

The shop fitters concentrated their efforts on installing the window bays and even found time to begin on the far back wall. More fittings arrived too such as glass shelving, our new custom marble table and other book tables. We expect each table to generate £5,000+ in annual sales.

We made a design decision to install bays directly in front of our windows across most of the space instead of retaining a “view” from the outside into the shop. We initially wanted to retain the “view” into the shop or use beautiful visual merchandised displays. However the displays required too much space which would reduce our shop floor area by 1m+. And keeping windows would eliminate our ability to increase overall “shoppable” space which is the aim of the game. We also took advice from a professional Visual merchandiser and between us the recommendation was “bays over beauty”. If it doesn’t add valuable then don’t do it. In the end a sensible comprise for budget reasons and to still retain some natural light at eye level was to have bays on the sides and keep the central of the windows open.

The decision to put bays in the window produced a new opportunity for us. We are now able to place graphics on the windows to promote our shop offer. Originally we planned to apply the graphics to the inside of the windows but missed the window of opportunity (bad joke) so instead will apply to the outside of the windows. This is actually a blessing in disguise as it now means we can change the graphics more frequently without a lengthy process of having to remove the bays to work. Below is the draft design we will send to the printers this week. You’ll see that two windows are to remain free and “viewable” from the outside. Natural light will still come in from the top panes.

Photo showing what the draft graphic will look like on the outer window - a giant M from our style guide
Photo showing what the draft graphic will look like on the outer window – a giant M from our style guide

In the windows that are in the foyer area there is the opportunity to expand beyond promoting retail to help explain what M Shed is and what we have on offer. Tomorrow i’ll ask our designer Fi what is possible in the four bays shown below.

The photo shows four windows that now show the back of the retail bays so we need to apply graphics to the outside windows.
The photo shows four windows that now show the back of the retail bays so we need to apply graphics to the outside windows.

Monday 1st July

ARJ-CRE8 arrived on site at 07:00 and were greeted by our Retail Manager Helen. Today’s schedule was to focus on the stripe out the current old shop fittings. The photo below shows a bare shop within just a few hours. Claudia and Helen spent the day helping where possible and getting our temporary pop-up shop ready.

 Photo of striped out M Shed shop

Once the old shop was stripped out the team then brought in lots of the new shop bays and fittings. At the same time, anther company were moving various sensors and alarms as we need to re-site lots of “bits” that were built into the original reception area. Fi, our 2D designer was also finalising graphics for the walls and windows. Onwards!

Photo showing the new shop fittings neatly laid out ready for the installation.
Photo showing the new shop fitting neatly stacked for install
Floor plan birds eye view showing a 2D pln of each part of the shop including all bays.
The floor plan of what the shop and reception areas will look like

Sunday 30th June

The last day of the current shop as we know it. Once the shop closed at 5pm a number of the team packed away all products on display. Products were either moved to the temporary “pop-up” shop area in the foyer or moved upstairs to our meeting room which is acting as our transit space during the project. At 07:00 the shopfitters from ARJ-CRE8 will be on-site to begin the project proper. Let’s hope the skip arrives earlier at day 1 is largely taking out the existing shop fittings and fixtures.

Sharing our retail performance

May I introduce the Retail Performance dashboard. Since taking on retail in 2015 we’re proud to have increased sales by 60% in three years. We’ve gone from loss making to profitable  and at the time of writings we are up 22% compared to last year. What that really means is that our retail efforts will contribute £100,000+ profit back to the service which keeps 2 or 3 staff outside of retail in employment. I jokingly say that our sales of ‘fart whistles’ are literally keeping others in gainful employment!

I regularly tweet stats of our retail performance so thought i’d now take that up a notch and share a dashboard that you can use to see the data yourself. The digital team are working on some much slicker visualisations but for now this will do.

The Retail Performance dashboard, powered by Google Data Studio .

Things to add include:

Integrating Shopify with Google Sheets (magic tricks made to look easy)

In team digital we like to make things look easy, and in doing so we hope to make life easier for people. A recent challenge has been how to recreate the Top sales by product analysis from the Shopify web application in Google Docs to see how the top 10 selling products compare month by month. The task of creating a monthly breakdown of product sales had up until now been a manual task of choosing from a date picker, exporting data, copying to google sheets, etc.

Having already had some success pushing and pulling data to google sheets using google apps script and our Culture Data platform, we decided to automate the process. The goal was to simplify the procedure of getting the sales analysis into Google docs to make it as easy as possible for the user – all they should need to do would be to select the month they wish to import.

We have developed a set of scripts for extracting data using the Shopify API, but needed to decide how to get the data into Google Sheets. Whilst there is a library for pushing data from a node application into a worksheet, our trials found it to be slow and prone to issues where the sheet did not have enough rows or other unforeseen circumstances. Instead, we performed our monthly analysis on the node server and saved this to a local database. we then built an api for that database that could be queried by shop and by month.

The next step, using google script was to query the api and pull in a month’s worth of data, then save this to a new sheet by month name. This could then be set added as a macro so that it was accessible in the toolbar for the user in a familiar place for them, at their command.

As the data is required on a monthly basis, we need to schedule the server side analysis to save a new batch of data after each month – something we can easily achieve with a cron job. The diagram below shows roughly how the prototype works from the server side and google sheets side. Interestingly, the figures don’t completely match up to the in-application analysis by Shopify, so we have some error checking to do, however we now have the power to enhance the default analysis with our own calculations, for example incorporating the cost of goods into the equation to work out the overall profitability of each product line.



Bristol Museum & Art Gallery refit ChangeLog

Photo of newly refit shop at Bristol Museum & Art Gallery

After much planning, preparation and excitement the week of 25-29th June 2018 was the building of our shop refit at Bristol Museum & Art Gallery. The first time in our history that we’re commissioned a specialist cultural heritage shop fitting firm, ARJ CRE8. It is the end of the week and many people have worked very long hours to smash out the out shop fittings and build us a shop that we can be proud of…and most importantly increase profit.

The shop is complete and ready for customers on Saturday. We have a small snagging list and need to visual merchandise properly but this is scheduled for early next week. For now we just need to ensure 100% of products are available and nothing is missing /left in storage.

Today is a proud moment

Thank you to everybody who encouraged us throughout the week and/or lent a hand.  A special thanks also to Bristol Museums Development Trust who agreed to significantly contribute to the cost of the project. I can’t thank Andy, Jon and the team from ARJ CRE8 enough for their professionalism, problem solving ability and relentless cheerfulness!

Now let’s go out and prove you don’t need a stockroom…..hehe


29th June 2018
  • 07:20-10:00  GO! GO! Go! Moved as much products as possible from storage to the shop and our holding space. Big thank you to the staff who volunteered some time to make stuff around
  • 07:45 – 17:00 Finished up adding doors to bays, shelving, lighting adjustments and painting
  • 11:00 accessories arrive from courier to enable visual merchandising of the shop
  • 12:00-16:30 a few of our international volunteers came to the rescue and helped us prepare shelving and get products out on the shelves.
  • 15:00-17:00 move the pop-up shop fittings back into the shop and setup the tills and digital signage
  • 15:30 sold to our first customer despite being technically closed! A visitor really wanted our Millerds Map so I showed him our new bay and we made the sale!
  • 17:00-18:30 vacuum, clean and move out any non-critical products and accessories
  • 18:31 Shop is ready to open Saturday morning
28th June 2018
  • 07:30-10:00 move stock from deep storage
  • 10:00-13:00 move bay units into position
  • 13:00-18:30 wire and light each bay, reconnect air-handling which appears to have been out of action for years, finish cutting ceiling tiles
  • 17:00-18:30 move products to outside shop ready for restocking Friday morning
27th June 2018
  • Build bay bases and measure out precise bay locations
  • Wire perimeter
  • Ordered accessories for displaying products
  • Wire networking to shop
  • Empty final waste to skip
26th June 2018
  • 07:30 Ceiling fitter arrives onsite to fit ceiling tiles on existing tracks. Quickly discovers that all the track is obsolete and needs to replace entire track
  • 08:00 Zak tears shirt moving pallet full of ceiling tiles
  • 08:30-10:00 set up pop up shop in front hall. Shop takes £496.35 gross during day
  • 08:30-11:00 Replace obsolete circuit board
  • 08:00-21:00 Continue work to perimeter walls. Edge of ceiling complete and 50% of ceiling track fitted
25th June 2018
  • 06:30 Skip arrives…in wrong location…… 2hr wait for move
  • 8am Contractors arrives and unloads tools
  • 08:30 Contractor begins to gut existing shop walls and ceiling
  • 10:00 Retail team begin to review products for pop-up shop which will run 26-29th June
  • 09:00 Sparks begins to review wiring and remove old…discover circuit board is ancient so we get in Carters to assess and agree to replace on 26th
  • 10:00 Waste for skip removed to front of building and loaded into waiting skip
  • 14:05 [redacted!]
  • 14:15 Building Practice team called to assess wall
  • 15:00 Large lorry of 38 shop bays arrives and is unloaded
  • 16:00 Stone mason’s make wall safe by carefully taking wall pillar apart without further damage to each stone which is then stored
  • 17:00 Second large van arrives to deliver central bay units and small fittings which is unloaded
  • 17:45 Remaining waste loaded into van
  • 17:45 to 18:15 Clean up of route
  • 19:30 Evening private hire event starts
24th June
  • Team of 6 empty all shop products and move to holding location
  • Old fittings e.g shelving removed to storage or for recycling

Photo of shop the day before refit all emptied and readyThe shop just hours before the refit to rip out the stockroom, install new bays and maximise the space

Preparing to refit Bristol Museum & Art Gallery shop

Between 25-29th June 2018 we’ll be closing our shop to gut the space and build a new and improved customer offer. I thought I’d take the time to explain the details of the project just ahead of the actual build.

The shop was last refit in the early 1990s and in the past 18-24 months it has been a daily struggle to grow the business within those dated constraints which are primarily:

  • Space isn’t used effectively both behind the scenes (stockroom) or in the public area of the shop and cannot be optimised further
  • the fittings are very dated and the super wood effect weakens our brand
  • a partial  2016 refit saw improvements to sales by introducing LED lighting, dedicated nesting tables and a bookshelf area which increased sales by over 100% for those categories
  • although the ceiling lighting has dramatically improved the general vibe, the majority of products are still not lit well which doesn’t show products in the best way
  • the bays are all slatwall which constraints our options for displaying products, limits the visual merchandising and has poor space/density

We went out to tender and successfully secured the expertise for design as build of ARJ-CRe8. Originally we hoped to complete the project earlier this year but we missed the narrow window. As the exhibition exits through the shop we can only do the work between exhibitions so a June date was set.

We had a reasonable budget, a contractor and a GO date. As with all my collaborative projects we use Basecamp to communicate with all the project team and to keep other interested parties in the loop. I love tools like this as they cut down on meetings and keep a full history of questions and decisions that we can refer back to. It means when we do meet face to face it is super productive. Between February and April we worked together on the design, staff feedback and drawings. In total we’ve had five evolutions of the original design. Each iteration is an incremental improvement to the previous direction and catching missed constraints.

I was keen to completely remove the traditional  “till” area as I believe this isn’t a productive use of space and the future of retail will be till free. However we’re not quite into the future so my colleagues successfully convinced me that being an early adopter isn’t always best. We will test a till free approach in the near future!

Now that the design is in the final build phase we know that the refit will:

  • remove the stockroom to give us 20% more shopfloor space and 31 total bays with under unit storage
  • allow us to provide a better customer experience with a shop designed and built for a heritage customer
  • use the removal of the stockroom to properly implement an effective buying and stockholding procedure – hold less stock to keep as much cash free as possible and not own risky products
  • increase serving from one cashier to up to two at the same time which has long been an issue
  • improve category management by having clearly defined zones
  • allow us to introduce improved security measures [redacted]
  • introduce a shop that is aligned to our brand with new colour ways and point of sale
  • improve flow from the exhibition area and give a better connected interaction of the exhibition and its related products
  • increased high price  point products with lockable units
  • allow us to study what we can maximise in this space to inform Project Alfred, our project which seeks to redevelop the building eg should we move the shop in that project or leave it by the exhibition space

We have been busy with lots of small but important detail such as moving key infrastructure, planning how to run a pop up shop in the front hall during the work and how to work with the exhibition team who will be in derig mode.

We expect a significant increase in sales and the hard work begins once the build is complete. We’ll have transformed the space which are in effect is our foundations and we can now set about building a very successful retail offer from these strong beginnings.

Ill let you know how we get on….onwards






New locker alert

Photo showing grey lockers

Adding additional lockers to our museums is a top 5 request from the public and staff alike. On Wednesday the 20th September we installed new lockers at Bristol Museum & Art Gallery and M Shed.

Until this week we only had 8 lockers at Bristol Museum & Art Gallery which is not exactly lots when you have 400,000 plus visits. At both museums the lockers have been finished in a suitable RAL colour way. We’ve introduced a £1 non-refundable fee which will initially re-pay the cost of lockers then be used to support our work. Slow money but sure money.

The main considerations for lockers are:

  • Custom brand colours
  • Coin retention lockers
  • The number of doors per locker – 2, 3 or 4 (more lockers more money but less useful if size is important)
  • installation
  • Location in the building
  • Disclaimers and cost messaging

The install didn’t quite go to plan. I asked for lockers. I got lockers. However I also needed the following which I hadn’t specified:

  • Numbered lockers – inserts so that the public can remember which locker they used
  • Numbered key fobs – the public need to know which key they have
  • Nuts and bolts – to connect each locker together and to the wall to eliminate the chance for the locker to tip over

I purposely located a bank of lockers in the corridor so that they’ll be in the sightline of visitors to the front hall. Previously the lockers were tucked away and a constant frustration for visitors. Regular readers will know one of my favourite quotes “Address the user need and the business need will be clear”.

Retail will be responsible for collecting the income with this new welcome income stream.

I was chuffed when one of our Visitor Assistants said “I’ve been here over 10 years and never thought I’d see the day we added extra lockers”.

If you remember to address the bullet points above you’ll have a smooth installation. Good luck.


As of Sept 2018 we successfully recouped the costs within six months and produced a 1x return (basically doubled revenue to cost) and expect a 2x return now annually.

Results of running a shop in the front hall

Photo of our front hall shop

Just before summer started I wrote about taking the plunge having an additional small shop in the front hall at Bristol Museum and Art Gallery throughout the summer. Summer is over and so is our shop, for now. This post is a summary of performance. In short, the shop was worth doing with the following results:

  • 10% of total sales for the month or £5,300 net sales
  • 939 orders
  • 2% conversion (confirmed it was additional sales not just taking sales from main shop)
  • Same ATV as main shop despite selling far fewer products
  • Staffing costs were covered by moving the second retail assistant from the main shop
  • £400 on a whacking huge shop sign (pictured) and a few units to supplement existing available units
  • Answered countless public enquiries

I would have been kicking myself if we hadn’t tried to push the retail needle and i’m glad we did. We had our most successful month on record and met our income target. The front hall shop helped us over the line in this respect. We started on day 1 of the school summer holiday without much of a plan other than a hunch the products should be suitable for kids and tourists. Over the course of the project we chopped and changed the products as the teams powers of observation dictated.


Finding staff at short notice proved to be a challenge with a few moments but fortunately the team including our brilliant casual pool came to the rescue. I asked the team for feedback throughout the project. Everyone on the front hall shop said they enjoyed the shifts and kept busy in the quiet periods by pricing and preparing products. As expected they also answered lots of public enquiries and raised awareness of our retail offer. The main shop coped with having one person instead of two but this made their work full-on and its much appreciated. We will use the feedback to see how we can better ease the load in the coming year, especially dealing with deliveries.

Product selection

We used the existing products from our range and started with best sellers with a Bristol theme. We thought this would appeal to the tourists. However the Bristol theme didn’t really push the needle so we switched to more ‘kids’ and ‘home’ which proved successful.  I really wanted to try a selection of jewellery but the hall is used frequently for evening events so we decided this could wait until a future project.

Positive fringe benefits

We don’t have staff permanently in our front hall anymore so having staff here was good for the public enquiries. The hall is large and having visitors milling around the shop gave a nice vibe to the space. Staff received lots of positive comments about the offer and many said they’d be back for Christmas shopping. Some visitors completely miss the fact we have a shop so this made sure 100% knew we had an offer.

Next time

I have decided that the front hall shop should come back at high visitor times so October half-term and then from late November until the end of the school holidays. We need to plan the range further in advance and be very mindful that December is peak evening event season so everything must be easily movable. We did indeed push the needle.


Pushing the needle: a shop in our fron thall

Photo of front hall shop on day 1

Each year until at least 2022 we need to grow our retail revenue and profitability by 10% or greater. At Bristol Museum & Art Gallery our retail is typical of the sector in converting about 10% of visitors into shoppers. For us, this will not be enough to do what we need to do. But we must hit our target.

To work out if you have any hope of making your target you need to do a little maths:

  • Divide the total number of visitors by the expected number of shoppers to get your conversion rate e.g 10%
  • Take your expected Average Transaction Value (ATV) and multiple this by your conversion rate to get a baseline amount of sales revenue you expect to generate – remember sales are not profit
  • Take your baseline expected sales and divide by the expected visitor numbers to get your Spend Per Head (SPH)

The problem should be obvious…. an average is basically an assumption that IF A + B + C happens then success!

It is good to have a working set of data, averages and assumptions to use as a baseline but we can’t run our retail on assumptions and need to try everything to maximise sales. I know that to have more confidence in our programme we need to keep poking the box to try to positively increase our conversion and thus sales.

The best way to get the conversion up to is raise awareness to the visitor that we have a shop offer and try to affect behaviour. Our front hall has 100% of our visitors passing through.

During the summer holiday we’re testing the idea that more exposure should increase sales by introducing a small shop located in the front hall. This project will inform our future plans for growing the retail business which may include moving the location of the main shop.

The outline business case is below:

  1. A visually attractive retail offer located in the front hall will undoubtedly increase awareness of a retail offer in the Museum. Although improvements have been made to the entrance of the main shop including introducing a large bookstand and jewellery stand, some visitors still miss the retail offer. This project will hopefully encourage impulse buying and whilst signposting to the main shop either now or for Christmas.
  2. As an example we used a pop up shop for Chinese New Year which increased sales by 18% on the previous year 2015-2016. This also avoided the big queues in the shop and congestion in the entrance and exit in the shop which aids the visitor experience
  3. There will be almost no investment costs, as we already have the equipment needed to set up a small shop.
  4. Staff costs will be met using the current budget by moving the second retail assistant to the front hall. The downside of course will be less ability to deal with deliveries and some customers who want assistance.
  5. The shop in the front hall will be open daily during summer school holidays 24th July -1st September and be mobile enough to be able to close and move for any evening events.
  6. The space taken for the shop would measure 9ft (2.7 mtrs) by 11ft (3.4 mtrs).
  7. Meeting the potential for growth – going on last year’s August figs of ATV at £6.18 an increase of 5% conversion could lead to an increase £10,000.
  8. Hopefully a secondary benefit is that this will reduce queue pressure on the main shop
  9. Use the opportunity to engage the visitors about our cafe and exhibition Pliosaurus

So how did we do the first week?

Sales in the front hall accounted for 13% of sales. The first two days we were finding our feet so i’d expect this to increase in the coming week. At this stage we can’t tell if this has simply taken a share of the main shop’s sales or increased sales overall… we need to compare a few data sources and experiment with the product range…. watch this space!

How we did it: automating the retail order forms using Shopify.

*explicit content warning* this post makes reference to APIs.

THE PROBLEM:  Having set ourselves the challenge of improving the buying process  , our task in Team Digital was to figure out where we can do things more efficiently and smartly. Thanks to our implementation of Shopify, we have no shortage of data on sales to help with this, however the process of gathering the required information to place an order of more stock is time consuming – retail staff need to manually copy and paste machine-like product codes, look up supplier details and compile fresh order forms each time, all the while attention is taken away from what really matters, i.e. which products are currently selling, and which are not.

In a nutshell, the problem can be addressed by creating a specific view of our shop data – one that combines the cost of goods, with the inventory quantity (amount of stock left) in a way that factors in a specific period of time and which can be combined with supplier information so we know who to order each top selling product from, without having to look anything up. We were keen to get in to the world of Shopify development and thanks to the handy Shopify developer programme documentation & API help it was fairly painless to get a prototype up and running.

SETTING UP: We first had to understand the difference between public and private apps with Shopify.  A private app lets you hard code it to speak to a specific shop, whereas the public apps need to be able to authenticate on the fly to any shop. With this we felt a private app was the way to go, at least until we know it works!

Following this and armed with the various passwords and keys needed to programmatically interact with our store, the next step was to find a way to develop a query to give us the data we need, and then to automate the process  and present it in a meaningful way. By default Shopify provides its data as JSON, which is nice, if you are a computer.

TECHNICAL DETAILS: We set up a cron job on an AWS virtual machine running Node and MongoDB. Using the MEAN stack framework and some open source libraries to integrate with Google Sheets, and notably to handle asynchronous processes in a tidy way. If you’d like to explore the code – that’s all here. In addition to scheduled tasks we also built an AngularJS web client which allows staff to run reports manually and to change some settings.

Which translates as: In order to process the data automatically, we needed a database and computer setup that would allow us to talk to Shopify and Google Docs, and to run at a set time each day without human intervention.

The way that Shopify works means we couldn’t develop a single query to do the job in one go as you might in SQL (traditional database language). Also, there are limitations in how many times you can query the store. What emerged from our testing was a series of steps, and an algorithm which did multiple data extractions and recombination’s, which I’ll attempt to describe here. P.S. do shout if there is an easier way to do this ;).

STEP 1: Get a list of all products in the store. We’ll need these to know which supplier each product comes from, and the product types might help in further analysis.

STEP 2: Combine results of step one with the cost of goods. This information lives in a separate app and needs to be imported from a csv file. We’ll need this when we come to build our supplier order form.

STEP 3: Get a list of all orders within a certain period. This bit is the crucial factor in understanding what is currently selling. Whilst we do this, we’ll add in the data from the steps above so we can generate a table with all the information we need to make an order.

STEP 4: Count how many sales of each product type have taken place. This converts our list of individual transactions into a list of products with a count of sales. This uses the MongoDB aggregation pipeline and is what turns our raw data into something more meaningful. It looks a bit like this, (just so you know):

STEP 5: Add the data to a Google Sheet. What luck there is some open source code which we can use to hook our Shopify data up to Google. There are a few steps needed in order for the Google sheet to talk to our data – we basically have our server act as a Google user and share editing access with him, or her?. And while we are beginning to personify this system, we are calling it ‘Stockify’, the latest member of Team Digital, however Zak prefers the lofty moniker Dave.

The result is a table of top selling products in the last x number of days, with x being a variable we can control. The whole process takes quite a few minutes, especially if x >60, and this is due to limitations with each integration – you can only add a new line to a Google sheet once / second, and there are over 500 lines. The great thing about our app is that he/she doesn’t mind working at night or early in the morning, and on weekends or at other times when retail managers probably shouldn’t be looking at sales stats, but probably are. With Stockify/Dave scheduled for 7am each morning we know that when staff look at the data to do the ordering it will be an up to date assessment of the last 60 days’ worth of sales.

We now have the following columns in our Google Sheet, some have come directly from their corresponding Shopify table, whereas some have been calculated on the fly to give us a unique view of our data and on we can gain new insights from.

  • product_type: (from the product table)
  • variant_i:d (one product can have many variants)
  • price: (from the product table)
  • cost_of_goods: (imported from a csv)
  • order_cost: (cost_of_goods * amount sold)
  • sales_value: (price * amount sold)
  • name: (from the product table)
  • amount sold: (transaction table compared to product table / time)
  • inventory_quantity: (from the product table)
  • order_status: (if inventory_quantity < amount sold /time)
  • barcode: (from the product table)
  • sku: (from the product table)
  • vendor: (from the product table)
  • date_report_ru:n (so we know if the scheduled task failed)

TEST, ITERATE, REFINE:  For the first few iterations we failed it on some basic sense checking – not enough data was coming through. This turned out to be because we were running queries faster than the Shopify API would supply the data and transactions were missing. We fixed this with some loopy code, and now we are in the process of tweaking the period of time we wish to analyse – too short and we miss some important items, for example if a popular book hasn’t sold in the last x days, this might not be picked up in the sales report. Also – we need to factor in things like half term, Christmas and other festivals such as Chinese New Year, which Stockify/Dave can’t predict. Yet.

AUTOMATIC ORDER FORMS: To help staff compile the order form we used our latest Google-sheet-fu using  a combination of pick lists, named ranges and the query function to lookup all products tagged with a status of “Re-order”

A list of suppliers appears on the order form template:

and then this formula looks up the products for the chosen supplier and populates the order table:

“=QUERY(indirect(“last_60_days”&”!”&”11:685″),”select G where M='”&$B2&”‘ and J=’re-order'”)”

The trick is  for our app to check if the quantity sold in the last x days is less than the inventory quantity, in which case it goes on the order form.

NEXT STEPS: Oh we’re not done yet! with each step into automation we take, another possibility appears on the horizon…here’s some questions we’ll be asking our system in the coming weeks..

  • -How many products have not sold in the last x days?
  • -If the product type is books, can we order more if the inventory quantity goes below a certain threshold?
  • Even if a particular product has not sold in the last 60 days, can we flag this product type anyway so it gets added to our automatic order form?
  • While we are at it, do we need to look up supplier email addresses each time – cant we just have them appear by magic.

…furthermore we need to integrate this data with our CRM…..looks like we will be busy for a while longer.




Retail: Improving the buying process

Customers buy what they can pick from the shop floor or online catalogue. Not what is “on its way from a warehouse” or “gathering dust” on our stockroom shelf. Stock not available to the customer is therefore waste. A waste of committed money (cash flow concern which  immediately introduces risk) and a waste of space in our shop stockroom which in turns reduces overall shop floor space and slows staff looking for product.

Because “Buying” is the most critical of the four pillars of retail, it seems the sensible place to focus our attention on to gain further improvement. I’m going to challenge ourselves to use 2017-18 to maximise our buying by refining the workflow.    This will be a collaboration between me, retail, user research and our digital team.

At present we do our buying like any other retailer, we order by supplier when we feel or notice heavy product depletion. Furthermore at any one time we’re “holding” about 10% of our annual total stockholding in our stockroom and another 10% on the shop floor. If it is on the stockroom shelf it has zero chance of being sold.  In addition to having a costly quantity of product hanging around, the space used to hold our product could potentially be converted into public shop floor space. For example at Bristol Museum and Art Gallery there is a false wall which conceals our stockroom which is about 8m in length with a depth of 1.4m. If we’re about to improve how we buy, it is possible to push this wall back further and gain that space as public floor space that could be used for 2-4 nesting tables worth £10,000+. The challenge of course is that would reduce our total stockroom space by 2/3.

If we can nail our understanding of what to buy and when, this would unlock the potential to carry out operation “shrink the stockroom”. Order exactly what we need when we need it and not before.

The upsides would be:

  •  Move to a new process of ordering “just in time”
  • reduce stockroom size thus freeing up new floor space for customers worth £10,000+
  • reduce “out of stock” scenario by improving the buying process
  • order by need not assumption
  • reduce ordering time across the full year
  • reduce owning costly stock that may not sell which also takes up space
  • maximise available money set aside for buying products that sell
  • Reduce time lost by staff who have to hunt around a big stockroom
  • Heavily reduce human interaction which will reduce our cost per transaction and help us move to being digital by default
  • Allow retail manager to focus on other tasks