Marketing Analytics: SQL to Power BI

Build an end-to-end analytics pipeline from SQL to a Power BI dashboard.

Introduction

30 Second Summary

Every time you scroll past a sponsored post or click on an ad, a marketing team somewhere is trying to figure out if that campaign actually worked. Turning thousands of rows of raw ad data into clear answers is the core skill that separates data analysts from spreadsheet users.

In this project, you will build a complete analytics pipeline that takes 200,000 rows of real marketing campaign data through SQL analysis, Excel pivot tables, and a polished Power BI dashboard you can publish and share on LinkedIn.

What You'll Build

An end-to-end marketing campaign analytics pipeline that moves raw advertising data from a local SQL Server database through intermediate SQL queries and Excel exploration into a professional, interactive Power BI dashboard.

By the end of this project, you'll have:

  • A SQL Server database loaded with 200,000+ rows of real campaign data, queried using CTEs, window functions, and CASE statements.
  • An Excel workbook with pivot tables comparing campaign performance across channels and platforms.
  • A published, interactive Power BI dashboard with KPI cards, trend charts, DAX measures, and slicers ready to share on LinkedIn.
  • Secret Mission: Add a "What-If" budget reallocation simulator that lets viewers adjust spend across channels and see projected ROI changes in real time.

Are there any prerequisites?

You will need a free Kaggle account (for downloading the dataset) and a Microsoft account (for publishing your Power BI dashboard). This project runs on Windows, where all tools are available for free.

Install Your Analytics Toolkit

Before you can analyze marketing campaigns, you need the right tools installed on your machine.

In this step, you will install three core tools: SQL Server for storing and querying data, Python for pulling datasets from Kaggle, and Power BI Desktop for building your final dashboard.

In this step, get ready to:
  • Install SQL Server 2022 Express and SQL Server Management Studio (SSMS).
  • Install Python and the Kaggle CLI.
  • Verify Power BI Desktop and Excel are installed.
Install SQL Server 2022 Express and SSMS

SQL Server 2022 Express is a free database engine you will use to store and query 200,000+ rows of campaign data.

SSMS is the graphical interface for writing and running SQL queries against that database.

  • Open your browser and go to the SQL Server 2022 Express download page.
  • Scroll down to the Express section and click Download now.
  • This downloads a small file called SQL2022-SSEI-Expr.exe (about 4 MB).
  • Run SQL2022-SSEI-Expr.exe and select Basic as the installation type.
  • Accept the license terms and click Install.
  • Wait for the installation to complete. This may take a few minutes.

What is SQL Server Express?

SQL Server Express is the free edition of Microsoft's database engine. It supports up to 10 GB of storage per database, which is more than enough for this project's dataset.

The "Basic" installation handles everything automatically. You do not need to configure ports or services manually.

  • Now download SSMS from the SSMS download page.
  • Click the download link for the latest version of SSMS.
  • Run the downloaded installer.
  • If you see a User Account Control prompt, click Yes.
  • Click Install to proceed with the default settings.
  • Wait for the SSMS installation to finish. This download is larger and may take 5-10 minutes depending on your internet speed.

Why two separate installs?

SQL Server Express is the database engine that stores your data and runs queries behind the scenes. SSMS is the visual tool you use to interact with that engine. They are separate products that work together.

  • Open SSMS by pressing the Windows key, typing SSMS, and pressing Enter.
  • In the Connect to Server dialog, enter localhost\SQLEXPRESS in the Server name field.
  • Leave Authentication set to Windows Authentication.
  • Click Connect.

✔️ I see the Object Explorer panel

You are connected to your local SQL Server instance. You should see your server name in the Object Explorer on the left side of the window.

ⓧ I see a connection error

If SSMS cannot connect, the SQL Server service may not be running yet.

  • Press the Windows key, type Services, and press Enter.
  • Scroll down to find SQL Server (SQLEXPRESS).
  • If the status is not Running, right-click it and select Start.
  • Go back to SSMS and click Connect again.

If you are still having trouble, double-check that you typed the server name exactly as localhost\SQLEXPRESS with a backslash, not a forward slash.

Install Python and the Kaggle CLI

You need Python to run the Kaggle command-line tool, which downloads the marketing dataset directly to your machine without a manual browser download.

  • Open your browser and go to python.org/downloads.
  • Click the large Download Python button for the latest version.
  • Run the downloaded installer.
  • On the first screen, check the box labeled Add python.exe to PATH. This is critical.
  • Click Install Now.
  • Wait for the installation to complete, then click Close.

Do not skip the PATH checkbox

If you forget to check Add python.exe to PATH, the python and pip commands will not work in your Command Prompt. You would need to uninstall and reinstall Python with the box checked.

  • Open Command Prompt by pressing the Windows key, typing cmd, and pressing Enter.
  • Check your Python installation by running this command:
python --version

✔️ I see a version number

Python is installed and added to your PATH. You are ready to install the Kaggle CLI.

ⓧ 'python' is not recognized

This means Python was not added to PATH during installation.

  • Open Settings.
  • Select Apps, then Installed apps.
  • Find Python in the list and click Uninstall.
  • Re-download and run the installer from python.org.
  • This time, check Add python.exe to PATH before clicking Install Now.
  • Close and reopen Command Prompt, then run python --version again.
  • Install the Kaggle CLI by running this command:
pip install kaggle

What is the Kaggle CLI?

The Kaggle CLI is a command-line tool that lets you download datasets directly to your machine. It is the standard programmatic way to access Kaggle datasets without manually downloading through a browser.

Now you need to set up your Kaggle API credentials so the CLI can authenticate with your account.

  • Open your browser and go to kaggle.com.
  • Click your profile icon in the top right corner.
  • Select Settings.
  • Scroll down to the API section.
  • Click Create New Token.
  • This downloads a file called kaggle.json to your Downloads folder.
  • Create the .kaggle folder and move the credentials file into it by running these commands in Command Prompt:
mkdir C:\Users\%USERNAME%\.kaggle
move %USERPROFILE%\Downloads\kaggle.json C:\Users\%USERNAME%\.kaggle\kaggle.json

What does this command do?

