🎙️ Session: 30 11:28 CDT - Notes by Gemini

Executive Summary

This meeting focused on defining an immediate, low-friction strategy for centralizing Pocket Suite data into a Google Workspace environment, primarily BigQuery, to support client lifecycle management in HubSpot and enable comprehensive reporting.

The core decision is to leverage an existing Google Sheet workflow as a primary handoff point. This “master sheet” will receive weekly data exports from Pocket Suite, bypassing direct database firewall integration challenges. Key data requirements include consistent unique client identifiers (phone numbers, UUIDs) and critical metrics such as income, clients, bookings, and payments.

The team aims to establish BigQuery as the central “single source of truth” for this data, offloading complex calculations from AppScript for scalability. A detailed plan outlining metrics and integration strategy will be documented by Liam Hahn for CTO review before full implementation. This approach is seen as a crucial step towards a more robust data architecture that supports future analytical and operational needs, including the development of agentic systems.

Key Architectural Frameworks & Core Principles

• Single Source of Truth: Establish BigQuery as the centralized repository for Pocket Suite data, serving as the definitive source for metrics across various systems (e.g., Looker Studio, HubSpot).
• Lowest Friction Data Handoff: Utilize an existing Google Sheet workflow as an intermediary “master sheet” for weekly data exports from Pocket Suite. This strategy is explicitly chosen to bypass direct firewall integration challenges with the Pocket Suite database.
• Offloading Calculations: Transition complex data calculations from AppScript within Google Sheets to BigQuery for enhanced scalability and efficiency, especially given Google Sheet cell limitations.
• Consistent Unique Identifiers: Mandate the inclusion of unique client identifiers (e.g., phone numbers, UUIDs) in all data exports to facilitate accurate cross-platform data integration and lifecycle management (e.g., in HubSpot).
• Read-Only Data for External Systems: The primary goal is to provide data in a read-only format to external systems, avoiding direct modification of source data and simplifying integration strategies.
• Iterative MVP Approach: Focus on establishing a minimum viable product for data delivery (daily/weekly/monthly export to a warehouse) before pursuing more complex real-time integrations or extensive metric enrichment.
• Strategic API/Service Account Integration (Future Consideration): While a Google Sheet intermediary is the immediate solution, longer-term discussions include exploring secure API, SSH, or Google Workspace service account access for direct data interaction, maintaining data within Pocket Suite’s infrastructure while allowing external access.

Flight Plan (Action Items)

• [Liam Hahn] Modify Workflow: Update the current Pocket Suite reporting workflow to export weekly data into a single “master Google Sheet” instead of generating new copies.
  • Deadline: Aim for proof of concept within one analytics day.
• [Liam Hahn] Document Detailed Data Plan: Create a detailed plan outlining:
  • The proposed data approach.
  • Specific metrics to be delivered (e.g., client name, phone, email, UUID, creation/update dates, client status; payment timestamps, amount, location).
  • The system hook-in strategy (master sheet to BigQuery, BigQuery to HubSpot/Looker Studio).
  • Deliverable: Send the documented plan to Kevin Dockman for review and feedback.
• [Liam Hahn] Secure CTO Review: Obtain approval and feedback from the CTO on the proposed data delivery approach before proceeding with full implementation.
  • Note: Alyson Fisher noted that the CTO is already scheduled to discuss long-term data planning in a leader team meeting tomorrow, making this a timely discussion point.
• [Kevin Dockman / Alyson Fisher] Review Documented Plan: Provide feedback and edits on the detailed data plan once received from Liam Hahn to ensure alignment.

Key Structural Sections of Discussion

Data Conversion Reporting and Dashboard Context

• Immediate Need: Kevin Dockman expressed an immediate need for data, specifically beta conversion reporting broken down by location, noting its richness and value. (00:02:46)
• Meeting Purpose: This meeting served as a “reverse demo” to the product team, combining insights from a quarterly review and previous data discussions. (00:03:37)
• MVP Goal: The immediate goal is to establish a Minimum Viable Product (MVP) for data delivery: getting data into a warehouse (like a BigQuery table) as close to real-time as possible (daily, weekly, or monthly) to serve as a single source of truth. (00:04:42)

Current Dashboard and Data Flow Demonstration

• Existing Dashboard: Kevin demonstrated their existing data dashboard, visualizing data from external sources like Google Ads, Google Business Profile, and Google Search Console. (00:05:42)
• Location Filtering: Data can be filtered by location (e.g., Louisville) to display relevant metrics from all connected sources for that specific location. (00:06:47)
• Desired Pocket Suite Structure: The ultimate structure for Pocket Suite data would show metrics and trend lines for key data points such as income, clients, and conversions over a date range. (00:08:10)

Ideal Metrics and Revenue Reporting

• Core Metrics: Ideal Pocket Suite data would include 6-8 basic metrics (income, clients, bookings, stop payments/invoices) from existing reports. (00:06:47)
• Second Layer Reporting: Integration should enable a second layer of reporting to connect impressions from external sources to new clients/leads, evaluations, and final income, allowing for Return on Ad Spend (RoAS) calculation. (00:08:10)
• BigQuery Foundation: This data structure relies on exporting data into a BigQuery database for integration with other systems like HubSpot. (00:09:09)

Proposed Solution for Data Handoff

• Lowest-Friction Path: Liam Hahn suggested modifying the existing workflow to update a single “master Google Sheet” weekly. This sheet would serve as the handoff point to BigQuery and Looker Studio, bypassing direct firewall integration issues. (00:11:10)
• AppScript Modification: Liam estimated about 20 minutes to change the AppScript to update the master sheet with new corporate-level data, with more time needed for adding and validating new metrics. (00:12:27)
• Constraints and Data Structure: Kevin Dockman highlighted Google Sheet’s million-cell limit, a potential issue for long-standing locations. The team aims to offload calculations from AppScript to BigQuery and create core corporate tables for clients, bookings, and payments to manage data volume and completeness. (00:13:23)

Client Data and Life Cycle Management Requirements

• Essential Client Data: Request for basic client data including name, phone number, email address, location assignment, and a unique identifier (UUID confirmed by Liam as helpful and exposed to Zapier). (00:14:29, 00:18:37)
• HubSpot Integration: This data is crucial for integrating with HubSpot to define customers by their lifecycle status (Lead, Opportunity, Customer). (00:16:08)
• Data Export Needs: Start with a CSV export for initial import into HubSpot and BigQuery, followed by real-time updates for changes (e.g., last names, emails) to maintain audience accuracy. (00:16:08)
• Dog Wizard IDs: Kevin plans to build internal “Dog Wizard IDs” to link Pocket Suite client data with other platforms (Quo, HubSpot) via a master key, utilizing the UUID provided by Pocket Suite. (00:19:41)
• Zapier Role: Zapier is seen as an “in between the lines” solution for urgent updates between weekly data exports, particularly for critical metrics or customer lifecycle stage changes. (00:21:27)

Next Steps for Alignment and Approval

• Commitment to Weekly Updates: Liam Hahn committed to providing weekly data updates by modifying their current workflow for broader reports. (00:17:34)
• Documenting the Approach: Liam will document the proposed approach, including metrics and the hook-in process, for review. (00:22:44)
• Proof of Concept & CTO Review: Liam estimates one analytics day for a proof of concept and plans to seek CTO approval before full delivery. (00:25:57)
• CTO Meeting Alignment: Alyson Fisher noted that their CTO is already scheduled to discuss long-term data planning in a leader team meeting, making this request a perfect case study. (00:27:28)