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.

 

 

QR codes and triggered content in museum spaces – in 2018

Any other museum digital people getting an influx of requests for QR codes to put in galleries recently? No? IS IT JUST US?!

After thinking that QR codes had died a death a few years ago, over the last few months we’ve had people from lots of different teams ask for QR codes to trigger content in galleries, for a variety of uses such as:

  • Sending people to additional content to what’s in an exhibition, to be used while in the gallery e.g. an audio guide
  • Showing the same content that’s in the exhibition but ‘just in case’ people want to look at it on their phones
  • Sending people to content that is referenced in exhibitions/galleries that needs a screen but doesn’t have an interactive e.g. a map on Know Your Place

After an attempt to fend them off we realised that we didn’t really have any evidence that people don’t use them. At least nothing recent or since the introduction of automatic QR code scanning with iOS 11 last year (thanks for that, Apple). So, we thought we’d test it out, making sure we’re tracking everything and also always providing a short URL for people to type into browsers as an alternative.

In most cases, it’s as expected and people just aren’t using them. They’re also not using the URL alternatives either, though, which maybe suggests that people don’t really want to have to go on their phones to look at content and are happy with reading the interpretation in the gallery. Controversial, I know. (Or maybe we need to provide more appealing content.)

However, then we come to our recent Grayson Perry exhibition at Bristol Museum & Art Gallery, which had audio content which was ‘extra’ to what was in the exhibition. We provided headphones but visitors used their own devices. A key difference with this one though was that our front of house team facilitated use of the QR codes, encouraging visitors to use them and showing them what to do. As such, the six audio files (there was one with each tapestry on display) had 5,520 listens altogether over the course of the exhibition (March – June), over 900 each on average.

Whilst it’s great that they were used – it threw us a bit – the flip side of this is that it was only in an instance where it’s being facilitated. I’m not partuclarly keen on using something that we’re having to teach visitors how to use and where we’re trying to change users’ behaviours.

There’s also some more here around the crossover between online and gallery content (should we be using one thing for both, are they different user cases that need to be separate) which we’re talking about and testing more and more at the moment, but that’s one for another post.

We’d be really interested to hear your thoughts on triggered content. Do people even know what QR codes are? Are ‘just because we can and they’re no/low cost’ reasons enough to use them? How do you do triggered content? Is this unique to medium-sized museums or are the big and smaller guys grappling with this too? Or is it really just us?!

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

ChangeLog

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

M Shed Matchboxes – an alternative audio tour

This is an Interview with Tom Marshman about an alternative audio tour available at M Shed

Q: Can you describe the new resource you have created?

A: Working together with Rowan Evans (sound artist) we have created an alternative audio tour of the M-shed.

The tour connects up some of the stories I have collected for my performance work within the exhibition about Bristol, sharing stories I heard when interviewing older LGBT people in Bristol about the stories that lie at the roots of their LGBT identity.

The stories are funny and touching, and I’ve presented them very lyrically so the tour almost becomes a long poem that moves you around the first and ground floors of the M-shed.

If you would like to do the tour the audio devices are kept behind the information desk on the ground floor, all you need to do is ask for one from a member of staff. The audio devices are encased in vintage matches, so you collect your headphones and match box and move around the space.

The piece was originally a live performance walk around the old city, around St Nicholas market so a lot of the stories are based there, most significantly the Radnor Hotel, which was a known gay venue from the 1930’s onwards.

 

Q: What is it about audio that made you decide to use this medium?

A: Each story is represented by the sound of a match striking; the stories burn brightly and quickly like a match, sharing a story before you move on to the next story. The idea for this came from one particular story where a man met his life partner by being asked for a light.

I really wanted people to feel like they were heading back in time with this work and that there was a retro vibe going on. I didn’t want them walking around the galleries with cutting edge technology I wanted something more tactile and evocative of stories people tell, this is why I chose the matchbox.

 

Q:  How does your product differ from a usual museum audio guide?

A: In my work I am not so concerned with facts and figures, what I want to do is tell a good story and in particular the stories of older LGBT people which could soon be lost.

I think they add a new texture to the exhibits in the M Shed, bringing out the human stories within the objects and focusing on LGBT stories. LGBT stories are often whitewashed in museum versions of history, where we told the stories of the ‘powerful white upper class men’ instead. This work, I think, helps address this imbalance, and adds a new range of stories so that M Shed represents the diverse and exciting Bristol we live in.

