Ad Spend Allocation

Version:

Experience level: Intermediate
Reasoning types: Prescriptive
Industry: Marketing
Tags: AllocationMILP
Experience level: Intermediate
Reasoning types: Prescriptive
Industry: Marketing
Tags: AllocationMILP
Experience level: Intermediate
Reasoning types: Prescriptive
Industry: Marketing
Tags: budget-allocationmixed-integer-programmingmarketingwhat-if-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.

This template formulates the ad spend allocation problem as a mixed-integer program. Binary variables determine which channel-campaign combinations are active, while continuous variables set the spend levels. Constraints enforce minimum/maximum spend per channel (when active), per-campaign budget limits, and a global budget ceiling. The objective maximizes the total expected conversions computed from spend times conversion rate.

The template includes scenario analysis that sweeps over three total budget levels (45K, $55K), letting you see how additional budget translates into incremental conversions and which channels the optimizer activates at each budget level.

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 ad optimization
Developers learning mixed-integer programming with RelationalAI

What you’ll build

A mixed-integer optimization model for multi-channel, multi-campaign budget allocation
Channel activation logic with minimum/maximum spend enforcement
Per-campaign budget constraints and a global budget cap
Scenario analysis across three budget levels with comparison of results

What’s included

ad_spend_allocation.py — main script with ontology, formulation, and scenario loop
data/channels.csv — 5 channels with spend bounds and ROI coefficients
data/campaigns.csv — 3 campaigns with budgets and target conversions
data/effectiveness.csv — 15 channel-campaign conversion rates
pyproject.toml — Python package configuration

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

Download ZIP:

curl -O https://docs.relational.ai/templates/zips/v1/ad_spend_allocation.zip
unzip ad_spend_allocation.zip
cd ad_spend_allocation

Create venv:

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

Install:
Terminal window
```
python -m pip install .
```
Configure:
Terminal window
```
rai init
```
Run:
Terminal window
```
python ad_spend_allocation.py
```

Expected output:

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
  budget_45k   Email  Brand_Awareness   2000.0
  budget_45k   Email    Seasonal_Sale   2000.0
  budget_45k  Search  Brand_Awareness   5000.0
  budget_45k  Search   Product_Launch  10000.0
  budget_45k  Search    Seasonal_Sale   8000.0
  budget_45k  Social  Brand_Awareness   8000.0
  budget_45k   Video   Product_Launch  10000.0
  budget_55k   Email  Brand_Awareness   2000.0
  budget_55k   Email    Seasonal_Sale   2000.0
  budget_55k  Search  Brand_Awareness   5000.0
  budget_55k  Search   Product_Launch  10000.0
  budget_55k  Search    Seasonal_Sale   8000.0
  budget_55k  Social  Brand_Awareness   8000.0
  budget_55k   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.

Template structure

.
├── README.md
├── pyproject.toml
├── ad_spend_allocation.py
└── data/
    ├── channels.csv
    ├── campaigns.csv
    └── effectiveness.csv

How it works

1. Define the ontology. Channels, campaigns, and their effectiveness (conversion rates) are modeled as concepts:

Channel = Concept("Channel", identify_by={"id": Integer})
Channel.min_spend = Property(f"{Channel} has {Float:min_spend}")
Channel.max_spend = Property(f"{Channel} has {Float:max_spend}")

Campaign = Concept("Campaign", identify_by={"id": Integer})
Campaign.budget = Property(f"{Campaign} has {Float:budget}")

Effectiveness = Concept("Effectiveness", identify_by={"channel_id": Integer, "campaign_id": Integer})
Effectiveness.conversion_rate = Property(f"{Effectiveness} has {Float:conversion_rate}")

2. Define decision variables. Continuous spend amounts and binary activation indicators per channel-campaign pair:

Allocation = Concept("Allocation", identify_by={"effectiveness": Effectiveness})
Allocation.x_spend = Property(f"{Allocation} has {Float:spend}")
Allocation.x_active = Property(f"{Allocation} is {Float:active}")

problem.solve_for(Allocation.x_spend, name=[...], lower=0)
problem.solve_for(Allocation.x_active, type="bin", name=[...])

3. Add constraints. Minimum/maximum spend when active, per-campaign budget limits, at least one active channel per campaign, and a global budget cap:

problem.satisfy(model.require(Allocation.x_spend >= Allocation.effectiveness.channel.min_spend * Allocation.x_active))
problem.satisfy(model.require(Allocation.x_spend <= Allocation.effectiveness.channel.max_spend * Allocation.x_active))

campaign_spend = sum(Allocation.x_spend).where(Allocation.effectiveness.campaign == Campaign).per(Campaign)
problem.satisfy(model.require(campaign_spend <= Campaign.budget))
problem.satisfy(model.require(sum(Allocation.x_spend) <= total_budget))

4. Maximize conversions. The objective sums spend times conversion rate across all active allocations:

total_conversions = sum(Allocation.x_spend * Allocation.effectiveness.conversion_rate)
problem.maximize(total_conversions)

5. Run scenarios. The loop iterates over budget levels, building a fresh Problem for each and comparing results.

Customize this template

Add or modify channels by editing channels.csv with new spend bounds and ROI coefficients.
Add campaigns by extending campaigns.csv and adding corresponding rows in effectiveness.csv.
Change conversion rates to reflect your own channel-campaign performance data.
Add diminishing returns by introducing piecewise linear or concave conversion functions.
Add channel-level constraints such as maximum total spend per channel across all campaigns.
Add temporal dimensions to model multi-period budget allocation with carry-over effects.

Troubleshooting

Solver returns INFEASIBLE

Check that 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.