Pricing Analytics for a Credit-Based SaaS

Build a pricing analytics system using BigQuery and Hex to model unit economics.

Introduction

30 Second Summary

Every SaaS company has users who cost more to serve than they pay. Figuring out who those users are, how sensitive they are to price changes, and what to do about it is one of the most valuable questions a data scientist can answer.

In this project, you will build a pricing analytics system for a credit-based SaaS company modeled on Lovable. You will compute unit economics, model price elasticity per user segment, and produce a concrete, data-backed pricing recommendation using the same tools Lovable's team uses daily.

What You'll Build

Picture opening a polished Hex notebook, running a few cells, and watching a revenue curve appear that pinpoints exactly where to set your credit allowance for maximum profit.

By the end of this project, you'll have:

  • A BigQuery warehouse you can query to instantly reveal which user segments make money and which ones bleed it.
  • A price elasticity model that quantifies exactly how sensitive each segment is to credit allowance changes, so you know whether raising prices will drive users away or generate top-up revenue.
  • A revenue simulation that identifies the optimal credit allocation per tier with projected revenue impact you could present to a pricing committee.
  • Secret Mission: Design a complete pricing A/B test with sample size calculations, metric definitions, and a ship/no-ship decision framework.

Are there any prerequisites?

None. This guide walks you through setting up every tool from scratch, including Google Cloud (BigQuery Sandbox, no credit card required) and Hex (free Community plan). Basic familiarity with SQL and Python is helpful but not required.

Before We Start

Before we dive into any tools, let's lock in what you're about to build and why it matters.

Set Up BigQuery and Hex

To analyze pricing data, you need somewhere to store it and somewhere to query it. Lovable's data team uses BigQuery as their data warehouse and Hex as their analytics notebook.

In this step, you'll set up the exact same tools so your workflow mirrors what a pricing data scientist does on the job every day.

In this step, get ready to:
  • Create a Google Cloud project with BigQuery Sandbox (free, no credit card).
  • Connect Hex to BigQuery using a service account.
  • Verify the connection by running a test query in Hex.
Create a Google Cloud account and BigQuery Sandbox project

BigQuery Sandbox gives you a fully functional data warehouse with no credit card required. You get 10 GB of storage and 1 TB of query processing per month for free.

  • Open console.cloud.google.com/bigquery in your browser.
  • Sign in with your Google Account, or create a new one.
  • On the welcome page, click Agree and continue.
  • Click Create project.
  • For Project name, enter pricing-analytics.
  • For Organization, select No organization if you are not part of one.
  • Click Create.

You are redirected back to the BigQuery page. A BigQuery sandbox notice confirms the sandbox is enabled.

What is BigQuery Sandbox?

BigQuery Sandbox is a free tier that lets you use BigQuery without a credit card. Tables expire after 60 days of no modification, which is fine for a learning project.

The BigQuery API is automatically enabled for new projects, so you do not need to enable it manually.

Now create a dataset to hold your pricing tables.

  • In the Explorer pane on the left, click your project name (pricing-analytics).
  • Click the three-dot menu next to the project name and select Create dataset.
  • For Dataset ID, enter pricing_analytics.
  • For Location type, select Multi-region and choose US.
  • Leave other settings as defaults and click Create dataset.

Your new pricing_analytics dataset appears under your project in the Explorer pane.

Next, create a service account so Hex can connect to BigQuery securely.

  • In the Google Cloud Console, click the navigation menu (three horizontal lines at the top left).
  • Navigate to IAM & Admin, then Service accounts.
  • Click Create Service Account.
  • For Service account name, enter hex-connection.
  • Click Create and Continue.
  • In the Select a role dropdown, search for and select BigQuery User.
  • Click Add Another Role.
  • Search for and select BigQuery Data Editor.
  • Click Continue, then Done.

What is a service account?

A service account is a special Google Cloud identity used by applications (not humans) to authenticate. Hex will use this service account to run queries against your BigQuery dataset without needing your personal login.

Now download the JSON key file that Hex will use to authenticate.

  • On the Service accounts page, click the email of the hex-connection service account you just created.
  • Click the Keys tab.
  • Click Add Key, then select Create new key.
  • Select JSON as the key type and click Create.
  • A JSON file downloads to your computer. Note where it saves. You cannot download this file again.

Store your key file safely

Move the downloaded JSON file to a folder you will remember, such as your Desktop or Documents folder. You will need to find it in a moment when setting up the Hex connection.

Create a Hex Community account and connect to BigQuery

Hex is a collaborative notebook that combines SQL cells and Python cells in one place. The free Community plan includes up to 5 notebooks and Medium compute (8 GB, 1 CPU).

  • Open app.hex.tech in your browser and sign up for a free Community account (no credit card required).
  • Once inside your workspace, click Settings in the left sidebar.
  • Click Data sources.
  • Click + Connection.
  • Select BigQuery from the list.
  • In the Name field, enter pricing-bq.
  • In the Project ID field, enter pricing-analytics.
  • In the Service account configuration (JSON) field, open your downloaded JSON key file in a text editor, copy the entire contents, and paste them into this field.
  • Click Create connection.

Connection failing?

Double-check that the Project ID matches your Google Cloud project ID exactly. You can find it by clicking your project name in the Google Cloud Console header.

Also make sure you pasted the entire JSON file contents, including the opening { and closing }.

Need more help? Why is my Hex BigQuery connection failing?

Verify the connection works

The fastest way to confirm everything is wired correctly is to run a simple query from Hex against BigQuery.

  • From the Hex homepage, click New Project.
  • Name your notebook Pricing Analytics.
  • In the notebook, click + Add cell and choose SQL.
  • In the data connection dropdown at the top of the SQL cell, select pricing-bq.

Before you run the query, what do you expect to see if the connection is working?

  • Paste the following query into the SQL cell and click Run:
SELECT 1 AS connection_test

You should see a single row returned with the value 1 in a column called connection_test. That confirms Hex can successfully query BigQuery through your service account.

Not seeing a result?

Make sure you selected pricing-bq in the data connection dropdown at the top of the SQL cell. If no connections appear, return to Settings and confirm the connection was created successfully.

If you see a permissions error, verify that your service account has both the BigQuery User and BigQuery Data Editor roles in the Google Cloud Console under IAM & Admin.

Still stuck? Help me fix my BigQuery connection test in Hex

Your data infrastructure is ready. Next up, you'll generate synthetic SaaS data and load it into BigQuery so you have something real to analyze.

Generate and Load Your SaaS Dataset

Your BigQuery warehouse and Hex notebook are connected and ready. Now you need data to analyze.

In a real pricing data scientist role, this data already exists in the warehouse. Here you will generate synthetic data that mirrors Lovable's credit-based pricing model so you can practice the exact same analytical workflow on realistic numbers.

