Shopify dbt Package
This dbt package transforms data from Fivetran's Shopify connector into analytics-ready tables.
Resources
- Number of materialized models¹: 107 (REST API) / 117 (GraphQL API)
- Connector documentation
- dbt package documentation
What does this dbt package do?
This package enables you to transform core object tables into analytics-ready models and understand customer behavior over time through cohort analysis. It creates enriched models with metrics focused on customer retention, product performance, and revenue analysis.
Output schema
Final output tables are generated in the following target schema:
<your_database>.<connector/schema_name>_shopify
Final output tables
By default, this package materializes the following final tables:
| Table | Description |
|---|---|
| shopify__customer_cohorts or shopify_gql__customer_cohorts | Monthly customer behavior tracking by acquisition cohorts, showing retention rates, spending patterns, and lifetime value progression for each customer ID. Example Analytics Questions: • What is the month-over-month retention rate for customers by their first purchase month? • Which customer cohorts have the highest lifetime value progression over their first 12 months? |
| shopify__customers or shopify_gql__customers | Complete customer profiles with lifetime spending, order history, abandoned checkouts, and customer tags. Includes CUSTOMER metafields if enabled. Example Analytics Questions: • Who are the top 100 customers by lifetime spending and what are their purchase patterns? • How many customers have abandoned checkouts but never completed a purchase? |
| shopify__customer_email_cohorts or shopify_gql__customer_email_cohorts | Email-based cohort analysis showing retention and purchasing behavior grouped by email address rather than customer ID, useful for tracking guest checkout behavior. Example Analytics Questions: • How does email-based customer retention compare across different acquisition months? • What is the average time between first and second purchase for email addresses acquired in each cohort month? |
| shopify__customer_emails or shopify_gql__customer_emails | Email address performance summary with lifetime value, order count, and purchase timing metrics, perfect for email marketing segmentation. Example Analytics Questions: • Which email addresses have the highest lifetime value and how many orders have they placed? • What percentage of customer emails have made repeat purchases vs one-time purchases? |
| shopify__orders or shopify_gql__orders | Enhanced order details with shipping costs, discounts, refunds, fulfillment tracking, and new vs repeat customer classification. Includes ORDER metafields if enabled. Example Analytics Questions: • What is the distribution of order values and how many orders are new vs repeat customer purchases? • Which fulfillment services have the fastest processing times and lowest shipping costs? |
| shopify__order_lines or shopify_gql__order_lines | Individual line items from orders with product details, quantities, pricing, refunds, and variant information for detailed product performance analysis. Example Analytics Questions: • Which product variants generate the highest revenue per order line and have the lowest refund rates? • What is the average quantity and tax amount per order line for different product categories? |
| shopify__products or shopify_gql__products | Product performance summary with sales volume, revenue, discounts, refunds, and order timing for inventory and marketing optimization. Includes PRODUCT metafields if enabled. Example Analytics Questions: • Which products have the highest profit margins based on quantity sold vs total discount given? • What products have been selling consistently but haven't been ordered recently (potential inventory issues)? |
| shopify__transactions or shopify_gql__transactions | Payment transaction details with currency exchange calculations, payment methods, and transaction status for financial reporting and fraud analysis. Example Analytics Questions: • What is the breakdown of payment methods used and their success/failure rates? • How do currency exchange rates impact transaction amounts for international orders? |
| shopify__daily_shop or shopify_gql__daily_shop | Daily business performance metrics including orders, revenue, customers, abandoned checkouts, and fulfillment events for operational dashboards. Includes SHOP metafields if enabled. Example Analytics Questions: • What are the peak sales days and how does daily revenue trend over the past year? • How do daily order counts correlate with shipping costs and discount usage? |
| shopify__discounts or shopify_gql__discounts | Discount code performance with redemption rates, revenue impact, and customer usage patterns for promotional campaign analysis. Example Analytics Questions: • Which discount codes have the highest redemption rates and total revenue impact? • What is the average order value for customers using percentage vs fixed amount discounts? |
| shopify__inventory_levels or shopify_gql__inventory_levels | Product variant inventory tracking by location with sales performance, stock levels, and fulfillment metrics for supply chain optimization. Includes PRODUCT_VARIANT metafields if enabled. Example Analytics Questions: • Which product variants have the lowest inventory levels relative to their sales velocity? • What is the total inventory value by location and which locations are most profitable? |
| shopify__line_item_enhanced or shopify_gql__line_item_enhanced | Standardized billing model that aligns with other platforms (Recharge, Stripe, Zuora, Recurly) for cross-platform revenue analysis and reporting. This comprehensive view enables consistent reporting across billing platforms. To see example insights, explore the Fivetran Billing Model Streamlit App. Example Analytics Questions: • What are the monthly recurring revenue trends and how do subscription vs one-time purchase patterns compare? • Which product categories and customer segments drive the highest lifetime value and retention rates? |
¹ Each Quickstart transformation job run materializes these models if all components of this data model are enabled. This count includes all staging, intermediate, and final models materialized as view, table, or incremental.
Visualizations
Many of the above reports are now configurable for visualization via Streamlit. Check out some sample reports here.
Prerequisites
To use this dbt package, you must have the following:
- At least one Fivetran Shopify connection syncing data into your destination.
- A BigQuery, Snowflake, Redshift, PostgreSQL, or Databricks destination.
How do I use the dbt package?
You can either add this dbt package in the Fivetran dashboard or import it into your dbt project:
- To add the package in the Fivetran dashboard, follow our Quickstart guide.
- To add the package to your dbt project, follow the setup instructions in the dbt package's README file to use this package.
How is this package maintained and can I contribute?
Package Maintenance
The Fivetran team maintaining this package only maintains the latest version of the package. We highly recommend you stay consistent with the latest version of the package and refer to the CHANGELOG and release notes for more information on changes across versions.
Contributions
A small team of analytics engineers at Fivetran develops these dbt packages. However, the packages are made better by community contributions.
We highly encourage and welcome contributions to this package. Learn how to contribute to a package in dbt's Contributing to an external dbt package article.