Write and save a query report

Write a query to answer a store question and save its result as a report.

What this helps you do

The query and preview sit beside one compact controls card. When the source has a date column, the date button above the query opens its date settings; changes update the query, and Done closes the popup. The query editor toolbar includes Undo query edit, Redo query edit, Query help, and Collapse query editor. The editor starts collapsed. Select Expand query editor to open it. Collapsing the editor keeps your query and edit history. The keyboard shortcut hint explains how to run the query and open suggestions. Available table suggestions include descriptions; the full table and column reference is below.

Write a query report when you need a calculation or grouping that the visual report builder does not offer. A query is a way to ask the store's reporting data a precise question.

You can also start from a Freeform report. Its query box is directly editable, beside the same right-hand controls. The report keeps its header, dates, controls and results on the same screen. Select Run query after changing the query. Supported changes to fields, filters, dates, row limit or sorting update the matching source controls after the query runs. Until the edit has been checked, those source controls are unavailable so they cannot overwrite your draft.

Changing a source control updates the query. When changes cannot be combined with your edits, Upstorr asks which version to keep. A custom calculation the source controls cannot describe stays in the query; after running it, available query result controls let you choose its fields and chart in the same sidebar. Relative dates move forward each time the query runs; custom dates stay fixed until edited. The first query line records the linked report's Compare choice. Summary and comparison appear while the final query still matches the source settings; a matching daily line or area chart can also show the comparison line. A changed calculation that no longer matches those settings hides source-only totals and comparison. The final query result and chart remain available. Saving keeps the source settings and final query in one custom report.

Live online store sessions, page views, events, page loading, layout stability and responsiveness use the selected store's activity source in the same workspace. The database-backed query controls described below apply to database-backed reports.

For a simple chart with one group, the query copy keeps the chart style, fields, and chosen number of chart groups. A stacked vertical or horizontal bar chart with two groups keeps both groups, the chart style, and the chart group count. If the original chart has settings the query chart cannot reproduce, the query copy shows the same table rows and explains why the chart was not copied. Choose another chart in query if needed.

The page speed reports use totalPageLoads() in their live activity query to calculate each page path's or page type's share of page loads. It counts recorded page openings for the selected store, dates, and filters before the table's row limit. This function is available for page loading, layout stability, and responsiveness reports. Older measurements without a page type appear as Unclassified.

Theme activity marks stay in the visual report.

Before you start

Use Upstorr Admin on a desktop. Store admin pages do not open on mobile devices yet.
Your role needs permission to view reports.
To select Write query and start a database-backed report, query reporting must be available. If Write query is missing, ask Upstorr support whether reporting is configured and whether your role can view reports. To edit a live storefront activity report, open that visual report and select Edit query.
Check money amounts carefully. Orders may have different currencies. Group money totals by currency or convert them using the order's exchange rate before adding them together.
The available database views include orders, items, payments, returns, current inventory, inventory transfers, their items and recorded receiving history, products, variants, customers, stock adjustments, stock state changes, and historical session, page-view, and event records. The query copy of a database-backed visual report can use additional case-sensitive source view names such as "Order", "InventoryTransferItem", and "InventoryAdjustmentState"; keep their double quotes when editing the query. "InventoryAdjustmentState" has one row for each recorded change to Available, Unavailable, or Committed stock, so one adjustment can produce two rows. Current storefront activity is recorded separately. Open a live activity visual report and select Edit query to query its scoped activity source.
Payment reports can group by the recorded digital wallet or card brand. A refund uses its recorded instrument or the original payment belonging to that same order and store. Missing details stay blank. These reporting fields do not include card numbers, customer cardholder names, or UPI addresses.
The query source "OrderEvent" includes recorded Razorpay dispute updates. Its metadata contains the provider, dispute and payment IDs, the original signed notification's signedWebhookPhase, and providerEventAt when recorded. New notifications also keep signedWebhookReasonCode, signedWebhookAmount, and signedWebhookCurrency separately from any later status check. FRAUD is an early warning and RETRIEVAL is a request for information; neither proves a chargeback. Use the first recorded CHARGEBACK notification for the chargeback date and count each payment once. A later status check does not prove when the chargeback began. Older updates without this notification history cannot provide that date; keep it blank.
Gift card balances and transactions are available in both the visual builder and query reports. gift_cards shows current balances, while gift_card_transactions records dated amounts. Group gift card money by currency. An ADJUSTED transaction records the size of a change, not whether it added or removed balance.
Chargeback rate uses actual Razorpay chargebacks divided by successful Razorpay payments. For example, 2 chargebacks and 50 payments give 4%. Its total uses all payments, so a 40% week and a 0% week do not automatically mean 20% overall. Cash and terminal payments, failed attempts, and separate refunds are excluded; the original successful payment still counts after a refund. Paid booking damage claims are included.
Chargeback rate (fraud) uses only actual chargebacks with a bank reason code verified for the recorded card network. For example, 1 fraud chargeback and 50 successful payments give 2%. Chargeback amount (fraud) uses the amount in the first signed chargeback notification, in your store currency. Missing card networks, unknown reason codes, missing original dates or amounts, and conflicting first notifications stay blank. Early fraud warnings and requests for information are not confirmed fraud chargebacks. A foreign-currency amount without a verified store-currency value stays blank.
"RazorpayPaymentCapture" provides the capture date from a verified payment.captured notification. "OrderPayment"."referenceId" links an order payment to that provider payment. "Booking" and "BookingDamageClaim" provide the booking-payment link and recorded paid amount. Each source stays limited to your store. An older payment without its original capture notification cannot prove its capture date: affected date groups and the rate stay blank instead of using the day Upstorr processed it. A period with no successful payments also has a blank rate.

