Quartz 5

Looker Studio Pro Architecture Blueprint

Properties1
tagsthedogwizard--looker--blueprint--architecture

10 min read

Looker Studio Pro Architecture Blueprint

🎨 Theme Portability (How to Import Your Custom Theme)

When building a new report from scratch, copying and pasting elements from a styled report transfers the custom styling only as individual element overrides. It will not automatically update your new report’s default global theme settings. Any new chart you draw will revert to the default Looker theme.

Additionally, to be 100% transparent: my previous instruction regarding a right-click “Extract theme from chart” context menu option was incorrect. Looker Studio does not have that right-click context option for chart elements.

To bridge this gap and get your custom theme saved globally into V3, use either of these two practical methods:

Method A: The Screenshot & Image Extraction Method (Fastest)

Looker Studio does have a built-in engine to extract themes from images. Since your V2 report has the styled dashboard:

  1. Take a quick screenshot of your beautifully styled chart or section from the V2 report (or from the canvas elements you pasted into V3) and save it.
  2. Open your new V3 report in Edit mode.
  3. Click Theme and layout in the top toolbar to open the right-side panel.
  4. In the Theme tab, scroll all the way to the bottom.
  5. Click Extract theme from image and upload your screenshot.
  6. Looker Studio will analyze the screenshot, extract the dominant hex colors, and generate four cohesive report themes. Select the one that matches your V2 design and click Apply.

Method B: Manual Global Theme Registry (Under 60 Seconds)

