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.

Partner with the AMPL team to transform complex problems into optimized solutions. AMPL consulting services combine deep technical knowledge with industry-leading insights, helping you unlock the full potential of optimization within your organization.

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)

variables, all linear
constraints, all linear; 75 nonzeros
equality constraint
inequality constraints
linear objective; 7 nonzeros.

cbc 2.10.10:cbc 2.10.10: optimal solution; objective 392.1201302
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            .
;

ampl.var["X"].get_values().to_pandas()

		X.val
index0	index1
Media Group	TV-6	104.837189
Media Group	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

7. Retrieve solution as a pandas dataframe¶

Optimization Methods in Finance: Chapter 3

Optimization of an advertising campaign for launching a new product on the market