Daily Work Log - 2026-06-12

Work Log (2026-06-12)

🏗️ Original Intent & Context

Our mission tonight was to resolve critical Looker Studio configuration errors (“This data source was improperly configured”) and map GSC URLs and Quo Phone Numbers into a scalable, high-performance 1:N (One-to-Many) Bridge-Table Architecture.

Previously, changing location details caused rigid 1:1 columns (url_google_search and quo_phone_number) to break downstream models, resulting in Looker Studio report failure and GSC “Location Name” dropdown filters returning only null.

⚡ Achievements & Breakthroughs

We successfully executed the transition and completely stabilized the Looker Studio data schema:

• Google Ads MCC Syncing & Activation:

  • Identified that newly added Google Ads MCC child accounts are opt-in only inside Fivetran.
  • Enabled all newly created child account customer IDs within the Fivetran configuration settings and completed a full database sync.
  • Successfully ingested and joined the newly synced child account metrics across all downstream Google Ads and LSA views in analytics_prod.

• 1:N Bridge-Table Synchronization:

  • Integrated the new Google Sheet Named Ranges (range_loc_phone_numbers and range_loc_urls) as raw sources synced via Fivetran to BigQuery: google_sheets.loc_urls (1,204 rows) and google_sheets.loc_phone_numbers (193 rows).
  • Purged the obsolete legacy columns (url_google_search and quo_phone_number) from staging and model references, preventing syntax failures.

• URL Join & Mapping Refactor:

  • Identified that GSC relative slugs (e.g., ga/atlanta) could not join against canonical landing page URLs (e.g., https://thedogwizard.com/atlanta/).
  • Swapped join conditions in pres_google_search_by_date_location_query.sql and pres_google_search_missing_location_references.sql to map on the full canonical URL column (s.page / b.page) instead of the slug (s.url / b.url). This successfully matched and restored real location names in report filters instead of null!

• Premium Aesthetics: Clean Directory Pathing:

  • Added a brand-new presentation column page_path to pres_google_search_by_date_location_query.sql using a custom BigQuery regular expression: regexp_replace(b.page, r'^https?://(?:[a-zA-Z0-9\-]+\.)?thedogwizard\.com/', '/')
  • This strips out protocols, www, and subdomains (like franchise.), showing clean, elegant, non-truncated directories (e.g., /atlanta/) inside Looker Studio tables, while maintaining 100% backward compatibility by keeping the original page column intact.

• Production Deployment & Integrity Verification:

  • Rebuilt all 56 models successfully in the production dataset analytics_prod (thedogwizard-kpi-dashboards project) with dbt run --target prod.
  • Executed all 46 data tests with dbt test --target prod, yielding a 100% success rate (46/46 passes).
  • Committed and pushed the changes cleanly to the remote branch main on GitHub (bypassing headless GPG signing blocks via -c commit.gpgsign=false).

---

🔮 Next Steps & Action Plan

1. Refresh Data Source Fields in Looker Studio:
   • Go to Looker Studio -> Resource -> Manage added data sources -> Edit GSC source.
   • Click the “REFRESH FIELDS” button in the bottom-left corner to fetch the brand-new page_path column from BigQuery.
2. Swap Page Dimensions:
   • Locate tables displaying GSC landing page URLs.
   • Swap the green dimension from page (full URL) to page_path (clean relative path) to instantly eliminate truncation.

💡 Recommended Improvements for Next Session

• Pre-computed Phone Role Metrics: Integrate the new phone_role column pre-computed in our Quo daily marts into a Drop-Down Control in Looker Studio. This will let users seamlessly filter calls and costs between Marketing, Owner, and Trainer views without writing calculated fields.
• Continuous Integration / Testing: Set up automated scheduled dbt testing on GCP to instantly catch structural changes in the source Google Sheets before they cause dashboard downtime.