Ad Spend Allocation

Version:

Experience level: Intermediate
Reasoning types: Prescriptive
Industry: Retail & Consumer
Tags: AllocationMILP
Experience level: Intermediate
Reasoning types: Prescriptive
Industry: Retail & Consumer
Tags: AllocationMILP
Experience level: Intermediate
Reasoning types: Prescriptive
Industry: Retail & Consumer
Tags: Budget AllocationMixed-Integer Programming (MIP)MarketingWhat-If AnalysisScenario Analysis

What this template is for

Marketing teams must distribute limited advertising budgets across multiple channels (Search, Social, Display, Video, Email) and campaigns to maximize return on investment. This template models allocating spend across 5 channels and 3 campaigns, where each channel-campaign combination has different conversion effectiveness.

The challenge is that each channel has minimum spend thresholds (you can’t spend just $10 on a channel—there are setup costs) and maximum caps, while campaign budgets constrain total spend. This template uses RelationalAI’s prescriptive reasoner to find the best allocation of spend across channel-campaign pairs to maximize total expected conversions, while respecting these constraints.

Prescriptive reasoning helps you:

Improve ROAS: Achieve higher Return on Ad Spend compared to manual allocation or simple rules
Budget efficiency: Eliminate waste from over-allocating to saturated channels while identifying under-invested opportunities
Make data-driven decisions: Replace gut-feel allocation with mathematically optimal distribution based on measured conversion rates

Who this is for

You want an end-to-end example of prescriptive reasoning (optimization) with RelationalAI.
You’re comfortable with basic Python and linear/mixed-integer optimization concepts.

What you’ll build

A semantic model of channels and campaigns using concepts + properties.
A MILP allocation model with continuous spend variables and binary “active” flags per channel–campaign pair.
A set of constraints for min/max spend, per-campaign budgets, and minimum coverage (at least one channel per campaign).
A solver that uses the HiGHS backend and prints a readable allocation table.

What’s included

Model + solve script: ad_spend_allocation.py
Sample data: data/channels.csv, data/campaigns.csv, data/effectiveness.csv

Prerequisites

Access

A Snowflake account that has the RAI Native App installed.
A Snowflake user with permissions to access the RAI Native App.

Tools

Python >= 3.10

Quickstart

Follow these steps to run the template with the included sample data. You can customize the data and model as needed after you have it running end-to-end.

Download the ZIP file for this template and extract it:
Terminal window
```
curl -O https://private.relational.ai/templates/zips/v0.13/ad_spend_allocation.zip
unzip ad_spend_allocation.zip
cd ad_spend_allocation
```
You can also download the template ZIP using the “Download ZIP” button at the top of this page.

Create and activate a virtual environment

python -m venv .venv
source .venv/bin/activate
python -m pip install -U pip

Install dependencies

From this folder:
Terminal window
```
python -m pip install .
```
Configure Snowflake connection and RAI profile
Terminal window
```
rai init
```
Run the template
Terminal window
```
python ad_spend_allocation.py
```

Expected output

The script solves three budget scenarios. The allocation table for each channel-campaign pair is printed for each scenario, along with the total expected conversions (objective value). At the end, a summary table compares the objective across scenarios to show the impact of the total budget constraint:

Running scenario: total_budget = 35000
  Status: OPTIMAL, Objective: 2880.0

  Spend allocation:
                        name   value
 spend_Email_Brand_Awareness  2000.0
   spend_Email_Seasonal_Sale  2000.0
 spend_Search_Product_Launch 10000.0
  spend_Search_Seasonal_Sale  8000.0
spend_Social_Brand_Awareness  3000.0
  spend_Video_Product_Launch 10000.0

Running scenario: total_budget = 45000
  Status: OPTIMAL, Objective: 3430.0

  Spend allocation:
                        name   value
 spend_Email_Brand_Awareness  2000.0
   spend_Email_Seasonal_Sale  2000.0
spend_Search_Brand_Awareness  5000.0
 spend_Search_Product_Launch 10000.0
  spend_Search_Seasonal_Sale  8000.0
spend_Social_Brand_Awareness  8000.0
  spend_Video_Product_Launch 10000.0

Running scenario: total_budget = 55000
  Status: OPTIMAL, Objective: 3430.0

  Spend allocation:
                        name   value
 spend_Email_Brand_Awareness  2000.0
   spend_Email_Seasonal_Sale  2000.0
spend_Search_Brand_Awareness  5000.0
 spend_Search_Product_Launch 10000.0
  spend_Search_Seasonal_Sale  8000.0
spend_Social_Brand_Awareness  8000.0
  spend_Video_Product_Launch 10000.0

==================================================
Scenario Analysis Summary
==================================================
  35000: OPTIMAL, obj=2880.0
  45000: OPTIMAL, obj=3430.0
  55000: OPTIMAL, obj=3430.0

Template structure

.
├─ README.md
├─ pyproject.toml
├─ ad_spend_allocation.py      # main runner / entrypoint
└─ data/                       # sample input data
   ├─ channels.csv
   ├─ campaigns.csv
   └─ effectiveness.csv

Start here: ad_spend_allocation.py

Sample data

Data files are in data/.

`channels.csv`

Column	Meaning
`id`	Unique channel identifier
`name`	Channel name (e.g., Search, Social)
`min_spend`	Minimum spend if the channel is active
`max_spend`	Maximum spend allowed
`roi_coefficient`	Additional channel attribute (not used in the objective in this template)

`campaigns.csv`

Column	Meaning
`id`	Unique campaign identifier
`name`	Campaign name
`budget`	Total spend allowed for the campaign
`target_conversions`	Campaign attribute (not used as a constraint in this template)

`effectiveness.csv`

Column	Meaning
`channel_id`	Foreign key to `channels.csv.id`
`campaign_id`	Foreign key to `campaigns.csv.id`
`conversion_rate`	Expected conversions per $ spent

Model overview

The semantic model for this template is built around four concepts.

`Channel`

