BI Dashboards

Why BI Dashboards matter for Marketing Analysts

BI dashboards turn raw channel data into decisions. As a Marketing Analyst, dashboards let you track KPIs (ROAS, CAC, CTR), compare channel efficiency, drill into campaigns, monitor funnel health, and alert teams when performance shifts. Strong dashboards save hours of manual reporting and enable self-serve insights for growth and acquisition teams.

Who this is for

• Marketing Analysts who report on performance across paid/owned channels.
• Growth and Acquisition Analysts who need fast, reliable KPIs and drilldowns.
• Generalist analysts asked to build executive scorecards and campaign views.

Prerequisites

• Basic spreadsheet fluency (formulas, pivots) and comfort with percentages/rates.
• Familiarity with core marketing metrics (impressions, clicks, spend, conversions, revenue).
• Foundational SQL or BI-calculated fields (measures) to create metrics.

Learning path (roadmap)

1. Define KPIs and data model
   Milestone details

   • List the exact metrics and formulas (ROAS, CPA, CTR, CVR, AOV).
   • Map relationships: Date table, Channels, Campaigns, Conversions, Spend.
   • Create a single source of truth for metric definitions.
2. Build a KPI scorecard
   Milestone details

   • Top-line metrics: Spend, Revenue, ROAS, CPA, CTR.
   • Week-over-week and month-to-date comparisons.
   • Currency banner: "Data as of" timestamp and refresh cadence.
3. Channel and campaign drilldowns
   Milestone details

   • Channel performance table with sorting, conditional formatting.
   • Campaign drillthrough page with filters for date, channel, device, geo.
   • Use slicers to match how marketers think (e.g., Brand vs Non-brand).
4. Funnel dashboard
   Milestone details

   • Stages: Impression → Click → Landing → Add-to-Cart → Purchase.
   • Stage conversion rates and loss diagnostics.
   • Align attribution windows and avoid double counting.
5. Refresh strategy and alerts
   Milestone details

   • Incremental refresh with backfill to cover late conversions.
   • Data currency indicator and latency notes.
   • Spike/drop alerts using thresholds and day-of-week baselines.
6. Self-serve enablement
   Milestone details

   • Glossary panel and metric tooltips.
   • Pre-built views for Exec, Channel Manager, Analyst.
   • Usage tips: saved filters, bookmarks, and export guidelines.

Worked examples (marketing-focused)

-- ROAS, CPA, CTR, CVR in SQL (example aggregate level)
SELECT 
  SUM(revenue) / NULLIF(SUM(spend), 0) AS roas,
  SUM(spend) / NULLIF(SUM(conversions), 0) AS cpa,
  SUM(clicks) * 1.0 / NULLIF(SUM(impressions), 0) AS ctr,
  SUM(conversions) * 1.0 / NULLIF(SUM(clicks), 0) AS cvr
FROM marketing_daily;

-- Aggregate by channel and month
SELECT 
  channel,
  DATE_TRUNC('month', date) AS month,
  SUM(spend) AS spend,
  SUM(revenue) AS revenue,
  SUM(conversions) AS conversions,
  SUM(revenue) / NULLIF(SUM(spend), 0) AS roas,
  SUM(spend) / NULLIF(SUM(conversions), 0) AS cpa
FROM marketing_daily
GROUP BY 1, 2
ORDER BY month, channel;

-- Compute stage-to-stage conversion rates
WITH s AS (
  SELECT date, channel, SUM(impressions) AS imp, SUM(clicks) AS clk,
         SUM(landing_visits) AS land, SUM(add_to_cart) AS atc,
         SUM(purchases) AS pur
  FROM funnel_daily
  GROUP BY 1,2
)
SELECT channel,
  SUM(clk)*1.0 / NULLIF(SUM(imp), 0) AS ctr,
  SUM(land)*1.0 / NULLIF(SUM(clk), 0) AS lcr,
  SUM(atc)*1.0 / NULLIF(SUM(land), 0) AS acr,
  SUM(pur)*1.0 / NULLIF(SUM(atc), 0) AS pcr
FROM s
GROUP BY channel;

-- DAX (concept) for "Data as of"
DataAsOf = MAX('RefreshLog'[completed_at_utc])
DataCurrencyLabel = 
  "Data as of " & FORMAT([DataAsOf], "yyyy-mm-dd HH:MM" ) & " UTC"

-- SQL moving average and z-score style check
WITH b AS (
  SELECT date,
         channel,
         spend,
         AVG(spend) OVER (PARTITION BY channel, EXTRACT(DOW FROM date)
                           ORDER BY date ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING) AS dow_avg,
         STDDEV_SAMP(spend) OVER (PARTITION BY channel, EXTRACT(DOW FROM date)
                           ORDER BY date ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING) AS dow_sd
  FROM spend_daily
)
SELECT *,
  CASE WHEN dow_sd IS NULL OR dow_sd = 0 THEN 0 ELSE (spend - dow_avg)/dow_sd END AS z
FROM b;

Drills and mini tasks

• Create a one-page KPI scorecard with Spend, Revenue, ROAS, CPA, CTR. Add week-over-week deltas.
• Build a channel table with conditional formatting that highlights ROAS < 2.0 when spend > median.
• Add slicers for Date (relative last 30 days), Channel, Device, Country.
• Design a drillthrough page that shows campaign details when a campaign is right-clicked.
• Implement a "Data as of" label and note expected latency (e.g., paid social: 24–48h).
• Create an anomaly badge using a 7-day moving average vs current day by DOW.

Common mistakes and debugging tips

• Mixing attribution windows: Align across sources or clearly separate views (e.g., 7-day click vs 28-day view).
• Double counting conversions: If joining multiple tables, aggregate first per grain (date, channel, campaign) before joining.
• Inconsistent metric definitions: Centralize glossary; define once and reuse measures.
• Ignoring conversion lag: Use incremental refresh with a rolling backfill (e.g., last 7–14 days).
• Overcluttered visuals: Prioritize the question; fewer charts, clearer labels, add tooltips for context.
• Timezone mismatch: Normalize to a single timezone and label it in the currency banner.
• Silent data failures: Add a refresh status tile and row counts vs prior period to catch breaks.

Mini project: Full-Funnel Growth Dashboard

Deliver a dashboard that leadership and channel managers can both use.

1. Data model: Tables for Date, Channel, Campaign, Daily Performance (impressions, clicks, spend), Conversions (purchases, revenue).
2. KPI scorecard: Spend, Revenue, ROAS, CPA, CTR, CVR with WoW deltas.
3. Channel view: Rank channels by spend; color-code low ROAS.
4. Campaign drilldown: Right-click drillthrough; detail page shows trends and cost breakdown.
5. Funnel: Stage conversion rates and drop-off percentages.
6. Reliability: Data as of label + note on latency and backfill window. Add anomaly flags.

Practical projects

• Executive KPI one-pager: Minimalist scorecard with YoY/WoW indicators and narrative notes using text boxes.
• Creative cohort tracker: Group campaigns by creative concept; compare ROAS and CPA over time.
• Geo/device matrix: Heatmap of ROAS by Country x Device with top/bottom filters.
• Budget pacing tool: Daily spend vs plan; forecast end-of-month spend and ROAS using simple linear trend.

Subskills

Next steps

• Go through each subskill section and complete the drills.
• Build the mini project and validate against a sample export.
• Take the Skill Exam at the bottom. Everyone can take it for free; only logged-in users get saved progress and auto-resume.