Build a Campaign Dashboard

Build a Google Sheets dashboard to compare campaigns and recommend budget tests.

Introduction

30 Second Summary

Big click numbers can make an advertising campaign look successful at first glance. Hiring managers want to see whether you can test that impression against the results that matter to a business.

In this project, you will build a campaign performance dashboard in Google Sheets for a fictional retailer. Your analysis will expose a misleading click leader before turning campaign outcomes into a defensible budget recommendation.

What You'll Build

Your finished spreadsheet tells a clear interview story: the click leader looks strongest first, then the outcome metrics reveal where the next budget test belongs.

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

A synthetic campaign comparison that lets you show why Awareness Social's 3,600 clicks create an incomplete first impression.
A KPI comparison with calculated CTR, conversion rate, cost per conversion, and ROAS. Side-by-side charts reveal the strongest campaign outcomes.
A portfolio case study that turns the evidence into a controlled budget recommendation. An explicit analytical caveat keeps the conclusion responsible. A view-only link makes the finished case study easy to share with recruiters.
Secret Mission: Add a hypothetical 300% ROAS goal that automatically flags the campaign below the target. Update your recommendation around the result.

Are there any prerequisites?

You need a Mac with internet access. No coding or prior marketing analytics experience is required.

Google Account setup is included if needed.

Before We Start

A clear outcome keeps every metric and chart focused on a real marketing decision. This checkpoint defines what your dashboard proves to a hiring manager before the hands-on work begins.

Set Up Your Campaign Workbook

A recruiter-ready dashboard needs a clear home before the campaign data arrives. A professional workbook name makes your analysis easy to recognize when you share it later.

You'll create that home in Google Sheets through Safari. A personal Google Account keeps the workbook available in Google Drive at no project cost.

In this step, get ready to:

Create a blank Google Sheets workbook in your personal account.
Name the workbook Campaign Performance Dashboard.
Rename the visible sheet tab Campaign Data.

Create the blank workbook

Google Sheets creates the project file once your personal account is signed in. The creation link takes you directly to a blank spreadsheet.

Press Cmd+Space to open your Mac's search bar.
Type Safari.
Press Return to open Safari.
Enter https://sheets.google.com/create in Safari's address bar.
Press Return to load the spreadsheet creation page.

✔️ A blank spreadsheet opens

You're in. A blank spreadsheet is open in Safari under your personal Google Account.

ⓧ Google asks me to sign in

Google needs to connect the new spreadsheet to your personal account before it creates the file.

Complete the onscreen sign-in prompts with your personal Google Account.
Complete any security check Google presents.
Return to https://sheets.google.com/create after signing in.

You'll see a blank spreadsheet open in Safari.

ⓧ I need a Google Account

A personal Google Account gives you access to the Google Sheets features used in this project.

Select Create account on the Google Account sign-in page.
Select Personal use.
Complete the onscreen prompts to create your account.
Return to https://sheets.google.com/create after account creation.

You'll see a blank spreadsheet open under your new personal Google Account.

The Blank Spreadsheet Did Not Open?

Check that Safari has an active internet connection.
Confirm that you completed the Google Account sign-in flow.
Reload the spreadsheet creation link after signing in.

Ask for targeted help with opening a new Google Sheets workbook.

Name the workbook

The workbook title identifies the whole case study in Google Drive. A specific title helps a recruiter understand the file before opening it.

Click the file name at the top of the spreadsheet.
Enter Campaign Performance Dashboard.
Press Return to apply the title.

Good start. Your blank analysis now has a professional title that matches the project you are building.

Rename the sheet tab

The visible sheet tab identifies the working area inside the workbook. Its name tells viewers where the campaign analysis begins.

Double-click the visible sheet tab at the bottom of the spreadsheet.
Enter Campaign Data.
Press Return to apply the tab name.

Before you check, which file title and sheet tab name do you expect to see?

Look at the file title at the top of the workbook.
Look at the active sheet tab at the bottom of the workbook.