The Kaggle CLI looks for your credentials at C:\Users\YourUsername\.kaggle\kaggle.json. The commands above create that folder and move the downloaded file into the correct location.

  • Verify the Kaggle CLI works by running:
kaggle --version

Seeing 'kaggle is not recognized'?

Close your Command Prompt and open a new one. The PATH changes from the Python installation sometimes require a fresh terminal session.

If the issue persists, Python's Scripts folder may not be in your PATH. Try running it with the full path:

python -m kaggle --version

Help me fix my Kaggle CLI installation on Windows.

Verify Power BI Desktop and Excel are installed

Power BI Desktop is the free visualization tool you will use to build your final interactive dashboard. Excel will be used for pivot table analysis in an intermediate step.

  • Check if Power BI Desktop is already installed by pressing the Windows key and typing Power BI Desktop.

✔️ I see Power BI Desktop in the results

  • Click to open it and confirm it launches to the start screen.
  • Close Power BI Desktop once you have confirmed it opens.

ⓧ Power BI Desktop is not found

You need to install Power BI Desktop. You have two options.

  • Recommended: Open the Microsoft Store app, search for Power BI Desktop, and click Install. This method provides automatic updates.
  • Alternative: Go to the Power BI Desktop download page and download PBIDesktopSetup_x64.exe. Run the installer and follow the prompts.

Once installed, open Power BI Desktop to confirm it launches correctly, then close it.

  • Check if Excel is installed by pressing the Windows key and typing Excel.
  • Click to open Excel and confirm it launches to the start screen.
  • Close Excel once you have confirmed it opens.

Don't have Excel?

Excel is part of Microsoft 365. If you do not have a Microsoft 365 subscription, you can use the free web version at office.com for the pivot table section of this project. Sign in with your Microsoft account.

Help me find an alternative to Excel for this project.

Your analytics toolkit is ready. Next up, you will download real marketing campaign data from Kaggle and load it into your SQL Server database.

Download and Load Campaign Data into SQL Server

With your analytics toolkit installed, you need data to analyze. Real analytics work starts with sourcing raw data and loading it into a database where you can query it.

In this step, you will pull 200,000+ rows of marketing campaign data from Kaggle using its CLI, create a dedicated database in SQL Server, and import the CSV so it is ready for querying.

In this step, get ready to:
  • Download the Marketing Campaign Performance Dataset from Kaggle using the CLI.
  • Create a new SQL Server database and table schema.
  • Import the CSV data into your database and verify the row count.
Download the dataset from Kaggle

The Kaggle CLI lets you pull datasets directly from the command line without downloading through a browser. This is how data professionals typically source datasets in their workflows.

  • Open Command Prompt by pressing the Windows key, typing cmd, and pressing Enter.
  • Download and unzip the Marketing Campaign Performance Dataset by running this command:
kaggle datasets download -d manishabhatt22/marketing-campaign-performance-dataset --unzip -p C:\CampaignData

What does this command do?

  • kaggle datasets download tells the Kaggle CLI to pull a dataset.
  • -d manishabhatt22/marketing-campaign-performance-dataset identifies the specific dataset by its owner and name.
  • --unzip automatically extracts the CSV from the downloaded zip file.
  • -p C:\CampaignData saves the file into a dedicated folder on your C: drive.
  • Confirm the download completed by running:
dir C:\CampaignData

You should see a CSV file listed in the output. This file contains approximately 200,000 rows of marketing campaign data spanning 5 advertising platforms.

Seeing a 403 Forbidden or authentication error?

This means your Kaggle API key is not in the correct location. Make sure your kaggle.json file is placed at C:\Users\YourUsername\.kaggle\kaggle.json (replace YourUsername with your actual Windows username).

If the .kaggle folder does not exist, create it manually and move your kaggle.json file into it.

Help me fix my Kaggle API authentication error

Create the CampaignAnalytics database in SSMS

Before importing the CSV, you need a database to hold it. You will create a new database and define a table schema that matches the dataset's columns.

  • Open SSMS by pressing the Windows key, typing SSMS, and pressing Enter.
  • Connect to your local instance using Server name: localhost\SQLEXPRESS.
  • In the Object Explorer panel on the left, right-click Databases.
  • Select New Database.
  • In the Database name field, type CampaignAnalytics.
  • Click OK.

You should see CampaignAnalytics appear under the Databases folder in Object Explorer.

  • Now create a table schema that matches the dataset. Click New Query in the toolbar at the top of SSMS.
  • Make sure CampaignAnalytics is selected in the database dropdown (top-left of the query window).
  • Create the table by pasting this SQL into the query window and clicking Execute (or pressing F5):
CREATE TABLE CampaignData (
    Campaign_ID INT IDENTITY(1,1) PRIMARY KEY,
    Campaign_Type NVARCHAR(100),
    Target_Audience NVARCHAR(100),
    Duration INT,
    Channel_Used NVARCHAR(100),
    Conversion_Rate FLOAT,
    Acquisition_Cost FLOAT,
    ROI FLOAT,
    Location NVARCHAR(100),
    Language NVARCHAR(50),
    Clicks INT,
    Impressions INT,
    Engagement_Score FLOAT,
    Customer_Segment NVARCHAR(100),
    Date DATE,
    Company NVARCHAR(200)
);

What does this SQL do?

This CREATE TABLE statement defines the structure for your campaign data. Each column matches a field in the Kaggle CSV.

  • Campaign_ID is an auto-incrementing primary key that uniquely identifies each row.
  • NVARCHAR columns hold text values like channel names, locations, and audience segments.
  • FLOAT columns store decimal numbers like conversion rates, costs, and ROI percentages.
  • INT columns store whole numbers like clicks and impressions.

You should see a "Commands completed successfully" message in the Messages tab at the bottom.

Seeing an error about the database context?

Make sure the database dropdown at the top of your query window shows CampaignAnalytics, not master. If it shows master, click the dropdown and switch to CampaignAnalytics before executing again.

Help me fix my SQL Server database context error

Import the CSV into SQL Server

