AI Report Builder: Create Custom Reports Using AI and SQL

Easily analyze your storefront data with UltraCart’s AI Report Builder. This tool helps you ask natural questions about your data and instantly generates custom reports—complete with SQL, sample data, and visualizations.

Introduction / Overview

The UltraCart AI Report Builder is an advanced reporting tool that allows store owners to explore, analyze, and visualize their storefront data using natural language. Whether you're tracking ad performance, identifying loyal customers, or comparing regional revenue, the AI does the heavy lifting by transforming your question into SQL and returning a complete report.

Why it matters:

• No SQL expertise required

• Instant insights with full data transparency

• AI understands your store’s unique structure

• Reports can be saved, reused, or modified

Prerequisites:

• You must have access to your UltraCart account

• To access Data Warehouse (BigQuery) data, you must be the account owner or be granted access (email must be a Google account)

• BigQuery-enabled UltraCart plan (all plans are eligible)

Quickstart / TL;DR

1. Navigate to Operations > Reporting

2. Click Build Custom Reports

3. Click Create New Report

4. Type a natural-language question about your data (e.g., “Show ad spend vs revenue over the last 60 days”)

5. Review the generated SQL, parameters, sample data, and visualization

6. Save your report with a name and assign it to a group for future use

Step-by-Step Instructions

Step 1: Access the AI Report Builder

1. Log in to your UltraCart account.

2. Navigate to Operations > Reporting.

3. Click the Build Custom Reports button.

Open

Step 2: Create or Manage a Report

• Click Create New Report to begin a new query.

• Or, click on an existing report to view/edit its configuration and results.

Step 3: Ask Your Data a Question

• Use plain English. Example:
  “Compare gross profit by product category after shipping and return costs over the past 90 days.”

• The AI interprets your request and begins building the logic behind the scenes.

Open

What You’ll See As You Build a Report

The AI Report Builder guides you through each stage of report creation—from initial question to SQL query to final visualization. Here’s a breakdown of each section and how it helps you understand and shape your report:

1. Talk to the AI Like a Teammate
   Start by typing your question in plain English—just like you’d ask a data-savvy coworker.
   Example: “Show total sales by state with a map for the last month.”]

2. Watch the AI Build in Real Time
   The assistant responds conversationally, reviewing your data structure and constructing the report logic. You’ll see how your question is being interpreted step-by-step—making the process transparent and easy to follow.

3. Token Usage & Context Transparency
   Each report includes token cost and context usage so you know how much processing power is being used. Thanks to UltraCart’s efficient caching and optimization, token usage remains low—often just a fraction of a cent.

4. SQL & Parameters Panel
   The Report Builder auto-generates the SQL query used behind the scenes. You can:

   • Review the query for accuracy

   • Copy and reuse it in tools like BigQuery

   • See clearly labeled inputs like @start_date and @end_date so you know exactly what data is being pulled

5. Sample Data Preview
   To keep things efficient, the builder displays a sample of the full dataset. This preview helps validate your query structure and report design.
   Note: The complete dataset is available once you save and open the report in the Report Viewer.

6. Visualization Panel
   If you’ve requested a chart or graph, it will appear here. You can:

   • Ask the AI to tweak, enhance, or modify the visual at any time

   • Further edit and refine the visualization later by opening the saved report

7. Expert Analysis Prompt
   Each report generates a custom AI analysis prompt. This prompt:

   • Extracts key insights from your report

   • Offers actionable recommendations

   • Can be tailored via chat to better align with your specific goals

   • Tip: This is just the prompt that will run when viewing the report later—so it can be refined now to guide future analysis effectively.

