ServicesSalesforce consultingArchitectureImplementationOrg health checkDevelopmentIntegrations and APIsData migrationTesting and QAManaged supportSpecialist supportMarketing Cloud auditDeliverability
SolutionsMarketing Cloud EngagementMarketing Cloud NextSalesforce + BrazeAccount EngagementData Cloud (Data 360)Identity resolutionInsights and segmentsAgentforce and AIMCP integrationsSales and Service CloudReal-time personalisationIntegration solutionsEnterprise AI integrationBy industry
ArchitectureSalesforce CRMData 360Salesforce + BrazeAgentforceCustomer 360API-led integrationEvent-drivenMCP host, client and serverIdentity and consentMCP securityData governanceCI/CD and releasesOur public MCP server
ResourcesEngineering journalDecision guidesSolution blueprintsSalesforce productsProjects and achievementsSales ROI calculatorWhere we workChecklists and templatesPlanning toolsReadiness assessmentGlossaryAbout GeekMidstOur approachOur expertiseContact
CompanyAbout GeekMidstOur approachDelivery methodologyTrust and evidenceProjectsAchievementsBook a consultationContactWhere we workSecurity and data
Book a Consultation

Automation Studio services

Reliable automations that import, prepare and deliver data on schedule, with clear dependencies and alerts that reach the people who can act on them.

Engineer checks a phone alert at dawn in an empty office, laptop showing a row of green blocks with one amber

Automation Studio is the engine room of most Marketing Cloud Engagement accounts. It imports files, runs SQL to build audiences, moves files to and from the SFTP, and refreshes the data extensions that journeys depend on. When it works, nobody notices. When an automation fails at 3 a.m., the morning send goes to yesterday's audience, or to nobody at all.

GeekMidst designs, builds and repairs Automation Studio processes so they are dependable, observable and understandable by the next person who opens them.

Typical situations

  • Dozens of automations with names like Copy of Daily Import 2, and no record of what depends on what.
  • Query activities that time out or produce duplicates as data volume grows.
  • File imports that fail when a supplier changes a column order, with no alert.
  • Scheduled automations that overlap, so one reads a data extension while another is overwriting it.
  • Script activities written years ago that no one is comfortable editing.

Activities we commonly design and what they are for

ActivityUsed forWhat we pay attention to
File TransferMoving and decrypting files on the SFTPNaming patterns, encryption, archive folders
Import FileLoading files into data extensionsAdd, update or overwrite mode, column mapping, error handling
SQL QueryBuilding audiences and reshaping dataData action, primary keys, duplicates, run time
Script (SSJS)Logic SQL cannot express, API callsError handling, logging, keeping scripts short
Data ExtractExporting tracking data or data extension contents to a fileDate ranges, file format, where the file goes next
VerificationStopping a run if counts look wrongThresholds agreed with the business
Marketing engineer scheduling overnight data jobs on a timeline at his desk in the evening

Design principles we apply

  1. One automation, one purpose. An automation that imports, transforms and sends is harder to rerun safely than three small ones with clear hand-offs.
  2. Staging before production data extensions. Imports land in staging tables, queries validate and dedupe them, and only then is the target refreshed.
  3. Steps express dependencies. Activities that depend on each other sit in sequential steps; independent activities can share a step.
  4. Fail loudly. Error notification addresses point to a monitored shared mailbox, and verification activities stop a run when record counts are outside expected bounds.
  5. Write it down. Every automation has an owner, a description and an entry in a simple dependency register.

Run-time limits and concurrency behaviour vary, so we check current limits for your account in Salesforce Help rather than relying on remembered numbers. Where heavy SQL is involved, our Marketing Cloud SQL development service goes deeper.

How we work on your automations

  1. 01

    Inventory

    We list every automation with its schedule, activities, targets, last run status and owner, and build a dependency map.

  2. 02

    Triage

    Failing, overlapping or risky automations are ranked by business impact so the urgent ones are fixed first.

  3. 03

    Redesign

    For each priority we write a short design: inputs, outputs, steps, data actions, alerts and rerun instructions.

  4. 04

    Build and test

    Changes are built against test data extensions, compared with existing outputs and checked for duplicates and missing rows.

  5. 05

    Cut-over

    The new version replaces the old on an agreed date, with the old one paused rather than deleted until results are confirmed.

  6. 06

    Document

    The dependency register, runbook and alert routing are handed to your team.

Workflow diagram

Explore how the pieces fit together. Select a step to read what happens there.

Workflow

Automation Studio data pipeline