SSMS includes an Import Flat File wizard that reads CSV files and loads them directly into a table. This saves you from writing bulk insert commands manually.

  • In Object Explorer, right-click the CampaignAnalytics database.
  • Select Tasks.
  • Click Import Flat File.
  • In the wizard, click Next on the Introduction page.
  • Click Browse and navigate to C:\CampaignData. Select the CSV file.
  • In the New table name field, type CampaignData.
  • Click Next to preview the data. You should see up to 50 rows displayed.
  • On the Modify Columns page, review the detected column types. The wizard auto-detects data types from the CSV content.
  • Click Next to reach the Summary page.
  • Click Finish to start the import.

What if the table already exists?

If you created the table using the SQL query in the previous substep, the wizard may warn that the table already exists. In that case, you can either drop the empty table first (run DROP TABLE CampaignData; in a new query) or let the wizard create the table with its own schema. Either approach works because the wizard maps the CSV columns automatically.

The import may take a moment with 200,000 rows. Wait for the Results page to show a success message.

  • Verify the import by opening a new query window and running this SQL:
SELECT COUNT(*) FROM CampaignData;

You should see a count of approximately 200,000 rows. This confirms all the campaign data is loaded and ready to query.

  • Run a quick preview to see what the data looks like:
SELECT TOP 10 * FROM CampaignData;

You should see 10 rows with values across all columns including campaign types, channels, click counts, and ROI figures.

Seeing 0 rows or an import error?

Check that you selected the correct CSV file in C:\CampaignData. If the folder is empty, re-run the Kaggle download command from the first substep.

If the import failed due to data type mismatches, drop the table and re-run the Import Flat File wizard. Let the wizard auto-detect column types instead of using the manually created schema.

Help me troubleshoot my SQL Server CSV import

Your database is loaded with real marketing data. Next up, you will write intermediate SQL queries to uncover performance insights across channels, audiences, and time periods.

Perform Intermediate SQL Analysis

Your database is loaded with 200,000 rows of real marketing campaign data. Now it is time to extract meaningful insights from it.

In this step, you will write intermediate SQL queries that go beyond basic SELECT statements. You will use aggregations, CASE statements, window functions, and CTEs to calculate key performance indicators like ROI by channel and cumulative spend over time.

In this step, get ready to:
  • Write aggregation queries with CASE statements to categorize campaign performance.
  • Use window functions (ROW_NUMBER, SUM OVER, LAG) to rank and compare campaigns.
  • Create a reusable SQL VIEW and export its results to CSV for Excel.
Write aggregation queries to calculate KPIs by channel

Before building a dashboard, you need to understand which marketing channels perform best. Aggregation queries group your 200,000 rows into meaningful summaries.

  • In SSMS, make sure you are connected to localhost\SQLEXPRESS and have the CampaignAnalytics database selected.
  • Open a new query window by clicking New Query in the toolbar.
  • Calculate average ROI, total impressions, total clicks, and average conversion rate grouped by channel by pasting and running this query:
-- Calculate KPIs grouped by marketing channel
SELECT
    Channel_Used,
    AVG(ROI) AS AverageROI,
    SUM(Impressions) AS TotalImpressions,
    SUM(Clicks) AS TotalClicks,
    AVG(Conversion_Rate) AS AvgConversionRate,
    COUNT(*) AS CampaignCount
FROM CampaignData
GROUP BY Channel_Used
ORDER BY AverageROI DESC;

What does this query do?

GROUP BY Channel_Used collapses all 200,000 rows into one row per channel (Display, Influencer, Search, Email, Social Media). The aggregate functions (AVG, SUM, COUNT) calculate the metrics for each group.

ORDER BY AverageROI DESC puts the highest-performing channel at the top so you can immediately see which channels deliver the best return.

  • Press F5 to execute the query.
  • You should see 5 rows in the Results pane, one for each channel, with their calculated KPIs.

Seeing zero rows or an error?

Make sure CampaignAnalytics is selected in the database dropdown at the top of SSMS. If you see the wrong database name, click the dropdown and switch to CampaignAnalytics before running.

If you see a column name error, double-check that your table uses Channel_Used (with an underscore) as the column name.

Help me debug my SQL aggregation query in SSMS.

Now add a CASE statement to categorize each channel's performance tier. Paste and run this query below your first one:

-- Categorize campaigns by ROI performance tier
SELECT
    Channel_Used,
    AVG(ROI) AS AverageROI,
    COUNT(*) AS CampaignCount,
    CASE
        WHEN AVG(ROI) > 5 THEN 'High ROI'
        WHEN AVG(ROI) BETWEEN 2 AND 5 THEN 'Medium ROI'
        ELSE 'Low ROI'
    END AS PerformanceTier
FROM CampaignData
GROUP BY Channel_Used
ORDER BY AverageROI DESC;

What does the CASE statement do?

CASE works like an if/else inside your query. It evaluates the average ROI for each channel and assigns a label: 'High ROI' for above 5%, 'Medium ROI' for 2-5%, and 'Low ROI' for below 2%.

This is a common pattern in analytics. Instead of forcing stakeholders to interpret raw numbers, you translate them into categories they can act on.

  • Press F5 to execute.
  • You should see the same 5 channel rows, now with a PerformanceTier column showing High ROI, Medium ROI, or Low ROI next to each channel.
Use window functions to rank campaigns and calculate running totals

Window functions let you perform calculations across rows without collapsing them into groups. They are essential for ranking, running totals, and period-over-period comparisons.

  • In your SSMS query window, paste and run this query to rank campaigns by ROI within each channel:
-- Rank campaigns by ROI within each channel
SELECT
    Campaign_ID,
    Channel_Used,
    ROI,
    ROW_NUMBER() OVER (
        PARTITION BY Channel_Used
        ORDER BY ROI DESC
    ) AS ChannelRank
FROM CampaignData
ORDER BY Channel_Used, ChannelRank;

What does ROW_NUMBER() OVER do?

ROW_NUMBER() assigns a sequential number to each row. PARTITION BY Channel_Used restarts the numbering for each channel. ORDER BY ROI DESC means the highest-ROI campaign in each channel gets rank 1.

This is how you answer questions like "What are the top 10 campaigns in each channel?" without writing separate queries for each one.

  • Press F5 to execute.
  • You should see all campaigns listed with a ChannelRank column. The rank resets to 1 at the start of each new channel group.
  • Now paste and run this CTE query to calculate cumulative spend by month:
