MARKETING
Nightly Reconciled UTM Snapshot to Warehouse
Nightly, pulls all Stripe revenue events with UTM tags, normalizes them against the spend table, and writes a clean reconciled fact table back into BigQuery for BI dashboards.
How it runs
The automated pipeline, trigger to output.
- TriggerNightly schedule
- ActionPull prior-day Stripe revenue with UTM tagsStripe
- LogicNormalize tags, aggregate per campaign/day
- ActionJoin spend rows for the dateBigQuery
- ActionWrite reconciled fact table partitionBigQuery
- OutputSlack confirmation with row countsSlack
What it does
It maintains the reconciled dataset everything else reads from. Each night it ingests Stripe revenue events, normalizes their UTM tags, joins them to the canonical spend table, and writes a tidy per-campaign-per-day fact table back into BigQuery so dashboards and other workflows query one trusted source.
When to use it
Use it as the foundational pipeline when multiple teams build Looker/Metabase dashboards on top of campaign data and you want reconciliation done once, server-side, instead of in every BI query. Run the other templates downstream of this one.
How it works
- 1A nightly schedule triggers the job.
- 2Stripe revenue events from the prior day are pulled with their UTM metadata.
- 3A logic step normalizes tag casing and aliases, then aggregates revenue per campaign per day.
- 4BigQuery supplies the matching daily spend rows for the join key.
- 5The reconciled rows (spend, revenue, ROAS, source counts) are written into a dedicated BigQuery fact table, replacing the day's partition idempotently.
- 6A Slack confirmation reports row counts and any tags that failed to normalize.
Set it up
What you configure once, before turning it on.
- 1Connect StripeCustomers, subscriptions, payments.
- 2Connect BigQueryDatasets, queries, schemas.
- 3Connect SlackChannels, DMs, threads, mentions.
- 4Set each agent's modelWe leave models unset so you pick the tier — fast + cheap, or top-quality.
- 5Tune it to your dataEdit the prompts, filters, and field mappings so it matches how your team works.
- 6Test, then turn it onRun once against a sample, confirm the output, then enable the trigger.
More Marketing workflows
Slack UTM Check: Paste a Link, Get an Instant Verdict
Lets marketers drop a tracking URL into a Slack channel and get an immediate threaded reply that says whether the UTMs are valid, what is wrong, and a corrected suggestion.
Unlinked Brand-Mention Detector with Authority Scoring
Scans the web for fresh brand mentions, filters to ones that name you without linking back, scores the publishing domain's authority.
Agentic UTM Taxonomy Steward: Detect, Explain, and Propose Fixes
An agent reviews the full backlog of campaign links in Airtable, reasons about taxonomy drift beyond rigid rules, drafts standardized replacements for malformed UTMs.
Nudge translators on overdue localization returns and escalate stuck ones
Runs on a daily schedule, scans monday.com translation subtasks for items past their return date that aren't marked Returned, reminds the assigned translator in Slack.
Backlink Outreach Drafter for Verified Mentions
Picks verified, high-authority unlinked mentions from Airtable, drafts a personalized backlink-request email for each.
Source-Authority Verifier for Mention Provenance
Takes a queue of detected brand mentions and runs an agent that verifies whether each source is a legitimate, authoritative publisher versus a scraper or syndication copy.
Run it inside a business
This workflow drops into a full company template. Import the org, and this is one of the playbooks its agents run.
Run this workflow in your colony.
14-day trial. No DevOps. No Sales call. Provisioned in under a minute.