[Bug] shopify__orders. fixed_amount_discount_amount is calculated incorrectly

justinwagg commented 5 months ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

The field shopify__orders. fixed_amount_discount_amount is filtering for, and aggregating Shipping discounts https://github.com/fivetran/dbt_shopify/blob/7cc06076939937265ab4e11b75a7b34c89c46000/models/shopify__orders.sql#L51

Relevant error log or model output

No response

Expected behavior

The field shopify__orders. fixed_amount_discount_amount is is calculated and filtering for fixed_amount coupons.

dbt Project configurations

N/A

Package versions

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1
  - package: fivetran/shopify
    version: [">=0.11.0", "<0.12.0"]

What database are you using dbt with?

bigquery

dbt Version

Core:
  - installed: 1.5.2
  - latest:    1.7.11 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - bigquery: 1.5.3 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Additional Context

https://shopify.dev/docs/api/admin-rest/2024-04/resources/order#resource-object shows there are three discount code types, fixed_amount, percentage and shipping. This looks like a copy/paste error, and shipping should be updated to fixed_amount.

Ex:

Current sum(case when type = 'shipping' then amount else 0 end) as fixed_amount_discount_amount, Fix sum(case when type = 'fixed_amount' then amount else 0 end) as fixed_amount_discount_amount,

Are you willing to open a PR to help address this issue?

[ ] Yes.
[ ] Yes, but I will need assistance and will schedule time during our office hours for guidance
[X] No.

fivetran-avinash commented 5 months ago

Thanks for introducing this issue @justinwagg ! At first glance I think your changes to get the right fixed_amount_discount_amount make sense. Let me do some verification and I'll get back to you with our plan for addressing it.

fivetran-avinash commented 5 months ago

Hi @justinwagg , was able to confirm that these changes should work! We will accept this task and will fold these updates into a coming sprint.

We will let you know when we have a branch ready for you to test this change.

justinwagg commented 5 months ago

Thanks @fivetran-avinash :)

fivetran-avinash commented 4 months ago

No problem @justinwagg ! Our next sprint start next week so we hope to have a solution ready for you to test out in the week or two after.

fivetran-avinash commented 4 months ago

Hi @justinwagg , we've deployed your suggested changes live in our latest release of dbt_shopify. Upgrade to the newest version of our package in your packages.yml and you should be good to grab the proper fixed amount discounts. https://hub.getdbt.com/fivetran/shopify/latest/

Let me know if you have any additional questions and feel free to submit any other features or bugs that come to mind!

fivetran / dbt_shopify