Looker Studio and API Ingestion Playbook

📌 Core Architectural Standards & Learnings

This document captures the long-term engineering learnings, playbooks, API nuances, and design patterns established during the looker-studio-dashboard development sessions.

---

🔑 Key Engineering Playbooks

1. Google Business Profile (GBP) Ingestion & Latency Lag

• aggregation Lag Rule: When debugging “missing” or “incomplete” GBP metrics, always assume a native 3-day aggregation lag from Google’s native APIs.
• Detection Protocol:
  • Fivetran raw syncs will show records with empty/null performance metric columns for the current date and the preceding 2 days.
  • For example, if today is June 26, the last fully-populated and accurate day of performance metrics will be June 23.
  • Always verify raw table contents directly before attempting to debug dbt models or pipelines for “gaps”.

2. OpenPhone API Constraints & Mapping Patterns

• Conversation vs. Record Endpoint:
  • The GET /v1/messages and /v1/calls endpoints require explicit phoneNumberId and a single-element participants list to retrieve individual interactions.
  • This requires pre-loading the full list of conversation participants from /v1/conversations (stored as data/raw_conversations.json), filtering to 1-to-1 interactions, and processing each sequentially.
• Schema Constraints:
  • OpenPhone’s to field returns as a JSON array (e.g. ["+17205610977"]). BigQuery’s standard schema expects flat strings.
  • Always flatten array types into standard, comma-separated strings (e.g., ", ".join(to_val)) in python crawlers prior to loading into BigQuery newline-delimited JSON targets.
• Rate-Limit (429) Handling:
  • OpenPhone imposes highly restrictive dynamic rate limits.
  • Standard multi-threading must utilize a thread-safe TokenBucketLimiter set to maximum ~8.5 to 9.0 requests/second.
  • Any thread hitting a 429 must execute exponential backoff (starting at 2s, doubling with each subsequent hit) and cleanly resume without dropping the thread’s payload.

3. dbt Real-Time/Historical Unified Staging Pattern

To seamlessly merge live raw event streams (e.g., Webhooks) and massive backfilled historical tables, utilize the following design pattern:

• Staging Logic:
  1. Define a combined CTE that executes a UNION ALL across both sources (historical_calls and quo_raw_events).
  2. Implement an incremental/temporal rank:

     ranked as (
         select *, row_number() over (partition by id order by created_at desc) as rn
         from combined
     )

  3. Select from ranked where rn = 1. This deduplicates overlaps between the historical backfill date and the start date of the live webhook.

---

🔮 Strategic Roadmap

• Next Target Integration: PocketSuite Data Integration
  • Core Objective: Map and visualize the complete customer lifecycle (New -> Evaluation -> Customer), lead pipeline stages, direct lead source attribution, revenue volume, and payment channels.
  • Resulting Blueprint: Provide “Marketing-to-Revenue” closed-loop analytics directly inside Looker Studio.