GDBase

Algorithm maximizing revenue from marketing campaign – application of GDBase

Creating a marketing campaign to gain leads may involve several problems, which are problemtic and tedious to solve. Thus, we want to build an algorithm that will maximize the expected revenue taking into account the limitation concerning number of offers sent to each lead in one message.

AdvancedMiner has a built-in GDBase database that supports triggers and queries of any complexity. It enables to freely write Python-based scripts and incorporate most SQL queries into them. As a result, it is possible to perform operations with many limitations resulting from information contained in databases.

The Python code (called Gython in AdvancedMiner due to modifications made to simplify analytical operations) can be entered directly into the script or during SQL use after TRANSFORM and GLOBALS.

GDBase, unlike other databases, allows you to create a simple algorithm that maximizes revenue even with many limitations.

Inserting a table from another database in the GDBase.

To insert a table from another database in the GDBase you need to enter a query in the script:

sql: IMPORT COMPRESSED Query: SELECT * FROM another_database.name_of_the_table AS s1 AS name_of_the_table_in_GDBase USING ODBC('DSN=RP')

An example of a table used in an algorithm in a marketing campaign:

GDBase - algorytm maksymalizujący przychód z kampanii marketingowej - 1

Download the data used in this post here.

The client_id column contains the client ID, which can be for example an e-mail address or a telephone number. To each of the customer there are assigned identifiers for all campaings (campaign_id), the probability of obtaining a lead from the customer for the specific campaign (lead_probability), the revenue that the campaign may generate (income), the expected revenue taking into account the probability (expected_income) and a daily lead limitations for each campaign (lead_limit).

The following algorithm is based on a sample table called “example”. The following steps contain comments to help you understand the algorithm.

Firstly, you must initialize constraint wariables.

max_number_of_offers = 3 #maximum number of offers per one message cups = { } #a bucket containing expected number of responses per lead limits = { } #daily lead limits for each campaign

We add a list of offers with limits and add cups and limits values.

sql a: SELECT DISTINCT campaign_id, lead_limit FROM example print a for r in a: cups[r[0]] = 0 limits[r[0]] = r[1]

We sort the table on the basis of the maximum scoring and scoring for each campaign.

sql: REPLACE TABLE example_recommendations AS SELECT * FROM ( SELECT client_id, max(expected_income) AS max_score FROM example GROUP BY 1 ORDER BY max_score DESC ) AS s1 LEFT JOIN (SELECT * FROM example ORDER BY expected_income DESC) as s2 USING (client_id) TRANSFORM: #Gython use if current_client_id <> client_id: number_of_offers = 0 for offer, contents in cups.items(): #we place an offer in the bucket, which will be sent if (offer == campaign_id) and (number_of_offers < max_number_of_offers): #however, we must check whether there is still room in the bucket if contents < limits[offer]: chosen_offer = offer cups[offer] = cups[offer] + lead_probability #the bucket filling criterion is the sum of the probability of obtaining a lead from a given campaign for all customers number_of_offers = number_of_offers + 1 __save__() current_client_id = client_id __skipRow__ = 1 GLOBALS: #the initialisation of global variables current_client_id = '' number_of_offers = 0 max_number_of_offers = $max_number_of_offers cups = $cups limits = $limits KEEP chosen_offer, client_id, lead_probability, expected_income

As a result, we obtain an output table with recommendations of the offers that will generate the highest revenue. For each of the client, there are 3 selected offers The limits for the campaign are maintained.

GDBase - algorytm maksymalizujący przychód z kampanii marketingowej - 2

For comparison, without using an algorithm when selecting a campaign, the response to offers is ten times lower, which results in a much lower revenue obtained from each of these campaigns. This solution makes it possible to deal with all the limitations. The possibility of introducing small modifications makes this algorithm extremely versatile.

Pin It on Pinterest