Context

Marketing teams often start campaign reporting in Google Sheets or Excel, but interviewers want to know whether you can structure the data model cleanly and translate that logic into SQL.

Question

Explain how you would build a tracking model in Google Sheets or Excel to monitor campaign efficiency. Your answer should cover:

1. What columns and tabs you would create
2. Which core efficiency metrics you would calculate
3. How you would structure the data so it can later be analyzed in SQL
4. Common mistakes that make campaign tracking unreliable

Scope guidance

Keep the answer practical. Focus on a simple but scalable model for campaign-level performance tracking, including inputs, calculated fields, and reporting outputs. You should also explain how spreadsheet formulas and pivots relate to SQL concepts such as filtering, grouping, and aggregation.

Structured campaign fact table

The core of a reliable tracking model is a row-based table where each row represents a campaign-day, campaign-channel, or campaign-ad combination. This structure avoids merged cells and manual summaries, and it maps directly to SQL tables.

SELECT campaign_name, report_date, spend, clicks, conversions
FROM campaign_performance;

Efficiency metrics

Campaign efficiency is usually measured with ratios such as CTR, CPC, CPA, ROAS, and conversion rate. These should be defined consistently so the same formulas are used across spreadsheet reports and SQL dashboards.

SELECT campaign_name,
       SUM(spend) / NULLIF(SUM(clicks), 0) AS cpc,
       SUM(conversions)::numeric / NULLIF(SUM(clicks), 0) AS conversion_rate
FROM campaign_performance
GROUP BY campaign_name;

Aggregation logic

Spreadsheet pivot tables and SQL GROUP BY solve the same problem: summarizing detailed rows into campaign, channel, or monthly performance. The key is to aggregate raw inputs first, then calculate ratio metrics from aggregated totals.

SELECT channel,
       SUM(spend) AS total_spend,
       SUM(revenue) AS total_revenue,
       SUM(revenue) / NULLIF(SUM(spend), 0) AS roas
FROM campaign_performance
GROUP BY channel;

Date-based reporting

Campaign tracking usually needs daily, weekly, or monthly rollups. In spreadsheets this is often done with pivot grouping; in SQL it is handled with date truncation or formatting functions.

SELECT TO_CHAR(report_date, 'YYYY-MM') AS report_month,
       SUM(spend) AS total_spend
FROM campaign_performance
GROUP BY TO_CHAR(report_date, 'YYYY-MM');

Data quality controls

A tracking model is only useful if the inputs are consistent. Standardized campaign names, fixed channel values, and checks for missing spend or conversion data prevent reporting errors and make downstream SQL much easier.

SELECT *
FROM campaign_performance
WHERE spend IS NULL OR campaign_name = '';