Ad Data Reporting Automation

B. Workflow

The Ad Data Reporting Automation runs as a scheduled data pipeline that moves reporting data from ad platforms and HubSpot through Google Sheets, transforms it through Apps Script, and lands it in normalized BigQuery reporting tables ready for downstream dashboards and AI-assisted reporting workflows. The pipeline progresses through four distinct stages: data ingestion, synchronization, HubSpot attribution processing, and the reporting layer. Each stage handles a specific responsibility, and the architecture is designed so that the same general pipeline structure can be replicated across additional client accounts.

Segment 1: Data Ingestion

Purpose

The purpose of this segment is to extract reporting data from external ad platforms and HubSpot and land it into Google Sheets as the staging layer for the rest of the pipeline. No transformation, attribution, or warehouse-level processing occurs in this segment. Its sole responsibility is to pull source data on a scheduled refresh so that downstream synchronization workflows have consistent, predictable inputs to work with.

1. Supermetrics Ingestion

Supermetrics acts as the ingestion tool for the reporting pipeline. It connects to external marketing and CRM systems, runs configured queries against each platform, and writes the structured results into platform-specific tabs within a per-client reporting spreadsheet in Google Sheets.

The current reporting environment uses Supermetrics to ingest data from ad platforms (such as Google Ads, LinkedIn, Reddit, Pinterest) and HubSpot. Each platform feeds its own tab within the spreadsheet, and a Mapping tab is maintained separately to support UTM-to-campaign translation logic used in later stages of the pipeline.

Supermetrics is configured to refresh automatically each day at 3:00 AM PDT, with each run performing a full refresh of the Google Sheets staging layer. The refresh can also be triggered manually when required.

2. Google Sheets Staging Layer

Google Sheets serves as the staging layer between Supermetrics ingestion and BigQuery synchronization. Each platform deposits its data into a dedicated tab, and the Mapping tab stores the UTM-to-campaign translation logic used during HubSpot attribution processing.

This staging layer is intentionally lightweight. It does not apply transformation, modeling, or attribution logic. Its sole responsibility is to hold the most recent Supermetrics output in a consistent, predictable structure that Apps Script can read during synchronization runs.

Segment 2: Synchronization

Purpose

The purpose of this segment is to synchronize ad platform reporting data from Google Sheets into native BigQuery tables. Native BigQuery tables physically store reporting data inside the warehouse, allowing downstream tools to query data directly without depending on external Google Sheets access. This segment handles the simpler synchronization workflow used for ad platform data. HubSpot synchronization includes additional attribution logic and is handled separately in Segment 3.

3. Apps Script Synchronization

A Google Apps Script attached to the client reporting spreadsheet serves as the operational synchronization engine for the pipeline. Because native BigQuery tables do not automatically synchronize with Google Sheets, the Apps Script handles all data extraction, transformation, and table refresh operations required to keep the warehouse aligned with the staging layer.

For each ad platform reporting tab, the Apps Script performs the following operations: it reads reporting rows from the spreadsheet tab, applies data type casting and transformation logic where required, standardizes platform reporting structures, refreshes the corresponding native BigQuery reporting table, and inserts the transformed reporting rows into BigQuery. This synchronization process runs across all four ad platform datasets (Google Ads, LinkedIn, Reddit, Pinterest) and produces normalized native BigQuery tables ready for downstream reporting workflows.

The Apps Script synchronization process is configured to run automatically each day at 7:00 AM PDT, after Supermetrics has refreshed the staging layer. Each run performs a full refresh so the native BigQuery reporting tables remain aligned with the spreadsheet staging layer. The synchronization workflow may also be triggered manually when required.

Segment 3: HubSpot Attribution Processing

Purpose

The purpose of this segment is to transform raw HubSpot lead data into attributed, reporting-ready records that connect lead outcomes back to the paid media activity that drove them. Unlike ad platform synchronization, which is a relatively direct sync, HubSpot processing requires normalization, qualification mapping, event value parsing, and campaign attribution before the data becomes usable for cross-platform reporting.

4. Transformation and Normalization

During synchronization, Apps Script reads HubSpot booking records from the Google Sheets staging layer and applies a series of transformation rules used to normalize reporting values. These transformations include platform derivation from utm_source values (for example, google/adwords maps to google_ads, reddit maps to reddit, linkedin maps to linkedin, and all other values map to other), budget range parsing (for example, "$15,000 - $30,000" is parsed into a normalized upper-bound event value of 30000), qualification tier mapping (QUALIFIED and HIGH_PRIORITY map to MQL, while UNQUALIFIED maps to Unqualified), event type cleanup, and attribution key generation.

The partially transformed records are written into the hubspot_staging table, which serves as an intermediate processing layer used during normalization and mapping workflows.

5. UTM Attribution Mapping

After staging records are created, Apps Script joins the staging data against the utm_campaign_key mapping table. This attribution step connects HubSpot lead records back to standardized advertising campaign and ad group structures using UTM parameter mapping logic, with utm_param1 as the primary lookup key and utm_term as a fallback when utm_param1 is unavailable. Records that fail attribution mapping receive Unknown campaign or ad group values rather than being dropped from the dataset.

Once attribution processing is complete, the final transformed records are written into the hubspot_native table for use in dashboarding, reporting, and AI-assisted analysis workflows.

Segment 4: Reporting Layer

Purpose

The purpose of this segment is to expose the centralized BigQuery reporting data through normalized reporting views and connect downstream consumers (dashboards and AI-assisted reporting workflows) to a single, consistent reporting structure rather than multiple platform-specific tables.

6. master_view Reporting View

The primary reporting view is master_view. Its purpose is to provide a unified reporting structure that combines ad platform activity data and HubSpot attribution data into a centralized reporting layer. The view uses UNION ALL operations to vertically combine Google Ads, LinkedIn, Reddit, Pinterest, and HubSpot data into a shared normalized reporting schema.

Because the view combines multiple reporting sources into a unified schema, certain fields may contain NULL values depending on the originating data source. Ad platform rows primarily contain activity metrics such as spend, impressions, clicks, conversions, and engagement activity, while HubSpot rows primarily contain attribution and lead outcome fields such as hs_form_fills, event_value, qualification, event_type, and mql.

By exposing a shared normalized reporting structure, the master_view allows downstream reporting tools and AI workflows to query campaign performance and attribution data without needing platform-specific query logic for each reporting source.

7. Data Studio and Claude Reporting Workflows

Data Studio dashboards connect to the centralized BigQuery reporting datasets and provide always-on weekly performance dashboards across advertising performance, attribution activity, and lead outcome metrics. Dashboards are designed to consume normalized reporting data exposed through the master_view reporting view rather than querying platform-specific reporting tables independently, which simplifies dashboard configuration and improves reporting consistency.

Claude connects to the centralized BigQuery reporting datasets to support agenda preparation, reporting summaries, campaign performance analysis, attribution analysis, and monthly or quarterly reporting workflows. By connecting Claude to the same normalized reporting layer used by dashboards, reporting workflows can move beyond static dashboards into interactive querying and reporting assistance.