Using Personas to Simulate Hallux Data#

This example has two goals#

  • To assign personas to the different entities in the Hallux database either randomly or based on existing data.

  • To use the assigned personas to predict an entity’s behavoior in order to create new data.

The example starts by importing the required libraries and creating dictionaries#

# import libraries

from os import getenv
import pandas as pd
import random
from datetime import *; from dateutil.relativedelta import *
import pymssql
from sqlalchemy import create_engine


# Personas dictionary (defined in Hallux tables Persona_Type and Persona)

Personas = {
    "Album" : ("Bomb","Non-Certified","Gold","Platinum","Multi Platinum","Diamond"), 
    "Band" : ("Bar Band","Cover","Tribute","1 hit wonder","Popular","Cult Following","Megaband"),
    "Customer" : ("Occasioanl","Discount","Loyal"),
    "Employee" : ("Bad","Good","Overachiever"),
    "Experience" : ("Bad","Good","Wonderful"),
    "Fan" : ("Casual","Fan","Fanatic"),
    "Music" : ("Popular", "Regional","Seasonal","Specialty","Nostalgic"),
    "Musician" : ("Amateur","Studio","Backup","Lead","Superstar"),
    "Performance" : ("Bad","Good","Epic"),
    "Person" : ("Student","Young Adult","Mature"),
    "Song" : ("Bad","Good","Popular","Hit"),
    "Video" : ("Bad","Good","Viral")
}

# Hallux entity types dictionary defined in the Entity_Type table
# Added an additional value list (table, id column, persona type, assignment method)
# and put in the order needed for processing if not using random selection
# For example, a band's persona will be determined by the personas of its band members.

Entities_List_Columns = ("Table Name","Artificial Key","Persona Type","Assignment Method","Persona Status","Simulation Status")

Entities = {
    
    "Genre" : ("Genre","Genre_Id","Music","Market Share","Under Construction","Under Construction"), 
    "Member" : ("Band_Member","Member_Id","Musician","Random","Completed","Under Construction"),
    "Band" : ("Band","Band_Id","Band","Members","Under Construction","Under Construction"),
    "Agent"   : ("Agent","Agent_Id","Employee","Commissions","Under Construction","Under Construction"),
    "Album" : ("Album","Album_Id","Album","Sales","Under Construction","Under Construction"), 
    "Song" : ("Song","Song_Id","Song","Sales","Under Construction","Under Construction"),
    "Performance" : ("Performance","Performance_Id","Performance","Revenue","Under Construction","Under Construction"),
    "Producer"   : ("Producer","Producer_Id","Employee","Revenue","Under Construction","Under Construction"),
    "Video" : ("Video","Video_Id","Video","Streams","Under Construction","Under Construction"),
    "Profile" : ("Customer_Profile","Profile_Id","Person","Demographics","Under Construction","Under Construction"),
    "Customer" : ("Customer","Customer_Id","Customer","Purchases","Under Construction","Under Construction"),
    "Follower" : ("Band_Follower","Follower_Id","Fan","Engagement","Under Construction","Under Construction"),
    "Playlist" : ("Playlist","Playlist_Id","Experience","Streams","Under Construction","Under Construction"),
    "Order"   : ("Order","Order_Id","Experience","Probability","Under Construction","Under Construction"),
    "Stream"   : ("Stream","Stream_Id","Experience","Probability","Under Construction","Under Construction")
}

Let’s assign Personas#

The dictionaries defined above reflect the Hallux tables designed to support personas#

../../_images/persona_tables.png

Set an individual entity’s persona#

The set_persona function will update the database with an entities persona

Note: This function uses a stored procedure to update data (sql provided below). This allows for greater security and users do not need update, insert or delete permission on specific tables, only execute permission for this stored procedure. Also, the stored procedure will determine if the information needs to be inserted or updated.

def set_persona ( a_entity_type, a_entity_id, a_persona_type, a_persona ):   

    ret_val = ''
   
    sp_exec_sql = "prc_set_Persona"
    parm_list = (str(a_entity_type), int(a_entity_id), str(a_persona_type), str(a_persona))
    cursor.callproc(sp_exec_sql,parm_list)    
 
    return ret_val

IF OBJECT_ID('dbo.prc_set_Persona') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.prc_set_Persona
    IF OBJECT_ID('dbo.prc_set_Persona') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.prc_set_Persona >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.prc_set_Persona >>>'
END
go
Create Proc dbo.prc_set_Persona ( @a_Entity_Type varchar(30), @a_Entity_Id int, @a_Persona_Type varchar(30) , @a_Persona varchar(30) ) 
As 
Begin 
 
