Customer Newsletter

The user provides:

1. Account name (required) - e.g., "SITA", "Nestle", "MSC CARGO"
2. --to email (optional) - recipient email address for the draft
3. --from name,email (optional) - sender name and email for CTA/footer. If omitted, defaults to the Account's AE (Owner) from Salesforce.
4. --since YYYY-MM-DD (optional) - only include content published after this date. Defaults to 3 months ago (quarterly cadence). Use --since 2025-01-01 to widen the search window.
5. --reset (optional) - clear the send history for this account, allowing previously sent links to be reused. Use when onboarding a new recipient or replaying from scratch.
6. --date YYYY-MM-DD (optional) - generate the newsletter as if it were that date (for replays/backfills). Affects the "Month Year" in the header and the --since default window.
7. --domain "topic" (optional) - Focus the newsletter on a specific domain (e.g., "Predictive Maintenance in Cargo", "Real-time Fraud Detection", "Customer 360"). When set, content curation shifts from general UCO-based matching to domain-focused research including tutorials, demos, articles, reference architectures, and case studies. Target 6-8 links grouped by category instead of the standard 4-6.

Inline help: If the user passes help, --help, or ?, display:

Usage: /customer-newsletter <Account Name> [--to [email protected]] [--from "Name,email"] [--since YYYY-MM-DD] [--reset] [--date YYYY-MM-DD] [--domain "topic"]

Generates a personalized Databricks newsletter for a customer:
  1. Queries Salesforce for account info and active Use Cases
  2. Searches web for relevant Databricks content (blogs, docs, case studies)
  3. Creates a professional HTML email with curated links
  4. Saves as Gmail draft ready to send
  5. Records sent links to avoid repeats in future newsletters

The sender (CTA button + footer) defaults to the Account's AE from Salesforce.
Override with --from to use a different sender.

Options:
  --since YYYY-MM-DD   Only include content after this date (default: 3 months ago)
  --reset              Clear send history for this account (fresh start)
  --date YYYY-MM-DD    Generate as if today were this date (for replays)
  --domain "topic"     Focus newsletter on a specific domain topic (6-8 links grouped by category)

Examples:
  /customer-newsletter SITA
  /customer-newsletter SITA --to [email protected]
  /customer-newsletter SITA --to [email protected] --from "Laurent Prat,[email protected]"
  /customer-newsletter SITA --since 2025-06-01              # wider content window
  /customer-newsletter SITA --reset --to [email protected]     # fresh start, no dedup
  /customer-newsletter SITA --date 2025-12-01               # replay Q4 2025 newsletter
  /customer-newsletter SITA --domain "Predictive Maintenance in Aviation"
  /customer-newsletter "MSC CARGO" --domain "Supply Chain Optimization" --to [email protected]
  /customer-newsletter help

Workflow

Phase 1: Collect Customer Context from Salesforce

Use the Salesforce CLI (/opt/homebrew/bin/sf) to query account and UCO data.

Step 1a: Get Account Info (including AE)

/opt/homebrew/bin/sf data query --query "SELECT Id, Name, Industry, Owner.Name, Owner.Email FROM Account WHERE Name LIKE '<account_name>%'" --json

Use prefix match (Name LIKE 'SITA%') not contains match (%SITA%) to avoid too many results.

Selecting the right account when multiple matches are returned:

• Prefer accounts with "Main account" in the name (e.g., "Swatch Group - Main account")
• If no "Main account" variant, prefer the one whose Industry is non-null
• If still ambiguous, query UCOs for each candidate and pick the one with active UCOs (U2-U6)
• Ignore unrelated accounts that happen to share a prefix (e.g., "swatchbook" vs "Swatch Group")

The Owner.Name and Owner.Email fields give us the Account Executive (AE). Use these as the sender in the CTA button and footer unless --from was explicitly provided.

Step 1b: Get Active Use Cases (U2-U6)

/opt/homebrew/bin/sf data query --query "SELECT Id, Name, Stages__c, Use_Case_Description__c, Implementation_Status__c, Demand_Plan_Next_Steps__c FROM UseCase__c WHERE Account__c = '<account_id>' AND Stages__c IN ('U2','U3','U4','U5','U6')" --json

Note on field names: The Account and UseCase__c objects do NOT have Products__c, Workloads__c, Platform__c, Cloud_Type__c, ACV__c, or ARR__c fields. Extract workload/product context from the UCO Name and Use_Case_Description__c fields instead.

Extract from results:

• Industry (from Account)
• Products and workloads (inferred from UCO names and descriptions, e.g., Unity Catalog, Delta Lake, MLflow, Streaming)
• UCO stages (to tailor content maturity: U2-U3 = evaluating, U4-U5 = onboarding, U6 = production)

Phase 1b: Load Send History from Google Drive (Deduplication)

