AudienceDB

AudienceDB

by akshata_yerdoor on ‎10-14-2015 02:20 PM - edited a week ago by Community Manager (3,006 Views)

This article describes how to access and use the AudienceDB offering of Tealium DataAccess and AudienceStream.

This article covers the following topics:

Table of Contents Placeholder

To use AudienceDB, the service must be enabled for your profile. Contact your account manager for more information.

How It Works

AudienceDB is a profile-level feature in the Universal Data Hub (UDH) that can be enabled for each profile in your account. AudienceDB stores both visit-level and visitor-level data to a Postgres-like database (Amazon Redshift™) where you can query and analyze the data directly using your preferred SQL client or Business Intelligence (BI) tool.

When AudienceDB is activated, a database is created in Amazon Redshift™ to store your AudienceStream data. The new database contains a table for each data type that it can store. Data associated with visit-level data is stored in tables prefixed with visit_. Data associated with visitor-level data is stored in tables prefixed with visitor_. In addition to the tables, several views are also created to make it easier to write queries.

Tables, Views and Normalized Views

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_5099". 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().

Attributes and Column Names

Each visit and visitor attribute created in your account that is enabled for AudienceDB will appear as a column in one or more of the database tables. The following is a list of the attribute data types and the corresponding naming convention of the columns where "###" represent the attribute ID.

These examples are for visitor attributes. Visit attributes will have the word "visit" in place of "visitor".

Attribute Data Type Table Column Name
Array
  • Table:
    "array_###"
  • View:
    "visitor array - array_name (###)"
  • Normalized:
    "visitor array - array_name"
Audience
Values "t"rue and "f"alse
indicate presence in audience.
  • Table:
    "audience_account_profile_###"
  • View:
    "visitor - audience - audience_name (account_profile_###)"
  • Normalized:
    "visitor - audience - audience_name"
Badge
Values "t"rue and "f"alse
indicate presence of badge.
  • Table:
    "badge_###"
  • View:
    "visitor - badge - badge_name (###)"
  • Normalized:
    "visitor - badge - badge_name"
Boolean
  • Table:
    "flag_###"
  • View:
    "visitor - flag - boolean_name (###)"
  • Normalized:
    "visitor - flag - boolean_name"
Date
  •  Table:
    "date_###"
  • View:
    "visitor - date - date_name (###)"
  • Normalized:
    "visitor - date - date_name"
Number
  • Table:
    "metric_###"
  • View:
    "visitor - metric - metric_name (###)"
  • Normalized:
    "visitor - metric - metric_name"
Set of Strings
  • Table:
    "list_###"
  • View:
    "visitor list - set_name (###)"
  • Normalized:
    "visitor list - set_name"
String
  • Table:
    "property_###"
  • View:
    "visitor - property - property_name (###)"
  • Normalized:
    "visitor - property - property_name"
Tally
  • Table:
    "visitor_tally_###_key"
    "visitor_tally_###_value"
  • View:
    "visitor tally - tally_name (###) - key"
    "visitor tally - tally_name (###) - value"
  • Normalized:
    "visitor tally - tally_name - key"
    "visitor tally - tally_name - value"

AudienceDB Tables

The following describes the AudienceDB table types used for audience data and the name of the corresponding "view" and "normalized" tables: 

Data Type and Description Table/View/Noramlized Name
Arrays
Each item in the array is a row in the table with an additional column named index for the zero-based array position.
visit_arrays
visit_arrays_view
visitor_arrays
visitor_arrays_view
visitor_arrays_view_normalized
Set of Strings
Each item in the set is a row in the table.
visit_lists
visit_lists_view
visitor_lists
visitor_lists_view
visitor_lists_view_normalized
Tally
Each item in the tally is a row in the table with one column for the key (suffix _key) and one column for the value (suffix _value).
visit_tallies
visit_tallies_view
visitor_tallies
visitor_tallies_view
visitor_tallies_view_normalized
Stitched Visitors
Visitor IDs that are stitched in the profile as part of visitor stitching.
visitor_replaces
visitor_replaces_view
Visits
Current visit attributes and any audiences they belong to.
visits
visits_view
Visitors
Visitor attributes and any audiences they belong to.
visitors
visitors_view
visitors_view_normalized
Visitor Batches For internal use only

