Quartz 5

Daily Work Log - 2026-06-25

Properties1
tagsdaily-note--engineering

1 min read

Session Log: 2026-06-25

📋 Session Overview

In this session, we expanded our marketing analytics suite by designing and deploying Google Ads funnel-performance presentation views. Additionally, we created an automated deployment script to establish secure, credential-locked service account access for our Looker Studio reports.

🚀 Key Accomplishments

1. Google Ads Funnel Stats Presentation (pres_google_ads_funnel_stats)

We designed a unified funnel statistics presentation view:

  • The Model: pres_google_ads_funnel_stats.sql
  • Metrics Consolidated: Aggregates top-of-funnel metrics (impressions, clicks) with bottom-of-funnel KPIs (conversion counts, cost, and Cost Per Acquisition - CPA) at the territory and campaign levels.
  • The Benefit: Allows franchise owners to instantly trace lead generation performance from the initial search impression down to the final conversion action.

2. Automated Looker Service Account Provisioning

To secure and establish permanent, non-expiring data links between Google BigQuery and Looker Studio reports, we implemented an automated security credential script:

  • The Tool: scripts/setup_looker_service_account.sh (or setup_looker_service_account.py)
  • Functionality: Grants raw read access (bigquery.dataViewer and bigquery.user roles) on the analytics_prod dataset to the dedicated Looker Studio system service account, resolving report authorization issues.

📁 Files Created & Modified

  • models/presentation/google_ads/pres_google_ads_funnel_stats.sql
  • scripts/setup_looker_service_account.sh

Graph View