These are stories I think everyone will enjoy hearing the stories, although some of the language is a bit racy so over 16’s only!

 

Q: Do you think the technology presents any barriers to access?

A: As an artist I’m based at the Pervasive Media Studio within Watershed Cinema where many artists and technologists are exploring ways to work with technology in new and exciting ways.

Amusingly, I am a technophobe, so for me to understand it, it has to be very simple. Because of this, what we have created is super easy to use, the only thing you have to do is turn it on, find the right volume, and follow the directions of where to move to within the audio tour. If people have smartphones they can also request a link or scan a QR code, to find the tour online. So technically they don’t need to have the matchbox, but I feel that spoils the fun slightly!

The important thing for me, when I am working with technology, is that it doesn’t get in the way of the stories and that the technology supports it, rather than presenting a barrier. And if anyone finds any teething problems, then I hope they’d mention it to the information desk so we can improve accessibility.

 

Q: How do you think the museum could learn from this project when developing their own audio resources?

A: The M-shed is not just about Bristol as a place, it’s also about the people of Bristol. And I love that it places importance on a wide-range of people too, not just people that are deemed to be ‘the great and the good’. I think our project reinforces that and tells us about a group of people whom you don’t often hear about.

I hope adding this will bring new LGBT audiences into museums to connect them to our history, as well as introducing non-LGBT museum-goers to it, all in an engaging and fun way.

As an artist I love working in museums because they are rich in stories, and I think it’s important to find new ways to share and celebrate within the museums. 

Move Over Darling talks about people’s lives, deaths, loves, friendships and sex lives in a way that many museums don’t. The way our society treated LGBT people up until very recently has become a shocking and shameful secret history, and projects like this one can help museums tackle these difficult issues as well making sure the positive stories of LGBT people are not lost.

There’s a personable quality to the work I make too. All the people I tell my stories about on the audio tour I have met, I know them and we have exchanged our stories in face-to-face conversation. Though you don’t get to hear my stories on the tour, the human exchange during this research has indelibly influenced and shaped how I tell these stories. Sadly a big contributor to the content passed away last year, it is nice that his stories are present in the museum in this way.

 

Q:  How can people access the content?

A: You can collect the matchboxes from the front desk at the M-shed anytime they are open, you can also find it online here and listen as you walk around the museums.

This is an ongoing part of the exhibition so hopefully my voice will be in the museum forever or at least until it doesn’t feel relevant anymore. Perhaps in a few years I will add more stories, we’ll see!

 

 

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

 

 

 

 

 

Preserving the digital

From physical to digital to…?

At Bristol Culture we aim to collect, preserve and create access to our
collections for use by present and future generations. We are increasingly dealing with digital assets amongst these collections – from photographs of our objects, to scans of the historical and unique maps and plans of Bristol, to born-digital creations such as 3D scans of our Pliosaurus fossil. We are also collecting new digital creations in the form of video artwork.

Photo credit Neil McCoubrey

One day we won’t be able to open these books because they are too fragile – digital will be the only way we can access this unique record of Bristol’s history, so digital helps us preserve the physical and provides access. Inside are original plans of Bristols most historic and well-known buildings including the Bristol Hippodrome, which require careful unfolding and digital stitching to reproduce the image of the full drawing inside.

Plans of the Hippodrome, 1912. © Bristol Culture

With new technology comes new opportunities to explore our specimens and this often means having to work with new file types and new applications to view them.  

This 3D scan of our Pliosaurus jaw allows us to gain new insights into the behavior and biology of this long-extinct marine reptile.

Horizon © Thompson & CraigheadThis digital collage by Thompson & Craghead features streaming images from webcams in the 25 time zones of the world. The work comes with a Mac mini and a USB drive in an archive box and can be projected or shown on a 42″ monitor. Bristol Museum is developing its artist film and video collection and now holds 22 videos by artists including Mariele Neudecker, Wood and Harrison, Ben Rivers, Walid Raad and Emily Jacir ranging from documentary to structural film, performance, web-based film and video and animation, in digital, video and analogue film formats, and accompanying installations.

What could go wrong?

