Documenting SFMC Automations with AI

Last Updated on July 10, 2025

Documentation life hack of the day: Do you have a MASSIVE automation in Marketing Cloud Engagement, which you never came around to document, because…. documentation is not your… thing? (Be honest: who doesn’t?)

Then pay close attention! As an example, I have used my DEWS Automation/Solution, described here: Don’t Let Deliverability Issues Sneak Up on You: Introducing the Deliverability Early Warning System for SFMC. So, what to do?

Step 1: Export your Automation as a definition (JSON based) in Package Manager:

Step 2: Upload this JSON file to your favourite GPT platform, asking it to tell you what this automation is supposed to do:

Step 3: Enjoy more accurate documentation than you had in step 1 👆

But how is the result then? Well – you can judge yourself. All the below output has been generated purely using the above prompt and the packaged automation. No editing has been done by mere – pure GPT Copy&Paste!

Overview: What Does DEWS Do?

DEWS is an automated, scheduled process in Salesforce Marketing Cloud (SFMC) that:

Monitors deliverability metrics (like open rate, bounce rate, etc.) by mailbox provider (MBP/ISP/domain)
Flags underperforming mailbox providers
Notifies selected recipients via email alert, providing a summary of issues and affected sends

Typical business value: Early detection of inboxing/deliverability issues, so you can react before things get out of hand (e.g., blocklisting or massive engagement drops).

High-Level Process Flow

Run SQL queries on SFMC send logs to aggregate deliverability data by domain/ISP.
Calculate overall (average) performance for comparison.
Identify ISPs (domains) that are underperforming compared to the average (e.g., open rate is much lower than average, or bounce rate is much higher).
Aggregate recent occurrences to prioritize issues.
Verify that there’s data to act upon.
Send an alert email with a summary to stakeholders.

Step-by-Step: Automation Activities

Let’s go through each automation step, including the SQL, data extensions, and the notification logic.

1. Deliverability EWS DataPrep – domain data

(Query Activity: 9f09dc99-8a0d-40a8-bb16-fe483fc554d8)

SELECT
  sent.jobid,
  COALESCE(dm.isp, sent.domain) AS ISP,
  COUNT(*) AS sends,
  COUNT(o.subscriberid) AS opens,
  COUNT(b.subscriberid) AS bounces,
  MAX(sent.eventdate) AS lastSend,
  CAST(COUNT(o.subscriberid) * 100.0 / COUNT(*) AS INT) AS openRate,
  CAST(COUNT(b.subscriberid) * 100.0 / COUNT(*) AS INT) AS bounceRate
FROM _sent AS sent
LEFT JOIN domainMapping AS dm ON sent.domain = dm.domain
LEFT JOIN _open AS o ON o.jobid = sent.jobid AND o.subscriberid = sent.subscriberid AND o.isunique = 'True' AND o.eventdate > DATEADD(day, -7, GETDATE())
LEFT JOIN _bounce AS b ON b.jobid = sent.jobid AND b.subscriberid = sent.subscriberid AND b.isunique = 'True' AND b.eventdate > DATEADD(day, -7, GETDATE())
WHERE sent.eventdate > DATEADD(day, -7, GETDATE())
GROUP BY sent.jobid, COALESCE(dm.isp, sent.domain)
HAVING COUNT(*) > 50

Purpose: Aggregate send, open, and bounce data by job and domain/ISP for the last 7 days.
How: Joins _Sent, _Open, _Bounce system data views. Groups by job and domain/ISP, counts sends, opens, and bounces. Only keeps results where more than 50 sends for that domain/job.
Output: Stores in Data Extension: Deliverability EWS – MBP Based

2. Deliverability EWS DataPrep – AVG calculation

(Query Activity: 132817af-ba79-4668-835d-d40b9700140d)