8. Save Your Report
   When you're ready, click Save to store your report for future access and analysis.

   You'll be prompted to enter:

   • Report Name: A clear, descriptive name (alphanumeric + spaces only) to help you quickly identify the report later.

   • Group Name (optional): Use this to organize related reports into categories—ideal for teams managing multiple campaigns or departments.

   Once saved, your report will be accessible in the Report Viewer, where you can revisit visualizations, run updated queries, or generate fresh AI analyses.

   Tip: Use consistent naming conventions for easy search and sorting (e.g., “Q3 Mobile Sales” or “Inventory by Region”).

What to Do Next

Now that your report is saved:

1. Go to the Report Builder landing page to view your saved reports.

2. Click on your report to open it in the Report Viewer.

3. Analyze your data by setting the desired timeframe and exploring the visualizations and AI-powered insights.

Advanced Options / Deeper Detail

AI & SQL Transparency

• Every report shows you the raw SQL the AI used to generate the report.

• You can edit or copy this SQL for use in other tools like BigQuery, Google Data Studio, or Power BI.

Supported Data

The AI can access your UltraCart BigQuery data sets, which include:

• Orders, Auto Orders, Customers, Items

• Marketing: UTMs, affiliate data, click streams

• Subscription metrics

• Storefront and upsell performance

• Custom fields, experiments, and more

Visualizations

The AI Report Builder not only delivers data—it brings it to life with compelling visualizations. By default, the system recommends and auto-generates the most relevant chart type based on your question (e.g., geographic heat maps, line graphs, bar charts, cohort tables).

Open

However, you’re not limited to just what the AI picks. You can directly influence how your data is visualized by:

• Requesting a specific chart type in your prompt (e.g., “Display as a dual-axis line graph” or “Use a stacked bar chart”)

• Specifying callouts, highlights, or annotations (e.g., “Highlight top 5 regions by color” or “Add markers to key revenue spikes”)

• Referencing chart elements or styles using the Apache ECharts documentation

• Passing custom color schemes or branding palettes to match your store’s design or your dashboard themes

Supported Chart Types

You can request a wide variety of chart formats, such as:

• Line charts (single/multi series)

• Bar & stacked bar charts (horizontal or vertical)

• Pie & donut charts

• Heatmaps & choropleth maps (U.S. state maps supported)

• Scatter plots

• Funnel charts

• Area charts

• Cohort grids

• Dual-axis/multi-metric overlays

• Tables with inline graphics or trendlines

5. Best Practices & Tips

• Think in outcomes: Ask “what do I want to learn?” instead of “what table do I need?”

• Reuse reports: Saved reports save time, especially for recurring data needs.

• Leverage BigQuery power: If you’re technically inclined, use the generated SQL in BigQuery directly for even more advanced analysis.

6. Troubleshooting / FAQ

Q: What if the AI misunderstands my request?
A: Rephrase it slightly or add more context—e.g., “last 30 days” or “by product category.”

Q: Can I use this for subscription or upsell analytics?
A: Yes! You can track retention, revenue, churn, and upsell conversion from all your data.

Q: What data does it pull from?
A: All UltraCart BigQuery-enabled data sets, including nested customer/order info.

Q: What’s the token cost for reports?
A: The ‘AI Build Custom Report' reports incur a usage cost, and display their token cost at the bottom of the report builder dialog form. Click 'View Token Breakdown’ display at the bottom right corner, for the detailed token cost breakdown.
(*Many queries start at under $0.10 due to smart caching.)

Q: Can I set a budget for the AI custom reports?
A: Yes. When you begin creating your first AI generated custom report, you are required to opt-in. To opt-in, click the “Settings” button at the top right of the report generator form. In the dialog window that appears, you’ll see a field “AL Budget: $10/ month”, with the amount field editable to your desired budget amount:

Open

7. Conclusion & Next Steps

The AI Report Builder empowers you to make smarter decisions—quickly and visually—using your own storefront data. With easy access, transparent SQL, and powerful visualizations, it turns your questions into actionable reports.

See also:

• AI Report Builder Overview

• Blog: How to Use AI to Make Better Data Decisions

• BigQuery Data Warehouse Documentation