Best Practices for Analytics Queries

Transform everyday questions into precise MariaDB SQL queries for Campaign, Deliver, or Journey data.

How to Use Prompts?

Structure your prompt like a sentence:
Action
Show / Compare / List
Subject
Campaign metrics / Journey performance
Modifiers
Timeframe, filters, groupings
Example
Show the open rate for Campaign X in the last 30 days.

Prompt Formatting Guidelines

  1. Simple Analytical Query
    Structure
    [Action] + [Subject] + [Predicate] + [Modifiers]
    Example
    Show [Action] the response rate [Predicate] for Campaign A [Subject] over the past week [Modifiers].
  2. Multi-part Query
    Structure
    [Action] + [Subject] + [Predicate] + [Modifiers] + [Temporal Indicator] + [Comparison/Qualifier]
    Example
    List [Action] all journeys [Subject] that had more than 50% completion rates [Predicate] last quarter [Temporal Indicator] and compare them to the current quarter [Comparison].
  3. Contextual Query with Metadata
    Structure
    [Action] + [Subject] + [Predicate] + [Modifiers] + [Metadata/Contextual Clause]
    Example
    Analyze [Action] the milestones [Subject] achieved in Journey X [Predicate] and show their completion dates [Modifiers] using the UARC database [Metadata].
  4. Detailed Temporal and Logical Query
    Structure
    [Action] + [Subject] + [Predicate] + [Temporal Indicator] + [Logical Operators]
    Example
    Show [Action] all campaigns [Subject] that were active last month [Temporal Indicator] AND had an ROI above 20% [Logical Operators].
  5. Comparative Query
    Structure
    [Action] + [Subject] + [Predicate] + [Comparison/Qualifier]
    Example
    Compare [Action] the response rates [Subject] for Campaign A and Campaign B [Comparison] to identify which performed better [Predicate].
  6. Multi-subject Query
    Structure
    [Action] + [Multiple Subjects] + [Predicate] + [Modifiers]
    Example
    List [Action] the milestones and goals [Multiple Subjects] for Journey Y [Predicate] and show their status [Modifiers].
  7. Directive-based Query
    Structure
    [Action] + [Subject] + [Directive] + [Temporal Indicator]
    Example
    Summarize [Action] the key performance indicators [Subject] for all journeys [Directive] over the past 6 months [Temporal Indicator].
  8. Hypothetical Analysis Query
    Structure
    [Action] + [Subject] + [Hypothetical Predicate] + [Modifiers]
    Example
    If [Hypothetical Predicate] 200 more contacts are sent to Campaign X [Subject], what will the response rate be [Action]?
  9. Operational Query
    Structure
    [Action] + [Subject] + [Procedural Predicate]
    Example
    Explain [Action] how to create a new segment [Subject] in Unica Campaign [Procedural Predicate].
  10. Query with Contextual and Temporal Constraints
    Structure
    [Action] + [Subject] + [Predicate] + [Temporal Indicator] + [Contextual Clause]
    Example
    Provide [Action] a summary of Campaign Z's performance [Predicate] over the last quarter [Temporal Indicator], considering its use of email and SMS channels [Contextual Clause].
  11. Nested Query
    Structure
    [Action] + [Subject] + [Nested Predicate] + [Temporal Indicator]
    Example
    Show [Action] all journeys [Subject] where email was the primary channel [Nested Predicate] and compare their performance over the last two months [Temporal Indicator].
  12. User-defined Guardrails Example
    Structure
    [Action] + [Subject] + [Predicate] + [User-Specific Rules]
    Example
    List [Action] all touchpoints [Subject] in Journey A [Predicate], ensuring only completed milestones are included [User-Specific Rules].

Examples for Good Prompt vs Bad Prompt

Prompt Good / Bad? Reason?
List campaigns. Bad No filters or context.
Show the click-through rate for Campaign A last month. Good Specific metric, entity, timeframe.
Data about journeys. Bad Too vague.
Compare email vs SMS performance for Journey Y over Q2. Good Clear comparison and period.
How many? Bad Missing subject.
What is the average revenue for Offer Z? Good Metric, entity.
SQL for campaign C. Bad Asks for SQL, not result.
Show response rate for Campaign B by segment. Good Adds segmentation.
Campaign details. Bad Ambiguous "details".
List milestones for Journey D. Good Clear entity.