So digital assets are helping us conserve our archives, explore our collections and experience new forms of art, but how do we look after those assets for future generations?

It might seem like we don’t need to worry about that now but as time goes by there is constant technological change; hardware becomes un-usable or non-existent, software changes and the very 1s and 0s that make up our digital assets can be prone to deteriorating by a process known as bitrot!.  Additionally, just as is the case for physical artifacts, the information we know about them including provenance and rights can become dissociated.  What’s more, the digital assets can and must multiply, move and adapt to new situations, new storage facilities and new methods of presentation. Digital preservation is the combination of procedures, technology and policy that we can use to help us prevent these risks from rendering our digital repository obsolete. We are currently in the process of upskilling staff and reviewing how we do things so that we can be sure our digital assets are safe and accessible.

Achieving standards

It is clear we need to develop and improve our strategy for dealing with these potential problems, and that this strategy should underline all digital activity where the result of that activity produces output which we wish to preserve and keep.  To rectify this, staff at the Bristol Archives, alongside Team Digital and Collections got together to write a digital preservation policy and roadmap to ensure that preserved digital content can be located, rendered (opened) and trusted well into the future.

Our approach to digital preservation is informed by guidance from national organisations and professional bodies including The National Archives, the Archives & Records Association, the Museums Association, the Collections Trust, the Digital Preservation Coalition, the Government Digital Service and the British Library. We will aim to conform to the Open Archival Information System (OAIS) reference model for digital preservation (ISO 14721:2012). We will also measure progress against the National Digital Stewardship Alliance (NSDA) levels of digital preservation.

A safe digital repository

We use EMu for our digital asset management and collections management systems. Any multimedia uploaded to EMu is automatically given a checksum, and this is stored in the database record for that asset. What this means is that if for any reason that file should change or deteriorate (which is unlikely, but the whole point of digital preservation is to have a mechanism to detect if this should happen) the new checksum won’t match the old one and so we can identify a changed file.

Due to the size of the repository, which is currently approaching 10Tb, it would not be practical to this manually, and so we use a scheduled script to pass through each record and generate a new checksum to compare with the original. The trick here is to make sure that the whole repo gets scanned in time for the next backup period because otherwise, any missing or degraded files would become the backup and therefore obscure the original. We also need a working relationship with our IT providers and an agreed procedure to rescue any lost files if this happens.

With all this in place, we know that what goes in can come back out in the same state -so far so good. But what we cant control is the constant change in technology for rendering files – how do we know that the files we are archiving now will be readable in the future? The answer is that we don’t unless we can migrate from out of date file types to new ones. A quick analysis of all records tagged as ‘video’ shows the following diversity of file types:

(See the stats for images and audio here).  The majority are mpeg or avi, but there is a tail end of various files which may be less common and we’ll need to consider if these should remain in this format or if we need to arrange for them to be converted to a new video format.

Our plan is to make gradual improvements in our documentation and systems in line with the NDSA to achieve level 2 by 2022:

 

The following dashboard gives an idea of where we are currently in terms of file types and the rate of growth:

Herding digital sheep

Its all very well having digital preservation systems in place, but the staff culture and working practices must also change and integrate with them.

The digitisation process can involve lots of stages and create many files

In theory, all digital assets should line up and enter the digital repository in an orderly and systematic manner. However, we all know that in practice things aren’t so straightforward.

Staff involved in digitisation and quality control need the freedom to be able to work with files in the applications and hardware they are used to without being hindered by rules and convoluted ingestion processes. They should to be allowed to work in a messy (to outsiders) environment, at least until the assets are finalised. Also there are many other environmental factors that affect working practices including rights issues, time pressures from exhibition development, and skills and tools available to get the job done. By layering new limitations based on digital preservation we are at risk of designing a system that wont be adopted, as illustrated in the following tweet by @steube:

So we’ll need to think carefully about how we implement any new procedures that may increase the workload of staff. Ideally, we’ll be able to reduce the time staff take in moving files around by using designated folders for multimedia ingestion – these would be visible to the digital repository and act as “dropbox” areas which automatically get scanned and any files automatically uploaded an then deleted. For this process to work, we’ll need to name files carefully so that once uploaded they can be digitally associated with the corresponding catalogue records that are created as part of any inventory project. Having a 24 hour ingestion routine would solve many of the complaints we hear from staff about waiting for files to upload to the system.

 

