Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Career Development

How to Build a Freelance Data Automation Service Using Excel Macros and Power Query: Packaging Repetitive Client Workflows into a Recurring Monthly Offer

Most freelancers sell time. This lesson shows you how to build an Excel and Power Query automation service that earns recurring revenue from work you've already done. Learn to identify, build, package, and price workflow automations as a monthly retainer offer.

⚡ Practitioner24 min readNov 15, 2026Updated Nov 15, 2026
How to Build a Freelance Data Automation Service Using Excel Macros and Power Query: Packaging Repetitive Client Workflows into a Recurring Monthly Offer
On this page
  • Introduction
  • Prerequisites
  • Step 1: Identifying Automation-Ready Workflows in Client Conversations
  • Step 2: Mapping the Workflow Before Writing a Single Line of Code
  • Step 3: Building the Power Query Pipeline
  • Connecting to the Input Files
  • Merging and Calculating Margin
  • Step 4: Writing the VBA Automation Layer
  • The Master Refresh Macro
  • Refreshing Power Query Connections Cleanly
  • Flagging Dropped Accounts with Conditional Formatting
  • Saving a Clean Output Copy
  • Step 5: Building the User Interface the Client Actually Sees
  • Step 6: Documentation That Becomes Your Maintenance Justification
  • Step 7: Packaging the Service for Monthly Recurring Revenue
  • The Three-Component Offer
  • Step 8: Scoping What's In and Out of Retainer Scope
  • Step 9: Pricing the Full Package
  • Step 10: The Handover That Locks In the Retainer
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Mistake 1: Hardcoding File Paths
  • Mistake 2: Not Handling Missing or Malformed Input Files Gracefully
  • Mistake 3: Running Formatting Before Refresh Completes
  • Mistake 4: Delivering the Master File to the Client
  • Mistake 5: Underscoping the Retainer
  • Mistake 6: Never Increasing the Retainer
  • Summary & Next Steps
  • How to Build a Freelance Data Automation Service Using Excel Macros and Power Query: Packaging Repetitive Client Workflows into a Recurring Monthly Offer

    Introduction

    Picture this: a small logistics company has an operations manager who spends every Monday morning doing the same thing. She downloads three separate CSVs from their transport management system, copies the data into a master Excel file, manually calculates route efficiency percentages, reformats supplier names to match their internal codes, and builds a summary table for the weekly ops meeting. The whole process takes about three hours. She's been doing it every week for two years.

    That's 312 hours — roughly eight full work weeks — spent on a process that you could automate in a single afternoon.

    This is the heartbeat of a data automation freelance service. It's not glamorous. It's not building a machine learning model or architecting a data warehouse. But it's deeply valuable, it's everywhere, and because it recurs every week or month, it becomes the foundation of a recurring revenue model that most project-based freelancers never figure out how to build. When you package this kind of automation properly — with clean deliverables, a maintenance commitment, and a sensible pricing structure — you stop trading hours for dollars and start earning money on work you've already done.

    By the end of this lesson, you'll know exactly how to identify these opportunities, build the automation using Excel macros and Power Query, package it as a productized service, and price it for recurring monthly revenue.

    What you'll learn:

    • How to identify and scope automation opportunities in client workflows
    • How to build a Power Query pipeline that refreshes with a single click
    • How to write and structure VBA macros for formatting, distribution, and error handling
    • How to package the automation as a deliverable with documentation and a maintenance retainer
    • How to price, sell, and renew a monthly automation service offer

    Prerequisites

    You should be comfortable with:

    • Excel fundamentals — pivot tables, named ranges, structured table references
    • Basic Power Query — connecting to files, applying transformations, merging queries
    • Some exposure to VBA (you don't need to be an expert; we'll cover what you need)
    • A general sense of how freelance client engagements work

    If you're just starting to think about the business side of freelancing, Starting a Data Freelancing Business: Essential Tools, Pricing Strategies, and Landing Your First Clients provides a solid foundation to build from before diving into service packaging.


    Step 1: Identifying Automation-Ready Workflows in Client Conversations

    The highest-value thing you can do at the start of any client relationship is listen for pain disguised as routine. When a client says "we do this every week," your ears should perk up. When they say "it's annoying but it's just how it is," that's your opening.

    The sweet spot for Excel/Power Query automation services is a workflow that meets three criteria:

    1. It's repetitive — the same steps happen on a predictable schedule (weekly, monthly, quarterly)
    2. It involves data from external sources — files dropped from another system, email attachments, database exports
    3. The output is consistent — a report, a summary email, a formatted table that feeds another process

    You're not looking for one-off data cleaning jobs. You're looking for processes — things the client will need to do again next month regardless of whether you're involved.

    Tip

    During discovery conversations, ask "Walk me through your Monday morning routine" or "What's the first thing you do when you get the sales data at the end of the month?" These prompts surface repeating workflows far better than "do you have any automation needs?"

    Here's a quick diagnostic framework. During a discovery call, listen for these trigger phrases:

    What they say What it signals
    "We download this every week from [system]" Repeatable data extraction
    "Then we copy it into our master file" Manual consolidation step
    "We have to rename things because..." Data normalization need
    "Then I build a table for the meeting" Report generation opportunity
    "It takes a few hours but it is what it is" High-value automation target

    Once you've identified the workflow, your next move is to map it. Ask the client to send you a sample of the raw input files and a sample of the finished output. That gap between input and output is where you live.


    Step 2: Mapping the Workflow Before Writing a Single Line of Code

    Before you open Excel, you need a workflow map. This sounds like overhead, but it does two things: it prevents you from building the wrong thing, and it becomes your documentation later.

    Let's work through a realistic example throughout this lesson. Our client is a mid-size wholesale distributor. Every month, their sales coordinator receives:

    • A CSV of orders from their ERP system (orders_MMYYYY.csv)
    • An Excel file of customer account data (customer_accounts.xlsx)
    • A CSV of product pricing (pricing_current.csv)

    She manually combines these into a monthly sales performance report that shows:

    • Sales by account with current vs. prior month comparison
    • Gross margin by product category
    • A flagged list of accounts where orders dropped more than 20% month-over-month
    • A formatted summary emailed to three regional managers

    She spends 4-5 hours on this every month. She's been doing it for 18 months.

    Your workflow map looks like this:

    INPUT FILES
    ├── orders_MMYYYY.csv        → Raw transaction records
    ├── customer_accounts.xlsx   → Account tiers, regions, rep assignments
    └── pricing_current.csv      → Cost and list prices by SKU
    
    TRANSFORMATION STEPS
    ├── 1. Load and clean orders data
    │   ├── Parse order date, extract month/year
    │   ├── Standardize product codes (remove legacy prefixes)
    │   └── Filter out cancelled orders (Status = "VOID")
    ├── 2. Load customer accounts
    │   └── Merge account tier and region onto orders
    ├── 3. Load pricing
    │   └── Join cost price to calculate gross margin per line
    ├── 4. Build summary tables
    │   ├── Sales by account (current month)
    │   ├── Prior month comparison
    │   ├── Margin by category
    │   └── Flag accounts with >20% drop
    └── 5. Format and distribute
        ├── Apply company formatting
        ├── Add conditional formatting to flagged rows
        └── Save formatted output for email
    
    OUTPUT
    └── Monthly_Sales_Report_MMYYYY.xlsx (ready to send)
    

    This map tells you exactly what Power Query handles (steps 1-4) and what a VBA macro handles (step 5). The division of responsibility matters: Power Query is your data pipeline; VBA is your automation glue.

    Note

    Some freelancers try to do everything in VBA. Resist this urge. Power Query is vastly more maintainable, debuggable, and client-friendly than VBA-based data transformations. Use VBA for what it's genuinely good at: triggering refreshes, formatting outputs, saving files, and sending emails.


    Step 3: Building the Power Query Pipeline

    Open a new Excel workbook. This will become your automation workbook — the file the client uses every month. Set up a dedicated folder structure on the client's machine (or a shared drive) like this:

    /Monthly_Sales_Automation/
    ├── /inputs/          ← Client drops fresh files here each month
    ├── /outputs/         ← Completed reports land here
    ├── /archive/         ← Previous months' inputs
    └── SalesReport_MASTER.xlsm   ← Your automation workbook
    

    Connecting to the Input Files

    In Power Query, connect to each input file using a folder-relative path. This is critical — if you hardcode C:\Users\Sarah\Desktop\orders.csv, the query breaks the moment Sarah gets a new laptop.

    Go to Data → Get Data → From File → From Text/CSV. When Power Query opens, go into Advanced Editor and modify the Source step to use a relative or parameterized path.

    The cleanest approach for client deployments is to use a named cell in your workbook as the base path. Create a named range called BasePath on a "Settings" sheet (something like C:\Monthly_Sales_Automation\inputs\). Then in Power Query, reference it via a parameter:

    // In Power Query: "fnGetBasePath" function query
    let
        BasePath = Excel.CurrentWorkbook(){[Name="BasePath"]}[Content]{0}[Column1]
    in
        BasePath
    

    Now your orders query looks like this:

    let
        BasePath = fnGetBasePath(),
        FilePath = BasePath & "orders_" & Date.ToText(Date.From(DateTime.LocalNow()), "MMyyyy") & ".csv",
        Source = Csv.Document(
            File.Contents(FilePath),
            [Delimiter=",", Columns=12, Encoding=65001, QuoteStyle=QuoteStyle.None]
        ),
        PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        
        // Standardize column types
        TypedTable = Table.TransformColumnTypes(PromotedHeaders, {
            {"OrderDate", type date},
            {"OrderID", type text},
            {"CustomerCode", type text},
            {"ProductCode", type text},
            {"Quantity", Int64.Type},
            {"UnitPrice", type number},
            {"Status", type text}
        }),
        
        // Remove voided orders
        FilteredOrders = Table.SelectRows(TypedTable, each [Status] <> "VOID"),
        
        // Standardize product codes (remove legacy "LGC-" prefix)
        CleanProductCodes = Table.TransformColumns(FilteredOrders, {
            {"ProductCode", each Text.Replace(_, "LGC-", ""), type text}
        }),
        
        // Extract month/year for grouping
        AddedMonthYear = Table.AddColumn(CleanProductCodes, "ReportMonth", 
            each Date.ToText([OrderDate], "yyyy-MM"), type text)
        
    in
        AddedMonthYear
    

    Warning

    The filename pattern orders_MMyyyy.csv only works if your client's ERP system generates consistent filenames. If the filename format varies, build a step that lists all CSV files in the input folder and filters to the most recent one using Folder.Files(). Never assume consistent filenames — always verify with the client first.

    Merging and Calculating Margin

    Once all three source queries are built, create a final SalesData query that merges them:

    let
        Orders = OrdersClean,
        
        // Merge customer accounts
        MergedAccounts = Table.NestedJoin(
            Orders, {"CustomerCode"},
            CustomerAccounts, {"AccountCode"},
            "AccountDetail", JoinKind.LeftOuter
        ),
        ExpandedAccounts = Table.ExpandTableColumn(
            MergedAccounts, "AccountDetail",
            {"AccountName", "Region", "AccountTier", "SalesRep"},
            {"AccountName", "Region", "AccountTier", "SalesRep"}
        ),
        
        // Merge pricing
        MergedPricing = Table.NestedJoin(
            ExpandedAccounts, {"ProductCode"},
            ProductPricing, {"SKU"},
            "PricingDetail", JoinKind.LeftOuter
        ),
        ExpandedPricing = Table.ExpandTableColumn(
            MergedPricing, "PricingDetail",
            {"CostPrice", "Category"},
            {"CostPrice", "Category"}
        ),
        
        // Calculate line-level gross margin
        AddedMargin = Table.AddColumn(ExpandedPricing, "GrossMargin",
            each ([UnitPrice] - [CostPrice]) * [Quantity], type number),
        
        AddedMarginPct = Table.AddColumn(AddedMargin, "MarginPct",
            each if [UnitPrice] * [Quantity] = 0 then null
                 else ([UnitPrice] - [CostPrice]) / [UnitPrice], type number)
    
    in
        AddedMarginPct
    

    Load each of your summary tables (Sales by Account, Margin by Category, MoM comparison) as separate queries that reference SalesData as their source. This way, refreshing the workbook re-runs the entire pipeline from raw inputs to formatted summaries in one shot.


    Step 4: Writing the VBA Automation Layer

    Power Query handles the data. VBA handles the ceremony — the formatting, file-saving, and distribution that turns a refreshed data model into a finished deliverable. The macro the client ultimately runs is a single button click.

    The Master Refresh Macro

    In the VBA editor (Alt+F11), insert a new module and write your master procedure:

    Sub RunMonthlySalesReport()
    
        Dim ws As Worksheet
        Dim reportMonth As String
        Dim outputPath As String
        Dim outputFile As String
        
        ' Grab settings from the Settings sheet
        reportMonth = Format(Date, "MMyyyy")
        outputPath = ThisWorkbook.Sheets("Settings").Range("OutputPath").Value
        outputFile = outputPath & "Monthly_Sales_Report_" & reportMonth & ".xlsx"
        
        ' Step 1: Refresh all Power Query connections
        Application.StatusBar = "Refreshing data connections..."
        Call RefreshAllQueries
        
        ' Step 2: Format the output sheets
        Application.StatusBar = "Applying formatting..."
        Call FormatSalesReport
        
        ' Step 3: Flag underperforming accounts
        Application.StatusBar = "Flagging accounts..."
        Call FlagDroppedAccounts
        
        ' Step 4: Save output file
        Application.StatusBar = "Saving report..."
        Call SaveReportOutput(outputFile)
        
        ' Step 5: Done
        Application.StatusBar = False
        MsgBox "Monthly Sales Report complete!" & vbCrLf & _
               "File saved to: " & outputFile, vbInformation, "Report Complete"
    
    End Sub
    

    Refreshing Power Query Connections Cleanly

    Sub RefreshAllQueries()
    
        Dim conn As WorkbookConnection
        Dim startTime As Double
        Dim timeoutSeconds As Integer
        
        timeoutSeconds = 120 ' 2-minute timeout
        
        ' Refresh all Power Query connections synchronously
        For Each conn In ThisWorkbook.Connections
            If conn.Type = xlConnectionTypeOLEDB Or _
               conn.Type = xlConnectionTypePowerQuery Then
                conn.Refresh
            End If
        Next conn
        
        ' Wait for background refresh to complete
        startTime = Timer
        Do While ThisWorkbook.Queries.Count > 0 And _
                 (Timer - startTime) < timeoutSeconds
            DoEvents
            ' Check if any connection is still refreshing
            Dim stillRefreshing As Boolean
            stillRefreshing = False
            For Each conn In ThisWorkbook.Connections
                If conn.OLEDBConnection.Refreshing Then
                    stillRefreshing = True
                    Exit For
                End If
            Next conn
            If Not stillRefreshing Then Exit Do
        Loop
    
    End Sub
    

    Tip

    Set BackgroundQuery = False on each connection before triggering the refresh. This forces Power Query to complete before the next VBA line executes, which prevents race conditions where your formatting macro runs before the data is loaded.

    Flagging Dropped Accounts with Conditional Formatting

    Sub FlagDroppedAccounts()
    
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim flagRange As Range
        Dim dropThreshold As Double
        
        Set ws = ThisWorkbook.Sheets("MoM Comparison")
        dropThreshold = -0.2 ' Flag accounts with >20% drop
        
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        
        ' Clear existing conditional formatting on this range
        ws.Range("A2:H" & lastRow).FormatConditions.Delete
        
        ' The MoM Change % is in column G (adjust to your layout)
        Set flagRange = ws.Range("A2:H" & lastRow)
        
        With flagRange.FormatConditions.Add(Type:=xlExpression, _
             Formula1:="=$G2<" & dropThreshold)
            .Interior.Color = RGB(255, 199, 206)  ' Light red fill
            .Font.Color = RGB(156, 0, 6)           ' Dark red text
            .Font.Bold = True
        End With
        
        ' Add a "REVIEW" flag in column I for filtered exports
        Dim i As Long
        For i = 2 To lastRow
            If ws.Cells(i, 7).Value < dropThreshold Then
                ws.Cells(i, 9).Value = "REVIEW"
            Else
                ws.Cells(i, 9).Value = ""
            End If
        Next i
    
    End Sub
    

    Saving a Clean Output Copy

    Sub SaveReportOutput(outputFile As String)
    
        Dim outputWB As Workbook
        
        ' Copy only the output sheets to a new workbook
        ' (don't send clients the raw data or Settings sheet)
        Dim sheetsToExport As Variant
        sheetsToExport = Array("Sales by Account", "Margin by Category", _
                               "MoM Comparison", "Executive Summary")
        
        ' Copy sheets to a new workbook
        ThisWorkbook.Sheets(sheetsToExport).Copy
        
        Set outputWB = ActiveWorkbook
        
        ' Remove any lingering connections from the copy
        Dim conn As WorkbookConnection
        For Each conn In outputWB.Connections
            conn.Delete
        Next conn
        
        ' Save as xlsx (not xlsm - no macros in the output file)
        Application.DisplayAlerts = False
        outputWB.SaveAs Filename:=outputFile, _
                        FileFormat:=xlOpenXMLWorkbook
        outputWB.Close SaveChanges:=False
        Application.DisplayAlerts = True
    
    End Sub
    

    The output file is a clean .xlsx with no connections, no macros, and no backend complexity. The client can open it, forward it, print it — it just works. The .xlsm master file is yours.


    Step 5: Building the User Interface the Client Actually Sees

    Your technical solution needs to be invisible to the client. What they see is a simple, non-threatening interface. Create a "Control Panel" sheet in your workbook with:

    • A large, clearly labeled button: "Generate Monthly Report" (assigned to RunMonthlySalesReport)
    • A "Settings" section with the two editable cells: BasePath and OutputPath
    • A "Last Run" timestamp that your macro updates on completion
    • Brief instructions in plain English: "1. Drop this month's files in the inputs folder. 2. Click the button. 3. Find your report in the outputs folder."

    Hide all other sheets from the client's view using xlSheetVeryHidden via VBA (not just the standard hide — that way they can't accidentally unhide them):

    Sub LockWorkbookView()
        Dim ws As Worksheet
        Dim visibleSheets As Variant
        visibleSheets = Array("Control Panel")
        
        For Each ws In ThisWorkbook.Sheets
            If Not IsInArray(ws.Name, visibleSheets) Then
                ws.Visible = xlSheetVeryHidden
            End If
        Next ws
    End Sub
    

    Key insight

    The cleaner your user interface, the more professional your service feels, and the less likely the client is to tinker with the internals. A button on a clean sheet feels like software. A complicated workbook with 12 visible tabs feels like a liability.

    Protect the VBA project with a password (Tools → VBAProject Properties → Protection). This isn't about being secretive — it's about protecting the client from accidentally breaking something and protecting you from unexpected support requests at 11pm.


    Step 6: Documentation That Becomes Your Maintenance Justification

    Here's the part most freelancers skip, and it's also the part that justifies your recurring fee: documentation.

    Deliver a one-page "Automation Guide" with every implementation. It doesn't need to be elaborate. It needs to answer four questions:

    1. What does this automation do? (plain English, one paragraph)
    2. What do I do each month? (numbered steps, no jargon)
    3. What could go wrong and what should I do? (3-5 common error scenarios)
    4. Who do I call if something breaks? (your contact details and response SLA)

    Example error table:

    Error Message Likely Cause What to Do
    "File not found: orders_112024.csv" Input file not dropped in /inputs/ folder, or filename doesn't match expected format Check the filename matches orders_MMYYYY.csv exactly, then rerun
    "Query refresh timed out" Input file is unusually large or network is slow Try running again; if it persists, contact [you]
    "Could not load customer_accounts.xlsx" Customer accounts file has been renamed or moved Confirm the file is in the /inputs/ folder with correct name
    Blank output file All rows filtered out — possibly wrong month's data dropped Verify the input files are for the correct reporting period

    This document does something subtle but powerful: it frames you as an ongoing service provider, not a one-time contractor. The existence of an SLA and a contact number for "when something breaks" plants the expectation of a continued relationship.

    This connects directly to the broader principle of building recurring revenue with data retainer clients — you're not just delivering a file, you're delivering operational continuity.


    Step 7: Packaging the Service for Monthly Recurring Revenue

    This is where you shift from "technical contractor" to "service provider." The automation workbook is an asset. The monthly retainer is what you charge to maintain, update, and support that asset.

    The Three-Component Offer

    Structure your offer in three parts:

    Part 1: The Build Fee (one-time) This covers discovery, mapping the workflow, building the Power Query pipeline and VBA macros, testing, documentation, and handover. Price this based on complexity. For the wholesale distributor example above, a realistic build fee is £800–£1,500 depending on your market and experience level.

    Part 2: The Monthly Retainer This covers:

    • Running the monthly refresh on the client's behalf (or being on-call when they run it)
    • One round of updates per month as business requirements change
    • Priority support if something breaks
    • Quarterly review of the automation to ensure it's still fit for purpose

    Price this at 15–25% of the build fee per month. For a £1,200 build, that's £180–£300/month. At £240/month, the client pays back the build fee value within five months — and you're earning money every month for maintaining something you built once.

    Part 3: Expansion Projects (optional add-ons) Once the client trusts your work, they'll naturally bring you new workflows. Each new automation module can be scoped and priced as an additional build, potentially increasing their retainer when you add it to your monthly support scope.

    Key insight

    The build fee covers your time to create the asset. The retainer covers the ongoing value the asset delivers, not your time to maintain it. This distinction matters enormously for how you frame the conversation. If the automation saves the client 5 hours a month at £60/hour of their own time, they're saving £300/month in productivity — your £240/month retainer is a clear net win.

    If you're new to structuring this kind of offer, Building Productized Services with Power BI and Excel: From Custom Consultant to Product Owner covers the productization mindset in depth, including how to scope what's included and what isn't.


    Step 8: Scoping What's In and Out of Retainer Scope

    The monthly retainer needs clear boundaries or it becomes unlimited free support. Define this in writing before you start.

    Included in monthly retainer:

    • Running or supervising the monthly automation run
    • Minor query adjustments (column name changes, new filter values)
    • Bug fixes caused by input file format changes
    • Email support with 24-hour response time
    • Quarterly check-in call (30 minutes)

    Not included (quoted separately):

    • Adding new data sources to the pipeline
    • Building new output sheets or report types
    • Changes to the core logic (new KPIs, different aggregation methods)
    • Training additional staff members
    • Migrating to a different platform (e.g., moving to Power BI)

    This isn't about being rigid — it's about being sustainable. If you don't define scope, the retainer quietly expands into a part-time job. For detailed guidance on managing this dynamic, Client Management: Scope, Communication, and Revisions for Data Freelancers covers exactly how to frame and enforce these conversations professionally.


    Step 9: Pricing the Full Package

    Pricing this service correctly is a function of three inputs: the value delivered, the market rate for similar work, and your positioning.

    For a workflow that saves 4–5 hours per month, the value case is easy to make. Even a conservative internal cost of £50/hour for the sales coordinator's time means the automation is worth £200–£250/month in direct productivity savings. That's before you account for consistency, reduced errors, and the time the report now frees up for analysis rather than data wrangling.

    A sensible pricing structure for this type of service:

    Component Price Range Notes
    Build fee (simple, 1-2 sources) £600 – £900 Under 8 hours of work
    Build fee (medium, 3-4 sources) £1,000 – £1,800 The wholesale distributor example
    Build fee (complex, 5+ sources or complex logic) £2,000 – £4,000+ Multiple systems, complex business rules
    Monthly retainer 15–25% of build fee Minimum £150/month

    Don't undercharge the build fee to get the retainer. The build fee validates that your work has real value. If you give it away, you train the client to see you as cheap, which makes the retainer harder to justify and harder to increase later.

    For a deeper treatment of how to structure pricing across your freelance practice, Pricing Data Projects: Hourly vs Fixed vs Value-Based - A Complete Guide for Data Professionals is the companion read to this lesson.


    Step 10: The Handover That Locks In the Retainer

    How you hand over the automation determines whether the client sees it as a one-time purchase or an ongoing service. Most freelancers send over a file and a Loom recording. That's fine, but it positions you as done.

    Instead, do a live handover session (60 minutes, video call). Walk the client through:

    1. The folder structure and where files go
    2. Running the automation together — they click the button, you narrate what's happening
    3. Reviewing the output together — explaining each tab
    4. What they should do if something looks wrong (reference the documentation)
    5. Your support process and what the retainer covers

    At the end of this call, ask: "Would you like me to handle the first few months' runs with you to make sure everything's working smoothly?" Almost every client says yes. That "yes" is the retainer starting. Document it in a simple email confirmation and invoice for month one.

    Tip

    Offer to run the first month's report yourself — meaning you do the refresh, QA the output, and deliver the finished file. This removes all anxiety from the client's side and demonstrates the ongoing value of your retainer immediately. You can transition them to running it themselves at month three or four once trust is established.


    Hands-On Exercise

    Build a minimal version of the wholesale distributor automation using sample data. Here's what to build:

    Setup: Create three sample input files (you can generate them with simple Excel formulas or use any sales dataset):

    • orders_[currentmonth].csv — 500 rows, columns: OrderID, CustomerCode, ProductCode, Quantity, UnitPrice, OrderDate, Status
    • customer_accounts.xlsx — 50 rows: AccountCode, AccountName, Region, AccountTier
    • pricing_current.csv — 30 rows: SKU, CostPrice, Category

    Build targets:

    1. Create the folder structure (/inputs/, /outputs/, /archive/)
    2. Set up a Settings sheet with BasePath and OutputPath named ranges
    3. Build the fnGetBasePath function query in Power Query
    4. Build the three source queries using parameterized file paths
    5. Build the SalesData merge query with margin calculations
    6. Add a summary table: total sales and average margin by Region
    7. Write the RunMonthlySalesReport macro that refreshes queries and saves the output file
    8. Add a "Control Panel" sheet with one button and step-by-step instructions
    9. Write a one-page Automation Guide document

    Stretch goal: Add a step that archives the input files to the /archive/[MMYYYY]/ subfolder after the report is generated, so the /inputs/ folder is always clean for next month.


    Common Mistakes & Troubleshooting

    Mistake 1: Hardcoding File Paths

    Symptom: The automation works on your machine, fails on the client's. Fix: Always use parameterized paths via a Settings sheet named range, or use relative paths. Test by running the workbook from a different user account or folder location before handover.

    Mistake 2: Not Handling Missing or Malformed Input Files Gracefully

    Symptom: When a column name changes slightly (e.g., the ERP updates CustomerCode to Customer_Code), the entire pipeline crashes with an obscure error. Fix: Add a validation step at the top of each source query that checks expected column names exist. Surface friendly error messages rather than technical Power Query errors:

    let
        Source = Csv.Document(...),
        Headers = Table.PromoteHeaders(Source),
        
        // Validate required columns exist
        RequiredColumns = {"OrderID", "CustomerCode", "ProductCode", "Quantity", "UnitPrice", "OrderDate", "Status"},
        MissingColumns = List.Select(RequiredColumns, each not List.Contains(Table.ColumnNames(Headers), _)),
        
        ValidationCheck = if List.Count(MissingColumns) > 0 
                          then error "Missing columns: " & Text.Combine(MissingColumns, ", ")
                          else Headers
    in
        ValidationCheck
    

    Mistake 3: Running Formatting Before Refresh Completes

    Symptom: Report generates but shows last month's data in some tables. Fix: Set BackgroundQuery = False on all connections before refreshing, or use ActiveWorkbook.RefreshAll followed by Application.CalculateFullRebuild and a DoEvents loop check.

    Mistake 4: Delivering the Master File to the Client

    Symptom: Client accidentally breaks queries while exploring, then blames you. Fix: The master .xlsm with Power Query and macros never leaves your control if possible. Alternatively, password-protect the VBA project and use xlSheetVeryHidden on all sheets except the Control Panel. The output file they receive is always a clean .xlsx copy.

    Mistake 5: Underscoping the Retainer

    Symptom: Client emails you every week with "just a quick question" and gradually the retainer becomes unlimited consulting. Fix: Define scope in writing at handover. When an out-of-scope request comes in, acknowledge it warmly and quote it separately: "Happy to add that — it's outside the monthly scope but I can get it done for £X. Want me to send a quote?" Client management practices for data freelancers covers how to handle these conversations without damaging the relationship.

    Mistake 6: Never Increasing the Retainer

    Symptom: Twelve months in, you're doing twice the work for the same price. Fix: Build an annual review into your retainer agreement. After 12 months of demonstrated value, a 10–15% rate increase is entirely reasonable. The client has sunk cost in the relationship and switching cost is high — this works in your favour.


    Summary & Next Steps

    You've now got the full playbook: how to find automation opportunities in client workflows, how to build a Power Query pipeline with parameterized paths and proper error handling, how to write the VBA layer that turns a refreshed data model into a finished deliverable, and — critically — how to package, price, and maintain the whole thing as a recurring monthly service.

    The business logic here is clean. You build an asset once. You charge a one-time build fee that covers your creation time. You then earn a monthly retainer for maintaining and supporting that asset — work that takes a fraction of your original build time. Scale this across three or four clients and you have a meaningful, predictable monthly income that doesn't require you to constantly find new work.

    Where to go from here:

    • If you want to evolve from automation services toward a broader productized practice, Building Productized Services with Power BI and Excel: From Custom Consultant to Product Owner is the natural next step.
    • If you want to find clients who need this kind of work, Finding and Winning Data Freelance Projects on Upwork and LinkedIn covers how to position and pitch automation services specifically.
    • If you're thinking about how to scale beyond what you can deliver solo, Scaling Your Freelance Data Business: Subcontracting and Automation shows how to bring in help without losing quality.

    The operations manager who spends every Monday morning wrestling with spreadsheets is out there right now, in hundreds of companies, waiting for someone to show up and solve her problem. Build the solution, package it well, and she'll happily pay you every month to keep it running.

    Work With Us

    From insight to implementation

    Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

    Let's Build

    Freelancing with Data Skills

    Previous

    How to Create a Freelance Data Services Rate Card: The One-Page Pricing Document That Answers "What Do You Charge?" Before the Discovery Call

    Related Insights

    Career DevelopmentFoundation

    How to Find and Use Real-World Datasets to Build a Data Portfolio When You Have No Work Experience

    17 min
    Career DevelopmentFoundation

    How to Create a Freelance Data Services Rate Card: The One-Page Pricing Document That Answers "What Do You Charge?" Before the Discovery Call

    18 min
    Career DevelopmentExpert

    How to Negotiate Your First Data Analyst Job Title When the Role, Responsibilities, and Compensation Don't Align

    32 min

    On this page

    • Introduction
    • Prerequisites
    • Step 1: Identifying Automation-Ready Workflows in Client Conversations
    • Step 2: Mapping the Workflow Before Writing a Single Line of Code
    • Step 3: Building the Power Query Pipeline
    • Connecting to the Input Files
    • Merging and Calculating Margin
    • Step 4: Writing the VBA Automation Layer
    • The Master Refresh Macro
    • Refreshing Power Query Connections Cleanly
    • Flagging Dropped Accounts with Conditional Formatting
    • Saving a Clean Output Copy
    • Step 5: Building the User Interface the Client Actually Sees
    • Step 6: Documentation That Becomes Your Maintenance Justification
    • Step 7: Packaging the Service for Monthly Recurring Revenue
    • The Three-Component Offer
    • Step 8: Scoping What's In and Out of Retainer Scope
    • Step 9: Pricing the Full Package
    • Step 10: The Handover That Locks In the Retainer
    • Hands-On Exercise
    • Common Mistakes & Troubleshooting
    • Mistake 1: Hardcoding File Paths
    • Mistake 2: Not Handling Missing or Malformed Input Files Gracefully
    • Mistake 3: Running Formatting Before Refresh Completes
    • Mistake 4: Delivering the Master File to the Client
    • Mistake 5: Underscoping the Retainer
    • Mistake 6: Never Increasing the Retainer
    • Summary & Next Steps