Creating an OMS Profile Score Report

Step 3: Understand the Database Structure

To ensure that you retrieve the right data from the analytics database, you need to create a SQL query that joins together multiple tables and returns the records you need. Before attempting this it is advisable that you become more familiar with the database, particularly the tables you intend to use in your report.

The OMS Analytics database contains 27 tables. I won’t try to describe all of the tables in this post. I’ll just focus on the ones you need to create this report.

Table Name	Description	Columns used in this Report
Session	The session table is central to all queries related to site visits.Every visit generates a session id.For example, to retrieve information about each site visitor you must create a link between the IP and IPOwner tables.	GlobalSessionId, SessionIdTo retrieve the visitor session and global session information.
Global Session	A cookie placed in the visitor’s browser detects a global session. Each organization has one global session and many sessions. Global session enable us to identify returning users.
IP	Stores the IP address of the visitor. Link the IP table and the IPOwner table to retrieve more specific information about the site visitor.
IPOwner	Provides more specific details related to the IP address such as name, country and visitor identification.	IPOwnerId, Name, Country
Profile	The Profile table is the link between the visitor session and the visitor profile information. It also contains the total score for all profile key groups. Link the Session table and the Profile table using the SessionId key.
ProfileKey	Profile Key stores the assigned profile key scores. Use this table as the link between the Profile and ProfileKeyDefinition tables. ProfileId and ProfileKeyDefinitionId are the keys you need to create this link.	Value – Total profile key score
ProfileKeyDefinition	This table contains the names of the profile keys you want to retrieve.	Name – Name of visitor organization

The diagram below, taken from SQL Server Management Studio, shows these tables. It shows the tables, columns, primary and foreign keys and relationships between the tables. You may find it useful to print this out so you can check your links as you build the query.

 

My next post: Step 4 – Create a Data Source