Quickstart transformation miscategorizes bill line

rishi-tripathy commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

I am working with a customer to bring in Quickbooks data by GL.

I see a transaction the GENERAL_LEDGER transformation as being associated with Account 7410, but in Quickbooks it is clearly classified as 7620.

I believe this is because the inference of the account via the item number associated with the bill line is not happening or is happening incorrectly. The Bill itself is associated with account 7410, but the Bill Line in question is a specific item that should be categorized as 7620.

Relevant error log or model output

As described above, there is a diff where more transactions that expected are being categorized in account 7410, since bill lines that are directly associated with an account are not being categorized as such

Expected behavior

Use the ACCOUNT_EXPENSE_ACCOUNT_ID column on Bill Line table to associate bill line items with accounts as part of the transformations, instead of only relying on the parent bill. line items with direct associations to accounts should override their parent bill.

dbt Project configurations

I'm using Fivetran's quickstart transformations for Quickbooks

Package versions

I'm using Fivetran's quickstart transformations for Quickbooks

What database are you using dbt with?

snowflake

dbt Version

I'm using Fivetran's quickstart transformations for Quickbooks

Additional Context

No response

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

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

fivetran-catfritz commented 1 year ago

Hi @rishi-tripathy thank you for opening the issue--you bring up a very interesting case. I will be looking into this in the next week and will let you know if I have any questions!

fivetran-catfritz commented 1 year ago

Hi @rishi-tripathy, following up to my last message, are you seeing this issue in the credit or is this issue with the debit transactions?

fivetran-catfritz commented 1 year ago

Hi @rishi-tripathy have you had a chance to check into this? Thanks!

rishi-tripathy commented 1 year ago

Ah sorry for the delay here Cat! Notifications were piping to my personal email instead of my work one. I'm seeing the impact on debit items in this case. What's the implication of handing them separately?

fivetran-catfritz commented 1 year ago

Thanks @rishi-tripathy ! This is useful to narrow down where the issue might be. I had actually suspected credits, so it's very interesting the issue lies with debits. For debit transactions, we already use account_expense_account_id as the first option to determine account_id for debits (see this line in our model), so I will have to investigate a bit more what could be going on.

fivetran-catfritz commented 1 year ago

Hi @rishi-tripathy. I discussed this issue with my team, and it would be really helpful if you could show us live what is happening. Would you be available for a call in the next week? If so, please send an email to solutions@fivetran.com with your availability, and I'll set up the meet!

fivetran-catfritz commented 1 year ago

Hi @rishi-tripathy just following up if you would be available for a call with our team?

rishi-tripathy commented 1 year ago

Yes, can do cc @@.*** - how is this ( https://calendly.com/r-tripathy/30 ) availability?

Rishi Tripathy | He, Him, His Twitter ( https://twitter.com/rishi_tripathy_ )

On Wed, Nov 15, 2023 at 12:56 PM, fivetran-catfritz < @.*** > wrote:

Hi @ rishi-tripathy ( https://github.com/rishi-tripathy ) just following up if you would be available for a call with our team?

— Reply to this email directly, view it on GitHub ( https://github.com/fivetran/dbt_quickbooks/issues/105#issuecomment-1813001450 ) , or unsubscribe ( https://github.com/notifications/unsubscribe-auth/AJRJLWASOEWU2JXA5FML5FLYET65ZAVCNFSM6AAAAAA6DCEPE2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJTGAYDCNBVGA ). You are receiving this because you were mentioned. Message ID: <fivetran/dbt_quickbooks/issues/105/1813001450 @ github. com>

fivetran-catfritz commented 1 year ago

Thanks @rishi-tripathy! I have scheduled a meeting.

rishi-tripathy commented 1 year ago

Great! Good timing as well - I just saw this issue pop up with another customer account.

The issue this time is with debit transaction types in credit account types. There are transactions included in the model (they're associated with the account number - in this case 4802) that should not be (i.e. they are not showing up as associated with that account in the customer's Quickbooks instance).

Happy to show you more live on our call tomorrow.

If you have a chance to poke around, two affected transactions from the QBO general_ledger transformation can be found by just filtering the entire transform model (org id is 709) by

adjusted_amount =-811 or =-292.40.

Rishi Tripathy | He, Him, His Twitter ( https://twitter.com/rishi_tripathy_ )

On Tue, Nov 21, 2023 at 6:27 PM, fivetran-catfritz < @.*** > wrote:

Thanks @ rishi-tripathy ( https://github.com/rishi-tripathy ) ! I have scheduled a meeting.

— Reply to this email directly, view it on GitHub ( https://github.com/fivetran/dbt_quickbooks/issues/105#issuecomment-1821856257 ) , or unsubscribe ( https://github.com/notifications/unsubscribe-auth/AJRJLWEKAYNV5M5Z4O6BPDTYFU2GNAVCNFSM6AAAAAA6DCEPE2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMRRHA2TMMRVG4 ). You are receiving this because you were mentioned. Message ID: <fivetran/dbt_quickbooks/issues/105/1821856257 @ github. com>

rishi-tripathy commented 1 year ago

In this second case (Would need to go back to confirm for the other case) — I can confirm that:

the bill line in quickbooks.bill_line is associated with the same account (using account_expense_account_id) that it shows up in the model — though this is different than how it's categorized in the customer account. Asked the customer for more context on why this might be the case.

fivetran-catfritz commented 12 months ago

Hi @rishi-tripathy thanks for meeting with us on the call yesterday to go over this issue! To summarize what we found:

The 7410 instead of 7620 account was associated with the bill line in question since in the source bill_line table, the account_expense_account_id did not have a value populated.
This causes the coalesce clause in this line to move on to another field to determine the account to use.

Next steps:

During the call we were unable to establish where the association of 7620 was coming from, so @rishi-tripathy will determine where that path is coming from.

Keep us posted with what you find!

rishi-tripathy commented 12 months ago

Hey team - have an update here. The discrepancy was being caused because the item_expense_account_id of the item associated with the transactions had changed over time.

That is to say: When the item was first created (in Feb), it was associated with account 7620 The transaction occured in Feb and Quickbooks correctly associated the transaction with 7620] at some point between then and now, the item_expense_account_id of the item was updated to be 7410 notably, this doesn't change how the transaction is categorized in Quickbooks.

Now (in November), when the transform runs and sees the item associated with the transaction, it sees a reference to 7410 and therefore puts the transaction in 7410 as per the line referenced

This causes a discrepancy between what hte customer sees in QBO (since the transaction was classified when the item was still associated with 7620), even though the item, today, maps to 7410.

The customer was able to resolve this by overwriting the item associated with the transaction to one with 7620.

I believe that the same customer is experiencing the same issue in a couple of places in their QBO connector, but awaiting confirmation from them. Thanks for investigating together!

rishi-tripathy commented 12 months ago

Not sure how this could prevented in the future - is historical item data captured somehow?

fivetran-catfritz commented 11 months ago

Hi @rishi-tripathy thank you for digging in and summarizing this very interesting find. I spoke with my team, and unfortunately there is currently no historical capture with the connector. Since this is at the connector level, could you open a feature request for the connector itself? You can submit a feature request here, and then I will flag it to the product manager. Thanks again for finding that!

fivetran-catfritz commented 11 months ago

Hi @rishi-tripathy, given the above message, I will mark this ticket as type:wontdo for the time being. If there are developments at the connector level then we will be sure to update the package to reflect the changes.

If anyone else is encountering this issue, please feel free to open a Feature Request for the Fivetran connector so it may be scoped and prioritized accordingly. Thanks!

fivetran / dbt_quickbooks