Automating Intelligence: From Raw JSON to Real-Time Review Dashboards

We are rapidly moving past the era where business intelligence was a retrospective activity. Today, the velocity of customer feedback demands a different approach. If a customer leaves a scathing review at 10:00 AM, waiting until the end-of-month report to address it is a strategic failure. The barrier isn't a lack of data, but the form it takes. Google Business Profile (GBP) locks sentiment away in paginated JSON responses and isolated interfaces, creating a recipe for data blindness.

I chose to leverage the Google Workspace ecosystem by utilizing **Google Apps Script**. This created a serverless, lightweight ETL (Extract, Transform, Load) pipeline that lives directly within the environment where the data ultimately resides.

• Eliminates the need for external server maintenance and costs.
• Simplifies authentication via Google's native OAuth2 flow.
• Scales automatically without manual infrastructure management.

The first challenge is getting data out of the silo. I constructed authenticated GET requests using the `UrlFetchApp` service. To ensure complete data integrity, I implemented a **do-while loop** that checks for the `nextPageToken`. The raw response arrives as a structured JSON object like this:

{
  "comment": "Great service!",
  "starRating": "FIVE",
  "createTime": "2024-10-02T15:01:23Z",
  "reviewer": {
    "displayName": "Jane Doe"
  }
}

Raw data is rarely good data. In its native format, the review data contains enums and ISO timestamps that break downstream visualizations. I engineered a transformation layer with two critical logic steps:

• Converting string enums (ONE, TWO, etc.) into integers for mathematical aggregation.
• Stripping generic ISO syntax and converting timestamps to a dd-MMM-yy format.
• Standardizing the 'Zulu' time zone indicator to prevent date parse errors in Looker Studio.

To unlock hidden value, I integrated keyword extraction logic that automatically identifies staff performance indicators mentioned by customers.

• Automated matching of employee names within review text.
• Tagging reviews in a dedicated 'Staff' column for easy pivoting.
• Identifying top-performing employees without reading manual comments.

Google Sheets serves as our 'single source of truth'. I configured Looker Studio to refresh every 15 minutes, ensuring negligible latency between feedback and action. The dashboard visualizes:

• Sentiment Trends: Line charts overlaying review volume with average star ratings.
• Staff Leaderboards: Bar charts showing feedback volume per team member.
• Response Rates: Tracking management replies to ensure zero customer neglect.

By automating the extraction and normalization process, we remove the cognitive load from the operations team. They no longer spend hours scrubbing CSV files; they spend their time coaching staff and engaging with customers. This project demonstrates that raw noise can be turned into a clear, actionable signal with the right API architecture.