-- Calculate cumulative spend by month using a CTE
WITH MonthlySpend AS (
    SELECT
        FORMAT(Date, 'yyyy-MM') AS Month,
        SUM(Acquisition_Cost) AS MonthlySpend
    FROM CampaignData
    GROUP BY FORMAT(Date, 'yyyy-MM')
)
SELECT
    Month,
    MonthlySpend,
    SUM(MonthlySpend) OVER (
        ORDER BY Month
    ) AS CumulativeSpend
FROM MonthlySpend
ORDER BY Month;

What is a CTE?

A CTE (Common Table Expression) is a temporary named result set defined with the WITH keyword. It makes complex queries readable by breaking them into logical steps.

Here the CTE first calculates total spend per month. The outer query then uses SUM() OVER(ORDER BY Month) to compute a running total across months, showing how spend accumulates over time.

  • Press F5 to execute.
  • You should see one row per month with a CumulativeSpend column that increases with each row.
  • Finally, paste and run this query using LAG() to compare month-over-month performance:
-- Compare month-over-month ROI using LAG
WITH MonthlyROI AS (
    SELECT
        FORMAT(Date, 'yyyy-MM') AS Month,
        AVG(ROI) AS AvgROI
    FROM CampaignData
    GROUP BY FORMAT(Date, 'yyyy-MM')
)
SELECT
    Month,
    AvgROI,
    LAG(AvgROI) OVER (ORDER BY Month) AS PreviousMonthROI,
    AvgROI - LAG(AvgROI) OVER (ORDER BY Month) AS MonthOverMonthChange
FROM MonthlyROI
ORDER BY Month;

What does LAG() do?

LAG() looks at the previous row's value without needing a self-join. Here it grabs last month's average ROI so you can calculate the change between months in a single query.

The first row will show NULL for PreviousMonthROI because there is no month before it. This is expected behavior.

  • Press F5 to execute.
  • You should see each month's average ROI alongside the previous month's value and the difference between them. The first row shows NULL for the previous month columns.

Getting a conversion error on the Date column?

If the Date column was imported as a text type instead of a date type, the FORMAT function may fail. Run SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CampaignData' AND COLUMN_NAME = 'Date' to check the type.

If it shows varchar or nvarchar, wrap the column in a CAST: FORMAT(CAST(Date AS DATE), 'yyyy-MM').

Help me fix a date conversion error in my SQL window function query.

Create a summary view and export results to CSV

A SQL VIEW saves your query logic so you can reuse it without rewriting the SQL. You will create a view that combines your aggregation and CASE statement work into a single reusable output.

  • Create the vw_ChannelPerformance view by pasting and running this query:
-- Create a reusable view combining channel KPIs and performance tiers
CREATE VIEW vw_ChannelPerformance AS
SELECT
    Channel_Used,
    SUM(Acquisition_Cost) AS TotalSpend,
    COUNT(CASE WHEN Conversion_Rate > 0 THEN 1 END) AS TotalConversions,
    AVG(ROI) AS AverageROI,
    COUNT(*) AS CampaignCount,
    CASE
        WHEN AVG(ROI) > 5 THEN 'High ROI'
        WHEN AVG(ROI) BETWEEN 2 AND 5 THEN 'Medium ROI'
        ELSE 'Low ROI'
    END AS PerformanceTier
FROM CampaignData
GROUP BY Channel_Used;

What does CREATE VIEW do?

A VIEW is a saved query that acts like a virtual table. Once created, you can query vw_ChannelPerformance the same way you query any table, but the data always reflects the latest state of the underlying CampaignData table.

The TotalConversions column uses a conditional COUNT. It only counts rows where Conversion_Rate is greater than 0, giving you the number of campaigns that actually converted.

  • Press F5 to execute. You should see the message "Commands completed successfully" in the Messages pane.
  • Verify the view works by running this query:
-- Verify the view returns data
SELECT * FROM vw_ChannelPerformance;
  • Press F5 to execute.
  • You should see 5 rows showing each channel's TotalSpend, TotalConversions, AverageROI, CampaignCount, and PerformanceTier.

Seeing 'there is already an object named vw_ChannelPerformance'?

This means you already ran the CREATE VIEW statement. If you need to update the view, use ALTER VIEW instead of CREATE VIEW, or drop it first with DROP VIEW vw_ChannelPerformance; and then re-create it.

Help me fix my SQL VIEW creation error.

Now export the view's results to a CSV file that you will use in Excel in the next step.

  • First, ensure column headers are included in exports. Click Tools in the SSMS menu bar.
  • Click Options.
  • Expand Query Results > SQL Server > Results to Grid.
  • Check the box for Include column headers when copying or saving the results.
  • Click OK.
  • Open a new query window (the setting only applies to new windows). Click New Query in the toolbar.
  • Run the view query again by pasting and executing:
SELECT * FROM vw_ChannelPerformance;
  • Press F5 to execute and see results in the grid.
  • Right-click anywhere in the results grid.
  • Click Save Results As....
  • Navigate to C:\CampaignData in the Save dialog.
  • Enter vw_ChannelPerformance_export.csv as the file name.
  • In the Save as type dropdown, select CSV (Comma delimited).
  • Click Save.

Why export to CSV instead of connecting Power BI directly?

You will connect Power BI directly to SQL Server in a later step. This CSV export is specifically for Excel pivot table analysis, which works best with a local file.

Exporting intermediate results is also a common real-world practice. It creates a snapshot you can share with stakeholders who do not have database access.

  • Verify the file exists by opening File Explorer and navigating to C:\CampaignData. You should see vw_ChannelPerformance_export.csv in the folder.

CSV file is missing or has no headers?

If the file is not in C:\CampaignData, check the location you selected in the Save dialog. SSMS saves to the last-used directory by default.

If the CSV has data but no column headers, make sure you checked the Include column headers when copying or saving the results option in a new query window. The setting does not apply to windows that were already open when you changed it.

Help me export my SQL results to CSV with column headers in SSMS.

✔️ Awesome, I've got everything!

Great. Double check you have the vw_ChannelPerformance_export.csv file saved and ready for Excel import in the next step.

ⓧ I'd like to double check the full code