Database Sample Structures

The following sections provide sample structure examples for each view to assist in determining what is unique about each view and how the view differs from other views.

Visit Arrays

The following example shows the the basic formatting for the visit_arrays table:

 visit_id                          | index | updated                 | visit_array_421
----------------------------------+--------------------------------------------------------------------------
13e1a63890793caa346f90607a76c1c98 | 0 | 2018-05-17 01:03:30.344 | Smartphone
13e1a63890793caa346f90607a76c1c98 | 1 | 2018-05-17 01:03:30.344 | Phone Charger
13e1a63890793caa346f90607a76c1c98 | 2 | 2018-05-17 01:03:30.344 | Smartphone Case

Visit Lists

The following example shows the the basic formatting for the visit_lists table:

myexample=# select visit_id, updated, visit_list_284 from visit_lists;

 visit_id | updated | visit_list_284
----------------------------------+-------------------------------------------------------------------------
13e1a63890793caa346f90607a76c1c98 | 2018-04-22 12:50:20.471 | Cell Phones & Accessories
13e1a63890793caa346f90607a76c1c98 | 2018-04-22 12:50:20.471 | Computers and Tablets
13e1a63890793caa346f90607a76c1c98 | 2018-04-22 12:50:20.471 | Office Supplies

Visit Tallies

The following example shows the the basic formatting for the visitor_tallies table:

myexample=# select visit_id, updated, visit_tally_5144_key, visit_tally_5144_value from visit_tallies;

visit_id | updated | visit_tally_5144_key | visit_tally_5144_value
-----------------------------------------------------------------+-------------------------+----------------------+-----------------------
19fd8716d2f8341b81f84f471b5f950873d5c88acee9c61089f286fb8b5d4903 | 2017-09-04 20:39:05.303 | Furniture | 2
06db172cf2a8fd7f9ff882a28a14ad266ee67824c9bc3ee0b1fcc451b42cec68 | 2017-09-05 06:20:16.209 | Furniture | 14
162e22ba6c168bff2385bcfba9d4ba8e15767d1ad8b519b3a872a2ad89d3f3dd | 2017-09-05 06:04:59.671 | Search | 2
4225575ce21a7f9454c56c269eccfee9782e03c6f647a743f058b7b667dd3bbb | 2017-09-20 06:30:14.63 | Home | 1
e1e5dd5e58bc97056f8340e242205e1ec2ab0a88c94c890563399f55828638f7 | 2017-09-09 14:22:08.575 | Furniture | 3
(5 rows)

Visitor Arrays View

The following example shows the the basic formatting for the visitor_arrays_view table:

myexample=# select "visitor - id", "index", "updated", "visitor array - cart product name (421)" from visitor_arrays_view;
"visitor array - visitor id" | index | updated | "visitor array - cart product name (421)"
----------------------------------+--------------------------------------------------------------------------
13e1a63890793caa346f90607a76c1c98 | 0 | 2018-05-17 01:03:30.344 | Smartphone
13e1a63890793caa346f90607a76c1c98 | 1 | 2018-05-17 01:03:30.344 | Phone Charger
13e1a63890793caa346f90607a76c1c98 | 2 | 2018-05-17 01:03:30.344 | Smartphone Case

Visitor Lists

The following example shows the the basic formatting for the visitor_lists table: 

myexample=# select visitor_id, updated, visitor_list_5168 from visitor_lists;
visitor_id | updated | visitor_list_5168 -----------------------------------------------+-------------------------+------------------- 015e94db670900084e37016b9b7300087002f07f00432 | 2017-11-04 13:30:33.553 | Cell Phones
015e94db670900084e37016b9b7300087002f07f00432 | 2017-11-04 13:30:33.553 | Phone Accessories
015e94db670900084e37016b9b7300087002f07f00432 | 2017-11-04 13:30:33.553 | Office Supplies