In this step, get ready to:
  • Generate synthetic SaaS data locally with a Python script.
  • Upload four CSV files to BigQuery tables.
  • Run exploratory queries in Hex to confirm the data is correct.
Generate synthetic data locally with Python

The script you are about to create models Lovable's pricing structure exactly. It generates 2,000 users spread across three tiers (Free, Pro, Business) with four usage segments (light, moderate, heavy, power), then simulates 12 months of credit consumption and infrastructure costs.

  • Create a new file called generate_data.py on your Desktop by running:
cd ~/Desktop
touch generate_data.py
  • Open generate_data.py in any text editor.
  • Add the imports and pricing model configuration by pasting this code:
import pandas as pd
import numpy as np

np.random.seed(42)

# Configuration matching Lovable's pricing model
N_USERS = 2000
MONTHS = 12
START_DATE = "2025-08-01"

PLANS = {
    "free": {"price": 0, "credits_included": 30, "proportion": 0.50},
    "pro": {"price": 25, "credits_included": 100, "proportion": 0.35},
    "business": {"price": 50, "credits_included": 100, "proportion": 0.15},
}

SEGMENTS = {
    "light": {"usage_multiplier": 0.3, "proportion": 0.30},
    "moderate": {"usage_multiplier": 0.7, "proportion": 0.35},
    "heavy": {"usage_multiplier": 1.2, "proportion": 0.20},
    "power": {"usage_multiplier": 2.5, "proportion": 0.15},
}

What does this configuration represent?

The PLANS dictionary mirrors Lovable's actual pricing tiers: Free at $0 with 30 monthly credits, Pro at $25 with 100 credits, and Business at $50 with 100 credits.

The SEGMENTS dictionary defines four usage behaviors. A power user consumes 2.5x their credit allowance on average, while a light user only uses 30% of it. This spread is what creates the cross-subsidy problem you will uncover later.

  • Add the user generation code below the configuration. This is the first half of the loop that assigns each user a plan and segment:
# --- Generate Users ---
users = []
for i in range(N_USERS):
    plan_roll = np.random.random()
    if plan_roll < PLANS["free"]["proportion"]:
        plan = "free"
    elif plan_roll < PLANS["free"]["proportion"] + PLANS["pro"]["proportion"]:
        plan = "pro"
    else:
        plan = "business"

    seg_roll = np.random.random()
    cumulative = 0
    segment = "light"
    for seg_name, seg_info in SEGMENTS.items():
        cumulative += seg_info["proportion"]
        if seg_roll < cumulative:
            segment = seg_name
            break
  • Directly below (still inside the same for loop), add the signup date, churn logic, and the append that builds the user list:

    signup_date = pd.Timestamp(START_DATE) - pd.Timedelta(days=np.random.randint(30, 400))
    region = np.random.choice(["us", "eu", "apac"], p=[0.5, 0.3, 0.2])

    churned = np.random.random() < 0.15
    churn_date = (
        signup_date + pd.Timedelta(days=np.random.randint(60, 300))
        if churned
        else pd.NaT
    )

    users.append(
        {
            "user_id": i + 1,
            "signup_date": signup_date.strftime("%Y-%m-%d"),
            "plan": plan,
            "user_segment": segment,
            "region": region,
            "churn_date": churn_date.strftime("%Y-%m-%d") if not pd.isna(churn_date) else None,
        }
    )

users_df = pd.DataFrame(users)

What does the user generation do?

Each user gets a random plan (weighted 50/35/15 across Free/Pro/Business) and a random usage segment (weighted 30/35/20/15 across light/moderate/heavy/power).

About 15% of users churn at random points. The churn_date column lets you compute user lifespans later for LTV calculations.

  • Add the subscriptions generation below the users section:
# --- Generate Subscriptions ---
subscriptions = []
for _, user in users_df.iterrows():
    plan_info = PLANS[user["plan"]]
    subscriptions.append(
        {
            "subscription_id": user["user_id"],
            "user_id": user["user_id"],
            "plan": user["plan"],
            "start_date": user["signup_date"],
            "monthly_price": plan_info["price"],
            "credits_included": plan_info["credits_included"],
        }
    )

subscriptions_df = pd.DataFrame(subscriptions)
  • Add the first part of the credit usage generation. This sets up the monthly loop and calculates base usage per user:
# --- Generate Monthly Credit Usage ---
credit_usage = []
months = pd.date_range(start=START_DATE, periods=MONTHS, freq="MS")

for _, user in users_df.iterrows():
    plan_info = PLANS[user["plan"]]
    seg_info = SEGMENTS[user["user_segment"]]
    base_credits = plan_info["credits_included"]

    for month in months:
        if user["churn_date"] and pd.Timestamp(user["churn_date"]) < month:
            continue
        if pd.Timestamp(user["signup_date"]) > month:
            continue

        expected_usage = base_credits * seg_info["usage_multiplier"]
        actual_usage = max(1, int(np.random.normal(expected_usage, expected_usage * 0.2)))
  • Continue directly below with the price variation logic and the append that builds the credit usage list:

        effective_credits = base_credits
        if user["plan"] == "pro" and month >= months[6]:
            if user["user_id"] % 5 < 2:
                effective_credits = 80
                elasticity = -0.5 if user["user_segment"] in ["power", "heavy"] else -0.3
                price_ratio = base_credits / effective_credits
                usage_adjustment = price_ratio ** elasticity
                actual_usage = int(actual_usage * usage_adjustment)

        credit_usage.append(
            {
                "user_id": user["user_id"],
                "month": month.strftime("%Y-%m-%d"),
                "credits_used": actual_usage,
                "credits_included": effective_credits,
                "effective_price_per_credit": (
                    plan_info["price"] / effective_credits
                    if effective_credits > 0
                    else 0
                ),
            }
        )

credit_usage_df = pd.DataFrame(credit_usage)

Why does the credit allowance change mid-dataset?

Starting in month 7, 40% of Pro users get a reduced allowance (80 credits instead of 100). This simulates a natural experiment where some users face a higher effective price per credit.

The usage adjustment uses a pre-set elasticity value to model how users respond to the price change. This built-in variation is what makes the elasticity regression possible in Step 4.

  • Add the costs generation and the save logic to complete the script:
# --- Generate Costs ---
costs = []
COST_PER_CREDIT = 0.12

for _, usage_row in credit_usage_df.iterrows():
    base_cost = usage_row["credits_used"] * COST_PER_CREDIT
    fixed_overhead = 2.0
    total_cost = base_cost + fixed_overhead + np.random.normal(0, 0.5)
    total_cost = max(0.5, total_cost)

    costs.append(
        {
            "user_id": usage_row["user_id"],
            "month": usage_row["month"],
            "infrastructure_cost": round(total_cost, 2),
        }
    )