At the top, you'll see Campaign Performance Dashboard. At the bottom, you'll see Campaign Data on the active visible sheet tab.

The sheet itself should still be blank. That's your clean foundation ready for campaign data.

Your campaign workbook is ready. Next, you'll turn the blank sheet into the first visible campaign comparison.

Build the Misleading Click Leaderboard

Your Campaign Performance Dashboard now has a named Campaign Data sheet ready for evidence. The next budget question is which campaign appears to be winning.

Clicks offer a quick signal of campaign attention. A leaderboard turns that signal into an immediate comparison.

In this step, you will add synthetic campaign data. You will chart its click counts in Google Sheets.

In this step, get ready to:

Add a clearly labeled fictional campaign dataset.
Create a column chart from the campaign click counts.
Test the first winner presented by the leaderboard.

Add the synthetic campaign data

Synthetic data uses invented values for practice. It keeps the exercise honest because no real client performance is being claimed.

Select cell A1 in the Campaign Data sheet from earlier.
Paste the following fictional dataset into A1:

Campaign	Clicks	Impressions	Spend (USD)	Conversions	Conversion Value (USD)
Awareness Social	3600	120000	1800	72	3600
Search Intent	2000	40000	1600	160	7200
Retargeting	1250	25000	1250	125	5000

What Does This Dataset Contain?

The Campaign column identifies each advertising campaign.
The Clicks column records how many visits each campaign attracted.
The Impressions column records how many times each campaign was shown.
The Spend (USD) column records the campaign cost.
The Conversions column records completed customer actions.
The Conversion Value (USD) column records the value attributed to those actions.

Confirm the pasted range by reading the headers across row 1.
Confirm the final record by reading row 4.

You should see six headers from Campaign through Conversion Value (USD).

Row 4 should begin with Retargeting and end with 5000.

Data Landed in One Column?

Clear the affected cells.
Select A1.
Paste the original tab-separated block again.

Ask for help with separating the pasted campaign data into columns.

Create the click leaderboard

A column chart compares categories with vertical bars. Each bar will represent one campaign's click count.

Select the range A1:B4.
Click Insert in the top menu.
Select Chart.

You should see a chart built from the campaign names and click counts.

Double-click the chart to open its settings.
Select Setup.

You should see controls for the chart's source data and visual type.

Open Chart type.
Choose a column chart.

Before you compare the bars, which campaign do you expect the click leaderboard to favor?

Compare the heights of the three bars.

Your first campaign visual is working. Awareness Social has the tallest bar at 3,600 clicks.

This is the intended shortfall. The chart answers which campaign received the most clicks.

A click-only result cannot support a budget recommendation by itself.

Chart Shows the Wrong Data?

Remove the incorrect chart.
Select only A1:B4.
Repeat the chart creation steps above.

Ask for help with fixing the campaign chart range.

Title and verify the leaderboard

A chart title tells the reader what the bars measure. A specific title keeps the first comparison focused on clicks.

Double-click the chart to reopen its settings.
Select Customize.

You should see the customization controls beside the sheet.

Select Chart & axis title.
Enter Clicks by Campaign under Title text.

Before your final check, what should the title and tallest bar communicate at a glance?

Read the chart title.
Compare the three bar heights.

The chart title should read Clicks by Campaign. Awareness Social should have the tallest bar at 3,600 clicks.

Chart Title Is Missing?

Reopen Customize.
Return to Chart & axis title.
Re-enter Clicks by Campaign under Title text.

Ask for help with restoring the chart title.

Good work. Your chart now makes the initial click winner impossible to miss.

Next, you will test that impression with efficiency and value metrics.

Calculate Decision Metrics

Your Google Sheets chart has exposed a tempting first conclusion. Awareness Social looks strongest because its bar reaches 3,600 clicks.

A click leader can waste budget when traffic fails to convert into value. You will calculate four outcome metrics to test whether that first impression deserves the next budget test.

