Category Archives: Retail

Pushing the needle: a shop in our front hall

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

 

Challenge: Improving the buying process for retail

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 concerns and thus an introduction of 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 work on iterating to gain further improvement. I’m going to challenge ourselves to use 2017 to maximise our buying workflow.

At present we do our buying like any other retailer, we order by supplier when we feel or notice heavy product depletion. Furthermore we “hold” about 1/4 of our stockholding in our stockroom. 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 increase our understanding of what to buy and when this would unlock the potential to carry out operation “shrink the stockroom”.

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

The first major step forward has been moving to a fully digital ordering process to minimise the steps required for that particular part of the system. We use a combination of Shopify reporting and google spreadsheets to do this. Our internal system for generating purchase orders currently prevents us using automated ordering directly to the supplier.

I think the next step will be to understand the rhythm of how often each product is sold AND what minimum order size suppliers can accept.

Myself and the team will write about our successes and challenges throughout the year. It won’t be easy but nothing worth doing is!

The week our office turned into a photography studio!

With one of the main aims this year being to improve our online shop, myself and Darren decided to improve and update some stock photos. We enrolled in a crash course from resident photographer David Emeney and by the end of session thought we’d be able to do it, easy.

However, we came to find that photography is not as easy as it seems! First came the issue of space. Although David kindly allowed us to use his studio in the basement, with no computer nearby to check pictures and in fear of messing with any of his equipment, we though it may be best  if we set up a studio a little more close by.IMG_20170109_154103292_HDR

In true Blue Peter style Darren and I set about creating our own in-office photography studio by collecting bits and pieces from around the museum to mirror the one in the basement. Cardboard tubes were stapled together acting as a rod to hold the white background in place, this was held up by string wrapped multiple times around our window bars, counter tops were cleaned as to not make the paper dirty and even a staff noticeboard was used behind the paper to block out any natural light. Of course our office had to be rearranged first to fit such a project inside, a move which would have me non-stop sneezing for a few days as the settled dust was disturbed!

After a while of playing with the camera’s settings trying to find the right ones, we set to work to photograph stock. With thanks to Debs for letting us borrow geology’s light, the products came out well and the online shop now looks a lot smarter for it. Having this type of light was key to taking a good image, the close proximity between the product and source of light and changing the camera’s white balance when needed added extra quality.

It was a really good experience getting to know the manual settings of a camera and how each product requires a slight adjustment, also to be up to date with what products we currently have in store. I look forward to doing more stock photo shoots in the future and hope, at some point, to have all products photographed like this to keep a consistent look for the online shop.

£70 stoneSSGB edt 4559warrior duck 4857

Our shopify setup for running POS in our gift shops

Across our museums and archive we sell in-person using an iPad and Shopify app. I thought i’d share with you our Shopify point of sale (POS) set up seeing as we’ve now rolled it out to five locations. The setup cost is just under £1000 for the kit and running costs are approx £3000 per year for the Shopify software, reporting and add-ons. It is worth mentioning that originally we used the basic Shopify package which is $29 per month (plus $40 for retail pos) but once we started to get serious with our retail management we upgraded to the “Advanced Shopify” which is approx £2800 per year.

Hardware

We purchased our kit from POS hardware as a bundle for approx £455 (ex vat) excluding the iPad and card reader. Most small merchants skip on getting the receipt printer and prefer to use “email this receipt”.

  • iPad air 2 – £379
  • Bluetooth barcode reader
  • Standard till drawer
  • Receipt printer
  • Contactless Credit/debit card reader with Applepay (approx £20 per month rental)
  • iPad secure stand
  • Router to avoid public wifi and maintain security – fitted by IT services

Software

A good thing about shopify is that you can scale up your options with the click of a button to install new features. Once you pay you can also install it on as many devices as you like which is great for pop-up shops.

Shopify retail $40 per month and is essential for turning your standard shopify account into a retail POS
Advanced Shopify £2800 per year which we use for the “reporting” features
– App – ALT Text FREE used to automatically generate image ALT text for our online shop
– App – Low Stock Alert FREE sends us an email warning which products are running low
– App – Profiteer $15 per month used to add the cost of goods to all products so we can understand our profit
– App – SEO Doctor FREE used to improve our online shop
– App – Shopify Theme Tool which we use for our custom online shop theme

Top tips

Updating shopify or iOS. It normally takes about 45mins to update the app shopify AND update iOS so never do this during hte business day..also avoid Friday or weekends if you have IT support who work Mon-Fri

If possible it is worth having a second iPad in case the primary device falls over for any reason. I’d also suggest you upgrade the software and test it using the second device before unleashing the latest version on the primary device

Shopify has 24/7 support so when I hit a snag I normally use their live chat to help get it resolved

100 days of using Shopify POS tills

We’ve just passed our 100 day mark since the introduction of Shopify till system in our retail shops. In case you don’t intend on reading the whole post, i’ll tell you now that we’re still using Shopify and i think it’s safe to say it is a success.

In this post I want to cover us going live, what features we use at the moment and what our next steps are.

Choosing Shopify

Our previous system was never properly setup and as a team we didn’t take advantage of its potential. I could of stuck with it but I saw this as an opportunity to explore using the latest shopping cart technology from the web. I’m a big fan of popular tools that I’ve seen ‘scale’ regardless of the sector. I had heard about lots of arts/museum sector specific approaches which quite frankly scare me. As a sector we aren’t really all that ‘special’ when it comes to doing normal things like running a shop. So instead of looking at any of these potentially risky solutions where the market is small and we can get tied to one small supplier I just went straight to looking at what local shops and market stalls were using as i’m treating our retail as a small business so what better place to look. All of these were using web services via tablet or phone. Having attended a Shopify workshop back in June 2014 run by Keir Whitaker I felt that it had what the other systems had to offer so why not use this – no long spec document just a nose for good software and services.