A marketing channel (e.g., Search, Social) with spend bounds used to constrain the optimization.

Property	Type	Identifying?	Notes
`id`	int	Yes	Loaded as the key from `data/channels.csv`
`name`	string	No	Human-readable channel name
`min_spend`	float	No	Minimum spend if the channel is active
`max_spend`	float	No	Maximum spend allowed (per allocation)
`roi_coefficient`	float	No	Included in sample data; not used in the objective in this template

`Campaign`

A marketing campaign with a budget constraint applied during optimization.

Property	Type	Identifying?	Notes
`id`	int	Yes	Loaded as the key from `data/campaigns.csv`
`name`	string	No	Human-readable campaign name
`budget`	float	No	Upper bound on total spend across all channels for the campaign
`target_conversions`	float	No	Included in sample data; not enforced as a constraint in this template

`Effectiveness`

A channel–campaign pair with an expected conversion rate used in the objective.

Property	Type	Identifying?	Notes
`channel`	`Channel`	Part of compound key	Joined via `data/effectiveness.csv.channel_id`
`campaign`	`Campaign`	Part of compound key	Joined via `data/effectiveness.csv.campaign_id`
`conversion_rate`	float	No	Expected conversions per $ spent

`Allocation`

A decision concept created for each Effectiveness row; the solver chooses spend and active.

Property	Type	Identifying?	Notes
`effectiveness`	`Effectiveness`	Yes	One allocation per channel–campaign pair
`spend`	float	No	Continuous decision variable ()
`active`	float	No	Binary decision variable (0/1)

How it works

This section walks through the highlights in ad_spend_allocation.py.

Import libraries and configure inputs

First, the script imports the Semantics APIs (Model, data, where, require, sum) and configures local inputs like DATA_DIR:

from pathlib import Path

import pandas
from pandas import read_csv

from relationalai.semantics import Model, data, require, select, sum, where
from relationalai.semantics.reasoners.optimization import Solver, SolverModel

# --------------------------------------------------
# Configure inputs
# --------------------------------------------------

DATA_DIR = Path(__file__).parent / "data"

# Disable pandas inference of string types. This ensures that string columns
# in the CSVs are loaded as object dtype. This is only required when using
# relationalai versions prior to v1.0.
pandas.options.future.infer_string = False

# --------------------------------------------------
# Define the semantic model & load data
# --------------------------------------------------

# Create a Semantics model container.
model = Model("ad_spend", config=globals().get("config", None), use_lqp=False)

Define concepts and load CSV data

Next, it defines the Channel and Campaign concepts and loads channels.csv and campaigns.csv via data(...).into(...):

# Channel concept: marketing channel with spend bounds (and an extra ROI field
# kept to show how additional attributes can live alongside the optimization inputs).
Channel = model.Concept("Channel")
Channel.id = model.Property("{Channel} has {id:int}")
Channel.name = model.Property("{Channel} has {name:string}")
Channel.min_spend = model.Property("{Channel} has {min_spend:float}")
Channel.max_spend = model.Property("{Channel} has {max_spend:float}")
Channel.roi_coefficient = model.Property("{Channel} has {roi_coefficient:float}")

# Load channel data from CSV.
channel_csv = read_csv(DATA_DIR / "channels.csv")
data(channel_csv).into(Channel, keys=["id"])

# Campaign concept: each campaign has a total budget across all channels.
# target_conversions is loaded as an example attribute; it is not used as a
# constraint in this template.
Campaign = model.Concept("Campaign")
Campaign.id = model.Property("{Campaign} has {id:int}")
Campaign.name = model.Property("{Campaign} has {name:string}")
Campaign.budget = model.Property("{Campaign} has {budget:float}")
Campaign.target_conversions = model.Property("{Campaign} has {target_conversions:int}")

# Load campaign data from CSV.
campaign_csv = read_csv(DATA_DIR / "campaigns.csv")
data(campaign_csv).into(Campaign, keys=["id"])

effectiveness.csv contains foreign keys (channel_id, campaign_id). The template resolves them into Channel and Campaign instances and creates an Effectiveness concept per row.

# Effectiveness concept: models the conversion rate for each channel-campaign pair.
# This is the key input that links channels and campaigns and allows us to model
# the optimization problem. In a real-world scenario, this could be derived from
# historical data or A/B tests rather than loaded from a CSV.
Effectiveness = model.Concept("Effectiveness")
Effectiveness.channel = model.Property("{Effectiveness} via {channel:Channel}")
Effectiveness.campaign = model.Property("{Effectiveness} for {campaign:Campaign}")
Effectiveness.conversion_rate = model.Property("{Effectiveness} has {conversion_rate:float}")

# Load channel-campaign effectiveness data from CSV.
eff_data = data(read_csv(DATA_DIR / "effectiveness.csv"))

# Define Effectiveness entities by joining the CSV data with the Channel and
# Campaign concepts.
where(
    Channel.id == eff_data.channel_id,
    Campaign.id == eff_data.campaign_id
).define(
    Effectiveness.new(channel=Channel, campaign=Campaign, conversion_rate=eff_data.conversion_rate)
)

Define decision variables, constraints, and objective

An Allocation decision concept is created for every Effectiveness row. The script defines a build_formulation helper that registers decision variables, constraints, and the objective on a SolverModel. It then loops over budget scenarios and solves a fresh model for each.

# Allocation concept: represents the decision variables for how much to spend on each
# channel-campaign pair. Each Allocation is linked to an Effectiveness entity, which
# provides the conversion rate for that channel-campaign pair. The `spend` and
# `active` properties represent the decision variables that the solver will determine.
Allocation = model.Concept("Allocation")
Allocation.effectiveness = model.Property("{Allocation} uses {effectiveness:Effectiveness}")
Allocation.x_spend = model.Property("{Allocation} has {spend:float}")
Allocation.x_active = model.Property("{Allocation} is {active:float}")

# Define Allocation entities.
model.define(Allocation.new(effectiveness=Effectiveness))

# Scenario parameter.
total_budget = 45000


