DEV3Technologies
All case studies

DEV3 Technologies · own product

DEV3 AI Store Insights: plain-English questions over WooCommerce Analytics, without letting AI write SQL

Our own WooCommerce reporting plugin: store owners type questions like "best sellers in Dubai last month" and get a chart, the table behind it and a short explanation. The AI only picks one of 11 read-only reports and fills typed options; it never writes a query.

WordPressWooCommercePHPReactMySQLGoogle Gemini APIFunction callingREST APISVG chartsPHPUnitJestTailwind CSS
Industry
E-commerce (WooCommerce)
Services
AI ImplementationWeb DevelopmentCustom Software

Live project preview

Client systems are private — screenshots shown where permitted.

DEV3 Technologies · own product — screenshots
Ask your store: "best sellers in Dubai last month" becomes a top-products report, with the interpretation chips, the chart and a short explanation.

Ask your store: "best sellers in Dubai last month" becomes a top-products report, with the interpretation chips, the chart and a short explanation.

The challenge

WooCommerce Analytics has the numbers, but answering a simple question such as which products sold best in one city last month means switching reports, setting filters by hand, and it still can't group sales by city at all. Hosted tools like Metorik or Putler fill the gap by copying store data into another service. Letting a language model answer directly has two obvious failure modes: a text-to-SQL model can be steered into reading or damaging tables it should never touch, and a model that reads raw orders can invent numbers or leak customer data. We wanted plain-English questions that produce exact, reproducible figures from the store's own database, work on both HPOS and legacy order storage, and keep working when no API key is set.

Our solution

We built DEV3 AI Store Insights as a WooCommerce plugin in PHP with a React admin. Every number comes from the WooCommerce Analytics lookup tables (wc_order_stats, wc_order_product_lookup, wc_customer_lookup and the coupon and product meta lookups), so the plugin reads the same data WooCommerce's own reports use.

Key design decisions

  • Function calling, not text-to-SQL. The Google Gemini API receives 11 function declarations (store summary, sales trend, top products, top categories, sales by location, customer segments, top customers, refunds, cohorts, stock status, top coupons) with tool_mode: ANY. It can only return a function name and typed arguments: enums, a bounded integer and short strings.
  • Every call is validated twice. Library::sanitize() rejects unknown functions, drops unknown keys, replaces invalid enums with defaults, clamps numbers, cuts strings to 80 characters and falls back to the last 30 days when a custom date is invalid. Values then reach the database only through $wpdb->prepare().
  • One query builder, two order storages. Country, region and city come from wc_order_addresses when HPOS is authoritative and from postmeta when legacy storage is. OrderUtil decides at runtime, and refunds are joined to their parent order's address.
  • Works without AI. A rule-based parser handles common phrasings ("top 5 products by units sold this year", "in March 2026", "past 14 days"), and Explore builds any report by hand. AI only interprets the question and explains the result.
  • Explanations from aggregates only. The model sees the report title, period, columns and at most 15 rows. Customer names become "Customer 1, 2…" unless the owner allows names, and emails never leave the site.
  • Owner-friendly extras. Saved questions that re-run on fresh data, a pinned dashboard with one date range for every card, scheduled HTML email digests, anomaly alerts (z-score against a 28-day baseline, with the products and countries that moved most) and formula-safe CSV export.

Architecture

The Ask endpoint plans the question (AI function calling or the rule parser), re-sanitises each call, runs it through a query builder that only accepts whitelisted metric expressions and placeholder values, and returns a result object that the React app renders as an accessible SVG chart with a table toggle. WooCommerce's excluded-status setting is honoured, periods resolve in the site timezone and week start, and a data-health check imports any order the Analytics tables missed. Daily alert checks and hourly digest ticks run on WP-Cron. The API client, encrypted key storage, usage logs and admin UI kit come from a shared core used across DEV3's plugins.

Results

The plugin is complete. It ships in the DEV3 AI WooCommerce Plugins Bundle on the DEV3 marketplace, alongside 20 other WooCommerce plugins, and is being prepared for CodeCanyon. What was built and verified:

  • 51 PHPUnit test methods (66 cases, including a 16-question rule-parser data set), 31 of them specific to this plugin and 20 for the shared core
  • 37 Jest tests: 17 for the charts, Ask, Explore and saving flows, 20 for the shared UI kit
  • Hostile input checked: SQL fragments in the location and category arguments return an empty report and leave the users table untouched; unknown functions such as drop_tables are rejected
  • Sales by location verified on both HPOS and legacy order storage with the same seeded orders
  • AI paths (function calling, explanations, alert explanations, suggested alert rules) tested with mocked Google Gemini API responses; a run with a live key is still to come
  • Every release gate passed: build, Jest, PHP_CodeSniffer at 0 errors and 0 warnings, PHPUnit in wp-env, translation template and packaged zips

Known gap in v1.0: orders in different currencies are not converted, so multi-currency stores see totals at face value.

