Optimization of an TV advertising campaign based on TRP, GRP indicators#
Description: The modern world is unthinkable without advertising. Advertising is the engine of progress. When developing and conducting advertising campaigns, companies strive to maximize the effectiveness of their advertising dollars. In this case, the effectiveness of advertising is measured by the degree to which the campaign goals are achieved. The main goals of an advertising campaign can be:
Increase in sales;
Attraction of new clients;
Launch of a new product on the market;
Increasing/maintaining market share or profit level.
Mathematical optimization allows you to synchronize all the specified factors and limitations of advertising channels with each other, and determine, among many options, the most effective (from the point of view of campaign goals) plan for investing in advertising.
Tags: Marketing, Advertisement, Deterministic model, ampl-only, cbc
Notebook author: Mikhail Riabtsev <mail@solverytic.com>
Let’s start by downloading the necessary extensions and libraries
# Install dependencies
%pip install -q amplpy pandas
import pandas as pd # Loading panda to work with pandas.DataFrame objects (https://pandas.pydata.org/)
import numpy as np # Loading numpy to perform multidimensional calculations numpy.matrix (https://numpy.org/)
import matplotlib.pyplot as plt # Loading library for creating static, animated, and interactive visualizations (https://matplotlib.org/)
# Google Colab & Kaggle integration
from amplpy import AMPL, ampl_notebook
ampl = ampl_notebook(
modules=["cbc"], # modules to install
license_uuid="default", # license to use
) # instantiate AMPL object and register magics
from amplpy import AMPL
ampl = AMPL() # create a new AMPL object with all default settings
1. Problem statement#
Media agency «Broadcaster» - carries out all media planning and purchase of advertising from advertisers for the international company «Super-Tampon», a world leader in the production of feminine hygiene products. This client is extremely important to the agency as it makes a very significant contribution to the agency’s turnover and helps to impress other potential clients during negotiations. In the case of planning and purchasing advertising time on TV, the «Broadcaster» Media Agency deals with two subcontractors who have exclusive rights to all advertising on 7 TV channels:
«Media International» - TV-1, TV-2, TV-3, TV-4 and TV-5
«Media Group» - TV-6 and TV-7
When purchasing advertising time, the «Broadcaster» Media Agency uses special advertising impact units: GRP (Gross Rating Points) and TRP (Target Rating Points).
1 GRP is the time required for 1% of the channel’s adult audience (men and women over 18 years of age) to see this advertisement at least once. 1 TRP is the time required for 1% of the target audience (in the case of the «Super-Tampon» company, these are women from 15 to 35 years old with a high level of income) to see this advertisement at least once. The media agency «Broadcaster» buys advertising exposure (measured in GRP units) from subcontractors.
«Media International» and «Media Group» competed fiercely for a share of any client’s budget and eventually agreed to give clients (or their agencies) maximum discounts if the client’s budget was split between 70% - «Media International», 30% - «Media Group».
In turn, the «Broadcaster» media agency sells advertising exposure to its clients (including «Super-Tampon»), measured in TRP units - clients are interested in ensuring that the advertisement is seen by the target group of viewers.
When choosing channels, the company is guided by the attractiveness index - the ratio of the target (for a given brand) audience of the channel to the entire adult audience:
\(Attractiveness Index = (TRP / GRP) * 100\);
Customers want this index to be as large as possible for the entire advertising campaign, and at least not less than 100. The total cost of 1 GRP, maximum discounts and typical attractiveness index value (assessed by the agency for Super Tampon advertising) for each channel are shown in the table.
Subcontractors |
TV channel |
Total cost of 1 GRP, USD |
Discount, % |
Attractiveness Index |
|---|---|---|---|---|
Media International |
TV-1 |
25 |
35 |
75 |
Media International |
TV-2 |
18 |
35 |
90 |
Media International |
TV-3 |
5 |
35 |
115 |
Media International |
TV-4 |
18 |
35 |
135 |
Media International |
TV-5 |
32 |
35 |
105 |
Media Group |
TV-6 |
45 |
45 |
95 |
Media Group |
TV-7 |
27 |
45 |
125 |
Table.1. Main characteristics of TV channels.
«Super-Tampon» is one of the most advanced clients of the agency, therefore, more than the attractiveness index, they value the share of TRP impressions on the 3 leading channels: TV-1, TV-2, TV-6. For each advertising campaign, the customer requires that the share of impressions on these channels was at least 70%. At the same time, it is required that the «Broadcaster» Media Agency use all 7 channels in the advertising campaign, with the TRP share for each of the remaining 4 channels not lower than 3 percent of the total for the advertising campaign. $500,000 has been allocated for the «Super-Tampon» advertising campaign.
Optimization goals
It is necessary to develop an advertising campaign that maximizes the response of the target audience, taking into account the fulfillment of the specified customer conditions.
3. AMPL formulation of the model#
Use %%ampl_eval to evaluate AMPL commands and declarations
%%ampl_eval
reset;
### SETS
set CLIENTS := {'Super_Tampon'}; # Сlient
set AGENCIES := {'Broadcaster'}; # Media agency
set SUBCONTR := {'Media International', 'Media Group'}; # Subcontractors
set CHANNELS {SUBCONTR}; # Advertising channel
set METRICS :={'GPR', 'TRP'}; # Advertising metrics
set LINKS within {METRICS, CLIENTS union SUBCONTR}; # Main performance indicator
set ITEMS := {'cost_gpr', 'discount', 'attractiveness'}; # Main characteristics of the channels
### PARAMETERS
param Amount {sb in SUBCONTR, с in CHANNELS[sb], ITEMS} >= 0; # Values of main characteristics of media channels
param Money := 500000; # Advertising company budget
### VARIABLES
var X {sb in SUBCONTR, с in CHANNELS[sb]} >= 0; # Quantity of GRP purchased
### OBJECTIVE
maximize Total_TRP: sum{sb in SUBCONTR, c in CHANNELS[sb]}
X[sb,c] * Amount[sb,c,'attractiveness']/100; # TRP/GRP = attrective, TRP = GRP*attrective/100;
### CONSTRAINTS
# Customers want the overall attrective index for the entire advertising campaign to be as large as possible, and at least not less than 100
s.t. Attractiveness: sum{sb in SUBCONTR, c in CHANNELS[sb]}
X[sb,c] * Amount[sb,c,'attractiveness'] >= 100 * sum{sb_ in SUBCONTR, c_ in CHANNELS[sb_]}X[sb_,c_];
# Costs for an advertising campaign <= Budget
s.t. Budget: sum{sb in SUBCONTR, c in CHANNELS[sb]}
X[sb,c] * Amount[sb,c,'cost_gpr'] <= Money; #*(1-Amount[sb,c,'discount'])
# At least 70% of all TPR impressions must be on TV-1, TV-2, TV-6
s.t. TPR_70: sum{sb in SUBCONTR, c in CHANNELS[sb]: c in{'TV-1', 'TV-2', 'TV-6'}}
X[sb,c] * Amount[sb,c,'attractiveness'] / 100 >= 0.7 * sum{sb in SUBCONTR, c in CHANNELS[sb]} X[sb,c] * Amount[sb,c,'attractiveness']/100;
# Use all 7 channels with their TPR share not lower than 3% of the total for the entire company
s.t. ChannelsWithTPR{sb in SUBCONTR, c in CHANNELS[sb]}:
X[sb,c] * Amount[sb,c,'attractiveness'] / 100 >= 0.03 * sum{sb_ in SUBCONTR, c_ in CHANNELS[sb_]} X[sb_,c_] * Amount[sb_,c_,'attractiveness']/100 ;
# Working out a separate case when the budget of an advertising company is divided between contractors 70/30
s.t. CompetitionRate70_30: sum{sb in SUBCONTR, c in CHANNELS[sb]: sb in {'Media International'}}
X[sb,c] * Amount[sb,c,'cost_gpr'] = Money *0.7;
4. Load data#
%%ampl_eval
data;
set CHANNELS['Media International']:= 'TV-1', 'TV-2', 'TV-3', 'TV-4', 'TV-5';
set CHANNELS['Media Group'] := 'TV-6', 'TV-7';
set LINKS: GPR TRP :=
'Super_Tampon' - +
' Media International' + -
'Media Group' + - ;
param Amount :=
['Media International',*,*]:cost_gpr discount attractiveness :=
'TV-1' 1500 0.35 75
'TV-2' 1350 0.35 90
'TV-3' 1100 0.35 135
'TV-4' 1000 0.35 105
'TV-5' 1000 0.35 135
['Media Group',*,*]: cost_gpr discount attractiveness :=
'TV-6' 1350 0.45 95
'TV-7' 900 0.45 125 ;
5. Solve problem#
Use %%ampl_eval to evaluate AMPL commands and declarations
%%ampl_eval
option solver cbc; # Choosing a solver
# Defining Output Settings
option show_stats 1; # (1) Show statistical information about the size of the problem. Default 0 (statistics are not displayed)
option display_1col 0; # Data Display Settings
option omit_zero_rows 1; # Hide rows with 0 values. Default (0)
# Selecting an Objective Function (choose one of)
objective Total_TRP; # goal 1
solve; # Solve the model (Objective function TotalAudienceInform)
7 variables, all linear
11 constraints, all linear; 75 nonzeros
1 equality constraint
10 inequality constraints
1 linear objective; 7 nonzeros.
cbc 2.10.10:cbc 2.10.10: optimal solution; objective 392.1201302
0 simplex iterations
6. Display the solution#
Use %%ampl_eval
%%ampl_eval
display X; # Show the value of the variables for the resulting solution
X [*,*] (tr)
: 'Media Group' 'Media International' :=
TV-1 . 15.6848
TV-2 . 181.25
TV-3 . 8.71378
TV-4 . 11.2034
TV-5 . 60.9965
TV-6 104.837 .
TV-7 9.41088 .
;
7. Retrieve solution as a pandas dataframe
ampl.var["X"].get_values().to_pandas()
| X.val | ||
|---|---|---|
| index0 | index1 | |
| Media Group | TV-6 | 104.837189 |
| TV-7 | 9.410883 | |
| Media International | TV-1 | 15.684805 |
| TV-2 | 181.250175 | |
| TV-3 | 8.713781 | |
| TV-4 | 11.203432 | |
| TV-5 | 60.996465 |