SELECT
  s.jobid,
  s.sends,
  COALESCE(o.opens, 0)    AS opens,
  COALESCE(b.bounces, 0)  AS bounces,
  CAST(o.opens   * 100.0 / s.sends AS INT) AS openRate,
  CAST(b.bounces * 100.0 / s.sends AS INT) AS bounceRate,
  s.lastSend,
  'average'               AS ISP
FROM
  (
    SELECT
      jobid,
      COUNT(subscriberid) AS sends,
      MAX(eventdate)     AS lastSend
    FROM _sent
    WHERE eventdate >= DATEADD(day, -14, GETDATE())
    GROUP BY jobid
  ) AS s
  LEFT JOIN (
    SELECT jobid, COUNT(subscriberid) AS opens
    FROM _open
    WHERE isunique = 'True'
      AND eventdate >= DATEADD(day, -14, GETDATE())
    GROUP BY jobid
  ) AS o
    ON s.jobid = o.jobid
  LEFT JOIN (
    SELECT jobid, COUNT(subscriberid) AS bounces
    FROM _bounce
    WHERE isunique = 'True'
      AND eventdate >= DATEADD(day, -14, GETDATE())
    GROUP BY jobid
  ) AS b
    ON s.jobid = b.jobid

Purpose: Calculate the average open and bounce rates across all domains for each send/job (acts as a “benchmark”).
How: For each jobid, counts all sends, opens, and bounces for last 14 days. Outputs one row per jobid labeled with ISP=’average’.
Output: Adds “average” records into the same Data Extension as above.

3. Deliverability EWS – Enrich with EmailName

(Query Activity: d1196fb8-f2bb-4510-a923-317d4515f4b9)

SELECT 
  dews.jobid, 
  dews.ISP, 
  _job.emailname, 
  _job.emailsubject 
FROM [Deliverability EWS - MBP Based] dews
JOIN _job ON _job.jobid = dews.jobid

Purpose: Add the email name and subject to each row, so alerts can reference them.
How: Joins the MBP-based data with the _job system data view.
Output: Updates the Deliverability EWS – MBP Based DE with the extra info.

4. Deliverability EWS – underperforming MBPs

(Query Activity: 1f826abd-7cc2-4cff-926a-ab48a5254f11)

SELECT
    d.ISP,
    d.jobid,
    d.sends,
    d.opens,
    d.bounces,
    d.openrate,
    d.bouncerate,
    d.clickrate,
    a.clickrate AS avgClickrate,
    a.openrate AS avgOpenrate,
    a.bouncerate AS avgBouncerate,
    d.lastsend,
    d.emailname,
    d.emailsubject
FROM [Deliverability EWS - MBP Based] d
INNER JOIN [Deliverability EWS - MBP Based] a
    ON d.jobid = a.jobid
   AND a.ISP = 'average'
WHERE d.ISP <> 'average'
  AND (
        d.openrate < (a.openrate * 0.6)
     OR (d.bouncerate > (a.bouncerate * 2) and (a.bouncerate > 0) and (d.bouncerate > 0))
      )

Purpose: Find domains/ISPs that are significantly underperforming compared to the average for that job.
How: For each domain/ISP that isn’t “average”, compare open rate and bounce rate vs. average:
- Open rate < 60% of average OR
- Bounce rate > 2x average (and both are > 0)
Output: Results written to Deliverability EWS Alert – MBP Based

5. Deliverability EWS – Recently problematic ISPs

(Query Activity: 8dfe3ba6-42f0-4cfa-8cea-f28086ad233d)

SELECT 
  jobid, 
  ISP, 
  sends, 
  opens, 
  bounces, 
  openrate, 
  avgOpenrate, 
  bouncerate, 
  avgBouncerate, 
  clickrate, 
  avgclickrate, 
  clicks,
  lastSend, 
  emailName, 
  emailSubject,
  COUNT(*) OVER (PARTITION BY ISP) AS occurences
FROM [Deliverability EWS Alert - MBP Based]
WHERE sends > 50
  AND lastsend > DATEADD(day,-7,GETDATE())