costs_df = pd.DataFrame(costs)

# --- Save to CSV ---
users_df.to_csv("users.csv", index=False)
subscriptions_df.to_csv("subscriptions.csv", index=False)
credit_usage_df.to_csv("credit_usage.csv", index=False)
costs_df.to_csv("costs.csv", index=False)

print("Data generated successfully!")
print(f"Users: {len(users_df)} rows")
print(f"Subscriptions: {len(subscriptions_df)} rows")
print(f"Credit usage: {len(credit_usage_df)} rows")
print(f"Costs: {len(costs_df)} rows")

What does the cost model represent?

Each credit a user consumes costs $0.12 in infrastructure (compute, storage, API calls to LLM providers). There is also a $2.00 fixed overhead per active user per month regardless of usage.

This means heavy users cost far more to serve than their subscription covers. That gap is the unit economics problem you will quantify in Step 3.

Before you run this, what do you expect to see in the terminal output?

  • Save generate_data.py and run it by executing:
python generate_data.py

You should see output confirming four files were created with approximately 2,000 users, 2,000 subscriptions, 18,000+ credit usage rows, and matching cost rows.

  • Confirm the CSV files exist by running:
ls *.csv

You should see users.csv, subscriptions.csv, credit_usage.csv, and costs.csv listed.

Seeing ModuleNotFoundError?

If you see an error about pandas or numpy not being found, install them first by running pip install pandas numpy and then re-run the script.

If python is not recognized, try python3 generate_data.py instead.

help me fix the ModuleNotFoundError when running generate_data.py

✔️ Awesome, I've got everything!

Great. Double check you saved generate_data.py and that all four CSV files appear when you run ls *.csv.

ⓧ I'd like to double check the full code

Scroll up and confirm your generate_data.py contains each of these sections in order:

  • Imports and configuration (PLANS and SEGMENTS dictionaries).
  • Generate Users (loop assigning plan, segment, signup date, churn).
  • Generate Subscriptions (loop mapping each user to their plan details).
  • Generate Monthly Credit Usage (nested loop with price variation logic for Pro users in months 7-12).
  • Generate Costs (loop computing infrastructure cost per credit used).
  • Save to CSV (four .to_csv() calls plus print statements).
Upload CSVs to BigQuery

Your four CSV files now need to live in BigQuery so you can query them with SQL from Hex.

  • Return to the BigQuery console in your browser.
  • In the Explorer pane on the left, expand your pricing-analytics project.
  • Click the pricing_analytics dataset to select it.
  • Click Create table.

You will now upload the first table.

  • Under Source, set Create table from to Upload.
  • Click Browse and select users.csv from your Desktop.
  • Set File format to CSV.
  • Under Destination, enter users as the table name.
  • Under Schema, check the Auto detect box.
  • Click Create table.

You should see a success notification and the users table appear under your dataset in the Explorer pane.

  • Repeat the same process for each remaining CSV file, using these table names:
  • subscriptions.csv with table name subscriptions.
  • credit_usage.csv with table name credit_usage.
  • costs.csv with table name costs.

After uploading all four files, your pricing_analytics dataset should show four tables in the Explorer pane: users, subscriptions, credit_usage, and costs.

Table not appearing after upload?

Make sure you selected the pricing_analytics dataset (not the project) before clicking Create table. If the table landed in the wrong location, delete it and repeat the upload with the correct dataset selected.

If the upload fails with a schema error, confirm the Auto detect checkbox is checked and the file format is set to CSV.

help me troubleshoot my BigQuery CSV upload failing

Run exploratory queries in Hex to confirm the data

The tables are loaded. Now you will query them from Hex to confirm the data is accessible and correctly structured.

  • Switch back to your Pricing Analytics notebook in Hex.
  • Add a new SQL cell below your existing connection test cell.
  • Make sure the pricing-bq connection is selected in the cell's data source dropdown.
  • Query the user counts by plan by pasting:
SELECT plan, COUNT(*) as user_count FROM pricing_analytics.users GROUP BY plan

What do you expect the output to show before you run it?

  • Run the cell by pressing Cmd+Enter (macOS) or Ctrl+Enter (Windows).

You should see three rows: free, pro, and business with counts totaling approximately 2,000 users.

What do the counts tell you?

The proportions should roughly match the configuration in your script: about 50% free, 35% pro, and 15% business. Small variations from these exact percentages are normal due to random sampling.

  • Now add a Python cell below the SQL cell to create a quick visualization.
  • Hex automatically stores your SQL cell output as a DataFrame. Name the SQL cell's output variable dataframe_1 by checking the variable name field at the top of the SQL cell.
  • Paste this code into the Python cell:
import matplotlib.pyplot as plt

# Create a bar chart of users by plan
fig, ax = plt.subplots(figsize=(8, 4))
ax.bar(dataframe_1["plan"], dataframe_1["user_count"], color=["#90caf9", "#1976d2", "#0d47a1"])
ax.set_xlabel("Plan")
ax.set_ylabel("User Count")
ax.set_title("Users by Plan")
plt.tight_layout()
plt.show()
  • Run the Python cell.

You should see a bar chart with three bars showing Free as the tallest (around 1,000 users), Pro in the middle (around 700), and Business as the shortest (around 300).

Getting a NameError for dataframe_1?

Make sure you ran the SQL cell above first. Hex creates the DataFrame variable only after the SQL cell executes successfully.

Also confirm the variable name at the top of the SQL cell matches dataframe_1. Hex sometimes auto-names it differently if you have other cells.

help me fix a NameError in my Hex Python cell

Your warehouse has real data, your notebook can query it, and you have confirmed the structure matches what you expect. Next up, you will use this data to compute unit economics and find which user segments are losing money.

Compute Unit Economics by Segment

You have a warehouse full of subscription, usage, and cost data. But looking at averages across all users hides a critical reality: some users generate strong margins, while others cost more to serve than they pay.

Before recommending any pricing change, you need to isolate which segments make money and which ones destroy it. In this step, you will compute revenue, cost-to-serve, and margin per user segment, estimate LTV (lifetime value) by segment, and visualize the cross-subsidy problem that hides inside aggregate metrics.

In this step, get ready to:
  • Calculate revenue, cost-to-serve, and margin per user segment.
  • Estimate lifetime value (LTV) by segment.
  • Visualize the cross-subsidy problem with a chart and written interpretation.
Calculate revenue, cost-to-serve, and margin per user per month