Since copying and pasting an element does preserve its styling overrides on the canvas, you can use a pasted chart as a style guide:

  1. Select one of your copy-pasted, styled charts on the V3 canvas.
  2. In the right-hand panel, go to the Style tab. Note down the primary colors (the hex codes, such as #1A365D or #F7FAFC) and the font family used.
  3. Click any empty spot on the canvas background to clear your selection. This will display the Theme and layout panel on the right.
  4. Click the Customize button at the top of the Theme tab.
  5. Under Report Theme, manually update the primary color palette fields with your noted hex codes, and set the default font.
  6. The Result: Looker Studio registers this as the official default theme for your V3 report. Any new charts or scorecards you add from now on will automatically inherit these custom styles without needing manual editing.

🏗️ Core Architecture: One Big Table (OBT) vs. Multi-Source Isolation

To prevent the common “finish-line collapse” when adding new platforms (Local Service Ads, Meta, Quo, etc.), we must transition from a fragile web of distinct connectors to an Enterprise Data Architecture.

Option A: The “One Big Table” (OBT) Pattern (Best for High-Level KPI Summaries)

In an OBT design, dbt aggregates and unions all metrics into a single, flat, pre-compiled table at a unified grain: location_dwid x date x rls_email.

🔒 Zero-Disruption Table Isolation Guarantee (V1 Remains Fully Live)

Any OBT we construct will be built as a completely brand-new, isolated table (e.g., analytics_prod.pres_executive_summary_obt).

  • This model is written to a new, separate SQL file (models/presentation/pres_executive_summary_obt.sql).
  • It will not touch, alter, or disrupt any of the existing views or tables used by the live V1 dashboard. Both dashboards can run in parallel with 100% data fidelity. V1 will continue to compile and run exactly as it does today.

📈 Future-Proofing for PocketSuite Integration (Detailed Architecture)

PocketSuite is a vital addition. To integrate its sales and revenue data (Bookings, Customers, Income, and more) into your reporting without any disruption or performance saturation, we use a tiered data engineering layout:

  1. Dedicated, Isolated Presentation Tables (For Deep-Dive Reporting): PocketSuite data will be ingested from Fivetran/API and compiled by dbt into separate, clean physical tables (e.g., pres_pocketsuite_bookings_daily, pres_pocketsuite_revenue_daily, pres_pocketsuite_customers_daily).

    • These tables will power their own dedicated deep-dive reports or pages (e.g., Evaluations, Customers, and Income).
    • Because they query their own dedicated, pre-compiled tables, these deep-dives will load instantly and never interfere with your advertising or SEO data sources.

  2. Executive OBT Integration (For Consolidated Overview): To display consolidated business health on the Page 1 Executive Overview, we will simply append a new union block inside the dbt model for our new OBT (pres_executive_summary_obt.sql):

    -- Union block for PocketSuite consolidated metrics inside pres_executive_summary_obt.sql
select 
    location_dwid, 
    date, 
    rls_email, 
    'pocketsuite' as platform, 
    income as revenue, 
    bookings as conversions,
    ...
from {{ ref('mrt_pocketsuite_consolidated_daily') }}

    This keeps your high-level overview automatically consolidated, while ensuring detail-level tables for deep analytics are queried safely from their own isolated endpoints.

Option B: The “Micro-Report + Client Portal” Pattern (The Gold Standard)

Instead of putting 8 different platforms on 8 pages inside one massive Looker report (which forces Looker’s single browser thread to manage 15 distinct BQ connections simultaneously, bricking the entire dashboard if one connection fails), you build independent, topic-isolated single-page reports.

  1. The Google & Meta Ads Report (1-2 pages, 2 data sources)
  2. The Local SEO & Search Console Report (1-2 pages, 2 data sources)
  3. The Quo Call & Message Lead Log (1-2 pages, 2 data sources)
  4. The Executive Hub (1 page, 1 OBT data source)

How You Bind Them Together:

You embed these highly optimized individual reports into a single, premium client-facing portal.

  • Option 1: Looker Studio Master Portal (Embed URL): Create a master Looker Studio menu report, and use Looker’s built-in “Embed URL” element to display the sub-reports inside tabs or frames.
  • Option 2: Premium Client Web Portal (Premium Embeds): Create a beautiful, simple HTML portal or a Notion/Obsidian shared workspace, and embed each Looker report’s clean iframe code into its own tab or page.

Why the Micro-Report Portal is 100% Future-Proof:

  • Failsafe Isolation: If Meta Ads changes its API schema tomorrow and breaks the Meta connector, only the Meta tab breaks. Your Google Ads, GBP, and Quo pages continue to run perfectly. One broken platform can never brick the entire client-facing dashboard again.
  • Lazy Loading (Zero Parallel Saturation): Looker Studio only compiles and executes queries for the specific report the user is currently viewing. Loading Page 1 never compiles Page 4 queries in the background, completely eliminating parallel query timeouts.

🌉 Step 3: How the Custom Query Bridge Protects You

Connecting your tables via Custom Query (select * from analytics_prod.pres_...) instead of the standard Table selector is your ultimate defense against dbt schema updates:

  • The Problem with Table Connector: When Looker Studio connects directly to a Table, it queries the BigQuery metadata API to lock down the exact columns, nullability, and order. If dbt updates or recreates that table, Looker’s rigid cache breaks and throws a “system error”.
  • The Custom Query Advantage: Looker treats Custom Query as a static SQL string. It doesn’t query BigQuery metadata; it just passes the string. If you add 5 new columns in dbt, Looker doesn’t crash; it just runs the SELECT, sees the new columns, and lists them as “New fields found” in your report config panel for you to map whenever you are ready. It completely decouples Looker’s UI engine from the dbt database structure.

🗺️ Step 4: The Ultimate Page Layout Blueprint

Since you are rebuilding V2, here is the most elegant, responsive, and performance-optimized page structure for a Data Studio Pro configuration:

Page 1: Executive Overview (The Single-Source Summary)

  • Data Source: One Big Table (OBT) pre-aggregated to Location-Date grain.
  • Visuals: High-level scorecard comparison grids (Google Ads vs Meta Ads vs GSC vs Quo Leads), trend timelines.
  • Why: This page has only one connection. It loads in under 1 second and is completely bulletproof.

Page 2: Paid Funnel (Google Ads + LSA + Meta Ads)

  • Data Sources: Custom Query bridges to:
    1. pres_google_ads_stats_by_account_campaign_date (Google Ads campaign totals)
    2. pres_google_ads_lsa_performance (Local Service Ads daily lead spend and cost-per-lead)
    3. pres_google_ads_lsa_leads (LSA detailed phone calls, contact info, and charge states)
    4. pres_facebook_ads_stats_by_account_campaign_date (Meta Ads social campaigns)
  • The Fivetran LSA Setup: Because Local Service Ads (LSA) is a custom source built in Fivetran, we have fully pre-compiled and flattened its data into the presentation layer models above. Under the hood, Fivetran aggregates daily lead spend and detail call records, which dbt materializes as flat tables in BigQuery. Linking these via direct Custom Queries ensures LSA performance loads instantly, is 100% stable, and aligns beautifully alongside Google Ads and Meta Ads in the Paid Funnel.
  • Visuals: Side-by-side cost and lead conversion metrics, LSA high-quality call logs, and the unpivoted Google Ads Funnel Stats chart.

Page 3: Organic Growth (GSC Queries + GBP Insights)

  • Data Sources: Custom Query bridges to pres_google_search_by_date_location_query and pres_google_business_location_insight.
  • Visuals: Organic keyword tables, maps impressions, and website click timelines.

Page 4: Lead Logs (Quo Calls & Messages Consolidated)

  • Data Sources: Separate Custom Query bridges to pres_quo_calls_daily and pres_quo_messages_daily.
  • Visuals: Consolidated page split into two clean visual sections:
    • Calls Section: Call trend timelines, answer rates, call-duration tables.
    • Messages Section: Outbound vs inbound message counters, engagement tables.
  • Why this is perfectly safe: Since calls and messages are pre-compiled as flat physical tables, having them on the same page is 100% safe. Looker will execute simple parallel SELECT queries that compile instantly in BigQuery with no resource contention.

🌿 Social Growth: Separate Report or Part of V3?

When deciding whether to include Organic Facebook and Instagram in your main report (e.g., as Page 5) or split them into a brand-new “Social Growth” report, here is the architectural guidance:

Option A: Create a Separate, Dedicated “Social Growth & Reputation” Report (Highly Recommended)

  • Why: Complete Credential Risk Isolation. Organic Facebook and Instagram rely on Meta’s Graph API, which enforces strict 60-day OAuth token expirations. If a Meta token expires and breaks, Looker Studio will throw a scary system error on any page containing that source.
  • By keeping Organic FB/IG in a completely separate, single-page Looker report, you guarantee that a Meta token expiration cannot brick your main V3 report. Your critical paid advertising (Google Ads, Meta Ads, LSA) and local search rankings (GSC, GBP) remain 100% functional.
  • Client Delivery: You can present a unified face to your client by linking both reports in a simple, premium client-facing menu, a shared Notion workspace, or by embedding them as tabbed elements in a single client portal.

Option B: Page 5 of the Main V3 Report (Only if a single Looker link is required)

  • Why: If your client absolutely demands a single, unified Looker Studio URL, you can put it on Page 5 of your V3 report.
  • The Failsafe: By connecting the Organic Meta metrics (pres_facebook_organic_page_metrics) via the Custom Query Bridge (select *), and isolating them on their own page, Looker restricts execution of that query to Page 5. If a Meta token expires, only Page 5 will display an error; Pages 1, 2, 3, and 4 will continue to load perfectly.

🛡️ Step 5: Security Account vs. Admin Email RLS Mechanics

To resolve your question on Line 97: No, you do not add the Service Account email to the admin emails list.

Here is the difference between these two identities:

  1. The Database Executor (The Service Account):
    • Who: [email protected]...
    • What it does: It is the database user that logs into BigQuery on behalf of the report to run the queries. It has project-wide roles/bigquery.dataViewer permissions, meaning it has the power to see all rows and all tables in the database.
  2. The Visual Viewer (Your Google/Chrome Profile Email):
    • Who: [email protected] (or other locations’ regional users).
    • What it does: Looker Studio uses its “Filter by Email” feature to detect the active viewer’s Google Account email, and passes this email to filter the rls_email column in the pre-compiled table.
    • Why it needs to be in admin_emails: The dbt RLS macro appends every row in the presentation tables to all admin emails in stg_google_sheets_admin_emails. This allows admins to see everything. If you are signed into Chrome as [email protected], the table filters by your email and returns all rows.

If you were to add the Service Account email to the admin_emails sheet, it would have no effect, because Looker never passes the Service Account’s email as the viewer’s identity to the “Filter by Email” filter; it always passes your personal logged-in Chrome profile email.

Graph View

Backlinks