/*----------------------------------------------------------------------------- 
  Procedure Name: prc_set_Persona

       Function: Populate the persona table
       Parameters: a_Entity_Type, a_Entity_Id, a_Persona_Type, a_Persona
                 
   Modifications:  
 
	11/18/2022	JDS	Initial Version

 -----------------------------------------------------------------------------*/ 


  DECLARE @errorMsg		varchar(255)
	, @errorNum 		int
	, @RowCount 		int
	, @Entity_Type_Id	int
	, @Persona_Type_Id	int
	, @Persona_Id		int


  -- Initialization										 

  SELECT @errorNum = 0



  select @Entity_Type_Id = Entity_Type_Id from Entity_Type where Entity_Type = @a_Entity_Type

  if @Entity_Type_Id is null
  BEGIN 
    SELECT @errorMsg = 'Unknown Entity Type.' 
    GOTO err_rtn
  END 

  select @Persona_Type_Id = Persona_Type_Id from Persona_Type where Persona_Type = @a_Persona_Type

  if @Persona_Type_Id is null
  BEGIN 
    SELECT @errorMsg = 'Unknown Persona Type.' 
    GOTO err_rtn
  END 

  select @Persona_Id = Persona_Id from Persona where Persona_Name = @a_Persona and Persona_Type_Id = @Persona_Type_Id

  if @Persona_Id is null
  BEGIN 
    SELECT @errorMsg = 'Unknown Persona.' 
    GOTO err_rtn
  END 

  if not exists ( select 1 from Persona_Entity where Entity_Id = @a_Entity_Id and Entity_Type_Id = @Entity_Type_Id)
  begin 

    insert into Persona_Entity ( Persona_Id, Entity_Id, Entity_Type_Id ) values (@Persona_Id, @a_Entity_Id, @Entity_Type_Id )

    SELECT @errorNum = @@error , @Rowcount = @@Rowcount
 
    IF @errorNum != 0 or @Rowcount = 0
    BEGIN 
      SELECT @errorMsg = 'Failed inserting row into Persona_Entity.' 
      GOTO err_rtn
    END 

  end    
  else
  begin

    update Persona_Entity set Persona_Id = @Persona_Id 
     where Entity_Id      = @a_Entity_Id
       and Entity_Type_Id = @Entity_Type_Id

    SELECT @errorNum = @@error , @Rowcount = @@Rowcount
 
    IF @errorNum != 0 or @Rowcount = 0
    BEGIN 
      SELECT @errorMsg = 'Update of Persona_Entity failed.' 
      GOTO err_rtn
    END 


  end

good_rtn: 
 
  Return 0

err_rtn: 

  select @errorMsg = 'SQL Exception (' + object_name(@@procid) + ') - ' + @errorMsg + case when IsNull(@errornum,0) <> 0 then ' (SQL Errno: ' + convert(varchar(30),@errorNum) + ')' else null end

  select @errmsg
  
  Return -1 
  
End /* prc_set_Persona */
go

IF OBJECT_ID('dbo.prc_set_Persona') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.prc_set_Persona >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.prc_set_Persona >>>'
go

Get an individual entity’s persona#

The get_persona function will return an entity’s persona

def get_persona ( a_entity_type, a_entity_id, a_persona_type):   
    
    my_persona = ''
    
    return my_persona

What is my Persona?#

The my_persona function will determine the persona for an entity

Will start by using a random selection

def whats_my_persona ( a_entity_type, a_entity_id, a_persona_type, a_assignment_method ):   

    
    my_persona = ''

    if a_assignment_method == 'Random' :
    
        if a_persona_type in Personas:
            persona_list = Personas[a_persona_type]
            ndx = random.randint(1, len(persona_list)) - 1       
            my_persona = persona_list[ndx]
                             

    return my_persona

Assign Personas#

The assign_personas function will loop thru all the members of a specific Hallux entity and assign them personas

This is where the hard work is done!

def assign_personas ( a_entity_type, a_persona_type, a_assignment_method, a_status ):   

    
    ret_val = a_status
    
    if a_status not in ('Completed','Under Construction') :
      
    
        # get all the entity ids

        if a_entity_type in Entities:

            entity_list = Entities[a_entity_type]
            sql_select = 'SELECT ' + entity_list[1] + ' FROM ' + entity_list[0] + ' order by ' + entity_list[1]
            df_entity = pd.read_sql(sql_select, eng)
            df_entity.set_index(entity_list[1])
            ret_val = str(len(df_entity))

        # assign and save persona

        for i in range(len(df_entity)):

            curr_entity_id = df_entity[entity_list[1]][i]
            curr_persona   = whats_my_persona ( a_entity_type, curr_entity_id, a_persona_type, a_assignment_method )

            if curr_persona != '' :
                set_persona ( a_entity_type, curr_entity_id, a_persona_type, curr_persona )   

        conn.commit()
        
        ret_val = str(len(df_entity))
        

    return ret_val