Previously sent newsletters are stored in a shared Google Drive folder. Each newsletter HTML file contains a <!-- newsletter-meta --> comment with the full list of links used. This is the source of truth for deduplication — no local history files.

Drive structure: SA Customer Newsletters / <Account Name> / in Google Drive (shared with databricks.com domain as writer, so any SA can generate newsletters and dedup across the team).

Each customer gets its own subfolder (e.g., SA Customer Newsletters/SITA/, SA Customer Newsletters/MSC Cargo/). This keeps newsletters organized per account even if the AE changes.

Known Drive folder ID: The parent folder "SA Customer Newsletters" has ID 1AdcDxj-gA55j1ihLt0Ohbjyd3WtpISbN. Use this directly to skip the search step when possible.

Steps:

1. Search Google Drive for the parent newsletters folder (or use known ID 1AdcDxj-gA55j1ihLt0Ohbjyd3WtpISbN):

TOKEN=$(gcloud auth application-default print-access-token)
curl -s "https://www.googleapis.com/drive/v3/files?q=name%3D'SA+Customer+Newsletters'+and+mimeType%3D'application/vnd.google-apps.folder'+and+trashed%3Dfalse&fields=files(id,name)" \
  -H "Authorization: Bearer $TOKEN" \
  -H "x-goog-user-project: gcp-sandbox-field-eng"

If the folder doesn't exist, create it (see Phase 4b).

2. Find or create the customer subfolder:

PARENT_FOLDER="<parent_folder_id>"
ACCOUNT_NAME="<Account Name>"
# Search for existing customer subfolder
curl -s "https://www.googleapis.com/drive/v3/files?q='${PARENT_FOLDER}'+in+parents+and+name%3D'${ACCOUNT_NAME}'+and+mimeType%3D'application/vnd.google-apps.folder'+and+trashed%3Dfalse&fields=files(id,name)" \
  -H "Authorization: Bearer $TOKEN" \
  -H "x-goog-user-project: gcp-sandbox-field-eng"

If not found, create it as a child of the parent folder.

3. List all newsletter files in the customer subfolder:

CUSTOMER_FOLDER="<customer_folder_id>"
curl -s "https://www.googleapis.com/drive/v3/files?q='${CUSTOMER_FOLDER}'+in+parents+and+trashed%3Dfalse&fields=files(id,name,createdTime)" \
  -H "Authorization: Bearer $TOKEN" \
  -H "x-goog-user-project: gcp-sandbox-field-eng"

4. For each file found, download and extract the <!-- newsletter-meta --> comment:

FILE_CONTENT=$(curl -s "https://www.googleapis.com/drive/v3/files/${FILE_ID}?alt=media" \
  -H "Authorization: Bearer $TOKEN" \
  -H "x-goog-user-project: gcp-sandbox-field-eng")
# Extract links from metadata comment
echo "$FILE_CONTENT" | grep -o '<!-- newsletter-meta:.*-->' | head -1

4. Parse the links= field from each metadata comment to build the previously sent links set
5. If --reset was passed, skip loading history (treat as empty set)
6. Pass the previously sent links set to Phase 2 for exclusion

Note: The newsletter HTML file IS the history record. No separate JSON files needed. This makes history shareable across SAs and machines.

Phase 2: Web Search for Relevant Databricks Content

Based on the customer context from Phase 1, curate 4-6 relevant public Databricks links.

IMPORTANT - Deduplication: Exclude any URL found in the previously sent links set (from Phase 1b). If a candidate link was already sent in a previous newsletter for this account, skip it and find a different one. This ensures each quarterly newsletter has fresh content.

Strategy: Try WebSearch first, fall back to known URLs if unavailable.

Date window: Determine the content search window:

• If --since provided: use that date as the start
• If --date provided: default to 3 months before that date
• Otherwise: default to 3 months before today
• Include the year in all WebSearch queries to bias toward recent results (e.g., "databricks unity catalog 2026")

Step A: Try WebSearch for content matching customer products/industry/workloads.

When --domain is set: Replace UCO-based search strategy with domain-focused searches across 6 content categories. Run WebSearch queries targeting each category:

• Target 6-8 links (more than the standard 4-6, since domain mode is comprehensive)
• Each category should have at least 1 link if available
• All links still go through curl verification
• Still exclude previously sent links (dedup from Drive history)
• Still apply --since date window where relevant

When --domain is NOT set (standard mode):

• Product updates matching their stack (e.g., "databricks streaming best practices 2026", "databricks unity catalog 2026")
• Industry content for their vertical (e.g., "databricks manufacturing logistics", "databricks aviation")
• Best practices matching UCO stages (U2-U3 = getting started; U4-U5 = architecture guides; U6 = optimization)
• Exclude previously sent links (from Phase 1b history). If a search result URL is in the sent set, skip it.

Step B: If WebSearch fails, use known-good Databricks URLs from this seed list (all verified 200 as of Mar 2026):

# Product pages