Dave at Brightec
Written by Dave Thompson
Apr 25, 2019

How To: Automate App Store Reviews Into Google Sheets

We've delved into the pain of collating app store reviews - so you don't have to...

As app developers, it’s important to make use of the data that’s given to you by Apple, Google and other 3rd-party tools about the performance of your app. It will help you  improve your users’ experience and differentiate your app from the many similar apps on the stores.

One of our recent blog posts gives an overview of the various analytics tools available that can help you gauge the performance of you app and how users interact and perceive your app. An important (and often overlooked) source of data are user reviews.

Not only do the review scores rank your app (which has an immediate effect on its app store visibility and the likelihood of winning new users) but they can provide clear insights into where your app is failing the users.

The usefulness of app reviews is not just limited to your own apps. Looking at the reviews of competitor apps can help you gain an insight into what makes an app successful or what really annoys users. This can then be used to make informed decisions about your own app.

Sounds good. What's the painful bit?

The major drawback to all of this is that app reviews require a lot of manual work - even accessing the review can be a problem. Apple and Google both provide web consoles that let you view, monitor and respond to reviews on your own app.

To monitor other apps, the Google Play Store website allows you to search for an app and view all customer reviews. However, Apple has in recent years removed the iOS app store from iTunes and prevented users from seeing more than the top three  user reviews on the website. The only way to view the full list of iOS app reviews is on an iOS device, which makes it hard to take the the list and analyse it on a computer.

A solution to this problem is to use the Apple RSS Feed Generator. This little known feature lets you create RSS feeds for all of Apple media content, such as featured albums, new and popular apps, top charting songs etc. An undocumented feature of this RSS feed is you can specify that it returns the 50 most recent reviews for a specific app ID.

This link returns the top 50 more recent reviews for the Apple’s App Store Connect app. Once we have this xml file, we can import the details we want into a Google Sheets spreadsheet. Google Sheets lets you create scripts in JavaScript to do almost anything you want.

We can write a script that gets the rss feed for a given app store id,  parses the XML and extracts the information we want.

function iOSReviews(input, useHeader) {
 var url = 'https://itunes.apple.com/GB/rss/customerreviews/id=' + input + '/sortBy=mostRecent/xml';
 var xml = UrlFetchApp.fetch(url).getContentText();
 var document = XmlService.parse(xml);
 var root = document.getRootElement();
 var atom = root.getNamespace()
 var itunes = XmlService.getNamespace('im', 'http://itunes.apple.com/rss');
 var qalName = root.getQualifiedName();
 var entries = root.getChildren("entry", atom);

 var array = [];
 if (useHeader == true) {
   array.push(['Date', 'Id', 'Rating', 'Version', 'Review title', 'Review']);
 }
 for (i = 0; i < entries.length; i++) {
   var item = entries[i];
   var val = item.getType();
   Logger.log(item.getValue());
   var date = item.getChild('updated', atom).getText();
   var id = item.getChild('id', atom).getText();
   var rating = item.getChild('rating', itunes).getText();
   var version = item.getChild('version', itunes).getText();
   var title = item.getChild('title', atom).getText();
   var content = item.getChild('content', atom).getText();
   array.push([date, id, rating, version, title, content])
 }
 return array;
}

This code will return the review date, ID, rating, version of the app being reviewed, review title and review content. Once all the content is in the spreadsheet, it’s probably best to copy all the generated data and Paste Values only, so new reviews aren’t fetched every time the sheet is opened.

Screen Shot 2019-03-21 at 12.53.51.png

You can now start the unavoidable manual work of reading the reviews to find trends in what the users think of the app. You can use lookup tables to speed up the process by breaking down and categorising reviews into general user sentiments.

For instance, reviews might mention that the app is slow to login, or navigate, it may have been difficult to find a feature, or there were issues with the company's support, or the user is requesting a missing feature. These different categories can be created in a separate tab and used as the source for a lookup table.

Screen Shot 2019-03-21 at 12.59.27.png

Dropdown list source

To create a dropdown list, select the first cell that you want the drop down list in, go to Data -> Data validation and in the criteria select the data range and highlight the list of categories in the categories tab, then save.

There should be a down arrow in the cell and clicking it shows the list of categories. Click the bottom right corner and drag to copy the drop down list to all the other cells.

Screen Shot 2019-03-21 at 12.53.51.png

When working through the reviews, a reason that doesn’t belong in any of the existing categories can be added to the list.

Screen Shot 2019-03-21 at 13.05.15.png

Once the reviews have been categorised, statistics can be gathered by counting up each of the categories. We can do this in the “Categories” tab referencing the reviews tabs.

Screen Shot 2019-03-21 at 13.07.19.png

For the App Store Connect app, we can see there’s a trend of users thinking there are missing features or requesting new features. There are also a number of users saying the data is not up-to-date. As an app developer, these would be areas that we could look at improving in the next update.

Analysing reviews in this way can be extended to multiple apps in the same category e.g multiple calendar or productivity apps. The statistics for all of these reviews can be collated and any trends within the app category can be determined to see if there are common issues that users of a particular type of app are facing. If so,  you can differentiate your app by fulfilling these needs.

Unfortunately, the Google Play Store doesn’t provide an easily parsable feed for app reviews, so we have had to copy these into our spreadsheets manually before analysing.

I hope this information will help relieve some of the pain of app review analysis. The attached Google Sheets project contains the review I’ve analysed in the screen shots, along with the scripts and conditional formatting used. If you analyse app store reviews as part of your development process, please feel free to get in touch with your own methods!

Previous Post

How to start a career as a mobile app developer

Top