.png)
Frameworks, core principles and top case studies for SaaS pricing, learnt and refined over 28+ years of SaaS-monetization experience.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Introduction
In the competitive SaaS landscape, pricing strategy can make or break your business. While many companies rely on gut feelings or competitor benchmarking to set prices, data-driven organizations are leveraging SQL (Structured Query Language) to extract actionable insights from their pricing data. This analytical approach enables SaaS executives to optimize pricing models, increase revenue, and improve customer retention. In this article, we'll explore how SQL can transform your approach to pricing analytics and help you make more strategic decisions.
Why SQL Matters for SaaS Pricing Analysis
SQL remains the gold standard for database analysis due to its powerful querying capabilities. For SaaS companies with vast amounts of customer, subscription, and pricing data, SQL provides several advantages:
Direct access to raw data: Bypass the limitations of pre-built dashboards and create custom analyses specific to your pricing questions.
Data integration: Combine pricing data with usage metrics, customer demographics, and other datasets to uncover multidimensional insights.
Reproducibility: Create reusable queries that can track pricing performance consistently over time.
Scalability: Efficiently analyze millions of subscription records without performance degradation.
Essential Data Tables for Pricing Analysis
Before diving into specific queries, let's understand the key data tables you'll likely work with:
- Customers: Customer metadata and segmentation information
- Subscriptions: Plan details, start dates, end dates, and renewal information
- Transactions: Payment history, including amounts and dates
- Product Usage: Feature utilization metrics by customer
- Plan_Details: Information about different pricing tiers and features
SQL Queries for Critical Pricing Insights
1. Analyzing Plan Distribution
Understanding how customers distribute across your pricing tiers provides baseline insights for optimization.
SELECT plan_name, COUNT(*) AS customer_count, ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM subscriptions WHERE status = 'active'), 2) AS percentageFROM subscriptionsWHERE status = 'active'GROUP BY plan_nameORDER BY customer_count DESC;This query shows what percentage of your customer base subscribes to each plan, helping you identify your most popular offerings.
2. Measuring Plan Conversion and Upgrade Paths
Track how customers move between different pricing tiers to understand upgrade patterns.
WITH plan_changes AS ( SELECT customer_id, plan_name AS current_plan, LAG(plan_name) OVER (PARTITION BY customer_id ORDER BY start_date) AS previous_plan, start_date FROM subscriptions)SELECT previous_plan, current_plan, COUNT(*) AS transition_countFROM plan_changesWHERE previous_plan IS NOT NULLGROUP BY previous_plan, current_planORDER BY transition_count DESC;This analysis reveals which plans customers most frequently upgrade to or downgrade from, highlighting effective upsell opportunities or potential pricing issues.
3. Revenue Impact of Price Changes
When implementing price changes, it's crucial to measure the actual revenue impact.
SELECT DATE_TRUNC('month', transaction_date) AS month, plan_name, COUNT(DISTINCT customer_id) AS paying_customers, SUM(amount) AS monthly_revenue, SUM(amount) / COUNT(DISTINCT customer_id) AS average_revenue_per_customerFROM transactions tJOIN subscriptions s ON t.subscription_id = s.idWHERE transaction_date BETWEEN '2022-06-01' AND '2023-06-01'GROUP BY DATE_TRUNC('month', transaction_date), plan_nameORDER BY month, plan_name;This query helps you visualize revenue trends before and after pricing changes, showing the tangible impact of your pricing decisions.
4. Churn Analysis by Price Point
One of the most critical pricing analytics questions: are certain price points causing customers to leave?
WITH churned_customers AS ( SELECT customer_id, plan_name, end_date FROM subscriptions WHERE status = 'cancelled' AND end_date BETWEEN '2023-01-01' AND '2023-06-30')SELECT plan_name, COUNT(*) AS churn_count, ROUND(COUNT(*) * 100.0 / ( SELECT COUNT(*) FROM subscriptions WHERE plan_name = c.plan_name AND start_date < '2023-01-01' ), 2) AS churn_rateFROM churned_customers cGROUP BY plan_nameORDER BY churn_rate DESC;This analysis identifies which pricing tiers experience the highest churn, allowing you to address potential price-value mismatches.
5. Feature Usage vs. Willingness to Pay
Understanding which features drive willingness to pay is crucial for pricing optimization.
SELECT s.plan_name, AVG(u.feature_a_usage) AS avg_feature_a_usage, AVG(u.feature_b_usage) AS avg_feature_b_usage, AVG(u.feature_c_usage) AS avg_feature_c_usage, AVG(t.amount) AS avg_monthly_paymentFROM subscriptions sJOIN product_usage u ON s.customer_id = u.customer_idJOIN transactions t ON s.id = t.subscription_idWHERE s.status = 'active' AND t.transaction_date > CURRENT_DATE - INTERVAL '30 days'GROUP BY s.plan_nameORDER BY avg_monthly_payment;This query correlates feature usage with payment levels, helping identify which features could justify premium pricing.
Advanced SQL Techniques for Pricing Optimization
Cohort Analysis
Track how different customer cohorts respond to pricing over time:
WITH customer_cohorts AS ( SELECT customer_id, DATE_TRUNC('month', MIN(start_date)) AS cohort_month FROM subscriptions GROUP BY customer_id)SELECT c.cohort_month, DATE_TRUNC('month', t.transaction_date) AS payment_month, DATEDIFF('month', c.cohort_month, DATE_TRUNC('month', t.transaction_date)) AS months_since_signup, COUNT(DISTINCT c.customer_id) AS customers, SUM(t.amount) AS revenue, SUM(t.amount) / COUNT(DISTINCT c.customer_id) AS arpuFROM customer_cohorts cJOIN subscriptions s ON c.customer_id = s.customer_idJOIN transactions t ON s.id = t.subscription_idGROUP BY c.cohort_month, DATE_TRUNC('month', t.transaction_date), months_since_signupORDER BY c.cohort_month, months_since_signup;This analysis reveals how customer value evolves over time for different acquisition cohorts, which can inform pricing strategy adjustments for new versus established customers.
Price Sensitivity Analysis
Measure how price changes affect conversion rates:
```sql
WITH trialconversions AS ( SELECT t.trialstartdate, p.planprice,
CASE
WHEN s.status = 'active' THEN 1
ELSE 0
END AS converted
FROM
trials t
LEFT JOIN
subscriptions s ON t.customerid = s.customerid AND s.startdate BETWEEN t.trialstartdate AND t.trialstartdate + INTERVAL '30 days' JOIN plandetails p ON s.planname = p.planname
WHERE
t.trialstartdate BETWEEN '2022-01-01' AND '2023-01-01'
)
SELECT
planprice, COUNT(*) AS trialcount,
SUM(converted) AS conversion_count,
ROUND(SUM(converte