A month has passed since the most recent BrightonSEO. Although my personal highlights include blagging a copy of Rand Fishkin’s book, eating the most amazing vegan kebab known to the human race and getting a photo with Tom at the Deep Crawl pre-party, where I look a bit like Bono, I also learned some interesting things.
“Selling on Other Search Engines and Multi-channel,” a talk by Katherine Khoo, stood out because it made me consider where our responsibility as an agency starts and ends. For instance, Amazon optimisation is a core area we could directly improve revenue for a handful of our clients.
However, the talk that got my brain juices flowing was the one by Raj Nijjer of YOTPO, titled “10 Things Your Customers Hate About You.” Yet, for reasons that went far beyond the direct talk. Although the stats and information he provided were interesting, it was the business concept of YOTPO and the services that it delivers, which inspired me.
The following blog post is a breakdown of the thought process and action that resulted in me building the skeleton of an Excel tool that can extract insights from reviews. If you want to go straight to said tool you can click here, however, I suggest you also read on as I feel the theory and explanations offer just as much (if not more) value than the tool alone.
Learning Your Business’ Weaknesses From Detailed Review Data
For those not familiar with YOTPO, as I wasn’t before Brighton SEO, the best way to summarise it would be, “a service that helps businesses leverage reviews on a mass scale.” The area of its service, which Raj touched on heavily at the start of his talk, was called ‘Insights‘, which is described on the YOTPO website as follows:
Turn Consumer Feedback Into Actionable Insights: Understand what your customers are saying about your products and brand and gain actionable insights to make better business decisions.
Essentially, part of what it does is it takes all of your review data, identifies patterns in word usage and correlates these to review score averages. From this, it exposes strengths in addition to pain points that customers are experiencing when dealing with your business.
The example Raj gave in his talk regarded a clothes retailer he worked who that noticed there was a pattern in the customer reviews around the word “smell.” Using this insight, the business discovered one of the packing services they used weren’t letting garments cool down before shrink wrapping them. So, when items arrived at customers’ homes, they had a strange odour. This awareness helped the retailer improve operations and avoid potential damage to customer sentiment.
I loved this example as it’s often taken for granted that online marketing isn’t just about improving results; it also helps companies make key business decisions. As such, Raj’s talk set off a lightbulb in my head, where dots began to connect.
Big Data Without a Big Budget?
Most people who have engaged with me in the industry will know I’m a sucker for Excel (like most SEOs). What I love about it, is that you only need a basic grasp of formulas and a small amount of assistance from a few external resources, in order to create tools that can do a bulk amount of work-related tasks (often mundane and time-consuming) fast and efficiently. Just last year, I put the Excel template I use daily to carry out keyword research up on our site for free download, hoping that it could help businesses and SEOs do their jobs in the best way possible with a small budget.
Partly as a learning exercise and partly out of curiosity, I started to question and piece together how YOTPO’s review and feedback analysis could be done on a smaller scale using Excel. My objective was that I would be able to offer some level of the same insights to SMBs or those working with smaller budgets.
Building The Tool & Making It Work
For my first step, I needed to extract data for a client who had a volume of reviews large enough to make it worthwhile. Luckily, we have a client who is on the pro-tier Trustpilot package, where you can export all reviews in CSV format.
Once I had this data, my next step was to get a word occurrence count on all review write-ups. For this, I needed a pure extract without any special characters. Formulas to do this were a bit beyond my Excel ability, and getting them working was going to be a timely affair. Fortunately, I was already aware of Found’s PPC tool, which has an option to do this. A quick copy and paste into the tool and then back into a spreadsheet gave me exactly what I needed.
From there, it was easy enough to get the word occurrence count by dumping all the data into this handy online word count tool by Browserling. What I like in particular about this tool is the output format – “[WORD] : [OCCURRENCE COUNT]”, which ended up making it easier to separate the data into different columns.
From there, using a mix of vlookups, length trims to the left/right of the column to do so, as well as concatenation, I made a wildcard search qualifier that would find all mentions of said words in a cell and add up the average review rating of all cells containing it. I also added a sum to calculate the percentage of total reviews each word featured in, as an easier way to digest scale.
During my last step, I created a pivot table of all of this data (including conditional colour formatting) so it could be filtered and sorted into a prioritised format based on occurrences and score.
As mentioned above, I have put the tool that does all of this (including instructions) on Google Drive with public view access if you wish to take a look, try it out with your own Trustpilot data or just nose around the formulas:Â https://strategiq.co/brightonseo-review-tool-test/
I should also note that all example data in the sheet as default is made up of extracts of Simpsons episode scripts.
TLDR; Yes, You Can Use Excel To Extract Insights From Reviews
Disclaimer: I don’t believe for a single second that anyone could do review analysis as efficiently or to the depth that YOTPO do using just Excel, which therefore justifies the price tag. I’d still certainly recommend using their service if review data is a big part of your business and you have the budget to do so.
However, this blog post has proven to myself it is possible, with a little bit of intermediate Excel skill, to take review data and extract insights of a similar vein on a smaller scale.
For now, the tool I have linked in this post is limited to Trustpilot reviews. If I had the additional time I’d have liked to build this out to a point where it has instructions to extract review data from all the common places (Google, Yelp, Trip Advisor and Check A Trade for example), but if I had attempted that, we’d have been to at least 2 more BrightonSEO events between now and the new publishing date. Another thing I’d also include would be a stop-word list to remove common words in sentence strings from featuring in the final pivot table.
Finally, I’d like to thank Raj for his talk at BrightonSEO and inspiring me to think about data in a way that had never occurred to me before. I recommend giving him a follow over on Twitter – @RajNijjer
Piece originally written by Dan Callis.
