Campaign Data Model

Data Model Overview

🏗️ Primary Entities

Campaigns: Marketing campaigns with configuration, scheduling, and content

Engagement Metrics: Send totals, delivery, opens, clicks, bounces

Attribution: Sales, replies, and reviews attributed to campaigns

🔗 Key Relationships

Many-to-One: Campaign → Shop (each campaign belongs to one shop)

Many-to-One: Campaign → Segment (campaigns grouped by segment)

One-to-Many: Campaign → Marketing Types (each campaign can have multiple marketing types)

Tip

Table Grain: Each row represents one campaign + marketing type combination. A campaign that sends via both Email and Text will appear as two rows with the same CAMPAIGN_ID but different MARKETING_TYPE values. The composite key is CAMPAIGN_ID + MARKETING_TYPE.

Field Specifications

Campaign Identification

Core identification fields linking campaigns to tenants, shops, and segments.

Field Name	Data Type	Description	Required
STEER_TENANT_ID	String (UUID)	Unique tenant identifier (parent organization)	Yes
STEER_SHOP_ID	String (UUID)	Shop location identifier	Yes
CAMPAIGN_ID	String (UUID)	Unique campaign identifier	Yes
SEGMENT_ID	String (UUID)	Campaign group/segment identifier	Yes

Campaign Information

Campaign configuration and classification details.

Field Name	Data Type	Description	Required
CAMPAIGN_NAME	String	Campaign display name	Yes
CAMPAIGN_LIFESTAGE	String	Campaign lifecycle stage (distinct from execution status in CAMPAIGN_STATUS)	Yes
CAMPAIGN_FREQUENCY_TYPE	String	One-time or repeating campaign	Yes
CREATED_AT	Timestamp	Campaign creation timestamp	Yes
UPDATED_AT	Timestamp	Last modification timestamp	Yes
CAMPAIGN_PURPOSE	String	Campaign purpose/category	Yes
SEGMENT_NAME	String	Segment display name	No
CAMPAIGN_STATUS	String	Current campaign execution status	No
HAS_BEEN_COPIED_TO_OTHER_LOCATION	Boolean	Whether this campaign was used as a template for other locations	No

Marketing Type

Channel and delivery configuration.

Field Name	Data Type	Description	Required
MARKETING_TYPE	String	Communication channel	Yes
MARKETING_TYPE_ENABLED	Boolean	Whether this marketing type is currently enabled	Yes
MARKETING_TYPE_ARCHIVED	Boolean	Whether this marketing type has been archived	Yes

Content

Campaign message content.

Field Name	Data Type	Description	Required
SUBJECT	String	Email subject line (empty for Text campaigns)	No
BODY_TEXT	String	Message body text (empty for Email campaigns). May include personalization tokens like {firstName.txt}, {vehicleMm.txt}, {locationName.txt}. The .txt / .ptxt extensions are part of the token syntax	No

Scheduling

Campaign timing and recurrence configuration.

Field Name	Data Type	Description	Required
SCHEDULED_AT	Timestamp	Future scheduled send time (null if not scheduled)	No
START_AT	Timestamp	Campaign start date	No
END_AT	Timestamp	Campaign end date (null for evergreen campaigns)	No
REPEAT_FREQUENCY	Number	Repeat interval in days (null for one-time campaigns)	No

Trigger Rules

Automated trigger conditions for campaign execution.

Field Name	Data Type	Description	Required
DAYS_SINCE_DUE	Number	Number of days since a vehicle service became due. A service becomes due when a vehicle reaches a configured threshold of days or miles since last service (based on Steer or shop management system settings). This value represents how many days past that due date the vehicle currently is	No
DAYS_SINCE_DUE_TYPE	String	Time direction for days since due	No
DAYS_AFTER_VISIT	Number	Lower bound (start) of the days-after-visit trigger range. Defines the earliest day after a customer visit that the campaign can fire	No
DAYS_AFTER_VISIT_RANGE_END	Number	Upper bound (end) of the days-after-visit trigger range. Together with DAYS_AFTER_VISIT, defines the window (e.g., DAYS_AFTER_VISIT=3 and DAYS_AFTER_VISIT_RANGE_END=7 means the campaign triggers between 3–7 days after a visit)	No
DAYS_SINCE_DEFERRED	Number	Number of days since the customer declined a service recommendation. “Deferred” means the customer was presented with a recommended service and chose not to proceed	No
DAYS_SINCE_DEFERRED_TYPE	String	Time direction for days since deferred	No