The new columns cover CTR, conversion rate, cost per conversion, plus ROAS. Together, they replace surface-level popularity with business evidence.

In this step, get ready to:

Add four decision metric columns to the campaign table.
Calculate the metrics for all three campaigns.
Format the results as percentages and USD values.

Create the metric columns

The extra columns give each formula a clear destination. Their labels also tell a recruiter which business questions your analysis answers.

Select cell G1 in the Campaign Data sheet from earlier.
Label cells G1:J1 by pasting this tab-separated row:

CTR	Conversion Rate	Cost per Conversion	ROAS

What Do These Metrics Measure?

CTR compares clicks with impressions.
Conversion rate compares conversions with clicks.
Cost per conversion divides spend by conversions.
ROAS divides conversion value by spend.

Confirm that cells G1:J1 display the four metric labels.

Headers Landed in One Cell?

Confirm that you selected only cell G1 before pasting. Copy the tab-separated row again if the labels do not fill four columns.

Ask for help with splitting the pasted headers across G1:J1.

Calculate all campaign metrics

Each formula uses values from one campaign row. Pasting three formula rows calculates the same four metrics for every campaign.

Before the formulas go in, predict whether the click leader will remain strongest once value enters the comparison.

Select cell G2.
Calculate all four metrics for all three campaigns by pasting this formula block:

=B2/C2	=E2/B2	=D2/E2	=F2/D2
=B3/C3	=E3/B3	=D3/E3	=F3/D3
=B4/C4	=E4/B4	=D4/E4	=F4/D4

What Do These Formulas Do?

The =B2/C2 pattern divides clicks by impressions to calculate CTR.
The =E2/B2 pattern divides conversions by clicks to calculate conversion rate.
The =D2/E2 pattern divides spend by conversions to calculate cost per conversion.
The =F2/D2 pattern divides conversion value by spend to calculate ROAS.

You should see calculated numbers fill G2:J4. Awareness Social now has a lower ROAS result than both other campaigns.

Formulas Did Not Fill the Table?

Confirm that you started in cell G2. Check that each formula begins with an equal sign.

Ask for help with diagnosing the campaign formulas.

✔️ Awesome, I've got everything!

Your four formulas now cover every campaign row. Continue to the formatting section.

ⓧ I'd like to double check the full code

Compare cells A1:J4 with this complete table. The formulas should remain visible when you select their cells.

Campaign	Clicks	Impressions	Spend (USD)	Conversions	Conversion Value (USD)	CTR	Conversion Rate	Cost per Conversion	ROAS
Awareness Social	3600	120000	1800	72	3600	=B2/C2	=E2/B2	=D2/E2	=F2/D2
Search Intent	2000	40000	1600	160	7200	=B3/C3	=E3/B3	=D3/E3	=F3/D3
Retargeting	1250	25000	1250	125	5000	=B4/C4	=E4/B4	=D4/E4	=F4/D4

Format and verify the results

Raw ratios are difficult to scan quickly. Percentage formatting turns them into stakeholder-ready values.

Select cells G2:H4.
Click Format in the menu bar.
Choose Number.
Choose the percentage format.
Select cells J2:J4.
Click Format in the menu bar.
Choose Number.
Choose the percentage format.

You should now see percentage signs across the CTR, Conversion Rate, and ROAS columns. Search Intent should show the highest ROAS at 450%.

Currency formatting gives spend, conversion value, and cost per conversion one consistent USD display. Selecting the three ranges together applies that format in one pass.

Select cells D2:D4.
Hold Cmd while selecting cells F2:F4.
Keep holding Cmd while selecting cells I2:I4.
Click Format in the menu bar.
Choose Number.
Choose Custom currency.
Choose USD as the currency.
Confirm the USD currency choice.

Before you compare the finished rows, make a prediction about whether the click leader remains the best budget candidate.

Read the formatted results in rows 2 through 4.