Automation can help but will need a human element to clean up and anomalies

 

Digital services

Providing user-friendly, online services is a principle we strive for at Bristol Culture – and access to our digital repository for researchers, commercial companies and the public is something we need to address.

We want to be able to recreate the experience of browsing an old photo album using gallery technology. This interactive uses the Turn JS open source software to simulate page turning on a touchscreen featuring in Empire Through the Lens at Bristol Museum.

Visitors to the search room at Bristol Archives have access to the online catalogue as well as knowledgeable staff to help them access the digital material. This system relies on having structured data in the catalogue and scripts which can extract the data and multiemdia and package them up for the page turning application.

But we receive enquiries and requests from people all over the world, in some cases from different time zones which makes communication difficult. We are planning to improve the online catalogue to allow better access to the digital repository, and to link this up to systems for requesting digital replicas. There are so many potential uses and users of the material that we’ll need to undertake user research into how we should best make it available and in what form.

 

Culture KPIs

There are various versions of a common saying that ‘if you don’t measure it you can’t manage it’. See Zak Mensah’s (Head of Transformation at Bristol Culture) tweet below. As we’ll explain below we’re doing a good job of collecting a significant amount of Key Performance Indicator data;  however, there remain areas of our service that don’t have KPIs and are not being ‘inspected’ (which usually means they’re not being celebrated). This blog is about our recent sprint to improve how we do KPI data collection and reporting.

The most public face of Bristol Culture is the five museums we run (including Bristol Museum & Art Gallery and M Shed), but the service is much more than its museums. Our teams include, among others; the arts and events team (who are responsible the annual Harbour Festival as well as the Cultural Investment Programme which funds over 100 local arts and cultural organisations in Bristol); Bristol Archives; the Modern Records Office; Bristol Film Office and the Bristol Regional Environmental Recording Centre who are responsible for wildlife and geological data for the region.

Like most organisations we have KPIs and other performance data that we need to collect every year in order to meet funding requirements e.g. the ACE NPO Annual Return. We also collect lots of performance data which goes beyond this, but we don’t necessarily have a joined up picture of how each team is performing and how we are performing as a whole service.

Why KPIs?

The first thing to say is that they’re not a cynical tool to catch out teams for poor performance. The operative word in KPI is ‘indicator’; the data should be a litmus test of overall performance. The second thing is that KPIs should not be viewed in a vacuum. They make sense only in a given context; typically comparing KPIs month by month, quarter by quarter, etc. to track growth or to look for patterns over time such as busy periods.

A great resource we’ve been using for a few years is the Service Manual produced by the Government Digital Service (GDS) https://www.gov.uk/service-manual. They provide really focused advice on performance data. Under the heading ‘what to measure’, the service manual specifies four mandatory metrics to understand how a service is performing:

  • cost per transaction– how much it costs … each time someone completes the task your service provides
  • user satisfaction– what percentage of users are satisfied with their experience of using your service
  • completion rate– what percentage of transactions users successfully complete
  • digital take-up– what percentage of users choose … digital services to complete their task

Added to this, the service manual advises that:

You must collect data for the 4 mandatory key performance indicators (KPIs), but you’ll also need your own KPIs to fully understand whether your service is working for users and communicate its performance to your organisation.

Up until this week we were collecting the data for the mandatory KPIs but they have been  somewhat buried in very large excel spreadsheets or in different locations.  For example our satisfaction data lives on a surveymonkey dashboard. Of course, spreadsheets have their place, but to get more of our colleagues in the service taking an interest in our KPI data we need to present it in a way they can understand more intuitively. Again, not wanting to reinvent the wheel, we turned to the GDS to see what they were doing. The service dashboard they publish online has two headline KPI figures followed below with a list of the departments which you can click into to see KPIs at a department level.

Achieving a new KPI dashboard

As a general rule, we prefer to use open source and openly available tools to do our work, and this means not being locked into any single product. This also allows us to be more modular in our approach to data, giving us the ability to switch tools or upgrade various elements without affecting the whole system. When it comes to analysing data across platforms, the challenge is how to get the data from the point of data capture to the analysis and presentation tech – and when to automate vs doing manual data manipulations. Having spent the last year shifting away from using Excel as a data store and moving our main KPIs to an online database, we now have a system which can integrate with Google Sheets in various ways to extract and aggregate the raw data into meaningful metrics. Here’s a quick summary of the various integrations involved:

Data capture from staff using online forms: Staff across the service are required to log performance data, at their desks, and on the move via tablets over wifi. Our online performance data system provides customised data entry forms for specific figures such as exhibition visits. These forms also capture metadata around the figures such as who logged the figure and any comments about it – this is useful when we come to test and inspect any anomalies. We’ve also overcome the risk of saving raw data in spreadsheets, and the bottleneck often caused when two people need to log data at the same time on the same spreadsheet.

Data capture directly from visitors: A while back we moved to online, self-completed visitor surveys using SurveyMonkey and these prompt visitors to rate their satisfaction. We wanted the daily % of satisfied feedback entries to make its way to our dashboard, and to be aggregated (both combined with data across sites and then condensed into a single representative figure). This proved subtly challenging and had the whole team scratching our heads at various points thinking about whether an average of averages actually meant something, and furthermore how this could be filtered by a date range, if at all.

Google Analytics:  Quietly ticking away in the background of all our websites.

Google sheets as a place to join and validate data: It is a piece of cake to suck up data from Google Sheets into Data Studio, provided it’s in the right format. We needed to use a few tricks to bring data into Google Sheets, however, including Zapier, Google Apps Script, and sheets Add-ons.

Zapier: gives us the power to integrate visitor satisfaction from SurveyMonkey into Google Sheets.

Google apps script: We use this to query the API on our data platform and then perform some extra calculations such as working out conversion rates of exhibition visits vs museum visits. We also really like the record macro feature which we can use to automate any calculations after bringing in the data. Technically it is possible to push or pull data into Google Sheets – we opted for a pull because this gives us control via Google Sheets rather than waiting for a scheduled push from the data server.

Google Sheets formulae: We can join museum visits and exhibition visits in one sheet by  using the SUMIFS function, and then use this to work out a daily conversion rate. This can then be aggregated in Data Studio to get an overall conversion rate, filtered by date.

Sheets Add-Ons: We found a nifty add-on for integrating sheets with Google Analytics. Whilst it’s fairly simple to connect Analytics to Data Studio, we wanted to combine the stats across our various websites, and so we needed a preliminary data ‘munging’ stage first.

Joining the dots…

1.) Zapier pushes the satisfaction score from SurveyMonkey to Sheets.

2.) A Google Sheets Add On pulls in Google Analytics data into Sheets, combining figures across many websites in one place.

3.) Online data forms save data directly to a web database (MongoDB).

4.) The performance platform displays raw and aggregated data to staff using ChartJS.

5.) Google Apps Script pulls in performance data to Google Sheets.

6.) Gooogle Data Studio brings in data from Google Sheets,  and provides both aggregation and calculated fields.

7.) The dashboard can be embedded back into other websites including our performance platform via an iframe.

8.) Good old Excel and some VBA programming can harness data from the performance platform.

logos
Technologies involved in gathering and analysing performance data across museums.

Data Studio

We’ve been testing out Google Data Studio over the last few months to get a feel for how it might work for us. It’s definitely the cleanest way to visualise our KPIs, even if what’s going on behind the scenes isn’t quite as simple as it looks on the outside.

There are a number of integrations for Data Studio, including lots of third party ones, but so far we’ve found Google’s own Sheets and Analytics integrations cover us for everything we need. Within Data Studio you’re somewhat limited to what you can do in terms of manipulating or ‘munging’ the data (there’s been a lot of munging talk this week), and we’re finding the balance between how much we want Sheets to do and how much we want Data Studio to do.

At the beginning of the sprint we set about looking at Bristol Culture’s structure and listing five KPIs each for 1.) the service as a whole; 2.) the 3 ‘departments’ (Collections, Engagement and Transformation) and 3.) each team underneath them. We then listed what the data for each of the KPIs for each team would be. Our five KPIs are:

  • Take up
  • Revenue
  • Satisfaction
  • Cost per transaction
  • Conversion rate

Each team won’t necessarily have all five KPIs but actually the data we already collect covers most of these for all teams.

