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
gold_shopify_coupon_performance columns
24
gold_shopify_customer_ltv columns
41
gold_shopify_funnel_daily columns
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;