App Store Reviews & Ratings

Automated daily sync of Apple App Store customer reviews and aggregate ratings into BigQuery, with Slack notifications for new reviews.

Overview

Customers can rate the Eli Health app on the App Store in two ways:

• Star-only ratings — a 1-5 star rating with no text (most common)
• Written reviews — a star rating with a title and body text

This system collects both types daily and stores them in BigQuery for analytics. New written reviews are posted to the #alerts-appstore-reviews Slack channel.

Architecture

Components

1. Reviews Sync Cloud Function

Location: eli-devops/tf/modules/global/appstore-reviews/

A Python 3.12 Cloud Function that runs daily and fetches data from two Apple APIs:

Source	API	Data Collected
App Store Connect API	/v1/apps/{app_id}/customerReviews	Individual written reviews with rating, title, body, author, territory
iTunes Lookup API	/lookup?id={app_id}&country={cc}	Aggregate rating snapshots per country (includes star-only ratings)

Configuration:

Setting	Value
Schedule	Daily at 7 AM UTC (0 7 * * *)
Runtime	Python 3.12
Memory	256 MB
Timeout	300s
App ID	6471992170 (Eli Health production)

Authentication: JWT tokens signed with ES256 algorithm using App Store Connect API keys stored in GCP Secret Manager.

Deduplication: Before inserting, the function queries existing review IDs from BigQuery and only inserts new records.

Rate Limiting: Handles HTTP 429 responses from Apple with exponential backoff.

Countries tracked for rating snapshots: US, CA, GB, AU, IN, DE, FR, JP, BR, MX (10 countries).

2. BigQuery Tables

Both tables are in the eli-health-prod.app_store dataset.

customer_reviews — Written Reviews

Column	Type	Description
review_id	STRING (required)	Unique review identifier
rating	INTEGER (required)	Star rating 1-5
title	STRING	Review title
body	STRING	Full review text
reviewer_nickname	STRING	User's display name
territory	STRING	Country code (e.g., "USA", "CAN")
created_date	TIMESTAMP (required)	When the review was posted
synced_at	TIMESTAMP (required)	When synced to BigQuery

rating_snapshots — Aggregate Ratings (Daily)

Column	Type	Description
snapshot_date	DATE (required)	Date of snapshot
country	STRING (required)	Country code (us, ca, gb, etc.)
rating_count	INTEGER (required)	Total ratings including star-only
average_rating	FLOAT (required)	Average star rating (1.0-5.0)
rating_count_current_version	INTEGER	Ratings for current app version only
average_rating_current_version	FLOAT	Average for current version
app_version	STRING	App version at snapshot time
synced_at	TIMESTAMP (required)	Sync timestamp

Star-only vs Written Reviews

The customer_reviews table only contains reviews where the user wrote text. The rating_snapshots table includes all ratings — both star-only and written. This is why rating_count in snapshots is higher than the row count in customer_reviews.

3. KPI Dashboard

URL: /appstore on the KPI service

The dashboard queries both BigQuery tables and displays:

• Total reviews and average rating from customer_reviews
• Rating distribution (1-5 star breakdown)
• Rating rate — total ratings as % of active customers (90d), with industry benchmark (3-8%)
• Reviews by territory — breakdown by country from written reviews
• Rating by country — aggregate snapshots including star-only ratings (10 countries)
• Monthly trend — review count and average rating over time
• Recent reviews — full text of latest reviews

4. Slack Notifications

Channel: #alerts-appstore-reviews (ID: C0ACLEF0T4G)

Trigger: POST /sync/appstore-reviews-notify on the KPI service

Deduplication strategy — Slack as the database:

Instead of maintaining a separate tracking table, the system uses the Slack channel itself for deduplication:

1. Fetch all reviews from BigQuery customer_reviews
2. Read the Slack channel history
3. Extract review_id values embedded in each message's context block
4. Post only reviews whose ID is not already in the channel

Each Slack message includes a context block with review_id:XXXXX that serves as the deduplication key.

⭐ ★★★★★ — alangreb (USA)
┃ Measuring regularly, excited for testosterone
┃ Started using this to measure cortisol to improve my sleep...
┗ Jan 22, 2026 | review_id:00000181-c2c3-6a03-2e5c-02ad00000000

Running the notification check:

# Manually trigger
curl -X POST https://kpi.eli.health/sync/appstore-reviews-notify

# Response
{
  "success": true,
  "total_reviews": 10,
  "already_posted": 10,
  "newly_posted": 0,
  "failed": 0,
  "new_review_ids": []
}

Data Flow Timeline

Time	Event
Daily 7:00 UTC	Cloud Scheduler triggers sync_reviews Cloud Function
7:00-7:05 UTC	Function fetches reviews from App Store Connect API (paginated, 200/page)
7:00-7:05 UTC	Function fetches rating snapshots from iTunes API (10 countries)
7:05 UTC	New records inserted into BigQuery (deduplicated)
After sync	/sync/appstore-reviews-notify posts new reviews to Slack

Terraform Configuration

The sync infrastructure is managed in eli-devops/tf/modules/global/appstore-reviews/:

# production.tfvars
appstore_reviews_enabled      = true
appstore_reviews_app_apple_id = "6471992170"
appstore_reviews_schedule     = "0 7 * * *"

Resources created:

• BigQuery dataset app_store with both tables
• Cloud Function sync_reviews (Python 3.12)
• Cloud Scheduler job
• Service account appstore-reviews-sync with BigQuery data editor + job user roles
• Secret Manager entries for App Store Connect API keys

Monitoring

• Cloud Function logs: Check execution status in GCP Console → Cloud Functions → sync_reviews
• BigQuery: Query synced_at to verify daily syncs are running
• Slack channel: #alerts-appstore-reviews shows all notified reviews
• KPI dashboard: /appstore shows latest data and trends

Testing

Unit tests for the sync function are in eli-devops/tf/modules/global/appstore-reviews/function/test_main.py (30+ tests covering JWT generation, review parsing, pagination, rate limiting, deduplication, and BigQuery inserts).

cd eli-devops/tf/modules/global/appstore-reviews/function
python3 -m pytest test_main.py -v