Open full size
Automation Studio data pipelineA source system drops a file on the Marketing Cloud SFTP, which starts a file drop automation. The file is imported to a staging data extension, SQL query activities build a sendable target, and a verification step checks the row count before the send. If the check fails the run stops, the team is notified, and the run is restarted after a fix.Automation Studio data pipelineSource systemAutomationStudioData extensionsOps teamYesNoRe-run after fixFile exported: CSV to Marketing Cloud SFTP. The source system exports a delimited file to the Marketing Cloud SFTP on a fixed schedule. Agree the file name, columns, encoding and delimiter in writing.File exportedCSV to MarketingCloud SFTPRun triggered: File drop, name pattern. A file drop automation starts when a matching file arrives, rather than at a guessed time, so a late file does not cause an empty or stale run.Run triggeredFile drop, namepatternImport activity: Into a staging DE. The import loads the file into a staging data extension. Type and length mismatches show up here as row errors, before any query runs.Import activityInto a staging DEStaging DE: Raw rows, replaced each run. Keeping raw rows apart from the sendable table means a bad file can be inspected and re-imported without touching live audiences.Staging DERaw rows, replacedeach runSQL query activities: Clean, dedupe, join. Query activities standardise values, remove duplicates with ROW_NUMBER and join reference data. Each query writes to its target with a deliberate update type.SQL queryactivitiesClean, dedupe, joinTarget DE: Sendable, keyed on contact. The final table has a primary key, a send relationship to Subscriber Key and only the fields the send needs, plus a data retention policy.Target DESendable, keyed oncontactRow count in range?. A verification activity checks the target row count against agreed limits and stops the automation if the data looks wrong, before anything is sent.Row count inrange?Send activity: Emails target DE. The send activity mails the verified audience. Because it runs last, a failed import or query never reaches subscribers.Send activityEmails target DERun complete: History recorded. The run finishes and tracking starts to arrive. Activity history shows the timing and outcome of each step for later checks.Run completeHistory recordedRun stopped: Error email to team. The verification stop and the automation error notification tell the team straight away, rather than someone noticing a missing send days later.Run stoppedError email to teamFix the cause: File or query, then check. The team corrects the source file or the query and checks the staging data. Overwrite imports and queries make the restart safe to repeat.Fix the causeFile or query, thencheckStartProcess stepDataDecisionChannel or touchpointEnd or outcomeAlert or exceptionPerson or teamOriginal GeekMidst explanatory diagram, not an official Salesforce reference.

Select any step in the diagram, or use Step through, to see what happens at that point and why.

Text version: every step in order
  1. File exported Source systemCSV to Marketing Cloud SFTP

    The source system exports a delimited file to the Marketing Cloud SFTP on a fixed schedule. Agree the file name, columns, encoding and delimiter in writing.

    Next: Run triggered

  2. Run triggered Automation StudioFile drop, name pattern

    A file drop automation starts when a matching file arrives, rather than at a guessed time, so a late file does not cause an empty or stale run.

    Next: Import activity

  3. Import activity Automation StudioInto a staging DE

    The import loads the file into a staging data extension. Type and length mismatches show up here as row errors, before any query runs.

    Next: Staging DE

  4. Staging DE Data extensionsRaw rows, replaced each run

    Keeping raw rows apart from the sendable table means a bad file can be inspected and re-imported without touching live audiences.

    Next: SQL query activities

  5. SQL query activities Automation StudioClean, dedupe, join

    Query activities standardise values, remove duplicates with ROW_NUMBER and join reference data. Each query writes to its target with a deliberate update type.

    Next: Target DE

  6. Target DE Data extensionsSendable, keyed on contact

    The final table has a primary key, a send relationship to Subscriber Key and only the fields the send needs, plus a data retention policy.

    Next: Row count in range?

  7. Row count in range? Automation Studio

    A verification activity checks the target row count against agreed limits and stops the automation if the data looks wrong, before anything is sent.

    Next: Yes: Send activity; No: Run stopped

  8. Send activity Automation StudioEmails target DE

    The send activity mails the verified audience. Because it runs last, a failed import or query never reaches subscribers.

    Next: Run complete

  9. Run complete Automation StudioHistory recorded

    The run finishes and tracking starts to arrive. Activity history shows the timing and outcome of each step for later checks.

    End of the flow.

  10. Run stopped Ops teamError email to team

    The verification stop and the automation error notification tell the team straight away, rather than someone noticing a missing send days later.

    Next: Fix the cause

  11. Fix the cause Ops teamFile or query, then check

    The team corrects the source file or the query and checks the staging data. Overwrite imports and queries make the restart safe to repeat.

    Next: Re-run after fix: Run triggered

A source system drops a file on the Marketing Cloud SFTP, which starts a file drop automation. The file is imported to a staging data extension, SQL query activities build a sendable target, and a verification step checks the row count before the send. If the check fails the run stops, the team is notified, and the run is restarted after a fix. Original GeekMidst explanatory diagram; not an official Salesforce reference architecture.

Questions we hear

Can you take over automations built by another agency?
Yes. The inventory and dependency map are designed for exactly that, so we understand what exists before changing anything.
What do you need to get started?
Access to Automation Studio and the related data extensions, plus SFTP details if file transfers are involved. A limited review role is often enough for the inventory.
Do you use SSJS in Script Activities?
Where SQL cannot do the job, for example calling an API or handling complex looping, yes. We keep scripts short, commented and logged.
How do we know an automation failed?
We route error notifications to a monitored mailbox and add verification activities so wrong-looking data stops the run instead of reaching a send.
Can automations read engagement data?
Yes, SQL queries can read system data views such as sends, opens and bounces. See our guide to using data views for engagement reporting.

Make your automations dependable

Tell us which automations fail or confuse your team, and we will propose a review and repair plan.

Request a technical assessment →

Part of our Marketing Cloud work: Salesforce Marketing Cloud Consulting

Next step: Automation Studio Services

  1. We read it A practitioner reads your message and replies by email.
  2. Short call We confirm goals, constraints and what success looks like.
  3. Written scope You get a scope and estimate before any work starts.

Tell us what you are trying to fix.

Share your Salesforce setup, goals and constraints. A practitioner reads every message and replies with questions, first thoughts or an honest view of whether you need outside help at all.

Talk to a Salesforce Expert
Book a Consultation