Setup guide

No licence key or activation is needed: install the zip and every feature works.

Requirements

  • WordPress 6.0 or newer and PHP 7.4 or newer
  • WooCommerce 8.0 or newer, active, with WooCommerce Analytics enabled (HPOS and legacy order storage are both supported)
  • Optional: a Google Gemini API key. Without one, questions are answered by the built-in rules and explanations are written by the plugin

1. Install the plugin

  1. Download the DEV3 AI WooCommerce Plugins Bundle and unzip it. This plugin is at dev3-ai-woocommerce-plugins-bundle/dev3-ai-store-insights-for-woocommerce/dev3-ai-store-insights-for-woocommerce.zip.
  2. In WordPress, go to Plugins → Add New → Upload Plugin, choose that zip and click Install Now.
  3. Click Activate. DEV3 AI Store Insights for WooCommerce appears in the plugin list and its menu, DEV3 AI Insights, appears in the admin sidebar.

2. Connect Google Gemini (optional)

  1. Create a free API key in Google AI Studio (sign in with a Google account, then Create API key).
  2. Go to DEV3 AI Insights → API Config, paste the key and click Validate & save. The key is stored encrypted (AES-256-GCM) and never sent to the browser.
  3. Optional: pick the model (gemini-2.5-flash is the fast default; gemini-2.5-pro for the highest quality) and set a monthly AI budget.

Prefer config files? Add define( 'DEV3SI_GEMINI_API_KEY', 'your-key' ); to wp-config.php instead.

3. Set it up

  1. Open Settings and check Data health. If WooCommerce Analytics has not imported every order yet, start the import there and let it finish.
  2. In Settings, choose whether locations use the billing or shipping address, whether customer names may be sent to the AI (off by default), and the light, dark or automatic appearance.
  3. Go to Ask and type a question such as "best sellers in Dubai last month". Check the "DEV3 AI read this as" chips, then use Save or pin to keep the answer.
  4. Use Explore to build any report by hand: report, measure, period, location, category and customer filters.
  5. Arrange pinned cards on the Dashboard and pick one date range for all of them.
  6. In Digests, schedule daily, weekly or monthly e-mails with your key numbers and saved questions. Use Preview and Send test before you activate one.
  7. In Alerts → Rules, review the default anomaly rules, add recipients and, optionally, let DEV3 AI suggest rules. Nothing is saved until you accept it.

Updating

Download the new version of the bundle, then upload this plugin's zip in Plugins → Add New → Upload Plugin and choose Replace current with uploaded. Settings and data are kept.

Uninstalling

Deactivate and delete the plugin from Plugins. Saved questions, digests, alerts and settings stay in the database unless you first turn on Delete saved questions, digests, alerts and settings when the plugin is deleted under Settings → Uninstall. Your WooCommerce orders and Analytics data are never changed.

How to use

Ask your store a question

  1. Go to DEV3 AI Insights → Ask.
  2. Type a question such as "best sellers in Dubai last month" or click one of the suggestion chips, then click Ask DEV3 AI (or press Enter).
  3. Check the DEV3 AI read this as chips: report, measure, period and filters. If something is off, click Edit query to change it in Explore and run it again.
  4. Switch between Chart and Table, read the explanation, or click an Ask next follow-up.
  5. Click CSV to download the rows, or Save or pin to keep the question.

Without an API key, or with Answer with set to rules, the built-in rules answer the common questions and the plugin writes a short summary itself.

Build a report by hand

  1. Open Explore.
  2. Pick a Report (for example Top products, Sales by location or Customer cohorts), a Measure, the number of Rows and a Period.
  3. Optionally narrow it by Location, Category, Customers (new or returning) and Accounts (registered or guest).
  4. Click Run report, then Save or pin it if you want it on the dashboard.

Set up your dashboard

  1. Pin answers from Ask or Explore, then open Dashboard.
  2. Use each card's buttons to move it up or down, switch it between half and full width, edit it or unpin it.
  3. Use Date range to keep each card's saved period or show every card for the same period, such as the last 30 days.
  4. Manage saved questions in Saved: run, rename, pin or delete them.

Send a weekly digest

  1. Go to Digests and click New digest.
  2. Choose daily, weekly or monthly, the day and hour, the recipients, whether to include key numbers, which saved questions to include and whether to add a DEV3 AI summary.
  3. Click Preview to see the e-mail and Send test to send it to yourself, then switch it to Active.

Watch for unusual days

  1. Open Alerts → Rules. Default rules for net sales, orders, refunds and best sellers running out are included. Set a metric, an optional location or category, drop, spike or both, the sensitivity, a minimum change and the recipients.
  2. Optional: click Suggest rules with DEV3 AI to get thresholds based on your last 90 days of daily totals. Review the draft; nothing is saved until you accept it.
  3. Each morning, new alerts appear under New. Open one to see the day against the 28-day baseline and the products and countries that moved most, then click Acknowledge.

Related case studies