# Customer LTV Analysis in Snowflake by Acquisition Channel

- URL: https://betteranalyst.com/data-connectors/snowflake/customer-ltv-analysis
- Published: 2026-11-03
- Updated: 2026-11-03
- Author: David Bressler, Founder (https://betteranalyst.com/authors/david-bressler)

> **Summary:** A customer LTV analysis in Snowflake sums each customer's gross profit over a fixed window, such as 12 or 24 months from their first order, and compares the averages by acquisition channel, first product or region. Fixed windows keep cohorts comparable; gross margin keeps LTV comparable with acquisition cost.

Data source: [Snowflake](https://betteranalyst.com/data-connectors/snowflake)

Calculate customer lifetime value from Snowflake order data by acquisition channel and cohort, on a gross-margin basis, with fields, sample output and setup.

## Who this report is for

Growth, finance and marketing analysts deciding where to spend acquisition budget, with order history and customer attributes in Snowflake.

## What the report includes

*Metrics and dimensions in this report*

| Metric | Source field | Why it matters |
| --- | --- | --- |
| Customer | FACT_ORDERS.CUSTOMER_ID (example schema) | The unit LTV is calculated for. |
| First order date | Calculated: MIN(FACT_ORDERS.ORDER_DATE) per customer | Starts each customer's LTV window. |
| Gross profit | FACT_ORDERS.NET_REVENUE − FACT_ORDERS.COGS (example schema) | LTV on gross profit is what you can compare with CAC. |
| Acquisition channel | DIM_CUSTOMER.ACQUISITION_CHANNEL (example schema) | The main lens for budget decisions. |
| 12-month LTV | Calculated: SUM(gross profit) within 365 days of first order ÷ customers | A fixed window that every cohort old enough can be measured on. |
| Repeat rate | Calculated: customers with 2+ orders in window ÷ customers | Explains whether LTV differences come from repeat buying or order size. |

## How to build it in Better Analyst from Snowflake

1. Create a dedicated Snowflake role and user with SELECT on the schemas you want to analyse (the Snowflake connector docs include the SQL).
2. In Better Analyst, open Connectors, choose Add Connector, then Snowflake, and enter the account identifier, username, password, warehouse and database, plus an optional schema and role.
3. Click Test Connection (the warehouse must be running) and save.
4. Ask for 12-month gross-profit LTV by acquisition channel for customers whose first order was at least 12 months ago, with customer counts and repeat rate.

## Questions to ask

- “Calculate 12-month gross-profit LTV by acquisition channel for customers acquired in 2025.”
- “Which first-purchased product leads to the highest 24-month LTV?”
- “Compare LTV for customers acquired with and without a discount.”
- “Show cumulative gross profit per customer by month since first order, by channel.”

## Sample output

Sample data for illustration, not real account data.

*Sample data: 12-month LTV by acquisition channel, 2025 cohorts (illustrative)*

| Channel | Customers | Repeat rate | 12-month gross profit | 12-month LTV |
| --- | --- | --- | --- | --- |
| Organic search | 6,420 | 34.1% | $1,123,500 | $175.00 |
| Paid social | 9,880 | 21.6% | $1,195,480 | $121.00 |
| Referral | 2,150 | 41.8% | $451,500 | $210.00 |
| Marketplace | 4,310 | 15.2% | $353,420 | $82.00 |

## Charts to use

- **12-month LTV by channel** (Bar chart): Average gross-profit LTV per channel, sorted.
- **Cumulative gross profit curves** (Line chart): How quickly each channel's customers accumulate value month by month.

## Keep it up to date

Rerun the LTV table quarterly as a scheduled task; a Data Flow can write it to a Snowflake table so marketing dashboards and spreadsheets read the same numbers.

## Further reading

- [Customer lifetime value formula](https://betteranalyst.com/metrics/customer-lifetime-value)
- [LTV to CAC ratio](https://betteranalyst.com/metrics/ltv-to-cac-ratio)
- [Snowflake connector](https://betteranalyst.com/data-connectors/snowflake)
- [Data Flows](https://betteranalyst.com/product/data-flows)

[Try Better Analyst free](https://betteranalyst.com/signup)

## Frequently asked questions

### Why use a fixed 12-month window instead of lifetime?

Customers acquired last month have had less time to buy than those acquired two years ago. A fixed window measured only on cohorts old enough to complete it keeps channels comparable.

### Should LTV use revenue or gross profit?

Use gross profit when comparing with acquisition cost; revenue LTV overstates what you can afford to spend.

### What if acquisition channel isn't in Snowflake?

Join it from wherever it lives, for example a CRM export in Google Sheets, or use first-touch source from your web analytics, and document the rule in Data Context.

## Related reports

- [Snowflake Dashboard for Revenue: Month, Region and Channel](https://betteranalyst.com/data-connectors/snowflake/revenue-dashboard)
- [Cohort Retention Analysis in BigQuery: Monthly Table](https://betteranalyst.com/data-connectors/bigquery/cohort-retention-analysis)