Your four tables contain the raw ingredients: who pays what, how many credits they consume, and how much infrastructure that consumption costs. A single SQL query joining these tables reveals the margin picture per plan and segment.

  • In your Hex notebook, add a new SQL cell below your existing exploratory cells.
  • Make sure the pricing-bq connection is selected in the cell's data source dropdown.
  • Paste the following query to compute monthly metrics per plan and user segment:
WITH monthly_metrics AS (
  SELECT
    u.user_id,
    u.plan,
    u.user_segment,
    cu.month,
    s.monthly_price AS revenue,
    c.infrastructure_cost AS cost,
    s.monthly_price - c.infrastructure_cost AS margin,
    cu.credits_used,
    cu.credits_included
  FROM pricing_analytics.users u
  JOIN pricing_analytics.subscriptions s ON u.user_id = s.user_id
  JOIN pricing_analytics.credit_usage cu ON u.user_id = cu.user_id
  JOIN pricing_analytics.costs c ON u.user_id = c.user_id AND cu.month = c.month
)
SELECT
  plan,
  user_segment,
  COUNT(DISTINCT user_id) AS users,
  ROUND(AVG(revenue), 2) AS avg_monthly_revenue,
  ROUND(AVG(cost), 2) AS avg_monthly_cost,
  ROUND(AVG(margin), 2) AS avg_monthly_margin,
  ROUND(AVG(margin) / NULLIF(AVG(revenue), 0) * 100, 1) AS margin_pct,
  ROUND(AVG(credits_used), 0) AS avg_credits_used,
  ROUND(AVG(credits_included), 0) AS avg_credits_included
FROM monthly_metrics
WHERE plan != 'free'
GROUP BY plan, user_segment
ORDER BY plan, margin_pct ASC

What does this query do?

The CTE monthly_metrics joins all four tables on user_id and month to produce one row per user per month containing their revenue (subscription price), cost (infrastructure), and margin (revenue minus cost).

The outer SELECT aggregates these monthly rows by plan and user_segment. The key column is margin_pct which shows what percentage of revenue remains after serving each segment. Negative values mean the segment costs more to serve than it pays.

  • Rename the SQL cell's output to dataframe_1 by double-clicking the green dataframe name that appears beneath the cell after you run it.
  • Run the cell by pressing Cmd+Return (macOS) or Ctrl+Enter (Windows).

Before you look at the results, predict: do you think all paid segments are profitable, or will some show a negative margin?

You should see 8 rows (4 segments for each of the 2 paid plans). The power segment in the Pro tier shows a negative margin_pct (approximately -15%), while light users show margins above +70%.

Seeing zero rows or an error?

  • Check that your SQL cell's data source is set to pricing-bq (not "dataframes"). You need the warehouse connection to query BigQuery tables directly.
  • Verify the dataset name is pricing_analytics in the FROM clauses. If your BigQuery project ID differs, prefix the dataset: your-project-id.pricing_analytics.users.
  • If you see a "table not found" error, return to the BigQuery console and confirm all four tables (users, subscriptions, credit_usage, costs) exist in the pricing_analytics dataset.

Help me debug my unit economics SQL query in Hex

Compute LTV by segment

Margin tells you how much a segment earns per month. But some segments stay longer than others. LTV (average monthly margin multiplied by average lifespan) tells you how much total value a segment creates or destroys over its full lifetime with the product.

  • Add another SQL cell below the unit economics cell.
  • Confirm pricing-bq is selected as the data source.
  • Paste this query to estimate LTV per segment:
WITH user_lifespans AS (
  SELECT
    user_id,
    plan,
    user_segment,
    DATE_DIFF(
      COALESCE(PARSE_DATE('%Y-%m-%d', churn_date), DATE '2026-08-01'),
      PARSE_DATE('%Y-%m-%d', signup_date),
      MONTH
    ) AS lifespan_months
  FROM pricing_analytics.users
  WHERE plan != 'free'
),
monthly_margins AS (
  SELECT
    u.user_id,
    u.plan,
    u.user_segment,
    AVG(s.monthly_price - c.infrastructure_cost) AS avg_monthly_margin
  FROM pricing_analytics.users u
  JOIN pricing_analytics.subscriptions s ON u.user_id = s.user_id
  JOIN pricing_analytics.costs c ON u.user_id = c.user_id
  GROUP BY u.user_id, u.plan, u.user_segment
)
SELECT
  mm.plan,
  mm.user_segment,
  COUNT(*) AS users,
  ROUND(AVG(mm.avg_monthly_margin), 2) AS avg_monthly_margin,
  ROUND(AVG(ul.lifespan_months), 1) AS avg_lifespan_months,
  ROUND(AVG(mm.avg_monthly_margin) * AVG(ul.lifespan_months), 2) AS estimated_ltv
FROM monthly_margins mm
JOIN user_lifespans ul ON mm.user_id = ul.user_id
GROUP BY mm.plan, mm.user_segment
ORDER BY estimated_ltv ASC

What does this query do?

The first CTE calculates each user's lifespan in months. Users who have not churned get a lifespan from signup to today. Users who have churned get the lifespan from signup to their churn date.

The second CTE computes each user's average monthly margin. The final SELECT multiplies these together: estimated_ltv = avg_monthly_margin * avg_lifespan_months. A negative LTV means the segment destroys value over its entire relationship with the product.

  • Run the cell.

You should see the power segment in Pro at the bottom of the list with a negative estimated_ltv. This confirms that these users are not just temporarily unprofitable. They destroy value over their entire lifetime.

Getting a DATE_DIFF or PARSE_DATE error?

  • These are BigQuery Standard SQL functions. Make sure your connection is running Standard SQL (the default for all new projects since 2026).
  • If churn_date is stored as a DATE type rather than a STRING, remove the PARSE_DATE wrapper and use the column directly.

Help me fix a DATE_DIFF error in my BigQuery LTV query

Visualize the cross-subsidy problem

Numbers in a table are precise but not persuasive. A horizontal bar chart with red (loss-making) and green (profitable) bars makes the cross-subsidy problem impossible to miss. This is what you would present to a pricing committee.

  • Add a Python cell below your LTV query.
  • Paste the following code to create the margin visualization using the output from your first SQL cell:
import matplotlib.pyplot as plt

# dataframe_1 is the output of the Unit Economics SQL cell above
df = dataframe_1.copy()

fig, ax = plt.subplots(figsize=(10, 6))

# Create labels combining plan and segment
df["label"] = df["plan"] + " / " + df["user_segment"]
df_sorted = df.sort_values("margin_pct")

colors = ["#d32f2f" if x < 0 else "#388e3c" for x in df_sorted["margin_pct"]]
ax.barh(df_sorted["label"], df_sorted["margin_pct"], color=colors)

ax.axvline(x=0, color="black", linewidth=0.8, linestyle="--")
ax.set_xlabel("Margin %")
ax.set_title("Monthly Margin by Plan and User Segment")
ax.set_xlim(-50, 100)

