Spreadsheet game in python (part 3)
Spreadsheet game in python: Part 1 Part 2
In Part 1 I created a function that generates a game population, in part 2 I created functions to simulate death rate and birthrate so that there would be an organic change in the population. In part 3 I’ll try to start bringing in the elements of the game so that players could compete for the attention and in the end for their wallets.
The plan
The goal is to create a simple mechanism where decisions by players somehow would have an effect on the behaviour of individuals in the population.
The simplified idea would be something like that:
- Each individual in the population reads newspapers, listens to the radio and watches TV. There should be some consumption logic, ie rich people in the city watch more TV than poor people in rural areas (especially in the year 1970).
- Each player can invest in marketing: newspaper ads, radio ads, TV ads. Based on the marketing budgets in each channel, awareness about the player can be calculated for each individual.
- Awareness can be used as a probability to decide whether an individual will seek services to fulfil needs from a player.
- If there is a need-offering fit a customer will buy (subscribe) to the players' product.
In order to do all that I’ll need to generate media consumption habits for the population. Some values how much each individual read newspapers, listen to the radio, watch TV. That will be the basis of how the marketing in each channel affects the individual.
I decided to define three channels the population consumes based on location and income. Each individual will get a value between 0–1 of what and ‘how much’ they consume.
Channels
Newspaper “Rural weekly” (rw)
Newspaper “Rural weekly” is consumed mostly by individuals living in location rural and in low, med and high income group:
Lookup table for newspaper “Rural weekly” looks like that:
Radio
Similar dataframe table is produced for radio. Radio is consumed mostly by city and suburb dwellers with med to v-high income
TV
As the game starts in 1970, the TV consumption is low and it has some traction in high to v-high income group in the city.
The idea is that as the game progresses (years pass) it would be possible to update these tables to reflect the changes in media consumption patterns. Fewer people listening to the radio, more people watching TV etc.
In order to use these tables and populate the population dataframe with consumption info for each individual following operation must be run each time a new population is generated (at the beginning and during new births):
This will add new columns to the population dataframe where each row (individual) has their own media consumption patterns.
Marketing
Each player can invest in marketing by allocating a marketing budget and decide how much and where they will invest. For the purpose of testing, the marketing budgets are currently hard-coded.
The marketing effectiveness for each player in a channel currently depends on how much all players invest in the same channel. If all players invest 1(eur) they all have marketing score 1/(1+1+1)=0.333… If player A invests 100(eur) but players B and C still invest 1(eur) then player A score will be ~0.9 and player B and C score ~0.01.
Marketing scores based on the example code:
Each player (company) can invest in marketing in order to generate awareness. Awareness converts to leads. Leads end up in the shops (sales channel). Shops have limited throughput (how many customers they are able to service) and conversion rate (the probability that the shop converts lead into a customer if there is a need-offering match). All leads not converted to customers will end up back in the population available in the next round (year).
Awareness calculation: marketing channel score * channel consumption * random number 0, 0.5 or 1 for each channel summed together. A random number is there to just make it more random that the marketing efforts have an effect.
Then it's possible to use awareness values and random.choice function to assign a choice company for an individual.
Line 8 will calculate awareness and line 32 will assign company based on random.choice function where the probability distribution is awareness. A ‘none’ choice will be made if total awareness (‘awarenessum’) is less than 0.4 and satisfaction is less than 0.1 (all individuals have default satisfaction 0 and a satisfaction score can be calculated based on various stuff. More about that later)
Now we have a customer base where marketing efforts of players have attracted some of the customers' awareness these could be considered leads. This data is in column ‘sample-awareness’:
If we count the ‘leads’ from the dataframe, whatever marketing decisions player ‘faststat’ has made in this example, it has attracted the most leads:
In the future, there should be multiple sales channels: shops, phone, online each with unique attributes, limits and cost structure. For now, there will be only one: Shops.
Shops
A shop is a sales channel where leads can be converted into paying customers. A player can have any number of shops. Shops have a throughput value and conversion rate. Throughput limits how many customers one shop can service and conversion rate will be used to calculate the probability that customer will be closed given there is a fit between customer need, income and offers.
The player should be then able to decide whether to open new shops, increase throughput or improve the conversion rate.
Some simple ‘offers’:
After some trial and error, I ended up with the following:
- Lines 8–10 select only customers whose preferred ‘channel’ is ‘shop’.
- Lines 12–13 will check if there are more customers than maximum shop throughput in the shops. If that is the case max_troughput number of customers will be randomly selected from the customers' pool whose preferred ‘channel’ is ‘shop’ (real-life analogue would be long lines in the shops and some marketing effort goes to waste because of the shop throughput will be a limiting factor).
- Lines 18–22 will look at the ‘need-mcalling’ (need for mobile calling) row of each customer who is ‘in the shop’ and tires to find the best match from an offer_df table.
- Line 26 will check if there is any calling need and applies conversion rate
- Line 27 will check whether the customer has income to buy the offer what the algorithm found in lines 18–22
- Lines 28–31 will close the deal
- Line 32 will calculate satisfaction based on how close the offering was to the actual need (if the satisfaction is very low, the customer will be open to new offers next year)
To test how it all comes together:
The output isn’t very elegant but good enough to validate that the loop works:
telcom - customers in shops: 279 - max: 279 - new customers: 7
comline - customers in shops: 167 - max: 167 - new customers: 5
fastsat - customers in shops: 620 - max: 300 - new customers: 39
Births: 150
died: 365
telcom - customers in shops: 239 - max: 239 - new customers: 8
comline - customers in shops: 201 - max: 201 - new customers: 7
fastsat - customers in shops: 635 - max: 300 - new customers: 37
### 1970 customers:
none 9684
fastsat 76
telcom 15
comline 10
Name: closed-company, dtype: int64
Births: 146
died: 341
telcom - customers in shops: 264 - max: 264 - new customers: 9
comline - customers in shops: 184 - max: 184 - new customers: 6
fastsat - customers in shops: 578 - max: 300 - new customers: 33
### 1971 customers:
none 9443
fastsat 106
telcom 26
comline 15
Name: closed-company, dtype: int64
Births: 143
died: 287
telcom - customers in shops: 258 - max: 258 - new customers: 10
comline - customers in shops: 176 - max: 176 - new customers: 5
fastsat - customers in shops: 596 - max: 300 - new customers: 29
### 1972 customers:
none 9260
fastsat 132
telcom 35
comline 19
Name: closed-company, dtype: int64
Births: 141
died: 280
telcom - customers in shops: 240 - max: 240 - new customers: 4
comline - customers in shops: 173 - max: 173 - new customers: 11
fastsat - customers in shops: 537 - max: 300 - new customers: 37
### 1973 customers:
none 9075
fastsat 165
telcom 37
comline 30
Name: closed-company, dtype: int64
Births: 139
died: 210
telcom - customers in shops: 233 - max: 233 - new customers: 11
comline - customers in shops: 198 - max: 198 - new customers: 5
fastsat - customers in shops: 603 - max: 300 - new customers: 32
### 1974 customers:
none 8954
fastsat 201
telcom 48
comline 33Next, I’ll probably try to create some sort of input/output for the players so they could change inputs (marketing budgets, etc) and think about how to implement corporate finances and some sort of simulated economy. The end goal of this game should be the most profitable company.
Source: https://github.com/martobjartel/spreadsheetceo
(to be continued)
