Cocoon: RAG for Large Data Pipeline
Cocoon prepares large data pipeline for RAG, enabling cursor-style chatbot functionality:
Live Demo: RAG Shopify dbt project in 2 steps
Step 1: Prepare Lightweight RAG in Seconds
Instead of traditional Vector RAG, Cocoon builds novel RAG based on pipeline lineage.
Optionally, Cocoon offers Deep RAG preparation that generates model & column description, and column lineage.
1. model.shopify.shopify__calendar
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{ dbt_utils.date_spine(
datepart="day",
start_date="cast('2019-01-01' as date)",
end_date="current_date"
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
2. model.shopify_source.stg_shopify__abandoned_checkout_discount_code_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='abandoned_checkout_discount_code',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='abandoned_checkout_discount_code_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
3. model.shopify_source.stg_shopify__abandoned_checkout_shipping_line_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='abandoned_checkout_shipping_line',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='abandoned_checkout_shipping_line_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
4. model.shopify_source.stg_shopify__abandoned_checkout_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='abandoned_checkout',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='abandoned_checkout_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
5. model.shopify_source.stg_shopify__collection_product_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='collection_product',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='collection_product_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
6. model.shopify_source.stg_shopify__collection_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='collection',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='collection_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
7. model.shopify_source.stg_shopify__customer_tag_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='customer_tag',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='customer_tag_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
8. model.shopify_source.stg_shopify__customer_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='customer',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='customer_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
9. model.shopify_source.stg_shopify__discount_code_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
-- this model will be all NULL until you create a discount code in Shopify
{{
fivetran_utils.union_data(
table_identifier='discount_code',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='discount_code_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
10. model.shopify_source.stg_shopify__fulfillment_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='fulfillment',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='fulfillment_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
11. model.shopify_source.stg_shopify__inventory_item_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='inventory_item',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='inventory_item_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
12. model.shopify_source.stg_shopify__inventory_level_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='inventory_level',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='inventory_level_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
13. model.shopify_source.stg_shopify__location_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='location',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='location_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
14. model.shopify_source.stg_shopify__metafield_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='metafield',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='metafield_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
15. model.shopify_source.stg_shopify__order_adjustment_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
-- this model will be all NULL until you have made an order adjustment in Shopify
{{
fivetran_utils.union_data(
table_identifier='order_adjustment',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='order_adjustment_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
16. model.shopify_source.stg_shopify__order_discount_code_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='order_discount_code',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='order_discount_code_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
17. model.shopify_source.stg_shopify__order_line_refund_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
-- this model will be all NULL until you have made an order line refund in Shopify
{{
fivetran_utils.union_data(
table_identifier='order_line_refund',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='order_line_refund_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
18. model.shopify_source.stg_shopify__order_line_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='order_line',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='order_line_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
19. model.shopify_source.stg_shopify__order_note_attribute_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='order_note_attribute',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='order_note_attribute_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
20. model.shopify_source.stg_shopify__order_shipping_line_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='order_shipping_line',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='order_shipping_line_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
21. model.shopify_source.stg_shopify__order_shipping_tax_line_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='order_shipping_tax_line',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='order_shipping_tax_line_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
22. model.shopify_source.stg_shopify__order_tag_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='order_tag',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='order_tag_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
23. model.shopify_source.stg_shopify__order_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='order',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='order_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
24. model.shopify_source.stg_shopify__order_url_tag_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='order_url_tag',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='order_url_tag_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
25. model.shopify_source.stg_shopify__price_rule_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='price_rule',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='price_rule_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
26. model.shopify_source.stg_shopify__product_image_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='product_image',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='product_image_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
27. model.shopify_source.stg_shopify__product_tag_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='product_tag',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='product_tag_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
28. model.shopify_source.stg_shopify__product_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='product',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='product_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
29. model.shopify_source.stg_shopify__product_variant_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='product_variant',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='product_variant_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
30. model.shopify_source.stg_shopify__refund_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
-- this model will be all NULL until you create a refund in Shopify
{{
fivetran_utils.union_data(
table_identifier='refund',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='refund_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
31. model.shopify_source.stg_shopify__shop_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='shop',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='shop_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
32. model.shopify_source.stg_shopify__tax_line_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='tax_line',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='tax_line_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
33. model.shopify_source.stg_shopify__tender_transaction_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='tender_transaction',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='tender_transaction_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
34. model.shopify_source.stg_shopify__transaction_tmp
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{
fivetran_utils.union_data(
table_identifier='transaction',
database_variable='shopify_database',
schema_variable='shopify_schema',
default_database=target.database,
default_schema='shopify',
default_variable='transaction_source',
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases'
)
}}
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
35. model.shopify_source.stg_shopify__abandoned_checkout
Model Description
A checkout is considered abandoned after the customer has added contact information, but before the customer has completed their purchase.
SQL
with base as (
select *
from {{ ref('stg_shopify__abandoned_checkout_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__abandoned_checkout_tmp')),
staging_columns=get_abandoned_checkout_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
_fivetran_deleted as is_deleted,
abandoned_checkout_url,
billing_address_address_1,
billing_address_address_2,
billing_address_city,
billing_address_company,
billing_address_country,
billing_address_country_code,
billing_address_first_name,
billing_address_last_name,
billing_address_latitude,
billing_address_longitude,
billing_address_name,
billing_address_phone,
billing_address_province,
billing_address_province_code,
billing_address_zip,
buyer_accepts_marketing as has_buyer_accepted_marketing,
cart_token,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(closed_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as closed_at,
currency as shop_currency,
customer_id,
customer_locale,
device_id,
email,
gateway,
id as checkout_id,
landing_site_base_url,
location_id,
name,
note,
phone,
presentment_currency,
referring_site,
shipping_address_address_1,
shipping_address_address_2,
shipping_address_city,
shipping_address_company,
shipping_address_country,
shipping_address_country_code,
shipping_address_first_name,
shipping_address_last_name,
shipping_address_latitude,
shipping_address_longitude,
shipping_address_name,
shipping_address_phone,
shipping_address_province,
shipping_address_province_code,
shipping_address_zip,
source_name,
subtotal_price,
taxes_included as has_taxes_included,
token,
total_discounts,
total_duties,
total_line_items_price,
total_price,
total_tax,
total_weight,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
user_id,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| is_deleted | Boolean representing whether the record was soft-deleted in Shopify. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| abandoned_checkout_url | The recovery URL that's sent to a customer so they can recover their checkout. |
| billing_address_address_1 | The street address of the billing address. |
| billing_address_address_2 | An optional additional field for the street address of the billing address. |
| billing_address_city | The city of the billing address. |
| billing_address_company | The company of the person associated with the billing address. |
| billing_address_country | The name of the country of the billing address. |
| billing_address_country_code | The two-letter code (ISO 3166-1 alpha-2 format) for the country of the billing address. |
| billing_address_first_name | The first name of the person associated with the payment method. |
| billing_address_last_name | The last name of the person associated with the payment method. |
| billing_address_latitude | The latitude of the billing address. |
| billing_address_longitude | The longitude of the billing address. |
| billing_address_name | The full name of the person associated with the payment method. |
| billing_address_phone | The phone number at the billing address. |
| billing_address_province | The name of the state or province of the billing address. |
| billing_address_province_code | The two-letter abbreviation of the state or province of the billing address. |
| billing_address_zip | The zip or postal code of the billing address. |
| has_buyer_accepted_marketing | Whether the customer would like to receive email updates from the shop. This is set by the 'I want to receive occasional emails about new products, promotions and other news' checkbox during checkout. |
| cart_token | The ID for the cart that's attached to the checkout. |
| closed_at | The date and time (ISO 8601 format) when the checkout was closed. If the checkout was not closed, then this value is null. |
| created_at | The date and time (ISO 8601 format) when the checkout was created. |
| shop_currency | The three-letter code (ISO 4217 format) of the shop's default currency at the time of checkout. For the currency that the customer used at checkout, see `presentment_currency`. |
| customer_id | ID of the customer with the abandoned checkout. |
| customer_locale | The two or three-letter language code, optionally followed by a region modifier. Example values - en, en-CA. |
| device_id | The ID of the Shopify POS device that created the checkout. |
| The customer's email address. | |
| gateway | The payment gateway used by the checkout. |
| checkout_id | The ID for the checkout. |
| landing_site_base_url | The URL for the page where the customer entered the shop. |
| location_id | The ID of the physical location where the checkout was processed. |
| name | Checkout order number. |
| note | The text of an optional note that a shop owner can attach to the order. |
| phone | The customer's phone number for receiving SMS notifications. |
| presentment_currency | The three-letter code (ISO 4217 format) of the currency that the customer used at checkout. For the shop's default currency, see `currency`. |
| referring_site | The website that referred the customer to the shop. |
| shipping_address_address_1 | The street address of the shipping address. |
| shipping_address_address_2 | An optional additional field for the street address of the shipping address. |
| shipping_address_city | The city of the shipping address. |
| shipping_address_company | The company of the person associated with the shipping address. |
| shipping_address_country | The name of the country of the shipping address. |
| shipping_address_country_code | The two-letter code (ISO 3166-1 alpha-2 format) for the country of the shipping address. |
| shipping_address_first_name | The first name of the person associated with the shipping address. |
| shipping_address_last_name | The last name of the person associated with the shipping address. |
| shipping_address_latitude | The latitude of the shipping address. |
| shipping_address_longitude | The longitude of the shipping address. |
| shipping_address_name | The full name of the person associated with the shipping address. |
| shipping_address_phone | The phone number at the shipping address. |
| shipping_address_province | The name of the state or province of the shipping address. |
| shipping_address_province_code | The two-letter abbreviation of the state or province of the shipping address. |
| shipping_address_zip | The zip or postal code of the shipping address. |
| source_name | Where the checkout originated. Valid values include `web`, `pos`, `iphone`, `android`. |
| subtotal_price | The price of the checkout in _presentment_ (customer) currency before shipping and taxes. |
| has_taxes_included | Boolean representing whether taxes are included in the price. |
| token | A unique ID for a checkout. |
| total_discounts | The total amount of discounts to be applied in presentment currency. |
| total_duties | The total duties of the checkout in presentment currency. |
| total_line_items_price | The sum of the prices of all line items in the checkout in _presentment_ (customer) currency. |
| total_price | The sum of line item prices, all discounts, shipping costs, and taxes for the checkout in _presentment_ (customer) currency. |
| total_tax | The sum of all the taxes applied to the checkout in _presentment_ (customer) currency. |
| total_weight | The sum of all the weights in grams of the line items in the checkout. |
| updated_at | The date and time (ISO 8601 format) when the checkout was last modified. |
| user_id | The ID of the user who created the checkout. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
36. model.shopify_source.stg_shopify__abandoned_checkout_discount_code
Model Description
Abandoned checkouts that applied a discount code.
SQL
with base as (
select *
from {{ ref('stg_shopify__abandoned_checkout_discount_code_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__abandoned_checkout_discount_code_tmp')),
staging_columns=get_abandoned_checkout_discount_code_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
checkout_id,
upper(code) as code,
discount_id,
amount,
type,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation,
case when checkout_id is null and code is null and index is null
then row_number() over(partition by source_relation order by source_relation)
else row_number() over(partition by checkout_id, upper(code), source_relation order by index desc)
end as index
from fields
)
select *
from final
where index = 1
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| amount | The amount of the discount in presentment currency. |
| checkout_id | ID of the checkout. |
| code | The discount code. |
| created_at | When the checkout discount application was created. |
| discount_id | ID of the discount. Deprecated, use `code` instead. |
| type | The type of discount. Valid values - percentage, shipping, fixed_amount. (default - fixed_amount) |
| updated_at | When the checkout's discount was last updated |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
37. model.shopify_source.stg_shopify__abandoned_checkout_shipping_line
Model Description
Shipping lines associated with abandoned checkouts.
SQL
with base as (
select *
from {{ ref('stg_shopify__abandoned_checkout_shipping_line_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__abandoned_checkout_shipping_line_tmp')),
staging_columns=get_abandoned_checkout_shipping_line_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as abandoned_checkout_shipping_line_id,
checkout_id,
index,
carrier_identifier,
code as shipping_code,
delivery_category,
delivery_expectation_range,
delivery_expectation_range_max,
delivery_expectation_range_min,
delivery_expectation_type,
discounted_price,
phone,
price,
requested_fulfillment_service_id,
source,
title,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| carrier_identifier | A reference to the carrier service that provided the rate. Present when the rate was computed by a third-party carrier service. |
| checkout_id | ID of the checkout that was abandoned. |
| shipping_code | A reference to the shipping method. |
| delivery_category | The general classification of the delivery method. |
| delivery_expectation_range | Expected delivery date range. |
| delivery_expectation_range_max | Latest expected delivery date. |
| delivery_expectation_range_min | Earliest possible expected delivery date. |
| delivery_expectation_type | Type of expected delivery. |
| discounted_price | The pre-tax shipping price with discounts applied in _presentment_ (customer) currency. |
| abandoned_checkout_shipping_line_id | Unique ID of the abandoned checkout shipping line. |
| index | Index of the line amongst shipping lines for this checkout. |
| phone | The phone number at the shipping address. |
| price | The price of the shipping method in presentment currency. |
| requested_fulfillment_service_id | The fulfillment service requested for the shipping method. Present if the shipping method requires processing by a third party fulfillment service. |
| source | The channel where the checkout originated. Example value - shopify. |
| title | The title of the shipping method. Example value - International Shipping. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
38. model.shopify_source.stg_shopify__collection
Model Description
A collection is a grouping of products that merchants can create to make their stores easier to browse. Merchants can create collections by selecting products individually or by defining rules that automatically determine whether products are included (aka a "smart collection").
SQL
with base as (
select *
from {{ ref('stg_shopify__collection_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__collection_tmp')),
staging_columns=get_collection_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as collection_id,
_fivetran_deleted as is_deleted,
case
when disjunctive is null then null
when disjunctive then 'disjunctive'
else 'conjunctive' end as rule_logic,
handle,
published_scope,
rules,
sort_order,
title,
{{ dbt_date.convert_timezone(column='cast(published_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as published_at,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| is_deleted | Boolean representing whether the record was soft-deleted in Shopify. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| rule_logic | Whether the `rules` are disjunctive (logical `OR`) or conjunctive (logical `AND`) |
| handle | A unique, human-readable string for the collection automatically generated from its title. This is used in themes by the Liquid templating language to refer to the collection. |
| collection_id | The ID for the collection. |
| published_at | The time and date (ISO 8601 format) when the collection was made visible. Returns null for a hidden collection. |
| published_scope | Whether the collection is published to the Point of Sale channel. Valid values `web` (the collection is published to the Online Store channel but not published to the Point of Sale channel) and `global` (the collection is published to both the Online Store channel and the Point of Sale channel).\n |
| rules | An array of rules that define what products go into the smart collection. Each rule (`column` -- `relation` --> `condition`) has these properties: - `column`: the property of a product being used to populate the smart collection. Ex: 'tag', 'type', 'vendor', 'variant_price', etc. - `relation`: The comparative relationship between the column choice, and the condition ('equals', 'contains', 'greater_than', etc.) - condition: Select products for a smart collection using a condition. Values are either strings or numbers, depending on the relation value. See the [Shopify docs](https://shopify.dev/api/admin-rest/2022-10/resources/smartcollection#resource-object) for more.\n |
| sort_order | The order of the products in the collection. Valid values incline - `alpha-asc`: The products are sorted alphabetically from A to Z. - `alpha-des`: The products are sorted alphabetically from Z to A. - `best-selling`: The products are sorted by number of sales. - `created`: The products are sorted by the date they were created, from oldest to newest. - `created-desc`: The products are sorted by the date they were created, from newest to oldest. - `manual`: The products are manually sorted by the shop owner. - `price-asc`: The products are sorted by price from lowest to highest. - `price-desc`: The products are sorted by price from highest to lowest.\n |
| title | The name of the collection |
| updated_at | The date and time (ISO 8601 format) when the collection was last modified. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
39. model.shopify_source.stg_shopify__collection_product
Model Description
Table relating products to the collections they belong to. Use this instead of the deprecated `COLLECT` table.
SQL
with base as (
select *
from {{ ref('stg_shopify__collection_product_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__collection_product_tmp')),
staging_columns=get_collection_product_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
collection_id,
product_id,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| collection_id | ID referencing the `collection` the product belongs to. |
| product_id | ID referencing the `product`. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
40. model.shopify_source.stg_shopify__customer
Model Description
Each record represents a customer in Shopify.
SQL
with base as (
select *
from {{ ref('stg_shopify__customer_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__customer_tmp')),
staging_columns=get_customer_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as customer_id,
lower(email) as email,
first_name,
last_name,
orders_count,
default_address_id,
phone,
lower(state) as account_state,
tax_exempt as is_tax_exempt,
total_spent,
verified_email as is_verified_email,
note,
currency,
case
when email_marketing_consent_state is null then
case
when accepts_marketing is null then null
when accepts_marketing then 'subscribed (legacy)'
else 'not_subscribed (legacy)' end
else lower(email_marketing_consent_state) end as marketing_consent_state,
lower(coalesce(email_marketing_consent_opt_in_level, marketing_opt_in_level)) as marketing_opt_in_level,
{{ dbt_date.convert_timezone(column='cast(coalesce(accepts_marketing_updated_at, email_marketing_consent_consent_updated_at) as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as marketing_consent_updated_at,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_timestamp,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_timestamp,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
{{ fivetran_utils.fill_pass_through_columns('customer_pass_through_columns') }}
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| marketing_consent_state | Field indicating if the customer has consented to receive marketing material via email. Coalescing of the new `email_marketing_consent_state` field and the deprecated `accepts_marketing` field. Records with the old field will be marked with '(legacy)'.\n |
| marketing_opt_in_level | The marketing subscription opt-in level, as described in the M3AAWG Sender Best Common Practices, that the customer gave when they consented to receive marketing material by email. |
| marketing_consent_updated_at | The date and time when the customer consented to receive marketing material by email. If no date is provided, then the date and time when the consent information was sent is used. |
| created_timestamp | The date and time when the customer was created. |
| default_address_id | The default address for the customer. |
| The unique email address of the customer. Attempting to assign the same email address to multiple customers returns an error. | |
| first_name | The customer's first name. |
| customer_id | A unique identifier for the customer. |
| last_name | The customer's last name. |
| orders_count | The number of orders associated with this customer. |
| phone | The unique phone number (E.164 format) for this customer. Attempting to assign the same phone number to multiple customers returns an error. |
| account_state | The state of the customer's account with a shop. |
| is_tax_exempt | Whether the customer is exempt from paying taxes on their order. If true, then taxes won't be applied to an order at checkout. If false, then taxes will be applied at checkout. |
| total_spent | The total amount of money that the customer has spent across their order history. |
| updated_timestamp | The date and time when the customer information was last updated. |
| is_verified_email | Whether the customer has verified their email address. |
| currency | The three-letter code (ISO 4217 format) for the currency that the customer used when they paid for their last order. Defaults to the shop currency. Returns the shop currency for test orders. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
| note | A note about the customer. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
41. model.shopify_source.stg_shopify__customer_tag
Model Description
Tags that the shop owner has attached to the customer. A customer can have up to 250 tags.
SQL
with base as (
select *
from {{ ref('stg_shopify__customer_tag_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__customer_tag_tmp')),
staging_columns=get_customer_tag_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
customer_id,
index,
value,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| index | Index (starting from 1) representing when the tag was placed on the customer. |
| customer_id | ID of the customer being tagged. |
| value | Value of the tag. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
42. model.shopify_source.stg_shopify__discount_code
Model Description
Table storing discount codes that enable specific discounts to be redeemed. Merchants can distribute discount codes to their customers using a variety of means, such as an email or URL, and customers can apply these codes at checkout. Each discount code belongs to a price rule, which holds the logic for the discount.
SQL
-- this model will be all NULL until you create a discount code in Shopify
with base as (
select *
from {{ ref('stg_shopify__discount_code_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__discount_code_tmp')),
staging_columns=get_discount_code_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as discount_code_id,
upper(code) as code,
price_rule_id,
usage_count,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| code | The case-insensitive discount code that customers use at checkout. Shopify recommends this map onto the associated `price_rule.title`. |
| created_at | The date and time (ISO 8601 format) when the discount code was created. |
| discount_code_id | The ID for the discount code. |
| price_rule_id | The ID for the price rule that this discount code belongs to. |
| updated_at | The date and time (ISO 8601 format) when the discount code was updated. |
| usage_count | The number of times that the discount code has been redeemed. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
43. model.shopify_source.stg_shopify__fulfillment
Model Description
A fulfillment order represents a group of one or more items in an order that will be fulfilled from the same location. A fulfillment represents work that is completed as part of a fulfillment order and can include one or more items.
SQL
with base as (
select *
from {{ ref('stg_shopify__fulfillment_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__fulfillment_tmp')),
staging_columns=get_fulfillment_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as fulfillment_id,
location_id,
order_id,
name,
service,
shipment_status,
lower(status) as status,
tracking_company,
tracking_number,
tracking_numbers,
tracking_urls,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| created_at | The date and time when the fulfillment was created. The API returns this value in ISO 8601 format. |
| fulfillment_id | The ID for the fulfillment. |
| location_id | The unique identifier of the location that the fulfillment was processed at. |
| name | The uniquely identifying fulfillment name, consisting of two parts separated by a .. The first part represents the order name and the second part represents the fulfillment number. The fulfillment number automatically increments depending on how many fulfillments are in an order (e.g. #1001.1, #1001.2).\n |
| order_id | The unique numeric identifier for the order. |
| service | The fulfillment service associated with the fulfillment. |
| shipment_status | The current shipment status of the fulfillment. Valid values include: - label_printed: A label for the shipment was purchased and printed. - label_purchased: A label for the shipment was purchased, but not printed. - attempted_delivery: Delivery of the shipment was attempted, but unable to be completed. - ready_for_pickup: The shipment is ready for pickup at a shipping depot. - confirmed: The carrier is aware of the shipment, but hasn't received it yet. - in_transit: The shipment is being transported between shipping facilities on the way to its destination. - out_for_delivery: The shipment is being delivered to its final destination. - delivered: The shipment was successfully delivered. - failure: Something went wrong when pulling tracking information for the shipment, such as the tracking number was invalid or the shipment was canceled.\n |
| status | The status of the fulfillment. Valid values include: - pending: Shopify has created the fulfillment and is waiting for the third-party fulfillment service to transition it to 'open' or 'success'. - open: The fulfillment has been acknowledged by the service and is in processing. - success: The fulfillment was successful. - cancelled: The fulfillment was cancelled. - error: There was an error with the fulfillment request. - failure: The fulfillment request failed.\n |
| tracking_company | The name of the tracking company. |
| tracking_number | Primary tracking number for the order. |
| tracking_numbers | A list of tracking numbers, provided by the shipping company. |
| tracking_urls | The URLs of tracking pages for the fulfillment. |
| updated_at | The date and time (ISO 8601 format) when the fulfillment was last modified. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
44. model.shopify_source.stg_shopify__inventory_item
Model Description
An inventory item represents a physical good. It holds essential information about the physical good, including its SKU and whether its inventory is tracked. There is a 1:1 relationship between a product variant and an inventory item.
SQL
with base as (
select *
from {{ ref('stg_shopify__inventory_item_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__inventory_item_tmp')),
staging_columns=get_inventory_item_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as inventory_item_id,
sku,
_fivetran_deleted as is_deleted, -- won't filter out for now
cost,
country_code_of_origin,
province_code_of_origin,
requires_shipping as is_shipping_required,
tracked as is_inventory_quantity_tracked,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| is_deleted | Boolean representing whether the record was soft-deleted in Shopify. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| cost | The unit cost of the inventory item. The shop's default currency is used. |
| country_code_of_origin | The country code (ISO 3166-1 alpha-2) of where the item came from. |
| created_at | The date and time (ISO 8601 format) when the inventory item was created. |
| inventory_item_id | The ID of the inventory item. |
| province_code_of_origin | The province code (ISO 3166-2 alpha-2) of where the item came from. The province code is only used if the shipping provider for the inventory item is Canada Post. |
| is_shipping_required | Boolean representing whether a customer needs to provide a shipping address when placing an order containing the inventory item. |
| sku | The unique SKU (stock keeping unit) of the inventory item. |
| is_inventory_quantity_tracked | Boolean representing whether inventory levels are tracked for the item. If true, then the inventory quantity changes are tracked by Shopify. |
| updated_at | The date and time (ISO 8601 format) when the inventory item was last modified. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
45. model.shopify_source.stg_shopify__inventory_level
Model Description
An inventory level represents the quantities of an inventory item for a location.
Each inventory level belongs to one inventory item and has one location. For every location where an inventory item can be stocked, there's an inventory level that represents the inventory item's quantities relating to that location.
SQL
with base as (
select *
from {{ ref('stg_shopify__inventory_level_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__inventory_level_tmp')),
staging_columns=get_inventory_level_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
inventory_item_id,
location_id,
available as available_quantity,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| available_quantity | The available quantity of an inventory item at the inventory level's associated location. Returns null if the inventory item is not tracked. |
| inventory_item_id | The ID of the inventory item associated with the inventory level. |
| location_id | The ID of the location that the inventory level belongs to. |
| updated_at | The date and time (ISO 8601 format) when the inventory level was last modified. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
46. model.shopify_source.stg_shopify__location
Model Description
A location represents a geographical location where your stores, pop-up stores, headquarters, and warehouses exist.
SQL
with base as (
select *
from {{ ref('stg_shopify__location_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__location_tmp')),
staging_columns=get_location_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as location_id,
name,
_fivetran_deleted as is_deleted,
active as is_active,
address_1,
address_2,
city,
country,
country_code,
country_name,
legacy as is_legacy,
localized_country_name,
localized_province_name,
phone,
province,
province_code,
zip,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| is_deleted | Boolean representing whether the record was soft-deleted in Shopify. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| is_active | Boolean representing whether the location is active. If true, then the location can be used to sell products, stock inventory, and fulfill orders.\n |
| address_1 | The location's street address. |
| address_2 | The optional second line of the location's street address. |
| city | The city the location is in. |
| country | The country the location is in (two-letter code). |
| country_code | The two-letter code (ISO 3166-1 alpha-2 format) corresponding to country the location is in. |
| country_name | Full name of the location's country. |
| created_at | The date and time (ISO 8601 format) when the location was created. |
| location_id | The ID of the location. |
| is_legacy | Boolean representing whether this is a fulfillment service location. If true, then the location is a fulfillment service location. If false, then the location was created by the merchant and isn't tied to a fulfillment service.\n |
| localized_country_name | The localized name of the location's country. |
| localized_province_name | The localized name of the location's region. Typically a province, state, or district. |
| name | The name of the location. |
| phone | The phone number of the location. This value can contain special characters, such as - or +. |
| province | The province, state, or district of the location. |
| province_code | The province, state, or district code (ISO 3166-2 alpha-2 format) of the location. |
| updated_at | The date and time (ISO 8601 format) when the location was last updated. |
| zip | The zip or postal code. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
47. model.shopify_source.stg_shopify__metafield
Model Description
Metafields are a flexible way to attach additional information to a Shopify resource (e.g. Product, Collection, etc.). Some examples of data stored using metafields include specifications, size charts, downloadable documents, release dates, images, or part numbers. Metafields are identified by an owner resource, a namespace, and a key and they store a value along with type information for that context.
SQL
with base as (
select *
from {{ ref('stg_shopify__metafield_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__metafield_tmp')),
staging_columns=get_metafield_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as metafield_id,
description,
namespace,
key,
value,
lower(coalesce(type, value_type)) as value_type,
owner_id as owner_resource_id,
lower(owner_resource) as owner_resource,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
lower({{ dbt.concat(["namespace","'_'","key"]) }}) as metafield_reference,
case when id is null and updated_at is null
then row_number() over(partition by source_relation order by source_relation) = 1
else row_number() over(partition by id, source_relation order by updated_at desc) = 1
end as is_most_recent_record,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| created_at | The date and time (ISO 8601 format) when the metafield was created. |
| description | A description of the information that the metafield contains. |
| metafield_id | The unique ID of the metafield. |
| key | The key of the metafield. Keys can be up to 64 characters long and can contain alphanumeric characters, hyphens, underscores, and periods. |
| namespace | A container for a group of metafields. Grouping metafields within a namespace prevents your metafields from conflicting with other metafields with the same key name. Must have between 3-255 characters. |
| owner_resource_id | The unique ID of the resource that the metafield is attached to. |
| owner_resource | The type of resource (table) that the metafield is attached to. |
| value_type | The type of data that the metafield stores in the `value` field. Refer to the [list](https://shopify.dev/apps/metafields/types) of supported types. Coalescing of type and value_type |
| updated_at | The date and time (ISO 8601 format) when the metafield was last updated. |
| value | The data to store in the metafield. The value is always stored as a string, regardless of the metafield's type. |
| metafield_reference | Combination of the namespace and key columns. This field is key for metafield mapping in downstream models. |
| is_most_recent_record | Boolean indicating the most up to date record identified by the most recent updated_at field. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
48. model.shopify_source.stg_shopify__order
Model Description
Each record represents an order in Shopify.
SQL
with base as (
select *
from {{ ref('stg_shopify__order_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__order_tmp')),
staging_columns=get_order_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as order_id,
user_id,
total_discounts,
total_discounts_set,
total_line_items_price,
total_line_items_price_set,
total_price,
total_price_set,
total_tax_set,
total_tax,
source_name,
subtotal_price,
taxes_included as has_taxes_included,
total_weight,
total_tip_received,
landing_site_base_url,
location_id,
name,
note,
number,
order_number,
cancel_reason,
cart_token,
checkout_token,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_timestamp,
{{ dbt_date.convert_timezone(column='cast(cancelled_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as cancelled_timestamp,
{{ dbt_date.convert_timezone(column='cast(closed_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as closed_timestamp,
{{ dbt_date.convert_timezone(column='cast(processed_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as processed_timestamp,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_timestamp,
currency,
customer_id,
lower(email) as email,
financial_status,
fulfillment_status,
referring_site,
billing_address_address_1,
billing_address_address_2,
billing_address_city,
billing_address_company,
billing_address_country,
billing_address_country_code,
billing_address_first_name,
billing_address_last_name,
billing_address_latitude,
billing_address_longitude,
billing_address_name,
billing_address_phone,
billing_address_province,
billing_address_province_code,
billing_address_zip,
browser_ip,
total_shipping_price_set,
shipping_address_address_1,
shipping_address_address_2,
shipping_address_city,
shipping_address_company,
shipping_address_country,
shipping_address_country_code,
shipping_address_first_name,
shipping_address_last_name,
shipping_address_latitude,
shipping_address_longitude,
shipping_address_name,
shipping_address_phone,
shipping_address_province,
shipping_address_province_code,
shipping_address_zip,
token,
app_id,
checkout_id,
client_details_user_agent,
customer_locale,
order_status_url,
presentment_currency,
test as is_test_order,
_fivetran_deleted as is_deleted,
buyer_accepts_marketing as has_buyer_accepted_marketing,
confirmed as is_confirmed,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
{{ fivetran_utils.fill_pass_through_columns('order_pass_through_columns') }}
from fields
)
select *
from final
where not coalesce(is_test_order, false)
and not coalesce(is_deleted, false)
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| app_id | The ID of the app that created the order. |
| checkout_id | ID of the order's checkout. |
| order_status_url | The URL pointing to the order status web page, if applicable. |
| billing_address_address_1 | The street address of the billing address. |
| billing_address_address_2 | An optional additional field for the street address of the billing address. |
| billing_address_city | The city, town, or village of the billing address. |
| billing_address_company | The company of the person associated with the billing address. |
| billing_address_country | The name of the country of the billing address. |
| billing_address_country_code | The two-letter code (ISO 3166-1 format) for the country of the billing address. |
| billing_address_first_name | The first name of the person associated with the payment method. |
| billing_address_last_name | The last name of the person associated with the payment method. |
| billing_address_latitude | The latitude of the billing address. |
| billing_address_longitude | The longitude of the billing address. |
| billing_address_name | The full name of the person associated with the payment method. |
| billing_address_phone | The phone number at the billing address. |
| billing_address_province | The name of the region (province, state, prefecture, …) of the billing address. |
| billing_address_province_code | The two-letter abbreviation of the region of the billing address. |
| billing_address_zip | The postal code (zip, postcode, Eircode, …) of the billing address. |
| browser_ip | The IP address of the browser used by the customer when they placed the order. |
| has_buyer_accepted_marketing | Whether the customer consented to receive email updates from the shop. |
| cancel_reason | The reason why the order was canceled. |
| cancelled_timestamp | The date and time when the order was canceled. |
| cart_token | The ID of the cart that's associated with the order. |
| closed_timestamp | The date and time when the order was closed (archived). |
| created_timestamp | The autogenerated date and time when the order was created in Shopify. |
| currency | The three-letter code for the shop currency. |
| customer_id | The ID of the order's customer. |
| The customer's email address. | |
| financial_status | The status of payments associated with the order. Can only be set when the order is created |
| fulfillment_status | The order's status in terms of fulfilled line items. |
| order_id | The ID of the order, used for API purposes. This is different from the order_number property, which is the ID used by the shop owner and customer. |
| landing_site_base_url | The URL for the page where the buyer landed when they entered the shop. |
| location_id | The ID of the physical location where the order was processed. |
| name | The order name, generated by combining the order_number property with the order prefix and suffix that are set in the merchant's general settings. |
| note | An optional note that a shop owner can attach to the order. |
| number | The order's position in the shop's count of orders. Numbers are sequential and start at 1. |
| order_number | The order 's position in the shop's count of orders starting at 1001. Order numbers are sequential and start at 1001. |
| processed_timestamp | The date and time when an order was processed. This value is the date that appears on your orders and that's used in the analytic reports. |
| referring_site | The website where the customer clicked a link to the shop. |
| shipping_address_address_1 | The street address of the shipping address. |
| shipping_address_address_2 | An optional additional field for the street address of the shipping address. |
| shipping_address_city | The city, town, or village of the shipping address. |
| shipping_address_company | The company of the person associated with the shipping address. |
| shipping_address_country | The name of the country of the shipping address. |
| shipping_address_country_code | The two-letter code (ISO 3166-1 format) for the country of the shipping address. |
| shipping_address_first_name | The first name of the person associated with the shipping address. |
| shipping_address_last_name | The last name of the person associated with the shipping address. |
| shipping_address_latitude | The latitude of the shipping address. |
| shipping_address_longitude | The longitude of the shipping address. |
| shipping_address_name | The full name of the person associated with the payment method. |
| shipping_address_phone | The phone number at the shipping address. |
| shipping_address_province | The name of the region (province, state, prefecture, …) of the shipping address. |
| shipping_address_province_code | The two-letter abbreviation of the region of the shipping address. |
| shipping_address_zip | The postal code (zip, postcode, Eircode, …) of the shipping address. |
| source_name | Where the order originated. Can be set only during order creation, and is not writeable afterwards. |
| subtotal_price | The price of the order in the shop currency after discounts but before shipping, taxes, and tips. |
| has_taxes_included | Whether taxes are included in the order subtotal. |
| is_test_order | Whether this is a test order. |
| token | A unique token for the order. |
| total_discounts | The total discounts applied to the price of the order in the shop currency. |
| total_line_items_price | The sum of all line item prices in the shop currency. |
| total_price | The sum of all line item prices, discounts, shipping, taxes, and tips in the shop currency. Must be positive. |
| total_tax | The sum of all the taxes applied to the order in th shop currency. Must be positive). |
| total_weight | The sum of all line item weights in grams. |
| updated_timestamp | The date and time (ISO 8601 format) when the order was last modified. |
| user_id | The ID of the user logged into Shopify POS who processed the order, if applicable. |
| total_shipping_price_set | The total shipping price of the order, excluding discounts and returns, in shop and presentment currencies. If taxes_included is set to true, then total_shipping_price_set includes taxes. |
| checkout_token | A unique value when referencing the checkout that's associated with the order. |
| customer_locale | A two-letter or three-letter language code, optionally followed by a region modifier. |
| is_deleted | Boolean representing whether the record was soft-deleted in Shopify. |
| total_tip_received | The sum of all the tips in the order in the shop currency. |
| client_details_user_agent | Details of the browsing client, including software and operating versions. |
| total_tax_set | The total tax applied to the order in shop and presentment currencies. |
| total_discounts_set | The total discounts applied to the price of the order in shop and presentment currencies. |
| presentment_currency | The presentment currency that was used to display prices to the customer. |
| source_relation | The ID of the order placed on the originating platform. This value doesn't correspond to the Shopify ID that's generated from a completed draft. |
| total_line_items_price_set | The total of all line item prices in shop and presentment currencies. |
| total_price_set | The total price of the order in shop and presentment currencies. |
| is_confirmed | Whether the order is confirmed. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
49. model.shopify_source.stg_shopify__order_adjustment
Model Description
Each record represents and adjustment to and order within Shopify.
SQL
-- this model will be all NULL until you have made an order adjustment in Shopify
with base as (
select *
from {{ ref('stg_shopify__order_adjustment_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__order_adjustment_tmp')),
staging_columns=get_order_adjustment_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as order_adjustment_id,
order_id,
refund_id,
amount,
amount_set,
tax_amount,
tax_amount_set,
kind,
reason,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| order_adjustment_id | The unique numeric identifier for the order adjustment. |
| order_id | Reference to the order which the adjustment is associated. |
| refund_id | Reference to the refund which the adjustment is associated. |
| amount | Amount of the adjustment. |
| tax_amount | Tax amount applied to the order adjustment in the shop currency. |
| kind | The kind of order adjustment (eg. refund, restock, etc.). |
| reason | The reason for the order adjustment. |
| amount_set | Amount set towards the order adjustment in presentment and shop currencies. |
| tax_amount_set | Tax amount set towards the order adjustment in the shop and presentment currencies. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
50. model.shopify_source.stg_shopify__order_discount_code
Model Description
Discount codes placed on orders.
SQL
with base as (
select *
from {{ ref('stg_shopify__order_discount_code_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__order_discount_code_tmp')),
staging_columns=get_order_discount_code_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
order_id,
index,
upper(code) as code,
type,
amount,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| amount | The amount that's deducted from the order total. |
| code | This property returns the discount code that was entered at checkout. Otherwise this property returns the title of the discount that was applied. |
| order_id | Associated order ID. |
| type | The type of discount - `fixed_amount`, `percentage`, or `shipping`. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
| index | Pairs with `order_id` to provide unique identifier for order discount code. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
51. model.shopify_source.stg_shopify__order_line
Model Description
Each record represents a line item from an order in Shopify.
SQL
with base as (
select *
from {{ ref('stg_shopify__order_line_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__order_line_tmp')),
staging_columns=get_order_line_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as order_line_id,
index,
name,
order_id,
fulfillable_quantity,
fulfillment_status,
gift_card as is_gift_card,
grams,
pre_tax_price,
pre_tax_price_set,
price,
price_set,
product_id,
quantity,
requires_shipping as is_shipping_required,
sku,
taxable as is_taxable,
tax_code,
title,
total_discount,
total_discount_set,
variant_id,
variant_title,
variant_inventory_management,
vendor,
properties,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
{{ fivetran_utils.fill_pass_through_columns('order_line_pass_through_columns') }}
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| fulfillable_quantity | The amount available to fulfill, calculated as follows: quantity - max(refunded_quantity, fulfilled_quantity) - pending_fulfilled_quantity - open_fulfilled_quantity |
| fulfillment_status | How far along an order is in terms line items fulfilled. |
| is_gift_card | Whether the item is a gift card. If true, then the item is not taxed or considered for shipping charges. |
| grams | The weight of the item in grams. |
| order_line_id | The ID of the line item. |
| name | The name of the product variant. |
| order_id | The ID of the related order. |
| price | The price of the item before discounts have been applied in the shop currency. |
| product_id | The ID of the product that the line item belongs to. Can be null if the original product associated with the order is deleted at a later date. |
| quantity | The number of items that were purchased. |
| is_shipping_required | Whether the item requires shipping. |
| sku | The item's SKU (stock keeping unit). |
| is_taxable | Whether the item was taxable. |
| title | The title of the product. |
| total_discount | The total amount of the discount allocated to the line item in the shop currency. |
| variant_id | The ID of the product variant. |
| vendor | The name of the item's supplier. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
| index | Index of the order line. |
| pre_tax_price | The pre tax price of the line item in shop currency. |
| pre_tax_price_set | The pre tax price of the line item in shop currency and presentment currency. |
| price_set | The price of the line item in shop and presentment currencies. |
| tax_code | Tax code applied to the line item. As multiple taxes can apply to a line item, we recommend referring to `stg_shopify__tax_line`. |
| total_discount_set | The total amount allocated to the line item in the presentment currency. |
| variant_title | The title of the product variant. |
| variant_inventory_management | The fulfillment service that tracks the number of items in stock for the product variant. |
| properties | Line item properties. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
52. model.shopify_source.stg_shopify__order_line_refund
Model Description
Each record represents a line item from an order in Shopify.
SQL
-- this model will be all NULL until you have made an order line refund in Shopify
with base as (
select *
from {{ ref('stg_shopify__order_line_refund_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__order_line_refund_tmp')),
staging_columns=get_order_line_refund_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as order_line_refund_id,
location_id,
order_line_id,
subtotal,
subtotal_set,
total_tax,
total_tax_set,
quantity,
refund_id,
restock_type,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
{{ fivetran_utils.fill_pass_through_columns('order_line_refund_pass_through_columns') }}
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| order_line_refund_id | The unique identifier of the line item in the refund. |
| location_id | TThe unique identifier of the location where the items will be restockedBD |
| order_line_id | The ID of the related line item in the order. |
| quantity | The quantity of the associated line item that was returned. |
| refund_id | The ID of the related refund. |
| restock_type | How this refund line item affects inventory levels. |
| subtotal | Subtotal amount of the order line refund in shop currency. |
| total_tax | The total tax applied to the refund in the shop currency. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
| subtotal_set | The subtotal of the refund line item in shop and presentment currencies. |
| total_tax_set | The total tax of the line item in shop and presentment currencies. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
53. model.shopify_source.stg_shopify__order_note_attribute
Model Description
Table storing custom attributes placed on orders.
SQL
with base as (
select *
from {{ ref('stg_shopify__order_note_attribute_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__order_note_attribute_tmp')),
staging_columns=get_order_note_attribute_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
order_id,
name,
value,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| name | Name of the attribute. |
| order_id | ID referencing the order the note attribute belongs to. |
| value | Value of the attribute. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
54. model.shopify_source.stg_shopify__order_shipping_line
Model Description
Represents the shipping details that the customer chose for their order.
SQL
with base as (
select *
from {{ ref('stg_shopify__order_shipping_line_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__order_shipping_line_tmp')),
staging_columns=get_order_shipping_line_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as order_shipping_line_id,
order_id,
carrier_identifier,
code,
delivery_category,
discounted_price,
discounted_price_set,
phone,
price,
price_set,
requested_fulfillment_service_id is not null as is_third_party_required,
source,
title,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| carrier_identifier | A reference to the carrier service that provided the rate. Present when the rate was computed by a third-party carrier service. |
| code | A reference to the shipping method. |
| delivery_category | The general classification of the delivery method. |
| discounted_price | The pre-tax shipping price with discounts applied in shop currency. |
| discounted_price_set | The pre-tax shipping price with discounts applied (JSON) in presentment and shop currencies. |
| order_shipping_line_id | A globally-unique identifier. |
| order_id | ID of the associated order. |
| phone | The phone number at the shipping address. |
| price | Returns the price of the shipping line in shop currency. |
| price_set | Returns the price of the shipping line (JSON) in shop and presentment currencies. |
| is_third_party_required | The fulfillment service requested for the shipping method. Present if the shipping method requires processing by a third party fulfillment service. |
| source | Returns the rate source for the shipping line. |
| title | Returns the title of the shipping line. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
55. model.shopify_source.stg_shopify__order_shipping_tax_line
Model Description
Represents taxes associated with order shipping lines.
SQL
with base as (
select *
from {{ ref('stg_shopify__order_shipping_tax_line_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__order_shipping_tax_line_tmp')),
staging_columns=get_order_shipping_tax_line_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
order_shipping_line_id,
index,
price,
price_set,
rate,
title,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| index | Index (from 1) representing the order of shipping lines per order. |
| order_shipping_line_id | ID of the order shipping line this record is associated with. |
| price | The amount of tax, in shop currency, after discounts and before returns. |
| price_set | The amount of tax, in shop and presentment currencies, after discounts and before returns (JSON). |
| rate | The proportion of the line item price that the tax represents as a decimal. |
| title | The name of the tax. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
56. model.shopify_source.stg_shopify__order_tag
Model Description
Tags that the shop owner has attached to the order.
SQL
with base as (
select *
from {{ ref('stg_shopify__order_tag_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__order_tag_tmp')),
staging_columns=get_order_tag_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
order_id,
index,
value,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| index | Index (starting from 1) representing when the tag was placed on the order. |
| order_id | ID of the order being tagged. |
| value | Value of the tag. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
57. model.shopify_source.stg_shopify__order_url_tag
Model Description
Key-value tags that the shop owner has attached to the order url.
SQL
with base as (
select *
from {{ ref('stg_shopify__order_url_tag_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__order_url_tag_tmp')),
staging_columns=get_order_url_tag_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
order_id,
key,
value,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| key | Key of the tag pair. |
| order_id | ID of the order url being tagged. |
| value | Value of the tag. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
58. model.shopify_source.stg_shopify__price_rule
Model Description
Table storing price rules. You can use price rules to create discounts dependent on conditions. Merchants can distribute the discount codes to their customers. Using price rules, you can create discounts that specify a discount as a percentage, a fixed amount, or free shipping. You use entitlements and prerequisites to dynamically build these discounts.
SQL
with base as (
select *
from {{ ref('stg_shopify__price_rule_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__price_rule_tmp')),
staging_columns=get_price_rule_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as price_rule_id,
allocation_limit,
allocation_method,
customer_selection,
once_per_customer as is_once_per_customer,
prerequisite_quantity_range as prereq_min_quantity,
prerequisite_shipping_price_range as prereq_max_shipping_price,
prerequisite_subtotal_range as prereq_min_subtotal,
prerequisite_to_entitlement_purchase_prerequisite_amount as prereq_min_purchase_quantity_for_entitlement,
quantity_ratio_entitled_quantity as prereq_buy_x_get_this,
quantity_ratio_prerequisite_quantity as prereq_buy_this_get_y,
target_selection,
target_type,
title,
usage_limit,
value,
value_type,
{{ dbt_date.convert_timezone(column='cast(starts_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as starts_at,
{{ dbt_date.convert_timezone(column='cast(ends_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as ends_at,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| allocation_limit | The number of times the discount can be allocated on the cart - if eligible. For example a Buy 1 hat Get 1 hat for free discount can be applied 3 times on a cart having more than 6 hats, where maximum of 3 hats get discounted - if the allocation_limit is 3. Empty (null) allocation_limit means unlimited number of allocations.\n |
| allocation_method | The allocation method of the price rule. Valid values include `each` (the discount is applied to each of the entitled items. For example, for a price rule that takes $15 off, each entitled line item in a checkout will be discounted by $15) and `across` (the calculated discount amount will be applied across the entitled items. For example, for a price rule that takes $15 off, the discount will be applied across all the entitled items).\n |
| created_at | The date and time (ISO 8601 format) when the price rule was created. |
| customer_selection | The customer selection for the price rule. Valid values include `all` (the price rule is valid for all customers) and `prerequisite` (the customer must either belong to one of the customer segments specified by customer_segment_prerequisite_ids, or be one of the customers specified by prerequisite_customer_ids).\n |
| ends_at | The date and time (ISO 8601 format) when the price rule ends. Must be after starts_at. |
| price_rule_id | The ID for the price rule. |
| is_once_per_customer | Boolean representing whether the generated discount code will be valid only for a single use per customer. This is tracked using customer ID. |
| prereq_min_quantity | If `customer_selection` is `prerequisite`, the minimum number of items for the price rule to be applicable. The quantity of an entitled cart item must be greater than or equal to this value. |
| prereq_max_shipping_price | If `customer_selection` is `prerequisite`, the maximum shipping price for the price rule to be applicable. The shipping price must be less than or equal to this value |
| prereq_min_subtotal | If `customer_selection` is `prerequisite`, the minimum subtotal for the price rule to be applicable. The subtotal of the entitled cart items must be greater than or equal to this value for the discount to apply. |
| prereq_min_purchase_quantity_for_entitlement | If `customer_selection` is `prerequisite`, the prerequisite purchase for a Buy X Get Y discount. The minimum purchase amount required to be entitled to the discount. |
| prereq_buy_x_get_this | If `customer_selection` is `prerequisite`, in a Buy/Get ratio for a Buy X Get Y discount, this is the offered 'get' quantity. |
| prereq_buy_this_get_y | If `customer_selection` is `prerequisite`, in a Buy/Get ratio for a Buy X Get Y discount, this defines the necessary 'buy' quantity. |
| starts_at | The date and time (ISO 8601 format) when the price rule starts. |
| target_selection | The target selection method of the price rule. Valid values include `all` (the price rule applies the discount to all line items in the checkout) and `entitled` (the price rule applies the discount to selected entitlements only).\n |
| target_type | The target type that the price rule applies to. Valid values include `line_item` (the price rule applies to the cart's line items) and `shipping_line` (the price rule applies to the cart's shipping lines). |
| title | The title of the price rule. This is used by the Shopify admin search to retrieve discounts. It is also displayed on the Discounts page of the Shopify admin for bulk discounts. Shopify recommends that this map onto the associated `discount_code.code`.\n |
| updated_at | The date and time (ISO 8601 format) when the price rule was updated. |
| usage_limit | The maximum number of times the price rule can be used, per discount code. |
| value | The value of the price rule. If if the value of `target_type` is `shipping_line`, then only -100 is accepted. The value must be negative. |
| value_type | The value type of the price rule. Valid values include `fixed_amount` (applies a discount of value as a unit of the store's currency. For example, if value is -30 and the store's currency is USD, then $30 USD is deducted when the discount is applied) and `percentage` (applies a percentage discount of value. For example, if value is -30, then 30% will be deducted when the discount is applied).\nIf `target_type` is `shipping_line`, then only `percentage` is accepted.\n |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
59. model.shopify_source.stg_shopify__product
Model Description
Each record represents a product in Shopify.
SQL
with base as (
select *
from {{ ref('stg_shopify__product_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__product_tmp')),
staging_columns=get_product_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as product_id,
handle,
product_type,
published_scope,
title,
vendor,
status,
_fivetran_deleted as is_deleted,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_timestamp,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_timestamp,
{{ dbt_date.convert_timezone(column='cast(published_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as published_timestamp,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
{{ fivetran_utils.fill_pass_through_columns('product_pass_through_columns') }}
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| is_deleted | Whether the record has been deleted in the source system. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| created_timestamp | The date and time when the product was created. |
| handle | A unique human-friendly string for the product. Automatically generated from the product's title. |
| product_id | An unsigned 64-bit integer that's used as a unique identifier for the product. Each id is unique across the Shopify system. No two products will have the same id, even if they're from different shops. |
| product_type | A categorization for the product used for filtering and searching products. |
| published_timestamp | The date and time (ISO 8601 format) when the product was published. Can be set to null to unpublish the product from the Online Store channel. |
| published_scope | Whether the product is published to the Point of Sale channel. |
| title | The name of the product. |
| updated_timestamp | The date and time when the product was last modified. |
| vendor | The name of the product's vendor. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
| status | The status of the product. Valid values: - active: The product is ready to sell and is available to customers on the online store, sales channels, and apps. By default, existing products are set to active. - archived: The product is no longer being sold and isn't available to customers on sales channels and apps. - draft: The product isn't ready to sell and is unavailable to customers on sales channels and apps. By default, duplicated and unarchived products are set to draft.\n |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
60. model.shopify_source.stg_shopify__product_image
Model Description
Table storing hosted images of products.
SQL
with base as (
select *
from {{ ref('stg_shopify__product_image_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__product_image_tmp')),
staging_columns=get_product_image_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as product_image_id,
product_id,
height,
position,
src,
variant_ids,
width,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
where not coalesce(_fivetran_deleted, false)
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| created_at | The date and time when the product image was created. The API returns this value in ISO 8601 format. |
| height | Height dimension of the image which is determined on upload. |
| product_image_id | Unique numeric identifier of the product image. |
| position | The order of the product image in the list. The first product image is at position 1 and is the "main" image for the product. |
| product_id | The id of the product associated with the image. |
| src | Specifies the location of the product image. This parameter supports URL filters that you can use to retrieve modified copies of the image. |
| updated_at | The date and time when the product image was last modified. The API returns this value in ISO 8601 format. |
| variant_ids | An array of variant ids associated with the image. |
| width | Width dimension of the image which is determined on upload. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
61. model.shopify_source.stg_shopify__product_tag
Model Description
Tags that the shop owner has attached to the product.
SQL
with base as (
select *
from {{ ref('stg_shopify__product_tag_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__product_tag_tmp')),
staging_columns=get_product_tag_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
product_id,
index,
value,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| index | Index (starting from 1) representing when the tag was placed on the product. |
| product_id | ID of the product being tagged. |
| value | Value of the tag. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
62. model.shopify_source.stg_shopify__product_variant
Model Description
Each record represents a product variant in Shopify
SQL
with base as (
select *
from {{ ref('stg_shopify__product_variant_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__product_variant_tmp')),
staging_columns=get_product_variant_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as variant_id,
product_id,
inventory_item_id,
image_id,
title,
price,
sku,
position,
inventory_policy,
compare_at_price,
fulfillment_service,
inventory_management,
taxable as is_taxable,
barcode,
grams,
coalesce(inventory_quantity, old_inventory_quantity) as inventory_quantity,
weight,
weight_unit,
option_1,
option_2,
option_3,
tax_code,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_timestamp,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_timestamp,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
{{ fivetran_utils.fill_pass_through_columns('product_variant_pass_through_columns') }}
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| barcode | The barcode, UPC, or ISBN number for the product. |
| compare_at_price | The original price of the item before an adjustment or a sale in shop currency |
| created_timestamp | The date and time (ISO 8601 format) when the product variant was created. |
| fulfillment_service | The fulfillment service associated with the product variant. |
| grams | The weight of the product variant in grams. |
| variant_id | The unique numeric identifier for the product variant. |
| image_id | The unique numeric identifier for a product's image. The image must be associated to the same product as the variant. |
| inventory_item_id | The unique identifier for the inventory item, which is used in the Inventory API to query for inventory information. |
| inventory_management | The fulfillment service that tracks the number of items in stock for the product variant. |
| inventory_policy | Whether customers are allowed to place an order for the product variant when it's out of stock. |
| inventory_quantity | An aggregate of inventory across all locations. To adjust inventory at a specific location, use the InventoryLevel resource. |
| option_1 | The custom properties that a shop owner uses to define product variants. You can define three options for a product variant: option1, option2, option3. |
| option_2 | The custom properties that a shop owner uses to define product variants. You can define three options for a product variant: option1, option2, option3. |
| option_3 | The custom properties that a shop owner uses to define product variants. You can define three options for a product variant: option1, option2, option3. |
| position | The order of the product variant in the list of product variants. The first position in the list is 1. The position of variants is indicated by the order in which they are listed. |
| price | The price of the product variant. |
| product_id | The unique numeric identifier for the product. |
| sku | A unique identifier for the product variant in the shop. Required in order to connect to a FulfillmentService. |
| is_taxable | Whether a tax is charged when the product variant is sold. |
| tax_code | This parameter applies only to the stores that have the Avalara AvaTax app installed. Specifies the Avalara tax code for the product variant. |
| title | The title of the product variant. The title field is a concatenation of the option1, option2, and option3 fields. You can only update title indirectly using the option fields. |
| updated_timestamp | The date and time when the product variant was last modified. Gets returned in ISO 8601 format. |
| weight | The weight of the product variant in the unit system specified with weight_unit. |
| weight_unit | The unit of measurement that applies to the product variant's weight. If you don't specify a value for weight_unit, then the shop's default unit of measurement is applied. Valid values: g, kg, oz, and lb. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
63. model.shopify_source.stg_shopify__refund
Model Description
Each record represents a refund within Shopify.
SQL
-- this model will be all NULL until you have made a refund in Shopify
with base as (
select *
from {{ ref('stg_shopify__refund_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__refund_tmp')),
staging_columns=get_refund_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as refund_id,
note,
order_id,
restock,
total_duties_set,
user_id,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(processed_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as processed_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| refund_id | The unique numeric identifier for the refund. |
| created_at | Timestamp of the date when the refund was created. |
| processed_at | Timestamp of the date when the refund was processed. |
| note | User generated note attached to the refund. |
| restock | Boolean indicating if the refund is a result of a restock. |
| user_id | Reference to the user id which generated the refund. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| total_duties_set | Record representing total duties set for the refund. |
| order_id | Reference to the order which the refund is associated. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
64. model.shopify_source.stg_shopify__shop
Model Description
The Shop resource is a collection of general business and store management settings and information about the store.
SQL
with base as (
select *
from {{ ref('stg_shopify__shop_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__shop_tmp')),
staging_columns=get_shop_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as shop_id,
name,
_fivetran_deleted as is_deleted,
address_1,
address_2,
city,
province,
province_code,
country,
country_code,
country_name,
zip,
latitude,
longitude,
case when county_taxes is null then false else county_taxes end as has_county_taxes,
currency,
enabled_presentment_currencies,
customer_email,
email,
domain,
phone,
timezone,
iana_timezone,
primary_locale,
weight_unit,
myshopify_domain,
cookie_consent_level,
shop_owner,
source,
tax_shipping as has_shipping_taxes,
case when taxes_included is null then false else taxes_included end as has_taxes_included_in_price,
has_discounts,
has_gift_cards,
has_storefront,
checkout_api_supported as has_checkout_api_supported,
eligible_for_card_reader_giveaway as is_eligible_for_card_reader_giveaway,
eligible_for_payments as is_eligible_for_payments,
google_apps_domain,
case when google_apps_login_enabled is null then false else google_apps_login_enabled end as is_google_apps_login_enabled,
money_format,
money_in_emails_format,
money_with_currency_format,
money_with_currency_in_emails_format,
plan_display_name,
plan_name,
password_enabled as is_password_enabled,
pre_launch_enabled as is_pre_launch_enabled,
requires_extra_payments_agreement as is_extra_payments_agreement_required,
setup_required as is_setup_required,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_at,
{{ dbt_date.convert_timezone(column='cast(updated_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as updated_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| is_deleted | Boolean representing whether the record was soft-deleted in Shopify. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| address_1 | The shop's street address. |
| address_2 | The optional second line of the shop's street address. |
| has_checkout_api_supported | Boolean representing whether the shop is capable of accepting payments directly through the Checkout API. |
| city | The shop's city. |
| cookie_consent_level | The cookie consent level defined on the shop's online store. |
| country | The shop's country. In most cases, this value matches the country_code. |
| country_code | The two-letter country code corresponding to the shop's country. |
| country_name | The shop's normalized country name. |
| has_county_taxes | Boolean representing whether the shop is applying taxes on a per-county basis. Only applicable to shops based in the US. Either `true` or `null` (not false, according to Shopify API docs). |
| created_at | The date and time (ISO 8601) when the shop was created. |
| currency | The three-letter code (ISO 4217 format) for the shop's default currency. |
| customer_email | The contact email used for communication between the shop owner and the customer. |
| domain | The shop's domain. |
| is_eligible_for_card_reader_giveaway | Boolean representing whether the shop is eligible to receive a free credit card reader from Shopify. |
| is_eligible_for_payments | Boolean representing whether the shop is eligible to use Shopify Payments. |
| The contact email used for communication between Shopify and the shop owner. | |
| enabled_presentment_currencies | An array of of enabled currencies (ISO 4217 format) that the shop accepts. Merchants can enable currencies from their Shopify Payments settings in the Shopify Admin. |
| google_apps_domain | The GSuite URL for the store, if applicable. |
| is_google_apps_login_enabled | Boolean representing whether the GSuite login is enabled. Shops with this feature will be able to log in through the GSuite login page. Valid values are `true` and `null`. |
| has_discounts | Boolean representing whether any active discounts exist for the shop. |
| has_gift_cards | Boolean representing whether any active gift cards exist for the shop. |
| has_storefront | Boolean representing whether this shop has an online store. |
| iana_timezone | The name of the timezone assigned by the [IANA](https://www.iana.org/time-zones). |
| shop_id | The ID for the shop. A 64-bit unsigned integer. |
| latitude | The latitude of the shop's location. |
| longitude | The longitude of the shop's location. |
| money_format | A string representing the way currency is formatted when the currency isn't specified. |
| money_in_emails_format | A string representing the way currency is formatted in email notifications when the currency isn't specified. |
| money_with_currency_format | A string representing the way currency is formatted when the currency is specified. |
| money_with_currency_in_emails_format | A string representing the way currency is formatted in email notifications when the currency is specified. |
| myshopify_domain | The shop's .myshopify.com domain. |
| name | The name of the shop. |
| is_password_enabled | Boolean representing whether the password protection page is enabled on the shop's online store. |
| phone | The contact phone number for the shop. |
| plan_display_name | The display name of the Shopify plan the shop is on. |
| plan_name | The name of the Shopify plan the shop is on. |
| is_pre_launch_enabled | Boolean representing whether the pre-launch page is enabled on the shop's online store. |
| primary_locale | The shop's primary locale, as configured in the language settings of the shop's theme. |
| province | The shop's normalized province or state name. |
| province_code | The two- or three-letter code for the shop's province or state. |
| is_extra_payments_agreement_required | Boolean representing whether the shop requires an extra Shopify Payments agreement. |
| is_setup_required | Boolean representing whether the shop has any outstanding setup steps. |
| shop_owner | The username of the shop owner. |
| source | The handle of the partner account that referred the merchant to Shopify, if applicable. |
| has_shipping_taxes | Boolean representing whether taxes are charged for shipping. Valid values are true or false. |
| has_taxes_included_in_price | Boolean representing whether applicable taxes are included in product prices. Valid values are true or null. |
| timezone | The name of the timezone the shop is in. |
| updated_at | The date and time (ISO 8601) when the shop was last updated. |
| weight_unit | The default unit of weight measurement for the shop. |
| zip | The shop's zip or postal code. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
65. model.shopify_source.stg_shopify__tax_line
Model Description
Represents a single tax applied to the associated line item.
SQL
with base as (
select *
from {{ ref('stg_shopify__tax_line_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__tax_line_tmp')),
staging_columns=get_tax_line_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
index,
order_line_id,
price,
price_set,
rate,
title,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| index | The index of the tax line. |
| order_line_id | The order line that this tax line is associated with. |
| price | The amount of tax, in shop currency, after discounts and before returns. |
| price_set | The amount of tax, in shop and presentment currencies, after discounts and before returns. |
| rate | The proportion of the line item price that the tax represents as a decimal. |
| title | The name of the tax. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
66. model.shopify_source.stg_shopify__tender_transaction
Model Description
Each tender transaction represents money passing between the merchant and a customer. A tender transaction with a positive amount represents a transaction where the customer paid money to the merchant. A negative amount represents a transaction where the merchant refunded money back to the customer. Tender transactions represent transactions that modify the shop's balance.
SQL
with base as (
select *
from {{ ref('stg_shopify__tender_transaction_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__tender_transaction_tmp')),
staging_columns=get_tender_transaction_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as transaction_id,
order_id,
amount,
currency,
payment_method,
remote_reference,
user_id,
{{ dbt_date.convert_timezone(column='cast(processed_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as processed_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
from fields
where not coalesce(test, false)
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| amount | The amount of the tender transaction in the shop's currency. |
| currency | The three-letter code (ISO 4217 format) for the currency used for the tender transaction. |
| transaction_id | The ID of the transaction. |
| order_id | The ID of the order that the tender transaction belongs to. |
| payment_method | Information about the payment method used for this transaction. Valid values include: - credit_card - cash - android_pay - apple_pay - google_pay - samsung_pay - shopify_pay - amazon - klarna - paypal - unknown - other\n |
| processed_at | The date and time (ISO 8601 format) when the tender transaction was processed. |
| remote_reference | The remote (gateway) reference associated with the tender. |
| user_id | The ID of the user logged into the Shopify POS device that processed the tender transaction, if applicable. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
67. model.shopify_source.stg_shopify__transaction
Model Description
Each record represents a transaction in Shopify.
SQL
with base as (
select * from {{ ref('stg_shopify__transaction_tmp') }}
),
fields as (
select
{{
fivetran_utils.fill_staging_columns(
source_columns=adapter.get_columns_in_relation(ref('stg_shopify__transaction_tmp')),
staging_columns=get_transaction_columns()
)
}}
{{ fivetran_utils.source_relation(
union_schema_variable='shopify_union_schemas',
union_database_variable='shopify_union_databases')
}}
from base
),
final as (
select
id as transaction_id,
order_id,
refund_id,
amount,
device_id,
gateway,
source_name,
message,
currency,
location_id,
parent_id,
payment_avs_result_code,
payment_credit_card_bin,
payment_cvv_result_code,
payment_credit_card_number,
payment_credit_card_company,
kind,
receipt,
currency_exchange_id,
currency_exchange_adjustment,
currency_exchange_original_amount,
currency_exchange_final_amount,
currency_exchange_currency,
error_code,
status,
user_id,
authorization_code,
{{ dbt_date.convert_timezone(column='cast(created_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as created_timestamp,
{{ dbt_date.convert_timezone(column='cast(processed_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as processed_timestamp,
{{ dbt_date.convert_timezone(column='cast(authorization_expires_at as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as authorization_expires_at,
{{ dbt_date.convert_timezone(column='cast(_fivetran_synced as ' ~ dbt.type_timestamp() ~ ')', target_tz=var('shopify_timezone', "UTC"), source_tz="UTC") }} as _fivetran_synced,
source_relation
{{ fivetran_utils.fill_pass_through_columns('transaction_pass_through_columns') }}
from fields
where not coalesce(test, false)
)
select *
from final
Column Description
| column_name | description |
|---|---|
| transaction_id | The ID for the transaction. |
| order_id | The ID for the order that the transaction is associated with. |
| refund_id | The ID associated with a refund in the refund table. |
| amount | The amount of money included in the transaction. |
| authorization_code | The authorization code associated with the transaction. |
| created_timestamp | The date and time when the transaction was created. |
| processed_timestamp | The date and time when a transaction was processed. |
| device_id | The ID for the device. |
| gateway | The name of the gateway the transaction was issued through. |
| source_name | The origin of the transaction. |
| message | A string generated by the payment provider with additional information about why the transaction succeeded or failed. |
| currency | The three-letter code (ISO 4217 format) for the currency used for the payment. |
| location_id | The ID of the physical location where the transaction was processed. |
| parent_id | The ID of an associated transaction. |
| payment_avs_result_code | The response code from the address verification system. |
| payment_credit_card_bin | The issuer identification number (IIN), formerly known as bank identification number (BIN) of the customer's credit card. |
| payment_cvv_result_code | The response code from the credit card company indicating whether the customer entered the card security code, or card verification value, correctly. |
| payment_credit_card_number | The customer's credit card number, with most of the leading digits redacted. |
| payment_credit_card_company | The name of the company that issued the customer's credit card. |
| kind | The transaction's type. |
| receipt | A transaction receipt attached to the transaction by the gateway. |
| currency_exchange_id | The ID of the adjustment. |
| currency_exchange_adjustment | The difference between the amounts on the associated transaction and the parent transaction. |
| currency_exchange_original_amount | The amount of the parent transaction in the shop currency. |
| currency_exchange_final_amount | The amount of the associated transaction in the shop currency. |
| currency_exchange_currency | The shop currency. |
| error_code | A standardized error code, independent of the payment provider. |
| status | The status of the transaction. |
| test | Whether the transaction is a test transaction. |
| user_id | The ID for the user who was logged into the Shopify POS device when the order was processed, if applicable. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
| authorization_expires_at | The date and time (ISO 8601 format) when the Shopify Payments authorization expires. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
68. model.shopify.int_shopify__daily_abandoned_checkouts
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with abandoned_checkout as (
select *
from {{ var('shopify_abandoned_checkout') }}
-- "deleted" abandoned checkouts do not appear to have any data tying them to customers,
-- discounts, or products (and should therefore not get joined in) but let's filter them out here
where not coalesce(is_deleted, false)
),
abandoned_checkout_aggregates as (
select
source_relation,
cast({{ dbt.date_trunc('day','created_at') }} as date) as date_day,
count(distinct checkout_id) as count_abandoned_checkouts,
count(distinct customer_id) as count_customers_abandoned_checkout,
count(distinct email) as count_customer_emails_abandoned_checkout
from abandoned_checkout
group by 1,2
)
select *
from abandoned_checkout_aggregates
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
69. model.shopify.int_shopify__customer_email_rollup
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with customers as (
select
*,
row_number() over(
partition by {{ shopify.shopify_partition_by_cols('email', 'source_relation') }}
order by created_timestamp desc)
as customer_index
from {{ var('shopify_customer') }}
where email is not null -- nonsensical to include any null emails here
), customer_tags as (
select
*
from {{ var('shopify_customer_tag' )}}
), rollup_customers as (
select
-- fields to group by
lower(customers.email) as email,
customers.source_relation,
-- fields to string agg together
{{ fivetran_utils.string_agg("distinct cast(customers.customer_id as " ~ dbt.type_string() ~ ")", "', '") }} as customer_ids,
{{ fivetran_utils.string_agg("distinct cast(customers.phone as " ~ dbt.type_string() ~ ")", "', '") }} as phone_numbers,
{{ fivetran_utils.string_agg("distinct cast(customer_tags.value as " ~ dbt.type_string() ~ ")", "', '") }} as customer_tags,
-- fields to take aggregates of
min(customers.created_timestamp) as first_account_created_at,
max(customers.created_timestamp) as last_account_created_at,
max(customers.updated_timestamp) as last_updated_at,
max(customers.marketing_consent_updated_at) as marketing_consent_updated_at,
max(customers._fivetran_synced) as last_fivetran_synced,
-- take true if ever given for boolean fields
{{ fivetran_utils.max_bool("case when customers.customer_index = 1 then customers.is_tax_exempt else null end") }} as is_tax_exempt, -- since this changes every year
{{ fivetran_utils.max_bool("customers.is_verified_email") }} as is_verified_email
-- for all other fields, just take the latest value
{% set cols = adapter.get_columns_in_relation(ref('stg_shopify__customer')) %}
{% set except_cols = ['_fivetran_synced', 'email', 'source_relation', 'customer_id', 'phone', 'created_at',
'marketing_consent_updated_at', 'orders_count', 'total_spent', 'created_timestamp', 'updated_timestamp',
'is_tax_exempt', 'is_verified_email'] %}
{% for col in cols %}
{% if col.column|lower not in except_cols %}
, max(case when customers.customer_index = 1 then customers.{{ col.column }} else null end) as {{ col.column }}
{% endif %}
{% endfor %}
from customers
left join customer_tags
on customers.customer_id = customer_tags.customer_id
and customers.source_relation = customer_tags.source_relation
group by 1,2
)
select *
from rollup_customers
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
70. model.shopify.int_shopify__order__shipping_aggregates
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with order_shipping_line as (
select
order_id,
source_relation,
order_shipping_line_id,
sum(price) as shipping_price,
sum(discounted_price) as discounted_shipping_price
from {{ var('shopify_order_shipping_line') }}
group by 1,2,3
), order_shipping_tax_line as (
select
order_shipping_line_id,
source_relation,
sum(price) as shipping_tax
from {{ var('shopify_order_shipping_tax_line') }}
group by 1,2
), aggregated as (
select
order_shipping_line.order_id,
order_shipping_line.source_relation,
sum(order_shipping_line.shipping_price) as shipping_price,
sum(order_shipping_line.discounted_shipping_price) as discounted_shipping_price,
sum(order_shipping_tax_line.shipping_tax) as shipping_tax
from order_shipping_line
left join order_shipping_tax_line
on order_shipping_line.order_shipping_line_id = order_shipping_tax_line.order_shipping_line_id
and order_shipping_line.source_relation = order_shipping_tax_line.source_relation
group by 1,2
)
select *
from aggregated
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
71. model.shopify.shopify__orders__order_refunds
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with refunds as (
select *
from {{ var('shopify_refund') }}
), order_line_refunds as (
select *
from {{ var('shopify_order_line_refund') }}
), refund_join as (
select
refunds.refund_id,
refunds.created_at,
refunds.order_id,
refunds.user_id,
refunds.source_relation,
order_line_refunds.order_line_refund_id,
order_line_refunds.order_line_id,
order_line_refunds.restock_type,
order_line_refunds.quantity,
order_line_refunds.subtotal,
order_line_refunds.total_tax
from refunds
left join order_line_refunds
on refunds.refund_id = order_line_refunds.refund_id
and refunds.source_relation = order_line_refunds.source_relation
)
select *
from refund_join
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
72. model.shopify.shopify__transactions
Model Description
Each record represents a transaction in Shopify.
SQL
{{
config(
materialized='table' if target.type in ('bigquery', 'databricks', 'spark') else 'incremental',
unique_key='transactions_unique_id',
incremental_strategy='delete+insert' if target.type in ('postgres', 'redshift', 'snowflake') else 'merge',
cluster_by=['transaction_id']
)
}}
with transactions as (
select
*,
{{ dbt_utils.generate_surrogate_key(['source_relation', 'transaction_id'])}} as transactions_unique_id
from {{ var('shopify_transaction') }}
{% if is_incremental() %}
-- use created_timestamp instead of processed_at since a record could be created but not processed
where cast(created_timestamp as date) >= {{ shopify.shopify_lookback(from_date="max(cast(created_timestamp as date))", interval=var('lookback_window', 7), datepart='day') }}
{% endif %}
), tender_transactions as (
select *
from {{ var('shopify_tender_transaction') }}
), joined as (
select
transactions.*,
tender_transactions.payment_method,
parent_transactions.created_timestamp as parent_created_timestamp,
parent_transactions.kind as parent_kind,
parent_transactions.amount as parent_amount,
parent_transactions.status as parent_status
from transactions
left join tender_transactions
on transactions.transaction_id = tender_transactions.transaction_id
and transactions.source_relation = tender_transactions.source_relation
left join transactions as parent_transactions
on transactions.parent_id = parent_transactions.transaction_id
and transactions.source_relation = parent_transactions.source_relation
), exchange_rate as (
select
*,
coalesce(cast(nullif({{ fivetran_utils.json_parse("receipt",["charges","data",0,"balance_transaction","exchange_rate"]) }}, '') as {{ dbt.type_numeric() }} ),1) as exchange_rate,
coalesce(cast(nullif({{ fivetran_utils.json_parse("receipt",["charges","data",0,"balance_transaction","exchange_rate"]) }}, '') as {{ dbt.type_numeric() }} ),1) * amount as currency_exchange_calculated_amount
from joined
)
select *
from exchange_rate
Column Description
| column_name | description |
|---|---|
| transactions_unique_id | Unique key representing a transaction. Hashed on 'transaction_id' and 'source_relation'. |
| transaction_id | The ID for the transaction. |
| order_id | The ID for the order that the transaction is associated with. |
| refund_id | The ID associated with a refund in the refund table. |
| amount | The amount of money included in the transaction in shop currency. |
| authorization | The authorization code associated with the transaction. |
| created_timestamp | The date and time when the transaction was created. |
| processed_timestamp | The date and time when a transaction was processed. |
| device_id | The ID for the device. |
| gateway | The name of the gateway the transaction was issued through. |
| source_name | The origin of the transaction. |
| message | A string generated by the payment provider with additional information about why the transaction succeeded or failed. |
| currency | The three-letter code (ISO 4217 format) for the currency used for the payment. |
| location_id | The ID of the physical location where the transaction was processed. |
| parent_id | The ID of an associated transaction. |
| payment_avs_result_code | The response code from the address verification system. |
| payment_credit_card_bin | The issuer identification number (IIN), formerly known as bank identification number (BIN) of the customer's credit card. |
| payment_cvv_result_code | The response code from the credit card company indicating whether the customer entered the card security code, or card verification value, correctly. |
| payment_credit_card_number | The customer's credit card number, with most of the leading digits redacted. |
| payment_credit_card_company | The name of the company that issued the customer's credit card. |
| kind | The transaction's type. |
| receipt | A transaction receipt attached to the transaction by the gateway. |
| currency_exchange_id | The ID of the adjustment. |
| currency_exchange_adjustment | The difference between the amounts on the associated transaction and the parent transaction. |
| currency_exchange_original_amount | The amount of the parent transaction in the shop currency. |
| currency_exchange_final_amount | The amount of the associated transaction in the shop currency. |
| currency_exchange_currency | The shop currency. |
| error_code | A standardized error code, independent of the payment provider. |
| status | The status of the transaction. |
| user_id | The ID for the user who was logged into the Shopify POS device when the order was processed, if applicable. |
| _fivetran_synced | Timestamp of the date the record was synced by Fivetran. |
| exchange_rate | The exchange rate between the home currency and the currency of sale at the time of the transaction. |
| currency_exchange_calculated_amount | The total amount of the transaction with the currency exchange rate applied. |
| source_relation | The schema or database this record came from, if you are unioning multiple connectors. Null if not. |
| payment_method | Method of payment. |
| parent_created_timestamp | Created on timestamp of the parent transaction. |
| parent_kind | Kind of the parent transaction. |
| parent_amount | Amount of the parent transaction. |
| parent_status | Status of the parent transaction. |
| authorization_expires_at | Timestamp when the authorization expires. |
| authorization_code | The authorization code associated with the transaction. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
73. model.shopify.int_shopify__discounts__abandoned_checkouts
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with abandoned_checkout as (
select *
from {{ var('shopify_abandoned_checkout') }}
-- "deleted" abandoned checkouts do not appear to have any data tying them to customers,
-- discounts, or products (and should therefore not get joined in) but let's filter them out here
where not coalesce(is_deleted, false)
),
abandoned_checkout_discount_code as (
select *
from {{ var('shopify_abandoned_checkout_discount_code') }}
-- we need the TYPE of discount (shipping, percentage, fixed_amount) to avoid fanning out of joins
-- so filter out records that have this
where coalesce(type, '') != ''
),
abandoned_checkout_shipping_line as (
select *
from {{ var('shopify_abandoned_checkout_shipping_line') }}
),
roll_up_shipping_line as (
select
checkout_id,
source_relation,
sum(price) as price
from abandoned_checkout_shipping_line
group by 1,2
),
abandoned_checkouts_aggregated as (
select
abandoned_checkout_discount_code.code,
abandoned_checkout_discount_code.type,
abandoned_checkout_discount_code.source_relation,
sum(abandoned_checkout_discount_code.amount) as total_abandoned_checkout_discount_amount,
sum(coalesce(abandoned_checkout.total_line_items_price, 0)) as total_abandoned_checkout_line_items_price,
sum(coalesce(roll_up_shipping_line.price, 0)) as total_abandoned_checkout_shipping_price,
count(distinct customer_id) as count_abandoned_checkout_customers,
count(distinct email) as count_abandoned_checkout_customer_emails,
count(distinct abandoned_checkout.checkout_id) as count_abandoned_checkouts
from abandoned_checkout_discount_code
left join abandoned_checkout
on abandoned_checkout_discount_code.checkout_id = abandoned_checkout.checkout_id
and abandoned_checkout_discount_code.source_relation = abandoned_checkout.source_relation
left join roll_up_shipping_line
on roll_up_shipping_line.checkout_id = abandoned_checkout_discount_code.checkout_id
and roll_up_shipping_line.source_relation = abandoned_checkout_discount_code.source_relation
group by 1,2,3
)
select *
from abandoned_checkouts_aggregated
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
74. model.shopify.shopify__orders__order_line_aggregates
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
{{ config(materialized='table') }}
with order_line as (
select *
from {{ var('shopify_order_line') }}
), tax as (
select
*
from {{ var('shopify_tax_line') }}
), shipping as (
select
*
from {{ ref('int_shopify__order__shipping_aggregates')}}
), tax_aggregates as (
select
order_line_id,
source_relation,
sum(price) as price
from tax
group by 1,2
), order_line_aggregates as (
select
order_line.order_id,
order_line.source_relation,
count(*) as line_item_count,
sum(order_line.quantity) as order_total_quantity,
sum(tax_aggregates.price) as order_total_tax,
sum(order_line.total_discount) as order_total_discount
from order_line
left join tax_aggregates
on tax_aggregates.order_line_id = order_line.order_line_id
and tax_aggregates.source_relation = order_line.source_relation
group by 1,2
), final as (
select
order_line_aggregates.order_id,
order_line_aggregates.source_relation,
order_line_aggregates.line_item_count,
order_line_aggregates.order_total_quantity,
order_line_aggregates.order_total_tax,
order_line_aggregates.order_total_discount,
shipping.shipping_price as order_total_shipping,
shipping.discounted_shipping_price as order_total_shipping_with_discounts,
shipping.shipping_tax as order_total_shipping_tax
from order_line_aggregates
left join shipping
on shipping.order_id = order_line_aggregates.order_id
and shipping.source_relation = order_line_aggregates.source_relation
)
select *
from final
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
75. model.shopify.int_shopify__inventory_level__aggregates
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with order_lines as (
select *
from {{ var('shopify_order_line') }}
),
fulfillment as (
select *
from {{ var('shopify_fulfillment') }}
),
orders as (
select *
from {{ var('shopify_order') }}
where not coalesce(is_deleted, false)
),
refunds as (
select *
from {{ ref('shopify__orders__order_refunds') }}
), refunds_aggregated as (
select
order_line_id,
source_relation,
sum(quantity) as quantity,
sum(coalesce(subtotal, 0)) as subtotal
from refunds
group by 1,2
),
joined as (
select
order_lines.order_line_id,
order_lines.variant_id,
order_lines.source_relation,
fulfillment.location_id, -- location id is stored in fulfillment rather than order
orders.order_id,
orders.customer_id,
fulfillment.fulfillment_id,
lower(orders.email) as email,
order_lines.pre_tax_price,
order_lines.quantity,
orders.created_timestamp as order_created_timestamp,
fulfillment.status as fulfillment_status,
refunds_aggregated.subtotal as subtotal_sold_refunds,
refunds_aggregated.quantity as quantity_sold_refunds
from order_lines
join orders
on order_lines.order_id = orders.order_id
and order_lines.source_relation = orders.source_relation
join fulfillment
on orders.order_id = fulfillment.order_id
and orders.source_relation = fulfillment.source_relation
left join refunds_aggregated
on refunds_aggregated.order_line_id = order_lines.order_line_id
and refunds_aggregated.source_relation = order_lines.source_relation
),
aggregated as (
select
variant_id,
location_id,
source_relation,
sum(pre_tax_price) as subtotal_sold,
sum(quantity) as quantity_sold,
count(distinct order_id) as count_distinct_orders,
count(distinct customer_id) as count_distinct_customers,
count(distinct email) as count_distinct_customer_emails,
min(order_created_timestamp) as first_order_timestamp,
max(order_created_timestamp) as last_order_timestamp
{% for status in ['pending', 'open', 'success', 'cancelled', 'error', 'failure'] %}
, count(distinct case when fulfillment_status = '{{ status }}' then fulfillment_id end) as count_fulfillment_{{ status }}
{% endfor %}
, sum(coalesce(subtotal_sold_refunds, 0)) as subtotal_sold_refunds
, sum(coalesce(quantity_sold_refunds, 0)) as quantity_sold_refunds
from joined
{{ dbt_utils.group_by(n=3) }}
)
select *
from aggregated
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
76. model.shopify.int_shopify__products_with_aggregates
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with products as (
select *
from {{ var('shopify_product') }}
),
collection_product as (
select *
from {{ var('shopify_collection_product') }}
),
collection as (
select *
from {{ var('shopify_collection') }}
where not coalesce(is_deleted, false) -- limit to only active collections
),
product_tag as (
select *
from {{ var('shopify_product_tag') }}
),
product_variant as (
select *
from {{ var('shopify_product_variant') }}
),
product_image as (
select *
from {{ var('shopify_product_image') }}
),
collections_aggregated as (
select
collection_product.product_id,
collection_product.source_relation,
{{ fivetran_utils.string_agg(field_to_agg='collection.title', delimiter="', '") }} as collections
from collection_product
join collection
on collection_product.collection_id = collection.collection_id
and collection_product.source_relation = collection.source_relation
group by 1,2
),
tags_aggregated as (
select
product_id,
source_relation,
{{ fivetran_utils.string_agg(field_to_agg='value', delimiter="', '") }} as tags
from product_tag
group by 1,2
),
variants_aggregated as (
select
product_id,
source_relation,
count(variant_id) as count_variants
from product_variant
group by 1,2
),
images_aggregated as (
select
product_id,
source_relation,
count(*) as count_images
from product_image
group by 1,2
),
joined as (
select
products.*,
collections_aggregated.collections,
tags_aggregated.tags,
variants_aggregated.count_variants,
coalesce(images_aggregated.count_images, 0) > 0 as has_product_image
from products
left join collections_aggregated
on products.product_id = collections_aggregated.product_id
and products.source_relation = collections_aggregated.source_relation
left join tags_aggregated
on products.product_id = tags_aggregated.product_id
and products.source_relation = tags_aggregated.source_relation
left join variants_aggregated
on products.product_id = variants_aggregated.product_id
and products.source_relation = variants_aggregated.source_relation
left join images_aggregated
on products.product_id = images_aggregated.product_id
and products.source_relation = images_aggregated.source_relation
)
select *
from joined
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
77. model.shopify.shopify__order_lines
Model Description
Each record represents a line item of an order in Shopify.
SQL
{{
config(
materialized='table' if target.type in ('bigquery', 'databricks', 'spark') else 'incremental',
unique_key='order_lines_unique_key',
incremental_strategy='delete+insert' if target.type in ('postgres', 'redshift', 'snowflake') else 'merge',
cluster_by=['order_line_id']
)
}}
with order_lines as (
select
*,
{{ dbt_utils.generate_surrogate_key(['source_relation', 'order_line_id']) }} as order_lines_unique_key
from {{ var('shopify_order_line') }}
{% if is_incremental() %}
where cast(_fivetran_synced as date) >= {{ shopify.shopify_lookback(from_date="max(cast(_fivetran_synced as date))", interval=var('lookback_window', 3), datepart='day') }}
{% endif %}
), product_variants as (
select *
from {{ var('shopify_product_variant') }}
), refunds as (
select *
from {{ ref('shopify__orders__order_refunds') }}
), refunds_aggregated as (
select
order_line_id,
source_relation,
sum(quantity) as quantity,
sum(coalesce(subtotal, 0)) as subtotal,
{{ fivetran_utils.string_agg("distinct cast(refunds.restock_type as " ~ dbt.type_string() ~ ")", "', '") }} as restock_types
from refunds
group by 1,2
), tax_lines as (
select *
from {{ var('shopify_tax_line')}}
), tax_lines_aggregated as (
select
tax_lines.order_line_id,
tax_lines.source_relation,
sum(tax_lines.price) as order_line_tax
from tax_lines
group by 1,2
), joined as (
select
order_lines.*,
refunds_aggregated.restock_types,
coalesce(refunds_aggregated.quantity,0) as refunded_quantity,
coalesce(refunds_aggregated.subtotal,0) as refunded_subtotal,
order_lines.quantity - coalesce(refunds_aggregated.quantity,0) as quantity_net_refunds,
order_lines.pre_tax_price - coalesce(refunds_aggregated.subtotal,0) as subtotal_net_refunds,
product_variants.created_timestamp as variant_created_at,
product_variants.updated_timestamp as variant_updated_at,
product_variants.inventory_item_id,
product_variants.image_id,
product_variants.price as variant_price,
product_variants.sku as variant_sku,
product_variants.position as variant_position,
product_variants.inventory_policy as variant_inventory_policy,
product_variants.compare_at_price as variant_compare_at_price,
product_variants.fulfillment_service as variant_fulfillment_service,
product_variants.is_taxable as variant_is_taxable,
product_variants.barcode as variant_barcode,
product_variants.grams as variant_grams,
product_variants.inventory_quantity as variant_inventory_quantity,
product_variants.weight as variant_weight,
product_variants.weight_unit as variant_weight_unit,
product_variants.option_1 as variant_option_1,
product_variants.option_2 as variant_option_2,
product_variants.option_3 as variant_option_3,
product_variants.tax_code as variant_tax_code,
tax_lines_aggregated.order_line_tax
from order_lines
left join refunds_aggregated
on refunds_aggregated.order_line_id = order_lines.order_line_id
and refunds_aggregated.source_relation = order_lines.source_relation
left join product_variants
on product_variants.variant_id = order_lines.variant_id
and product_variants.source_relation = order_lines.source_relation
left join tax_lines_aggregated
on tax_lines_aggregated.order_line_id = order_lines.order_line_id
and tax_lines_aggregated.source_relation = order_lines.source_relation
)
select *
from joined
Column Description
| column_name | description |
|---|---|
| order_lines_unique_key | Unique key representing an order line. Hashed on 'order_line_id' and 'source_relation'. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| fulfillable_quantity | The amount available to fulfill, calculated as follows: quantity - max(refunded_quantity, fulfilled_quantity) - pending_fulfilled_quantity - open_fulfilled_quantity\n |
| fulfillment_status | How far along an order is in terms line items fulfilled. |
| is_gift_card | Whether the item is a gift card. If true, then the item is not taxed or considered for shipping charges. |
| grams | The weight of the item in grams. |
| order_line_id | The ID of the line item. |
| name | The name of the product variant. |
| order_id | The ID of the related order. |
| price | The price of the item before discounts have been applied in the shop currency. |
| product_id | The ID of the product that the line item belongs to. Can be null if the original product associated with the order is deleted at a later date. |
| quantity | The number of items that were purchased. |
| is_shipping_required | Whether the item requires shipping. |
| sku | The item's SKU (stock keeping unit). |
| is_taxable | Whether the item was taxable. |
| title | The title of the product. |
| total_discount | The total amount of the discount allocated to the line item in the shop currency. |
| variant_id | The ID of the product variant. |
| vendor | The name of the item's supplier. |
| refunded_quantity | Quantity of the item that has been refunded. |
| quantity_net_refunds | Quantity ordered, excluding refunds. |
| variant_barcode | The barcode, UPC, or ISBN number for the product. |
| variant_compare_at_price | The original price of the item before an adjustment or a sale in shop currency. |
| variant_created_at | The date and time (ISO 8601 format) when the product variant was created. |
| variant_fulfillment_service | The fulfillment service associated with the product variant. |
| variant_grams | The weight of the product variant in grams. |
| inventory_item_id | The unique identifier for the inventory item, which is used in the Inventory API to query for inventory information. |
| variant_inventory_management | The fulfillment service that tracks the number of items in stock for the product variant. |
| variant_inventory_policy | Whether customers are allowed to place an order for the product variant when it's out of stock. |
| variant_inventory_quantity | An aggregate of inventory across all locations. To adjust inventory at a specific location, use the InventoryLevel resource. |
| variant_option_1 | The custom properties that a shop owner uses to define product variants. You can define three options for a product variant: option1, option2, option3. |
| variant_option_2 | The custom properties that a shop owner uses to define product variants. You can define three options for a product variant: option1, option2, option3. |
| variant_option_3 | The custom properties that a shop owner uses to define product variants. You can define three options for a product variant: option1, option2, option3. |
| variant_position | The order of the product variant in the list of product variants. The first position in the list is 1. The position of variants is indicated by the order in which they are listed. |
| variant_price | The price of the product variant. |
| variant_sku | A unique identifier for the product variant in the shop. Required in order to connect to a FulfillmentService. |
| variant_is_taxable | Whether a tax is charged when the product variant is sold. |
| variant_tax_code | This parameter applies only to the stores that have the Avalara AvaTax app installed. Specifies the Avalara tax code for the product variant. |
| variant_title | The title of the product variant. The title field is a concatenation of the option1, option2, and option3 fields. You can only update title indirectly using the option fields. |
| variant_updated_at | The date and time when the product variant was last modified. Gets returned in ISO 8601 format. |
| variant_weight | The weight of the product variant in the unit system specified with weight_unit. |
| variant_weight_unit | The unit of measurement that applies to the product variant's weight. If you don't specify a value for weight_unit, then the shop's default unit of measurement is applied. Valid values: g, kg, oz, and lb. |
| refunded_subtotal | Subtotal amount of the refund applied to the order line in shop currency. |
| subtotal_net_refunds | Subtotal of the order line with refunds subtracted in shop currency. |
| image_id | Image id of the product variant associated with the order line. |
| source_relation | The schema or database this record came from, if you are unioning multiple connectors. Null if not. |
| restock_types | List of how this refund line item affects inventory levels. |
| order_line_tax | Total taxes for the line item. |
| index | The index associated with the order. |
| pre_tax_price | The total pre tax price of the order. |
| tax_code | This parameter applies only to the stores that have the Avalara AvaTax app installed. Specifies the Avalara tax code for the product variant. |
| pre_tax_price_set | The pre tax price of the line item in shop currency and presentment currency. |
| price_set | The price of the line item in shop and presentment currencies. |
| total_discount_set | The total amount allocated to the line item in the presentment currency. |
| properties | Line item properties. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
78. model.shopify.int_shopify__emails__order_aggregates
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with orders as (
select *
from {{ var('shopify_order') }}
), order_aggregates as (
select *
from {{ ref('shopify__orders__order_line_aggregates') }}
), transactions as (
select *
from {{ ref('shopify__transactions')}}
where lower(status) = 'success'
and lower(kind) not in ('authorization', 'void')
and lower(gateway) != 'gift_card' -- redeeming a giftcard does not introduce new revenue
), transaction_aggregates as (
-- this is necessary as customers can pay via multiple payment gateways
select
order_id,
source_relation,
lower(kind) as kind,
sum(currency_exchange_calculated_amount) as currency_exchange_calculated_amount
from transactions
{{ dbt_utils.group_by(n=3) }}
), customer_emails as (
-- in case any orders records don't have the customer email attached yet
select
customer_id,
source_relation,
email
from {{ var('shopify_customer') }}
where email is not null
{{ dbt_utils.group_by(n=3) }}
), aggregated as (
select
lower(customer_emails.email) as email,
orders.source_relation,
min(orders.created_timestamp) as first_order_timestamp,
max(orders.created_timestamp) as most_recent_order_timestamp,
avg(transaction_aggregates.currency_exchange_calculated_amount) as avg_order_value,
sum(transaction_aggregates.currency_exchange_calculated_amount) as lifetime_total_spent,
sum(refunds.currency_exchange_calculated_amount) as lifetime_total_refunded,
count(distinct orders.order_id) as lifetime_count_orders,
avg(order_aggregates.order_total_quantity) as avg_quantity_per_order,
sum(order_aggregates.order_total_tax) as lifetime_total_tax,
avg(order_aggregates.order_total_tax) as avg_tax_per_order,
sum(order_aggregates.order_total_discount) as lifetime_total_discount,
avg(order_aggregates.order_total_discount) as avg_discount_per_order,
sum(order_aggregates.order_total_shipping) as lifetime_total_shipping,
avg(order_aggregates.order_total_shipping) as avg_shipping_per_order,
sum(order_aggregates.order_total_shipping_with_discounts) as lifetime_total_shipping_with_discounts,
avg(order_aggregates.order_total_shipping_with_discounts) as avg_shipping_with_discounts_per_order,
sum(order_aggregates.order_total_shipping_tax) as lifetime_total_shipping_tax,
avg(order_aggregates.order_total_shipping_tax) as avg_shipping_tax_per_order
from orders
join customer_emails
on orders.customer_id = customer_emails.customer_id
and orders.source_relation = customer_emails.source_relation
left join transaction_aggregates
on orders.order_id = transaction_aggregates.order_id
and orders.source_relation = transaction_aggregates.source_relation
and transaction_aggregates.kind in ('sale','capture')
left join transaction_aggregates as refunds
on orders.order_id = refunds.order_id
and orders.source_relation = refunds.source_relation
and refunds.kind = 'refund'
left join order_aggregates
on orders.order_id = order_aggregates.order_id
and orders.source_relation = order_aggregates.source_relation
group by 1,2
)
select *
from aggregated
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
79. model.shopify.shopify__customers__order_aggregates
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with orders as (
select *
from {{ var('shopify_order') }}
where customer_id is not null
), order_aggregates as (
select *
from {{ ref('shopify__orders__order_line_aggregates') }}
), transactions as (
select *
from {{ ref('shopify__transactions')}}
where lower(status) = 'success'
and lower(kind) not in ('authorization', 'void')
and lower(gateway) != 'gift_card' -- redeeming a giftcard does not introduce new revenue
), transaction_aggregates as (
-- this is necessary as customers can pay via multiple payment gateways
select
order_id,
source_relation,
lower(kind) as kind,
sum(currency_exchange_calculated_amount) as currency_exchange_calculated_amount
from transactions
{{ dbt_utils.group_by(n=3) }}
), customer_tags as (
select *
from {{ var('shopify_customer_tag' )}}
), customer_tags_aggregated as (
select
customer_id,
source_relation,
{{ fivetran_utils.string_agg("distinct cast(value as " ~ dbt.type_string() ~ ")", "', '") }} as customer_tags
from customer_tags
group by 1,2
), aggregated as (
select
orders.customer_id,
orders.source_relation,
customer_tags_aggregated.customer_tags,
min(orders.created_timestamp) as first_order_timestamp,
max(orders.created_timestamp) as most_recent_order_timestamp,
avg(transaction_aggregates.currency_exchange_calculated_amount) as avg_order_value,
sum(transaction_aggregates.currency_exchange_calculated_amount) as lifetime_total_spent,
sum(refunds.currency_exchange_calculated_amount) as lifetime_total_refunded,
count(distinct orders.order_id) as lifetime_count_orders,
avg(order_aggregates.order_total_quantity) as avg_quantity_per_order,
sum(order_aggregates.order_total_tax) as lifetime_total_tax,
avg(order_aggregates.order_total_tax) as avg_tax_per_order,
sum(order_aggregates.order_total_discount) as lifetime_total_discount,
avg(order_aggregates.order_total_discount) as avg_discount_per_order,
sum(order_aggregates.order_total_shipping) as lifetime_total_shipping,
avg(order_aggregates.order_total_shipping) as avg_shipping_per_order,
sum(order_aggregates.order_total_shipping_with_discounts) as lifetime_total_shipping_with_discounts,
avg(order_aggregates.order_total_shipping_with_discounts) as avg_shipping_with_discounts_per_order,
sum(order_aggregates.order_total_shipping_tax) as lifetime_total_shipping_tax,
avg(order_aggregates.order_total_shipping_tax) as avg_shipping_tax_per_order
from orders
left join transaction_aggregates
on orders.order_id = transaction_aggregates.order_id
and orders.source_relation = transaction_aggregates.source_relation
and transaction_aggregates.kind in ('sale','capture')
left join transaction_aggregates as refunds
on orders.order_id = refunds.order_id
and orders.source_relation = refunds.source_relation
and refunds.kind = 'refund'
left join order_aggregates
on orders.order_id = order_aggregates.order_id
and orders.source_relation = order_aggregates.source_relation
left join customer_tags_aggregated
on orders.customer_id = customer_tags_aggregated.customer_id
and orders.source_relation = customer_tags_aggregated.source_relation
{{ dbt_utils.group_by(n=3) }}
)
select *
from aggregated
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
80. model.shopify.shopify__inventory_levels
Model Description
Model representing the current status of an inventory level in Shopify. Inventory Levels are unique pairings of inventory items (which have a 1:1 relationship with product variants) and locations.
Inventory Level = Inventory item @ a Location.
SQL
with inventory_level as (
select *
from {{ var('shopify_inventory_level') }}
),
inventory_item as (
select *
from {{ var('shopify_inventory_item') }}
),
location as (
select *
from {{ var('shopify_location') }}
),
product_variant as (
select *
from {{ var('shopify_product_variant') }}
),
product as (
select *
from {{ var('shopify_product') }}
),
inventory_level_aggregated as (
select *
from {{ ref('int_shopify__inventory_level__aggregates') }}
),
joined_info as (
select
inventory_level.*,
inventory_item.sku,
inventory_item.is_deleted as is_inventory_item_deleted,
inventory_item.cost,
inventory_item.country_code_of_origin,
inventory_item.province_code_of_origin,
inventory_item.is_shipping_required,
inventory_item.is_inventory_quantity_tracked,
inventory_item.created_at as inventory_item_created_at,
inventory_item.updated_at as inventory_item_updated_at,
location.name as location_name,
location.is_deleted as is_location_deleted,
location.is_active as is_location_active,
location.address_1,
location.address_2,
location.city,
location.country,
location.country_code,
location.is_legacy as is_legacy_location,
location.province,
location.province_code,
location.phone,
location.zip,
location.created_at as location_created_at,
location.updated_at as location_updated_at,
product_variant.variant_id,
product_variant.product_id,
product_variant.title as variant_title,
product_variant.inventory_policy as variant_inventory_policy,
product_variant.price as variant_price,
product_variant.image_id as variant_image_id,
product_variant.fulfillment_service as variant_fulfillment_service,
product_variant.inventory_management as variant_inventory_management,
product_variant.is_taxable as is_variant_taxable,
product_variant.barcode as variant_barcode,
product_variant.grams as variant_grams,
product_variant.inventory_quantity as variant_inventory_quantity,
product_variant.weight as variant_weight,
product_variant.weight_unit as variant_weight_unit,
product_variant.option_1 as variant_option_1,
product_variant.option_2 as variant_option_2,
product_variant.option_3 as variant_option_3,
product_variant.tax_code as variant_tax_code,
product_variant.created_timestamp as variant_created_at,
product_variant.updated_timestamp as variant_updated_at
{{ fivetran_utils.persist_pass_through_columns('product_variant_pass_through_columns', identifier='product_variant') }}
from inventory_level
join inventory_item
on inventory_level.inventory_item_id = inventory_item.inventory_item_id
and inventory_level.source_relation = inventory_item.source_relation
join location
on inventory_level.location_id = location.location_id
and inventory_level.source_relation = location.source_relation
join product_variant
on inventory_item.inventory_item_id = product_variant.inventory_item_id
and inventory_item.source_relation = product_variant.source_relation
),
joined_aggregates as (
select
joined_info.*,
coalesce(inventory_level_aggregated.subtotal_sold, 0) as subtotal_sold,
coalesce(inventory_level_aggregated.quantity_sold, 0) as quantity_sold,
coalesce(inventory_level_aggregated.count_distinct_orders, 0) as count_distinct_orders,
coalesce(inventory_level_aggregated.count_distinct_customers, 0) as count_distinct_customers,
coalesce(inventory_level_aggregated.count_distinct_customer_emails, 0) as count_distinct_customer_emails,
inventory_level_aggregated.first_order_timestamp,
inventory_level_aggregated.last_order_timestamp,
coalesce(inventory_level_aggregated.subtotal_sold_refunds, 0) as subtotal_sold_refunds,
coalesce(inventory_level_aggregated.quantity_sold_refunds, 0) as quantity_sold_refunds
{% for status in ['pending', 'open', 'success', 'cancelled', 'error', 'failure'] %}
, coalesce(count_fulfillment_{{ status }}, 0) as count_fulfillment_{{ status }}
{% endfor %}
from joined_info
left join inventory_level_aggregated
on joined_info.location_id = inventory_level_aggregated.location_id
and joined_info.variant_id = inventory_level_aggregated.variant_id
and joined_info.source_relation = inventory_level_aggregated.source_relation
),
final as (
select
*,
subtotal_sold - subtotal_sold_refunds as net_subtotal_sold,
quantity_sold - quantity_sold_refunds as net_quantity_sold
from joined_aggregates
)
select *
from final
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| available_quantity | The available quantity of an inventory item at the inventory level's associated location. Returns null if the inventory item is not tracked. |
| inventory_item_id | The ID of the inventory item associated with the inventory level. |
| location_id | The ID of the location that the inventory level belongs to. |
| updated_at | The date and time (ISO 8601 format) when the inventory level was last modified. |
| is_inventory_item_deleted | Boolean representing whether the record was soft-deleted in Shopify. |
| sku | The unique SKU (stock keeping unit) of the inventory item. |
| cost | The unit cost of the inventory item. The shop's default currency is used. |
| province_code_of_origin | The province code (ISO 3166-2 alpha-2) of where the item came from. The province code is only used if the shipping provider for the inventory item is Canada Post. |
| country_code_of_origin | The country code (ISO 3166-1 alpha-2) of where the item came from. |
| is_shipping_required | Boolean representing whether a customer needs to provide a shipping address when placing an order containing the inventory item. |
| is_inventory_quantity_tracked | Boolean representing whether inventory levels are tracked for the item. If true, then the inventory quantity changes are tracked by Shopify. |
| inventory_item_created_at | The date and time (ISO 8601 format) when the inventory item was created. |
| inventory_item_updated_at | The date and time (ISO 8601 format) when the inventory item was last modified. |
| is_location_deleted | Boolean representing whether the record was soft-deleted in Shopify. |
| is_location_active | Boolean representing whether the location is active. If true, then the location can be used to sell products, stock inventory, and fulfill orders.\n |
| address_1 | The location's street address. |
| address_2 | The optional second line of the location's street address. |
| city | The city the location is in. |
| country | The country the location is in (two-letter code). |
| country_code | The two-letter code (ISO 3166-1 alpha-2 format) corresponding to country the location is in. |
| location_created_at | The date and time (ISO 8601 format) when the location was created. |
| is_legacy_location | Boolean representing whether this is a fulfillment service location. If true, then the location is a fulfillment service location. If false, then the location was created by the merchant and isn't tied to a fulfillment service.\n |
| location_name | The name of the location. |
| phone | The phone number of the location. This value can contain special characters, such as - or +. |
| province | The province, state, or district of the location. |
| province_code | The province, state, or district code (ISO 3166-2 alpha-2 format) of the location. |
| location_updated_at | The date and time (ISO 8601 format) when the location was last updated. |
| zip | The zip or postal code. |
| variant_barcode | The barcode, UPC, or ISBN number for the product. |
| variant_created_at | The date and time (ISO 8601 format) when the product variant was created. |
| variant_fulfillment_service | The fulfillment service associated with the product variant. |
| variant_grams | The weight of the product variant in grams. |
| variant_id | The unique numeric identifier for the product variant. |
| variant_image_id | The unique numeric identifier for a product's image. The image must be associated to the same product as the variant. |
| variant_inventory_management | The fulfillment service that tracks the number of items in stock for the product variant. |
| variant_inventory_policy | Whether customers are allowed to place an order for the product variant when it's out of stock. |
| variant_inventory_quantity | An aggregate of inventory across all locations. To adjust inventory at a specific location, use the InventoryLevel resource. |
| variant_option_1 | The custom properties that a shop owner uses to define product variants. You can define three options for a product variant: option1, option2, option3. |
| variant_option_2 | The custom properties that a shop owner uses to define product variants. You can define three options for a product variant: option1, option2, option3. |
| variant_option_3 | The custom properties that a shop owner uses to define product variants. You can define three options for a product variant: option1, option2, option3. |
| variant_price | The price of the product variant in shop currency. |
| product_id | The unique numeric identifier for the product. |
| is_variant_taxable | Whether a tax is charged when the product variant is sold. |
| variant_tax_code | This parameter applies only to the stores that have the Avalara AvaTax app installed. Specifies the Avalara tax code for the product variant. |
| variant_title | The title of the product variant. The title field is a concatenation of the option1, option2, and option3 fields. You can only update title indirectly using the option fields. |
| variant_updated_at | The date and time when the product variant was last modified. Gets returned in ISO 8601 format. |
| variant_weight | The weight of the product variant in the unit system specified with weight_unit. |
| variant_weight_unit | The unit of measurement that applies to the product variant's weight. If you don't specify a value for weight_unit, then the shop's default unit of measurement is applied. Valid values: g, kg, oz, and lb. |
| subtotal_sold | Total amount (monetary, in shop currency) sold from the inventory level. Includes refunds. |
| quantity_sold | Total quantity sold from the inventory level. Includes refunds. |
| count_distinct_orders | The number of distinct orders placed from this inventory level. |
| count_distinct_customers | The number of distinct customers (based on customer_id) that have placed orders against this inventory level. |
| count_distinct_customer_emails | The number of distinct customer emails that have placed orders against this inventory level. |
| first_order_timestamp | The timetamp of the first order against this inventory level. |
| last_order_timestamp | The timestamp of the first order against this inventory level. |
| subtotal_sold_refunds | The monetary amount (in shop currency) of inventory level goods that have been refunded. |
| quantity_sold_refunds | The quantity of inventory level goods that have been refunded. |
| net_subtotal_sold | Net monetary amount sold (in shop currency) from the inventory level. Excludes refunds. |
| net_quantity_sold | Net quantity sold from this inventory level. Excludes refunds. |
| count_fulfillment_pending | Count of currently pending distinct fulfillments for this inventory level. "Pending" = Shopify has created the fulfillment and is waiting for the third-party fulfillment service to transition it to 'open' or 'success'.\n |
| count_fulfillment_open | Count of currently open distinct fulfillments for this inventory level. "Open" = The fulfillment has been acknowledged by the service and is in processing.\n |
| count_fulfillment_success | Count of successful distinct fulfillments for this inventory level. "Success" = The fulfillment was successful.\n |
| count_fulfillment_cancelled | Count of cancelled distinct fulfillments for this inventory level. "Cancelled" = The fulfillment was cancelled.\n |
| count_fulfillment_error | Count of distinct fulfillments for this inventory level that encountered an error. "Error" = There was an error with the fulfillment request.\n |
| count_fulfillment_failure | Count of distinct fulfillments for this inventory level that failed. "Failure" = The fulfillment request failed.\n |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
81. model.shopify.shopify__orders
Model Description
Each record represents an order in Shopify.
SQL
{{
config(
materialized='table' if target.type in ('bigquery', 'databricks', 'spark') else 'incremental',
unique_key='orders_unique_key',
incremental_strategy='delete+insert' if target.type in ('postgres', 'redshift', 'snowflake') else 'merge',
cluster_by=['order_id']
)
}}
with orders as (
select
*,
{{ dbt_utils.generate_surrogate_key(['source_relation', 'order_id']) }} as orders_unique_key
from {{ var('shopify_order') }}
{% if is_incremental() %}
where cast(coalesce(updated_timestamp, created_timestamp) as date) >= {{ shopify.shopify_lookback(
from_date="max(cast(coalesce(updated_timestamp, created_timestamp) as date))",
interval=var('lookback_window', 7),
datepart='day') }}
{% endif %}
), order_lines as (
select *
from {{ ref('shopify__orders__order_line_aggregates') }}
), order_adjustments as (
select *
from {{ var('shopify_order_adjustment') }}
), order_adjustments_aggregates as (
select
order_id,
source_relation,
sum(amount) as order_adjustment_amount,
sum(tax_amount) as order_adjustment_tax_amount
from order_adjustments
group by 1,2
), refunds as (
select *
from {{ ref('shopify__orders__order_refunds') }}
), refund_aggregates as (
select
order_id,
source_relation,
sum(subtotal) as refund_subtotal,
sum(total_tax) as refund_total_tax
from refunds
group by 1,2
), order_discount_code as (
select *
from {{ var('shopify_order_discount_code') }}
), discount_aggregates as (
select
order_id,
source_relation,
sum(case when type = 'shipping' then amount else 0 end) as shipping_discount_amount,
sum(case when type = 'percentage' then amount else 0 end) as percentage_calc_discount_amount,
sum(case when type = 'fixed_amount' then amount else 0 end) as fixed_amount_discount_amount,
count(distinct code) as count_discount_codes_applied
from order_discount_code
group by 1,2
), order_tag as (
select
order_id,
source_relation,
{{ fivetran_utils.string_agg("distinct cast(value as " ~ dbt.type_string() ~ ")", "', '") }} as order_tags
from {{ var('shopify_order_tag') }}
group by 1,2
), order_url_tag as (
select
order_id,
source_relation,
{{ fivetran_utils.string_agg("distinct cast(value as " ~ dbt.type_string() ~ ")", "', '") }} as order_url_tags
from {{ var('shopify_order_url_tag') }}
group by 1,2
), fulfillments as (
select
order_id,
source_relation,
count(fulfillment_id) as number_of_fulfillments,
{{ fivetran_utils.string_agg("distinct cast(service as " ~ dbt.type_string() ~ ")", "', '") }} as fulfillment_services,
{{ fivetran_utils.string_agg("distinct cast(tracking_company as " ~ dbt.type_string() ~ ")", "', '") }} as tracking_companies,
{{ fivetran_utils.string_agg("distinct cast(tracking_number as " ~ dbt.type_string() ~ ")", "', '") }} as tracking_numbers
from {{ var('shopify_fulfillment') }}
group by 1,2
), joined as (
select
orders.*,
coalesce(cast({{ fivetran_utils.json_parse("total_shipping_price_set",["shop_money","amount"]) }} as {{ dbt.type_float() }}) ,0) as shipping_cost,
order_adjustments_aggregates.order_adjustment_amount,
order_adjustments_aggregates.order_adjustment_tax_amount,
refund_aggregates.refund_subtotal,
refund_aggregates.refund_total_tax,
(orders.total_price
+ coalesce(order_adjustments_aggregates.order_adjustment_amount,0) + coalesce(order_adjustments_aggregates.order_adjustment_tax_amount,0)
- coalesce(refund_aggregates.refund_subtotal,0) - coalesce(refund_aggregates.refund_total_tax,0)) as order_adjusted_total,
order_lines.line_item_count,
coalesce(discount_aggregates.shipping_discount_amount, 0) as shipping_discount_amount,
coalesce(discount_aggregates.percentage_calc_discount_amount, 0) as percentage_calc_discount_amount,
coalesce(discount_aggregates.fixed_amount_discount_amount, 0) as fixed_amount_discount_amount,
coalesce(discount_aggregates.count_discount_codes_applied, 0) as count_discount_codes_applied,
coalesce(order_lines.order_total_shipping_tax, 0) as order_total_shipping_tax,
order_tag.order_tags,
order_url_tag.order_url_tags,
fulfillments.number_of_fulfillments,
fulfillments.fulfillment_services,
fulfillments.tracking_companies,
fulfillments.tracking_numbers
from orders
left join order_lines
on orders.order_id = order_lines.order_id
and orders.source_relation = order_lines.source_relation
left join refund_aggregates
on orders.order_id = refund_aggregates.order_id
and orders.source_relation = refund_aggregates.source_relation
left join order_adjustments_aggregates
on orders.order_id = order_adjustments_aggregates.order_id
and orders.source_relation = order_adjustments_aggregates.source_relation
left join discount_aggregates
on orders.order_id = discount_aggregates.order_id
and orders.source_relation = discount_aggregates.source_relation
left join order_tag
on orders.order_id = order_tag.order_id
and orders.source_relation = order_tag.source_relation
left join order_url_tag
on orders.order_id = order_url_tag.order_id
and orders.source_relation = order_url_tag.source_relation
left join fulfillments
on orders.order_id = fulfillments.order_id
and orders.source_relation = fulfillments.source_relation
), windows as (
select
*,
row_number() over (
partition by {{ shopify.shopify_partition_by_cols('customer_id', 'source_relation') }}
order by created_timestamp)
as customer_order_seq_number
from joined
), new_vs_repeat as (
select
*,
case
when customer_order_seq_number = 1 then 'new'
else 'repeat'
end as new_vs_repeat
from windows
)
select *
from new_vs_repeat
Column Description
| column_name | description |
|---|---|
| orders_unique_key | Unique key representing an order. Hashed on 'order_id' and 'source_relation'. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| app_id | The ID of the app that created the order. |
| billing_address_address_1 | The street address of the billing address. |
| billing_address_address_2 | An optional additional field for the street address of the billing address. |
| billing_address_city | The city, town, or village of the billing address. |
| billing_address_company | The company of the person associated with the billing address. |
| billing_address_country | The name of the country of the billing address. |
| billing_address_country_code | The two-letter code (ISO 3166-1 format) for the country of the billing address. |
| billing_address_first_name | The first name of the person associated with the payment method. |
| billing_address_last_name | The last name of the person associated with the payment method. |
| billing_address_latitude | The latitude of the billing address. |
| billing_address_longitude | The longitude of the billing address. |
| billing_address_name | The full name of the person associated with the payment method. |
| billing_address_phone | The phone number at the billing address. |
| billing_address_province | The name of the region (province, state, prefecture, …) of the billing address. |
| billing_address_province_code | The two-letter abbreviation of the region of the billing address. |
| billing_address_zip | The postal code (zip, postcode, Eircode, …) of the billing address. |
| browser_ip | The IP address of the browser used by the customer when they placed the order. |
| has_buyer_accepted_marketing | Whether the customer consented to receive email updates from the shop. |
| cancel_reason | The reason why the order was canceled. |
| cancelled_timestamp | The date and time when the order was canceled. |
| cart_token | The ID of the cart that's associated with the order. |
| closed_timestamp | The date and time when the order was closed (archived). |
| created_timestamp | The autogenerated date and time when the order was created in Shopify. |
| currency | The three-letter code for the shop currency. |
| customer_id | The ID of the order's customer. |
| The customer's email address. | |
| financial_status | The status of payments associated with the order. Can only be set when the order is created |
| fulfillment_status | The order's status in terms of fulfilled line items. |
| order_id | The ID of the order, used for API purposes. This is different from the order_number property, which is the ID used by the shop owner and customer. |
| landing_site_base_url | The URL for the page where the buyer landed when they entered the shop. |
| location_id | The ID of the physical location where the order was processed. |
| name | The order name, generated by combining the order_number property with the order prefix and suffix that are set in the merchant's general settings. |
| note | An optional note that a shop owner can attach to the order. |
| number | The order's position in the shop's count of orders. Numbers are sequential and start at 1. |
| order_number | The order 's position in the shop's count of orders starting at 1001. Order numbers are sequential and start at 1001. |
| processed_timestamp | The date and time when an order was processed. This value is the date that appears on your orders and that's used in the analytic reports. |
| referring_site | The website where the customer clicked a link to the shop. |
| shipping_address_address_1 | The street address of the shipping address. |
| shipping_address_address_2 | An optional additional field for the street address of the shipping address. |
| shipping_address_city | The city, town, or village of the shipping address. |
| shipping_address_company | The company of the person associated with the shipping address. |
| shipping_address_country | The name of the country of the shipping address. |
| shipping_address_country_code | The two-letter code (ISO 3166-1 format) for the country of the shipping address. |
| shipping_address_first_name | The first name of the person associated with the shipping address. |
| shipping_address_last_name | The last name of the person associated with the shipping address. |
| shipping_address_latitude | The latitude of the shipping address. |
| shipping_address_longitude | The longitude of the shipping address. |
| shipping_address_name | The full name of the person associated with the payment method. |
| shipping_address_phone | The phone number at the shipping address. |
| shipping_address_province | The name of the region (province, state, prefecture, …) of the shipping address. |
| shipping_address_province_code | The two-letter abbreviation of the region of the shipping address. |
| shipping_address_zip | The postal code (zip, postcode, Eircode, …) of the shipping address. |
| source_name | Where the order originated. Can be set only during order creation, and is not writeable afterwards. |
| source_relation | The ID of the order placed on the originating platform. This value doesn't correspond to the Shopify ID that's generated from a completed draft. |
| subtotal_price | The price of the order in the shop currency after discounts but before shipping, taxes, and tips. |
| has_taxes_included | Whether taxes are included in the order subtotal. |
| is_test_order | Whether this is a test order. |
| token | A unique token for the order. |
| total_discounts | The total discounts applied to the price of the order in the shop currency. |
| total_line_items_price | The sum of all line item prices in the shop currency. |
| total_price | The sum of all line item prices, discounts, shipping, taxes, and tips in the shop currency. Must be positive. |
| total_tax | The sum of all the taxes applied to the order in th shop currency. Must be positive. |
| total_weight | The sum of all line item weights in grams. |
| updated_timestamp | The date and time (ISO 8601 format) when the order was last modified. |
| user_id | The ID of the user logged into Shopify POS who processed the order, if applicable. |
| line_item_count | Number of line items included in the order. |
| customer_order_seq_number | The sequential number of the order as it relates to the customer |
| new_vs_repeat | Whether the order was a new or repeat order for the customer. |
| shipping_cost | The shipping cost of the order. |
| order_adjustment_amount | Total adjustment amount applied to the order in shop currency. |
| order_adjustment_tax_amount | Total tax applied to the adjustment on the order in shop currency. |
| refund_subtotal | Total refund amount applied to the order in shop currency. |
| refund_total_tax | Total tax applied to the refund on the order in shop currency. |
| order_adjusted_total | Order total adjusted for refunds and other adjustments. The calculation used for this field is as follows: total price listed on the original order (including shipping costs and discounts) + adjustments + adjustments tax - total refunds - refunds tax The order_adjusted_total will equate to the total sales - refunds listed within the transactions table for the order (after currency exchange).\n |
| checkout_token | The checkout token applied to the order. |
| total_shipping_price_set | The total shipping price set to the order. |
| order_total_shipping_tax | Total shipping tax attributed to the order. |
| order_tags | List of tags associated with the order. |
| order_url_tags | List of url tags associated with the order. |
| number_of_fulfillments | Total fulfillments for the order. |
| fulfillment_services | List of fulfillment services for the order. |
| tracking_companies | List of tracking companies for the order. |
| tracking_numbers | List of tracking numbers for the order. |
| total_tip_received | The sum of all the tips in the order in the shop currency. |
| checkout_id | The ID for the checkout. |
| client_details_user_agent | Details of the browsing client, including software and operating versions. |
| customer_locale | The two or three-letter language code, optionally followed by a region modifier. Example values - en, en-CA. |
| order_status_url | The URL pointing to the order status web page, if applicable. |
| presentment_currency | The three-letter code (ISO 4217 format) of the currency that the customer used at checkout. For the shop's default currency, see `currency`. |
| is_deleted | Boolean representing whether the record was soft-deleted in Shopify. |
| total_discounts_set | The total discounts applied to the price of the order in shop and presentment currencies. |
| total_line_items_price_set | The total of all line item prices in shop and presentment currencies. |
| total_price_set | The total price of the order in shop and presentment currencies. |
| total_tax_set | The total tax applied to the order in shop and presentment currencies. |
| is_confirmed | Whether the order is confirmed. |
| shipping_discount_amount | The total amount of discount (in shop currency) allocated toward shipping. |
| percentage_calc_discount_amount | The total amount of discount (in shop currency) allocated via a percentage-based discount |
| fixed_amount_discount_amount | The total amount of discount (in shop currency) allocated via a fixed-amount discount |
| count_discount_codes_applied | Distinct discount codes applied on the order |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
82. model.shopify.shopify__customers
Model Description
Each record represents a customer in Shopify.
SQL
with customers as (
select
{{ dbt_utils.star(from=ref('stg_shopify__customer'), except=["orders_count", "total_spent"]) }}
from {{ var('shopify_customer') }}
), orders as (
select *
from {{ ref('shopify__customers__order_aggregates' )}}
), abandoned as (
select
customer_id,
source_relation,
count(distinct checkout_id) as lifetime_abandoned_checkouts
from {{ var('shopify_abandoned_checkout' )}}
where customer_id is not null
group by 1,2
), joined as (
select
customers.*,
coalesce(abandoned.lifetime_abandoned_checkouts, 0) as lifetime_abandoned_checkouts,
orders.first_order_timestamp,
orders.most_recent_order_timestamp,
orders.customer_tags,
orders.avg_order_value,
coalesce(orders.lifetime_total_spent, 0) as lifetime_total_spent,
coalesce(orders.lifetime_total_refunded, 0) as lifetime_total_refunded,
(coalesce(orders.lifetime_total_spent, 0) - coalesce(orders.lifetime_total_refunded, 0)) as lifetime_total_net,
coalesce(orders.lifetime_count_orders, 0) as lifetime_count_orders,
orders.avg_quantity_per_order,
coalesce(orders.lifetime_total_tax, 0) as lifetime_total_tax,
orders.avg_tax_per_order,
coalesce(orders.lifetime_total_discount, 0) as lifetime_total_discount,
orders.avg_discount_per_order,
coalesce(orders.lifetime_total_shipping, 0) as lifetime_total_shipping,
orders.avg_shipping_per_order,
coalesce(orders.lifetime_total_shipping_with_discounts, 0) as lifetime_total_shipping_with_discounts,
orders.avg_shipping_with_discounts_per_order,
coalesce(orders.lifetime_total_shipping_tax, 0) as lifetime_total_shipping_tax,
orders.avg_shipping_tax_per_order
from customers
left join orders
on customers.customer_id = orders.customer_id
and customers.source_relation = orders.source_relation
left join abandoned
on customers.customer_id = abandoned.customer_id
and customers.source_relation = abandoned.source_relation
)
select *
from joined
Column Description
| column_name | description |
|---|---|
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| created_timestamp | The date and time when the customer was created. |
| default_address_id | The default address for the customer. |
| The unique email address of the customer. Attempting to assign the same email address to multiple customers returns an error. | |
| first_name | The customer's first name. |
| customer_id | A unique identifier for the customer. |
| last_name | The customer's last name. |
| lifetime_count_orders | The number of orders associated with this customer. |
| phone | The unique phone number (E.164 format) for this customer. Attempting to assign the same phone number to multiple customers returns an error. |
| account_state | The state of the customer's account with a shop. |
| is_tax_exempt | Whether the customer is exempt from paying taxes on their order. If true, then taxes won't be applied to an order at checkout. If false, then taxes will be applied at checkout. |
| updated_timestamp | The date and time when the customer information was last updated. |
| is_verified_email | Whether the customer has verified their email address. |
| first_order_timestamp | The timestamp the customer completed their first order. |
| most_recent_order_timestamp | The timestamp the customer completed their most recent order. |
| avg_order_value | The average order value for the customer. |
| lifetime_total_spent | The total amount of money in shop currency that the customer has spent on orders across their order history. |
| lifetime_total_refunded | The total amount of money that the customer has been refunded on orders across their order history. |
| lifetime_total_net | The total amount of money (minus refunds) that the customer has spent across their order history. |
| source_relation | The schema or database this record came from, if you are unioning multiple connectors. Null if not. |
| lifetime_abandoned_checkouts | Total number of abandoned checkouts abandoned by the customer. |
| customer_tags | A string aggregated list of all tags associated with a customer. |
| avg_quantity_per_order | Average quantity of items per order customer orders. |
| lifetime_total_tax | Total amount of tax attributed to the customer. |
| avg_tax_per_order | Average tax per order attributed to the customer. |
| lifetime_total_discount | Total discounts attributed to the customer. |
| avg_discount_per_order | Average discount per order attributed to the customer. |
| lifetime_total_shipping | Total shipping costs attributed to the customer. |
| avg_shipping_per_order | Average shipping cost per order attributed to the customer. |
| lifetime_total_shipping_with_discounts | Total shipping costs after discounts attributed to the customer. |
| avg_shipping_with_discounts_per_order | Average shipping costs after discounts per order attributed to the customer. |
| lifetime_total_shipping_tax | Total shipping tax attributed to the customer. |
| avg_shipping_tax_per_order | Average shipping tax per order attributed to the customer. |
| currency | The three-letter code (ISO 4217 format) for the currency that the customer used when they paid for their last order. Defaults to the shop currency. Returns the shop currency for test orders. |
| note | A note about the customer. |
| marketing_consent_updated_at | The date and time when the customer consented to receive marketing material by email. If no date is provided, then the date and time when the consent information was sent is used. |
| marketing_opt_in_level | The marketing subscription opt-in level, as described in the M3AAWG Sender Best Common Practices, that the customer gave when they consented to receive marketing material by email. |
| marketing_consent_state | Field indicating if the customer has consented to receive marketing material via email. Coalescing of the new `email_marketing_consent_state` field and the deprecated `accepts_marketing` field. Records with the old field will be marked with '(legacy)'.\n |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
83. model.shopify.shopify__customer_emails
Model Description
Each record represents a customer email in Shopify.
SQL
with customer_emails as (
select
{{ dbt_utils.star(from=ref('int_shopify__customer_email_rollup'), except=["orders_count", "total_spent"]) }}
from {{ ref('int_shopify__customer_email_rollup') }}
), orders as (
select *
from {{ ref('int_shopify__emails__order_aggregates' )}}
where email is not null
), abandoned as (
select
lower(email) as email,
source_relation,
count(distinct checkout_id) as lifetime_abandoned_checkouts
from {{ var('shopify_abandoned_checkout' )}}
where email is not null
group by 1,2
), joined as (
select
customer_emails.*,
coalesce(abandoned.lifetime_abandoned_checkouts, 0) as lifetime_abandoned_checkouts,
orders.first_order_timestamp,
orders.most_recent_order_timestamp,
orders.avg_order_value,
coalesce(orders.lifetime_total_spent, 0) as lifetime_total_spent,
coalesce(orders.lifetime_total_refunded, 0) as lifetime_total_refunded,
(coalesce(orders.lifetime_total_spent, 0) - coalesce(orders.lifetime_total_refunded, 0)) as lifetime_total_net,
coalesce(orders.lifetime_count_orders, 0) as lifetime_count_orders,
orders.avg_quantity_per_order,
coalesce(orders.lifetime_total_tax, 0) as lifetime_total_tax,
orders.avg_tax_per_order,
coalesce(orders.lifetime_total_discount, 0) as lifetime_total_discount,
orders.avg_discount_per_order,
coalesce(orders.lifetime_total_shipping, 0) as lifetime_total_shipping,
orders.avg_shipping_per_order,
coalesce(orders.lifetime_total_shipping_with_discounts, 0) as lifetime_total_shipping_with_discounts,
orders.avg_shipping_with_discounts_per_order,
coalesce(orders.lifetime_total_shipping_tax, 0) as lifetime_total_shipping_tax,
orders.avg_shipping_tax_per_order
from customer_emails
left join orders
on customer_emails.email = orders.email
and customer_emails.source_relation = orders.source_relation
left join abandoned
on customer_emails.email = abandoned.email
and customer_emails.source_relation = abandoned.source_relation
)
select *
from joined
Column Description
| column_name | description |
|---|---|
| last_fivetran_synced | The time when a record was last updated by Fivetran. |
| default_address_id | The default address for the customer. |
| The unique email address of the customer. | |
| first_name | The customer's first name. |
| customer_ids | Comma-separated list of customer IDs associated with the email. |
| last_name | The customer's last name. |
| lifetime_count_orders | The number of orders associated with this customer. |
| phone_numbers | Comma-separated list of phone numbers associated with this email. |
| account_state | The state of the customer's account with a shop. |
| is_tax_exempt | Whether the customer is exempt from paying taxes on their order. If true, then taxes won't be applied to an order at checkout. If false, then taxes will be applied at checkout. |
| last_updated_at | The date and time when the customer information was last updated. |
| is_verified_email | Whether the customer has verified their email address. |
| first_order_timestamp | The timestamp the customer completed their first order. |
| most_recent_order_timestamp | The timestamp the customer completed their most recent order. |
| avg_order_value | The average order value for the customer. |
| lifetime_total_spent | The total amount of money that the customer has spent on orders across their order history (in shop currency). |
| lifetime_total_refunded | The total amount of money that the customer has been refunded on orders across their order history. |
| lifetime_total_net | The total amount of money (minus refunds) that the customer has spent across their order history. |
| first_account_created_at | Timestamp of when the first account associated with this email was created. |
| last_account_created_at | Timestamp of when the last account associated with this email was created. |
| source_relation | The schema or database this record came from, if you are unioning multiple connectors. Null if not. |
| lifetime_abandoned_checkouts | Total number of abandoned checkouts abandoned by the customer. |
| customer_tags | A string aggregated list of all tags associated with a customer. |
| avg_quantity_per_order | Average quantity of items per order customer orders. |
| lifetime_total_tax | Total amount of tax attributed to the customer. |
| avg_tax_per_order | Average tax per order attributed to the customer. |
| lifetime_total_discount | Total discounts attributed to the customer. |
| avg_discount_per_order | Average discount per order attributed to the customer. |
| lifetime_total_shipping | Total shipping costs attributed to the customer. |
| avg_shipping_per_order | Average shipping cost per order attributed to the customer. |
| lifetime_total_shipping_with_discounts | Total shipping costs after discounts attributed to the customer. |
| avg_shipping_with_discounts_per_order | Average shipping costs after discounts per order attributed to the customer. |
| lifetime_total_shipping_tax | Total shipping tax attributed to the customer. |
| avg_shipping_tax_per_order | Average shipping tax per order attributed to the customer. |
| marketing_consent_updated_at | The date and time when the customer consented to receive marketing material by email. If no date is provided, then the date and time when the consent information was sent is used. |
| currency | The three-letter code (ISO 4217 format) for the currency that the customer used when they paid for their last order. Defaults to the shop currency. Returns the shop currency for test orders. |
| marketing_consent_state | Field indicating if the customer has consented to receive marketing material via email. Coalescing of the new `email_marketing_consent_state` field and the deprecated `accepts_marketing` field. Records with the old field will be marked with '(legacy)'.\n |
| marketing_opt_in_level | The marketing subscription opt-in level, as described in the M3AAWG Sender Best Common Practices, that the customer gave when they consented to receive marketing material by email. |
| note | A note about the customer. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
84. model.shopify.int_shopify__discounts__order_aggregates
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with order_discount_code as (
select *
from {{ var('shopify_order_discount_code') }}
),
orders as (
select *
from {{ ref('shopify__orders') }}
),
orders_aggregated as (
select
order_discount_code.code,
order_discount_code.type,
order_discount_code.source_relation,
avg(order_discount_code.amount) as avg_order_discount_amount,
sum(order_discount_code.amount) as total_order_discount_amount,
max(orders.total_line_items_price) as total_order_line_items_price, -- summing would multiply the total by the # of discount codes applied to an order
max(orders.shipping_cost) as total_order_shipping_cost, -- summing would multiply the total by the # of discount codes applied to an order
max(orders.refund_subtotal + orders.refund_total_tax) as total_order_refund_amount, -- summing would multiply the total by the # of discount codes applied to an order
count(distinct customer_id) as count_customers,
count(distinct email) as count_customer_emails,
count(distinct order_discount_code.order_id) as count_orders
from order_discount_code
join orders
on order_discount_code.order_id = orders.order_id
and order_discount_code.source_relation = orders.source_relation
group by 1,2,3
)
select *
from orders_aggregated
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
85. model.shopify.int_shopify__daily_orders
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with orders as (
select *
from {{ ref('shopify__orders') }}
where not coalesce(is_deleted, false)
),
order_lines as(
select *
from {{ ref('shopify__order_lines') }}
),
order_aggregates as (
select
source_relation,
cast({{ dbt.date_trunc('day','created_timestamp') }} as date) as date_day,
count(distinct order_id) as count_orders,
sum(line_item_count) as count_line_items,
avg(line_item_count) as avg_line_item_count,
count(distinct customer_id) as count_customers,
count(distinct email) as count_customer_emails,
sum(order_adjusted_total) as order_adjusted_total,
avg(order_adjusted_total) as avg_order_value,
sum(shipping_cost) as shipping_cost,
sum(order_adjustment_amount) as order_adjustment_amount,
sum(order_adjustment_tax_amount) as order_adjustment_tax_amount,
sum(refund_subtotal) as refund_subtotal,
sum(refund_total_tax) as refund_total_tax,
sum(total_discounts) as total_discounts,
avg(total_discounts) as avg_discount,
sum(shipping_discount_amount) as shipping_discount_amount,
avg(shipping_discount_amount) as avg_shipping_discount_amount,
sum(percentage_calc_discount_amount) as percentage_calc_discount_amount,
avg(percentage_calc_discount_amount) as avg_percentage_calc_discount_amount,
sum(fixed_amount_discount_amount) as fixed_amount_discount_amount,
avg(fixed_amount_discount_amount) as avg_fixed_amount_discount_amount,
sum(count_discount_codes_applied) as count_discount_codes_applied,
count(distinct location_id) as count_locations_ordered_from,
sum(case when count_discount_codes_applied > 0 then 1 else 0 end) as count_orders_with_discounts,
sum(case when refund_subtotal > 0 then 1 else 0 end) as count_orders_with_refunds,
min(created_timestamp) as first_order_timestamp,
max(created_timestamp) as last_order_timestamp
from orders
group by 1,2
),
order_line_aggregates as (
select
order_lines.source_relation,
cast({{ dbt.date_trunc('day','orders.created_timestamp') }} as date) as date_day,
sum(order_lines.quantity) as quantity_sold,
sum(order_lines.refunded_quantity) as quantity_refunded,
sum(order_lines.quantity_net_refunds) as quantity_net,
sum(order_lines.quantity) / count(distinct order_lines.order_id) as avg_quantity_sold,
sum(order_lines.quantity_net_refunds) / count(distinct order_lines.order_id) as avg_quantity_net,
count(distinct order_lines.variant_id) as count_variants_sold,
count(distinct order_lines.product_id) as count_products_sold,
sum(case when order_lines.is_gift_card then order_lines.quantity_net_refunds else 0 end) as quantity_gift_cards_sold,
sum(case when order_lines.is_shipping_required then order_lines.quantity_net_refunds else 0 end) as quantity_requiring_shipping
from order_lines
left join orders -- just joining with order to get the created_timestamp
on order_lines.order_id = orders.order_id
and order_lines.source_relation = orders.source_relation
group by 1,2
),
final as (
select
order_aggregates.*,
order_line_aggregates.quantity_sold,
order_line_aggregates.quantity_refunded,
order_line_aggregates.quantity_net,
order_line_aggregates.count_variants_sold,
order_line_aggregates.count_products_sold,
order_line_aggregates.quantity_gift_cards_sold,
order_line_aggregates.quantity_requiring_shipping,
order_line_aggregates.avg_quantity_sold,
order_line_aggregates.avg_quantity_net
from order_aggregates
left join order_line_aggregates
on order_aggregates.date_day = order_line_aggregates.date_day
and order_aggregates.source_relation = order_line_aggregates.source_relation
)
select *
from final
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
86. model.shopify.int_shopify__product__order_line_aggregates
Model Description
⚠️ Model description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
SQL
with order_lines as (
select *
from {{ ref('shopify__order_lines') }}
), orders as (
select *
from {{ ref('shopify__orders')}}
), product_aggregated as (
select
order_lines.product_id,
order_lines.source_relation,
-- moved over from shopify__products
sum(order_lines.quantity) as quantity_sold,
sum(order_lines.pre_tax_price) as subtotal_sold,
sum(order_lines.quantity_net_refunds) as quantity_sold_net_refunds,
sum(order_lines.subtotal_net_refunds) as subtotal_sold_net_refunds,
min(orders.created_timestamp) as first_order_timestamp,
max(orders.created_timestamp) as most_recent_order_timestamp,
-- new columns
sum(order_lines.total_discount) as product_total_discount,
sum(order_lines.order_line_tax) as product_total_tax,
avg(order_lines.quantity) as avg_quantity_per_order_line,
avg(order_lines.total_discount) as product_avg_discount_per_order_line,
avg(order_lines.order_line_tax) as product_avg_tax_per_order_line
from order_lines
left join orders
on order_lines.order_id = orders.order_id
and order_lines.source_relation = orders.source_relation
group by 1,2
)
select *
from product_aggregated
Column Description
⚠️ Column description is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
87. model.shopify.shopify__discounts
Model Description
Each record represents a unique discount, enriched with information about its associated `price_rule` and metrics regarding orders and abandoned checkouts.
SQL
{{
config(
materialized='table' if target.type in ('bigquery', 'databricks', 'spark') else 'incremental',
unique_key='discounts_unique_key',
incremental_strategy='delete+insert' if target.type in ('postgres', 'redshift', 'snowflake') else 'merge',
cluster_by=['discount_code_id']
)
}}
with discount as (
select
*,
{{ dbt_utils.generate_surrogate_key(['source_relation', 'discount_code_id']) }} as discounts_unique_key
from {{ var('shopify_discount_code') }}
{% if is_incremental() %}
where cast(coalesce(updated_at, created_at) as date) >= {{ shopify.shopify_lookback(
from_date="max(cast(coalesce(updated_at, created_at) as date))",
interval=var('lookback_window', 7),
datepart='day') }}
{% endif %}
),
price_rule as (
select *
from {{ var('shopify_price_rule') }}
),
orders_aggregated as (
select *
from {{ ref('int_shopify__discounts__order_aggregates')}}
),
abandoned_checkouts_aggregated as (
select *
from {{ ref('int_shopify__discounts__abandoned_checkouts')}}
),
discount_price_rule_joined as (
select
discount.*,
price_rule.target_selection,
price_rule.target_type,
price_rule.title,
price_rule.usage_limit,
price_rule.value,
price_rule.value_type,
price_rule.allocation_limit,
price_rule.allocation_method,
price_rule.is_once_per_customer,
price_rule.customer_selection,
-- the below are NULL if customer_selection = all
price_rule.prereq_min_quantity,
price_rule.prereq_max_shipping_price,
price_rule.prereq_min_subtotal,
price_rule.prereq_min_purchase_quantity_for_entitlement,
price_rule.prereq_buy_x_get_this,
price_rule.prereq_buy_this_get_y,
price_rule.starts_at,
price_rule.ends_at,
price_rule.created_at as price_rule_created_at,
price_rule.updated_at as price_rule_updated_at
from discount
left join price_rule
on discount.price_rule_id = price_rule.price_rule_id
and discount.source_relation = price_rule.source_relation
),
aggregates_joined as (
select
discount_price_rule_joined.*,
coalesce(orders_aggregated.count_orders, 0) as count_orders,
coalesce(abandoned_checkouts_aggregated.count_abandoned_checkouts, 0) as count_abandoned_checkouts,
orders_aggregated.avg_order_discount_amount,
coalesce(orders_aggregated.total_order_discount_amount, 0) as total_order_discount_amount,
coalesce(abandoned_checkouts_aggregated.total_abandoned_checkout_discount_amount, 0) as total_abandoned_checkout_discount_amount,
coalesce(orders_aggregated.total_order_line_items_price, 0) as total_order_line_items_price,
coalesce(orders_aggregated.total_order_shipping_cost, 0) as total_order_shipping_cost,
coalesce(abandoned_checkouts_aggregated.total_abandoned_checkout_shipping_price, 0) as total_abandoned_checkout_shipping_price,
coalesce(orders_aggregated.total_order_refund_amount, 0) as total_order_refund_amount,
coalesce(orders_aggregated.count_customers, 0) as count_customers,
coalesce(orders_aggregated.count_customer_emails, 0) as count_customer_emails,
coalesce(abandoned_checkouts_aggregated.count_abandoned_checkout_customers, 0) as count_abandoned_checkout_customers,
coalesce(abandoned_checkouts_aggregated.count_abandoned_checkout_customer_emails, 0) as count_abandoned_checkout_customer_emails
from discount_price_rule_joined
left join orders_aggregated
on discount_price_rule_joined.code = orders_aggregated.code
and discount_price_rule_joined.source_relation = orders_aggregated.source_relation
-- in case one CODE can apply to both shipping and line items, percentages and fixed_amounts
and (case
when discount_price_rule_joined.target_type = 'shipping_line' then 'shipping' -- when target_type = 'shipping', value_type = 'percentage'
else discount_price_rule_joined.value_type end) = orders_aggregated.type
left join abandoned_checkouts_aggregated
on discount_price_rule_joined.code = abandoned_checkouts_aggregated.code
and discount_price_rule_joined.source_relation = abandoned_checkouts_aggregated.source_relation
-- in case one CODE can apply to both shipping and line items, percentages and fixed_amounts
and (case
when discount_price_rule_joined.target_type = 'shipping_line' then 'shipping' -- when target_type = 'shipping', value_type = 'percentage'
else discount_price_rule_joined.value_type end) = abandoned_checkouts_aggregated.type
)
select *
from aggregates_joined
Column Description
| column_name | description |
|---|---|
| discounts_unique_key | Unique key representing a discount. Hashed on 'discount_code_id' and 'source_relation'. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| code | The case-insensitive discount code that customers use at checkout. Shopify recommends this map onto the associated `price_rule.title`. |
| created_at | The date and time (ISO 8601 format) when the discount code was created. |
| discount_code_id | The ID for the discount code. |
| price_rule_id | The ID for the price rule. |
| updated_at | The date and time (ISO 8601 format) when the discount code was updated. |
| usage_count | The number of times that the discount code has been redeemed. |
| allocation_limit | The number of times the discount can be allocated on the cart - if eligible. For example a Buy 1 hat Get 1 hat for free discount can be applied 3 times on a cart having more than 6 hats, where maximum of 3 hats get discounted - if the allocation_limit is 3. Empty (null) allocation_limit means unlimited number of allocations.\n |
| allocation_method | The allocation method of the price rule. Valid values include `each` (the discount is applied to each of the entitled items. For example, for a price rule that takes $15 off, each entitled line item in a checkout will be discounted by $15) and `across` (the calculated discount amount will be applied across the entitled items. For example, for a price rule that takes $15 off, the discount will be applied across all the entitled items).\n |
| price_rule_created_at | The date and time (ISO 8601 format) when the price rule was created. |
| customer_selection | The customer selection for the price rule. Valid values include `all` (the price rule is valid for all customers) and `prerequisite` (the customer must either belong to one of the customer segments specified by customer_segment_prerequisite_ids, or be one of the customers specified by prerequisite_customer_ids).\n |
| ends_at | The date and time (ISO 8601 format) when the price rule ends. Must be after starts_at. |
| is_once_per_customer | Boolean representing whether the generated discount code will be valid only for a single use per customer. This is tracked using customer ID. |
| prereq_min_quantity | If `customer_selection` is `prerequisite`, the minimum number of items for the price rule to be applicable. The quantity of an entitled cart item must be greater than or equal to this value. |
| prereq_max_shipping_price | If `customer_selection` is `prerequisite`, the maximum shipping price for the price rule to be applicable. The shipping price must be less than or equal to this value |
| prereq_min_subtotal | If `customer_selection` is `prerequisite`, the minimum subtotal for the price rule to be applicable. The subtotal of the entitled cart items must be greater than or equal to this value for the discount to apply. |
| prereq_min_purchase_quantity_for_entitlement | If `customer_selection` is `prerequisite`, the prerequisite purchase for a Buy X Get Y discount. The minimum purchase amount required to be entitled to the discount. |
| prereq_buy_x_get_this | If `customer_selection` is `prerequisite`, in a Buy/Get ratio for a Buy X Get Y discount, this is the offered 'get' quantity. |
| prereq_buy_this_get_y | If `customer_selection` is `prerequisite`, in a Buy/Get ratio for a Buy X Get Y discount, this defines the necessary 'buy' quantity. |
| starts_at | The date and time (ISO 8601 format) when the price rule starts. |
| target_selection | The target selection method of the price rule. Valid values include `all` (the price rule applies the discount to all line items in the checkout) and `entitled` (the price rule applies the discount to selected entitlements only).\n |
| target_type | The target type that the price rule applies to. Valid values include `line_item` (the price rule applies to the cart's line items) and `shipping_line` (the price rule applies to the cart's shipping lines). |
| title | The title of the price rule. This is used by the Shopify admin search to retrieve discounts. It is also displayed on the Discounts page of the Shopify admin for bulk discounts. Shopify recommends that this map onto the associated `discount_code.code`.\n |
| price_rule_updated_at | The date and time (ISO 8601 format) when the price rule was updated. |
| usage_limit | The maximum number of times the price rule can be used, per discount code. |
| value | The value of the price rule. If if the value of `target_type` is `shipping_line`, then only -100 is accepted. The value must be negative. |
| value_type | The value type of the price rule. Valid values include `fixed_amount` (applies a discount of value as a unit of the store's currency. For example, if value is -30 and the store's currency is USD, then $30 USD is deducted when the discount is applied) and `percentage` (applies a percentage discount of value. For example, if value is -30, then 30% will be deducted when the discount is applied).\nIf `target_type` is `shipping_line`, then only `percentage` is accepted.\n |
| total_order_discount_amount | Total monetary amount (in shop currency) of discounts taken off of orders. |
| total_abandoned_checkout_discount_amount | Total monetary amount (in shop currency) of discounts taken off abandoned checkout orders. |
| total_order_line_items_price | Total monetary amount (in shop currency) of line items for orders that have used this discount. |
| total_order_shipping_cost | Total shipping costs for orders that used this discount. |
| total_abandoned_checkout_shipping_price | Total projected shipping costs for abandoned checkouts that applied this discount first. |
| total_order_refund_amount | Total refunded amount (in shop currency) for orders that used this discount code. |
| count_customers | Count of distinct customers who placed orders using this discount. |
| count_customer_emails | Count of distinct customer emails who placed orders using this discount. |
| avg_order_discount_amount | Average amount (in shop currency) of discount taken off orders. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
| count_orders | Count of orders in which this discount code was applied. |
| count_abandoned_checkouts | Count of abandoned checkouts in which this discount code was applied. |
| count_abandoned_checkout_customers | Distinct count of customers who applied this discount in a checkout that they abandoned. |
| count_abandoned_checkout_customer_emails | Distinct count of customer emails who applied this discount in a checkout that they abandoned. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
88. model.shopify.shopify__daily_shop
Model Description
Each record represents a day in your Shop, enriched with a suite of metrics.
SQL
with shop as (
select *
from {{ var('shopify_shop') }}
),
calendar as (
select *
from {{ ref('shopify__calendar') }}
where cast({{ dbt.date_trunc('day','date_day') }} as date) = date_day
),
daily_orders as (
select *
from {{ ref('int_shopify__daily_orders') }}
),
daily_abandoned_checkouts as (
select *
from {{ ref('int_shopify__daily_abandoned_checkouts') }}
),
{% if var('shopify_using_fulfillment_event', false) %}
daily_fulfillment as (
select *
from {{ ref('int_shopify__daily_fulfillment') }}
),
{% endif %}
shop_calendar as (
select
cast({{ dbt.date_trunc('day','calendar.date_day') }} as date) as date_day,
shop.shop_id,
shop.name,
shop.domain,
shop.is_deleted,
shop.currency,
shop.enabled_presentment_currencies,
shop.iana_timezone,
shop.created_at,
shop.source_relation
from calendar
join shop
on cast(shop.created_at as date) <= calendar.date_day
),
final as (
select
shop_calendar.*,
coalesce(daily_orders.count_orders, 0) as count_orders,
coalesce(daily_orders.count_line_items, 0) as count_line_items,
daily_orders.avg_line_item_count,
coalesce(daily_orders.count_customers, 0) as count_customers,
coalesce(daily_orders.count_customer_emails, 0) as count_customer_emails,
coalesce(daily_orders.order_adjusted_total, 0) as order_adjusted_total,
daily_orders.avg_order_value,
coalesce(daily_orders.shipping_cost, 0) as shipping_cost,
coalesce(daily_orders.order_adjustment_amount, 0) as order_adjustment_amount,
coalesce(daily_orders.order_adjustment_tax_amount, 0) as order_adjustment_tax_amount,
coalesce(daily_orders.refund_subtotal, 0) as refund_subtotal,
coalesce(daily_orders.refund_total_tax, 0) as refund_total_tax,
coalesce(daily_orders.total_discounts, 0) as total_discounts,
daily_orders.avg_discount,
coalesce(daily_orders.shipping_discount_amount, 0) as shipping_discount_amount,
daily_orders.avg_shipping_discount_amount,
coalesce(daily_orders.percentage_calc_discount_amount, 0) as percentage_calc_discount_amount,
daily_orders.avg_percentage_calc_discount_amount,
coalesce(daily_orders.fixed_amount_discount_amount, 0) as fixed_amount_discount_amount,
daily_orders.avg_fixed_amount_discount_amount,
coalesce(daily_orders.count_discount_codes_applied, 0) as count_discount_codes_applied,
coalesce(daily_orders.count_locations_ordered_from, 0) as count_locations_ordered_from,
coalesce(daily_orders.count_orders_with_discounts, 0) as count_orders_with_discounts,
coalesce(daily_orders.count_orders_with_refunds, 0) as count_orders_with_refunds,
daily_orders.first_order_timestamp,
daily_orders.last_order_timestamp,
coalesce(daily_orders.quantity_sold, 0) as quantity_sold,
coalesce(daily_orders.quantity_refunded, 0) as quantity_refunded,
coalesce(daily_orders.quantity_net, 0) as quantity_net,
daily_orders.avg_quantity_sold,
daily_orders.avg_quantity_net,
coalesce(daily_orders.count_variants_sold, 0) as count_variants_sold,
coalesce(daily_orders.count_products_sold, 0) as count_products_sold,
coalesce(daily_orders.quantity_gift_cards_sold, 0) as quantity_gift_cards_sold,
coalesce(daily_orders.quantity_requiring_shipping, 0) as quantity_requiring_shipping,
coalesce(daily_abandoned_checkouts.count_abandoned_checkouts, 0) as count_abandoned_checkouts,
coalesce(daily_abandoned_checkouts.count_customers_abandoned_checkout, 0) as count_customers_abandoned_checkout,
coalesce(daily_abandoned_checkouts.count_customer_emails_abandoned_checkout, 0) as count_customer_emails_abandoned_checkout
{% if var('shopify_using_fulfillment_event', false) %}
{% for status in ['attempted_delivery', 'delayed', 'delivered', 'failure', 'in_transit', 'out_for_delivery', 'ready_for_pickup', 'picked_up', 'label_printed', 'label_purchased', 'confirmed']%}
, coalesce(count_fulfillment_{{ status }}, 0) as count_fulfillment_{{ status }}
{% endfor %}
{% endif %}
from shop_calendar
left join daily_orders
on shop_calendar.source_relation = daily_orders.source_relation
and shop_calendar.date_day = daily_orders.date_day
left join daily_abandoned_checkouts
on shop_calendar.source_relation = daily_abandoned_checkouts.source_relation
and shop_calendar.date_day = daily_abandoned_checkouts.date_day
{% if var('shopify_using_fulfillment_event', false) %}
left join daily_fulfillment
on shop_calendar.source_relation = daily_fulfillment.source_relation
and shop_calendar.date_day = daily_fulfillment.date_day
{% endif %}
)
select *
from final
Column Description
| column_name | description |
|---|---|
| date_day | Day for which the shop activity is being measured. |
| shop_id | The ID for the shop. A 64-bit unsigned integer. |
| name | The name of the shop. |
| domain | The shop's domain. |
| is_deleted | Boolean representing whether the record was soft-deleted in Shopify. |
| currency | The three-letter code (ISO 4217 format) for the shop's default currency. |
| enabled_presentment_currencies | An array of of enabled currencies (ISO 4217 format) that the shop accepts. Merchants can enable currencies from their Shopify Payments settings in the Shopify Admin. |
| iana_timezone | The name of the timezone assigned by the [IANA](https://www.iana.org/time-zones). |
| created_at | The date and time (ISO 8601) when the shop was created. |
| count_orders | Count of the distinct orders placed on this day. |
| count_line_items | Count of the line items included in orders placed on this day. |
| count_customers | Count of distinct customers who placed an order on this day. |
| count_customer_emails | Count of distinct customer email addresses who placed an order on this day. |
| order_adjusted_total | Order total adjusted for refunds and other adjustments. The calculation used for this field is as follows: total price listed on the original order (including shipping costs and discounts) + adjustments + adjustments tax - total refunds - refunds tax The order_adjusted_total will equate to the total sales - refunds listed within the transactions table for the order (after currency exchange).\n |
| avg_order_value | Average adjusted total per order placed on this day (in shop currency). |
| shipping_cost | The shipping cost of the orders placed on this day (in shop currency). |
| order_adjustment_amount | Total adjustment amount (in shop currency) applied to the orders placed on this day. |
| order_adjustment_tax_amount | Total tax applied to adjustments (in shop currency) on the orders placed on this day. |
| refund_subtotal | Total refund amount applied to the orders placed on this day. |
| refund_total_tax | Total tax applied to the refund on the orders placed on this day (in shop currency). |
| total_discounts | The total amount of the discount allocated to this day's orders in the shop's currency. |
| shipping_discount_amount | The total amount of discount (in shop currency) allocated toward shipping for orders placed on this day. |
| percentage_calc_discount_amount | The total amount of discount (in shop currency) allocated via a percentage-based discount for orders placed on this day. |
| fixed_amount_discount_amount | The total amount of discount (in shop currency) allocated via a fixed-amount discount for orders placed on this day. |
| count_discount_codes_applied | Distinct discount codes applied by customers on orders for this day. |
| count_locations_ordered_from | Distinct locations with orders placed against them on this day. |
| count_orders_with_discounts | Count of orders in which a discount was applied. |
| count_orders_with_refunds | Count of orders in which there was a refund. |
| first_order_timestamp | Timestamp of the first order of the day for the shop. |
| last_order_timestamp | Timestamp of the last order of the day for the shop. |
| quantity_sold | Total quantity sold from the inventory level. Includes refunds. |
| quantity_refunded | The quantity of goods ORDERED on this day that have been refunded. |
| quantity_net | Net quantity sold from this shop on this day. Excludes refunds. |
| count_variants_sold | Distinct product variants sold on this day (includes refunds). |
| count_products_sold | Distinct products sold on this day (includes refunds). |
| quantity_gift_cards_sold | Quantity of gift cards sold on this day. |
| quantity_requiring_shipping | Quantity of goods sold on this day that require shipping. |
| count_abandoned_checkouts | Count of abandoned checkouts on this day. |
| count_customers_abandoned_checkout | Count of distinct customers who abandoned checkouts on this day. |
| count_customer_emails_abandoned_checkout | Count of distinct customer emails who abandoned checkouts on this day. |
| count_fulfillment_attempted_delivery | Count of distinct fulfillments on this day where the delivery of the shipment was attempted, but unable to be completed. |
| count_fulfillment_delivered | Count of successful distinct fulfillments successfully delivered on this day. |
| count_fulfillment_failure | Count of failed distinct fulfillments on this day. Something went wrong when pulling tracking information for the shipment, such as the tracking number was invalid or the shipment was canceled. |
| count_fulfillment_in_transit | Count of distinct fulfillments in transit on this day. Each shipment is being transported between shipping facilities on the way to its destination. |
| count_fulfillment_out_for_delivery | Count of distinct fulfillments being delivered to their final destination. |
| count_fulfillment_ready_for_pickup | Count of distinct fulfillments ready for pickup at a shipping depot. |
| count_fulfillment_picked_up | Count of distinct fulfillments successfully picked up on this day. |
| count_fulfillment_label_printed | Count of distinct fulfillments for which a purchased shipping label has been printed on this day. |
| count_fulfillment_label_purchased | Count of distinct fulfillments for which a shipping label has been purchased (but not printed yet) on this day. |
| count_fulfillment_confirmed | Count of confirmed distinct fulfillments. This is the default status when no other information is available about a fulfillment. |
| count_fulfillment_delayed | Count of delayed distinct fulfillments. |
| avg_line_item_count | Average line item count for orders placed on this day. |
| avg_discount | Average total discount placed on orders on this day (in shop currency). |
| avg_shipping_discount_amount | Average discount amount (in shop currency) allocated toward shipping on orders placed on this day. |
| avg_percentage_calc_discount_amount | Average discount amount (in shop currency) allocated via a percentage-based-discount on orders placed on this day. |
| avg_fixed_amount_discount_amount | Average discount amount (in shop currency) allocated via a fixed-amount-discount on orders placed on this day. |
| avg_quantity_sold | Average quantity sold per order on this day. Includes refunds. (in shop currency) |
| avg_quantity_net | Average net quantity sold per order on this day. Excludes refunds. (in shop currency) |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
89. model.shopify.shopify__customer_cohorts
Model Description
Each record represents a customer's performance in a calendar month.
SQL
{{
config(
materialized='table' if shopify.shopify_is_databricks_sql_warehouse() else 'incremental',
unique_key='customer_cohort_id',
incremental_strategy='insert_overwrite' if target.type in ('bigquery', 'databricks', 'spark') else 'delete+insert',
partition_by={
"field": "date_month",
"data_type": "date"
} if target.type not in ('spark','databricks')
else ['date_month'],
cluster_by=['date_month', 'customer_id'],
file_format='delta' if shopify.shopify_is_databricks_sql_warehouse() else 'parquet'
)
}}
with calendar as (
select *
from {{ ref('shopify__calendar') }}
where cast({{ dbt.date_trunc('month','date_day') }} as date) = date_day
{% if is_incremental() %}
and cast(date_day as date) >= {{ shopify.shopify_lookback(from_date="max(date_month)", interval=1, datepart='month') }}
{% endif %}
), customers as (
select *
from {{ ref('shopify__customers') }}
), orders as (
select *
from {{ ref('shopify__orders') }}
), customer_calendar as (
select
cast(calendar.date_day as date) as date_month,
customers.customer_id,
customers.first_order_timestamp,
customers.source_relation,
cast({{ dbt.date_trunc('month', 'first_order_timestamp') }} as date) as cohort_month
from calendar
inner join customers
on cast({{ dbt.date_trunc('month', 'first_order_timestamp') }} as date) <= calendar.date_day
), orders_joined as (
select
customer_calendar.date_month,
customer_calendar.customer_id,
customer_calendar.first_order_timestamp,
customer_calendar.cohort_month,
customer_calendar.source_relation,
coalesce(count(distinct orders.order_id), 0) as order_count_in_month,
coalesce(sum(orders.order_adjusted_total), 0) as total_price_in_month,
coalesce(sum(orders.line_item_count), 0) as line_item_count_in_month
from customer_calendar
left join orders
on customer_calendar.customer_id = orders.customer_id
and customer_calendar.source_relation = orders.source_relation
and customer_calendar.date_month = cast({{ dbt.date_trunc('month', 'created_timestamp') }} as date)
{{ dbt_utils.group_by(n=5) }}
), windows as (
{% set partition_string = 'partition by ' ~ shopify.shopify_partition_by_cols('customer_id', 'source_relation') ~ 'order by date_month rows between unbounded preceding and current row' %}
select
*,
sum(total_price_in_month) over ({{ partition_string }}) as total_price_lifetime,
sum(order_count_in_month) over ({{ partition_string }}) as order_count_lifetime,
sum(line_item_count_in_month) over ({{ partition_string }}) as line_item_count_lifetime,
row_number() over (
partition by {{ shopify.shopify_partition_by_cols('customer_id', 'source_relation') }}
order by date_month asc)
as cohort_month_number
from orders_joined
{% if is_incremental() %}
), backfill_lifetime_sums as (
-- for incremental runs we need to fetch the prior lifetimes to properly continue adding to them
select
source_relation,
customer_id,
max(total_price_lifetime) as previous_total_price_lifetime,
max(order_count_lifetime) as previous_order_count_lifetime,
max(line_item_count_lifetime) as previous_line_item_count_lifetime,
max(cohort_month_number) as previous_cohort_month_number
from {{ this }}
where date_month < {{ shopify.shopify_lookback(from_date="max(date_month)", interval=1, datepart='month') }}
group by 1,2
), final as (
select
windows.date_month,
windows.customer_id,
windows.first_order_timestamp,
windows.cohort_month,
windows.source_relation,
windows.order_count_in_month,
windows.total_price_in_month,
windows.line_item_count_in_month,
backfill_lifetime_sums.previous_cohort_month_number + windows.cohort_month_number as cohort_month_number,
backfill_lifetime_sums.previous_total_price_lifetime + windows.total_price_lifetime as total_price_lifetime,
backfill_lifetime_sums.previous_order_count_lifetime + windows.order_count_lifetime as order_count_lifetime,
backfill_lifetime_sums.previous_line_item_count_lifetime + windows.line_item_count_lifetime as line_item_count_lifetime,
{{ dbt_utils.generate_surrogate_key(['windows.date_month','windows.customer_id','windows.source_relation']) }} as customer_cohort_id
from windows
left join backfill_lifetime_sums
on backfill_lifetime_sums.source_relation = windows.source_relation
and backfill_lifetime_sums.customer_id = windows.customer_id
{% else %}
), final as (
select
*,
{{ dbt_utils.generate_surrogate_key(['date_month','customer_id','source_relation']) }} as customer_cohort_id
from windows
{% endif %}
)
select *
from final
Column Description
| column_name | description |
|---|---|
| cohort_month | The month the cohort belongs to, i.e the first month the customer had an order. |
| cohort_month_number | The 'number' of the `date_month` of the record, i.e. how many months from their start month this cohort occurred |
| customer_cohort_id | Unique key representing a customer in a given month. Hashed on 'date_month', 'customer_id', and 'source_relation'. |
| customer_id | The ID of the related customer. |
| date_month | The calendar month the customer stats relate to. |
| first_order_timestamp | The timestamp of the customer's first order. |
| line_item_count_in_month | Number of line items purchased in the `date_month` |
| line_item_count_lifetime | Number of line items purchased up until and including this `date_month`. |
| order_count_in_month | Number of orders purchased in the `date_month` |
| order_count_lifetime | Number of orders purchased up until and including this `date_month`. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
| total_price_in_month | Total amount (in shop currency) purchased in the `date_month` |
| total_price_lifetime | Total amount (in shop currency) up until and including this `date_month`. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
90. model.shopify.shopify__customer_email_cohorts
Model Description
Each record represents a customer's (on the EMAIL grain) performance in a calendar month.
SQL
{{
config(
materialized='table' if shopify.shopify_is_databricks_sql_warehouse() else 'incremental',
unique_key='customer_cohort_id',
incremental_strategy='insert_overwrite' if target.type in ('bigquery', 'databricks', 'spark') else 'delete+insert',
partition_by={
"field": "date_month",
"data_type": "date"
} if target.type not in ('spark','databricks')
else ['date_month'],
cluster_by=['date_month', 'email'],
file_format='delta' if shopify.shopify_is_databricks_sql_warehouse() else 'parquet'
)
}}
with calendar as (
select *
from {{ ref('shopify__calendar') }}
where cast({{ dbt.date_trunc('month','date_day') }} as date) = date_day
{% if is_incremental() %}
and cast(date_day as date) >= {{ shopify.shopify_lookback(from_date="max(date_month)", interval=1, datepart='month') }}
{% endif %}
), customers as (
select *
from {{ ref('shopify__customer_emails') }}
), orders as (
select *
from {{ ref('shopify__orders') }}
), customer_calendar as (
select
cast(calendar.date_day as date) as date_month,
customers.email,
customers.first_order_timestamp,
customers.source_relation,
{{ dbt.date_trunc('month', 'first_order_timestamp') }} as cohort_month
from calendar
inner join customers
on cast({{ dbt.date_trunc('month', 'first_order_timestamp') }} as date) <= calendar.date_day
), orders_joined as (
select
customer_calendar.date_month,
customer_calendar.email,
customer_calendar.first_order_timestamp,
customer_calendar.cohort_month,
customer_calendar.source_relation,
coalesce(count(distinct orders.order_id), 0) as order_count_in_month,
coalesce(sum(orders.order_adjusted_total), 0) as total_price_in_month,
coalesce(sum(orders.line_item_count), 0) as line_item_count_in_month
from customer_calendar
left join orders
on customer_calendar.email = orders.email
and customer_calendar.source_relation = orders.source_relation
and customer_calendar.date_month = cast({{ dbt.date_trunc('month', 'created_timestamp') }} as date)
{{ dbt_utils.group_by(n=5) }}
), windows as (
{% set partition_string = 'partition by ' ~ shopify.shopify_partition_by_cols('email', 'source_relation') ~ 'order by date_month rows between unbounded preceding and current row' %}
select
*,
sum(total_price_in_month) over ({{ partition_string }}) as total_price_lifetime,
sum(order_count_in_month) over ({{ partition_string }}) as order_count_lifetime,
sum(line_item_count_in_month) over ({{ partition_string }}) as line_item_count_lifetime,
row_number() over (
partition by {{ shopify.shopify_partition_by_cols('email', 'source_relation') }}
order by date_month asc)
as cohort_month_number
from orders_joined
{% if is_incremental() %}
), backfill_lifetime_sums as (
-- for incremental runs we need to fetch the prior lifetimes to properly continue adding to them
select
source_relation,
email,
max(total_price_lifetime) as previous_total_price_lifetime,
max(order_count_lifetime) as previous_order_count_lifetime,
max(line_item_count_lifetime) as previous_line_item_count_lifetime,
max(cohort_month_number) as previous_cohort_month_number
from {{ this }}
where date_month < {{ shopify.shopify_lookback(from_date="max(date_month)", interval=1, datepart='month') }}
group by 1,2
), final as (
select
windows.date_month,
windows.email,
windows.first_order_timestamp,
windows.cohort_month,
windows.source_relation,
windows.order_count_in_month,
windows.total_price_in_month,
windows.line_item_count_in_month,
backfill_lifetime_sums.previous_cohort_month_number + windows.cohort_month_number as cohort_month_number,
backfill_lifetime_sums.previous_total_price_lifetime + windows.total_price_lifetime as total_price_lifetime,
backfill_lifetime_sums.previous_order_count_lifetime + windows.order_count_lifetime as order_count_lifetime,
backfill_lifetime_sums.previous_line_item_count_lifetime + windows.line_item_count_lifetime as line_item_count_lifetime,
{{ dbt_utils.generate_surrogate_key(['windows.date_month','windows.email','windows.source_relation']) }} as customer_cohort_id
from windows
left join backfill_lifetime_sums
on backfill_lifetime_sums.source_relation = windows.source_relation
and backfill_lifetime_sums.email = windows.email
{% else %}
), final as (
select
*,
{{ dbt_utils.generate_surrogate_key(['date_month','email','source_relation']) }} as customer_cohort_id
from windows
{% endif %}
)
select *
from final
Column Description
| column_name | description |
|---|---|
| cohort_month | The month the cohort belongs to, i.e the first month the customer had an order. |
| cohort_month_number | The 'number' of the `date_month` of the record, i.e. how many months from their start month this cohort occurred |
| customer_cohort_id | Unique key representing a customer in a given month. Hashed on 'date_month','email', and 'source_relation'. |
| The ID of the related customer. | |
| date_month | The calendar month the customer stats relate to. |
| first_order_timestamp | The timestamp of the customer's first order. |
| line_item_count_in_month | Number of line items purchased in the `date_month` |
| line_item_count_lifetime | Number of line items purchased up until and including this `date_month`. |
| order_count_in_month | Number of orders purchased in the `date_month` |
| order_count_lifetime | Number of orders purchased up until and including this `date_month`. |
| source_relation | The schema or database this record came from if you are making use of the `shopify_union_schemas` or `shopify_union_databases` variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
| total_price_in_month | Total amount (in shop currency) purchased in the `date_month` |
| total_price_lifetime | Total amount (in shop currency) up until and including this `date_month`. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
91. model.shopify.shopify__products
Model Description
Each record represents a product in Shopify.
SQL
with products as (
select *
from {{ ref('int_shopify__products_with_aggregates') }}
), product_order_lines as (
select *
from {{ ref('int_shopify__product__order_line_aggregates')}}
), joined as (
select
products.*,
coalesce(product_order_lines.quantity_sold,0) as total_quantity_sold,
coalesce(product_order_lines.subtotal_sold,0) as subtotal_sold,
coalesce(product_order_lines.quantity_sold_net_refunds,0) as quantity_sold_net_refunds,
coalesce(product_order_lines.subtotal_sold_net_refunds,0) as subtotal_sold_net_refunds,
product_order_lines.first_order_timestamp,
product_order_lines.most_recent_order_timestamp,
product_order_lines.avg_quantity_per_order_line as avg_quantity_per_order_line,
coalesce(product_order_lines.product_total_discount,0) as product_total_discount,
product_order_lines.product_avg_discount_per_order_line as product_avg_discount_per_order_line,
coalesce(product_order_lines.product_total_tax,0) as product_total_tax,
product_order_lines.product_avg_tax_per_order_line as product_avg_tax_per_order_line
from products
left join product_order_lines
on products.product_id = product_order_lines.product_id
and products.source_relation = product_order_lines.source_relation
)
select *
from joined
Column Description
| column_name | description |
|---|---|
| is_deleted | Whether the record has been deleted in the source system. |
| _fivetran_synced | The time when a record was last updated by Fivetran. |
| created_timestamp | The date and time when the product was created. |
| handle | A unique human-friendly string for the product. Automatically generated from the product's title. |
| product_id | An unsigned 64-bit integer that's used as a unique identifier for the product. Each id is unique across the Shopify system. No two products will have the same id, even if they're from different shops. |
| product_type | A categorization for the product used for filtering and searching products. |
| published_timestamp | The date and time (ISO 8601 format) when the product was published. Can be set to null to unpublish the product from the Online Store channel. |
| published_scope | Whether the product is published to the Point of Sale channel. |
| title | The name of the product. |
| updated_timestamp | The date and time when the product was last modified. |
| vendor | The name of the product's vendor. |
| total_quantity_sold | Quantity of the product sold. |
| subtotal_sold | Total amount (in shop currency) of the product sold. |
| quantity_sold_net_refunds | Quantity of the product sold, excluding refunds. |
| subtotal_sold_net_refunds | Total amount (in shop currency) of the product sold, excluding refunds. |
| first_order_timestamp | The timestamp the product was first ordered. |
| most_recent_order_timestamp | The timestamp the product was most recently ordered. |
| source_relation | The schema or database this record came from, if you are unioning multiple connectors. Null if not. |
| avg_quantity_per_order_line | Average quantity per order line with this product. |
| product_total_discount | Total discounts associated with the product. |
| product_avg_discount_per_order_line | Average discount per order line with this product. |
| product_total_tax | Total taxes associated with the product. |
| product_avg_tax_per_order_line | Average taxes per order line with this product. |
| count_variants | Count of product variants. |
| has_product_image | If the product has an image. |
| status | The status of the product. Valid values: - active: The product is ready to sell and is available to customers on the online store, sales channels, and apps. By default, existing products are set to active. - archived: The product is no longer being sold and isn't available to customers on sales channels and apps. - draft: The product isn't ready to sell and is unavailable to customers on sales channels and apps. By default, duplicated and unarchived products are set to draft.\n |
| collections | Comma-separated list of collections associated with the product. |
| tags | Comma-separated list of tags associated with the product. |
Column Lineage
⚠️ Column lineage is missing from the provided dbt project.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
🚀 Run Cocoon Deep RAG to automatically generate it for better RAG.
Step 2: Power Copilot with RAG
Ask any question about data pipelines. The copilot knows your large data pipeline, thanks to Cocoon RAG.
Cocoon is open-sourced
Need support or have questions? Contact Us