Steps

Open Reports.
Select Write query beside New report.
Open Report details and enter a Name.
Optionally enter a Description.
Select Expand query editor, then enter a read-only query in the query editor. For example, the sample query counts orders by sales channel for the last 30 days. The editor highlights query and suggests available table and column names as you type. Press Ctrl+Space to open suggestions.
Select Run query in the editor toolbar, or press Ctrl+Enter (⌘+Enter on Mac), to preview the result. If PostgreSQL points to a mistake in your query, the affected word is underlined and the error appears below the editor. Editing the query clears that mark.
Check the rows and numbers in Result. When query still matches its visual source, known measures keep that source's names and money or percent formatting in the table.
After the query runs, expandable Metrics, Dimensions, Filters, and Sort and limit sections appear in the controls card for its result without changing your query. To filter, set dates, or sort the returned rows, change a control. Your original query is then placed inside a new query that keeps its result columns. Select Add measure when you want to count or combine those rows into a summary; then you can group them. You can add up to 12 measures, 8 filters, and a row limit from 1 to 1,000. If your query returns a date or timestamp column, open the date button above the query and select Add date range. Choose Custom dates for fixed India calendar days, or choose Last 30 days, This month, or another moving range. Last number of units lets you choose a number of minutes, hours, days, weeks, months, quarters, or years and whether to include the current unit. Date-only columns cannot use minutes or hours. Moving ranges update each time the report runs; the end date of a custom range is included. For several matching values in one field, choose Is one of and enter one value per line. Is not one of excludes those values. Text fields also offer Contains, Does not contain, Starts with, and Ends with. These text rules ignore letter case and treat % and _ as ordinary characters. Is not, Is not one of, and Does not contain include rows where the field is blank. Filters on different fields all have to match. Count rows works for any result. Sum, Average, Minimum, and Maximum need a numeric result column.
Select Run query again after changing a control. The controls write query immediately; the result and chart update only after you run it. If you edit the query yourself, run it before using the controls again. query that the controls cannot describe still runs as code. For money totals, group by currency or convert amounts in your source query before adding them.