def build_formulation(s):
    """Register variables, constraints, and objective on the solver model."""
    # Variable: spend (continuous, >= 0)
    s.solve_for(
        Allocation.x_spend,
        name=[
            "spend",
            Allocation.effectiveness.channel.name,
            Allocation.effectiveness.campaign.name,
        ],
        lower=0,
    )

    # Variable: active (binary 0/1)
    s.solve_for(
        Allocation.x_active,
        type="bin",
        name=[
            "active",
            Allocation.effectiveness.channel.name,
            Allocation.effectiveness.campaign.name,
        ],
    )

    # Constraint: minimum spend per channel when active
    min_spend_bound = require(
        Allocation.x_spend >= Allocation.effectiveness.channel.min_spend * Allocation.x_active
    )
    s.satisfy(min_spend_bound)

    # Constraint: maximum spend per channel when active
    max_spend_bound = require(
        Allocation.x_spend <= Allocation.effectiveness.channel.max_spend * Allocation.x_active
    )
    s.satisfy(max_spend_bound)

    # Constraint: per-campaign budget across all channels
    campaign_spend = (
        sum(Allocation.x_spend)
        .where(Allocation.effectiveness.campaign == Campaign)
        .per(Campaign)
    )
    budget_limit = require(campaign_spend <= Campaign.budget)
    s.satisfy(budget_limit)

    # Constraint: require at least one active channel per campaign
    campaign_channels = (
        sum(Allocation.x_active)
        .where(Allocation.effectiveness.campaign == Campaign)
        .per(Campaign)
    )
    min_channels = require(campaign_channels >= 1)
    s.satisfy(min_channels)

    # Constraint: total budget across all campaigns (scenario parameter)
    total_budget_limit = require(sum(Allocation.x_spend) <= total_budget)
    s.satisfy(total_budget_limit)

    # Objective: maximize total expected conversions
    total_conversions = sum(Allocation.x_spend * Allocation.effectiveness.conversion_rate)
    s.maximize(total_conversions)

With the formulation defined, each scenario iteration sets total_budget, builds a fresh SolverModel, and solves.

scenario_results = []

SCENARIO_PARAM = "total_budget"
SCENARIO_VALUES = [35000, 45000, 55000]

for scenario_value in SCENARIO_VALUES:
    print(f"\nRunning scenario: {SCENARIO_PARAM} = {scenario_value}")

    # Set scenario parameter value
    total_budget = scenario_value

    # Create a fresh SolverModel for each scenario.
    solver_model = SolverModel(model, "cont")
    build_formulation(solver_model)

    # Solve the model with a time limit of 60 seconds. The `Solver` class provides
    # an interface to various optimization solvers. Here we use the open-source
    # HiGHS solver, which is suitable for linear and mixed-integer problems.
    solver = Solver("highs")
    solver_model.solve(solver, time_limit_sec=60)

    scenario_results.append({
      "scenario": scenario_value,
      "status": str(solver_model.termination_status),
      "objective": solver_model.objective_value,
    })
    print(
      f"  Status: {solver_model.termination_status}, "
      f"Objective: {solver_model.objective_value}"
    )

    # Print spend allocation from solver results
    var_df = solver_model.variable_values().to_df()
    spend_df = var_df[
      var_df["name"].str.startswith("spend") & (var_df["float"] > 0.001)
    ].rename(columns={"float": "value"})
    print(f"\n  Spend allocation:")
    print(spend_df.to_string(index=False))

# Summary
print("\n" + "=" * 50)
print("Scenario Analysis Summary")
print("=" * 50)
for result in scenario_results:
    print(f"  {result['scenario']}: {result['status']}, obj={result['objective']}")

Solve and print results

For each scenario, the model is solved with a time limit of 60 seconds using the HiGHS solver. The script prints the solver status, objective value, and a table of spend allocations for channel-campaign pairs with non-trivial spend (greater than $0.001):

    # Solve the model with a time limit of 60 seconds. The `Solver` class provides
    # an interface to various optimization solvers. Here we use the open-source
    # HiGHS solver, which is suitable for linear and mixed-integer problems.
    solver = Solver("highs")
    solver_model.solve(solver, time_limit_sec=60)

    scenario_results.append({
      "scenario": scenario_value,
      "status": str(solver_model.termination_status),
      "objective": solver_model.objective_value,
    })
    print(
      f"  Status: {solver_model.termination_status}, "
      f"Objective: {solver_model.objective_value}"
    )

    # Print spend allocation from solver results
    var_df = solver_model.variable_values().to_df()
    spend_df = var_df[
      var_df["name"].str.startswith("spend") & (var_df["float"] > 0.001)
    ].rename(columns={"float": "value"})
    print(f"\n  Spend allocation:")
    print(spend_df.to_string(index=False))

After all scenarios run, the script prints a small summary:

# Summary
print("\n" + "=" * 50)
print("Scenario Analysis Summary")
print("=" * 50)
for result in scenario_results:
    print(f"  {result['scenario']}: {result['status']}, obj={result['objective']}")

Customize this template

Here are some ideas for how to customize and extend this template to fit your specific use case.

Change the scenario parameters

This template includes a simple what-if analysis that reruns the same optimization under different company-wide budget caps.

Parameter	Type	Values	Description
`total_budget`	numeric	`35000`, `45000`, `55000`	Total spend cap across all campaigns

How to customize the scenarios:

In ad_spend_allocation.py, edit SCENARIO_VALUES to the budgets you want to test.

How to interpret results:

If increasing total_budget doesn’t change the objective, the total budget cap is non-binding (other constraints are limiting).
If reducing total_budget decreases the objective, the cap is binding and forces spend away from higher-converting allocations.

Use your own data

Replace the CSVs in data/ with your own, keeping the same column names (or update the loading logic in ad_spend_allocation.py).
Ensure that effectiveness.csv only references valid channel_id and campaign_id values.

Extend the model

