Skip to content
locomu.
Workflows

How to Build a Reusable UTM Link Template in Google Sheets or Airtable

A step‑by‑step guide that shows solo creators how to set up a single spreadsheet or Airtable base that automatically assembles clean, validated UTM URLs for any campaign.

Goal and What You’ll Get

This tutorial helps creators, freelancers, and small teams build a single, reusable template that turns raw campaign data into a complete, analytics‑ready URL. Once set up, you can add a new row for each campaign and instantly get a correctly formatted link that respects existing query strings, encodes special characters, and follows a consistent naming convention.

Prerequisites

  • A Google account with edit access to Google Sheets (free tier is sufficient).
  • An Airtable account (free tier works for the formula example).
  • Basic familiarity with spreadsheet formulas – CONCAT, IF, SUBSTITUTE, etc.
  • List of standard UTM parameters you want to capture (source, medium, campaign, term, content).

Step 1 – Define the Columns

  1. Column A: Destination URL – the page you are sending traffic to.
  2. Column B–F: UTM Source, Medium, Campaign, Term, Content – leave blank if a parameter does not apply.
  3. Column G: Full UTM URL – this will contain the assembled link.

This layout mirrors the widely‑used bulk‑UTM templates described by UTMTool and CampTag, which recommend one row per link and the same set of columns.[3][4]

Step 2 – Build the Base URL Formula (Google Sheets)

In the Full UTM URL cell (G2) paste the following formula and drag it down:

=IFERROR(
  IF(
    REGEXMATCH(A2, "[?&#]"),
    CONCAT(A2, "&", JOIN("&", FILTER({"utm_source="&B2,"utm_medium="&C2,"utm_campaign="&D2,"utm_term="&E2,"utm_content="&F2}, LEN({B2,C2,D2,E2,F2})))),
    CONCAT(A2, "?", JOIN("&", FILTER({"utm_source="&B2,"utm_medium="&C2,"utm_campaign="&D2,"utm_term="&E2,"utm_content="&F2}, LEN({B2,C2,D2,E2,F2}))))
  ),
  ""
)

This formula does three things:

  • Detects whether the destination already contains a query string (?) or a fragment (#) and chooses the correct separator (? or &).
  • Builds each utm_ pair only if the corresponding cell is not empty, preventing trailing ampersands.
  • Wraps the whole expression in IFERROR so a blank row returns an empty string.

The handling of existing queries and fragments follows the best‑practice logic outlined by UTMTool’s “final URL” formula.[3]

To keep naming consistent across a team, set Data → Data validation on the UTM columns:

  • Choose “List of items” and paste the approved source list (e.g., facebook,instagram,linkedin).
  • Enable “Show warning” or “Reject input” depending on how strict you want to be.

HowToAtlas’s UTM Builder template demonstrates the same approach, using dropdowns to enforce a shared vocabulary.[2]

Step 4 – Replicate the Logic in Airtable

Airtable does not have IFERROR or REGEXMATCH, but the same result can be achieved with the concatenation operator & and SUBSTITUTE. In a new field called Full UTM URL, use a formula similar to:

IF({Destination URL},
  SUBSTITUTE(
    {Destination URL} &
    IF(FIND("?", {Destination URL}), "&", "?" ) &
    IF({UTM Source}, "utm_source=" & {UTM Source} & "&", "") &
    IF({UTM Medium}, "utm_medium=" & {UTM Medium} & "&", "") &
    IF({UTM Campaign}, "utm_campaign=" & {UTM Campaign} & "&", "") &
    IF({UTM Term}, "utm_term=" & {UTM Term} & "&", "") &
    IF({UTM Content}, "utm_content=" & {UTM Content} & "&", ""),
    "&&", "&"
  ),
  ""
)

The core idea—joining fields with & and then cleaning up any double ampersands—is exactly what the Airtable community member bdelanghe described when recreating a Google‑Sheets UTM formula.[1]

Step 5 – Test the Template

  1. Enter a destination URL that already has a query string, such as https://example.com/page?ref=abc.
  2. Fill in at least one UTM field (e.g., Source = facebook).
  3. Verify that the Full UTM URL column adds the new parameters after the existing ? using an ampersand (&) and does not duplicate the question mark.
  4. Copy the generated link into a new browser tab and check that the URL loads correctly and that the parameters appear in the address bar.
  5. Repeat with a plain destination (no query) to confirm the formula inserts a ? as the first separator.

If the result is blank or contains stray “&&”, double‑check the formula syntax and ensure no extra spaces exist in the field names.

Troubleshooting Common Issues

  • Blank output: Most often caused by a typo in a field name inside the formula. Airtable shows the exact field name in the formula editor.
  • Double ampersands (&&): The SUBSTITUTE(...,"&&","&") step removes them; if they persist, verify that every IF block ends with an ampersand only when a value is present.
  • URL‑encoding problems: Neither Google Sheets nor Airtable automatically percent‑encode spaces or special characters. Use ENCODEURL() in Sheets or wrap each parameter value with SUBSTITUTE(...," ","%20") in Airtable if needed.

Next Steps

Once your template is reliable, you can:

  • Share the Google Sheet with teammates and protect the formula column (View → Protect range).
  • Export the Airtable view as CSV for bulk upload to a URL shortener.
  • Connect the sheet to Google Data Studio or Airtable’s own reporting blocks to visualise campaign performance.

With a single source of truth for UTM links, you avoid the “fb vs Facebook” fragmentation that many marketers struggle with, as highlighted by the HowToAtlas template creator.[2]

FAQ

Can the template handle destination URLs that already contain a fragment (#)?

Yes. The Google Sheets formula checks for both “?” and “#”. If a fragment is present, the UTM parameters are inserted before the “#”, preserving the original anchor.

Do I need a paid Airtable plan to use the formula field?

No. Formula fields are available in Airtable’s free tier; the only limitation is the number of records per base.

How do I ensure my team uses the same source names (e.g., facebook vs fb)?

Add data‑validation dropdowns in Google Sheets or a single‑select field in Airtable with the approved list of source values.

Sources & further reading

  1. Airtable Community, “Basic task: Re-create UTM Tracking Google sheet formula in AirTable”, https://community.airtable.com/formulas-10/basic-task-re-create-utm-tracking-google-sheet-formula-in-airtable-33061
  2. HowToAtlas, “UTM Builder (Free Google Sheets Template) + Best Practices”, https://howtoatlas.com/sheets/utm-builder-free-google-sheets-template-best-practices/
  3. UTMTool, “Bulk UTM builder template: one row per link, checked before you export”, https://utmtool.com/guides/bulk-utm-builder-templates
  4. CampTag, “UTM building using Google sheets”, https://camptag.ai/resources/utm-builder-using-google-sheets.html

Leave a Reply

Your email address will not be published. Required fields are marked *