Awareness Social shows 3% CTR, 2% conversion rate, $25.00 cost per conversion, plus 200% ROAS.

Search Intent shows 5% CTR, 8% conversion rate, $10.00 cost per conversion, plus 450% ROAS.

Retargeting shows 5% CTR, 10% conversion rate, $10.00 cost per conversion, plus 400% ROAS.

That is the misleading click story overturned. Awareness Social attracts the most clicks while producing the weakest conversion rate, highest cost per conversion, and lowest ROAS.

Seeing Decimals or the Wrong Currency?

Reselect G2:H4 plus J2:J4 if ratios still appear as decimals. Apply the percentage format to those ranges.

Reselect D2:D4, F2:F4, plus I2:I4 if another currency symbol appears. Choose USD through Custom currency.

Ask for help with fixing the percentage or USD formatting.

Your table now separates attention from business performance. Next, you will turn that evidence into a visual decision dashboard.

Build the Decision Dashboard

Your calculated metrics expose the weakness in the click-only story. Search Intent leads on ROAS.

Awareness Social still leads on clicks. A stakeholder needs this contrast in a visual decision dashboard.

In this step, get ready to:

Create a helper table that links each campaign to its ROAS.
Compare campaign ROAS in a second column chart.
Summarize the strongest outcomes with three KPI callouts.

Create the ROAS helper table

The helper table gives the new chart one compact source range. Its formulas stay linked to the metrics already calculated in column J.

In the Campaign Data sheet from earlier, click cell L1.
Create the chart source range by pasting this table:

Campaign	ROAS
Awareness Social	=J2
Search Intent	=J3
Retargeting	=J4

What Does This Table Do?

The first column supplies the campaign labels used by the chart.
Each formula pulls an existing ROAS result from column J.
The links keep the chart connected to the calculated campaign results.

Check the helper table in cells L1:M4.

Awareness Social should show 200%. Search Intent should show 450%.

Retargeting should show 400%.

Helper Table Looks Wrong?

Confirm that the paste began in cell L1.
Paste the full block again if the data did not spread across cells L1:M4.
Check that each formula begins with an equals sign.

Ask for help with diagnosing the ROAS helper table.

Create the ROAS comparison chart

A second column chart turns the calculated ROAS values into a data visualization. Matching chart shapes make the campaign comparison easier to scan.

Select cells L1:M4.
Click Insert in the top menu.
Click Chart.

You should see a new chart based on the helper table.

Double-click the new chart to open its settings.
Select Setup.
Open Chart type.
Choose the column chart option.

You should see one vertical bar for each campaign.

Select Customize.
Select Chart & axis title.
Enter ROAS by Campaign under Title text.
Drag the new chart beside the existing Clicks by Campaign chart.

Your dashboard should now show the two titled column charts side by side.

Why Use Matching Column Charts?

Both charts compare the same three campaigns. Their matching shapes make the change in campaign ranking visible at a glance.

The different measures reveal why a click leader can still deliver the weakest business return.

ROAS Chart Does Not Look Right?

Confirm that the chart uses cells L1:M4.
Check that the selected chart type uses vertical columns.
Confirm that the chart title reads ROAS by Campaign.

Ask for help with fixing the ROAS comparison chart.

Add the KPI callouts

The charts expose the contrast between attention and return. KPI callouts capture the exact results that support a budget decision.

Click the first empty cell beneath the two charts.
Add the three dashboard callouts by pasting this block:

Best ROAS	Search Intent: 450%
Best Conversion Rate	Retargeting: 10%
Lowest Cost per Conversion	Search Intent and Retargeting: $10.00

What Do These Callouts Show?

Best ROAS identifies the campaign that returns the most conversion value per dollar of spend.
Best Conversion Rate identifies the campaign that turns the greatest share of clicks into conversions.
Lowest Cost per Conversion highlights the efficiency tie between two campaigns.

Check the three KPI labels in the left column.
Check their matching values in the right column.

