Security Risk Drill

---
name: phishing-attachment-drill
description: "Run a phishing-attachment scan on your support conversation data for a given date range. Reads conversations through Rippit across the channels you track, checks the true row count per channel before building anything, automatically batches large datasets, enriches every conversation with a fixed set of AI columns (attachment present, attachment contains a link, attachment type, external URLs found, and an overall phishing risk level), aggregates the findings, and delivers a native Google Sheets report per channel. Use it when you want a repeatable, batched sweep of customer conversations for suspicious attachments, lookalike domains, and social-engineering signals, with results in a shareable spreadsheet for your security or escalation team."
---
# Phishing Attachment Drill

This skill scans your support conversation data for suspicious attachments and links, scores each conversation for phishing risk, and delivers a native Google Sheets report per channel. It reads conversations through Rippit, batches large datasets automatically, and enriches every row with a fixed set of AI columns.

## First run — make it yours

Before asking anything, LOOK at what is connected:
1. List the available Rippit data sources and describe the one or two tables most relevant to conversation analysis.
2. Identify which columns could serve as: a channel / source-type field ({{channel_filters}}), a datetime column for the date range ({{date_range}}), a conversation identifier, a customer identifier, and a brand / business-unit field.
3. If the connected data lacks a datetime column or a channel field this skill needs, say so plainly and ask how to proceed rather than running into a dead end.

Then ASK, ONE QUESTION AT A TIME, leading with what you found and proposing a default:
- **{{data_table}}** — "Your workspace has one conversation source, <real source>, with a <real table> table — use that for the scan?"
- **{{date_range}}** — "What date range should I scan? Give exact start and end dates. NOTE: <real datetime column> looks like a DateTime — I will always use full ISO timestamps (T00:00:00 / T23:59:59), because date-only strings silently return 0 rows on DateTime columns."
- **{{channel_filters}}** — "I see a <real column> field with values like <found values>. The author's version split on an email channel and a chat channel. Which values map to which channels you want scanned?"
- **{{internal_domains}}** — "Which of your own domains and support addresses should I treat as internal and exclude when listing flagged external URLs?"
- **{{output_destination}}** — "I'll deliver the report as native Google Sheets, one sheet per channel. Which Drive location should they land in?"

After the interview, restate the filled-in bindings for confirmation, then run. On later runs, reuse these bindings unless the user asks to change them.

---

## Step 1 — Collect inputs

Confirm {{date_range}} (exact start and end dates). Channel scope comes from {{channel_filters}}; enrichment columns and output format are fixed.

## Step 2 — Row count pre-check (mandatory, every channel)

Before creating any worksheet, run `aggregate_table` with a `count` measure to get the true row count for each slice in {{channel_filters}}.

- Table: {{data_table}}
- Channel filter: the field/value pairs defined in {{channel_filters}}
- Date filter: `<datetime_column> BETWEEN <start>T00:00:00 AND <end>T23:59:59`

> **CRITICAL — never use date-only strings on a DateTime column.** Date-only strings like `2026-09-09` can return 0 rows with no error. Always use full ISO timestamps: `2026-09-09T00:00:00` / `2026-09-15T23:59:59`.

Report the counts per channel to the user before proceeding.

## Step 3 — Decide batching strategy

For each channel independently:
- **≤ 10,000 rows**: proceed with a single worksheet.
- **> 10,000 rows**: split the date range into batches of ≤ 9,000 rows each, present the batch plan (number of batches, date range and estimated rows per batch), wait for user confirmation, then launch all batches in parallel (single message, multiple Agent tool calls) — never sequentially. Always confirm the batch plan before running; never auto-batch silently.

## Step 4 — Create worksheets and enrich

For each channel (or batch):
1. Call `create_worksheet` with the appropriate `where` filters.
2. Wait for rows to land (re-read with `read_table` to confirm the worksheet is populated).
3. Call `enrich_worksheet` with all 5 columns in a **single call** (cost scales with rows, not columns).

### Fixed enrichment columns