Add filter starts with an editable example value: zero for a number or today's India date when only a date field is available. Choose the field, rule, and value you actually want before running the query.
Open Visualization and choose Display if you want a single value, vertical or horizontal bars, stacked bars, stacked area, line, area, pie, donut, or heatmap. For charts that offer it, set Chart groups from 1 to 1,000. This changes the chart only; the result table keeps its loaded rows.
For a bar, line, area, pie, or donut chart, choose the Labels column. Stacked charts need two different label columns; stacked area uses dates for the first label and another field for its segments. A single value shows the first result row, so aggregate your query to one total when you use it.
For a heatmap, choose one group column for Rows and a different group column for Columns. Your query must return one row for each pair of groups. For example, group orders by sales channel and payment status.
For any chart, choose the Numbers column. A Measure list needs one row of totals. When opened from a dated visual report, it uses the matching source’s full-period totals instead of the first day. If the query changes beyond the visual source, the list asks you to run matching query rather than guessing a total.

A missing number stays blank in a single value or measure list; it is not zero. Bar, line, area, pie, and donut charts leave out rows with a missing number and show a notice. The result table keeps those rows so you can see what is missing.
Select Save to keep the query and chart in the store's report list. The button becomes available after a successful preview, a name, and valid chart columns.

The controls can narrow or summarize rows returned by your original query. They cannot restore rows excluded by a date rule, filter, or LIMIT inside that query. To include more rows, change the original query and run it again.

Tables and columns

Use the exact table and column names below in your query. A column is one piece of information, such as an order date or amount. Names shown in double quotes must keep those quotes. Queries read only the selected store. The editor also suggests these names while you type.

orders

Table name: orders.

Columns: order_id, order_number, created_at, paid_at, channel, payment_status, payment_method, subtotal, discount, tax, shipping, total, currency, exchange_rate, shipping_state, discount_code, customer_name, risk_level, supply_type, cancelled_at, shipped_at, delivered_at, shipping_method, tracking_recorded.

order_items

Table name: order_items.

Columns: item_id, order_id, product_id, variant_id, product_name, quantity, price, discount_amount, line_total, hsn_code, cgst, sgst, igst, cess, created_at, unit_cost_at_sale.

payments

Table name: payments.

Columns: payment_id, order_id, method, status, amount, commerce_amount, captured_at, created_at.

returns

Table name: returns.

Columns: return_id, order_id, status, reason, refund_amount, restocking_fee, created_at.

inventory

Table name: inventory.

Columns: inventory_id, product_id, variant_id, location_id, on_hand, available, committed, unavailable, updated_at.

products

Table name: products.

Columns: product_id, product_name, slug, status, sku, barcode, list_price, sale_price, recorded_cost, track_quantity, is_physical, category_id, vendor_id, created_at, updated_at.

variants

Table name: variants.

Columns: variant_id, product_id, product_name, variant_name, sku, barcode, price_override, sale_price_override, cost_override, track_quantity, position, created_at, updated_at, is_default_variant, inventory_cost_for_valuation.

customers

Table name: customers.

Columns: customer_id, name, created_at, last_order_at, lifetime_value, lifecycle_stage, rfm_group.

inventory_adjustments

Table name: inventory_adjustments.

Columns: adjustment_id, product_id, variant_id, location_id, quantity_delta, available_delta, unavailable_delta, reason, created_at.

gift_cards

Table name: gift_cards.

Columns: card_id, initial_value, recorded_balance, usable_balance, currency, is_active, expires_at, created_at.

gift_card_transactions

Table name: gift_card_transactions.

Columns: transaction_id, card_id, order_id, amount, type, currency, created_at.

sessions

Table name: sessions.

Columns: session_id, started_at, ended_at, device, browser, os, country, city, referrer, utm_source, utm_medium, utm_campaign, utm_content, utm_term, landing_page, duration_seconds, page_view_count, event_count, is_active.

page_views

Table name: page_views.

Columns: page_view_id, session_id, path, title, referrer, duration_seconds, exit_page, created_at.

events

Table name: events.

Columns: event_id, session_id, event_name, event_type, product_id, order_id, quantity, search_query, created_at.

Customer

Table name: "Customer".

Columns: "createdAt", "email", "id", "lifecycleStage", "lifetimeValue", "merchantId", "name", "rfmGroup", "storeId", "lastOrderAt".

CustomerMarketingPreference

Table name: "CustomerMarketingPreference".

Columns: "customerId", "emailOptIn", "merchantId", "storeId".

GiftCard

Table name: "GiftCard".

Columns: "balance", "currency", "expiresAt", "id", "initialValue", "isActive", "merchantId", "storeId".