Using this structure we can then create a Data Studio report for each team, department and the service as a whole. So far we’ve cracked the service-wide dashboard and have made a start on department and team-level dashboards, which *should* mean we can roll out in a more seamless way. Although those could be famous last words, couldn’t they?

Any questions, let us know.

 

 

Darren Roberts (User Researcher), Mark Pajak (Head of Digital) &  Fay Curtis (User Researcher)

 

 

 

Discover St. Paul’s Black History in a storymap and walks – test upload

By Tanja Aminata Bah, MA Curator-in-training at  M Shed / Social History Team

Discover Black History in St. Paul’s via a story map and walks
Always wanted to find out more about your local area? Ever wondered where the Bamboo Club was or where the St.Paul’s riots started? St. Paul’s is full of exciting stories waiting to be discovered with this new handy introduction to Black History in the area.

Over the course of the last year, I have been placed with Bristol Culture’s Social History Team at M Shed and Blaise Castle House Museum as part of my MA Curating at UWE Bristol. My interest in Black History, engagement and innovation through digital media in museum spaces lead me to my creating a story map reimagining, preserving and documenting key Black Bristolian stories as my final project. The map offers not just stories, which I gathered via a call out for information, but also showcases some unique, not yet published archival imagery of St. Paul’s and people in the area.
The map is fully integrated with Google Maps for Android and iPhones and can be used here in your browser.

How to use the map?

The map has different layers, which can be navigated via clicking (this icon). The map works best on mobile devices such as Android and iPhones. Simply open this blog post in your browser and click the enlarge icon in the right corner. This will lead you to the Google Maps integration, where you can scroll through the tours and layers of the map on the go.

Walking tours online

I have designed three unique walking tours, giving you insights while you explore the area. If you enable your GPS signal on your phone the tours will even lead you from stop to stop.

  1. Only have an hour to spare? Essential St. Paul’s is your brief 101 to St. Paul’s African Caribbean history since the 1950s. The hour-long stroll follows a leisurely flat course around the heart of St. Paul’s, Grosvenor road? and City Road and offers plenty to see in a short time. If you haven’t got internet on the go you can also download and print out a leaflet here.
  2. If you want to explore for a bit longer you can try out the walk Before The Riots. The walk is flat and will lead you from the Bamboo Club near Portland Square to the Empire Sports Club near St. Agnes, exploring St. Paul’s between 1950 and 1980.
  3. Want it all? The Full Walk will lead you from the Bamboo Club to Ashley Parade on a 2hour uphill course. You will learn all about the African Caribbean community in St.Paul’s and Montpellier before heading to St.Werburghs to learn about two Victorian and Edwardian Black Bristolian families.

St. Paul’s Vibes

While you are out and about exploring you can listen to a selection of my favourite tracks that remind me of St. Paul’s, including many Bristolian artists such as massive attack alongside classics of Calypso and Roots Reggae, which enjoyed a popular following in St. Paul’s.

Finding out more

Got curious and want to find out more about some stories? Here is a handy list to find out more about Black History in and around St. Paul’s.  

The project would not have been possible without my mentor Catherine Littlejohns, curator of Social History, as well as the kind support of Bristol Museum, M Shed, Bristol Archives and UWE staff alongside local stakeholders. Thank you!

Tanja Aminata Bah (Twitter: @jakumata, tanja2.bah@live.uwe.ac.uk)  is a MA Curating Student at UWE Bristol and is placed as curator- in- training with M Shed and the Social History team. In her studies, she is interested in the crossroads between history, representation and digital developments in the heritage field. She holds a B.A. in History and African Studies from University of Cologne.   Her studies are supported by the Rosa Luxemburg Foundation. 

Exhibitions online

We recently (softly softly) went live with Exhibitions Online.

A place to translate our in-house exhibitions for an online audience, we worked with Mike and Luke at Thirty8 Digital to create a narrative structure with scroll-through content and click-through chapters on WordPress. They built in lovely features such as object grids, timelines, slideshows, maps and quotes.

There are a few exhibitions already up, past (death: the human experience) present (Empire through the Lens) and future (What is Bristol Music?). We’ve most recently used it for our European Old Masters gallery to showcase a beautiful painting we have on loan for two years: St Luke Drawing the Virgin and Child by Dieric Bouts (I discovered the Pantone app with this one, taking the red from the gallery to use online. V satisfying). I’m currently working with the exhibition team to get our Pliosaurus! exhibition up – watch this space for some fun things with that one, which we’re hoping to use for interp in our Sea Dragons gallery at Bristol Museum & Art Gallery too.