Fast forward to launch

After an initial alpha use of Shopify using the free trial (tip: use the 7 day trial as your alpha test so you have no money to front) I felt happy to use Shopify with the public. Our fallback was to keep the old system plugged in and as we use a separate card reader, we could easily manually take orders with that and a calculator if we really got jammed up.

We decided to launch in early May at Bristol Museum & Art Gallery. We decided to do one shop first and then if all went well we’d do live at M Shed, followed by our tills used by the exhibition team.

As Shopify is pretty user friendly we showed Helen how to add products, how to make a custom sale and how to cash up at the end of the day in less than 20mins. It turns out that Helen had never used an iPad before, let alone Shopify. But within minutes Helen was comfortable enough to plough on with only a little arm twisting from me.

Rather than add 100s of products to the inventory we decided to use the ‘custom sale’ option on the first day and then add any purchased products to the inventory retrospectively. As a word of advice, i think this approach makes the most sense instead of committing many hours to adding products to the Shopify inventory which you may or may not run with. Instead, add as you go.

On the first day I made sure that we had both Zahid and myself available. I spent the first ‘live’ hour down in the shop. Within an hour it was clear that I wasn’t needed. By the day of the first day Helen and Zahid knew way more than I did – in this type of case i’m glad to be made redundant!

After two days Helen asked us to remove the old system as she was very happy with how things were progressing. We have a small retail team of four part-time staff and a small bank of casual staff. Within 2-3 weeks I was getting staff thanking me for introducing the new system. In my previous two years I’ve never had such positive feedback. After the third week we also replaced our M Shed till too. On week six we also used this for our third till which is used to buy tickets for our exhibition (William Hogarth: Painter and Printmaker).

Helpful documentation and support

One of the things I love about modern day web services is that they normally offer good documentation and Shopify is no different. This not only helps us to learn about how to best use the service but saves any of us having to write lengthy support documentation. I’ve since used their live online chat a few times when i’ve got stuck and it’s 24/7. A service i’m sure many of the museum POS vendors can only dream of offering. You can ring, live chat, email or use the forums. All of which help staff when none of us digital types are around, which is the way it should be.

Mobile app for the win

I have a great retail team led by Helen Lewis. In theory I just need to know our current financial position. The mobile app lets me see live sales income for the last 90 days. This alone is a leap forward for POS and i get ‘POS envy’ now by all other retail managers whenever I show them. Furthermore I can see what product inventory level is is at anytime and i can scan barcodes to make sales if i really wanted to. I’m currently keeping an eye on our Hogarth mugs, scarfs, a book by Louise Brown and drinks. All new products that I like to track.

Reporting sales

To keep the cost down there a number of features which don’t come as standard and reporting is one of them. We have paid for the reporting features which we mostly use for splitting vat/non-vat and exhibition tickets at this point. In the next few months we will really get our head around what reports we want.

A few problems and issues

We’re very happy so far with our shopify service but there have been a few teething problems worth mentioning.

We hit our first major technical snag – till drawer says No!

Our exhibition till has a unique challenge compared to our retail shops. We have over 50 visitor assistants. For each day of the exhibition we may have any one or more of them on the till. This poses a few challenges, mainly around processes and training. Some people had no problems but others really struggled with the idea of an iPad for a till. Nothing too bad. But then it happened. I got a call to say that the exhibition till wasn’t working. I went down and sure enough it was working. False alarm right? Another call 30mins later. This time I could see the problem. Although we could use the Shopify app, the till cash drawer refused to open. Turn it off, turn it back on. Boom. Fixed…. so I thought. This kept happening, time after time. Anyway it turns out that although Shopify will run perfectly happily offline, the cash drawer NEEDS wifi to be triggered to open. A major problem that made lots of visitor assistants quite reluctant to use the till. The problem only occurred on one of the four tills. Zahid tracked down the issue to the router. Apparently there is a known issue with some routers – despite it working fine with the same router elsewhere. Zahid swapped out the router and the problem hasn’t come back. Luckily for us we could use the shop as a fallback till but this wasn’t the best customer service period.

Costs of goods isn’t standard

By default there isn’t a feature to include the ‘cost of goods – COGS’ which are essential for knowing the price you paid against the retail price and thus your profit margin. How did I miss that in the alpha! Luckily one of the reasons I chose Shopify was for its adaptability. Shopify has a useful feature to allow them or third parties to make apps for beefing up the default service. One of these, deepmine looks like it has COGS so we’ll be trialling this very soon.

Not many hints and tricks yet

I haven’t found much information about using Shopify POS as it is still quite new. This means that it hasn’t been super fast to find answers to some of our issues. One of the reasons i’m writing this is to increase that information pool. Oh and there is no public roadmap for what’s coming so follow the blog to stay in the loop

What’s coming next

Now that we’re comfortable with Shopify we’re starting to turn our attention to the next phase of work.

  • Trial deepmine app to get COGs and deep reporting
  • Setup better custom reports to help staff
  • Offer group workshops on basic training and reporting
  • Add inventory levels to all products
  • Add photos to all products
  • Explore email upsell and sales offers

Get in touch

I’ve had several chats with other museums who spotted by last blog post asking about Shopify. Please do get in touch by phone 0117 922 3571 or zak.mensah@bristol.gov.uk if you want me to help you with anything around our use of Shopify. We’ll also be happy to be paid consultants to set up your service if you need a proper hand.