GiftCardTransaction

Table name: "GiftCardTransaction".

Columns: "amount", "createdAt", "giftCardId", "id", "merchantId", "storeId", "type".

FulfillmentAnalyticsEvent

Table name: "FulfillmentAnalyticsEvent".

Columns: "carrier", "eventAt", "id", "merchantId", "orderCreatedAt", "orderId", "stage", "storeId", "trackingIncluded".

InventoryAdjustment

Table name: "InventoryAdjustment".

Columns: "createdAt", "id", "locationId", "merchantId", "productId", "quantityDelta", "reason", "storeId".

InventoryAdjustmentState

Table name: "InventoryAdjustmentState".

Columns: "adjustmentId", "createdAt", "createdByStaffId", "id", "inventoryState", "locationId", "merchantId", "productId", "quantityDelta", "reason", "referenceDocumentId", "referenceDocumentType", "storeId", "variantId".

InventoryDailyBalance

Table name: "InventoryDailyBalance".

Columns: "id", "merchantId", "storeId", "locationId", "productId", "variantId", "productTitle", "variantTitle", "sku", "tracked", "available", "onHand", "committed", "unavailable", "unitCost", "unitPrice", "currency", "balanceDate", "captureKind", "capturedAt".

InventorySaleAllocation

Table name: "InventorySaleAllocation".

Columns: "merchantId", "storeId", "orderId", "orderItemId", "tracked", "productId", "variantId", "evidence".

InventoryLevel

Table name: "InventoryLevel".

Columns: "available", "committed", "locationId", "merchantId", "onHand", "productId", "storeId", "unavailable", "variantId".

InventoryLocation

Table name: "InventoryLocation".

Columns: "id", "merchantId", "name", "storeId", "isActive".

InventoryTransfer

Table name: "InventoryTransfer".

Columns: "createdAt", "fromLocationId", "id", "merchantId", "referenceNumber", "status", "storeId", "toLocationId", "tags", "shippedAt", "firstReceivedAt".

InventoryTransferItem

Table name: "InventoryTransferItem".

Columns: "createdAt", "id", "merchantId", "productId", "quantity", "storeId", "transferId", "variantId", "receivedQuantity", "rejectedQuantity".

InventoryTransferReceipt

Table name: "InventoryTransferReceipt".

Columns: "id", "merchantId", "storeId", "transferId", "lineId", "acceptedQuantity", "rejectedQuantity", "receivedAt".

Order

Table name: "Order".

Columns: "billingCity", "billingCountry", "billingState", "cancelledAt", "channel", "couponCode", "createdAt", "customerEmail", "customerName", "deliveredAt", "discount", "id", "marketCurrency", "marketExchangeRate", "merchantId", "orderNumber", "paymentMethod", "paymentStatus", "posLocationId", "posStaffId", "riskLevel", "shippedAt", "shipping", "shippingCity", "shippingCountry", "shippingMethod", "shippingState", "storeId", "subtotal", "supplyType", "tax", "tipAmount", "total", "totalCess", "totalCgst", "totalIgst", "totalSgst", "trackingNumber", "userId", "isExchange", "securityDepositTotal", "giftCardShippingAmount", "referenceSnapshot".

OrderItem

Table name: "OrderItem".

Columns: "gstRate", "assessableValue", "attributedStaffId", "cessAmount", "cgstAmount", "createdAt", "discountAmount", "discountLines", "giftCardAmount", "hsnCode", "id", "igstAmount", "lineTotal", "merchantId", "metadata", "name", "orderId", "price", "productId", "quantity", "sgstAmount", "shippingAmount", "shippingTaxAmount", "storeId", "unitCostAtSale", "variantId", "variant".

LocalDeliveryJourney

Table name: "LocalDeliveryJourney".

Columns: "id", "merchantId", "storeId", "orderId", "returnId", "kind".

LocalDeliveryEvent

Table name: "LocalDeliveryEvent".

Columns: "merchantId", "storeId", "journeyId", "status", "occurredAt".

OrderEvent

Table name: "OrderEvent".

Columns: "createdAt", "eventType", "id", "merchantId", "metadata", "orderId", "referenceSnapshot", "storeId".