Here is the CREATE VIEW statement that should exist in your CampaignAnalytics database:

CREATE VIEW vw_ChannelPerformance AS
SELECT
    Channel_Used,
    SUM(Acquisition_Cost) AS TotalSpend,
    COUNT(CASE WHEN Conversion_Rate > 0 THEN 1 END) AS TotalConversions,
    AVG(ROI) AS AverageROI,
    COUNT(*) AS CampaignCount,
    CASE
        WHEN AVG(ROI) > 5 THEN 'High ROI'
        WHEN AVG(ROI) BETWEEN 2 AND 5 THEN 'Medium ROI'
        ELSE 'Low ROI'
    END AS PerformanceTier
FROM CampaignData
GROUP BY Channel_Used;

Your SQL analysis is complete. You have aggregation queries, window functions, and a reusable VIEW ready to go. Next up, you will import your exported CSV into Excel and build pivot tables to explore the data visually.

Analyze Campaign Data in Excel with Pivot Tables

Your SQL queries revealed channel-level performance patterns hiding in 200,000 rows of campaign data. Now it's time to explore those results interactively.

In this step, you'll import your exported SQL results into Excel and build pivot tables that let you slice campaign performance by channel, audience, and segment. You'll finish with charts and written insights ready for your Power BI dashboard.

In this step, get ready to:
  • Import your SQL export CSV into Excel with proper data types.
  • Build pivot tables comparing channel and audience performance.
  • Create summary charts and write key insights for stakeholders.
Import the SQL query results CSV into Excel

Excel's text import wizard handles CSV parsing and lets you set column data types before the data lands in your worksheet. This prevents numbers from being treated as text and dates from losing their formatting.

  • Open Excel.
  • Click the **Data** tab in the ribbon at the top.
  • Click From Text/CSV in the Get & Transform Data group.
  • Navigate to your exported file (vw_ChannelPerformance_export.csv in C:\CampaignData) and click Import.
  • In the preview dialog, verify the columns look correct.
  • Click Load to bring the data into your worksheet.

Your data should now appear in a worksheet. Next, apply proper data types and format it as a table.

  • Select the AverageROI column by clicking its column header letter.
  • Right-click and select Format Cells.
  • Choose Percentage with 2 decimal places, then click OK.
  • Select the TotalSpend column and format it as Currency.
  • Click anywhere inside your data range.
  • Press Ctrl+T to format the data as an Excel Table.
  • In the Create Table dialog, confirm My table has headers is checked, then click OK.

Your data should now have banded rows and filter arrows on each column header. This confirms it's formatted as an Excel Table.

Why format as an Excel Table?

Excel Tables automatically expand when you add data and make pivot table creation easier. They also give your data a named range that Power BI can reference later.

Data looks wrong after import?

If numbers appear as text (left-aligned instead of right-aligned), select the column, go to Data > Text to Columns, and click Finish to force Excel to re-detect the data type.

help me fix data types after CSV import into Excel

Build pivot tables to compare channel and audience performance

Pivot tables let you summarize thousands of rows into a compact comparison table with a few clicks. You'll create two pivot tables: one comparing channels and one breaking down audience segments.

  • Click anywhere inside your data table.
  • Click the Insert tab in the ribbon.
  • Click PivotTable.
  • Select New Worksheet and click OK.

A blank pivot table appears on a new sheet with the PivotTable Fields pane on the right. Now configure it to show channel performance.

  • In the PivotTable Fields pane, drag Channel_Used to the Rows area.
  • Drag AverageROI to the Values area.
  • Drag TotalClicks to the Values area.
  • Drag TotalImpressions to the Values area.

You should now see a table with each channel (Display, Email, Influencer, Search, Social Media) as rows, with their Average ROI, Total Clicks, and Total Impressions displayed as columns.

What if the values show as Count instead of Sum or Average?

Excel sometimes defaults to Count for numeric fields. Click the dropdown arrow on the field in the Values area, select Value Field Settings, and change the summarization to Average for ROI or Sum for Clicks and Impressions.

Now rename this sheet and create your second pivot table for audience analysis.

  • Double-click the sheet tab at the bottom and rename it to Channel Pivot.
  • Go back to your data sheet (the one with the imported table).
  • Click inside the data table again.
  • Click Insert > PivotTable > New Worksheet > OK.
  • Drag Target_Audience to the Rows area.
  • Drag Customer_Segment to the Columns area.
  • Drag AverageROI to the Values area.
  • Rename this sheet tab to Audience Pivot.

This second pivot table shows which audience-segment combinations deliver the best ROI. Now add conditional formatting to make top performers stand out at a glance.

  • Select all the ROI values in the pivot table (the number cells, not headers).
  • Click the Home tab.
  • Click Conditional Formatting > Color Scales > Green - Yellow - Red Color Scale.

High ROI values now appear green and low values appear red. Repeat the same conditional formatting on your Channel Pivot sheet's ROI column.

Pivot table fields not showing?

Make sure you're clicking inside the pivot table area. The PivotTable Fields pane only appears when the pivot table is selected. If you accidentally clicked outside, click any cell inside the pivot table to bring it back.

help me troubleshoot my Excel pivot table fields pane

Create a summary sheet with key findings and charts

Charts turn your pivot table numbers into visual stories. A bar chart makes channel comparison instant, while a line chart reveals performance trends over time.

  • Navigate to your Channel Pivot sheet.
  • Select the Channel_Used column and the AverageROI values column (hold Ctrl to select non-adjacent columns).
  • Click the Insert tab.
  • Click Insert Column or Bar Chart and select Clustered Bar.

A bar chart comparing ROI across channels should appear. You can see at a glance which channels drive the highest return.

Next, create a line chart showing how performance changes over time. For this chart, you'll use data from the original data sheet.

  • Go back to your data sheet with the imported table.
  • Create a quick pivot table: click Insert > PivotTable > New Worksheet > OK.
  • Drag Date to Rows (Excel may auto-group by month).
  • Drag AverageROI to Values.
  • Select the pivot table, then click Insert > Insert Line Chart > Line.

You should see a line chart showing how average ROI trends month over month across your campaign data.

