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.
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 name | Field | Description |
|---|---|---|
| Product currency | client_currency_id | The currency in which the client sets the product price. Multiple product currencies are supported. |
| Payout currency | payout_currency_id | The 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 currency | main_currency | A 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 name | Field | Description |
|---|---|---|
| Product currency to EUR rate | rate_client_currency_to_eur | The exchange rate used to convert transaction values from the original product currency into euros. |
| EUR to USD rate | rate_eur_to_usd | The 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 name | Field | Description |
|---|---|---|
| Net customer sales revenue | customer_sales_revenue_net | Net amount the customer paid to Cleverbridge excluding taxes. Net Customer Sales Revenue = Gross Transaction Amount – (Applicable Discounts + Shipping Costs) Currency: Product currency |
| Net Cleverbridge fee | fee_net | Net amount retained by Cleverbridge from the client’s revenue. Totals the resale commissions and any applicable affiliate fees. |
| Net client sales revenue | client_sales_revenue_net | Net 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 name | Field | Description |
|---|---|---|
| Forecast amount | forecast_amount | Estimated 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 amount | payout_amount | The 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 amount | Payout 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.
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 revenue and payout summary by currency
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.
Weekly sales values and clearing data
The following queries provide a comprehensive financial view of client performance across different currency perspectives over fixed weekly intervals. They allow clients to analyze the revenue, and payout flows from various angles: how much was earned and paid out in the payout currency, how those figures appear in the original product currency, and how everything aligns when standardized to USD.
Report in payout currency
This query shows weekly totals of net client revenue and actual payout amounts, grouped by payout currency. It converts client revenue into the payout currency using applicable exchange rates and supports validating final clearing totals in each currency.
SELECT
period,
payout_currency_id,
SUM(client_sales_revenue_net_payout_curr) AS client_sales_revenue_net,
SUM(ROUND(payout_amount, 2)) AS payout_amount
FROM (
SELECT
<!-- Define the reporting periods -->
CASE
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-01') AND DATE('2025-05-11') THEN '01/05/2025-11/05/2025'
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-12') AND DATE('2025-05-18') THEN '12/05/2025-18/05/2025'
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-19') AND DATE('2025-05-25') THEN '19/05/2025-25/05/2025'
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-26') AND DATE('2025-05-31') THEN '26/05/2025-31/05/2025'
END AS period,
<!-- Convert client revenue into 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,
payout_currency_id,
payout_amount
<!-- Source of the transaction-level performance data. REPLACE with your data source -->
FROM SUBSCRIPTIONPURCHASES.SUBSCRIPTION_DB_EVENT_LOGS
<!-- Filter records to the reporting month -->
WHERE DATE(report_finance_time) BETWEEN DATE('2025-05-01') AND DATE('2025-05-31')
<!-- Alias 't' assigned to the subquery result for reference in the outer query -->
) t
<!-- Group the aggregated results by weekly period and payout currency -->
GROUP BY period, payout_currency_id;
Report in product currency
The query summarizes weekly financials in the product (client-defined) currency. It highlights what the end customer paid, what the client retained, and what portion was deducted as fees—all in the currency in which the product was originally priced.
SELECT
period,
client_currency_id,
SUM(client_sales_revenue_net) AS client_sales_revenue_net,
SUM(customer_sales_revenue_net) AS customer_sales_revenue_net,
SUM(fee_net) AS fee_net
FROM (
SELECT
purchase_id,
client_currency_id,
<!-- Define fixed weekly periods for reporting -->
CASE
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-01') AND DATE('2025-05-11') THEN '01/05/2025-11/05/2025'
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-12') AND DATE('2025-05-18') THEN '12/05/2025-18/05/2025'
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-19') AND DATE('2025-05-25') THEN '19/05/2025-25/05/2025'
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-26') AND DATE('2025-05-31') THEN '26/05/2025-31/05/2025'
END AS period,
<!-- Select financial fields in product currency -->
client_sales_revenue_net,
customer_sales_revenue_net,
<!-- Calculate total fees as diff between what customer paid and what client receives -->
(customer_sales_revenue_net - client_sales_revenue_net) AS fee_net
<!-- Source of the transaction-level performance data. REPLACE with your data source -->
FROM SUBSCRIPTIONPURCHASES.SUBSCRIPTION_DB_EVENT_LOGS
<!-- Filter to the desired reporting month -->
WHERE DATE(report_finance_time) BETWEEN DATE('2025-05-01') AND DATE('2025-05-31')
<!-- Alias 't' assigned to the subquery result for reference in the outer query -->
) t
<!-- Group results by week and product currency -->
GROUP BY period, client_currency_id;
Standardized report in USD
This query translates all revenue and fees into USD for unified reporting across global markets.
SELECT
period,
SUM(customer_sales_revenue_net_usd) AS customer_sales_revenue_net_usd,
SUM(client_sales_revenue_net_usd) AS client_sales_revenue_net_usd,
SUM(fee_net_usd) AS fee_net_usd
FROM (
SELECT
purchase_id,
client_currency_id,
<!-- Define custom weekly reporting periods -->
CASE
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-01') AND DATE('2025-05-11') THEN '01/05/2025-11/05/2025'
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-12') AND DATE('2025-05-18') THEN '12/05/2025-18/05/2025'
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-19') AND DATE('2025-05-25') THEN '19/05/2025-25/05/2025'
WHEN DATE(report_finance_time) BETWEEN DATE('2025-05-26') AND DATE('2025-05-31') THEN '26/05/2025-31/05/2025'
END AS period,
<!-- Calculate revenue and fees converted to USD -->
(customer_sales_revenue_net - client_sales_revenue_net) AS fee_net,
customer_sales_revenue_net * rate_client_currency_to_eur * rate_eur_to_usd AS customer_sales_revenue_net_usd,
client_sales_revenue_net * rate_client_currency_to_eur * rate_eur_to_usd AS client_sales_revenue_net_usd,
(customer_sales_revenue_net - client_sales_revenue_net) * rate_client_currency_to_eur * rate_eur_to_usd AS fee_net_usd,
payout_currency_id,
payout_amount
<!-- Source of the transaction-level performance data. REPLACE with your data source -->
FROM SUBSCRIPTIONPURCHASES.SUBSCRIPTION_DB_EVENT_LOGS
<!-- Filter to the month of May 2025 -->
WHERE DATE(report_finance_time) BETWEEN DATE('2025-05-01') AND DATE('2025-05-31')
<!-- Alias 't' assigned to the subquery result for reference in the outer query -->
) t
<!-- Group by defined week period -->
GROUP BY period;
Yearly report
You can also create a query to calculate aggregated sales and payout values for clients and customers from within a specific date range with currency conversions applied to normalize amounts into the payout currency (either EUR or USD).
SELECT
client_currency_id,
<!-- Total net amount received by the client (before conversion) -->
SUM(client_sales_revenue_net) AS client_sales_revenue_net,
<!-- Total amount paid by the customer (before conversion) -->
SUM(customer_sales_revenue_net) AS customer_sales_revenue_net,
payout_currency_id,
<!-- Total net client revenue converted to payout currency -->
SUM(client_sales_revenue_net_payout_curr) AS client_sales_revenue_net_payout_curr,
<!-- Total net customer revenue converted to payout currency -->
SUM(customer_sales_revenue_net_payout_curr) AS customer_sales_revenue_net_payout_curr,
<!-- Final payout amount recorded in the clearing report -->
SUM(payout_amount) AS payout_amount,
<!-- Forecasted payout amount based on exchange rate at transaction time -->
SUM(forecast_amount) AS forecast_amount
FROM (
SELECT
payout_currency_id,
client_currency_id,
client_sales_revenue_net,
customer_sales_revenue_net,
<!-- 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_payout_curr,
forecast_amount,
payout_amount
<!-- Source of the transaction-level performance data. REPLACE with your data source -->
FROM SUBSCRIPTIONPURCHASES.SUBSCRIPTION_DB_EVENT_LOGS
<!-- Filter: include transactions from the 2024 calendar year -->
WHERE DATE(report_finance_time) >= DATE('2024-01-01')
AND DATE(report_finance_time) <= DATE('2024-12-31')
<!-- Ensure payout is included only for finalized (cleared) transactions -->
AND clearing_id IS NOT NULL
<!-- Alias 't' assigned to the subquery result for reference in the outer query -->
) t
<!-- Group the aggregated results by product and payout currency -->
GROUP BY
client_currency_id,
payout_currency_id;
Results of the query are yearly financial metrics by product currency, including both original values and their converted equivalents in the designated payout currency. This view allows you to understand how revenues and payouts compare across different currencies and how exchange rates influence the final clearing amounts. Data retrieved from Snowflake is also compared to the final Payout values from the clearing report.