Ecommerce Data Analysis in 2026: The Working Playbook

A working playbook for ecommerce data analysis in 2026 — sources from Shopify to GA4, RFM and cohort methods, the metric tree, tool ladder, and where AI agents earn the seat.

Sources every ecommerce stack pulls from

• Storefront platform. Shopify (most common), BigCommerce, Magento, WooCommerce, or a custom storefront. Orders, line items, products, customers, refunds.
• Web and product analytics. GA4 with the BigQuery export turned on, supplemented by Hotjar or FullStory for qualitative session replay if needed.
• Ad platforms. Meta, Google, TikTok, Pinterest, programmatic — spend, impressions, clicks, attributed conversions.
• Email and SMS. Klaviyo, Postscript, or platform native — campaign sends, opens, clicks, attributed revenue.
• Warehouse. The reconciled view — Snowflake, BigQuery, Redshift, or a Postgres data warehouse via Fivetran or Hightouch reverse-ETL.

The warehouse is where order data, web sessions, and ad spend land in canonical form, modeled by dbt or a transformation tool. Storefront UI exports are useful for spot checks; the warehouse is the ground truth for cross-cut analysis.

The ecommerce metric tree — revenue at the top, controllable inputs at the bottom

The metric tree decomposes revenue into the controllable inputs an ecommerce operator can move:

Revenue
  = Sessions × Conversion rate × AOV
  = (Paid sessions + Organic sessions + Direct sessions) × CR × AOV
  = ... where each session source has its own CR and AOV profile

Below that, AOV decomposes into units per transaction × average unit price. Conversion rate decomposes into product-view-to-add-to-cart, add-to-cart-to-checkout, and checkout-to-completion. The tree is the contract — every dashboard panel, every analyst investigation, and every AI agent prompt should be traceable to one node in this tree.

The five metrics that move a quarterly review

1. Revenue and revenue growth rate (week, month, quarter)
2. AOV and AOV trend by channel and customer segment
3. Conversion rate by device and traffic source
4. Repeat purchase rate and 90-day retention
5. Customer acquisition cost and LTV-to-CAC by channel

RFM segmentation in five SQL steps

RFM is the most reliable customer segmentation framework for ecommerce — Recency (days since last order), Frequency (orders in the period), Monetary value (spend in the period). Five SQL steps from raw orders to a per-customer segment label:

-- Step 1: per-customer base metrics over the last 365 days
WITH base AS (
  SELECT customer_id,
         DATE_DIFF(CURRENT_DATE, MAX(order_date), DAY) AS recency_days,
         COUNT(*) AS frequency,
         SUM(total_amount) AS monetary
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '365 day'
  GROUP BY customer_id
),
-- Step 2: percentile buckets
ranked AS (
  SELECT customer_id,
         NTILE(5) OVER (ORDER BY recency_days ASC)  AS r_score,
         NTILE(5) OVER (ORDER BY frequency DESC)    AS f_score,
         NTILE(5) OVER (ORDER BY monetary DESC)     AS m_score
  FROM base
)
SELECT customer_id, r_score, f_score, m_score,
       CASE
         WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
         WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal'
         WHEN r_score <= 2 AND f_score >= 3 THEN 'At-risk'
         WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
         ELSE 'Potential'
       END AS segment
FROM ranked;

The segments become the unit of marketing addressability — Champions get loyalty offers, At-risk get reactivation, Lost get a final win-back. Wire the segment column into your CRM via reverse-ETL and you have closed the loop.

Cohort retention and repeat purchase analysis

Cohort retention answers "of customers acquired in month M, what share placed an order in month M+N?" Three patterns ecommerce teams care about:

• Month-1 repeat rate. The share of a new-customer cohort that buys again in the 30 days after first purchase. Single most predictive number for LTV.
• 90-day retention. The share of a cohort active 90 days post-first-purchase. Smooth weekly noise; reveal channel quality.
• 12-month LTV curve. Cumulative revenue per cohort across 12 months — the basis for the LTV/CAC ratio in board decks.

Channels with high acquisition counts but low month-1 repeat rates are the most common over-investment trap. Read the cohort table before reading the ROAS dashboard, not after.

Funnel diagnostics from catalog view to checkout completion

Segment the funnel by device — mobile vs desktop — and by traffic source. Most ecommerce funnel work lives in the segmentation, not the headline numbers. The drop-off shape, not the absolute conversion rate, is the diagnostic.

The tool ladder for ecommerce analytics

The graduations are forced by question shape, not by ad spend size. A small team with complex cross-source questions belongs on rung 3 sooner than a large team selling one SKU.

Where AI data agents earn the seat in ecommerce analytics

Three concrete patterns where an AI data analyst changes the workflow:

• Anomaly investigation. AOV drops 8% on Wednesday. The dashboard shows the drop. The agent decomposes by SKU, channel, device, and discount code in one prompt, returns the four candidates that explain the drift, and shows the SQL it ran for each. Half-day query session becomes 15 minutes.
• New-question onboarding. A merchant asks "which products bought together drove the highest 90-day LTV?" — a question no dashboard pre-builds. The agent uses the bound knowledge base to map "bought together" to a basket join, "90-day LTV" to the cohort table, and produces a ranked list.
• Cross-source reconciliation. GA4 attributed revenue and warehouse paid revenue disagree by 6%. The agent quantifies the gap, points to the join key where it lives, and surfaces the rows on each side that should match and do not.

The pattern is the same as elsewhere — dashboards answer the standing 80%, agents answer the ad-hoc 20%. Both belong in the stack; neither replaces the other. See the AI database query pillar guide for the connection pattern and read database + knowledge base binding for why the bound layer matters.

FAQ

Sources and references

1. [Vendor] Shopify. Reports and analytics. help.shopify.com/manual/reports-and-analytics.
2. [Vendor] Google. GA4 ecommerce events reference. developers.google.com/analytics/ga4.
3. [Vendor] dbt Labs. Analytics engineering guide. docs.getdbt.com.
4. [Vendor] Klaviyo. Reporting and analytics reference. help.klaviyo.com.
5. [Independent] Yao et al. ReAct: Synergizing Reasoning and Acting in Language Models. arxiv.org/abs/2210.03629.
6. [Vendor] Anthropic. Building Effective Agents. anthropic.com/research/building-effective-agents.
7. [Standard] NIST. AI Risk Management Framework. nist.gov/itl/ai-risk-management-framework.
8. [Independent] BIRD-SQL benchmark. bird-bench.github.io.

Related guides