Understanding the Google Ads dataset and identifying key KPIs to drive actionable business insights. This project assesses campaign performance, evaluates cost efficiency, and uncovers opportunities to improve engagement and conversions across devices and time periods.
- Rows: 2601
- Columns:
Ad_ID, Campaign_Name, Clicks, Impressions, Cost, Lead, Conversions, Conversion Rate, Sale_Amount, Ad_Date, Location, Device, Keyword - Known issues: inconsistent date formats, spelling errors, duplicate rows, mixed currency symbols, missing values
- Data source:[Google Ads sales dataset] (https://www.kaggle.com/datasets/nayakganesh007/google-ads-sales-dataset/data)
- Power BI Desktop (Data visualization, dashboard design, and data modeling)
- Power Query (Data cleaning, transformation)
- DAX (All custom measures and KPIs)
- Excel (Data cleaning - fixing typos, spacing issues, and inconsistent capitalization, handling, numeric Conversion blank cells, data transformation
- GitHub (Version control and project documentation)
After data (This dataset simulates a real Google Ads campaign promoting data analytics courses) cleaning and arrangement, the next step is preparing the dashboard. This section demonstrates my approach to using Power BI, highlighting how I translate raw data into actionable insights. It includes three pages:
- Page 1 – an overview summarizing investment, engagement, conversion, and revenue.
- Page 2 – analyzing clicks, impressions, CTR, device breakdown, and top keywords.
- Page 3 – focusing on conversion rates, cost per acquisition, cost per lead, and sales trends.
Intro
- 🧩 This page shows how daily spend translates into engagement, leads, and revenue.
- 🧩 Highlights where revenue spikes occur and how efficiently budget is being used.
- 🧩 ROAS and conversion trends indicate overall campaign health and scalability.
🎯 Key Insights
- 🔑 Total campaign spend: $559,240 generated $3.886M in revenue, achieving an overall ROAS of 6.95.
- 🔑 Week 1 → Week 3: Revenue steadily increased ($860k → $946k) with moderate spend growth → campaign scaling without losing efficiency.
- 🔑 Daily revenue fluctuations: Revenue fluctuates more than spend → certain days outperform without higher investment, signaling highly responsive audience windows.
- 🔑 Conversion Rate: Consistent at ~4.76%, indicating stable lead quality.
🔍 Performance Interpretation
- This page evaluates how efficiently budget drives revenue.
- Daily surges, e.g., Nov 14 ($175k revenue with $25.6k spend), highlight high-performing audience windows.
- Stable ROAS (~6.8–7.4) indicates efficient campaign delivery — allocating spend toward high-ROAS days could increase returns without increasing total budget.
Intro
- 🧩 This page helps us understand how effectively campaigns capture user attention — and how much that attention costs.
- 🧩 CTR trends reveal "how engaging our ad creatives and audience targeting are.
- 🧩 CPC trends show how much is paid for each click — reflecting competition and targeting quality.
- 🧩 CPM & CPL trends indicate overall efficiency and lead acquisition cost, providing a full picture of traffic value.
🎯 Key Insights
- 🔑 CTR stabilized mid-month after early fluctuations — likely due to campaign learning curve completion and refined audience targeting.
- 🔑 CPC slightly decreased toward the end of the month, indicating cost optimization and improved delivery efficiency.
- 🔑 Overall attention-cost efficiency improved as CPC trended slightly downward while CTR remained stable.
- 🔑 The 7-day moving average line filters out a clear mid-month stabilization trend — the campaign’s learning phase had completed, and consistent audience engagement was achieved.
📊 KPI Snapshot
| KPI | Value |
|---|---|
| 👁️ Impressions | 11,760,515 |
| 👆 Clicks | 345,725 |
| 🎯 CTR | 2.94% |
| ⚖️ CPC | $1.62 |
| 💰 Cost | $559,240 |
🔍 Performance Interpretation
- This page uncovers the true cost of attention — how much we pay for each user’s curiosity. When CTR increases but CPC also rises, we must ask:
- 👉 Are we paying too much for the same level of attention?
Intro This page analyzes how effectively the campaign turned generated leads into actual revenue. It highlights both conversion journey dynamics and revenue accumulation, linking user engagement quality to tangible business impact.
🎯 Key Insights
- 🔑 Stable conversion efficiency: CR remained between 4.4–5.3% throughout November, indicating consistent lead quality and audience relevance.
- 🔑 Conversion momentum: Converted leads peaked mid-month, aligning with revenue acceleration phases.
- 🔑 Cumulative revenue: Reached $3.89M by month-end, maintaining steady upward momentum.
- 🔑 Cost stability: Despite daily variations, CPC, CPL, and CPA remained within optimal ranges, supporting sustained profitability.
🔍 Performance Interpretation
- 👉 The campaign maintained conversion stability even during volume peaks → suggests strong alignment between targeting and audience behavior.
- 👉 Cumulative revenue slope shows consistent acceleration, confirming the scalability of the campaign strategy.
- 👉 CPL and CPA stability indicate efficient ad spend allocation — minimizing acquisition cost volatility.
- ⚙️ Total Sales Revenue=
SUM(GoogleAds_DataAnalytics_Sal[Sale_Amount]) - Enables understanding of the total revenue generated by the campaign - Spotlight [1]
- ⚙️ Total Leads=
SUM(GoogleAds_DataAnalytics_Sal[Leads]) - Tracks lead generation performance - Spotlight [1]
- ⚙️ Total Impressions=
SUM(GoogleAds_DataAnalytics_Sal[Impressions]) - Gauges campaign reach - Spotlight [1]
- ⚙️ Total Cost=
SUM(GoogleAds_DataAnalytics_Sal[Cost]) - Evaluates spending efficiency - Spotlight [1]
- ⚙️ Total Conversions=
SUM(GoogleAds_DataAnalytics_Sal[Conversions]) - Measures conversion success - Spotlight [1]
- ⚙️ Total Clicks=
SUM(GoogleAds_DataAnalytics_Sal[Clicks]) - Assesses engagement levels - Spotlight [1]
- ⚙️ Date Table=
ADDCOLUMNS(CALENDAR(MIN(GoogleAds_DataAnalytics_Sal[Ad_Date]),MAX(GoogleAds_DataAnalytics_Sal[Ad_Date])), "ShortDate_EN",FORMAT([Date], "dd MMM", "en-US")) - Spotlight [2] , Spotlight [3] , Spotlight [6]
- ⚙️ Week of Month= VAR CurrentDate = 'Date Table'[Date] VAR MonthStart = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1) RETURN "Week " & INT((DAY(CurrentDate) - 1) / 7) + 1 Spotlight [4], Spotlight [5]
- ⚙️ Final Conversion Rate (CR) =
DIVIDE([Total Conversions], [Total Clicks], 0) - Measures how effectively ad clicks turned into conversions, indicating overall campaign efficiency
- ⚙️ ROAS (Return on Ad Spend =
DIVIDE([Total Sales Revenue], [Total Cost], 0) - Evaluates revenue generated per unit of ad spend to assess campaign profitability - ⚙️ CPC (Cost per Click) =
DIVIDE([Total Cost],[Total Clicks],0) - Evaluates how much each click costs.
- ⚙️ CTR (Click-Through Rate) =
DIVIDE([Total Clicks],[Total Impressions],0) - Measures the efficiency of converting impressions into clicks — a direct indicator of ad engagement
- ⚙️ Click Share =
DIVIDE([Total Clicks],CALCULATE([Total Clicks],ALL('GoogleAds_DataAnalytics_Sal'[Device])),0) - Shows how click activity is distributed across devices, helping identify which platforms dominate user engagement. - 🔹Spotlight [7]
- ⚙️ CPL (Cost per Lead) =
DIVIDE([Total Cost],[Total Leads],0) - Evaluates acquisition cost efficiency.
- ⚙️ CPM =
DIVIDE([Total Cost], [Total Impressions] / 1000, 0) - Tracks cost per thousand impressions to gauge spend efficiency
- ⚙️ CTR_7d_MA =
CALCULATE(AVERAGEX(DATESINPERIOD('Date Table'[Date],LASTDATE('Date Table'[Date]), -7, DAY), [CTR (Click-Through Rate)])) - Tracks short-term engagement stability by smoothing daily CTR fluctuations. Helps identify consistent performance patterns and detect sudden drops or spikes that may signal creative fatigue or targeting misalignment. - Spotlight [8]
- ⚙️ Converted Leads Daily = SUMX('GoogleAds_DataAnalytics_Sal', 'GoogleAds_DataAnalytics_Sal'[Leads] * [Final Conversion Rate (CR)]) - EstimateS effective leads generated daily -Spotlight [9]
- ⚙️ CPA (Cost per Acquisition) =
DIVIDE([Total Cost],[Total Conversions],0) - Assess cost efficiency per conversion - Spotlight [10]
- ⚙️ Cumulative Revenue =
CALCULATE([Total Sales Revenue],FILTER(ALLSELECTED('Date Table'[Date]), 'Date Table'[Date] <=MAX('Date Table'[Date]))) - Tracks revenue accumulation over time to reveal momentum and pacing relative to conversion efficiency, enabling evaluation of both sales performance and funnel quality in a single view.* - Spotlight [11]
🔹 Spotlight [1] – Total Sales Revenue / Total Leads / Total Clicks / Total Conversions / Total Cost / Total Impressions
Using SUM() ensures that row-level data (e.g., clicks, cost) are aggregated respecting the active filter context — such as selected campaign, device, or date — which makes these base measures flexible across visuals.
🔹 Spotlight [2] – Using CALENDAR(MIN(), MAX())generates a contiguous date range from the dataset’s minimum to maximum dates. Ensures all potential dates exist, supporting consistent time-based grouping and avoiding gaps in visuals.
🔹 Spotlight [3]- Using ADDCOLUMNS(), adds calculated columns to a table, allowing dynamic attributes like formatted date labels or time buckets to be attached directly to a date table.
🔹 Spotlight [4]- Using ****VAR ... RETURN stores intermediate calculations within a DAX formula, improving readability and avoiding repeated computations. Example: store CurrentDate or MonthStart for Week of Month calculation. Improves both readability and performance by storing intermediate results, reducing redundant recalculations.
🔹 Spotlight [5] - INT() , rounds down a decimal number to the nearest integer. In Week of Month calculation, it converts the fractional week number into a discrete “Week 1 / Week 2 / …” label. Ensures clean categorical grouping for week segmentation instead of decimals.
🔹 Spotlight [6]- FORMAT() transforms a date or number into a specific text format for display purposes. Here, formats Date as dd MMM in English.
🔹 Spotlight [7] – Uses CALCULATE() with ALL() to ignore device-level filters and compute each device’s share of total clicks across all devices. This allows fair comparison even when visual filters are applied.
🔹 Spotlight [8] – Combines DATESINPERIOD() and AVERAGEX() inside CALCULATE() to compute a dynamic 7-day rolling average of CTR.
DATESINPERIOD()generates a moving window of dates ending with the latest visible date in context (LASTDATE()) and extending backward 7 days.AVERAGEX()iterates through this 7-day range, averaging CTR values for each day.- Wrapping in
CALCULATE()ensures the expression respects report filters while dynamically shifting the date context. This combination smooths daily volatility and highlights medium-term engagement trends, offering a clearer view of audience behavior stability.
🔹 Spotlight [9] – SUMX()Iterates row by row, applying [Final Conversion Rate] to each day’s lead count, then sums the results to estimate total converted leads.
🔹 Spotlight [10] – DIVIDE()Safe division function that avoids errors when the denominator is zero. Used in CPA to compute cost per successful conversion.
🔹 Spotlight [11] – Uses CALCULATE with a date-based FILTER to accumulate revenue up to the current period.
ALLSELECTED keeps user-applied filters (e.g., campaign, channel) while maintaining continuous date logic. Enables accurate time-based revenue tracking within filtered contexts.
🔹 Spotlight [9] – SUMX()Iterates row by row, applying [Final Conversion Rate] to each day’s lead count, then sums the results to estimate total converted leads.
🔹 Spotlight [10] – DIVIDE()Safe division function that avoids errors when the denominator is zero. Used in CPA to compute cost per successful conversion.
🔹 Spotlight [11] – Uses CALCULATE with a date-based FILTER to accumulate revenue up to the current period.
ALLSELECTED keeps user-applied filters (e.g., campaign, channel) while maintaining continuous date logic. Enables accurate time-based revenue tracking within filtered contexts.
- This dashboard was built as part of my journey to master advanced Power BI techniques and DAX calculations. While working on it, I explored how to make data tell a story and highlight trends dynamically.