Lucen

Shopify data reference

E-commerce
9 tables
265 columns

Lucen syncs your Shopify data (E-commerce) to your warehouse and models it into the tables below. You connect Shopify with one OAuth sign-in. By default your data refreshes every day at 06:00 Buenos Aires time (UTC-3). Your plan sets how many times a day it can sync, and you can choose the times, see pricing.

Your plan sets how much history the first sync loads, see pricing.

Send Shopify data to

Tables

gold_shopify_attribution_daily

Grain
day / utm source, medium, campaign
Use for
Revenue by last-visit UTM

gold_shopify_coupon_performance

Grain
discount code
Use for
Redemptions and revenue of orders that used each code

gold_shopify_customer_ltv

Grain
customer
Use for
Lifetime value and audiences (PII, hashed email/phone, consent)

gold_shopify_funnel_daily

Grain
day
Use for
Abandoned checkouts vs completed online-store orders

gold_shopify_order_lifecycle

Grain
order
Use for
Hours to paid, fulfilled, delivered, closed, cancelled, refunded

gold_shopify_orders

Grain
order
Use for
Order-level fact: totals, refunds, net payment, UTMs, customer

gold_shopify_orders_daily

Grain
day (purchase date)
Use for
Daily orders and revenue, refunds restated to the order's day; cancelled orders out of revenue

gold_shopify_product_performance

Grain
product
Use for
Units, gross, discounts, returns, net sales per product

gold_shopify_sales_events_daily

Grain
day (movement date)
Use for
Daily sales that match Shopify Analytics: returns on the day of the refund

Columns

Column names match your warehouse exactly. Descriptions come from dbt.

gold_shopify_attribution_daily columns

IDNameTypeDescription
order_dateOrder dateDATEShop-local date the order was processed (Shopify's report day).
shop_idShop idSTRINGNumeric Shopify shop id. Grain key.
currencyCurrencySTRINGShop currency (ISO 4217). Amounts are in this currency.
utm_sourceUtm sourceSTRINGutm_source of the last visit before the order.
utm_mediumUtm mediumSTRINGutm_medium of the last visit.
utm_campaignUtm campaignSTRINGutm_campaign of the last visit.
orders_countOrders countINT64Orders in the row.
first_orders_countFirst orders countINT64First orders of their customer.
gross_revenue_amountGross revenue amountNUMERICOrder totals of non-cancelled orders.
net_revenue_amountNet revenue amountNUMERICOrder totals minus money refunded, non-cancelled orders.
net_sales_amountNet sales amountNUMERICGross sales minus discounts minus returns (Shopify's net sales definition).
discount_total_amountDiscount total amountNUMERICTotal discounts on the order, as placed.
unique_customersUnique customersINT64Distinct customers.
aov_amountAov amountNUMERICAverage order value (non-cancelled). Recompute when aggregating.
built_atBuilt atTIMESTAMPWhen this table was built.

gold_shopify_coupon_performance columns

24

gold_shopify_customer_ltv columns

41

gold_shopify_funnel_daily columns

IDNameTypeDescription
funnel_dateFunnel dateDATEShop-local date: checkout created (abandoned) or order processed (completed).
shop_idShop idSTRINGNumeric Shopify shop id. Grain key.
currencyCurrencySTRINGShop currency (ISO 4217). Amounts are in this currency.
checkouts_startedCheckouts startedINT64Abandoned + completed checkouts.
checkouts_abandonedCheckouts abandonedINT64Abandoned checkouts created that day.
checkouts_completedCheckouts completedINT64Online-store orders that day.
abandoned_cart_value_amountAbandoned cart value amountNUMERICValue of abandoned checkouts.
completed_cart_value_amountCompleted cart value amountNUMERICValue of completed online-store orders.
total_cart_value_amountTotal cart value amountNUMERICAbandoned + completed value.
abandoned_with_email_countAbandoned with email countINT64Abandoned checkouts with a known customer email.
abandoned_cart_rateAbandoned cart rateFLOAT64Abandoned / started.
completion_rateCompletion rateFLOAT64Completed / started.
email_capture_rateEmail capture rateFLOAT64Abandoned with email / abandoned.
built_atBuilt atTIMESTAMPWhen this table was built.

gold_shopify_order_lifecycle columns

22

gold_shopify_orders columns

66

gold_shopify_orders_daily columns

39

gold_shopify_product_performance columns

28

gold_shopify_sales_events_daily columns

16

Typical questions

Prompt to try
Net sales this month, as Shopify shows it

gold_shopify_sales_events_daily

Prompt to try
Revenue of the orders placed this month

gold_shopify_orders_daily

Prompt to try
Best products

gold_shopify_product_performance

Prompt to try
Revenue by campaign

gold_shopify_attribution_daily

Prompt to try
Customers to target / audience export

gold_shopify_customer_ltv

Prompt to try
Coupon results

gold_shopify_coupon_performance

Prompt to try
Abandonment rate

gold_shopify_funnel_daily

Prompt to try
How fast orders ship

gold_shopify_order_lifecycle

Starter queries

sql
SELECT event_date, shop_id, currency, orders_count, refunds_count
FROM `your-project.your_org_gold.gold_shopify_sales_events_daily`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY event_date DESC
LIMIT 100;
sql
SELECT order_date, shop_id, currency, orders_count, cancelled_orders_count
FROM `your-project.your_org_gold.gold_shopify_orders_daily`
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY order_date DESC
LIMIT 100;
sql
SELECT order_date, shop_id, order_id, order_name, created_at
FROM `your-project.your_org_gold.gold_shopify_orders`
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY order_date DESC
LIMIT 100;
sql
SELECT order_date, shop_id, currency, utm_source, utm_medium
FROM `your-project.your_org_gold.gold_shopify_attribution_daily`
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY order_date DESC
LIMIT 100;

Integration page