# Build the weekly marketing update that writes itself

## The challenge

You have one hour. By the end of it, your spreadsheet should be able to:

- Render a dashboard from the raw weekly data
- Spot what changed and what it means
- Write the weekly stakeholder update for you
- Push it to whatever channel your CMO actually reads — email, deck, or both

The closer it gets to **zero clicks Friday afternoon**, the better.

## What we're giving you

- **A starter Sheet** with 12 weeks of B2B SaaS marketing data — sessions, signups, MQLs, and demo bookings across five channels (Organic search, Paid search, Paid social, Email, Webinars). Real-feeling numbers, with a couple of deliberate anomalies hidden inside for the AI to find.
- **A starter Apps Script** — `=ASK()` custom function and one menu item that builds a basic dashboard. ~120 lines.
- **A reference build** — full dashboard, AI insights sidebar, weekly email draft, Slides export, anomaly flagging. Steal from it; don't copy it.
- **A Brand DNA template** to swap with your own.
- **A Gemini API key path** (free tier from AI Studio).

## The rules

1. **The data is the input.** Don't go find a transcript. The numbers are what we're working with.
2. **The reader is non-technical.** Your CMO doesn't want a chart, they want the takeaway.
3. **AI is the co-pilot, not the artist.** It shouldn't fabricate causes — it should describe what changed.
4. **The Friday-afternoon test.** Could you open this Sheet at 4pm on Friday, click one button, and have something stakeholder-ready by 4:05? The closer you get, the better.

## The upgrade ladder

Start with the starter Sheet. Pick one or more upgrades. Build as far up as you can before show-and-tell.

| Level | Upgrade | What you'll learn |
|-------|---------|-------------------|
| 1 | Build a dashboard tab with a real chart from the data | `Charts` API in Apps Script |
| 2 | Add an AI-generated weekly summary — "what changed and why?" | Prompt engineering on tabular data |
| 3 | Detect anomalies — flag weeks where a metric moved >2σ from rolling mean | Statistical helpers + AI explanation |
| 4 | Add per-channel drill-downs with sparklines | `SPARKLINE()` formulas + structured ranges |
| 5 | Draft the weekly stakeholder email — auto-saved as a Gmail draft | `GmailApp.createDraft` |
| 6 | Push the dashboard to a Slides deck for the leadership readout | `SlidesApp` API |
| 7 | Add a Brand DNA layer so summaries sound like your team | Loading external context into prompts |
| 8 | Schedule it to run every Friday at 4pm — Sheet rebuilds itself overnight | Time-driven triggers |

## What "zero clicks" means

Every feature decision passes this test: does this make Friday afternoon shorter, or longer?

A button that builds the dashboard is shorter than 30 minutes of pivot tables.
A button that drafts the email is shorter than 15 minutes of writing.
A scheduled trigger that does both before you sit down — that's the win condition.

## A note on the data

The dataset has **three patterns hidden in it**. Two are loud — anomaly detection will flag them once you've written it. One is quiet — it won't show up in any single column's rolling-mean check. Only a sharp prompt or a sharp eye will catch it.

We're not going to tell you what or where. Half the fun is whether your tool catches all three. Most teams catch the loud ones. The quiet one separates good prompts from lazy ones.

## Show-and-tell

Last 10 minutes. Bring whatever you've got. The interesting question is *"what does your version do that nobody else's does?"*

## Stack

- **Gemini** for analysis and narrative (`gemini-2.5-flash`)
- **Apps Script** for hosting + Workspace integration
- **Google Sheets / Docs / Slides / Gmail** as your data and your outputs

That's it. No external infra. The Sheet is the tool.

---

**Your one-line goal:** *Friday at 4pm. One click. The update is in your CMO's inbox.*