Finally, create a summary sheet to document your findings.

  • Right-click any sheet tab at the bottom and select Insert > Worksheet.
  • Rename the new sheet to Summary.
  • In cell A1, type a heading: Campaign Performance Key Findings.
  • Starting in cell A3, write 3-5 bullet-point insights based on your pivot tables.

What insights should I write?

Look at your pivot tables and note patterns like which channels drive the highest ROI, which audience segments respond best, and how performance varies over time. Example insights:

  • "Display and Influencer channels drive 5%+ ROI vs. 4.9% for Email."
  • "The Health & Wellness segment consistently outperforms other customer segments."
  • "ROI peaked in Q3 and dropped in Q4, suggesting seasonal campaign fatigue."

Your exact numbers will depend on your data. Write what you actually observe in your pivot tables.

  • Move your bar chart and line chart to the Summary sheet by right-clicking each chart, selecting Move Chart > Object in > Summary.
  • Press Ctrl+S to save.
  • In the Save As dialog, choose a location, name the file CampaignAnalysis.xlsx, and click Save.

Your Summary sheet should now show your key findings text alongside the bar chart and line chart. This gives you a single page that tells the story of your campaign data.

Charts look empty or show wrong data?

Make sure you selected both the label column (channel names or dates) and the value column before inserting the chart. If the chart is empty, right-click it, select Select Data, and verify the data range points to the correct cells in your pivot table.

help me fix my Excel chart that's not showing data correctly

Your Excel analysis is complete. You now have a workbook with formatted data, two pivot tables, conditional formatting, charts, and written insights. Next up, you'll bring this data into Power BI to build an interactive dashboard that stakeholders can explore on their own.

Build a Power BI Dashboard

Your Excel analysis revealed which channels and audiences drive the best ROI. Now it is time to bring those insights to life in an interactive dashboard that stakeholders can explore on their own.

In this step, you will connect Power BI Desktop directly to your SQL Server database, write DAX measures to calculate KPIs, and assemble a multi-visual dashboard page with slicers and charts.

In this step, get ready to:
  • Connect Power BI Desktop to your SQL Server database and configure column types in Power Query.
  • Create DAX measures and a calculated column for campaign KPIs.
  • Design an interactive dashboard page with KPI cards, charts, and slicers.
Connect Power BI to your SQL Server database

Power BI can pull data directly from SQL Server, which means your dashboard updates automatically when the underlying data changes. This is more professional than importing a static CSV.

  • Open Power BI Desktop.
  • In the Home ribbon, click Get Data.
  • Select SQL Server from the list and click Connect.
  • In the Server field, enter localhost\SQLEXPRESS.
  • In the Database field, enter CampaignAnalytics.
  • Click OK.
  • In the Navigator window that appears, check both CampaignData and vw_ChannelPerformance.
  • Click Transform Data (not Load) to open Power Query Editor.

Why Transform Data instead of Load?

Clicking Transform Data opens the Power Query Editor where you can verify and correct column types before the data enters your model. Incorrect types (like dates stored as text) break time-based calculations and hierarchies.

Now set the correct data types for key columns in the Power Query Editor.

  • In the left Queries pane, select CampaignData.
  • Click the Date column header to select it.
  • In the Transform tab, click the Data Type dropdown and select Date.
  • If a Change Column Type dialog appears, select Replace current.
  • Select the Acquisition_Cost column. Change its type to Fixed decimal number (currency).
  • Select the Conversion_Rate column. Change its type to Decimal Number.
  • Select the ROI column. Change its type to Decimal Number.
  • Click Close & Apply in the Home tab to load the data into your model.

After loading completes, you should see both CampaignData and vw_ChannelPerformance listed in the Data pane on the right side of the screen.

Don't see your tables in the Data pane?

  • Make sure SQL Server Express is running. Open SSMS and confirm you can connect to localhost\SQLEXPRESS.
  • Verify you entered the server name exactly as localhost\SQLEXPRESS with a backslash, not a forward slash.
  • Check that you selected both tables in the Navigator before clicking Transform Data.

Why can't Power BI find my SQL Server database?

Create DAX measures for your KPIs

DAX measures are formulas that calculate values dynamically based on the filters and slicers active in your report. Unlike calculated columns, measures recalculate on the fly as viewers interact with your dashboard.

  • In the Data pane, right-click the CampaignData table.
  • Select New measure.
  • In the formula bar at the top, type the following DAX formula and press Enter to commit:
Total Spend = SUM(CampaignData[Acquisition_Cost])
  • Right-click CampaignData again and select New measure. Enter this formula:
Average ROI = AVERAGE(CampaignData[ROI])
  • Create a third measure:
Total Conversions = COUNTROWS(FILTER(CampaignData, CampaignData[Conversion_Rate] > 0))
  • Create a fourth measure:
CTR = DIVIDE(SUM(CampaignData[Clicks]), SUM(CampaignData[Impressions]))

What do these measures calculate?

  • Total Spend sums all acquisition costs across whatever filters are active.
  • Average ROI calculates the mean return on investment for filtered campaigns.
  • Total Conversions counts only rows where the conversion rate is above zero.
  • CTR (Click-Through Rate) divides total clicks by total impressions. DIVIDE handles division-by-zero gracefully by returning blank instead of an error.

You should see all four measures appear under the CampaignData table in the Data pane, each with a small calculator icon.

Next, add a calculated column to categorize campaigns by performance tier (the same logic you used in your SQL CASE statement).

  • Right-click the CampaignData table in the Data pane.
  • Select New column.
  • In the formula bar, enter this DAX formula and press Enter:
PerformanceTier = 
IF(
    CampaignData[ROI] > 5,
    "High ROI",
    IF(
        CampaignData[ROI] >= 2,
        "Medium ROI",
        "Low ROI"
    )
)

What does this calculated column do?

Unlike a measure (which recalculates dynamically), a calculated column evaluates once per row when the data loads. It adds a permanent PerformanceTier value to each campaign row.

Campaigns with ROI above 5% are tagged "High ROI". Those between 2% and 5% get "Medium ROI". Everything below 2% is "Low ROI".

Confirm the new PerformanceTier column appears under CampaignData in the Data pane (without a calculator icon, since it is a column, not a measure).

