Shuntline›Solutions›Campaign referential & UTM›Documentation

Shuntline Campaigns · documentation · version 1.0

Install, plan, attribute

For Snowflake administrators, marketing operations, analysts and security reviewers: how to install Shuntline Campaigns, what it holds, the SQL API that reads and writes the referential, how access works, and how the app is built.

← The campaign referential: what it does and why

Install and try

A Snowflake Native App, coming to the Snowflake Marketplace. It runs entirely inside your Snowflake account: no container, no external connection, no task.

  1. 01 · INSTALL

    From the listing

    Install into your account and approve the one privilege it asks for.

  2. 02 · GRANT

    Three roles

    CAMPAIGNS_PLANNER to the people who plan, CAMPAIGNS_VIEWER to analysts, BI tools and agents. The installing role is administrator.

  3. 03 · TRY

    Sample company

    One click in an empty console, or one call, loads a fictional company: four business units, about forty campaigns and their links.

  4. 04 · CONFIGURE

    Your rules

    Your business units, campaign types, channels, UTM convention and readiness checks, in the console or in SQL.

CALL SHUNTLINE_CAMPAIGNS.API.MANAGE('load_sample', {});
Privilege requestedWhy
CREATE WAREHOUSEThe app's own X-Small warehouse for the console and the SQL API, suspended 60 seconds after the last query.

Requires a paid Snowflake account on AWS or Azure: campaigns live in hybrid tables, not available on trial accounts and in preview on Google Cloud.

Concepts

A referential: the one list a group agrees to name campaigns by. It defines and mints; your platforms execute.

Business unitA product line, a region or a brand: the top of the referential. Its code is minted from its name.
CampaignName, type, market, status, priority, objective, dates and time zone, channel, audience, tags, budget, actual cost, expected revenue. ROI, cost variance and duration are derived.
Minted namesAt creation each campaign gets a code (from its name), a tracking id (e.g. stc0a1b2c3d4e5f: unique, it ends every UTM name the campaign carries) and a utm_campaign from the group's convention (e.g. acme_pro_spring_launch_stc0a1b2c3d4e5f).
FrozenMinted names never change, even when the campaign is renamed. A rule change applies to campaigns and links created next. A live UTM keeps resolving, and last quarter's attribution still joins.
Tracking linkA destination URL and a channel. The channel sets utm_source and utm_medium, the convention sets the link's utm_campaign, the destination's last path segment sets utm_content. Minted once; it can be archived, never edited.
ReadinessA checklist per campaign: objective, dates, budget, a tracking link, audience, status. Scored 0 to 100: below 50 just getting started, 80 and above launch-ready. A check that does not apply to the campaign's type is skipped.
RulesCampaign types, channels (eleven, plus Other), currencies, the UTM convention (tracking id prefix and length, separator, the segments of campaign and link names) and the readiness checks. Shipped defaults, overridden by administrators, validated whole, applied at once.
ArchiveSoft and restorable, for business units, campaigns and links: what is archived keeps resolving.
ReferentialDefined and minted herebusiness units · campaigns · codes · tracking ids · links
ExecutionYour platforms carry the linksads, e-mails, events, QR codes: copy the URL
AttributionA join on the codesessions, ad exports and leads come back, resolved by RESOLVE_UTM

Tables

The app's own records, inside the app. They are read through the DATA views and written through the API procedures, never directly.

TableKindWhat it holds
Business unitsHybridCode (unique, frozen), name, description, owning team, tags, status.
CampaignsHybridCode, tracking id and utm_campaign (each unique, enforced by the database, frozen); the business unit (enforced foreign key); planning and financial fields; readiness; the attributes Hub scopes match (business unit, market, type).
Tracking linksHybridThe campaign (enforced foreign key), destination, channel, label, the nine UTM parameters as minted, the final URL.
AuditHybridOne row per change: who, what, when, which record.
RulesStandardThe administrators' overrides of the shipped rules; no row means the default.

SQL API

Views and functions to read, procedures to write: from a worksheet, any Snowflake driver, the Snowflake SQL API (HTTPS), a BI tool or an AI agent. SHUNTLINE_CAMPAIGNS is the default app name.

Read

