An end-to-end analytics workflow covering SQL transformations, data modeling, CRM attribution, and Power BI reporting.
This case study demonstrates an end-to-end paid media analytics workflow built with BigQuery, Power BI and CRM data. The solution prepares raw advertising data in SQL, models multiple marketing data sources in Power BI, and provides campaign-level reporting across spend, engagement, lead generation, and pipeline metrics.
All company-specific identifiers, campaign names, and performance values have been anonymized.
The reporting workflow combines paid media data from Google Ads with CRM funnel data from Salesforce. Google Ads performance is transformed in BigQuery using reusable SQL views before being imported into Power BI, while CRM data is loaded separately from Salesforce. Both sources are then brought together in the Power BI semantic model through shared Date and Campaign dimensions.
Google Ads
↓
BigQuery
↓
SQL Transformation Views
↓
Google Ads Fact Tables
↓
Power BI
↘
Shared Dimensions
• dim_date
• dim_campaign
↗
Salesforce
↓
CRM Fact Tables
↓
Power BI
Data preparation is handled as close to the source as possible: advertising aggregations and joins are performed in BigQuery, while cross-source modeling, CRM attribution and analytical measures are handled in Power BI.
I created a reusable BigQuery view at daily campaign × ad group × keyword grain. Performance metrics and conversion metrics are aggregated separately before joining to avoid row multiplication.
WITH keyword_stats AS (
SELECT
segments_date AS date,
campaign_id,
ad_group_id,
keyword_id,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SUM(cost_micros) / 1000000 AS spend
FROM `marketing_data.google_ads.keyword_basic_stats`
WHERE segments_date >= DATE '2025-01-01'
GROUP BY
date,
campaign_id,
ad_group_id,
keyword_id
),
keyword_conversions AS (
SELECT
segments_date AS date,
campaign_id,
ad_group_id,
keyword_id,
SUM(
CASE
WHEN conversion_action_name = 'Marketing Qualified Lead'
THEN conversions
ELSE 0
END
) AS mqls
FROM `marketing_data.google_ads.keyword_conversion_stats`
WHERE segments_date >= DATE '2025-01-01'
GROUP BY
date,
campaign_id,
ad_group_id,
keyword_id
),
keyword_metadata AS (
SELECT
campaign_id,
ad_group_id,
keyword_id,
keyword,
match_type,
keyword_status
FROM `marketing_data.google_ads.keyword_metadata`
WHERE data_date = latest_date
)
SELECT
s.date,
s.campaign_id,
s.ad_group_id,
s.keyword_id,
k.keyword,
k.match_type,
k.keyword_status,
s.impressions,
s.clicks,
s.spend,
COALESCE(c.mqls, 0) AS mqls
FROM keyword_stats s
LEFT JOIN keyword_conversions c
ON s.date = c.date
AND s.campaign_id = c.campaign_id
AND s.ad_group_id = c.ad_group_id
AWITH campaign_stats AS (
SELECT
segments_date AS date,
campaign_id,
SUM(clicks) AS clicks,
SUM(impressions) AS impressions,
SUM(cost_micros) / 1000000 AS spend
FROM `marketing_data.google_ads.campaign_basic_stats`
WHERE segments_date >= DATE '2025-01-01'
GROUP BY
date,
campaign_id
),
campaign_metadata AS (
SELECT
campaign_id,
campaign_name
FROM `marketing_data.google_ads.campaign_metadata`
WHERE data_date = latest_date
)
SELECT
s.date,
s.campaign_id,
m.campaign_name,
s.clicks,
s.impressions,
s.spend
FROM campaign_stats s
LEFT JOIN campaign_metadata m
ON s.campaign_id = m.campaign_id
WHERE s.spend > 0;WITH campaign_stats AS (
SELECT
segments_date AS date,
campaign_id,
SUM(clicks) AS clicks,
SUM(impressions) AS impressions,
SUM(cost_micros) / 1000000 AS spend
FROM `marketing_data.google_ads.campaign_basic_stats`
WHERE segments_date >= DATE '2025-01-01'
GROUP BY
date,
campaign_id
),
campaign_metadata AS (
SELECT
campaign_id,
campaign_name
FROM `marketing_data.google_ads.campaign_metadata`
WHERE data_date = latest_date
)
SELECT
s.date,
s.campaign_id,
m.campaign_name,
s.clicks,
s.impressions,
s.spend
FROM campaign_stats s
LEFT JOIN campaign_metadata m
ON s.campaign_id = m.campaign_id
WHERE s.spend > 0;ND s.keyword_id = c.keyword_id
LEFT JOIN keyword_metadata k
ON s.campaign_id = k.campaign_id
AND s.ad_group_id = k.ad_group_id
AND s.keyword_id = k.keyword_id
WHERE s.spend > 0;
WITH campaign_stats AS (
SELECT
segments_date AS date,
campaign_id,
SUM(clicks) AS clicks,
SUM(impressions) AS impressions,
SUM(cost_micros) / 1000000 AS spend
FROM `marketing_data.google_ads.campaign_basic_stats`
WHERE segments_date >= DATE '2025-01-01'
GROUP BY
date,
campaign_id
),
campaign_metadata AS (
SELECT
campaign_id,
campaign_name
FROM `marketing_data.google_ads.campaign_metadata`
WHERE data_date = latest_date
)
SELECT
s.date,
s.campaign_id,
m.campaign_name,
s.clicks,
s.impressions,
s.spend
FROM campaign_stats s
LEFT JOIN campaign_metadata m
ON s.campaign_id = m.campaign_id
WHERE s.spend > 0;
Campaign metadata uses only the latest snapshot to ensure a single metadata row per campaign and prevent one-to-many joins from duplicating performance metrics.
Google Ads and CRM data did not share a reliable cross-platform campaign ID. I therefore created a normalized campaign key to support attribution between both sources while retaining the original campaign name for reporting.
let
campaign =
if [Cookie UTM Campaign] <> null
and Text.Trim([Cookie UTM Campaign]) <> ""
and [Cookie UTM Campaign] <> "-"
then [Cookie UTM Campaign]
else if [Most Recent UTM Campaign] <> null
and Text.Trim([Most Recent UTM Campaign]) <> ""
and [Most Recent UTM Campaign] <> "-"
then [Most Recent UTM Campaign]
else null
in
if campaign = null then null
else
Text.Lower(
Text.Trim(
Text.Replace(campaign, "_", " ")
)
)