Seeing a formula error?

  • Check that every parenthesis has a matching pair. DAX syntax errors are most often caused by missing closing parentheses.
  • Make sure column names match exactly. CampaignData[ROI] is case-sensitive to the table name.
  • Press Enter to commit the formula. If you need multiple lines, use Shift+Enter for line breaks within the formula bar.

Why is my DAX formula showing an error in Power BI?

Design a multi-visual dashboard page

With your data model and measures ready, you can now assemble the visuals that tell the campaign performance story. Each visual serves a distinct purpose for stakeholders.

  • Switch to Report view by clicking the chart icon on the left sidebar.

Start by adding KPI cards across the top of the page.

  • In the Visualizations pane, click the Card visual (single number icon).
  • Drag the visual to the top-left of the canvas and resize it to about one-quarter width.
  • From the Data pane, drag Campaign_ID into the card's Fields well. It will show a Count by default, representing Total Campaigns.
  • Add three more Card visuals next to the first. Drag Average ROI (your measure) into the second card.
  • Drag Impressions into the third card (it sums automatically for Total Impressions).
  • Drag CTR (your measure) into the fourth card for Average CTR.

Next, add a clustered bar chart showing ROI by channel.

  • Click an empty area of the canvas.
  • In the Visualizations pane, select Clustered bar chart.
  • Drag Channel_Used to the Y-axis well.
  • Drag Average ROI (your measure) to the X-axis well.
  • Position the chart below the KPI cards on the left side.

Add a line chart for the monthly performance trend.

  • Click an empty area of the canvas.
  • Select the Line chart visual from the Visualizations pane.
  • Drag Date to the X-axis well.
  • Drag Average ROI to the Y-axis well.
  • Position this chart to the right of the bar chart.

Add a donut chart to show campaign distribution by audience.

  • Click an empty area of the canvas.
  • Select the Donut chart visual.
  • Drag Target_Audience to the Legend well.
  • Drag Campaign_ID to the Values well (Count).
  • Position the donut chart in the remaining space on the page.

Finally, add slicers so viewers can filter the entire page interactively.

  • Click an empty area of the canvas.
  • Select the Slicer visual from the Visualizations pane.
  • Drag Channel_Used into the slicer's Field well.
  • Add a second Slicer visual. Drag Date into it (it becomes a date range slicer automatically).
  • Add a third Slicer visual. Drag Location into it.
  • Arrange the three slicers along the right edge or top of your page.

Apply a consistent color theme and title to finish the page.

  • Click the View tab in the ribbon.
  • Click Themes and select a professional theme that suits your style.
  • Click an empty area of the canvas, then in the Visualizations pane, select Text box.
  • Type Marketing Campaign Performance Dashboard as the title.
  • Position it at the very top of the page above the KPI cards.
  • Save your file by pressing Ctrl+S. Name it CampaignDashboard.pbix.

Your dashboard page should now show four KPI cards across the top, a clustered bar chart, a line chart, a donut chart, and three slicers. Clicking any slicer value filters all visuals simultaneously.

Why do slicers update every visual?

Power BI uses cross-filtering by default. When a viewer selects "Display" in the Channel slicer, every visual on the page recalculates to show only Display channel data. This interactivity is what makes dashboards more powerful than static reports.

Visuals showing blank or unexpected values?

  • Check that your measures reference the correct table. CampaignData[Acquisition_Cost] must match the exact column name in your model.
  • If the date slicer is not showing a range slider, confirm that the Date column type was set to Date in Power Query (not Text).
  • Try clicking Refresh in the Home ribbon to reload data from SQL Server.

Why are my Power BI visuals not showing any data?

Your interactive dashboard is ready. Next up, you will polish it with a second page, add tooltips, and publish it to the web so you can share a live link on LinkedIn.

Publish and Share Your Dashboard

Your Power BI dashboard is packed with insights from 200,000 rows of marketing data. Now it's time to get it in front of other people.

In this step, you'll add a second "Deep Dive" page, polish the visuals for a professional audience, and publish the report to the web so you can share it on LinkedIn.

In this step, get ready to:
  • Add a "Deep Dive" page with a matrix visual and tooltips.
  • Publish your report to Power BI Service.
  • Generate a shareable link and take screenshots for LinkedIn.
Polish your dashboard for LinkedIn

A single-page dashboard tells a good story, but hiring managers want to see that you can organize data at multiple levels of detail. Adding a dedicated "Deep Dive" page shows you know how to layer high-level summaries with granular breakdowns.

  • In Power BI Desktop, right-click the page tab at the bottom of the canvas.
  • Select New page.
  • Double-click the new page tab and rename it to Deep Dive.

Now add a matrix visual that cross-references channels with audience segments.

  • In the Visualizations pane on the right, click the Matrix icon (a grid of rows and columns).
  • Drag Channel_Used into the Rows well.
  • Drag Customer_Segment into the Columns well.
  • Drag your Average ROI measure into the Values well.
  • Also drag Total Spend and Total Conversions into Values to round out the view.

What is a matrix visual?

A matrix visual is the Power BI equivalent of a pivot table. It shows values at the intersection of two categorical dimensions. In this case, each cell shows a KPI (like Average ROI) for a specific channel and customer segment combination.

Next, add tooltips to your Page 1 charts so viewers see richer context when they hover over data points.

  • Switch back to Page 1 by clicking the Marketing Campaign Performance Dashboard tab at the bottom.
  • Click your clustered bar chart (ROI by Channel) to select it.
  • In the Build visual pane, find the Tooltips field well.
  • Drag Total Spend and Total Conversions into the Tooltips well.
  • Repeat this for the line chart (monthly trend). Click it, then drag CTR into its Tooltips well.

Tooltips add depth without clutter

Dragging fields into the Tooltips well means they appear only when a viewer hovers over a data point. This keeps your chart clean while still providing extra detail on demand.

Finally, make sure all visuals across both pages have clear titles and axis labels.

  • Click each visual one at a time.
  • In the Format visual pane (paint roller icon), expand General > Title.
  • Confirm the title toggle is On and that the title text is descriptive (e.g., Average ROI by Channel, Monthly Performance Trend).
  • For charts with axes, expand the X-axis and Y-axis sections under Visual and confirm Title is toggled on for each.
  • Verify your color theme is consistent across both pages. If needed, adjust individual bar/line colors in the Colors section of the Format pane.
  • Save your report by pressing Ctrl+S.