ObjectPurpose
DATA.BUSINESS_UNITSBusiness units.
DATA.CAMPAIGNSCampaigns with their minted names, readiness score and missing checks, ROI, cost variance, duration and link count.
DATA.TRACKING_LINKSEvery link: final URL and each UTM parameter as minted.
DATA.UTM_DIRECTORYEvery UTM campaign value ever minted, campaign or link level: the table attribution joins on.
DATA.BUSINESS_UNIT_KPISPer business unit: campaigns, active, upcoming, completed, overdue, budget, average readiness.
DATA.RESOLVE_UTM(text)Table function: a UTM value back to its campaign, from its exact utm_campaign or the tracking id it carries, whatever was added around it.
API.PARSE_UTM(url)The UTM parameters a URL carries, as an object.
API.NORMALIZE_UTM(value)A value written as the referential writes it in a UTM.

Write

ProcedurePurpose
API.SAVE_BUSINESS_UNIT(object)Creates a business unit, or updates one given its id.
API.CREATE_CAMPAIGN(object)Creates a campaign (its business unit by code or id); answers it with its minted names.
API.UPDATE_CAMPAIGN(id or code, changes)Changes a campaign; minted names are refused as frozen.
API.CAMPAIGN_ACTION(id or code, action)archive, restore or duplicate (a new draft with its own names).
API.PREVIEW_LINK(campaign, url, channel, options)The link that would be minted, with its warnings, writing nothing.
API.MINT_LINK(campaign, url, channel, options)Mints a tracking link; options: a label, and UTM parameters to set explicitly (term, content…).
API.UPDATE_LINK(link id, changes)A link's label, or archive and restore it.
API.MANAGE(op, args)Administrators: config (the effective rules), save_setting (or reset it), load_sample.

Examples

They run in order on a fresh install, as the role that installed the app.

Load a sample company

Acme Group, a fictional company: four business units, about forty campaigns over the last year and the next, and their tracking links. Works once, in an empty app.

CALL SHUNTLINE_CAMPAIGNS.API.MANAGE('load_sample', {});

The group view

Campaigns, budget and readiness per business unit: the group view.

SELECT business_unit_name, campaigns, active, upcoming, overdue, total_budget, avg_readiness
FROM SHUNTLINE_CAMPAIGNS.DATA.BUSINESS_UNIT_KPIS
ORDER BY total_budget DESC;

Create a campaign

Give a business unit's code; the answer carries the campaign's minted code, tracking id and utm_campaign, frozen from then on, and its readiness.