Visitor Replaces

The following example shows the the basic formatting for the visitor_replaces table:

myexample=# select visitor_replaces_id, visitor_id, updated from visitor_replaces where updated is not null;

visitor_replaces_id | visitor_id                                      | updated
----------------------------------------------+-------------------------------------------------+------------------------
015f8d82498b00132b921ecf890d00089001c08100432 | myexample_main__5216_username@gmail.com      | 2017-11-23 15:32:28.81
015f4a0302b9009e17205a49027005079001c07100c48 | myexample_main__5216_username@myexample.com   | 2017-11-24 22:50:31.105
015feb1537cc00305e319fca622400085001d07d00720 | myexample_main__5216_username@yahoo.com  | 2017-11-26 01:22:27.57
015decf4f3170011afb0979834420007e007b07600720 | myexample_main__5216_username@hotmail.com       | 2018-01-03 22:03:20.837
015e3b903e48000c7944f6af4b8e00087016907f0049e | myexample_main__5216_username@cox.net        | 2018-01-28 03:59:45.031
(5 rows)

Visitor Tallies

The following example shows the the basic formatting for the visitor_tallies table:

myexample=# select visitor_id, updated, visitor_tally_57_key, visitor_tally_57_value from visitor_tallies;
visitor_id | updated | visitor_tally_57_key | visitor_tally_57_value ----------------------------------------------+-------------------------+----------------------+------------------------ 015dcd068996007c1f95a24aa47002075008d06d0093c | 2017-09-01 03:14:18.519 | Safari | 8 015e40b27e55004ec842e2b9d0f800090001c08800408 | 2017-09-02 13:35:40.834 | Chrome | 2 015e34c9fd270014a6799597769a00088008808000408 | 2017-08-31 05:44:14.013 | Chrome | 1 (5 rows)

Visitors View

The following example shows the the basic formatting for the visitors_view table:

select "visitor - id", "visitor - created", "updated", "visitor - audience - bedroom shoppers (myexample_main_102)" from visitors_view limit 1;

                 visitor - id                  |  visitor - created  |        updated         | visitor - audience - bedroom shoppers (myexample_main_102)
-----------------------------------------------+---------------------+------------------------+---------------------------------------------------------------
 015de32cbb1e00265733c8c3c5bc00080001c07800976 | 2017-08-14 23:55:46 | 2017-08-20 15:27:48.75 | f
(1 rows)

Visits Table

The following example shows the the basic formatting for the visits table:

myexample=# select visit_id, visitor_id, start_time, last_event_time, updated, property_5300, flag_5432 from visits limit 5;

visit_id | visitor_id |     start_time | last_event_time  | updated | property_5300 | flag_5432
-----------------------------------------------------------------+-----------------------------------------------+---------------------+---------------------+-------------------------+---------------+-----------
4af5f070998b6e471d05a809d55c62811784c57ee196c39a599c0a352e925e01 | 015c9e257f220048ebb26bfdb68405072001c06a00bd0 | 2017-07-18 17:04:31 | 2017-07-18 17:39:27 | 2017-07-18 18:09:28.994 |               |
42fd5d3067b5472a57aa7fe6e8cf12d5d60ea2a9399d54de01bfb0b06e33bd73 | 015cc166a9b100028f7070a6e51d01049003e00d00bd0 | 2017-07-18 21:10:21 | 2017-07-18 21:13:32 | 2017-07-18 21:43:32.948 |               |
9729e709ec80bc476dc6c36c6bf33f998f92c3f2e3af5509078c6402ceff29bf | 015d56a5ec3d00128011a73f446404079001c07100838 | 2017-07-19 18:28:26 | 2017-07-19 18:28:26 | 2017-07-19 18:38:27.903 |               |
6d1c801fb894234347d27a61b9b0f82ac918bfdc36a0510ab3be8ea6a72b9038 | 015c662062aa00474d60cc4f7b4005072001c06a00bd0 | 2017-07-19 19:30:08 | 2017-07-19 19:30:08 | 2017-07-19 19:40:12.802 |               |
c97a0a0c9dfc4cb58e23dbfdb98528cddc8a989c9d65f0e6fd89ffdc5e82c727 | 015c662062aa00474d60cc4f7b4005072001c06a00bd0 | 2017-07-19 21:33:00 | 2017-07-19 21:33:00 | 2017-07-19 21:43:02.803 |               |
(5 rows) 