Matrix visual looks empty?

Make sure you are on the Deep Dive page tab (not Page 1) when building the matrix. Also check that you dragged fields from the CampaignData table in the Data pane, not from vw_ChannelPerformance.

Help me troubleshoot my matrix visual in Power BI

Publish to Power BI Service

Power BI Desktop is great for building reports, but to share them you need to push the file to Power BI Service. This is Microsoft's cloud platform that hosts your reports and makes them viewable in a browser.

  • In Power BI Desktop, click the Home tab in the ribbon at the top.
  • Click the Publish button on the far right of the ribbon.
  • If prompted, sign in with your Microsoft account.
  • Select My Workspace as the destination.
  • Click Select.

Power BI will upload your report. When it finishes, you'll see a success message with a link to open the report in your browser.

  • Click the link in the success dialog to open your report at app.powerbi.com.
  • Verify both pages (Marketing Campaign Performance Dashboard and Deep Dive) appear in the browser and load correctly.

Why My Workspace?

My Workspace is your personal area in Power BI Service. It's included free with any Microsoft account. Shared workspaces and collaborative features require Power BI Pro ($14/user/month), but for personal portfolio sharing, the free tier is all you need.

Publish button is greyed out?

Make sure you have saved your report first (Ctrl+S). The Publish button requires a saved .pbix file. If you're still having trouble, check that you are signed into a Microsoft account under File > Account.

Help me fix my Power BI Publish issue

Generate a shareable link for LinkedIn

Now that your report lives in Power BI Service, you can generate an embed link that lets anyone view it in their browser. You can also take screenshots for a LinkedIn carousel post.

  • In your browser at app.powerbi.com, open your published report.
  • Click File in the top menu bar.
  • Select Embed report > Website or portal.
  • Copy the embed link from the dialog that appears.

What if I want a fully public link?

The Embed report > Website or portal option generates a secure link that requires viewers to sign in with a Microsoft account. For a completely public link (no sign-in), use File > Publish to web instead. Only use this for non-sensitive data since it makes your report visible to anyone.

For a LinkedIn carousel post, take high-quality screenshots of both dashboard pages.

  • Navigate to Page 1 of your report in the browser.
  • Press Windows+Shift+S to open the Snipping Tool and capture the full dashboard.
  • Save the screenshot somewhere easy to find.
  • Switch to the Deep Dive page and take a second screenshot.

Make your LinkedIn post stand out

Pair your screenshots with a short write-up of your findings. Mention the dataset size (200,000+ rows), the tools you used (SQL Server, Excel, Power BI), and 2-3 specific insights you discovered. Hiring managers love seeing both the technical skills and the ability to communicate results.

Your dashboard is live and shareable. Next up, you'll tackle the Secret Mission and add a "What-If" budget simulator that lets viewers play with different spending scenarios.

Secret mission

Add a "What-If" Budget Reallocation Simulator

Your dashboard shows what happened. But what if you could show stakeholders what could happen? Build an interactive budget simulator that lets viewers drag a slider and watch projected ROI change in real time.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine with free tools, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • CampaignAnalytics SQL Server database (local).
  • CSV files in C:\CampaignData.
  • Excel workbook (CampaignAnalysis.xlsx).
  • Power BI Desktop file (.pbix).
  • Published Power BI report on Power BI Service.
  • Kaggle API token (kaggle.json).
  • SQL Server 2022 Express and SSMS (installed software).
  • Python and Kaggle CLI (installed software).
  • Power BI Desktop (installed software).

Keep everything running

No action needed. Choose this if you plan to continue exploring the data, refining your dashboard, or using these tools for future projects.

  • SQL Server Express continues running as a background service. It uses minimal resources when idle.
  • Your Power BI report stays published in your My Workspace on Power BI Service at no cost.
  • All local files remain on your machine.

Pause - I'll come back to this later

Shut down running processes to free up memory while keeping all your files and installed software in place.

  • Stop the SQL Server service to free up memory. Open Command Prompt as Administrator and run:
  • Close SSMS, Power BI Desktop, and Excel.
net stop "SQL Server (SQLEXPRESS)"

Restarting later

When you are ready to resume, open Command Prompt as Administrator and run:

net start "SQL Server (SQLEXPRESS)"

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow as many of these steps as you like.

Delete the published Power BI report:

  • Go to app.powerbi.com and open My Workspace.
  • Hover over your report name, click the three dots ... menu, and select Delete.
  • Confirm the deletion when prompted.

Drop the SQL Server database:

  • Open SSMS and connect to localhost\SQLEXPRESS.
  • Right-click the CampaignAnalytics database, select Delete, check Close existing connections, and click OK.

Delete local project files:

  • Open Command Prompt and run these commands to remove the data folder and Kaggle API token:
rmdir /s /q C:\CampaignData
del C:\Users\%USERNAME%\.kaggle\kaggle.json
  • Delete your CampaignAnalysis.xlsx and .pbix files from wherever you saved them.

Uninstall software:

  • Press the Windows key and search for Add or remove programs.
  • Find and uninstall Microsoft SQL Server 2022 (Express edition).
  • Find and uninstall SQL Server Management Studio.
  • Find and uninstall Python (this also removes pip and the Kaggle CLI).
  • Find and uninstall Microsoft Power BI Desktop (if installed via the download). If you installed from the Microsoft Store, right-click the app in the Start menu and select Uninstall.

Nice Work!

Nice Work!

You just built a complete end-to-end data analytics pipeline. From raw campaign data to a polished, interactive dashboard ready to share on LinkedIn.

You've learned how to:

  • Load 200,000+ rows of real marketing data into SQL Server and query it using CTEs, window functions, CASE statements, and VIEWs.
  • Build Excel pivot tables with conditional formatting and summary charts to explore campaign performance across channels.
  • Design and publish a multi-page Power BI dashboard with DAX measures, interactive slicers, and a shareable web link.
  • Add a What-If budget simulator using Power BI parameters that lets viewers simulate budget reallocation and see projected ROI changes in real time.

Ready to quiz yourself?