Quartz 5

Daily Work Log - 2026-06-11

Properties1
tagsdaily-note--engineering

2 min read

Session Log: 2026-06-11

📋 Session Overview

This was a major foundation-building session. We established the entire Quo (OpenPhone) communication pipelines, deployed the daily performance transformations for Facebook/Meta Ads, upgraded our Google Ads client mapping cascade, and added multi-URL expansion support for Google Search Console (GSC) to align organic queries with franchise territory scopes.

🚀 Key Accomplishments

1. Quo (OpenPhone) Communication Architecture

Laid the entire foundation of our customer text and call volume analytics, mapping phone numbers to physical locations and integrating Row-Level Security (RLS):

  • Staging Layer:
    • stg_quo_calls.sql — Ingests and casts raw inbound/outbound calls.
    • stg_quo_messages.sql — Parses and formats raw text messages.
    • stg_quo_phone_numbers.sql — Houses active OpenPhone system numbers.
  • Intermediate Mapping Layer:
    • int_location_reference_phone.sql — Bridges OpenPhone numbers with static Google Sheets reference location codes.
  • Marts Layer (marts/quo):
    • mrt_quo_calls_daily.sql & mrt_quo_messages_daily.sql — Aggregates calls/texts to daily, territory-level performance statistics.
  • Presentation Layer (presentation/quo):
    • pres_quo_calls_daily.sql & pres_quo_messages_daily.sql — Unified communication views complete with territory owner Row-Level Security (RLS).

2. Facebook/Meta Ads Daily stats Pipeline

  • Completed the end-to-end dbt pipelines for Meta Ads campaigns, pulling daily reach, frequency, spend, clicks, and impressions.
  • Enforced RLS presentation layers so territory owners only see their regional Facebook/Instagram campaigns.

3. Google Ads & GSC Reference Upgrades

  • Google Ads Mapping Cascade: Upgraded location cascade mapping to handle dynamic location identifiers and removed rigid ID matching expressions.
  • GSC Multi-URL Mapping: Created int_location_reference_google_search.sql supporting multi-URL expansion to cleanly associate GSC web traffic with individual territory boundaries.

📁 Files Created & Modified

  • models/sources.yml
  • models/staging/quo/stg_quo_calls.sql
  • models/staging/quo/stg_quo_messages.sql
  • models/staging/quo/stg_quo_phone_numbers.sql
  • models/staging/google_sheets/stg_google_sheets_locations_phone_numbers.sql
  • models/staging/google_sheets/stg_google_sheets_locations_urls.sql
  • models/intermediate/int_location_reference_phone.sql
  • models/intermediate/int_location_reference_google_search.sql
  • models/marts/quo/mrt_quo_calls_daily.sql
  • models/marts/quo/mrt_quo_messages_daily.sql
  • models/marts/quo/quo_marts.yml
  • models/presentation/quo/pres_quo_calls_daily.sql
  • models/presentation/quo/pres_quo_messages_daily.sql
  • models/presentation/quo/quo_presentation.yml

Graph View