Understanding Stitched Visitors

In AudienceDB, only one visitor profile is maintained in the visitors table for visitors that become stitched. The visitor profiles for stitched visitors are viewed as one, but with different IDs. The visitor_replaces table provides a lookup method to view the replaced visitor_ids to accomplish this.

Visitor Replaces table example:

 visitor_replaces_id  visitor_id
1 3
2 3
4 6
5 6

In this table:

  • Visitor 1 and Visitor 2 are stitched into Visitor 3.
  • Visitor 4 and Visitor 5 are stitched into Visitor 6.

The above information can be used to join the visitors table to the visits table or the EventDB tables, if needed. 

To join events from EventDB to AudienceDB:

  • Join events_x directly to the visitors table for non-stitched visitors, or
  • Join through the visitor_replaces table for those that have been stitched, as shown in the following example:
    SELECT e."event - id", v."visit - id", v."visit - visitor id"
    FROM account__profile.events_view__all_events__all_events e
    JOIN account__profile.visits_view v ON e."event - visitor id" = v."visit - visitor id"
    WHERE v."visit - visitor id" = e."event - visitor id"
    AND e."event - time" >= v."visit - start time"
    AND e."event - time" <= v."visit - last event time"
    ORDER BY v."visit - id";
  • This example accounts for visitor IDs in events_all_events being in visitors OR  visitor_replaces.

    Visitors attributed to single event sessions will not be stored in the visitors table.

EventDB and AudienceDB Diagram

Event, visitor, and visit attributes work together in EventDB and AudienceDB. The following diagram illustrates the relationships between the EventDB and AudienceDB attributes. These relationships are important when writing queries that return event data for a specific visit or visitor.

EventDB and AudienceDB Diagram Update for TLC.jpg

Make an Attribute Available in AudienceDB

Use the following steps to enable an attribute to be store in AudienceDB:

  1. Navigate to Audience Stream (AS) > Attributes.
  2. Select an attribute to edit and click the pencil icon on the upper right to edit the properties.
  3. Click the AudienceDB checkbox to store the attribute in AudienceDB.
    AudienceDB_002a.png

    This checkbox is not available for Preloaded attributes.

  4. Click Save.
  5. Save/Publish your profile.
    After you save and publish, AudienceStream compiles a list of attributes included in AudienceDB.

View List of AudienceDB Attributes

Use the following steps to view the list of AudienceDB-enabled attributes:

  1. Navigate to DataAccess (DA) > AudienceDB.
  2. Click Show AudienceDB Attributes.
    The AudienceDB attributes display.
  3. Scroll up or down the list to find an attribute.
  4. To jump to an attribute to see its details, click the arrow to the right of the attribute name.
    AudienceDB_Show AudienceDB Attributes.jpg

Get Database Connection Credentials

AudienceDB credentials are unique to the profile where the setting is enabled. Regenerating credentials in a profile will not change the credentials for other AudienceDB-enabled profiles. Only one set of credentials is issued per profile at any given time. Regenerating credentials will overwrite the previous credentials, as indicated in the warning message in the screenshot below.

Use the following steps to retrieve your database connection credentials:

  1. Navigate to DataAccess (DA) > AudienceDB.
  2. Click Get DB Connection Details.
    The DB Connections Details dialog displays.
    AudienceDB_005.png
  3. Make a note of the username, database, host, and port details as they are required to access your Amazon Redshift™ account.
  4. Click Regenerate DB Credentials to generate new credentials.

    Ensure that you want to regenerate and remember that regenerating DB credentials may break existing integrations.

  5. From the confirmation screen, click Yes.
  6. Ensure that you make a note of the password, as passwords are not stored in the system.
  7. Click Close.

Additional Resources