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.
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.
- 01 · INSTALL
From the listing
Install into your account and approve the one privilege it asks for.
- 02 · GRANT
Three roles
CAMPAIGNS_PLANNERto the people who plan,CAMPAIGNS_VIEWERto analysts, BI tools and agents. The installing role is administrator. - 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.
- 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 requested | Why |
|---|---|
CREATE WAREHOUSE | The 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 unit | A product line, a region or a brand: the top of the referential. Its code is minted from its name. |
|---|---|
| Campaign | Name, 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 names | At 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). |
| Frozen | Minted 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 link | A 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. |
| Readiness | A 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. |
| Rules | Campaign 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. |
| Archive | Soft and restorable, for business units, campaigns and links: what is archived keeps resolving. |
Tables
The app's own records, inside the app. They are read through the DATA views and written through the API procedures, never directly.
| Table | Kind | What it holds |
|---|---|---|
| Business units | Hybrid | Code (unique, frozen), name, description, owning team, tags, status. |
| Campaigns | Hybrid | Code, 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 links | Hybrid | The campaign (enforced foreign key), destination, channel, label, the nine UTM parameters as minted, the final URL. |
| Audit | Hybrid | One row per change: who, what, when, which record. |
| Rules | Standard | The 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
| Object | Purpose |
|---|---|
DATA.BUSINESS_UNITS | Business units. |
DATA.CAMPAIGNS | Campaigns with their minted names, readiness score and missing checks, ROI, cost variance, duration and link count. |
DATA.TRACKING_LINKS | Every link: final URL and each UTM parameter as minted. |
DATA.UTM_DIRECTORY | Every UTM campaign value ever minted, campaign or link level: the table attribution joins on. |
DATA.BUSINESS_UNIT_KPIS | Per 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
| Procedure | Purpose |
|---|---|
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.
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.
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.
| Need | How | Status |
|---|---|---|
| Ads, e-mails, events, QR codes carry the links | Copy the final URL from the console or from DATA.TRACKING_LINKS; the platforms execute. | Your platforms |
| Web analytics and ad-platform exports | Joined to campaigns in Snowflake with RESOLVE_UTM and PARSE_UTM. | Available now |
| Leads carry the code back | Joined on the UTM the lead arrived with: the referential next to Shuntline Leads or any lead table. | Available now |
| Audiences pushed to marketing execution | Built from the referential and consent, pushed by reverse ETL. | Outside the app |
| Integrations create campaigns | The API procedures, through the SQL API or any driver, as a service user. | Available now |
| AI agents read and act | Cortex 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.
| Component | What it is |
|---|---|
| Console | Streamlit 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. |
| Engine | JavaScript procedures: validation, minting, readiness, rules, access. |
| UTM algorithm | One file, embedded in the procedures and loaded by the console: a preview is exactly what will be minted. |
| Hybrid tables | Business units, campaigns, tracking links, audit. |
| Read views | DATA.* and RESOLVE_UTM, filtered per person when Shuntline Hub is connected. |
Design choices
| Hybrid tables | Records 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 once | Generated from the convention at creation and frozen: nobody types a UTM, and a live one never breaks. |
| The tracking id as a token | Attribution matches the id inside any text, so prefixes, suffixes and separators added downstream do not break the join. |
| Scoped before the browser | The 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 task | Simpler 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).