CALL SHUNTLINE_CAMPAIGNS.API.CREATE_CAMPAIGN(PARSE_JSON('{
  "business_unit": "acme-pro", "name": "Spring fleet renewal 2028", "campaign_type": "email",
  "market": "France", "start_date": "2028-03-01", "end_date": "2028-04-30",
  "budgeted_cost": 15000, "expected_revenue": 240000, "currency": "EUR",
  "target_audience": "Customers with machines older than six years"
}'));

Mint a tracking link

A destination and a channel. Source, medium, the link's name and its content are minted; the answer carries the final URL to paste into the ad, the e-mail or the QR code.

CALL SHUNTLINE_CAMPAIGNS.API.MINT_LINK('spring-fleet-renewal-2028',
  'https://www.example.com/fr/fleet-renewal', 'linkedin');

Campaigns not ready to launch

Drafts and planned campaigns below the launch-ready band, with the checks they miss.

SELECT name, business_unit_name, start_date, readiness_score, readiness_missing
FROM SHUNTLINE_CAMPAIGNS.DATA.CAMPAIGNS
WHERE status IN ('draft', 'planned') AND readiness_score < 80
ORDER BY start_date;

Attribution as a join

Any URL or UTM value back to its campaign. Here the minted links themselves; in production, the landing URLs of web analytics or an ad platform's export.

SELECT r.campaign_code, r.business_unit_code, r.matched_on, t.utm_campaign
FROM SHUNTLINE_CAMPAIGNS.DATA.TRACKING_LINKS t,
     TABLE(SHUNTLINE_CAMPAIGNS.DATA.RESOLVE_UTM(
       SHUNTLINE_CAMPAIGNS.API.PARSE_UTM(t.final_url):utm_campaign::VARCHAR)) r
LIMIT 10;

Attribution on your own data

Web sessions joined to the campaigns their landing URL carries, whatever an ad platform added around the UTM.

SELECT s.session_id, r.campaign_code, r.business_unit_code, r.matched_on
FROM web_sessions s,
     TABLE(SHUNTLINE_CAMPAIGNS.DATA.RESOLVE_UTM(
       SHUNTLINE_CAMPAIGNS.API.PARSE_UTM(s.landing_url):utm_campaign::VARCHAR)) r;

Access

The same rule applies at every way in: the console, the views, the procedures and the AI agents that use them.

Out of the box

The app's three roles

CAMPAIGNS_VIEWER reads the referential, CAMPAIGNS_PLANNER also opens the console, creates campaigns and mints links, CAMPAIGNS_ADMIN also sets the rules. Granted by your Snowflake administrator; the grant is the whole decision.

With Shuntline Hub

Roles per person and scope

Administrator, planner, viewer, each optionally limited to business units, markets or campaign types; a planner edits the campaigns they created. The console shows each person only what their scope reaches, and the views return it to each reader. Connected by one call. Shuntline Hub →

GRANT APPLICATION ROLE SHUNTLINE_CAMPAIGNS.CAMPAIGNS_PLANNER TO ROLE <marketing role>;
GRANT APPLICATION ROLE SHUNTLINE_CAMPAIGNS.CAMPAIGNS_VIEWER TO ROLE <analyst role>;

-- with Shuntline Hub
GRANT APPLICATION ROLE SHUNTLINE_HUB.SOLUTION_RUNTIME TO APPLICATION SHUNTLINE_CAMPAIGNS;
CALL SHUNTLINE_CAMPAIGNS.SETUP.CONNECT_HUB('SHUNTLINE_HUB', 'campaigns');

Connecting the stack

What the referential does itself, and what sits next to it.

NeedHowStatus
Ads, e-mails, events, QR codes carry the linksCopy the final URL from the console or from DATA.TRACKING_LINKS; the platforms execute.Your platforms
Web analytics and ad-platform exportsJoined to campaigns in Snowflake with RESOLVE_UTM and PARSE_UTM.Available now
Leads carry the code backJoined on the UTM the lead arrived with: the referential next to Shuntline Leads or any lead table.Available now
Audiences pushed to marketing executionBuilt from the referential and consent, pushed by reverse ETL.Outside the app
Integrations create campaignsThe API procedures, through the SQL API or any driver, as a service user.Available now
AI agents read and actCortex Agents or an MCP server over the DATA views and API procedures, as the person using them.Available now

Architecture

Installed from the Marketplace into your account, the app runs there with your data, and nothing leaves.

ComponentWhat it is
ConsoleStreamlit in Snowsight, one page: dashboard, business units, campaigns as a list, a board or a timeline, each campaign with its readiness, minted names, financials, links and history, tracking links, rules. It receives only what the viewer may see, and works in the browser from there.
EngineJavaScript procedures: validation, minting, readiness, rules, access.
UTM algorithmOne file, embedded in the procedures and loaded by the console: a preview is exactly what will be minted.
Hybrid tablesBusiness units, campaigns, tracking links, audit.
Read viewsDATA.* and RESOLVE_UTM, filtered per person when Shuntline Hub is connected.

Design choices

Hybrid tablesRecords written one at a time; codes, tracking ids and UTM names are unique keys the database enforces, links cannot point at a campaign that does not exist.
Names minted onceGenerated from the convention at creation and frozen: nobody types a UTM, and a live one never breaks.
The tracking id as a tokenAttribution matches the id inside any text, so prefixes, suffixes and separators added downstream do not break the join.
Scoped before the browserThe console filters by the viewer's access in Snowflake first: the browser never holds a record its viewer may not see.
No container, no external connection, no taskSimpler to install and to review; nothing to patch; nothing leaves the account; an idle install costs nothing.

Structure

Schemas inside the app: core (state), logic (the console's operations), api (what people, agents and systems call), data (read views), setup (installation), ui (the console).

Campaign referential & UTM

One campaign catalogue, every business unit

Why a group needs one referential, how UTMs are minted, and how attribution becomes a join.

Back to the campaign referential →the problem · UTMs · FAQ