Purpose: Aggregate how many times each ISP/domain has appeared as “problematic” in the last 7 days, for prioritization.
How: Counts occurrences per ISP/domain, filters for sends > 50, and recent activity.
Output: Populates DEWS recent issues (this DE is the “input” for the notification logic).

6. Verification: DEWS recent issues

(Verification Activity: b004193c-e3aa-40b5-af1a-9bc3f4f19421)

Purpose: Only continue if the “DEWS recent issues” DE contains data (i.e., there really are current issues).
How: Stops the automation if there are no records to process (prevents sending empty alerts).

7. Send Notification Email: DEWS Notifications

(Send Definition: 8bbf4730-d325-f011-baa3-48df37deb10c)

Purpose: Send a summary email to defined recipients.
How: Uses an HTML email asset, AMPscript pulls from the “DEWS recent issues” DE to display the most problematic MBPs, how many times they have been an issue, open and bounce rates, and which sends/jobs were affected.
Recipients: Pulled from the DEWS – Notifications DE (with subscriberKey, emailAddress, name).

Subject line:
“Vi har deliverability problemer med %%=v(@mostProblematicDomain)=%%”
(“We have deliverability problems with [Domain]”)

Email Body (AMPscript summary):
Lists problematic MBPs, # of occurrences
For each, shows open/bounce rate vs average, send name, and date

Data Extensions (DEs): Key Schemas

Deliverability EWS – MBP Based

Stores aggregated metrics per jobid/ISP for last 7 days

Field	Type	Description
jobid	Number	SFMC Job ID
ISP	Text	Domain/ISP
sends	Number	Number of sends
opens	Number	Number of opens
bounces	Number	Number of bounces
openrate	Number	% opens
bouncerate	Number	% bounces
emailName	Text	Name of the email
emailSubject	Text	Subject line
lastSend	Date	Most recent send
…	…	…

Deliverability EWS Alert – MBP Based

Subset of above, only for underperforming MBPs + calculated deltas vs. average.

DEWS recent issues

Results of problematic MBPs for the last 7 days, used in the email summary.

DEWS – Notifications

The “audience” DE for notification emails (subscriberKey, emailAddress, name).

Scheduled Automation

Runs: Weekdays (Mo-Fri)
Schedule: E.g., daily at 07:39 (can be adjusted as needed)
Flow: Steps above, in sequence

Email Content Example (summarized by AMPscript)

Email shows:

Most problematic MBPs: Which domain/ISP is causing the most trouble, and how often.
Open/bounce rates: Compared to normal/average.
Which sends were affected: By name, subject, and date.
Human-readable: Designed for ops/marketing/deliverability owners.

Summary Table: DEWS Automation Steps

Step	Action/Query	Output DE	Purpose
1	Aggregate metrics by domain/ISP	Deliverability EWS – MBP Based	Raw metrics by ISP/domain
2	Calculate “average” job-level metrics	Deliverability EWS – MBP Based	Benchmark for comparison
3	Enrich with EmailName/Subject	Deliverability EWS – MBP Based	User-friendly reporting
4	Find underperforming MBPs	Deliverability EWS Alert – MBP	Highlight where there are problems
5	Aggregate recent problematic ISPs	DEWS recent issues	Show which problems are recurring
6	Verify there’s data to process	–	Don’t send empty alerts
7	Send notification to recipients	–	Alert the right people

Customization/Extension

Add more metrics: E.g., click rate, complaint rate, etc.
Change thresholds: Tune what counts as “underperforming.”
Expand audience: Add/rem recipients to DEWS – Notifications.
Change frequency: Adjust schedule for more/less frequent checks.

Final Notes

The DEWS process is data-driven: All logic is based on actual campaign performance.
Each step writes to a data extension; each is fully auditable/historicized.
The notification is actionable: It tells you which MBPs need your attention and which sends were affected.

Digital Marketing on Cloud