Attribution Configuration

Settings for revenue and engagement attribution.

Field Name	Data Type	Description	Required
STEER_ATTRIBUTION_RANGE_DAY	Number	The attribution window — number of days after a campaign is received during which an attribution event (e.g., an invoice, reply, or review) will be tied to that campaign as a success event	No
ATTRIBUTION_TYPE	String	Type of attribution tracked for this campaign (Sales, Reviews, or Replies)	No

Engagement Metrics

Aggregated send and engagement data from DM_SENT_TOTALS_CAMPAIGN.

Field Name	Data Type	Description	Required
TOTAL_SENT	Number	Total messages sent	No
DELIVERED_COUNT	Number	Successfully delivered messages	No
OPENS_COUNT	Number	Total opens (email only)	No
UNIQUE_OPENS_COUNT	Number	Unique opens (email only)	No
CLICKS	Number	Total link clicks (email only)	No
UNIQUE_CLICKS	Number	Unique link clicks (email only)	No
BOUNCED	Number	Bounced messages (email only)	No
UNDELIVERED_COUNT	Number	Undelivered messages	No
UNSUBSCRIBED_COUNT	Number	Recipients who unsubscribed	No

Total Attribution

Field Name	Data Type	Description	Required
TOTAL_STEER_ATTRIBUTION	Number	Attributed value based on ATTRIBUTION_TYPE: when Sales, this is the total revenue amount; when Reviews, this is the count of attributed reviews; when Replies, this is the count of attributed replies. The attribution is calculated within the window defined by STEER_ATTRIBUTION_RANGE_DAY	No

Field Enum Values

Lifestage

CAMPAIGN_LIFESTAGE — the lifecycle stage of the campaign (is it live, being built, or retired?):

• Active: Campaign is currently live and eligible for sending
• Draft: Campaign is being configured and has not yet been activated
• Archive: Campaign has been retired and will no longer send

Note

Lifestage vs. Status: CAMPAIGN_LIFESTAGE describes where the campaign is in its lifecycle (active, draft, or archived). CAMPAIGN_STATUS describes the current execution state of an active campaign (e.g., Scheduled, Processing, Finished). A campaign must have CAMPAIGN_LIFESTAGE = Active before it can progress through execution statuses.

Frequency Type

• OneTime: Campaign runs once
• Repeat: Campaign runs on a recurring schedule

Purpose

• FollowUp: Post-visit follow-up campaigns
• Outreach: Customer outreach campaigns
• ServiceReminders: Service reminder campaigns
• DeclinedServices: Deferred/declined service campaigns

Marketing Type

• Text: SMS/text message
• Email: Email message
• Postcard: Physical postcard
• Letter: Physical letter

Campaign Status

• Scheduled: Campaign is scheduled for future delivery
• Queued: Campaign is queued for processing
• Processing: Campaign is currently being processed
• SentToday: Campaign was sent today
• Finished: Campaign has completed sending
• Suspended: Campaign is suspended
• Delivering: Campaign is currently delivering

Time Direction

Applies to DAYS_SINCE_DUE_TYPE and DAYS_SINCE_DEFERRED_TYPE:

• Unknown: Time direction not specified
• Past: Days in the past
• Before: Days before the due date

Attribution Type

• Sales: Track attributed sales revenue
• Reviews: Track attributed reviews
• Replies: Track attributed replies

Timestamp Handling

Caution

Timezone: All timestamp fields (CREATED_AT, UPDATED_AT, SCHEDULED_AT, START_AT, END_AT) are stored as timestamp_ntz (timestamp without timezone) in UTC. Data consumers should convert to local time using the shop’s timezone when displaying to end users.

Data Quality Notes

Null Values

Note

Expected Null Values

• SUBJECT is null for Text campaigns; BODY_TEXT is null for Email campaigns
• SCHEDULED_AT is null if the campaign was not scheduled for future delivery
• END_AT is null for evergreen campaigns with no end date
• REPEAT_FREQUENCY is null for one-time campaigns
• Trigger rule fields (DAYS_SINCE_DUE, DAYS_AFTER_VISIT, DAYS_SINCE_DEFERRED) may be null depending on campaign purpose
• Engagement metrics may be null if no messages have been sent yet
• TOTAL_STEER_ATTRIBUTION may be null if no attribution data exists