Add per-channel total spend limits across all campaigns.
Add campaign conversion targets as constraints (using Campaign.target_conversions).
Add diminishing returns (piecewise linear approximations) if conversion rate decreases with spend.

Scale up and productionize

Replace CSV ingestion with Snowflake sources.
Write allocations back to Snowflake after solving.

Troubleshooting

Why does authentication/configuration fail?

Run rai init to create/update raiconfig.toml.
If you have multiple profiles, set RAI_PROFILE or switch profiles in your config.

Why does the script fail to connect to the RAI Native App?

Verify the Snowflake account/role/warehouse and rai_app_name are correct in raiconfig.toml.
Ensure the RAI Native App is installed and you have access.

Why do I get Status: INFEASIBLE?

Check that each campaign budget is high enough to pay for at least one active channel’s min_spend.
Check that channel min_spend values are not greater than max_spend.
Confirm conversion rates are present for each campaign (missing effectiveness rows reduce options).

Why is the spend allocation empty?

The script filters allocations with Allocation.x_spend > 0.001. If everything is near zero, inspect constraints and budgets.
Confirm input CSVs were read correctly and contain rows.

What this template is for

The challenge is that each channel has minimum spend thresholds (you can’t spend just $10 on a channel—there are setup costs) and maximum caps, while campaign budgets constrain total spend. This template uses RelationalAI’s Prescriptive reasoning capabilities to find the best allocation of spend across channel-campaign pairs to maximize total expected conversions, while respecting these constraints.

Prescriptive reasoning helps you:

Improve ROAS: Achieve higher Return on Ad Spend compared to manual allocation or simple rules
Budget efficiency: Eliminate waste from over-allocating to saturated channels while identifying under-invested opportunities
Make data-driven decisions: Replace gut-feel allocation with mathematically optimal distribution based on measured conversion rates

Who this is for

You want an end-to-end example of prescriptive reasoning (optimization) with RelationalAI.
You’re comfortable with basic Python and linear/mixed-integer optimization concepts.

What you’ll build

A semantic model of channels and campaigns using concepts + properties.
A MILP allocation model with continuous spend variables and binary “active” flags per channel–campaign pair.
A set of constraints for min/max spend, per-campaign budgets, and minimum coverage (at least one channel per campaign).
A solver that uses the HiGHS backend and prints a readable allocation table.

What’s included

Model + solve script: ad_spend_allocation.py
Sample data: data/channels.csv, data/campaigns.csv, data/effectiveness.csv

Prerequisites

Access

A Snowflake account that has the RAI Native App installed.
A Snowflake user with permissions to access the RAI Native App.

Tools

Python >= 3.10

Quickstart

Follow these steps to run the template with the included sample data. You can customize the data and model as needed after you have it running end-to-end.

Download the ZIP file for this template and extract it:
Terminal window
```
curl -O https://private.relational.ai/templates/zips/v0.14/ad_spend_allocation.zip
unzip ad_spend_allocation.zip
cd ad_spend_allocation
```
You can also download the template ZIP using the “Download ZIP” button at the top of this page.

Create and activate a virtual environment

python -m venv .venv
source .venv/bin/activate
python -m pip install -U pip

Install dependencies

From this folder:
Terminal window
```
python -m pip install .
```
Configure Snowflake connection and RAI profile
Terminal window
```
rai init
```
Run the template
Terminal window
```
python ad_spend_allocation.py
```

Expected output

Running scenario: total_budget = 35000
  Status: OPTIMAL, Objective: 2880.0

  Spend allocation:
                        name   value
 spend_Email_Brand_Awareness  2000.0
   spend_Email_Seasonal_Sale  2000.0
 spend_Search_Product_Launch 10000.0
  spend_Search_Seasonal_Sale  8000.0
spend_Social_Brand_Awareness  3000.0
  spend_Video_Product_Launch 10000.0

Running scenario: total_budget = 45000
  Status: OPTIMAL, Objective: 3430.0

  Spend allocation:
                        name   value
 spend_Email_Brand_Awareness  2000.0
   spend_Email_Seasonal_Sale  2000.0
spend_Search_Brand_Awareness  5000.0
 spend_Search_Product_Launch 10000.0
  spend_Search_Seasonal_Sale  8000.0
spend_Social_Brand_Awareness  8000.0
  spend_Video_Product_Launch 10000.0

Running scenario: total_budget = 55000
  Status: OPTIMAL, Objective: 3430.0

  Spend allocation:
                        name   value
 spend_Email_Brand_Awareness  2000.0
   spend_Email_Seasonal_Sale  2000.0
spend_Search_Brand_Awareness  5000.0
 spend_Search_Product_Launch 10000.0
  spend_Search_Seasonal_Sale  8000.0
spend_Social_Brand_Awareness  8000.0
  spend_Video_Product_Launch 10000.0

==================================================
Scenario Analysis Summary
==================================================
  35000: OPTIMAL, obj=2880.0
  45000: OPTIMAL, obj=3430.0
  55000: OPTIMAL, obj=3430.0

Template structure

.
├─ README.md
├─ pyproject.toml
├─ ad_spend_allocation.py      # main runner / entrypoint
└─ data/                       # sample input data
   ├─ channels.csv
   ├─ campaigns.csv
   └─ effectiveness.csv

Start here: ad_spend_allocation.py

Sample data

Data files are in data/.

`channels.csv`

Column	Meaning
`id`	Unique channel identifier
`name`	Channel name (e.g., Search, Social)
`min_spend`	Minimum spend if the channel is active
`max_spend`	Maximum spend allowed
`roi_coefficient`	Additional channel attribute (not used in the objective in this template)

`campaigns.csv`

Column	Meaning
`id`	Unique campaign identifier
`name`	Campaign name
`budget`	Total spend allowed for the campaign
`target_conversions`	Campaign attribute (not used as a constraint in this template)

`effectiveness.csv`

Column	Meaning
`channel_id`	Foreign key to `channels.csv.id`
`campaign_id`	Foreign key to `campaigns.csv.id`
`conversion_rate`	Expected conversions per $ spent

