[Feature] add staging for discount_allocation

[AaronHagan4]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Our company uses the discount_allocation table for calculating line_item_discount and having these tables staged in this package would be really helpful. I am willing to open a PR for it if someone is willing to review it? I would also then be willing to add line_item_discount into the shopify__order_lines model in the dbt-shopify package if that is something of interest.

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

No response

[elanfivetran]

Hey @AaronHagan4, thanks for reaching out about this feature and we are glad you are interested in contributing to our Shopify package!

If I'm understanding this correctly, it seems that you are primarily looking to have the index field (which I believe you reference as line_item_discount) from the discount_allocation table included in the shopify__order_lines output model. Additionally, you are looking for a discount_allocation staging model that you can reference to bring into the shopify__order_lines output model.

I would need to double check Stripe's relationship between discount allocation and order line items, but it seems that there may be multiple types of discount allocations that can be applied to one order line item. It seems that in the shopify__orders__order_line_aggregates model, we actually aggregate the discount amount from the order_lines tables rather than the discount_allocation table. We will begin investigating this relationship.

So we can better understand the purpose behind your request, do you mind elaborating on why you are looking to add the line_item_discount field to the shopify__order_lines output model and any nuances regarding the discount_allocation table relative to the total_discount field in the order_lines table?

[AaronHagan4]

hey @elanfivetran, thanks for the reply!

In our shop, total_discounts in the line_item table do not provide accurate discounts. The main reason we want to add the discount_allocation staging model is because this total_discounts column does not match our shopify user interface for line_items. I'm not 100% sure why this is but i think it might be because discounts can be applied at different levels.

we pull the order_line discount from the discounts_allocation table;

select
     order_line_id,
     source_relation,
     SUM(amount) AS discount_amount
 from {{ var('shopify_discount_allocation') }}
 group by order_line_id, source_relation

i dont think I'm the first to run into this issue from reading the dbt slack community;
https://getdbt.slack.com/archives/C0VLZPLAE/p1557426649335900
https://getdbt.slack.com/archives/C0VLZPLAE/p1679377714002969

I've been unable to find documentation on how line item discount is calculated but please do share if you do!

[justinwagg]

+1 on this. Line item discounts are important to understanding product revenues. Shopify provides a valuable service in that it takes order level discounts and allocates to line items. The source table discount_allocation holds these item level allocations.

Shopify recommends using it as well. Within the line_items documentation I see total_discount is not accurate

total_discount: The total amount of the discount allocated to the line item in the shop currency. This field must be explicitly set using draft orders, Shopify scripts, or the API. Instead of using this field, Shopify recommends using discount_allocations, which provides the same information.

and it's recommended to use discount_allocations

discount_allocations: An ordered list of amounts allocated by discount applications. Each discount allocation is associated with a particular discount application.
amount: The discount amount allocated to the line in the shop currency.
discount_application_index: The index of the associated discount application in the order's discount_applications list.
amount_set: The discount amount allocated to the line item in shop and presentment currencies.

I think that's what @AaronHagan4 was getting at. I think it's still an issue.