AudienceDB and EventDB

[#1]

This article describes how to work with the Tealium DataAccess AudienceDB and EventDB.

In this article:

• How It Works
• Prerequisites
• Tables, Views and Normalized Views
• Visit/Visitor Data
• AudienceDB Tables
• Event Data
• EventDB Tables
• Writing SQL Queries
• Enabling Data Storage
• Adjusting Preloaded EventDB Attributes
• Adjusting AudienceDB Attributes
• Database Credentials
• Getting Database Authentication Credentials

How It Works

AudienceDB and EventDB services are used to store structured audience and event data in a Postgres-like database (Amazon Redshift). From Amazon Redshift, you can then query and analyze the data directly using your preferred SQL client or Business Intelligence (BI) tool.

Prerequisites

AudienceDB and EventDB must be activated for your account. For additional information, contact your account manager.

Tables, Views and Normalized Views

When a service is activated, a database is created in Amazon Redshift to store your data. The columns in the tables are named according to the attribute type and the internal attribute ID. For example, if you have a badge attribute with an internal ID of "30", the table will contain a column named badge_30. Views and normalized views of data are created to make it easier to write queries. The normalized view is similar to the view but with the attribute ID omitted from the field name.

• Tables
  Standard tables have columns named after the attribute type and the attribute ID.
  Example: badge_30
• Views
  Table names appended with _view have user-friendly column names with attribute IDs.
  Example: visitor - badge - fan (30)
• Normalized Views
  Table names appended with _view_normalized have user-friendly column names without the attribute IDs.
  Example: visitor - badge - fan

The views also simplify the process of running queries with aggregations such as SUM() , MIN(), and MAX().

Visit/Visitor Data

Visit and visitor attributes are stored in database table columns according to their attribute type and name. Each table is keyed using a visit_id or visitor_id. Database views are created for each table to make it easier to write queries.

AudienceDB Tables

The following tables are available for visit and visitor data:

• Visit/Session Data: visits
• Visitor Data: visitors

In addition, the following tables exist for special attribute types:

• Arrays: visit_arrays, visitor_arrays
• Set of Strings: visit_lists, visitor_lists
• Tally: visit_tallies, visitor_tallies

For detailed information, see the AudienceDB Data Guide.

Event Data

For tables containing event data, event data includes event attributes for all events in the event feed. Table columns are named according to the attribute type and name, with only some attributes referencing internal ID's. Standard Universal Data Object (UDO) variables are named with a udo_ prefix and most column names match their corresponding attribute names, for example: udo_event_name.

Event data coming from the Tealium collect tag also includes information about which tags executed on the page and page performance metrics.

For additional information, see Live Events and Feeds.

EventDB Tables

The following tables are available for event data:

• Event Feed Data: events_{FEED}

Writing SQL Queries

The following articles provide best practices and examples of useful queries:

• Connecting to EventDB and AudienceDB with SQL Workbench/J
• Best Practices for Writing Queries for EventDB and AudienceDB
• Helpful SQL Queries for EventDB and AudienceDB

Enabling Data Storage

• Attributes
  Data storage is controlled at the attribute level. Event attributes and visit/visitor attributes have a checkbox that determines if it will be stored in the database. Simply edit the attribute and toggle the checkbox on or off to include or omit the attribute from the database.
  For additional information, see Using Attributes.
• Audiences
  All audiences are stored in AudienceDB.
• Event Feeds
  Event feeds must be configured for the data to be stored in EventDB.
  
  EventDB also collects preloaded attributes. Although preloaded attributes cannot be edited from the Attributes screen, you can adjust the preloaded attributes that are stored in EventDB using the DataAccess Console.
  

  It is recommended to only enable EventDB for the specific event feeds that you need since the amount of data can become quite large depending on your volume. For additional information, see Live Events and Feeds.

Adjusting Preloaded EventDB Attributes

To adjust which preloaded attributes are stored in EventDB:

1. Go to DataAccess > EventDB.
   
2. Click Show EventDB Attributes.
3. Select or deselect attributes to add or remove them from EventDB.
   All all attributes are checked by default.
   
   

   DOM attributes, such as URL, domain, referrer, and user agent are always sent and cannot be excluded.

4. Click Save.
5. Save and Publish your changes.

Adjusting AudienceDB Attributes

To adjust which visitor attributes are stored in AudienceDB:

1. Go to DataAccess > AudienceDB.
   
2. Click Show AudienceDB Attributes.
3. Select or deselect attributes to add or remove them from AudienceDB.
   
4. Click Save.
5. Save and Publish your changes.

Database Credentials

You must use a third-party tool with Postgres support to connect to your database. These tools require authentication credentials to connect, which are provided in the DataAccess Console.

Database Credentials are now generated for each user. Previously, credentials were generated for an account and profile and were shared by all users. If someone regenerated global credentials, all user connections were terminated, and all users had to reconnect. 

For user-specific credentials, the generated credentials are based on the account, profile, and the user's email address. Users can regenerate their own credentials without terminating other connections. You can remove access for a specific user without terminating other connections. To deactivate a specific user's credentials, contact Tealium Support.

Previously generated global credentials can still be used, but cannot be regenerated.

Getting Database Authentication Credentials

Use the following steps to get the database authentication credentials:

1. In the sidebar, click DataAccess > EventDB or DataAccess > AudienceDB.
2. Click Get DB Connection Details.
3. Click Regenerate DB Credentials. You need to regenerate credentials even if this is your first time getting credentials. 
   
4. Click Yes to confirm that you want to delete your existing credentials and generate new ones.
   The DB Connection Details screen displays the following fields:
   • Username
     The username for the database connection, which is a combination of your account, profile names, and your email address. For example, account__profile__email.
   • Password
     The password for the database connection.
   • Database
     The name of the database, usually the name of your account.
   • Host
     The host name of the database server, specific to your data storage region.
   • Port
     The port number for the connection.
5. Save the connection details for future use, then click Close.