Now that the Personas have been assigned we can start Simulating data…#

Let’s start with the new tables in Hallux that support social media#

../../_images/social_media_tables.png

Simulate Follower Data#

This fuction will determine which bands the specified customer profile will follow, if any

def sim_follower_data (a_start_date, a_end_date):   
    
    ret_val = 'Under Construction'
    return ret_val

Simulate Playlist Data#

This fuction will determine which playlists the specified customer profile will create, if any

def sim_playlist_data (a_start_date, a_end_date):   
    
    ret_val = 'Under Construction'
    return ret_val

Simuate Streaming Data#

This fuction will determine which songs the specified customer profile will stream, if any

def sim_streaming_data (a_start_date, a_end_date):   
    
    ret_val = 'Under Construction'
    return ret_val

Simuate Data#

This fuction calls the appropriate simulation function based on the entitity type and status

def sim_data ( a_entity_type, a_start_date, a_end_date, a_status ):   

    ret_val = a_status
    
    if a_status not in ('Completed','Under Construction') :
    
    
        if a_entity_type == 'Follower':
            ret_val = sim_follower_data (a_start_date, a_end_date )

        if a_entity_type == 'Playlist':
            ret_val = sim_playlist_data (a_start_date, a_end_date )

        if a_entity_type == 'Stream':
            ret_val = sim_streaming_data (a_start_date, a_end_date )
        
    
    return ret_val

Here we go…#

  • Initalization, database engine

  • Assign personas to Hallux entities

  • Generate the data for the specified date range!

This section of code loops thru the different entities and calls the appropriate functions to assign personas and/or simulated data. The persona and simulation status columns in the Entities dictionary are used to determine whether or not an action should be performed. Currently, there is only a random persona assignment logic and no data simulation logic.

hallux_svr = getenv("halluxsvr")
hallux_usr = getenv("halluxusr")
hallux_psd = getenv("halluxpsd")
hallux_db  = getenv("halluxdb")

# for use in creating a dataframe
conn_string = f"mssql+pyodbc://{hallux_usr}:{hallux_psd}@{hallux_svr}/{hallux_db}?driver=SQL+Server+Native+Client+11.0&TrustServerCertificate=yes"
eng = create_engine(conn_string, fast_executemany=True)

# used for executing dynamic sql
conn   = pymssql.connect(hallux_svr, hallux_usr, hallux_psd, hallux_db)
cursor = conn.cursor(as_dict=True)

# loop thru the entities and assign the appropriate persona

start_date = date(2023, 1, 1)
end_date   = date(2023, 12, 31)  

for curr_entity, curr_value in Entities.items():

    print(curr_entity)
    
    ret_per = assign_personas (curr_entity, curr_value[2], curr_value[3], curr_value[4])
    ret_sim = sim_data (curr_entity, start_date, end_date, curr_value[5])
    
    print('   Personas:   ',ret_per)
    print('   Simulation: ', ret_sim )


conn.close()
eng.dispose()

Genre
   Personas:    Under Construction
   Simulation:  Under Construction
Member
   Personas:    1415
   Simulation:  Under Construction
Band
   Personas:    Under Construction
   Simulation:  Under Construction
Agent
   Personas:    Under Construction
   Simulation:  Under Construction
Album
   Personas:    Under Construction
   Simulation:  Under Construction
Song
   Personas:    Under Construction
   Simulation:  Under Construction
Performance
   Personas:    Under Construction
   Simulation:  Under Construction
Producer
   Personas:    Under Construction
   Simulation:  Under Construction
Video
   Personas:    Under Construction
   Simulation:  Under Construction
Profile
   Personas:    Under Construction
   Simulation:  Under Construction
Customer
   Personas:    Under Construction
   Simulation:  Under Construction
Follower
   Personas:    Under Construction
   Simulation:  Under Construction
Playlist
   Personas:    Under Construction
   Simulation:  Under Construction
Order
   Personas:    Under Construction
   Simulation:  Under Construction
Stream
   Personas:    Under Construction
   Simulation:  Under Construction