Skip to main content

Google Play Reviews Investigation

Investigate Google Play reviews by identifying the user in the farfalla database and posting findings to Slack.

Prerequisites​

  • GOOGLE_PLAY_SERVICE_ACCOUNT_JSON set in formica/.env
  • GOOGLE_PLAY_PACKAGE_NAME set in formica/.env (default: la.publica.reader)
  • SLACK_BOT_TOKEN set in formica/.env (for posting to Slack)

Available Commands (from formica/)​

# List recent reviews
php scripts/googleReviewsHandler.php list [--limit=100] [--lang=es]

# Reviews without a reply
php scripts/googleReviewsHandler.php pending [--limit=50]

# Get a specific review
php scripts/googleReviewsHandler.php get <reviewId> [--lang=es]

# Reply to a review
php scripts/googleReviewsHandler.php reply <reviewId> "<message>"

# Stats overview (ratings distribution, pending replies, recent negative/positive)
php scripts/googleReviewsHandler.php stats

Investigation Process​

Step 1: Fetch review(s)​

Fetch the latest review(s) or pending reviews using the handler:

cd /Users/gpublica/workspace/pla/formica
php scripts/googleReviewsHandler.php list --limit=5 --lang=es

Present a summary to the user: author, rating, text, app version, date, has_reply.

Step 2: Ask for Slack thread​

Use AskUserQuestion to ask the user for the Slack thread URL where the investigation will be posted. Phrase it as:

"Which Slack thread should I post the investigation to?"

With a free-text input (use a single option "Paste Slack thread URL" to prompt the user to provide the link via "Other").

Step 3: Investigate the user in farfalla DB​

Search for the reviewer by name in the users table:

SELECT id, name, email, tenant_id, created_at
FROM users
WHERE name LIKE '%<author_name>%'
LIMIT 10

For each match, gather:

  1. Tenant info:
SELECT id, name, slug FROM tenants WHERE id IN (<tenant_ids>)
  1. Content access (issues):
SELECT iu.user_id, iu.issue_id, iu.created_at, i.name AS issue_name, i.file_type, i.tenant_id
FROM issue_user iu
JOIN issues i ON iu.issue_id = i.id
WHERE iu.user_id IN (<user_ids>)
ORDER BY iu.user_id, iu.created_at DESC
  1. Orders:
SELECT o.id, o.user_id, o.tenant_id, o.amount, o.currency_id, o.status, o.gateway_type, o.created_at
FROM orders o
WHERE o.user_id IN (<user_ids>)
ORDER BY o.created_at DESC
  1. Subscription plans:
SELECT user_id, COUNT(*) AS total_plans
FROM users_plans
WHERE user_id IN (<user_ids>)
GROUP BY user_id

Step 4: Identify the most likely user​

When multiple users match, use these heuristics to pick the most likely reviewer:

  • Exact name match (case-insensitive) is stronger than partial match
  • Multiple content items aligns with reviews mentioning "los libros que compre"
  • App version and review date vs user's created_at (user must exist before review)
  • Single-content users from institutional tenants are less likely to leave public reviews

Step 5: Post investigation to Slack​

Parse the Slack thread URL to extract channel ID and thread timestamp. Post a well-formatted summary using the Slack handler:

php -r "
require 'scripts/slackHandler.php';
\$h = new SlackHandler();
echo json_encode(\$h->postMessage('<channel_id>', '<message>', '<thread_ts>'), JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
"

Message format (Slack mrkdwn):

:mag: *Investigacion review Google Play - <author> (<rating> estrella(s), <date>)*

*Usuario probable:* `<email>` (id: <user_id>)
*Tenant:* <tenant_name> (`<tenant_slug>`)
*Registrado:* <created_at>
*App version:* <app_version>

*Contenido (<count> items):*
- _<issue_name>_ - <tenant_name> (<file_type>)

*Ordenes en PLA:* <count> (<details>)

*Queja:* <brief summary of review complaint>

*Analisis:*
- <key insight 1>
- <key insight 2>
- <key insight 3>

Reply Guidelines​

When drafting a reply to a review:

  • Be empathetic and professional
  • Acknowledge the user's frustration
  • Explain briefly (without blaming the customer's platform)
  • Offer a concrete next step (support email, web reader if available)
  • Keep it short (3-4 sentences max)
  • Match the reviewer's language (if review is in Spanish, reply in Spanish)

Notes​

  • Google Play Reviews API only returns reviews from the last ~7 days
  • The pending command filters reviews that have no developer reply
  • User names in reviews may not match DB exactly (nicknames, display names, etc.)
  • Some users buy through marketplace partners (e.g., Bajalibros, Bookwire) — their orders may not appear in our orders table since the partner handles checkout
  • Always present findings as investigation results, not certainties — name matching is heuristic