Purchased Shiprocket labels have "eventType" = 'SHIPMENT_LABEL_PURCHASED'. Their "referenceSnapshot" includes the waybill, forward direction, purchase status and five label details: purchase date, courier name, selected service, origin country and destination country. It excludes private booking tokens.

OrderPayment

Table name: "OrderPayment".

Columns: "referenceId", "amount", "commerceAmount", "capturedAt", "securityDepositAmount", "metadata", "createdAt", "id", "merchantId", "method", "orderId", "status", "storeId".

The built-in payment reports use "capturedAt" for completed payments and refunds, and "createdAt" for attempts that have not completed. Dates are grouped in India time. A ₹100 payment started at 11:59 p.m. on Monday and completed at midnight belongs to Tuesday. A refund uses its own recorded completion date. Using "createdAt" for every row would answer when the payment was started instead.

For Razorpay payments and refunds, a verified completion notification supplies the provider's completion time, even when Upstorr receives it later. Repeated notifications keep the earliest verified completion. An older record without this notification still shows when Upstorr recorded its completion; it does not prove the original Razorpay time. Running an old report again can therefore show a corrected date after that notification arrives.

RazorpayPaymentCapture

Table name: "RazorpayPaymentCapture".

Columns: "id", "merchantId", "observedAt", "paymentId", "providerCapturedAt", "providerPaymentCreatedAt", "storeId", "feeCurrency", "feeAmount", "feeTaxAmount", "isInternational".

Recorded INR fees use rupees. "feeAmount" already includes the GST in "feeTaxAmount": a ₹59 fee with ₹9 GST costs ₹59, not ₹68. Subtract the tax component only when you specifically need the fee before GST. Missing fee or tax details stay blank; a recorded zero is different from a missing amount. Fees in a currency other than INR stay blank until their currency can be established accurately. Repeat notifications can fill missing details without changing known amounts. Do not count the same "paymentId" more than once when combining capture notifications.

Booking

Table name: "Booking".

Columns: "id", "merchantId", "orderId", "storeId".

BookingDamageClaim

Table name: "BookingDamageClaim".

Columns: "bookingId", "createdAt", "id", "merchantId", "paidAmount", "providerPaymentId", "settledAt", "storeId".

RazorpayPaymentDispute

Table name: "RazorpayPaymentDispute".

Columns: "amount", "amountDeducted", "currency", "disputeId", "id", "merchantId", "orderId", "paymentId", "phase", "providerCreatedAt", "reasonCode", "reasonDescription", "status", "storeId".

Return

Table name: "Return".

Columns: "createdAt", "id", "merchantId", "orderId", "reason", "refundAmount", "restockingFee", "status", "storeId", "updatedAt".

PosLocation

Table name: "PosLocation".

Columns: "id", "merchantId", "name", "storeId", "kind", "inventoryLocationId".

PosStaff

Table name: "PosStaff".

Columns: "id", "merchantId", "name", "storeId".

Product

Table name: "Product".

Columns: "costPrice", "id", "merchantId", "name", "storeId", "isPhysical", "price", "sku", "vendorId", "categoryId".

Vendor

Table name: "Vendor".

Columns: "id", "merchantId", "storeId", "name".

ProductVariant

Table name: "ProductVariant".

Columns: "costPrice", "id", "merchantId", "name", "options", "storeId", "productId", "sku", "price", "trackQuantity", "createdAt".

ReturnLineItem

Table name: "ReturnLineItem".

Columns: "id", "merchantId", "orderItemId", "quantity", "reason", "returnId", "storeId".

Shipment

Table name: "Shipment".

Columns: "merchantId", "orderId", "status", "storeId", "waybillNumber", "id", "createdAt", "carrier", "deliveredAt", "fulfillmentOrderId".

ShipmentBillingEntry

Table name: "ShipmentBillingEntry".

Columns: "id", "merchantId", "storeId", "connectionId", "sourceVersion", "providerTransactionId", "shipmentId", "waybillNumber", "returnWaybillNumber", "action", "charge", "description", "debitAmount", "creditAmount", "providerCreatedAtRaw", "isConflicted", "firstObservedAt", "lastObservedAt".