Before you compare the charts, which campaign do you expect to lead each one?

Inspect the tallest bar in Clicks by Campaign.
Inspect the tallest bar in ROAS by Campaign.
Read the three KPI callouts beneath the charts.

You'll see Awareness Social lead clicks at 3,600. You'll see Search Intent lead ROAS at 450%.

The KPI callouts identify Retargeting at 10% conversion rate. They also show Search Intent and Retargeting tied at $10.00 per conversion.

Great work. Your dashboard now turns a misleading click leader into a comparison that supports a clear decision.

KPI Callouts Are Misaligned?

Confirm that you selected one empty cell before pasting the block.
Paste the block again if every value appeared in one cell.
Check that each KPI label remains paired with the value beside it.

Ask for help with repairing the KPI callout layout.

Your visual comparison now makes the stronger business performer clear. Next, you'll turn that evidence into a concise portfolio case study.

Write and Share Your Portfolio Case Study

Your Google Sheets dashboard now exposes the gap between clicks and business outcomes. A recruiter still needs to see how you turn that evidence into a responsible decision.

A concise case-study panel adds a controlled budget recommendation. It also states the limit of ROAS without margin data.

In this step, get ready to:

Add a case-study panel that connects your metrics to a budget decision.
Format the panel as a readable portfolio artifact.
Share the workbook through a view-only link.

Add the case-study panel

A portfolio case study connects analysis to a decision. This panel records your objective before presenting the finding that supports your recommendation.

In the Campaign Data sheet from earlier, select cell A7.
Paste the case-study panel below into cell A7:

PORTFOLIO CASE STUDY	Synthetic Campaign Performance Analysis
Objective	Choose which campaign deserves the next controlled budget test.
Key finding	Awareness Social leads in clicks but has the weakest conversion rate, highest cost per conversion, and lowest ROAS.
Recommendation	Prioritize Search Intent for the next budget test, protect Retargeting efficiency, and redesign Awareness Social before increasing spend.
Caveat	This is synthetic data. ROAS compares conversion value with spend, not profit, so margin data is still needed.
Skills shown	Campaign measurement, spreadsheet analysis, data visualization, and stakeholder communication.

What Does This Panel Communicate?

The objective frames the next decision as a controlled test.
The key finding explains why clicks alone mislead the budget choice.
The recommendation gives Search Intent priority for the next test.
The caveat separates ROAS from profit because margin data is missing.

Inspect the pasted block from A7 downward.
Confirm that B7 identifies the analysis as synthetic.
Confirm that B10 recommends a controlled budget test for Search Intent.
Confirm that B11 says margin data is still needed.

Did the Panel Paste Incorrectly?

Check that you selected cell A7 before pasting.
Undo the paste if every value landed in one cell.
Paste the tab-separated block into A7 again.

Ask for help with fixing the pasted case-study panel.

Format the panel for recruiters

Visual hierarchy helps a recruiter scan the panel quickly. Clear labels also separate your evidence from your final recommendation.

Select cells A7:B13.
Use the formatting controls in the toolbar to give the panel a consistent readable style.
Select cells A7:B7.
Use the text-formatting controls in the toolbar to make the title row visually distinct.
Select cells A8:A12.
Use the same controls to make the field labels visually distinct.
Drag the right edge of the B column header until every sentence is visible.

You should see a clearly separated title row above five labeled case-study fields. Every sentence in column B should be visible.

Your analysis now reads as a stakeholder decision backed by metrics.

The comparison below gives you a final reference for every label and sentence in the panel.

✔️ Awesome, I've got everything!

Your panel identifies the synthetic dataset. It also includes the objective, finding, recommendation, caveat, and skills shown.

ⓧ I'd like to double check the full code