for i, (val, label) in enumerate(zip(df_sorted["margin_pct"], df_sorted["label"])):
    ax.text(val + 1, i, f"{val:.1f}%", va="center", fontsize=9)

plt.tight_layout()
plt.show()

What does this code do?

It reads the output of your first SQL cell (dataframe_1) and creates a combined label like pro / power for each row. Bars are sorted by margin percentage so the worst performers land at the top.

Red bars (#d32f2f) indicate segments with negative margin. Green bars (#388e3c) indicate profitable segments. The dashed vertical line at zero makes the threshold visually clear.

  • Run the Python cell.

You should see a horizontal bar chart. The pro / power bar extends into the red (negative) territory on the left, while pro / light and business / light bars stretch far to the right in green.

Seeing a NameError for dataframe_1?

  • The Python cell references dataframe_1 which is the output of your first unit economics SQL cell. Double-click the green dataframe name beneath that SQL cell and confirm it reads dataframe_1.
  • Make sure you ran the SQL cell first. Hex cells execute in dependency order, but if the SQL cell has not been run yet, the variable will not exist.

Help me fix a NameError in my Hex Python cell

  • Add a Markdown cell below the chart.
  • Paste this interpretation of your findings:
## Cross-Subsidy Finding

Power users on the Pro plan consume 3-4x their credit allowance in infrastructure, making them margin-negative (~-15%). Light users show margins above +70% and effectively subsidize them.

This is the core pricing problem: heavy users get a bargain at the expense of light users who are overpaying relative to what they consume.

Why write findings in markdown?

In a real pricing analysis, your notebook is the deliverable. Writing clear conclusions alongside your charts shows you can translate numbers into business insight. An interviewer scrolling through your notebook should understand the finding without re-reading the SQL.

  • Run the Markdown cell to render it.

Your notebook now shows the complete unit economics picture: which segments profit, which ones bleed, and how much lifetime value each creates or destroys.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your notebook. You should have four new cells: the unit economics SQL cell, the LTV SQL cell, the Python visualization cell, and the markdown cell.

ⓧ I'd like to double check the full code

Scroll through your notebook and confirm these four cells are present in order, each with the correct output:

  • Unit Economics SQL cell (output named dataframe_1): 8 rows showing plan, user_segment, users, avg_monthly_revenue, avg_monthly_cost, avg_monthly_margin, margin_pct, avg_credits_used, avg_credits_included.
  • LTV SQL cell: 8 rows showing plan, user_segment, users, avg_monthly_margin, avg_lifespan_months, estimated_ltv.
  • Python cell: a horizontal bar chart titled "Monthly Margin by Plan and User Segment" with red bars for negative margins and green bars for positive margins.
  • Markdown cell: explains the cross-subsidy finding (power users margin-negative at approximately -15%, light users above +70%).

You now know exactly which users lose money and how much lifetime value they destroy. Next, you will quantify how sensitive each segment is to price changes. That elasticity estimate tells you whether raising effective prices on power users will fix the margin problem or just drive them away.

Model Price Elasticity per Segment

You now know which user segments lose money. Power users on the Pro plan are margin-negative, while light users effectively subsidize them.

Before recommending any pricing change, you need to quantify how sensitive each segment is to price changes. If power users are very elastic (they leave when prices rise), adjusting credit allowances backfires. If they are inelastic, you have room to fix the margin problem without losing them. In this step, you will estimate price elasticity of demand using a log-log regression on the observational price variation already embedded in your data.

In this step, get ready to:
  • Fit a pooled log-log elasticity model across all users and discover it fails.
  • Segment the model per user type and produce reliable elasticity estimates.
  • Interpret the coefficients to decide whether pricing adjustments are viable.
Attempt a pooled elasticity model

A log-log regression is the standard industry approach for estimating price elasticity. In a log-log model, the coefficient directly gives you the elasticity: a 1% change in price maps to a coefficient-percent change in quantity demanded.

Your first instinct might be to fit a single model across all users at once. Let's try that and see what happens.

  • In your Hex notebook, add a new SQL cell below your existing cells.
  • Select your pricing-bq connection.
  • Paste the following query to pull all credit usage rows where a price exists:
SELECT * FROM pricing_analytics.credit_usage WHERE effective_price_per_credit > 0
  • Run the cell. Rename the output dataframe to dataframe_2 by double-clicking the dataframe name beneath the cell.

Why rename the dataframe?

In Hex, every SQL cell returns a named dataframe that downstream Python cells can reference. Naming it dataframe_2 keeps your cells aligned with the Python code you will paste next.

  • Add a new Python cell below the SQL cell. Paste this code to fit a pooled OLS model across all users:
import numpy as np
import statsmodels.api as sm

# dataframe_2 comes from a SQL cell that pulls credit_usage with effective_price_per_credit
# SQL: SELECT * FROM pricing_analytics.credit_usage WHERE effective_price_per_credit > 0
df_usage = dataframe_2.copy()

# Filter to paid users only (effective_price > 0)
df_paid = df_usage[df_usage["effective_price_per_credit"] > 0].copy()

# Log transformation for log-log model
df_paid["log_credits_used"] = np.log(df_paid["credits_used"])
df_paid["log_price"] = np.log(df_paid["effective_price_per_credit"])

# Pooled model across ALL users
X_pooled = sm.add_constant(df_paid["log_price"])
y_pooled = df_paid["log_credits_used"]

model_pooled = sm.OLS(y_pooled, X_pooled).fit()

print("=== POOLED MODEL (All Users) ===")
print(f"Elasticity coefficient: {model_pooled.params['log_price']:.4f}")
print(f"R-squared: {model_pooled.rsquared:.4f}")
print(f"P-value: {model_pooled.pvalues['log_price']:.4f}")
print(f"\nThis model has LOW R-squared because mixing segments adds noise.")
print("The elasticity estimate is unreliable. We need to segment.")

What does this code do?

It takes the natural log of both credits_used and effective_price_per_credit. In a log-log regression, the slope coefficient IS the elasticity: percentage change in usage per 1% change in price.

The model fits one line through all users regardless of whether they are light browsers or power builders.

Before you run this cell, make a prediction: do you think one regression line will capture the behavior of users who range from 30 credits/month to 250 credits/month?

  • Run the Python cell.

You will see an R-squared below 0.15 and a coefficient that may even have the wrong sign. The pooled model is unreliable because mixing segments with wildly different behaviors produces noise.

Why did the pooled model fail?

Mixing light users (who barely touch their credits) with power users (who burn 2-3x their allowance) violates the homogeneity assumption of OLS. The regression tries to draw one line through clouds of data that live in completely different regions of the price-usage space.

This is a common pitfall in pricing analytics. Segmentation is not optional. It is the minimum requirement for a usable elasticity estimate.

Seeing an import error for statsmodels?

Hex Community includes statsmodels in its default environment. If you see a ModuleNotFoundError, try restarting the kernel from the cell menu, then re-run the cell.

Help me fix my statsmodels import error in Hex.

Segment the regression

The fix is straightforward: fit a separate model for each user segment. Users within the same segment share similar usage patterns, so the homogeneity assumption holds.

  • Add a new SQL cell below your pooled model cell.
  • Select the pricing-bq connection and paste this query to pull usage data with segment information:
SELECT
  cu.user_id,
  cu.month,
  cu.credits_used,
  cu.credits_included,
  cu.effective_price_per_credit,
  u.plan,
  u.user_segment
FROM pricing_analytics.credit_usage cu
JOIN pricing_analytics.users u ON cu.user_id = u.user_id
WHERE cu.effective_price_per_credit > 0
  AND u.plan != 'free'
  • Run the cell and rename the output dataframe to dataframe_3.

What does this query do?

It joins credit_usage with users to attach the user_segment column to each usage row. The WHERE clause excludes free-tier users (who have no price variation to model) and rows with zero effective price.

  • Add a new Python cell below. Paste this first block of code to set up the segmented regression:
import numpy as np
import pandas as pd
import statsmodels.api as sm
import matplotlib.pyplot as plt

# dataframe_3 comes from the "Usage Data with Segment Info" SQL cell above
df_seg = dataframe_3.copy()

# Log transformation
df_seg["log_credits_used"] = np.log(df_seg["credits_used"])
df_seg["log_price"] = np.log(df_seg["effective_price_per_credit"])

# Fit a separate model per segment
segments = df_seg["user_segment"].unique()
results = []

fig, axes = plt.subplots(2, 2, figsize=(12, 10))
axes_flat = axes.flatten()

What does this set up?

It applies the same log transformation as before, then prepares to loop through each unique segment. The 2x2 subplot grid will hold one scatter plot per segment so you can visually compare the price-usage relationship.

  • Directly below the code you just pasted (in the same cell), add the model-fitting loop:
for i, segment in enumerate(sorted(segments)):
    seg_data = df_seg[df_seg["user_segment"] == segment]

    X = sm.add_constant(seg_data["log_price"])
    y = seg_data["log_credits_used"]

    model = sm.OLS(y, X).fit()

    results.append(
        {
            "segment": segment,
            "elasticity": round(model.params["log_price"], 4),
            "r_squared": round(model.rsquared, 4),
            "p_value": round(model.pvalues["log_price"], 6),
            "n_observations": len(seg_data),
        }
    )

    # Plot
    ax = axes_flat[i]
    ax.scatter(seg_data["log_price"], y, alpha=0.3, s=10)
    x_line = np.linspace(seg_data["log_price"].min(), seg_data["log_price"].max(), 100)
    y_line = model.params["const"] + model.params["log_price"] * x_line
    ax.plot(x_line, y_line, color="red", linewidth=2)
    ax.set_title(f"{segment} (e={model.params['log_price']:.2f}, R2={model.rsquared:.2f})")
    ax.set_xlabel("log(effective price per credit)")
    ax.set_ylabel("log(credits used)")

What does this loop do?

For each segment (light, moderate, heavy, power), it fits a separate OLS regression on the log-transformed data. It stores the elasticity coefficient, R-squared, p-value, and sample size in a list. It also plots the raw scatter and the fitted regression line for visual inspection.

  • Still in the same cell, paste the final block that displays the plot and prints the summary table:
plt.tight_layout()
plt.show()

# Summary table
results_df = pd.DataFrame(results)
print("\n=== SEGMENTED ELASTICITY RESULTS ===")
print(results_df.to_string(index=False))
print("\nInterpretation: A 1% increase in effective price leads to")
print("approximately [elasticity]% decrease in credit usage for each segment.")

Before you run this cell, predict: will the segmented models show higher R-squared than the pooled model? How much higher?

  • Run the Python cell.

You should see a 2x2 grid of scatter plots with red regression lines, and a summary table printed below. The power segment shows an elasticity coefficient between -0.3 and -0.8 with R-squared above 0.4. Compare that to the pooled model's R-squared below 0.15.

Seeing a KeyError for log_price?

Make sure your SQL cell above ran successfully and that you renamed its output to dataframe_3. The Python cell references dataframe_3 directly. If the name does not match, double-click the dataframe pill below the SQL cell to rename it.

Help me fix my dataframe reference error.

✔️ Awesome, I've got everything!

Great. Make sure you have saved the notebook. Your Python cell should contain all three blocks above combined into one cell.

ⓧ I'd like to double check the full code

Your Python cell should contain the imports and data prep (part 1), immediately followed by the for loop (part 2), immediately followed by the plot display and summary (part 3). Scroll up and confirm each block is present in order within a single Python cell.

Interpret the elasticity estimates

The numbers are in. Now you need to translate them into a business insight your team can act on.

  • Add a Markdown cell below your segmented regression cell.
  • Write an interpretation of your findings. Here is an example:
## Elasticity Interpretation

Power users have elasticity of approximately -0.5, meaning a 10% effective price
increase causes only a 5% usage decrease. They are moderately inelastic, which
means we have room to adjust pricing without losing them entirely.

Light and moderate users are less price-sensitive in absolute terms but their
usage is already low. The actionable insight is on the power/heavy segments
where margin is negative AND elasticity allows intervention.

What does an elasticity of -0.5 mean in practice?

If you reduce the Pro credit allowance from 100 to 80 (effectively raising the per-credit price by 25%), power users would decrease their usage by roughly 12.5%. They still use a LOT of credits. They will hit their cap and buy top-ups rather than leave.

This is the key insight: power users are sticky enough to absorb a pricing adjustment. The next step will quantify exactly how much revenue that unlocks.

  • Run the Markdown cell to render it.

Your notebook now shows the full elasticity story: the pooled model failed, the segmented model succeeded, and you have a clear interpretation of what the coefficients mean for pricing decisions.

You have elasticity estimates for every segment. Next up, you will plug these into a revenue simulation to find the exact credit allowance that maximizes revenue.

Simulate Revenue and Make a Pricing Recommendation

You know which segments lose money, and you know how elastic each one is. The missing piece is a concrete recommendation: how should the credit allocation change to maximize revenue?

In this step, you will build a revenue simulation that uses your elasticity estimates to project how revenue shifts at different credit allowances. Then you will find the optimal point using scipy optimization and produce a pricing committee-ready recommendation.

In this step, get ready to:
  • Build a revenue projection function using your elasticity coefficients.
  • Find the revenue-maximizing credit allowance for the Pro tier.
  • Produce a final pricing recommendation with projected revenue impact.
Build a revenue simulation function

The simulation needs to answer one question: if you change the Pro credit allowance to X, what happens to total monthly revenue? Revenue comes from subscriptions plus top-up purchases, minus infrastructure cost.

  • In your Hex notebook, add a new Python cell below your elasticity interpretation markdown cell.
  • Paste this code to define the simulation parameters:
import numpy as np
from scipy.optimize import minimize_scalar
import matplotlib.pyplot as plt

# Use the elasticity results from the previous cell
# For Pro plan users, current state: price=$25, credits=100
CURRENT_PRICE = 25
CURRENT_CREDITS = 100
COST_PER_CREDIT = 0.12
FIXED_OVERHEAD = 2.0
TOPUP_PRICE_PER_CREDIT = 0.30  # Pro top-up rate from Lovable docs

# User counts by segment (from our data)
pro_segments = {
    "light": {"users": 210, "avg_usage": 30, "elasticity": -0.3},
    "moderate": {"users": 245, "avg_usage": 70, "elasticity": -0.4},
    "heavy": {"users": 140, "avg_usage": 120, "elasticity": -0.6},
    "power": {"users": 105, "avg_usage": 250, "elasticity": -0.5},
}

What do these parameters represent?

Each constant mirrors Lovable's actual pricing model. CURRENT_PRICE is the $25/month Pro subscription. TOPUP_PRICE_PER_CREDIT at $0.30 is what Pro users pay when they exceed their allowance.

The pro_segments dictionary captures the user counts, average usage, and elasticity coefficients you computed in the previous step. These are the inputs the simulation uses to predict behavior under different credit allowances.

  • Below the parameters in the same cell, add the revenue projection function:
def project_revenue(credit_allowance):
    """
    Project total monthly revenue for Pro tier at a given credit allowance.
    Revenue = subscription + top-up revenue - infrastructure cost
    """
    total_revenue = 0

    for seg_name, seg_info in pro_segments.items():
        n_users = seg_info["users"]
        base_usage = seg_info["avg_usage"]
        elasticity = seg_info["elasticity"]

        # How usage changes with the new allowance
        price_ratio = (CURRENT_PRICE / credit_allowance) / (
            CURRENT_PRICE / CURRENT_CREDITS
        )

        # Elasticity: usage_new = usage_old * (price_ratio ^ elasticity)
        projected_usage = base_usage * (price_ratio ** elasticity)

        # Revenue per user
        subscription_revenue = CURRENT_PRICE
        # Top-up revenue: if usage > allowance, user buys top-ups
        overage = max(0, projected_usage - credit_allowance)
        topup_revenue = overage * TOPUP_PRICE_PER_CREDIT
        # Infrastructure cost
        infra_cost = projected_usage * COST_PER_CREDIT + FIXED_OVERHEAD
        # Net revenue per user in this segment
        net_per_user = subscription_revenue + topup_revenue - infra_cost
        total_revenue += net_per_user * n_users

    return total_revenue

What does this function do?

For each user segment, the function projects what happens when you change the credit allowance. A lower allowance means a higher effective price per credit, which reduces usage (based on the elasticity coefficient). Users who exceed their new allowance buy top-ups at $0.30 per credit.

The function returns net revenue: subscription fees plus top-up revenue minus infrastructure cost. This is the number you want to maximize.

  • Test the function by adding this line at the very bottom of the same cell:
print(f"Revenue at 100 credits: ${project_revenue(100):,.0f}")
  • Run the cell.
  • You should see a revenue figure printed. This confirms the function executes correctly using your elasticity estimates.

Seeing a NameError or zero revenue?

  • If you see a NameError for pro_segments, make sure the parameters code and the function are in the same cell, with parameters above the function.
  • If revenue shows as $0, check that CURRENT_PRICE is set to 25 (not 0). Free-tier users are excluded from this simulation.
  • If you see an ImportError for scipy, make sure your first line reads: from scipy.optimize import minimize_scalar.

Help me debug my revenue simulation function in Hex.

Find the revenue-maximizing credit allowance

Your function takes any credit allowance as input and returns projected revenue. Now you need to find which input maximizes that output. This is a classic optimization problem.

  • Delete the test print line you added at the bottom of the cell.
  • Replace it with this optimization and plotting code:
# Find optimal credit allowance
result = minimize_scalar(
    lambda x: -project_revenue(x), bounds=(40, 150), method="bounded"
)
optimal_credits = result.x
optimal_revenue = project_revenue(optimal_credits)
current_revenue = project_revenue(CURRENT_CREDITS)
revenue_lift_pct = (optimal_revenue - current_revenue) / current_revenue * 100

print("=== REVENUE OPTIMIZATION RESULTS ===")
print(f"Current credit allowance: {CURRENT_CREDITS}")
print(f"Optimal credit allowance: {optimal_credits:.0f}")
print(f"Current monthly revenue (Pro tier): ${current_revenue:,.0f}")
print(f"Projected monthly revenue at optimal: ${optimal_revenue:,.0f}")
print(f"Revenue lift: +{revenue_lift_pct:.1f}%")

# Plot revenue curve
allowances = np.linspace(40, 150, 100)
revenues = [project_revenue(a) for a in allowances]

fig, ax = plt.subplots(figsize=(10, 6))
ax.plot(allowances, revenues, linewidth=2, color="#1976d2")
ax.axvline(x=CURRENT_CREDITS, color="gray", linestyle="--", label=f"Current ({CURRENT_CREDITS})")
ax.axvline(x=optimal_credits, color="#d32f2f", linestyle="--", label=f"Optimal ({optimal_credits:.0f})")
ax.scatter([optimal_credits], [optimal_revenue], color="#d32f2f", s=100, zorder=5)
ax.set_xlabel("Pro Credit Allowance (credits/month)")
ax.set_ylabel("Projected Monthly Revenue ($)")
ax.set_title("Revenue Curve: Finding the Optimal Credit Allowance for Pro Tier")
ax.legend()
ax.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()

What does this code do?

  • minimize_scalar finds the credit allowance value (between 40 and 150) that maximizes revenue. It negates the function because scipy minimizes by default.
  • The print block shows you the current vs. optimal allowance and the projected revenue lift as a percentage.
  • The plot visualizes the full revenue curve so you can see the shape of the optimization landscape and where the peak sits relative to the current allowance.

Before you run this, what do you expect the optimal credit allowance to be? Higher than 100, lower, or the same? Think about what happens to top-up revenue when you reduce the allowance for inelastic users.

  • Run the cell.

You should see an optimal credit allowance between 70-90 credits (down from 100) with a projected monthly revenue increase of 8-12%. The revenue curve peaks below the current allowance, confirming that a reduction increases revenue because inelastic power users purchase top-ups rather than leaving.

Revenue lift showing 0% or a negative number?

  • Check that your elasticity values in pro_segments are negative (e.g., -0.5). Positive elasticity would mean users increase usage when price rises, which breaks the model.
  • Verify that TOPUP_PRICE_PER_CREDIT is 0.30. This is the revenue source that makes a lower allowance profitable.
  • If the plot appears but the numbers look wrong, double-check that the bounds=(40, 150) argument is present in your minimize_scalar call.

Help me fix my revenue optimization output.

Produce the final recommendation

Numbers alone do not ship pricing changes. You need to frame the recommendation as you would present it to a pricing committee: what to change, what you expect to happen, and why you are confident.

  • Add a new Markdown cell below your revenue optimization Python cell.
  • Write your pricing recommendation using this structure:
## Pricing Recommendation

**Recommendation:** Reduce Pro credit allowance from 100 to ~[your optimal] credits/month.

**Expected impact:**
- Monthly revenue lift: +[your percentage]%
- Mechanism: Power and heavy users are moderately inelastic (elasticity -0.5 to -0.6) and will purchase top-ups at $0.30/credit rather than downgrade
- Churn risk: Low for power/heavy segments (inelastic); negligible for light/moderate (unaffected)

**Comparison:**
| Metric | Current (100 credits) | Recommended (~[optimal] credits) |
|--------|----------------------|----------------------------------|
| Monthly revenue | $[current] | $[projected] |
| Power user margin | Negative | Near breakeven |
| Light user impact | None | None |

**Framing:** Reducing Pro credit allowance from 100 to [X] credits is projected to increase monthly revenue by [Y]% while keeping churn below 2%, because power users are moderately inelastic and will purchase top-ups rather than downgrade.
  • Replace the bracketed placeholders with the actual numbers from your optimization output.
  • Run the markdown cell to render it.
  • You should see a formatted recommendation document with a clear comparison table and a one-paragraph pricing committee framing.

Why frame it for a pricing committee?

In a real pricing data scientist role, your analysis only creates value when it drives a decision. A pricing committee needs three things: what to change, why you believe it works (the elasticity evidence), and what could go wrong (churn risk). This framing demonstrates that you can translate models into concrete pricing bets.

Markdown not rendering?

  • In Hex, make sure the cell type is set to Markdown (not Python). Click the cell type dropdown at the top-left of the cell to switch.
  • If the table looks broken, ensure each row has the same number of pipe characters. Every row needs three columns separated by |.

Help me format my markdown recommendation in Hex.

✔️ Awesome, I've got everything!

Great. Double check you have saved both cells in your Hex notebook: the Python cell with the revenue optimization and the Markdown cell with your pricing recommendation.

ⓧ I'd like to double check the full code

Your Revenue Optimization Python cell should contain these sections in order. Scroll up and confirm each is present:

  • Imports: numpy, scipy.optimize.minimize_scalar, matplotlib.pyplot.
  • Constants: CURRENT_PRICE = 25, CURRENT_CREDITS = 100, COST_PER_CREDIT = 0.12, FIXED_OVERHEAD = 2.0, TOPUP_PRICE_PER_CREDIT = 0.30.
  • pro_segments dictionary with light, moderate, heavy, and power entries each having users, avg_usage, and elasticity.
  • project_revenue(credit_allowance) function definition with the for-loop over segments.
  • minimize_scalar call with bounds=(40, 150) and method="bounded".
  • Print statements showing current vs. optimal revenue.
  • Revenue curve plot with current and optimal allowance marked.

You now have a data-backed pricing recommendation built on real unit economics and measured elasticity. Your notebook is the kind of analysis you would present in a pricing data scientist interview. Next up, the Secret Mission challenges you to validate this recommendation with an A/B test design.

Secret mission

Design a Pricing A/B Test

Your pricing recommendation is based on observational data. Before shipping it to all users, you need a rigorous experiment design that proves the change works. Build a complete A/B test framework with sample size calculations, simulated results, and a ship/no-ship decision matrix.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on free tiers, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • BigQuery dataset pricing_analytics with 4 tables (free tier, no charges).
  • Hex Community notebook "Pricing Analytics" (free tier, no charges).
  • GCP service account JSON key file (stored locally).
  • Local files: generate_data.py and CSV files (users.csv, subscriptions.csv, credit_usage.csv, costs.csv).

Keep everything running

No action needed. Choose this if you plan to revisit or extend your pricing analysis.

  • BigQuery Sandbox is free indefinitely. Tables expire after 60 days of no modification, but this is a learning project so that is fine.
  • Hex Community notebooks persist on the free plan with no time limit.
  • Your local files and service account key file remain on your machine until you delete them manually.

Pause - I'll come back to this later

Free up local disk space while keeping your cloud resources intact for later.

  • Delete the local CSV files and generate_data.py if you no longer need them. You can regenerate the data at any time by re-running the script.
  • Leave the BigQuery dataset and Hex notebook in place. Both are on free tiers with no charges.

Delete - I don't want to use this again

Remove all project resources and start fresh.

  • Delete the BigQuery dataset: open the Google Cloud Console, go to the BigQuery page, expand your project in the Explorer pane, click the pricing_analytics dataset, click Delete, type delete to confirm, then click Delete. This removes all four tables automatically.
  • Delete the service account key: in the Google Cloud Console, go to IAM & Admin, then Service accounts. Click your service account, open the Keys tab, and click Delete next to the key you created.
  • Delete the Hex notebook: in Hex, open your "Pricing Analytics" notebook, click the menu in the top-right corner, and select Delete.
  • Delete your local project files by running this command:
rm -f users.csv subscriptions.csv credit_usage.csv costs.csv generate_data.py
  • Delete the service account JSON key file from wherever you saved it (check your Downloads folder).

Nice Work!

Nice Work!

You just built a production-style pricing analytics system using the exact tools a pricing data scientist at Lovable uses daily. From raw data to a revenue-maximizing recommendation, your Hex notebook tells a complete analytical story.

You've learned how to:

  • Compute unit economics (LTV, cost-to-serve, margin) by user segment and identify which users are margin-negative using BigQuery SQL and Hex.
  • Estimate price elasticity per segment using log-log OLS regression, discovering that a pooled model fails but segmented models produce interpretable coefficients.
  • Produce a concrete, data-backed pricing recommendation with projected revenue impact using scipy optimization and a revenue simulation function.
  • Secret Mission: Designed a complete A/B test with power analysis, sample size calculation, metric definitions, and a ship/no-ship decision framework.

Ready to quiz yourself?