Rico_Evolve - Fees & sigma data

[graceful ibex]

You may want to create a separate query for each of those. SQL can make aggregating all this data in one query very difficult. What have you tried so far to get the other pieces of data?

[urban tiger]

I had the same thought. I'll be creating a separate query for each category or more if necessary.
I've been able to break out the stripe fee categories as I described above. Including a screenshot of the categories - let me know if the code is needed.

For the Credit Card & Debit Fees:
I got the total transactions by counting all records with a reporting_category = 'charge' in stripe.balance_transaction for a given month.
I got the $ Card Volume by summing the amount with the same conditions described when gathering the total transactions
For Card Network Cost, I summed the amount from records with reporting_category = 'network_cost' in stripe.balance_transaction.
For Card Fees - Volume and Per Auth, I used the custom categories I created that where derived from the description in stripe.balance_transaction.

Not sure if the above is 100% accurate and having a harder time getting the stats for the remaining categories (Stripe Connect Fees, Foreign Exchange Fees, Other Fees, Radar). I figured this can be done using the balance_transaction table and using the right type, reporting categories or otherwise but cant find the right matches for each category - possibly overlooking something or missing additional tables that need added.

[graceful ibex]

Alright, let's take each data point one at a time then. Can you post one of the data points that you've built a query for and include the SQL query you ran to get the data?

[urban tiger]

Sounds like a plan.
Credit Card & Debit Fees:

• Total Transactions:
  ---- number of Credit Card & Debit transactions SELECT DATE_TRUNC( 'MONTH', created::DATE) year_month_created, reporting_category, type, '1 Total Transactions' AS description, COUNT(*) AS value -- [SUM(amount) * .01:$] AS amount FROM stripe.balance_transaction WHERE 1 = 1 AND reporting_category = 'charge' ---- otherwise -- AND type IN ( 'charge', 'payment') GROUP BY year_month_created, reporting_category, type

[graceful ibex]

@urban tiger does the data from that query appear correct based on looking through the number of payments in the dashboard? Keep in mind, some charges may have multiple types of fees, so it's worth doing a select * statement at the beginning to get an idea of what data is counting toward the total

I have to step away, but @willow oak will be available for follow-ups

[willow oak]

👋

[urban tiger]

Thank you @graceful ibex and Hi @willow oak. I'm hoping to get access to the strip account and therefore see the dashboard today. What section of the dashboard do you recommend to compare the above query numbers to?

And can this be done for each of the data values in the mock report outline I sent? That would be very helpful - to know what to compare query results to that is

[willow oak]

I'm not super familiar with the dashboard reports especially around fees and we're focused on helping with questions around using the API & SDKs.

I think once you have real data and real queries, sharing a report for a specific window of time with our support team and asking for any necessary clarifications would be the best bet.

[urban tiger]

Gotcha. Do you have a good contact for the support team to share the data/report with?

[willow oak]

If you visit https://support.stripe.com/contact you should be able to select "Sigma" as a topic to reach the best team to help you

[urban tiger]

Awesome thank you!