PORTFOLIO CASE STUDY	Synthetic Campaign Performance Analysis
Objective	Choose which campaign deserves the next controlled budget test.
Key finding	Awareness Social leads in clicks but has the weakest conversion rate, highest cost per conversion, and lowest ROAS.
Recommendation	Prioritize Search Intent for the next budget test, protect Retargeting efficiency, and redesign Awareness Social before increasing spend.
Caveat	This is synthetic data. ROAS compares conversion value with spend, not profit, so margin data is still needed.
Skills shown	Campaign measurement, spreadsheet analysis, data visualization, and stakeholder communication.

What Should Match?

This reference preserves the exact labels and statements for the finished panel. Each tab separates a field label from its corresponding case-study text.

Is the Text Still Cut Off?

Drag the right edge of the B column header farther to the right.
Use the toolbar text-wrapping controls if widening the column makes the panel difficult to scan.

Ask for help with making the case-study panel readable.

Share a view-only link

The workbook from earlier remains stored in Google Drive. A view-only link gives recruiters access to the case study.

The Viewer role protects your calculations and recommendation from edits.

Check Your Sharing Identity

Sharing by link makes your name and email visible as the file owner. This visibility can feel personal.

A professional account identity keeps the portfolio presentation intentional.

Confirm that your signed-in Google Account uses a professional name and email before continuing.

Click Share in the top-right corner.
Under General access, choose Anyone with the link.
Select Viewer as the role.
Click Copy link.
Click Done.

Before you reopen the sharing dialog, which access level should let recruiters view the sheet without editing it?

Reopen the sharing dialog by clicking Share.

You should see General access set to Anyone with the link. You should also see Viewer as the role.

Close the sharing dialog by clicking Done.

Strong finish. Your dashboard now tells a defensible budget story through a view-only link.

Secret mission

Add a ROAS Goal Alert

Set a hypothetical 300% ROAS goal and turn it into a red underperformance alert. Update the case-study recommendation to explain what the target changes.

Clean Up Your Resources

The basic Google Sheets features in this project cost $0 under your personal Google Account. Choose whether to keep the portfolio link available, restrict public access, or move the workbook to trash.

Resources you used:

The Campaign Performance Dashboard workbook stored in Google Drive.
The copied view-only link with Anyone with the link access and the Viewer role.

Check Your Sharing Identity

Your name and email are visible as the owner when you share a file by link.

Confirm that your Google Account uses a professional identity before adding the link to applications.

Keep everything running

Keep this option if recruiters still need access to your campaign case study. The workbook creates no ongoing charges.

Leave General access set to Anyone with the link.
Leave the sharing role set to Viewer.
Keep the copied view-only link in your portfolio or applications.

Recruiters can open the dashboard through the link. They cannot edit your analysis.

Pause - I'll come back to this later

Restrict public access while keeping the workbook in Google Drive. You can enable link sharing again when you are ready.

In the Campaign Performance Dashboard workbook from earlier, click Share.
Under General access, choose Restricted.
Click Done.

The copied link no longer gives access to people who were not explicitly added. Your workbook remains stored in Google Drive.

Delete - I don't want to use this again

Revoke public access before moving the workbook to trash. This closes the view-only sharing route before the file leaves your active Drive files.

In the open Campaign Performance Dashboard workbook, click Share.
Under General access, choose Restricted.
Click Done.
Click File in the top menu.
Select Move to trash.

The workbook now sits in Google Drive Trash. The copied public link no longer provides access because you restricted sharing first.

Nice Work!

You did it! Your recruiter-ready campaign dashboard in Google Sheets now turns synthetic advertising data into a defensible budget recommendation.

What you learned:

Build a recruiter-ready dashboard that compares three fictional campaigns from initial clicks through business value.
Calculate campaign efficiency through four metrics: CTR, conversion rate, cost per conversion, and ROAS.
Challenge the misleading click leader with outcome evidence. Communicate a controlled budget recommendation through a view-only case study with an explicit analytical caveat.
Secret Mission: Translate a hypothetical 300% ROAS target into a visual underperformance alert. Revise the recommendation around Awareness Social's results.

See what stuck with a quick quiz.