| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
1 | https://strategiqmarketing.co.uk/ | Unit 16, Brightwell Barns Ipswich Road Brightwell, Suffolk IP10 0BJ enquiries@strategiq.co 0333 358 3343 | |||||
2 | PLEASE NOTE: This "tool" only works with Trust Pilot Pro and Enterprise accounts (for now anyway). It was thrown together in a couple of hours as an example of what you can do with a data export, a little bit of Excel knowledge, a few online tools and some spare time - it is by no means as well rounded, straight forward, intuative or inclusive as it could be. My main aim is to get your brain juices flowing and inspire you, as opposed to giving you the ultimate solution to all your review big data woes - there's a reason why YOTPO charge good money for its services and I'm giving this spreadsheet away alongside a blog post ;) | ||||||
3 | |||||||
4 | Instructions: | ||||||
5 | (You will need to make your own copy of this document in your Google Drive by going to 'File > Make a copy...') | ||||||
6 | 1. Take an export of your Trust Pilot review data and paste it into the "Trust Pilot Data" sheet | ||||||
7 | 2. Copy all cells from the "Review Content" cells (Column H), paste it into one of the windows on Found's PPC Keyword Tool and click the "DELETE SPECIAL CHARACTERS" button... | ||||||
8 | https://www.found.co.uk/ppc-keyword-tool/ | ||||||
9 | 3. Copy the data from with all special characters removed from the window you inserted it, and then paste it into the following Word Frequency tool... | ||||||
10 | https://www.browserling.com/tools/word-frequency | ||||||
11 | 4. Now copy the output from the Word Frequency tool and paste it into the "Trust Pilot Word Count" sheet | ||||||
12 | 5. You will find the final data output in the "Trust Pilot Results" sheet (which is a pivot table, meaning you can sort and apply filters if needed) | ||||||
13 | 6. Use this data to find common word themes that correlate with your good, average or poor reviews left by customers | ||||||
14 | 7. If you want to find the specific reviews that mention certain words, you can use the filters in the "Trust Pilot Data" sheet to search for them | ||||||
15 | |||||||
16 | Please send any issues or feedback via email (dan@strategiq.co) or Twitter (@callis1987). Thank you. | ||||||
17 | This tool was inspired by Raj Nijjer's April 2018 Brighton SEO talk "10 Things Your Customers Hate About You" - https://www.slideshare.net/RajNijjer/10-things-your-customers-hate-about-you | ||||||
18 | |||||||