These are recorded Shiprocket account charges linked to your store's Shiprocket shipments. Join "shipmentId" to "Shipment"."id". A matching local-delivery shipment does not receive a Shiprocket charge. Debits are money taken from the account; credits are money added back. The provider's charge name explains the entry. Freight, weight adjustments and return-to-origin charges are different entries; do not treat every debit as the cost of the original delivery.

A missing amount stays blank. A recorded zero means zero. If repeated provider entries disagree, "isConflicted" is true: the first known amount is retained, but the correct amount is uncertain. Do not treat it as a confirmed expense. "providerCreatedAtRaw" keeps the provider's original date text; Upstorr does not guess its timezone or split a charge into GST and other costs.

The first history scan must finish before charges appear. Changing the connected account's credentials starts a fresh history check; earlier versions do not appear alongside the new version or double the same transaction. During later refreshes, existing entries can gain missing details or be marked conflicted. These are recorded entries, not a frozen or guaranteed complete expense total. No matching entry does not prove that delivery was free.

ShipmentBillingSync

Table name: "ShipmentBillingSync".

Columns: "connectionId", "merchantId", "storeId", "sourceVersion", "completedFrom", "completedTo", "completedAt", "hasUnidentifiedRows", "scanFrom", "scanTo".

This source shows the last completed billing-history scan for the connected account. A non-blank "scanFrom" or "scanTo" means a refresh is unfinished; wait before claiming a complete original shipping cost. A blank "completedAt" means the first scan has not completed. "hasUnidentifiedRows" means at least one matching charge lacked a usable transaction ID and could not be safely recorded. Keep affected costs unknown. The completion date describes that scan; it does not mean later refreshes cannot update the recorded entries.

Purchased shipping labels

Shipping labels by order shows the base cost of forward Shiprocket labels purchased through Upstorr, grouped by purchase date, order, countries, courier and service. Shipping labels over time shows the label count, cost and average cost per label, with the previous period for comparison. Both use the same visual controls and query editor.

The purchase date comes from the provider's label receipt. Shipping carrier is the recorded courier name; Shipping service is the selected Surface or Express mode. Older bookings without a recorded purchase date cannot be placed into these dated reports. Upstorr does not substitute the order date or today's date.

Label cost uses the recorded Freight Charge for that waybill. It excludes later weight adjustments, COD charges, return-to-origin charges and other adjustments. Return labels, cancelled labels and reversed purchases are excluded. Rebooking keeps the earlier purchase receipt; cancelling one waybill removes only that purchase.

Costs stay blank when the billing-history scan is unfinished or the base charge is missing or conflicting. A recorded zero means zero. If any included label has an unknown cost, the affected total and average stay blank. An empty period in Shipping labels over time has a count and cost of zero, with a blank average. Shipping labels by order has no row when no purchase matches.

For example, two September labels cost ₹59 each and one August label costs ₹29. The total is 3 labels costing ₹147 and the average is ₹147 ÷ 3 = ₹49. The total uses every matching label even if the table displays only one row.

Market and original fulfillment

"Market" exposes the recorded market id, name, merchantId, and storeId. Join the order's marketId to this view; a missing market stays blank instead of being guessed from a delivery address.

"FulfillmentOrder" exposes id, merchantId, storeId, orderId, type, status, and a restricted referenceSnapshot containing only the fulfillment source, exchange ID, and return ID. It does not expose a pickup code or customer address. A shipment's fulfillmentOrderId connects its billed charges to the original fulfillment or an exchange replacement.

Exchange

Table name: "Exchange".

Columns: "createdAt", "differenceAmount", "id", "merchantId", "orderId", "status", "storeId", "updatedAt".

ExchangeLineItem

Table name: "ExchangeLineItem".

Columns: "exchangeId", "id", "merchantId", "newOrderItemId", "originalOrderItemId", "quantity", "storeId".

Live storefront query columns

Live activity query uses a different set of fields for each visual source. Its table is named activity; it cannot be joined to the database tables above. Keep the selected source when writing its query. The raw blob and double fields have source-specific meanings. They are not interchangeable between datasets.