Model overview

The semantic model for this template is built around four concepts.

`Channel`

A marketing channel (e.g., Search, Social) with spend bounds used to constrain the optimization.

Property	Type	Identifying?	Notes
`id`	int	Yes	Loaded as the key from `data/channels.csv`
`name`	string	No	Human-readable channel name
`min_spend`	float	No	Minimum spend if the channel is active
`max_spend`	float	No	Maximum spend allowed (per allocation)
`roi_coefficient`	float	No	Included in sample data; not used in the objective in this template

`Campaign`

A marketing campaign with a budget constraint applied during optimization.

Property	Type	Identifying?	Notes
`id`	int	Yes	Loaded as the key from `data/campaigns.csv`
`name`	string	No	Human-readable campaign name
`budget`	float	No	Upper bound on total spend across all channels for the campaign
`target_conversions`	int	No	Included in sample data; not enforced as a constraint in this template

`Effectiveness`

A channel–campaign pair with an expected conversion rate used in the objective.

Property	Type	Identifying?	Notes
`channel`	`Channel`	Part of compound key	Relationship joined via `data/effectiveness.csv.channel_id`
`campaign`	`Campaign`	Part of compound key	Relationship joined via `data/effectiveness.csv.campaign_id`
`conversion_rate`	float	No	Expected conversions per $ spent

`Allocation`

A decision concept created for each Effectiveness row; the solver chooses spend and active.

Property	Type	Identifying?	Notes
`effectiveness`	`Effectiveness`	Yes	Relationship; one allocation per channel–campaign pair
`spend`	float	No	Continuous decision variable ()
`active`	float	No	Binary decision variable (0/1)

How it works

This section walks through the highlights in ad_spend_allocation.py.

Import libraries and configure inputs

First, the script imports the Semantics APIs (Model, data, where, require, sum) and configures local inputs like DATA_DIR:

from pathlib import Path

import pandas
from pandas import read_csv

from relationalai.semantics import Model, Relationship, data, require, select, sum, where
from relationalai.semantics.reasoners.optimization import Solver, SolverModel

# --------------------------------------------------
# Configure inputs
# --------------------------------------------------

DATA_DIR = Path(__file__).parent / "data"

# Disable pandas inference of string types. This ensures that string columns
# in the CSVs are loaded as object dtype. This is only required when using
# relationalai versions prior to v1.0.
pandas.options.future.infer_string = False

# --------------------------------------------------
# Define the semantic model & load data
# --------------------------------------------------

# Create a Semantics model container.
model = Model("ad_spend", config=globals().get("config", None))

Define concepts and load CSV data

Next, it defines the Channel and Campaign concepts and loads channels.csv and campaigns.csv via data(...).into(...):

# Channel concept: marketing channel with spend bounds (and an extra ROI field
# kept to show how additional attributes can live alongside the optimization inputs).
Channel = model.Concept("Channel")
Channel.id = model.Property("{Channel} has {id:int}")
Channel.name = model.Property("{Channel} has {name:string}")
Channel.min_spend = model.Property("{Channel} has {min_spend:float}")
Channel.max_spend = model.Property("{Channel} has {max_spend:float}")
Channel.roi_coefficient = model.Property("{Channel} has {roi_coefficient:float}")

# Load channel data from CSV.
channel_csv = read_csv(DATA_DIR / "channels.csv")
data(channel_csv).into(Channel, keys=["id"])

# Campaign concept: each campaign has a total budget across all channels.
# target_conversions is loaded as an example attribute; it is not used as a
# constraint in this template.
Campaign = model.Concept("Campaign")
Campaign.id = model.Property("{Campaign} has {id:int}")
Campaign.name = model.Property("{Campaign} has {name:string}")
Campaign.budget = model.Property("{Campaign} has {budget:float}")
Campaign.target_conversions = model.Property("{Campaign} has {target_conversions:int}")

# Load campaign data from CSV.
campaign_csv = read_csv(DATA_DIR / "campaigns.csv")
data(campaign_csv).into(Campaign, keys=["id"])

effectiveness.csv contains foreign keys (channel_id, campaign_id). The template resolves them into Channel and Campaign instances and creates an Effectiveness concept per row.

# Effectiveness concept: models the conversion rate for each channel-campaign pair.
# This is the key input that links channels and campaigns and allows us to model
# the optimization problem. In a real-world scenario, this could be derived from
# historical data or A/B tests rather than loaded from a CSV.
Effectiveness = model.Concept("Effectiveness")
Effectiveness.channel = model.Relationship("{Effectiveness} via {channel:Channel}")
Effectiveness.campaign = model.Relationship("{Effectiveness} for {campaign:Campaign}")
Effectiveness.conversion_rate = model.Property("{Effectiveness} has {conversion_rate:float}")

# Load channel-campaign effectiveness data from CSV.
eff_data = data(read_csv(DATA_DIR / "effectiveness.csv"))

# Define Effectiveness entities by joining the CSV data with the Channel and
# Campaign concepts.
where(
    Channel.id == eff_data.channel_id,
    Campaign.id == eff_data.campaign_id
).define(
    Effectiveness.new(channel=Channel, campaign=Campaign, conversion_rate=eff_data.conversion_rate)
)

Define decision variables, constraints, and objective

# Allocation concept: represents the decision variables for how much to spend on each
# channel-campaign pair. Each Allocation is linked to an Effectiveness entity, which
# provides the conversion rate for that channel-campaign pair. The `spend` and
# `active` properties represent the decision variables that the solver will determine.
Allocation = model.Concept("Allocation")
Allocation.effectiveness = model.Relationship("{Allocation} uses {effectiveness:Effectiveness}")
Allocation.x_spend = model.Property("{Allocation} has {spend:float}")
Allocation.x_active = model.Property("{Allocation} is {active:float}")

# Define Allocation entities.
model.define(Allocation.new(effectiveness=Effectiveness))

# Scenario parameter.
total_budget = 45000


