fivetran / dbt_stripe

Data models for Stripe built using dbt.
https://fivetran.github.io/dbt_stripe/
Apache License 2.0
29 stars 33 forks source link

[Feature] Add `paid_out_of_bound` boolean to better identify invoices paid outside of Stripe #32

Closed ryan-loveland closed 2 years ago

ryan-loveland commented 2 years ago

Is there an existing feature request for this?

Describe the Feature

There is a paid_out_of_bound value in the Stripe API that flags whether an invoice was marked as "Paid outside of Stripe" which is helpful to be able to isolate invoices where the funds weren't collected through Stripe directly.

Link to the API docs: https://stripe.com/docs/api/invoices/object#invoice_object-paid_out_of_band

In my specific use case, I was trying to understand why invoices were marked as paid, but had no charge record. Upon further investigation, this could be due a few different scenarios:

  1. Invoice was paid in full or partially by discounts
  2. Invoice was paid in full or partially by applied balances
  3. Invoice was marked as paid due to the amount is below the min charge amount.
  4. Invoice received a credit note
  5. Invoice was marked as paid outside of Stripe

It would greatly simplify any logic needed to identify each scenario by including the Boolean.

Describe alternatives you've considered

I started to play around with logic based on the invoice.amount_due value to check why no charge was created to clearly show the different scenarios. Right now the solution would be to evaluate whether the discount, applied balance, min charge or credit note covered the entire invoice and then anything remaining would be assumed as Paid Outside of Stripe.

Are you interested in contributing this feature?

Anything else?

No response

fivetran-joemarkiewicz commented 2 years ago

Hi @d3ad-pix3l thanks so much for opening this feature request.

Before we talked further about this feature, I did want to bring light to the metadata fields that are synced within the Fivetran Stripe connector. This field is a JSON object that contains extra fields and information related to your base tables that weren't awarded their own specific field. This is usually due to the fact that not all customers may have these fields.

Do you by chance see the paid_out_of_bond field within the metadata JSON object of your invoice table? If so, you can leverage the feature of this package to pivot out a metadata field in order to leverage it in your down stream analysis.

Let me know if this was helpful. If you are unable to find that field in the metadata object then we can chat more about the possible implementation of this feature.

Thanks!

ryan-loveland commented 2 years ago

@fivetran-joemarkiewicz I checked the metadata column on the invoice table and did not see this value. Looking in Stripe's UI and their Sigma reporting solution I do not see this value in the metadata. In the Sigma reporting it is a separate column on the invoices table.

fivetran-joemarkiewicz commented 2 years ago

Ah I see this is a field that doesn't exist within the metadata column or in the source table at all. If that is the case, then I actually feel that a better first step would be to see if this is something that could be brought into the connector.

Since there doesn't seem to be a very clear and direct way to identify if an invoice was paid_out_of_bound, I am leaning towards trying to bring the field into the connector instead of attempting to recreate in the dbt package. It actually looks like another customer was hoping to do the same with their Feature Request. I think a best next step would be for you to upvote that FR and comment your need for the field within the invoice table.

Reaching out to your account rep should also be able to get more eyes on this to be integrated into the connector. Let me know if you have any other questions!

ryan-loveland commented 2 years ago

@fivetran-joemarkiewicz That makes sense. I was told to create the issue here, but think there might have been some confusion about the ask and what was currently being supported. I have followed and voted the linked feature request and will send an email to our rep. Thanks for your help.

fivetran-joemarkiewicz commented 2 years ago

Happy to help @d3ad-pix3l

Yeah I would be happy to support this within the package but it seems like there would be too many edge cases that we wouldn't be able to account for in our logic if we implemented a transformation to recreate the field. Since it seems to be a similar request from other customers, it may be the most beneficial to add this field into the connector itself. I would rather have the connector produce something 100% accurate than try our best to recreate the field in the dbt package and see it not work in all cases.

I will close this issue as your rep will be able to direct you in the right place following our discussion here. Feel free to have the rep loop me into your correspondence if I can help provide any clarification 😄