```
Name: Has Attachment
Instructions: Read the full conversation transcript. Did the customer send any file attachment — image, screenshot, PDF, document, video, or other file? Answer true or false.
Output type: boolean

Name: Attachment Contains Link
Instructions: If the customer sent an attachment, does that attachment contain or reference an external URL or hyperlink? This includes links embedded in document text, image descriptions mentioning a URL, or file metadata. If no attachment was sent, answer false.
Output type: boolean

Name: Attachment Type
Instructions: What type of file did the customer attach? Choose the best match: image (photos, screenshots, JPG, PNG, GIF, WEBP), pdf, document (Word, Excel, text files), video (MP4, MOV, AVI), other (any file type not listed above), none (no attachment sent).
Output type: enum [image, pdf, document, video, other, none]

Name: URLs Found in Conversation
Instructions: List all external URLs or domains mentioned in this conversation by any party (customer or agent). Exclude the internal domains and support addresses in {{internal_domains}} and their subdomains. If no external URLs are present, write 'none'.
Output type: string

Name: Phishing Risk Level
Instructions: Based on the full conversation transcript, rate the phishing risk. HIGH = strong indicators of phishing or social engineering: lookalike domains, credential harvesting attempts, suspicious links in attachments, impersonation of official services, unusual file types with embedded URLs, or behavioral signals of manipulation. MEDIUM = moderate concern requiring human review: unverified external links, unrecognized attachment types, partial indicators without clear malicious intent. LOW = minor flags that are likely benign but worth logging: standard external links in context, image attachments with no URL, spam received by the team. NONE = no phishing indicators present.
Output type: enum [HIGH, MEDIUM, LOW, NONE]
```

## Step 5 — Poll enrichment status

After `enrich_worksheet`, immediately call `get_enrich_status` with the `worksheetId` and `jobId`. Poll until `done: true`; do not ask the user to check back. If a worksheet was capped at 10,000 rows but `rowsMatchingFilter > rowsAfter`, flag the gap and offer to re-run that period with a tighter date split.

## Step 6 — Aggregate results

Once all enrichment jobs complete, run `aggregate_table` on each worksheet to compute: total rows analyzed, count with attachments, count of attachments containing links, risk-level distribution (HIGH/MEDIUM/LOW/NONE), attachment-type breakdown, and a daily breakdown. Then `read_table` on each worksheet filtered to `Phishing Risk Level IN [HIGH, MEDIUM, LOW]` for the detail rows.

## Step 7 — Build and upload the Google Sheet

**Output is always native Google Sheets via MCP — never XLSX, never Artifacts.**

For each channel, build a CSV string of all report sections and upload with `mcp__Google_Drive__create_file` into {{output_destination}} using:
- `contentMimeType: "text/csv"` (plain text)
- no `disableConversionToGoogleType` flag (Drive auto-converts CSV to a native Sheet)
- `textContent`: the full CSV string passed directly

> **Why CSV, not XLSX base64?** Large inline base64 strings can silently truncate, causing "not a valid base64 string" errors. CSV + textContent avoids this at any size.

### CSV sections per report
1. **Summary** — scope statement, key metrics, daily breakdown, attachment-type breakdown, risk distribution
2. **Risk Cases** — all HIGH and MEDIUM rows with Risk, Date, External ID, Customer Email, Brand, Attachment, Link in File, Attachment Type, URLs Found, Channel, Backoffice Link, Assessment
3. **Suspicious URLs** — deduplicated flagged external URLs with Risk, URL, Date, External ID, Brand, Threat Type, Recommendation
4. **Low Risk Notable** — LOW-risk cases worth logging
5. **Scope & Next Steps** — what was analyzed, what was not, confirmed actions, recommended next steps

### Naming convention
- One sheet per channel, e.g. `Phishing Report – <Channel> (<date range>)`

## Step 8 — Deliver

Return all Google Sheets links to the user in one message, clearly labeled by channel.

## Key rules
- Never use date-only strings on a DateTime column — always full ISO timestamps.
- Never create a worksheet without counting rows first.
- Never upload XLSX as base64 — use CSV + textContent.
- Never deliver results as an Artifact — always native Google Sheets via MCP.
- Never enrich in multiple calls — batch all 5 columns into one `enrich_worksheet` call.
- Never launch batches sequentially — always parallel. Always confirm the batch plan first.
- Always poll `get_enrich_status` to completion.
- Always flag incomplete coverage if a batch was capped below its actual row count.