AudienceDB Data Guide

AudienceDB Data Guide

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

This article is a guide to the data available within the AudienceDB offering of Tealium DataAccess.

This article covers the following topics:

Table of Contents Placeholder

If you are new to AudienceDB, review the basics in Working with AudienceDB and EventDB.

How It Works

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.

Attributes and Column Names

Each visit and visitor attribute 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

Additional Resources