def build_formulation(s):
    """Register variables, constraints, and objective on the solver model."""
    # Variable: spend (continuous, >= 0)
    s.solve_for(
        Allocation.x_spend,
        name=[
            "spend",
            Allocation.effectiveness.channel.name,
            Allocation.effectiveness.campaign.name,
        ],
        lower=0,
    )

    # Variable: active (binary 0/1)
    s.solve_for(
        Allocation.x_active,
        type="bin",
        name=[
            "active",
            Allocation.effectiveness.channel.name,
            Allocation.effectiveness.campaign.name,
        ],
    )

    # Constraint: minimum spend per channel when active
    min_spend_bound = require(
        Allocation.x_spend >= Allocation.effectiveness.channel.min_spend * Allocation.x_active
    )
    s.satisfy(min_spend_bound)

    # Constraint: maximum spend per channel when active
    max_spend_bound = require(
        Allocation.x_spend <= Allocation.effectiveness.channel.max_spend * Allocation.x_active
    )
    s.satisfy(max_spend_bound)

    # Constraint: per-campaign budget across all channels
    campaign_spend = (
        sum(Allocation.x_spend)
        .where(Allocation.effectiveness.campaign == Campaign)
        .per(Campaign)
    )
    budget_limit = require(campaign_spend <= Campaign.budget)
    s.satisfy(budget_limit)

    # Constraint: require at least one active channel per campaign
    campaign_channels = (
        sum(Allocation.x_active)
        .where(Allocation.effectiveness.campaign == Campaign)
        .per(Campaign)
    )
    min_channels = require(campaign_channels >= 1)
    s.satisfy(min_channels)

    # Constraint: total budget across all campaigns (scenario parameter)
    total_budget_limit = require(sum(Allocation.x_spend) <= total_budget)
    s.satisfy(total_budget_limit)

    # Objective: maximize total expected conversions
    total_conversions = sum(Allocation.x_spend * Allocation.effectiveness.conversion_rate)
    s.maximize(total_conversions)

With the formulation defined, each scenario iteration sets total_budget, builds a fresh SolverModel, and solves.

scenario_results = []

SCENARIO_PARAM = "total_budget"
SCENARIO_VALUES = [35000, 45000, 55000]

for scenario_value in SCENARIO_VALUES:
    print(f"\nRunning scenario: {SCENARIO_PARAM} = {scenario_value}")

    # Set scenario parameter value
    total_budget = scenario_value

    # Create a fresh SolverModel for each scenario.
    solver_model = SolverModel(model, "cont")
    build_formulation(solver_model)

    # Solve the model with a time limit of 60 seconds. The `Solver` class provides
    # an interface to various optimization solvers. Here we use the open-source
    # HiGHS solver, which is suitable for linear and mixed-integer problems.
    solver = Solver("highs")
    solver_model.solve(solver, time_limit_sec=60)

    scenario_results.append({
      "scenario": scenario_value,
      "status": str(solver_model.termination_status),
      "objective": solver_model.objective_value,
    })
    print(
      f"  Status: {solver_model.termination_status}, "
      f"Objective: {solver_model.objective_value}"
    )

    # Print spend allocation from solver results
    var_df = solver_model.variable_values().to_df()
    spend_df = var_df[
      var_df["name"].str.startswith("spend") & (var_df["value"] > 0.001)
    ]
    print(f"\n  Spend allocation:")
    print(spend_df.to_string(index=False))

# Summary
print("\n" + "=" * 50)
print("Scenario Analysis Summary")
print("=" * 50)
for result in scenario_results:
    print(f"  {result['scenario']}: {result['status']}, obj={result['objective']}")

Solve and print results

    # Solve the model with a time limit of 60 seconds. The `Solver` class provides
    # an interface to various optimization solvers. Here we use the open-source
    # HiGHS solver, which is suitable for linear and mixed-integer problems.
    solver = Solver("highs")
    solver_model.solve(solver, time_limit_sec=60)

    scenario_results.append({
      "scenario": scenario_value,
      "status": str(solver_model.termination_status),
      "objective": solver_model.objective_value,
    })
    print(
      f"  Status: {solver_model.termination_status}, "
      f"Objective: {solver_model.objective_value}"
    )

    # Print spend allocation from solver results
    var_df = solver_model.variable_values().to_df()
    spend_df = var_df[
      var_df["name"].str.startswith("spend") & (var_df["value"] > 0.001)
    ]
    print(f"\n  Spend allocation:")
    print(spend_df.to_string(index=False))

After all scenarios run, the script prints a small summary:

# Summary
print("\n" + "=" * 50)
print("Scenario Analysis Summary")
print("=" * 50)
for result in scenario_results:
    print(f"  {result['scenario']}: {result['status']}, obj={result['objective']}")

Customize this template

Here are some ideas for how to customize and extend this template to fit your specific use case.

Change the scenario parameters

This template includes a simple what-if analysis that reruns the same optimization under different company-wide budget caps.

Parameter	Type	Values	Description
`total_budget`	numeric	`35000`, `45000`, `55000`	Total spend cap across all campaigns

How to customize the scenarios:

In ad_spend_allocation.py, edit SCENARIO_VALUES to the budgets you want to test.

How to interpret results:

If increasing total_budget doesn’t change the objective, the total budget cap is non-binding (other constraints are limiting).
If reducing total_budget decreases the objective, the cap is binding and forces spend away from higher-converting allocations.

Use your own data

Replace the CSVs in data/ with your own, keeping the same column names (or update the loading logic in ad_spend_allocation.py).
Ensure that effectiveness.csv only references valid channel_id and campaign_id values.

Extend the model

Add per-channel total spend limits across all campaigns.
Add campaign conversion targets as constraints (using Campaign.target_conversions).
Add diminishing returns (piecewise linear approximations) if conversion rate decreases with spend.

Scale up and productionize

Replace CSV ingestion with Snowflake sources.
Write allocations back to Snowflake after solving.

Troubleshooting

Why does authentication/configuration fail?