Sample Record

Complete API response example with a campaign record:

{
  "data": [
    [
      "f2e3d4c5-b6a7-8901-2345-6789abcdef01",
      "c3d4e5f6-a7b8-9012-3456-789abcdef012",
      "d4e5f6a7-b8c9-0123-4567-89abcdef0123",
      "e5f6a7b8-c9d0-1234-5678-9abcdef01234",
      "Welcome Back Follow-Up",
      "Active",
      "Repeat",
      "2025-06-15T10:30:00Z",
      "2025-12-01T14:00:00Z",
      "FollowUp",
      "Recent Visitors",
      "Email",
      true,
      false,
      "We miss you! Come back for a check-up",
      null,
      null,
      "2025-06-15T10:30:00Z",
      null,
      30,
      null,
      null,
      7,
      null,
      null,
      null,
      30,
      "Sales",
      "Finished",
      false,
      1250,
      1180,
      890,
      720,
      45,
      38,
      12,
      58,
      3,
      15420.50
    ]
  ],
  "metadata": {
    "rowType": [
      {"name": "STEER_TENANT_ID", "type": "text"},
      {"name": "STEER_SHOP_ID", "type": "text"},
      {"name": "CAMPAIGN_ID", "type": "text"},
      {"name": "SEGMENT_ID", "type": "text"},
      {"name": "CAMPAIGN_NAME", "type": "text"},
      {"name": "CAMPAIGN_LIFESTAGE", "type": "text"},
      {"name": "CAMPAIGN_FREQUENCY_TYPE", "type": "text"},
      {"name": "CREATED_AT", "type": "timestamp_ntz"},
      {"name": "UPDATED_AT", "type": "timestamp_ntz"},
      {"name": "CAMPAIGN_PURPOSE", "type": "text"},
      {"name": "SEGMENT_NAME", "type": "text"},
      {"name": "MARKETING_TYPE", "type": "text"},
      {"name": "MARKETING_TYPE_ENABLED", "type": "boolean"},
      {"name": "MARKETING_TYPE_ARCHIVED", "type": "boolean"},
      {"name": "SUBJECT", "type": "text"},
      {"name": "BODY_TEXT", "type": "text"},
      {"name": "SCHEDULED_AT", "type": "timestamp_ntz"},
      {"name": "START_AT", "type": "timestamp_ntz"},
      {"name": "END_AT", "type": "timestamp_ntz"},
      {"name": "REPEAT_FREQUENCY", "type": "fixed"},
      {"name": "DAYS_SINCE_DUE", "type": "fixed"},
      {"name": "DAYS_SINCE_DUE_TYPE", "type": "text"},
      {"name": "DAYS_AFTER_VISIT", "type": "fixed"},
      {"name": "DAYS_AFTER_VISIT_RANGE_END", "type": "fixed"},
      {"name": "DAYS_SINCE_DEFERRED", "type": "fixed"},
      {"name": "DAYS_SINCE_DEFERRED_TYPE", "type": "text"},
      {"name": "STEER_ATTRIBUTION_RANGE_DAY", "type": "fixed"},
      {"name": "ATTRIBUTION_TYPE", "type": "text"},
      {"name": "CAMPAIGN_STATUS", "type": "text"},
      {"name": "HAS_BEEN_COPIED_TO_OTHER_LOCATION", "type": "boolean"},
      {"name": "TOTAL_SENT", "type": "fixed"},
      {"name": "DELIVERED_COUNT", "type": "fixed"},
      {"name": "OPENS_COUNT", "type": "fixed"},
      {"name": "UNIQUE_OPENS_COUNT", "type": "fixed"},
      {"name": "CLICKS", "type": "fixed"},
      {"name": "UNIQUE_CLICKS", "type": "fixed"},
      {"name": "BOUNCED", "type": "fixed"},
      {"name": "UNDELIVERED_COUNT", "type": "fixed"},
      {"name": "UNSUBSCRIBED_COUNT", "type": "fixed"},
      {"name": "TOTAL_STEER_ATTRIBUTION", "type": "real"}
    ]
  }
}