Skip to main content

Snowflake data export queries

This section provides guidance on how to query Cleverbridge performance data available through the Snowflake Secure Data Share. It is intended to help you extract and analyze data efficiently, using tested field combinations and example queries that reflect common business needs.

note

While Snowflake data is ideal for operational and performance reporting, it is not intended for financial reconciliation. Always refer to your official clearing report for payout and accounting purposes.

Understanding key fields​

Following subsections provide an overview of the fields commonly included in export queries:

Financial report time​

Financial report time (report_finance_time) is the date on which the transaction is financially recognized in the system. It is used for financial reporting and defines when a transaction is included in a clearing report:

  • For payments: Actual payment date; the date when the transaction was settled.
  • For reimbursements/refunds: The date when the reimbursement was processed.

Currencies​

As mentioned in the Why performance data may differ from clearing values section, transactions in Cleverbridge reports involve several currencies that serve different purposes throughout the sales, reporting, and payout processes.

Field nameFieldDescription
Product currencyclient_currency_idThe currency in which the client sets the product price.

Multiple product currencies are supported.
Payout currencypayout_currency_idThe currency in which the funds are transferred to the client.

Payout currencies used for each product currency are defined in the course of the client's onboarding process.
Main currencymain_currencyA unified payout and clearing currency defined in your Cleverbridge account.

Exchange rates​

You can retrieve exchange rate values from Snowflake, to learn what rate was used to calculate a specific value. Reports below, use the following rates:

Field nameFieldDescription
Product currency to EUR raterate_client_currency_to_eurThe exchange rate used to convert transaction values from the original product currency into euros.
EUR to USD raterate_eur_to_usdThe exchange rate used to convert transaction values from EUR to USD.

Financial performance values​

Financial performance values provide insight into the financial flow of each transaction and are key to interpreting business performance and supporting financial reporting.

These values are saved in the Snowflake DB in the corresponding currencies. At the end of the clearing period, these values are converted to the payout currency and used to generate clearing reports.

Field nameFieldDescription
Net customer sales revenuecustomer_sales_revenue_netNet amount the customer paid to Cleverbridge excluding taxes.

Net Customer Sales Revenue = Gross Transaction Amount – (Applicable Discounts + Shipping Costs)

Currency: Product currency
Net Cleverbridge feefee_netNet amount retained by Cleverbridge from the client's revenue. Totals the resale commissions and any applicable affiliate fees.
Net client sales revenueclient_sales_revenue_netNet amount the client receives from Cleverbridge excluding taxes.

Net client sales revenue = Net customer sales revenue – Net Cleverbridge fee

Currency: Product currency

Payout values​

These two fields reflect the payout to the client as a transaction-time estimate and as a final value. While both relate to the revenue a client receives, they differ in timing, accuracy, and source:

Here is the markdown version of the table you shared:

Field nameFieldDescription
Forecast amountforecast_amountEstimated payout in the main currency at the time of transaction, using the exchange rate valid on that day.

Net Customer Sales Revenue × exchange rate to the main currency
Payout amountpayout_amountThe amount Cleverbridge transfers to the client for the transaction during the clearing period in compliance with the clearing report.

As the difference between the two values is important for the overall understanding of the reporting, let us provide a separate table to illustrate it.

Here is the markdown version of the comparison table:

Forecast amountPayout amount
- Early forecasted value- Final settled value
- Used for early reporting and forecasting- Used for financial reports and reconciliations
- Generated at the time of the transaction- Available only after the clearing report is generated
- Calculated using raw data available in Snowflake- Uploaded to Snowflake from the finalized clearing report
- Converted to the main currency as of the transaction date- Converted to the payout currency next day after the end of the clearing period

Query examples​

The following section includes a series of SQL queries designed to extract, transform, and summarize transactional performance data available through the Cleverbridge Secure Data Share on Snowflake. These queries support various reporting perspectives—daily, weekly, and yearly—and cover revenue, fee, and payout metrics across multiple currencies.

Each query demonstrates how raw Snowflake data can be used to:

  • Analyze financial performance at different levels of aggregation.
  • Convert revenue values into reporting or payout currencies.
  • Compare forecasted payouts with finalized amounts from the clearing report.
note

The queries below include a dummy data source. Be sure to replace the FROM clause with your actual Snowflake data source to ensure correct execution.

Daily report​

The following query provides a daily financial breakdown of revenue and payouts, grouped by product and payout currencies. It helps clients understand how much was earned (from both the customer and Cleverbridge perspectives), what portion was retained as fees, and how those values convert into the payout currency.

SELECT
    report_finance_time,
    client_currency_id,
    payout_currency_id,
    SUM(client_sales_revenue_net) AS client_sales_revenue_net,
    SUM(customer_sales_revenue_net) AS customer_sales_revenue_net,
    <!-- Calculated fee: customer revenue - client revenue -->
    SUM(fee) AS fee,
    <!-- Net client revenue converted to payout currency -->
    SUM(client_sales_revenue_net_payout_curr) AS client_sales_revenue_net_payout_curr,
    SUM(payout_amount) AS payout_amount,
    SUM(forecast_amount) AS forecast_amount
FROM (
    SELECT
        DATE(report_finance_time) AS report_finance_time,
        client_currency_id,
        payout_currency_id,
        client_sales_revenue_net,
        customer_sales_revenue_net,
        <!-- Fee calculation -->
        (customer_sales_revenue_net - client_sales_revenue_net) AS fee,
        <!-- Conversion of client revenue to payout currency -->
        CASE
            WHEN payout_currency_id = 'EUR' THEN
                CASE
                    WHEN client_currency_id = 'EUR' THEN client_sales_revenue_net
                    ELSE client_sales_revenue_net * rate_client_currency_to_eur
                END
            WHEN payout_currency_id = 'USD' THEN
                CASE
                    WHEN client_currency_id = 'USD' THEN client_sales_revenue_net
                    ELSE client_sales_revenue_net * rate_client_currency_to_eur * rate_eur_to_usd
                END
        END AS client_sales_revenue_net_payout_curr,
        <!-- Conversion of customer revenue to payout currency -->
        CASE
            WHEN payout_currency_id = 'EUR' THEN
                CASE
                    WHEN client_currency_id = 'EUR' THEN customer_sales_revenue_net
                    ELSE customer_sales_revenue_net * rate_client_currency_to_eur
                END
            WHEN payout_currency_id = 'USD' THEN
                CASE
                    WHEN client_currency_id = 'USD' THEN customer_sales_revenue_net
                    ELSE customer_sales_revenue_net * rate_client_currency_to_eur * rate_eur_to_usd
                END
        END AS customer_sales_revenue_net_net_payout_curr,
        forecast_amount,
        payout_amount
    <!-- Source of the transaction-level performance data. REPLACE with your data source -->
    FROM SUBSCRIPTIONPURCHASES.SUBSCRIPTION_DB_EVENT_LOGS
    <!—Reporting period -->
    WHERE DATE(report_finance_time) >= DATE('2025-05-01')
      AND DATE(report_finance_time) <= DATE('2025-05-31')
<!-- Alias 't' assigned to the subquery result for reference in the outer query -->
) t
GROUP BY
    report_finance_time,
    client_currency_id,
    payout_currency_id;

The report received with the query shows how net client revenue is calculated, how it is affected by fees, and how the payout amount is calculated.

It also displays the delta between the actual payout in the clearing report and the calculated daily revenue in the payout currency. This way, you can see how currency exchange rate differences affect the final values.


Did you find this doc useful?