Run rai init to create/update raiconfig.toml.
If you have multiple profiles, set RAI_PROFILE or switch profiles in your config.

Why does the script fail to connect to the RAI Native App?

Verify the Snowflake account/role/warehouse and rai_app_name are correct in raiconfig.toml.
Ensure the RAI Native App is installed and you have access.

Why do I get Status: INFEASIBLE?

Check that each campaign budget is high enough to pay for at least one active channel’s min_spend.
Check that channel min_spend values are not greater than max_spend.
Confirm conversion rates are present for each campaign (missing effectiveness rows reduce options).

Why is the spend allocation empty?

The script filters allocations with Allocation.x_spend > 0.001. If everything is near zero, inspect constraints and budgets.
Confirm input CSVs were read correctly and contain rows.

What this template is for

Marketing teams face a recurring challenge: how to distribute a limited budget across multiple advertising channels and campaigns to get the most conversions. Each channel (search, social, display, video, email) has different minimum and maximum spend thresholds, and each channel-campaign combination has a different conversion rate. The goal is to find the spend allocation that maximizes total expected conversions while respecting per-channel bounds, per-campaign budgets, and an overall budget cap. Guessing at this by hand leaves conversions on the table; a small change in the mix can move the outcome more than a small change in the total budget.

This template also answers the follow-up question every planner asks: if we had more budget, would it help? It sweeps three total-budget levels (45K, $55K) so you can see how additional budget translates into incremental conversions and which channels the optimizer activates at each level — often revealing that beyond a point the extra money buys nothing.

Under the hood it uses prescriptive reasoning: a mixed-integer program where binary variables decide which channel-campaign pairs to fund and continuous variables set the spend, solved for all three budget levels at once.

Who this is for

Marketing analysts optimizing media spend across channels.
Growth teams evaluating budget scenarios for campaign planning.
Data scientists building prescriptive models for advertising optimization.
Developers learning mixed-integer programming (MIP) with RelationalAI.
Assumed knowledge: comfortable reading Python. The marketing and optimization terms are explained as they come up, and no prior RelationalAI experience is required to run it.

What you’ll build

A funded spend allocation across every channel-campaign pair that maximizes total expected conversions within per-channel, per-campaign, and total-budget limits, produced by prescriptive reasoning (mixed-integer program).
Channel activation logic that enforces minimum and maximum spend only when a pair is funded, using binary decision variables.
A Scenario concept that drives three budget levels through a single solve, so the whole what-if sweep is one optimization rather than three.
A per-scenario allocation table you can query from the ontology after the run, showing which pairs are funded and at what spend.

What’s included

Model: a single ontology with Channel, Campaign, Effectiveness, Scenario, and an Allocation decision concept — plus the prescriptive formulation (decision variables, constraints, objective) that runs on it.
Runner: ad_spend_allocation.py — one Python script that loads the CSVs, builds the model, solves all scenarios at once, and prints the allocation table.
Runbook: runbook.md — a paste-testable walkthrough that reproduces the template step by step with the RAI skills; as important a reference as the script itself.
Sample data: 5 channels with spend bounds, 3 campaigns with budgets, and the 15 channel-campaign conversion rates. See Sample data below.
Outputs: solver termination status, objective value, and a per-scenario table of non-trivial spend allocations printed to stdout; the spend and funding decisions are also written back to the ontology as queryable properties.

Prerequisites

Access

A Snowflake account with the RelationalAI Native App installed.
A Snowflake user with permissions to access the RelationalAI Native App.

Tools

Python >= 3.10.
RelationalAI Python SDK (relationalai == 1.0.14).

Quickstart

Download the template and extract it:
Terminal window
```
curl -O https://docs.relational.ai/templates/zips/v1/ad_spend_allocation.zip
unzip ad_spend_allocation.zip
cd ad_spend_allocation
```
You can also download the template ZIP using the “Download ZIP” button at the top of this page.

Create a virtual environment and activate it:

python -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip

Install dependencies:
Terminal window
```
python -m pip install .
```
Configure your RelationalAI connection:
Terminal window
```
rai init
```
Run the template end-to-end:
Terminal window
```
python ad_spend_allocation.py
```
Expected output. A per-scenario allocation table confirms a successful run (trimmed to the first budget level here; the full run also prints the 55K scenarios):
```
Spend allocation per scenario:
    scenario channel         campaign    spend
  budget_35k   Email  Brand_Awareness   2000.0
  budget_35k   Email    Seasonal_Sale   2000.0
  budget_35k  Search   Product_Launch  10000.0
  budget_35k  Search    Seasonal_Sale   8000.0
  budget_35k  Social  Brand_Awareness   3000.0
  budget_35k   Video   Product_Launch  10000.0
```
All three budgets invest heavily in Search and Video (highest ROI channels). The 45K and 5K) and increase Social Brand_Awareness to 10K from 55K produces no new activations. See runbook.md for the full printout and a step-by-step walkthrough.

Template structure

The tree below shows the top-level layout:

ad_spend_allocation/
├── ad_spend_allocation.py   # Main script (ontology, formulation, single-solve scenario sweep)
├── data/
│   ├── channels.csv         # 5 channels with min/max spend and ROI coefficient
│   ├── campaigns.csv        # 3 campaigns with budget and target conversions
│   └── effectiveness.csv    # 15 channel-campaign conversion rates
├── README.md                # this file
├── runbook.md               # analyst-facing paste-testable walkthrough
└── pyproject.toml           # dependencies

Start here: run python ad_spend_allocation.py for the full run end to end, or follow runbook.md to reproduce it step by step with the RAI skills.

Sample data

The bundled data is synthetic and illustrative — designed to teach the optimization flow, not to match a specific advertiser’s account.

channels.csv (5 rows) — the marketing channels (Search, Social, Display, Video, Email), each with a minimum and maximum spend and an ROI coefficient.
campaigns.csv (3 rows) — the campaigns (Brand_Awareness, Product_Launch, Seasonal_Sale), each with a budget and a target-conversions figure.
effectiveness.csv (15 rows) — one row per channel-campaign pair, giving the conversion rate (conversions per dollar spent) for that pair.

