Reporting on your Google review score in Explore

The app tags the star count, and a tag is something Explore counts rather than averages. A calculation turns it into a score you can follow per location.

The app tags every review ticket with its star count, and a tag is something Explore counts rather than averages. A calculated metric turns those tags into a number, and from there an average score per month and per location is a single report.

Why the tags on their own fall short

gmb-1 to gmb-5 are text. Explore will count the tickets carrying each one, which gives you a distribution, but it cannot average text. It therefore cannot tell you that a shop sits at 4.3 and slipped from 4.6, which is usually the number someone actually wants. A calculation closes that gap.

Put the score in a field

1. In Admin Center, go to Objects and rules > Tickets > Fields.
2. Add a Drop-down field and name it something you will recognise in Explore, such as Rating.
3. Give it five values whose tags are the ones the app already writes. The value is what a report shows and the tag is only what links it to the app, so put the stars in the value: gmb-5 in a chart tells a reader nothing.

No trigger is needed. Tags and drop-down options work in both directions: choosing an option adds its tag, and adding a tag selects the matching option. The app writes the tag, so the field fills itself.

Two things to check before you save. A tag can belong to only one field option, so if gmb-1 to gmb-5 are already in use on another field, putting them here takes them off that one. And the field fills from the moment it exists: tickets that arrived earlier keep their tag and stay empty, which is why a report over a long period shows a group with no value in it.

Wait for a review to come in

A custom field only becomes available in Explore once tickets are using it, and reporting data is synced on a schedule rather than as it happens: hourly on the plans this article needs. Create the field, let the next review arrive, give the sync its hour, and build the calculation after that. Going looking for it earlier means it is not there to pick.

Turn the stars into a number

1. Open a report on the Support: Tickets dataset.
2. Open the Calculations menu in the right sidebar and choose Standard calculated metric.
3. Name it. Avoid brackets, parentheses and quotation marks: a name that contains them causes errors as soon as the metric is used inside another calculation.
4. Enter the formula, using the name of your own field:

SWITCH ([Rating]) {
CASE "★★★★★": 5
CASE "★★★★☆": 4
CASE "★★★☆☆": 3
CASE "★★☆☆☆": 2
CASE "★☆☆☆☆": 1
}

Each CASE has to match the field value exactly, so copy the values out of the field rather than typing the stars again. Save the metric.

Restrict what can be done with it

A new metric starts with every aggregator available, and most of them produce nonsense here. Adding up stars is not a score, and a count of them only repeats your ticket count.

1. In the Metrics panel, click Add, expand Calculated metrics and click the pencil next to your metric.
2. From Options, choose Edit aggregators.
3. Make AVG the default, keep MIN, MAX and MED visible, and hide the rest.
4. From Options, choose Edit display format and set decimal places to 1.

Restricting the aggregators on a new metric is what Zendesk advises in general, and here it decides what a colleague can build on top of your work. One decimal is the difference between a score and a long number nobody reads.

Name the locations

The same trick makes the store readable. Add a second drop-down field with the store name as the value and gmb-store_ plus its Store ID as the tag, so a report says Alkmaar rather than gmb-store_18. With a chain of any size, use the bulk import on the field values instead of typing them.

Set your Store IDs in Google first. A location without one produces a tag built from Google's own long internal identifier, and that is what you will be pasting. See Routing and reporting per location.

Build the report

1. Add two metrics: Tickets for the volume, and your calculated metric with the AVG aggregator for the score.
2. Put Ticket created - Month and Ticket created - Year in Columns.
3. Filter on the ticket form the reviews land on, or on the gmb tag.
4. Add the location field under Explosions to get one chart per store instead of one line for everything.

Volume as bars and score as a line over the same months is what makes the two readable together: a month with one angry review and a month with forty both move the average, and only the bars tell you which is which.

See also

• The ticket tags the app adds
• Why the app adds no ticket fields
• Routing and reporting per location