How to build Microsoft Advertising dashboards in Metabase

# 1) Submit a report request (Reporting API v13, REST)
curl -X POST "https://reporting.api.bingads.microsoft.com/Reporting/v13/GenerateReport/Submit" \
  -H "Authorization: Bearer $MSADS_ACCESS_TOKEN" \
  -H "DeveloperToken: $MSADS_DEV_TOKEN" \
  -H "CustomerId: $CUSTOMER_ID" \
  -H "CustomerAccountId: $ACCOUNT_ID" \
  -H "Content-Type: application/json" \
  -d @campaign-performance-request.json

# 2) Poll GenerateReport/Poll with the returned ReportRequestId,
#    then download and unzip the CSV it links to.

# 3) Load it into Metabase
mb upload csv --file microsoft-ads-campaign-daily.csv --collection root

# Install the Metabase CLI
npm install -g @metabase/cli

# Log in (opens your browser; requires Metabase v62+)
mb auth login --url https://your-metabase.example.com

# Load a campaign-daily-stats export — creates a table AND a model
mb upload csv --file microsoft-ads-campaign-daily-stats.csv --collection root

# Refresh that same table later from a new export
mb upload replace <table-id> --file microsoft-ads-campaign-daily-stats.csv

Create a polished Metabase dashboard for Microsoft Advertising advertising analytics.
Work end to end: get the data into Metabase if it isn't there yet, then build.

Goal: Help marketing and growth leaders understand spend, ROAS, CTR, CPC, budget pacing, and acquisition cost by campaign and channel from Microsoft Advertising data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for microsoft-ads tables and
  models). If durable Microsoft Advertising data is already present — synced from a warehouse
  or uploaded earlier — use it and skip to Step 2.
- If nothing is there, pull a scoped, summarized export from the Microsoft Advertising Reporting API (write and run a short export script):
  campaign daily stats, plus campaigns, ad groups, keywords.
  Prefer daily aggregates over raw events. Write each result to a CSV,
  then load it with the Metabase CLI — run "mb upload csv --file <export>.csv" so
  each upload creates a table and a ready-to-query model. Use "mb upload replace
  <table-id> --file <export>.csv" to refresh an existing table instead of creating
  duplicates.

Step 2 — Inspect before querying:
Do not assume exact table or column names. Inspect available fields, channels,
campaigns, dates, and whether daily history exists before creating trend or
pacing cards.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Microsoft Advertising — it reads a database or CLI-uploaded tables.
- Never try to load raw event or click streams into Metabase; use daily
  aggregates, campaign-grain stats, and entity tables.
- Only compute rates (CTR, conversion rate, ROAS, CAC) when both numerator and
  denominator exist — and state the attribution model when reporting conversions.
- Exclude test campaigns and internal traffic from headline cards, and keep
  currency consistent when spend spans accounts.
- A single CSV is a point-in-time snapshot: only build trend cards if there is a
  usable date column or multiple periods have been uploaded.

Dashboard title: Microsoft Advertising Advertising Overview

Sections:
1. Executive summary: Spend this month; Conversions; ROAS; CAC; Budget pacing
   vs. plan.
2. Campaigns: Spend, clicks, conversions, and ROAS by campaign by week.
3. Efficiency: CTR, CPC, and cost-per-conversion trends; wasted-spend table.
4. Pacing: Month-to-date spend vs. budget; projected end-of-month spend.
5. Acquisition: CAC and new customers by channel, joined to revenue where synced.

Filters: Date range, Channel, Campaign, Country, Device, Segment.

Output: Build the dashboard if you have permission; otherwise provide the exact
questions, SQL, model definitions, and layout. Include caveats for any metric
that cannot be calculated from the available data.

The core paid-performance trend.

SELECT
  campaign_name,
  date_trunc('week', stat_date) AS week,
  ROUND(SUM(spend), 2) AS spend,
  SUM(conversions) AS conversions,
  ROUND(SUM(conversion_value) / NULLIF(SUM(spend), 0), 2) AS roas
FROM ad_performance_daily
GROUP BY 1, 2
ORDER BY 2, spend DESC;

Efficiency signals across the account.

SELECT
  channel,
  date_trunc('month', stat_date) AS month,
  ROUND(100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0), 2) AS ctr_pct,
  ROUND(SUM(spend) / NULLIF(SUM(clicks), 0), 2) AS cpc,
  ROUND(SUM(spend) / NULLIF(SUM(conversions), 0), 2) AS cost_per_conversion
FROM ad_performance_daily
GROUP BY 1, 2
ORDER BY 1, 2;

Spend joined to newly acquired customers.

WITH monthly_spend AS (
  SELECT
    channel,
    date_trunc('month', stat_date) AS month,
    SUM(spend) AS spend
  FROM ad_performance_daily
  GROUP BY 1, 2
),
monthly_customers AS (
  SELECT
    acquisition_channel AS channel,
    date_trunc('month', acquired_at) AS month,
    COUNT(*) AS new_customers
  FROM customers
  GROUP BY 1, 2
)
SELECT
  s.channel,
  s.month,
  ROUND(s.spend, 2) AS spend,
  COALESCE(c.new_customers, 0) AS new_customers,
  ROUND(s.spend / NULLIF(c.new_customers, 0), 2) AS cac
FROM monthly_spend s
LEFT JOIN monthly_customers c
  ON c.channel = s.channel AND c.month = s.month
ORDER BY 1, 2;