The three budget levels (45K, $55K) that drive the scenario sweep are defined in the script, not in a CSV.

Model overview

One ontology holds the inputs and the decision variables. The three source CSVs load into Channel, Campaign, and Effectiveness; Scenario carries the budget levels; and Allocation holds the per-pair decision variables the solver sets.

Key entities: Channel — a marketing channel with spend bounds and an ROI coefficient; Campaign — a campaign with a budget and a conversion target; Effectiveness — the conversion rate for one channel-campaign pair (also the link between a channel and a campaign); Scenario — a budget level in the what-if sweep; and the decision concept Allocation — one per channel-campaign pair, holding the spend and funding variables the solver sets (indexed by Scenario, so a single solve covers all budget levels).
Primary identifiers: integer id on Channel and Campaign; a composite channel_id + campaign_id on Effectiveness; a string name on Scenario; and the linked Effectiveness on Allocation.
Important invariants: spend is non-negative; x_active is binary (0/1); spend on a pair sits within its channel’s min/max only when the pair is active; per-campaign spend stays within the campaign budget; every campaign has at least one funded channel; and total spend stays within the scenario’s total budget.

For the full concept and property definitions, see ad_spend_allocation.py; runbook.md builds them step by step with the RAI skills.

How it works

The script loads the CSVs into concepts, defines the decision variables, adds the constraints and objective, and solves all budget levels in one call.

Define the ontology. Channels, campaigns, and their per-pair effectiveness (conversion rates) load into concepts, with the effectiveness rows linking each channel to each campaign.
Model budget levels as a concept. Scenario carries the three total-budget levels, so the what-if sweep is data rather than a Python loop.
Define decision variables. For each channel-campaign pair the solver sets a continuous spend amount and a binary activation indicator, both indexed by scenario so one solve covers every budget level.
Add constraints. Spend must fall within a channel’s min/max only when its pair is active, per-campaign spend stays within the campaign budget, every campaign keeps at least one active channel, and total spend stays within the scenario’s total budget — each scoped per scenario.
Maximize conversions. The objective sums spend times conversion rate across all allocations, so the solver funds the highest-return pairs first.
Solve once for all scenarios. A single HiGHS solve covers all three budget levels; results are extracted per scenario and printed as a table.

See ad_spend_allocation.py for the implementation and runbook.md for the skill-driven reproduction. The end-to-end flow:

CSV inputs → load into concepts → decision variables (spend + funding, per scenario)
   → constraints + objective → single HiGHS solve → per-scenario allocation table

Customize this template

Focus on the first changes most users will make.

Use your own data

Replace the CSVs in data/ with your own; keep the column names listed in Sample data above (channels.csv: id, name, min_spend, max_spend, roi_coefficient; campaigns.csv: id, name, budget, target_conversions; effectiveness.csv: channel_id, campaign_id, conversion_rate).
Add or remove channels by editing channels.csv with new spend bounds and ROI coefficients; add campaigns by extending campaigns.csv and adding the corresponding rows in effectiveness.csv.
Change the conversion rates in effectiveness.csv to reflect your own channel-campaign performance data. Rates are conversions per dollar (a rate of 0.10 means 0.10 conversions per dollar), so keep min/max spend bounds and campaign budgets in the same currency units.
For Snowflake-backed runs, swap the read_csv(...) calls for model.data(snowflake_table) calls.

Tune parameters

Budget levels — the three scenarios (budget_35k, budget_45k, budget_55k) are defined in the scenario_data block near the top of the script. Add, remove, or change levels there to sweep a different range.
Spend bounds and budgets — per-channel min_spend / max_spend live in channels.csv; per-campaign budget lives in campaigns.csv. These are usually the binding limits, so they are the first knobs to adjust when the total-budget cap stops mattering.
Solver settings — the solve uses HiGHS with a 60-second time limit (problem.solve("highs", time_limit_sec=60)); adjust the limit for larger instances.

Extend the model

Add diminishing returns by introducing piecewise-linear or concave conversion functions in place of the flat per-dollar rate.
Add channel-level constraints such as a maximum total spend per channel across all campaigns.
Add temporal dimensions to model multi-period budget allocation with carry-over effects.
Add a coverage floor such as a minimum spend or minimum conversion target per campaign.

Scale up / productionize

Replace the data/ CSV bundle with data loaded directly from Snowflake tables via model.data(...).
Pin the relationalai SDK version (see Prerequisites) so runs are reproducible; the single-solve Scenario design keeps the whole sweep deterministic within a solve.
Schedule the run as part of a planning pipeline and read the written-back Allocation.x_spend / Allocation.x_active properties from the ontology for downstream reporting.

Troubleshooting

Solver returns INFEASIBLE

Check that each scenario’s total budget is large enough to satisfy the minimum-spend requirements for at least one channel per campaign.
Verify that per-campaign budgets in campaigns.csv are consistent with channel minimum spends.
Ensure every campaign has at least one channel in effectiveness.csv.

Import error for relationalai

Confirm your virtual environment is active: which python should point to .venv.
Reinstall dependencies: python -m pip install ..

Authentication or configuration errors

Run rai init to create or update your RelationalAI / Snowflake configuration.
If you have multiple profiles, set export RAI_PROFILE=<your_profile>.

Objective value seems too low or too high

Conversion rates in effectiveness.csv are per dollar spent. A rate of 0.10 means 0.10 conversions per dollar.
Verify that your conversion rates are scaled appropriately for your use case.
Check that channel min/max spend bounds are in the same units as campaign budgets.

Learn more

Core concepts

PyRel v1 query language — model.where(...), model.select(...), and aggregation used to build and read the model.

Reasoner reference

Prescriptive reasoner — the Problem API, decision variables, constraints, and objectives used in this template.

CLI / SDK guides

RelationalAI setup and configuration — installing the SDK and running rai init.

Support

File issues at the RelationalAI templates repository.