(For the What is Bristol Music? exhibition opening in May 2018, we’re using WP plugin Gravity Forms to collate peoples’ experiences and pictures of the Bristol music scene to be featured in the physical exhibition. Chip in if you have a story to tell.)

So far, we’ve found the content and arrangement really depends on the exhibition. The idea isn’t to simply put the physical exhibition online (I say ‘simply’, as if it would be) but instead to use the format and content of the exhibition to engage with people in a different environment: albeit one where we’re competing with a thousand other things for people’s attention. Exhibitions which have been and gone have been slightly more challenging, as the content was never intended for this use and has needed some wrangling. The more we use it though the smoother the process is getting, now that we know what we need and it being on teams’ plans as something to consider.

We’re still in the early stages of reviewing analytics to see how people are using it. Initial results are heartening, though, with a few thousand visits having had minimal promotion. At the moment most people are finding it from our what’s on pages (where most of our traffic to the main website is anyway) and we’re thinking about what campaigns we can do to get it out there more.

Any feedback or thoughts, hmu → fay.curtis@bristol.gov.uk

MA Final Project St. Paul’s Black History virtual map – Beta

Hi! I am Tanja, a current MA Curating student at UWE, placed as a Curator in training with the Social History Team in Bristol Culture since January 2017. I am interested in engagement work, black history and innovation through digital media in museums. Aside from assisting the Social History Team, I became involved with mainly digital developments, writing up a project proposal to redevelop the “Big Question Displays” in M Shed to address Brexit on a limited budget, as part of my course and writing up a new online collection highlight on “Green Bristol”. For my final project I aimed to contribute to the service through piloting something new and innovative, but rather budget friendly for the service, that crosscuts my interests.

I decided to develop a customized Google map to document black History in St. Paul’s, capturing some key stories of prominent Black Bristolians that were and are active in the area. Initially planned as a walking tour, one motivation for me was to preserve these stories in an ever-changing St. Paul’s and reimagine these for an online audience, who might want to access the map remotely and as a “gateway” of getting first insides into Black History. While focussing on the African Caribbean community from the 1950s I wanted to design something speaking to digital natives and older generations alike. One of my inspirations was “Black Histories London”, a research project capturing the black presence in London from 1958 to 1981 by Rob Waters, who works for University of Sussex and the Sussex Humanities Lab.

I started contacting local stakeholders in June to September, reached out via our Bristol Museums blog and researched intensively in the archives, while tracing back old material from other service affiliated projects such as the Black Bristolian Learning Resource and the Bristol Black Archives Partnership, to combine information into this new digital offering.

Over the last months, I developed a prototype, that I wanted to share with you as an early beta test to gather feedback. The prototype will be trailed with some members of the Bristol Culture Youth Panel on Wednesday 8th November 2017, in a feedback workshop, as well. Although this prototype is fully functional, it is not yet revised in its size and scope as such. Texts for the stations are still earliest drafts, some pictures will change, and some stations will not end up in the final version.

At the moment I am looking at the following questions:

  • How is the layout and design working?
  • Should I use multiple layers sorting stories after themes, instead of one full layer?
  • Should I do a second map for possible walking routes or work in one map with layers?
  • How are the texts and the stations? Are they fully understandable? Does it contain unneccessary information?

 

The project is already fully integrated and tested into the google map app for Android and iPhones. To access it on the go, the user needs to open this blog post (and later the final blog post) with their browser and then click the “fullscreen”/ enlarge icon. This should automatically open the map on the google maps app.

The end product will be offered to the public via a blog post in late November and will hopefully be supplemented by a “Discover and Walk your own” Guide/Booklet as a pdf download. I am currently also seeking out possibilities to further integrate the legacy of the project in form of the map as a QR code label into M Shed.

It would be great to hear your feedback and ideas for improvement as well as general thoughts on this project. I have created a google survey to fill with your impressions and ideas. The form is completely anonymous and does not require any personal data here!
After the Youth Panel Workshop I will try to start systematically evaluating the different tools and map types I discovered and how this pilot is proceeding.