• In Depth

How We Automate App Store Reviews into Google Sheets

In the fast-paced world of technology, it’s vital we keep up with the latest in app development so we can provide the best service to our clients. App store reviews are an integral part of this process.

As app developers, it’s important we make use of app store reviews provided by Apple, Google and other 3rd-party tools. This data is an invaluable tool and can be used to make informed decisions during the app development process that keep our apps competitive in an ever-changing marketplace.

In this article, we will share how to automate app store reviews into Google Sheets, making your data readily accessible and ready for analysing.

App Store Reviews: The Challenge

The major drawback to all of this is that app reviews require a lot of manual work - even accessing the reviews 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 store reviews is on an iOS device, which makes it hard to take the list and analyse it on a computer.

How to Automate App Review Collection

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 xml file 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.

Find App Store Review Trends

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.

Create a 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.

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

Gather App Review Statistics

Once the App Store 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.

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.

In Summary

I hope this information will help relieve some of the pain of app review analysis. One of our recent articles, shared on our blog, gives an overview of the various analytics tools available that can help you gauge app performance and how users interact with and perceive your app.

If you automate app reviews as part of your development process, please feel free to get in touch with your own methods.