Live Tap overview

This is for:

Developer

In this article

Getting started
A closer look at business views
What business views are available?
Data layer
Running a query
Connecting to a Business Intelligence (BI) tool
Data dictionary / schema
Live Tap derived measures - semantic model subject areas

Data Exchange Live Tap provides full access to all the data in your data layer through a cloud-based high performance query platform, which combines high speed, low latency, data exploration, and analysis.

Live Tap is powered by Google BigQuery, which delivers unparalleled scale and performance for even the most taxing queries on your largest dataset.

To help you get started with Live Tap, sample content is provided for the Looker and Google Data Studio reporting tools. Alternatively, you can connect your own favorite BI tool or use Live Tap data as a data source for your own applications, scoring models, or customer data warehouse.

Getting started

Connecting to the data warehouse

If your organization has access to Live Tap, we will need a Google Id for each individual that requires access to the Live Tap Google BigQuery data warehouse. Contact Qubit with these Ids and we will request access for your Google Cloud project and organize Live Tap onboarding.

Once you’ve been onboarded with Live Tap access, you should receive an email from Google Cloud inviting you to join a project, which you can do by selecting the link in the email. This is a one-time step and you can logout when done.

To access your data, you can use various tools, including the web interface provided by BigQuery.

Step 1

Navigate to bigquery.cloud.google.com and login with the Google account you provided to Qubit

Step 2

Confirm you’re in your Qubit project by checking the name at the top of the list of datasets on the left. Your Qubit project will be named qubit-x where x is your company name

Finding your Qubit project

If you can’t see your Qubit project, follow these steps:

Step 1

Select the blue down arrow next to the project name:

Step 2

Select Switch to Project and select your Qubit project from the list of projects

Within each customer dataset in your Google BigQuery project, there are two categories of tables and views you can access using Live Tap:

Event Tables - a set of BigQuery tables and views with a schema (data model) optimized for real-time ingestion and processing of your event data
Business Views - an abstraction-layer over these event tables, designed for reporting and analysis that simplifies the event table data model, and presents it as a dimensional model more suited for use with business intelligence tools such as Looker and Tableau

A closer look at business views

Business views are a set of BigQuery SQL views designed for use with Live Tap that simplify reporting and analysis tasks by:

Removing columns and tables from the model that aren’t relevant for reporting and analysis work, for example by excluding from the qp_bi_view business view the following ecView event table columns that are either not relevant for reporting, or are not usually populated:
- meta_* fields (for example, meta_batchTs)
- context_* (for example, context_latestViewTs, context_latestSessionTs)
- store_address*
- user_address*
Combining data items from multiple underlying event tables into single views for segments, views, experiences, and other reporting entities, for example by creating the qp_bi_product view that combines together columns from the following event tables:
- ecProduct
- ecBasketItem
- ecBasketItemTransaction
"Baking in" best practice joins and data access paths between these underlying tables to ensure efficient data access and avoid double-counting of event activity.

As an example, we make segments easily accessible by joining segment and view events from the underlying data layer event tables to create the qp_bi_segment business view. This contains one row for every view in the context of segment the user is in at a particular point in time.

Business Views are available to all Live Tap customers and are automatically provisioned by Qubit using the most appropriate industry model for the customer.

Each business view contains measures and dimension attributes relating to a particular subject area and additional metadata to describe the purpose of each view column. Views can be joined together for cross-subject area reporting.

For example, by combining a Conversion Rate view with an average transaction value view we can report on an experience broken down by segment.

What business views are available?

The core set of business views covers the common set of metrics, subject areas, and dimensions common to all verticals supported on the Qubit platform. These core business views may be extended and enhanced from time-to-time to include new metrics and subject areas.

You can still directly query the underlying event tables if a column or subject area you require is not yet present in the business views.

The following core business views are available for all Qubit customers that have Live Tap data access:

Views
Sessions
Entrances
Transactions
Segments Membership
Experiences
Goal Achieved
Attribution
Visitor pulse

In addition, three industry-specific product business views are provided for ecommerce, Travel, and eGaming customers:

Product interaction and sales for ecommerce industry customers
Package interaction and sales for travel industry customers
Bet interaction and sales for e-Gaming industry customers

When brought together, these core views plus any industry-specific ones make up the Industry Business Models.

Ecommerce industry business model

The following table provides the underlying BigQuery object name, key columns and contents for the core business views:

Name BigQuery View Key(s) Contents

Name	BigQuery View	Key(s)	Contents
Views	`view`	`view_id`	All visitor page and view events, the base "event" to which all other events are linked
Product Interaction and Sales	`product`	`view_id`, `product_id`	Views where products were displayed, interacted with, or purchased
Segment Membership	`segment`	`view_id`,`segmentId`	Views that happened on or after a visitor entered a segment
Experience	`experience`	`view_id, experienceId`, `goalId`	- Views that happened on or after a visitor saw an experience * If you wish to work with just the views that had the experience delivered on them, use `is_post_experience_view` to filter rows that are `false`
Transactions	`transaction`	`transaction_id`	Details every transaction
Sessions	`session`	`session_id`	Details every session for users
Entrances	`entrance`	`entrance_id`	Details every entrance for users
Goal Achieved	`goal_achieved_views`	`view_id`,`goalId`	- Contains all views after the goal was achieved * If `is_post_goal_achieved_view` is `FALSE`, goal was achieved on the current `view_id` and if `TRUE` the goal was achieved previously
Attribution	`attribution`	`Session_id`, `entrance_id`	Entrances which took place before the visitor completed a transaction
Visitor pulse	`visitor_pulse`	`View_id`, `question_id`	Details visitor survey results

Views

view

view_id

All visitor page and view events, the base "event" to which all other events are linked

Product Interaction and Sales

product

view_id, product_id

Views where products were displayed, interacted with, or purchased

Segment Membership

segment

view_id,segmentId

Views that happened on or after a visitor entered a segment

Experience

experience

view_id, experienceId, goalId

- Views that happened on or after a visitor saw an experience

* If you wish to work with just the views that had the experience delivered on them, use is_post_experience_view to filter rows that are false

Transactions

transaction

transaction_id

Details every transaction

Sessions

session

session_id

Details every session for users

Entrances

entrance

entrance_id

Details every entrance for users

Goal Achieved

goal_achieved_views

view_id,goalId

- Contains all views after the goal was achieved

* If is_post_goal_achieved_view is FALSE, goal was achieved on the current view_id and if TRUE the goal was achieved previously

Attribution

attribution

Session_id, entrance_id

Entrances which took place before the visitor completed a transaction

Visitor pulse

visitor_pulse

View_id, question_id

Details visitor survey results

In addition, two more views, qp_bi_all_v01 and qp_bi_all_exp_seen_context_v01, are provided specifically for use with the Google Data Studio BI tool.

For more information on using Live Tap as a data source for Google Data Studio, see Google Data Studio in Connecting to a Business Intelligence (BI) tool.

Attribution business view

Qubit’s attribution model is optimized to run on Qubit’s QProtocol infrastructure and includes a subject area for attribution using SQL expressions to almost entirely replicate the output of the previous ML model.

It provides better attribution weightings than the previous version and is being back-ported on request to current Decipher attribution reports.

Example query:

-- Get attribution values per referer type
SELECT
  true_referrer_type,
  SUM(first_click_value),
  SUM(last_click_value ),
  SUM(behavioural_value)
FROM [qubit-demosites:retail_demo_union_fashion_livetap_v1_1.qp_bi_attribution]
GROUP BY true_referrer_type

See About the Live Tap BI content for Looker semantic model in Connecting to a Business Intelligence (BI) tool for details on how to use this subject area in Live Tap BI Content for Looker.

Visitor pulse business view

Visitor pulse data is part of the Live Tap overall data model. The structure is relatively simple. Each row in the business view represents the given answer, and is supplemented by the question and other attributes.

The logic in this business view ensures that data is deduped and normalized. Each row also contains the answers to other questions in the survey. This enables cross-field filtering. For example, you may search for answers to question 3 only for visitors who had a specific response to question 2 and/or question 1:

Example query:

--For all users who responded to question to 2 as 'Other' - how did they respond to question 3?
SELECT answer FROM
retail_demo_union_fashion_livetap_v1_1.qp_bi_visitor_pulse_v01
WHERE  survey_id = '71773'
   AND user_survey_answer_2 = 'Other' --FILTER ON ANOTHER ANSWER
   AND question_order = 3
GROUP BY answer

See Visitor pulse subject area in Connecting to a Business Intelligence (BI) tool for details on use of this new subject area in Live Tap BI Content for Looker.

In addition to simplifying and restructuring your data layer to make reporting and analysis easier, each of the views deduplicates or otherwise transforms the underlying event table data to make it more suitable for reporting.

The following table shows these data transformations:

BQ Business View Name Transformations

BQ Business View Name	Transformations
`view`	- Deduplicate rows to avoid double-counting, exclude bad rows, omit non-essential columns * Bring in extra fields useful in analysis * Capture views that happened in all QP events thereby turning the business view into a complete power set of views, for example, to capture views that happened in an experience or transaction events that may not have come through to the original `ecView` event
`product`	- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns * Combine in one view all product-related QP events * Enrich basic product data with derived metrics and attributes
`segment`	- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns * Transform the current QP segment event so it’s easy to join on. This allows segment analysis in context of views or anything else * Bring in extra details by joining with aux data (for example, segment name, value)
`experience_v01`	- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns * Bring in essential analysis data such as experience name, goal value * Bring in ‘goal achieved immediately’ fields
`transaction`	- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns
`session_v01`	- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns * Bring in extra fields useful in analysis by using window functions and if statements
`entrance`	- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns * Bring in extra fields useful in analysis (for example, UTM)
`attribution_v01`	- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns
`visitor_pulse`	- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns * Bring in extra fields useful in analysis (for example, answers to other questions by that respondent)

view

- Deduplicate rows to avoid double-counting, exclude bad rows, omit non-essential columns

* Bring in extra fields useful in analysis

* Capture views that happened in all QP events thereby turning the business view into a complete power set of views, for example, to capture views that happened in an experience or transaction events that may not have come through to the original ecView event

product

- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns

* Combine in one view all product-related QP events

* Enrich basic product data with derived metrics and attributes

segment

- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns

* Transform the current QP segment event so it’s easy to join on. This allows segment analysis in context of views or anything else

* Bring in extra details by joining with aux data (for example, segment name, value)

experience_v01

- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns

* Bring in essential analysis data such as experience name, goal value

* Bring in ‘goal achieved immediately’ fields

transaction

- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns

session_v01

- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns

* Bring in extra fields useful in analysis by using window functions and if statements

entrance

- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns

* Bring in extra fields useful in analysis (for example, UTM)

attribution_v01

- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns

visitor_pulse

- Deduplicate rows to avoid double-counting, exclude bad rows, exclude not-needed columns

* Bring in extra fields useful in analysis (for example, answers to other questions by that respondent)

Travel industry business model

The Travel Industry business model is largely based on the e Commerce model but replaces Product Interaction and Sales with Package Interaction and Sales:

Name Contents

Name	Contents
`package_item`	Similar to the Product table offered for retail schemas, this includes details on viewed & purchased package items such as a flight or hotel

package_item

Similar to the Product table offered for retail schemas, this includes details on viewed & purchased package items such as a flight or hotel

eGaming industry business model

Data layer

A data layer contains all the data for one property. In the following example, we can see a set of datasets for a Qubit internal demonstration property, qubit-demosites:

Under each dataset is a list of BigQuery views. There are two types:

Event table and auxiliary data views - names start with event_ec and aux_ respectively and contain raw event-level records and experiences data:

Business Views - names start with qp_bi. Business Views contain the same raw event-level and auxiliary data but organized into more query-friendly table structures with non-relevant columns removed.

Selecting any of these views reveal the view schema, as shown in the following example:

Refer to the Data dictionary / schema for more information.

Ingested datasets

In addition to event tables and business views, custom BigQuery tables may also be created in your data layer.

The following screenshot shows a dataset held in a BigQuery table called topshop.returnsData under the project qubit-client-1234:

This table would be visible using Live Tap and in the BigQuery Web UI and queryable using SQL in the same way as other tables and views in that project. Metadata for this table could be imported into a BI tool along with the Live Tap Business Views if you wanted to include it in reporting and analysis.

Running a query

To run a query, select Compose Query:

In the editor, you can compose SQL statements to access your data as well as setting some options for that query job, such as destination table, query caching, and query priority:

When composing statements, you can also use BigQuery legacy and standard SQL query syntax and functions. See here for more information.

Connecting to a Business Intelligence (BI) tool

Google has a number of partners with native connections to BigQuery. You can find an extended list here. In this section, we will focus on Looker and Google Data Studio.

Looker external

This guide explains how to connect to your Qubit BigQuery data from Looker. This is done in three stages:

Setting up the BigQuery connection in Looker
Forking GitHub Repo
Setting up a project in Looker

Setting up the BigQuery connection in Looker

Before getting started:

Before setting up the BigQuery connection, you’ll need appropriate permissions on Looker to see Admin panel. In addition, you’ll need the following information, which will be provided by Qubit:

Looker Connection Name - for example, client-connection-100000
Qubit Project Name - for example, qubit-client-10000
Qubit Dataset - the name of one of the datasets in your project
Service Account Name - address corresponding to the service account. This will start with qubit-client-bi@qubit-client
P12 Key File - encrypted file needed to authenticate with Google
Service Account Password - password for the service account

Configuring the connection:

Step 1

Select Admin in the upper-right hand corner of the page and then Connections > New Connection

Step 2

Enter the following connection details:

Name - Looker Connection Name
Dialect - Google BigQuery (make sure you do not pick Standard SQL)
Project Name - Qubit Project Name
Dataset - Qubit Dataset (the connection will work for all datasets in the project, not just the one specified)
OAuth Credentials Email - Service Account Name
OAuth Credentials P12 File - upload P12 Key File
Password - Service Account Password
Max Billing Tier - 1

Step 3

Select Test These Settings to ensure the setup works and select Save. The remaining connection settings can be left in their default state.

Forking the GitHub Repo

Before getting started:

Before forking the GitHub repo, you’ll need the link to a read-only GitHub repo holding your configured Live Tap model files. This will be provided by Qubit.

You’ll need to provide Qubit with the GitHub username so we can grant them access permissions to the Live Tap repo. If you don’t have a GitHub account, you’ll need to create one.

Forking the repo

Step 1

Go to the GitHub repo using the link we provided and the account permissioned by Qubit, select Fork and follow the on-screen instructions

This will create your own copy of Qubit repo which you now can modify and integrate with Looker

Step 2

Navigate to repo’s main page and select Clone or download. Take note of the repo URL (for example, git@github.com:user_or_org/project-name.git).

Setting up a project in Looker

Before getting started:

Before completing this final step, ensure that you’ve forked the GitHub repo and that you have repo URL. You’ll also need appropriate permissions on Looker to set up projects.

Setting up a Looker project

Step 1

Select Develop, select the Development Mode toggle and then Develop and Manage LookML Projects

Step 2

Scroll to the bottom of the page and select New LookML Project. Enter a project name. We recommend using the same project same name as the GitHub repo holding your files

Step 3

Select Generate Model & Views and Save. You’ll be taken to the project page. Select Configure Git and enter the repo URL. Select Continue. Looker will now issue a deploy key

Step 4

Select the full content of the key and copy it to your clipboard. Navigate to your repo in GitHub and select Settings. On the resulting page, select Deploy Keys and Add Deploy Key

Step 5

Enter a title for the deploy key and paste the deploy key given by Looker. Select Allow write access and Add key

Step 6

Return to Looker and on the page showing the deploy key, select Continue setup. Looker will now attempt to sync with GitHub repo

Finally, select Sync Development Mode

If successful, the response will be Up to date with Production. This means that all permissioned users should be able to see the Live Tap model on your Looker instance.

Looker internal

Looker has a database connection to Google BigQuery, and requires a key file to be provisioned. Contact your Qubit Strategy Consultant to get your Looker instance connected to Qubit.

Customers who have Looker can make use of a semantic model, example reports ("Looks") and dashboards that can be customized and extended by the customer as required. These are detailed later in this section.

Setting up Live Tap on Looker on your own Looker instance

Step 1

Fork the livetap-looker-templates GitHub repository using the following naming convention livetap-client-clientFriendlyName

Step 2

Setup a new Looker project, de-selecting the option to automatically generate model and views

Step 3

Create a new database connection in Looker. The connection name should be the same as the BigQuery project Id, for example `qubit-client-9393`.

Select Persistent Derived Tables
Set Temp Dataset to qubit_cs_bi_cache
In the new Looker project, configure git, pointing to the repository forked in point 1
Synchronize with production
Replace all tracking_id instances in file names. You can do this manually under Looker’s Develop or in GitHub
Synchronize once again with production
Use the Find & Replace in Project option to change all instances of {TRACKING_ID} in the new project. 233 occurences in 15 files
Use the Find & Replace in Project option to change all instance of {PROJECT_ID} in the new project. 5 occurences in 4 files..
Select Validate LookML, then commit and push to GitHub
In BigQuery’s client project create a new dataset in BigQuery called qubit_cs_bi_cache
If you use a service account to connect to Looker, ensure it has editor permissions to the project

About the Live Tap BI content for Looker semantic model

Intro

The Live Tap BI Content for Looker repo contains views and a Looker model to define a semantic model over the Live Tap Business Views, along with some example reports Looks and dashboards that you can use to create a joined-up, user-friendly analysis environment in the Looker BI tool:

As shown above, this example semantic model joins together all of the Business Views and presents the contents in the form of subject areas, measures, and dimensions, giving users of the Looker BI tool a point-and-click, "friction-free" analysis environment.

This can be extended and used as a starting point to create your own customized Looker project.

Semantic model subject areas

The following table lists the subject areas in the Looker model along with the corresponding business views:

Subject Area Contents Source Business Views

Subject Area	Contents	Source Business Views
Entrances	Views that arrived first in visitors’s entrance	`qp_bi_entrance`
Atrribution	A collection of entries which happened before the visitor completed the transaction.	`qp_bi_attribution`
Visitor pulse	Survey responses	`qp_bi_visitor_pulse`
Experience	Views that happened on or after a visitor saw an experience along with the goals for that experience Can be filtered to show only those views on which the experience occurred	`qp_bi_visitor_first_seen_experience` `qp_bi_post_goal_achieved_views` `qp_bi_transaction`
Product Interaction and Sales	Views where products were displayed, interacted with, or purchased	`qp_bi_product`
Segment Membership	Views that happened on or after a visitor entered a segment	`qp_bi_segment` `qp_bi_transaction`
Sessions	Views that arrived first in user’s session	`qp_bi_session`
Transactions	Views on which purchase happened	`qp_bi_transaction` `qp_bi_view`
Views	All Visitor view events	`qp_bi_view` `qp_bi_visitor_first_seen_experience`

Entrances

Views that arrived first in visitors’s entrance

qp_bi_entrance

Atrribution

A collection of entries which happened before the visitor completed the transaction.

qp_bi_attribution

Visitor pulse

Survey responses

qp_bi_visitor_pulse

Experience

Views that happened on or after a visitor saw an experience along with the goals for that experience Can be filtered to show only those views on which the experience occurred

qp_bi_visitor_first_seen_experience qp_bi_post_goal_achieved_views qp_bi_transaction

Product Interaction and Sales

Views where products were displayed, interacted with, or purchased

qp_bi_product

Segment Membership

Views that happened on or after a visitor entered a segment

qp_bi_segment qp_bi_transaction

Sessions

Views that arrived first in user’s session

qp_bi_session

Transactions

Views on which purchase happened

qp_bi_transaction qp_bi_view

Views

All Visitor view events

qp_bi_view qp_bi_visitor_first_seen_experience

Attribution subject area

Attribution on its own or combined with other subject areas allows for analysis of visitor’s activity before they converted and how each part of the activity might have influenced the user to make the purchase. Entry is a most centric concept to attribution and essentially this is what forms every row in the underlying business view.

The key characteristics of an entry are:

Entry Referrer group of dimensions - tells us how the visitor landed on the site for each visit, that is, marketing channel, campaign, referrer URL, etc. These reside in the ‘Entry Referrer’ group of dimensions
Views in Attribution Entry dimension - indicates how many views there were in an entry. The more views an entry had, the more impact it probably had on the purchase. You could combine this with Page Type (from View subject area) to see categories of pages the visitor looked at before converting. The same applies for combining with Product to inspect the products that the visitor looked at before converting, etc
Additional attributes of Entry:
- Entry Number - consecutive number incrementing on each new entry, and always starting from 1. Combined with other fields, could answer various questions (for example, which channel is usually the first to refer users to site)
- Touchpoint - classification of entries based on Entry Number. It has following values:
  - Single entry visitor had just one entry in that purchase cycle before they converted
  - First - tag for first entry in a purchase cycle, in a sequence of 2 entries or more
  - Last - tag for last entry in a purchase cycle, in a sequence of 2 entries or more
  - Assist - represents an intermediate entry in a purchase cycle (obviously - which was not the first or last one)
Entrance and Session Number - correspond to the actual context session and entrance numbers in QP

Naturally, some dimensions repeat themselves across entries:

Purchase Cycle - designates the number of user purchases (completed in separate sessions). If user makes several purchases in the same session - for the purpose of attribution, they would fall into the same Purchase Cycle

All transactions which happened in the same session are treated as a single attribution order - their totals are summed and order IDs stitched together.
Purchase Cycle Group - corresponds to Purchase Cycle (above) for values 1- 4. Values 5 or above set to ‘5+’. We have seen that the majority of purchases falls into the former group
Visitor ID
Order IDs in Purchase Cycle
Order Time

Measures describe properties of an entry or an order:

Measures describing ‘Entry’
- Attribution can provide insights on how valuable each entry might have been. One way to express this ‘value’ is in a monetary sense:
  - First Click Value - full order value is only attributed to first entry in sequence, that is, where Touchpoint = ‘First’ or ‘Single’ and will be 0.00 for all
  - Last Click Value - full order value is only attributed to last entry in sequence, that is, where Touchpoint = ‘Last’ or ‘Single’
  - Even Click Value - full order value divided by number of all entries user had in that purchase cycle
  - Behavioral Value - the amount of revenue proportional to the number of views a visitor had in that entry had
Measures describing ‘Order’. Attribution order is all transactions completed by visitor in the same session:
- Order Count - returns the number of ‘attribution orders’
- Conversion Value - summed transactional total (basket total)

Points to note:

Reconciling revenue figures in attribution vs. actual transactions may show some discrepancies but they should stay within 0.01%. While order count may be slightly different due to reasons mentioned previously, revenue totals should stay very close
It’s theoretically possible to combine attribution with any of the other subject areas, however some combinations may not be 100% effective:
- Combining with Views, Sessions, and Entrances can enrich the data with details such as device type, browser, OS, extra referrer details, UTM, etc. These should work with no additional restrictions
- Due to the way the Looker model joins business views, combining Attribution with Transaction subject area may not work as one may expect. It is recommended to avoid this combination. If you require attribution combined with transactional analysis, it is a good idea to filter on order ID in attribution subject area rather than transaction
- Proceed with caution when using attribution revenue values with subject areas of high granularity such as experience, goal achieved, and segments; this applies in all cases where these are used anyway. This combination will increase revenue values proportionally to the number of experiences, segments, or achieved goals
The Attribution model needs a finite subset of data to attribute values correctly therefore it will only show data up to the day before. Trying to apply the model to the live data may produce incorrect results

Visitor pulse subject area

Live Tap includes a subject area for Visitor pulse. On top of the simplest measures and dimensions related to survey, questions, and answers, as shown below, it is possible to filter the data by answers given to other questions.

For example you may search for answers to question 3 only for users who had a specific response to question 2 and/or. This can be done by setting the filter on any of the dimensions in Filter by Answers to Other Questions dimension group.

The Visitor pulse subject area can be combined freely with all other subject areas to enrich analysis with extra data points such as device, browser, country, or referrer information from session, entrance, or others.

Cross-field filter:

Sample dashboards and reports ("looks")

The Live Tap BI content comes with a sample dashboard called Experiences by Segments and four Explore URLs that show work in progress. These can be saved as reports or "looks", single charts, or tabular visualizations.

Sample Dashboard

The Post-Experience Revenue-per-Visitor by Segments dashboard includes:

Key performance indicators - total revenue, number of distinct visitors and Revenue Per Visitor
A chart showing number of visitors that saw various experiences over time, Experience delivery over time, dimensioned by week
Top 10 segments by their size, along with the total revenue generated by each segment
A detailed table presenting number of visitors, total revenue, and Revenue Per Visitor by experiences (control vs variant groups)

Example looks

The tracking_id_looks-url-templates.md file contains definitions for 4 looks (Looker reports) that you can customize for your environment and use as example Live Tap queries:

Conversions progression over time by segment - a line chart, showing number of converters after 1,2,3… weeks since first entry. Each line represents a different segment cohort:

Product Interaction Funnel - a bar-funnel chart that shows the steps on the path-to-purchase for a set of visitors, with each step on that path being one of the product interaction events in the funnel (Product Listing, Product Detail, View with Product in Basket, and then finally the Product Purchase event) and the chart filterable by segment and experience:

Segments Comparison - a bar chart showing some key metrics by segment name:
- Revenue per Visitor
- Segment Visitors
- Visitor Conversion Rate

Y axes are decombined so that each measure uses its own scale. The chart be filtered by experience name:

Visitors Geomapped - a map visualization showing the number of visitors in a selected segment or multiple segments according to location:

To use these looks, follow these steps.

Step 1

Locate {YOUR_LOOKER_INSTANCE_ID} and {TRACKING_ID} placeholders in the URLs:

Example:

https://{YOUR_LOOKER_INSTANCE_ID}.looker.com/explore/{TRACKING_ID}_livetap/**{TRACKING_ID}_qp_bi_view?fields={TRACKING_ID}_qp_bi_product_v01.meta_type,{TRACKING_ID}...&origin=share-expanded

Step 2

Replace those placeholder values with your Looker Instance Id and Google BigQuery Tracking Id, so that the URL looks like this:

https://**qubit**.looker.com/explore/**union_fashion_livetap**/**union_fashion**_qp_bi_view?fields=**union_fashion**_qp_bi_product_v01.meta_type,**union_fashion**...&origin=share-expanded

Step 3

Paste this updated URL into your browser’s address bar

You can find your Looker instance Id between https:// and .looker.com in the URL of your Looker instance and your tracking Id(s) in Google BigQuery UI by referring to the dataset name.

There may be multiple datasets in your Google BigQuery project.

After you run the looks for the first time, you can save them in your Looker project as a starting point for similar looks, or just as examples of starter reports.

Usage tracking dashboard

This dashboard is intended for use by Looker and BigQuery administrators within an organization, and comprises several sections that show usage of the underlying BigQuery database platform through the following KPI tiles and data visualizations:

Three KPI metric tiles for average query runtime in seconds, number of queries run, and total megabytes queried.
Number of queries over time, with a by-user breakdown area chart.
Daily Active Users and Daily/Monthly Active Users Ratio is a mixed bar/line chart:

Number of business views touched & avg megabytes queried is a mixed bar/line chart.
Business Views Touched is a tabular view:

Individual Queries is a tabular view, displaying details such as exact timestamp of starting the query, number of business views touched and query raw text.

The entire dashboard can be filtered by date, type of query, user account name, megabytes queried range, and query runtime range in seconds.

The default reporting date is the last 30 days. The default type of query is those queries that touched business views.

Google Data Studio

Google Data Studio is a free-to-use BI tool provided by Google that can be used to connect to a range of data source types including Google BigQuery and Google Analytics, and visualize your data through a number of different chart types.

You can find an example report template here.

You can make a copy of this template, connect to your Google BigQuery project, and then use it to view the same or similar metrics as we provide in the Looker sample dashboard:

The example report template contains the following sub-pages:

Example Entrance Report
Example Product Report
Example Experience Report
Example Transaction Report
Example Segment Report
Example View Report
Example Session Report

Copying and re-connecting the Google Data Studio template to your Live Tap Google project

To Make a copy of the example Google Data Studio template and connect it to your own Live Tap Google BigQuery project, follow these steps:

Step 1

Using your web browser navigate to the template URL and select File > Make a copy…

Step 2

Select Create Report and when the One or more of the Data Sources used in this Report is not attached to the report dialog is shown, select Maybe Later when prompted

Step 3

With the copied template open, select Resource > Manage added data sources. For each added data source, select Edit then EDIT CONNECTION and then locate the corresponding BigQuery view in your Live Tap project dataset

Step 4

Apply the data source change by selecting FINISHED in the upper-right hand corner. Repeat steps 2-5 for each remaining data source to complete the data source remapping to your Live Tap dataset

Using Live Tap business views as a data source for Google Data Studio

Google Data Studio supports a single data source for each report template that can either be sourced from one BigQuery table, or another supported data provider, one view, or one custom SQL statement. Data from Live Tap can therefore be used as a data source for Google Data Studio in 3 ways:

By selecting one of the individual Business Views or QProtocol event views
Through creating a custom SQL query
By selecting one of qp_bi_all_exp_seen_context_v01 and qp_bi_all_v01

Business Views join the other business views together to get around the single view or table data source restriction in Google Data Studio.

Single business view

Using a single Business View, for example qp_bi_segment, is potentially the simplest and most efficient way to use Live Tap data in a template but restricts you to just the data returned by that view.

Custom SQL

Writing a custom SQL query gives you the most flexibility but requires you to understand SQL and write a custom query per report template.

qp_bi_all_v01

qp_bi_all_v01 joins the experience delivery business view with all of the other non-experience related views to give you access to all the Live Tap Business Views data within one view.

Using this view therefore means you can avoid writing your own custom SQL query to join these views together and prefixes the column names with the source business view name.

qp_bi_all_exp_seen_context_v01

qp_bi_all_exp_seen_context_v01 similarly takes the post-experience and post-goal achieved views and joins them to the other non-experience related views.

qp_biall views return large amounts of view columns and should only be used with Google Data Studio and any other BI tools that only allow a single table or view to be used as a report datasource.

Other BI tools that support Google BigQuery connections

Many other BI and Data Integration tools, for example Tableau, Domo, Informatica PowerCenter, Microsoft Excel, and Microstrategy, support connecting to Google BigQuery as a data source either through native drivers third-party ODBC/OData drivers.

Whilst these BI tools will connect to BigQuery and can be used to report against Live Tap data, caution should be used when using tools such as Tableau and Microsoft Power BI that are designed primarily to work with data extracts, as this approach may not be practical with the amount of data you’d want to analyze via Live Tap or the frequency at which it is updated.

However we do provide guidance on how to access Live Tap data using Tableau and PowerBI for those customers who are happy to work within these limitations.

Joining business views within a BI Tool Data source definition

When you connect a BI tool such as Tableau or Microstrategy to Live Tap Business Views, you will typically need to set up table joins between the views before you can create reports that include measures and dimension attributes from more than one view.

The join conditions for the Live Tap Business Views are detailed below:

qp_bi_view.view_id = qp_bi_product.view_id
qp_bi_view.view_id = qp_bi_segment.view_id
qp_bi_view.view_id = qp_bi_experience.view_id
qp_bi_view.session_id = qp_bi_session.session_id
qp_bi_view.entrance_id = qp_bi_entrance.entrance_id
qp_bi_view.view_id = qp_bi_transaction.view_id
qp_bi_view.view_id = qp_bi_visitor_pulse.view_id
qp_bi_view.view_id = qp_bi_goal_achieved.view_id

Useful derived measures and dimension attributes for BI tools

In addition to the measures contained with the Live Tap Business Views, most BI tools allow you to create custom or derived measures and dimension attributes using SQL expressions.

The Live Tap BI Content for Looker semantic model described earlier in this article includes a number of derived measures. These are detailed in Live Tap derived measures.

Using Live Tap business views with Tableau

Tableau can connect to Google BigQuery for reporting and analyzing data from Live Tap, a process that involves first connecting Tableau to the BigQuery datasource and then joining these table sources together, as required, to create the data source for the Tableau report you are looking to create.

To connect Tableau to Live Tap and the business views, follow these example steps:

Step 1

Open Tableau Desktop and navigate to the Data Source tab. From the blue Connect panel on the left, choose Google BigQuery

You might need to select More… and then Google BigQuery.

Step 2

Use your Google credentials to log in to Google BigQuery. When connected, in the Billing Project combo box, select your project from the list

If you do not see your project listed, contact Coveo Support to obtain access.

Step 3

In the Dataset combo box, select the dataset that contains tables / views you are going to query. The list of available tables and views will be displayed, as shown in the following example:

Joining sources

To join these table sources together for a particular report, for example to analyze customer transactions together with the segments they were members of at the time, follow these steps:

Step 1

Select and drag and drop each of the tables you would like to join together into the Drag tables here panel. When prompted, select View Id as the join column from both table sources, and Left (outer) as the join type, as shown in the following example:

For a full list of business view-to-business view join column, see the section Joining Business Views within a BI Tool Data source definition above.

If you would like to introduce another view, for example qp_bi_session, to be able to filter by geolocation, just drag it to the canvas and define a new join, as shown in below:

Step 2

After defining all necessary joins, change your connection to Extract, then select File > Save As > and save your workbook as a Tableau file with .twbx extension

Step 3

Finally, select Go to Worksheet

The default Sheet 1 created with the tab name is visible in the toolbar at the bottom of the page.

You will now see a dialog box with information about the Tableau extract being created. Once done, you’ll see all business views in the Data panel

An important consideration when joining business views in Tableau is that joins might result in duplicated values. An example is joining transaction data with segment data, where a customer can be a member of multiple segments when conducting a transaction, for example:

context_id	context_viewNumber	segmentName	transaction_id	transaction_total
123xyz	14	UK Desktop	tr4	700$
123xyz	14	VIP Customers	tr4	700$
123xyz	14	Returning Visitors	tr4	700$
123xyz	14	Frequent purchasers	tr4	700$

In this example, visitor 123xyz had a single transaction worth $700 but was a member of 4 segments when this transaction took place. This is a common situation and would result in 4 rows being returned and an incorrect double-counting result from the usual Sum aggregation when the segment and transaction business views are joined together.

The Live Tap BI Content for Looker semantic model handles the deduplication required to return the correct results automatically, but to do this with Tableau, you need to use Tableau’s Level of Detail expression to create the Transaction Total for this report.

To create a calculated field aggregating the transaction total without double-counting the transaction value due to the visitor belonging to four segments at the time of the transaction, follow these steps:

Step 1

Ctrl-select in the Measures panel and select Create Calculated Field….

Step 2

Define the calculated field as MIN(transaction_total) per each transaction_id, and then SUM this value, using the expression:

   `SUM( { FIXED [Transaction Id] : MIN([Transaction Total]) } )`

And as shown in this example:

You can do the same for any other measures that result in double-counting. For more details, see Tableau’s documentation covering Level of Detail Expressions.

Configuring Tableau Desktop version 10.1+to use BigQuery Legacy SQL

Google BigQuery currently supports two dialects of SQL, Legacy SQL and Standard SQL.

Currently, the SQL dialect that is used to process QP data in BigQuery is BigQuery Legacy SQL, but if you use Tableau Desktop version 10 or higher, the default dialect for connections to BigQuery is Standard SQL.

Therefore, to connect to the BigQuery database data provided via Live Tap, you must follow the Tableau installation configuration step detailed above.

Full details of this installation configuration step can be found in the Tableau product documentation.

To make Tableau Desktop recognize BigQuery Legacy SQL as default dialect, a connection customization driver file needs to be placed in a particular repository. Follow these steps:

Step 1

Using a text editor, create a file containing the following:

<connection-customization class='bigquery' enabled='true' version='10.1' >
    <vendor name='bigquery' />
    <driver name='bigquery' />
    <customizations>
        <customization name='connection-dialect' value='google-bql' />
    </customizations>
</connection-customization>

Step 2

If the version you’re using is not 10.1, change the version to the Tableau Desktop version you’re using. You can check the version number by selecting *Tableau > About Tableau * .

Step 3

Save the text file with the file extension .tdc. In this example we’ll name the file BigQueryCustomization.tdc

Step 4

Locate the My Tableau Repository > Datasources directory on your workstation and place the .tdc connection customization driver file in the Datasources directory

If there are any subfolders there, do not put the customization file inside them. It should be placed directly in Datasources

Step 5

Open Tableau Desktop and connect to your Live Tap project using Google BigQuery as the data source

Data dictionary / schema

All verticals

qp_bi_view

Column Name BQ Data Type Column Description

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
view_id	STRING	Unique ID of view
entrance_id	STRING	Unique ID of entrance. A new entrance is started if an attribute of the previous page is different that is, the referrer, is on a different domain or if the url of the `View` is typed in manually in the address bar. Reloading the browser or navigating between pages on the same domain will not cause entrance events to be emitted
session_id	STRING	Unique ID of session - a session ends when a visitor is inactive for more than 30 minutes and starts when activity resumes after ending a session or on the first visit.
entry_id	STRING	Unique ID of entry where entry is each session in an entrance
context_id	STRING	Visitor ID based on browser cookie
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in their lifetime (across all sessions)
context_sessionViewNumber	INTEGER	The sequential pageview number of a user in a session (across 1 session)
context_entranceViewNumber	INTEGER	The sequential pageview number of a user in an entrance (across 1 session)
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all session) up to the current view
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone
context_lifetimeValue_baseValue	FLOAT	The tota conversion value visitor has had (across all session) up to the current view
user_id	STRING	User ID emitted from the point user was logged on until the session expires
user_username	STRING	Username emitted from the moment user was logged on all views throughout the session
user_title	STRING	User’s title emitted from the moment user was logged on or registered on all views throughout the session
user_email	STRING	User’s email emitted from the moment user was logged on or registered on all views throughout the session
user_loyalty_tier	STRING	User’s loyality tier emitted from the moment user was logged on or registered on all views throughout the session
user_gender	STRING	User’s gender from the moment user was logged on or registered on all views throughout the session
user_age	INTEGER	User’s age from the moment user was logged on or registered on all views throughout the session
user_phoneNumber	STRING	User’s phone number emitted from the moment user was logged on or registered on all views throughout the session
user_isGuest	STRING	Indicates whether the visitor was using guest account (if applicable)
user_firstName	STRING	User’s first name emitted from the moment user was logged on or registered on all views throughout the session
user_lastName	STRING	User’s last name emitted from the moment user was logged on or registered on all views throughout the session
is_user_sign_up_view	BOOLEAN	True if visitor registered on the view
meta_ts	INTEGER	Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_url	STRING	URL on which view was emitted
page_type	STRING	Type of page on which view was emitted - as emitted by website
page_subtype	STRING	Page category / breadcrumb of view - as emitted by website
transaction_id	STRING	Transaction ID if user submitted order on the page view
views_in_session	INTEGER	The number of views user has had in a session
first_view_in_session_ts	INTEGER	Epoch time of first view that was emitted in the session
last_view_in_session_ts	INTEGER	Epoch time of last view that was emitted in the session
views_in_entrance	INTEGER	Total number of views in user’s entrance
total_visitor_views	INTEGER	Total number of views in user’s livetime
visitor_first_entry_date	STRING	The first date visitor has entered the site
last_view_in_session	BOOLEAN	True if view was the last view in user’s session
last_view_in_entrance	BOOLEAN	True if view was the last view in user’s entrance
last_visitor_view	BOOLEAN	True if view was the last view in user’s lifetime
new_vs_returning	STRING	Label for new_vs_returning status
session_duration_seconds	FLOAT	The number of seconds the session was active for
property_event_ts	TIMESTAMP	The timestamp of event in client property local time

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

view_id

STRING

Unique ID of view

entrance_id

STRING

Unique ID of entrance. A new entrance is started if an attribute of the previous page is different that is, the referrer, is on a different domain or if the url of the View is typed in manually in the address bar. Reloading the browser or navigating between pages on the same domain will not cause entrance events to be emitted

session_id

STRING

Unique ID of session - a session ends when a visitor is inactive for more than 30 minutes and starts when activity resumes after ending a session or on the first visit.

entry_id

STRING

Unique ID of entry where entry is each session in an entrance

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_entranceNumber

INTEGER

The sequential entrance number of a user in their lifetime (across all sessions)

context_sessionViewNumber

INTEGER

The sequential pageview number of a user in a session (across 1 session)

context_entranceViewNumber

INTEGER

The sequential pageview number of a user in an entrance (across 1 session)

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all session) up to the current view

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone

context_lifetimeValue_baseValue

FLOAT

The tota conversion value visitor has had (across all session) up to the current view

user_id

STRING

User ID emitted from the point user was logged on until the session expires

user_username

STRING

Username emitted from the moment user was logged on all views throughout the session

user_title

STRING

User’s title emitted from the moment user was logged on or registered on all views throughout the session

user_email

STRING

User’s email emitted from the moment user was logged on or registered on all views throughout the session

user_loyalty_tier

STRING

User’s loyality tier emitted from the moment user was logged on or registered on all views throughout the session

user_gender

STRING

User’s gender from the moment user was logged on or registered on all views throughout the session

user_age

INTEGER

User’s age from the moment user was logged on or registered on all views throughout the session

user_phoneNumber

STRING

User’s phone number emitted from the moment user was logged on or registered on all views throughout the session

user_isGuest

STRING

Indicates whether the visitor was using guest account (if applicable)

user_firstName

STRING

User’s first name emitted from the moment user was logged on or registered on all views throughout the session

user_lastName

STRING

User’s last name emitted from the moment user was logged on or registered on all views throughout the session

is_user_sign_up_view

BOOLEAN

True if visitor registered on the view

meta_ts

INTEGER

Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_url

STRING

URL on which view was emitted

page_type

STRING

Type of page on which view was emitted - as emitted by website

page_subtype

STRING

Page category / breadcrumb of view - as emitted by website

transaction_id

STRING

Transaction ID if user submitted order on the page view

views_in_session

INTEGER

The number of views user has had in a session

first_view_in_session_ts

INTEGER

Epoch time of first view that was emitted in the session

last_view_in_session_ts

INTEGER

Epoch time of last view that was emitted in the session

views_in_entrance

INTEGER

Total number of views in user’s entrance

total_visitor_views

INTEGER

Total number of views in user’s livetime

visitor_first_entry_date

STRING

The first date visitor has entered the site

last_view_in_session

BOOLEAN

True if view was the last view in user’s session

last_view_in_entrance

BOOLEAN

True if view was the last view in user’s entrance

last_visitor_view

BOOLEAN

True if view was the last view in user’s lifetime

new_vs_returning

STRING

Label for new_vs_returning status

session_duration_seconds

FLOAT

The number of seconds the session was active for

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

qp_bi_entrance

Column Name BQ Data Type Column Description

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
view_id	STRING	Unique ID of view
entrance_id	STRING	Unique ID of entrance. A new entrance is started if an attribute of the previous page is different that is, the referrer, is on a different domain or if the url of the `View` is typed in manually in the address bar. Reloading the browser or navigating between pages on the same domain will not cause entrance events to be emitted
session_id	STRING	Unique ID of session - a session ends when a visitor is inactive for more than 30 minutes and starts when activity resumes after ending a session or on the first visit.
property_event_ts	TIMESTAMP	The timestamp of event in client property local time
context_id	STRING	Visitor ID based on browser cookie
context_entranceNumber	INTEGER	The sequential entrance number of a user in their lifetime (across all sessions)
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all session) up to the current view
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_ts	INTEGER	Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone
meta_url	STRING	URL on which view was emitted
referrer_url	STRING	The URL of the page a visitor has come from.
referrer_type	STRING	The type of the page a visitor has come from affiliate
for example.	referrer_network	STRING
If applicable - the network of the page a visitor has come from affiliate window	for example.	referrer_mediaType
STRING	If applicable - the media type of the page a visitor has come from.	referrer_campaign
STRING	If applicable - the campaign of the page a visitor has come from.	referrer_adGroup
STRING	If applicable - the ad group of the page a visitor has come from.	referrer_content
STRING	If applicable - the content of the page a visitor has come from.	referrer_keywords
STRING	If applicable - the keywords a visitor used to get to the original page before the view.	referrer_searchQuery
STRING	If applicable - the search query a visitor used to get to the original page before the view.	referrer_label
STRING	The label of the page a visitor has come from.	referrer_domain
STRING	The domain of the page a visitor has come from.	referrer_matchType
STRING	The match type of the page a visitor has come from.	utm_source
STRING	The value of Google UTM source parameter - extracted from landing page (meta_url)	utm_medium
STRING	The value of Google UTM medium parameter - extracted from landing page (meta_url)	utm_content
STRING	The value of Google UTM content parameter - extracted from landing page (meta_url)	utm_campaign
STRING	The value of Google UTM campaign parameter - extracted from landing page (meta_url)	utm_term

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

view_id

STRING

Unique ID of view

entrance_id

STRING

session_id

STRING

Unique ID of session - a session ends when a visitor is inactive for more than 30 minutes and starts when activity resumes after ending a session or on the first visit.

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

context_id

STRING

Visitor ID based on browser cookie

context_entranceNumber

INTEGER

The sequential entrance number of a user in their lifetime (across all sessions)

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all session) up to the current view

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_ts

INTEGER

Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone

meta_url

STRING

URL on which view was emitted

referrer_url

STRING

The URL of the page a visitor has come from.

referrer_type

STRING

The type of the page a visitor has come from affiliate

for example.

referrer_network

STRING

If applicable - the network of the page a visitor has come from affiliate window

for example.

referrer_mediaType

STRING

If applicable - the media type of the page a visitor has come from.

referrer_campaign

STRING

If applicable - the campaign of the page a visitor has come from.

referrer_adGroup

STRING

If applicable - the ad group of the page a visitor has come from.

referrer_content

STRING

If applicable - the content of the page a visitor has come from.

referrer_keywords

STRING

If applicable - the keywords a visitor used to get to the original page before the view.

referrer_searchQuery

STRING

If applicable - the search query a visitor used to get to the original page before the view.

referrer_label

STRING

The label of the page a visitor has come from.

referrer_domain

STRING

The domain of the page a visitor has come from.

referrer_matchType

STRING

The match type of the page a visitor has come from.

utm_source

STRING

The value of Google UTM source parameter - extracted from landing page (meta_url)

utm_medium

STRING

The value of Google UTM medium parameter - extracted from landing page (meta_url)

utm_content

STRING

The value of Google UTM content parameter - extracted from landing page (meta_url)

utm_campaign

STRING

The value of Google UTM campaign parameter - extracted from landing page (meta_url)

utm_term

qp_bi_experience

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
property_event_ts	TIMESTAMP	The timestamp of event in client property local time
view_id	STRING	Unique ID of view
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone (in cllent property timezone)
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
context_id	STRING	Visitor ID based on browser cookie
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all session) up to the current view
meta_ts	INTEGER	Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
experienceId	INTEGER	The numeric ID of experience.
experienceName	STRING	The name of experience.
variationMasterId	INTEGER	The numeric ID of master variation.
variationName	STRING	The name of variation
iterationId	INTEGER	The numeric ID of iteration. (iteration is created when variation is updated)
isControl	INTEGER	True if variation represents the group control
first_view_meta_ts	INTEGER	First time the user has seen the iteration of variation - in epoch time
first_view_meta_recordDate	STRING	First day when the visitor has seen the iteration of variation
first_view_in_iteration	INTEGER	First view number when the visitor has seen the iteration of variation
last_view_in_iteration	INTEGER	Last view number when the visitor has seen the iteration of variation
is_post_experience_view	INTEGER	False if experience was delivered on the view. True if the experience fired prior to the view.

Column Name

BQ Data Type

Column Description

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

view_id

STRING

Unique ID of view

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone (in cllent property timezone)

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all session) up to the current view

meta_ts

INTEGER

Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

experienceId

INTEGER

The numeric ID of experience.

experienceName

STRING

The name of experience.

variationMasterId

INTEGER

The numeric ID of master variation.

variationName

STRING

The name of variation

iterationId

INTEGER

The numeric ID of iteration. (iteration is created when variation is updated)

isControl

INTEGER

True if variation represents the group control

first_view_meta_ts

INTEGER

First time the user has seen the iteration of variation - in epoch time

first_view_meta_recordDate

STRING

First day when the visitor has seen the iteration of variation

first_view_in_iteration

INTEGER

First view number when the visitor has seen the iteration of variation

last_view_in_iteration

INTEGER

Last view number when the visitor has seen the iteration of variation

is_post_experience_view

INTEGER

False if experience was delivered on the view. True if the experience fired prior to the view.

qp_bi_goal_achieved

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
property_event_ts	TIMESTAMP	The timestamp of event in client property local time
view_id	STRING	Unique ID of view
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone (in cllent property timezone)
meta_ts	INTEGER	Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
context_id	STRING	Visitor ID based on browser cookie
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all session) up to the current view
experienceId	INTEGER	The numeric ID of experience.
experienceName	STRING	The name of experience.
variationMasterId	INTEGER	The numeric ID of master variation.
variationName	STRING	The name of variation
iterationId	INTEGER	The numeric ID of iteration. (iteration is created when variation is updated)
isControl	INTEGER	True if variation represents the group control
goalId	INTEGER	The numeric ID of experience goal
goalValue	STRING	The value emitted for experience goal
goalType	STRING	The type emitted for experience goal
first_view_meta_ts	INTEGER	First time the user has seen the iteration of variation - in epoch time
first_view_meta_recordDate	STRING	First day when the visitor has seen the iteration of variation
first_view_in_iteration	INTEGER	First view number when the visitor has seen the iteration of variation
last_view_in_iteration	INTEGER	Last view number when the visitor has seen the iteration of variation
is_post_goal_achieved_view	INTEGER	False if goal was achieved on the view. True if the goal was achieved prior to the view.

Column Name

BQ Data Type

Column Description

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

view_id

STRING

Unique ID of view

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone (in cllent property timezone)

meta_ts

INTEGER

Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all session) up to the current view

experienceId

INTEGER

The numeric ID of experience.

experienceName

STRING

The name of experience.

variationMasterId

INTEGER

The numeric ID of master variation.

variationName

STRING

The name of variation

iterationId

INTEGER

The numeric ID of iteration. (iteration is created when variation is updated)

isControl

INTEGER

True if variation represents the group control

goalId

INTEGER

The numeric ID of experience goal

goalValue

STRING

The value emitted for experience goal

goalType

STRING

The type emitted for experience goal

first_view_meta_ts

INTEGER

First time the user has seen the iteration of variation - in epoch time

first_view_meta_recordDate

STRING

First day when the visitor has seen the iteration of variation

first_view_in_iteration

INTEGER

First view number when the visitor has seen the iteration of variation

last_view_in_iteration

INTEGER

Last view number when the visitor has seen the iteration of variation

is_post_goal_achieved_view

INTEGER

False if goal was achieved on the view. True if the goal was achieved prior to the view.

qp_bi_segment

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
view_id	STRING	Unique ID of view
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone (in cllent property timezone)
meta_ts	INTEGER	Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
context_id	STRING	Visitor ID based on browser cookie
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all session) up to the current view
segment_rows	INTEGER	The number of different segments the user was in at the time of the view
segmentId	STRING	The numeric ID of segment
segmentName	STRING	The name of segment

Column Name

BQ Data Type

Column Description

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

view_id

STRING

Unique ID of view

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone (in cllent property timezone)

meta_ts

INTEGER

Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all session) up to the current view

segment_rows

INTEGER

The number of different segments the user was in at the time of the view

segmentId

STRING

The numeric ID of segment

segmentName

STRING

The name of segment

qp_bi_session

Column Name BQ Data Type Column Description

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
session_id	STRING	Unique ID of session - a session ends when a visitor is inactive for more than 30 minutes and starts when activity resumes after ending a session or on the first visit.
view_id	STRING	Unique ID of view
entrance_id	STRING	Unique ID of entrance. A new entrance is started if an attribute of the previous page is different, that is, the referrer, is on a different domain or if the url of the `View` is typed in manually in the address bar. Reloading the browser or navigating between pages on the same domain will not cause entrance events to be emitted
property_event_ts	TIMESTAMP	The timestamp of event in client property local time
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone
meta_ts	INTEGER	Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone
meta_url	STRING	URL on which view was emitted
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
context_id	STRING	Visitor ID based on browser cookie
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in their lifetime (across all sessions)
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all sessions) up to the current view
deviceType	STRING	"The type of device user initiated the session with (for example, computer, mobile, or tablet) deviceName,STRING,"The name of device user initiated the session with (for example, Iphone or Sony) appType,STRING,The application type used on the device user initiated the session with (for example, browser) appName,STRING,The application name used on the device user initiated the session with (for example, Chrome) appVersion,STRING,The application version used on the device user initiated the session with (for example, 1.3) osName,STRING,The OS type used on the device user initiated the session with (for example, Windows) osVersion,STRING,The OS version used on the device user initiated the session with (for example, 7) ipLocation_country,STRING,The country in which user initiated the session (for example, United Kingdom) ipLocation_country_code,STRING,The code of country in which user initiated the session (for example, UK) ipLocation_region,STRING,The region of country in which user initiated the session ipLocation_city,STRING,The city in which user initiated the session (for example, London) ipLocation_area,STRING,The area of city in which user initiated the session

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

session_id

STRING

Unique ID of session - a session ends when a visitor is inactive for more than 30 minutes and starts when activity resumes after ending a session or on the first visit.

view_id

STRING

Unique ID of view

entrance_id

STRING

Unique ID of entrance. A new entrance is started if an attribute of the previous page is different, that is, the referrer, is on a different domain or if the url of the View is typed in manually in the address bar. Reloading the browser or navigating between pages on the same domain will not cause entrance events to be emitted

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone

meta_ts

INTEGER

Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone

meta_url

STRING

URL on which view was emitted

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_entranceNumber

INTEGER

The sequential entrance number of a user in their lifetime (across all sessions)

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all sessions) up to the current view

deviceType

STRING

"The type of device user initiated the session with (for example, computer, mobile, or tablet) deviceName,STRING,"The name of device user initiated the session with (for example, Iphone or Sony) appType,STRING,The application type used on the device user initiated the session with (for example, browser) appName,STRING,The application name used on the device user initiated the session with (for example, Chrome) appVersion,STRING,The application version used on the device user initiated the session with (for example, 1.3) osName,STRING,The OS type used on the device user initiated the session with (for example, Windows) osVersion,STRING,The OS version used on the device user initiated the session with (for example, 7) ipLocation_country,STRING,The country in which user initiated the session (for example, United Kingdom) ipLocation_country_code,STRING,The code of country in which user initiated the session (for example, UK) ipLocation_region,STRING,The region of country in which user initiated the session ipLocation_city,STRING,The city in which user initiated the session (for example, London) ipLocation_area,STRING,The area of city in which user initiated the session

qp_bi_attribution

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
context_id	STRING	Visitor ID based on browser cookie
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in their lifetime (across all sessions)
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all sessions) up to the current view
purchase_timestamp	TIMESTAMP	The timestap of order
entry_timestamp	TIMESTAMP	The timestap of entry
ranked_entry_number_reverse	INTEGER	The sequential number of entry - reversed. For example
the last entry in purchase cycle of order would have value 1	ranked_purchase_number	INTEGER
Purchase cycle / sequential number of orders made in a session in user’s lifetime. For example all orders made in the same session for the first time would have ranked_purchase_number 1, the orders made in the next session would have value 2. Orders made in separate session would have separate ranked purchase numbers	ranked_entry_number	INTEGER
The sequential number of entry in purchase cycle.	entry_id	STRING
Unique entry ID. A new entry is created when there’s a new entrance in a session. Ultimately the number of entries is equal to the number of sesions and the number of entrances that happened in those sessions.	order_ids_in_purchase_cycle	STRING
The number of orders made in purchase cycle.	referrer_type	STRING
The type of the page a visitor has come from in the converting entry (for example	affiliate).	referrer_network
STRING	If applicable - the network of the page a visitor has come from in the converting entry (for example	affiliate window).
referrer_mediaType	STRING	If applicable - the media type of the page a visitor has come from (in the converting entry)
referrer_campaign	STRING	If applicable - the campaign of the page a visitor has come from (in the converting entry)
referrer_adGroup	STRING	If applicable - the ad group of the page a visitor has come from (in the converting entry)
referrer_content	STRING	If applicable - the content of the page a visitor has come from (in the converting entry)
referrer_keywords	STRING	If applicable - the keywords a visitor used to get to the original page before the view (in the converting entry)
referrer_searchQuery	STRING	If applicable - the search query a visitor used to get to the original page before the view (in the converting entry)
referrer_label	STRING	The label of the page a visitor has come from (in the converting entry)
referrer_domain	STRING	The domain of the page a visitor has come from (in the converting entry)
referrer_matchType	STRING	The match type of the page a visitor has come from (in the converting entry)
first_click_value	FLOAT	This column will only have a value for entries that were first in purchase cycle. They bear the full transactional value of purchase cycle.
even_click_value	FLOAT	The transactional value divided by the number of entries in purchase cycle
last_click_value	FLOAT	This column will only have a value for entries that were last in purchase cycle. They bear the full transactional value of purchase cycle.
pageviews_in_entry	INTEGER	The total number of views in entry
entries_in_purchase_cycle	INTEGER	The total number of entries in purchase cycle
behavioural_value	FLOAT	The transactional value attributed to entry based on its behavioural value
conversion_value_in_purchase_cycle	FLOAT	The total transactional value
touchpoint	STRING	The label for entry, that is, First, Last, Assit and Single entry - designates where in purchase cycle the entry happened
true_referrer_type	STRING	Normalised referrer_type
purchase_cycle_group	STRING	Purchase cycle group bucketed into 5 values: 1,2,3,4, 5+,
property_event_ts	TIMESTAMP	The timestamp of transaction in client property local time

Column Name

BQ Data Type

Column Description

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_entranceNumber

INTEGER

The sequential entrance number of a user in their lifetime (across all sessions)

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all sessions) up to the current view

purchase_timestamp

TIMESTAMP

The timestap of order

entry_timestamp

TIMESTAMP

The timestap of entry

ranked_entry_number_reverse

INTEGER

The sequential number of entry - reversed. For example

the last entry in purchase cycle of order would have value 1

ranked_purchase_number

INTEGER

Purchase cycle / sequential number of orders made in a session in user’s lifetime. For example all orders made in the same session for the first time would have ranked_purchase_number 1, the orders made in the next session would have value 2. Orders made in separate session would have separate ranked purchase numbers

ranked_entry_number

INTEGER

The sequential number of entry in purchase cycle.

entry_id

STRING

Unique entry ID. A new entry is created when there’s a new entrance in a session. Ultimately the number of entries is equal to the number of sesions and the number of entrances that happened in those sessions.

order_ids_in_purchase_cycle

STRING

The number of orders made in purchase cycle.

referrer_type

STRING

The type of the page a visitor has come from in the converting entry (for example

affiliate).

referrer_network

STRING

If applicable - the network of the page a visitor has come from in the converting entry (for example

affiliate window).

referrer_mediaType

STRING

If applicable - the media type of the page a visitor has come from (in the converting entry)

referrer_campaign

STRING

If applicable - the campaign of the page a visitor has come from (in the converting entry)

referrer_adGroup

STRING

If applicable - the ad group of the page a visitor has come from (in the converting entry)

referrer_content

STRING

If applicable - the content of the page a visitor has come from (in the converting entry)

referrer_keywords

STRING

If applicable - the keywords a visitor used to get to the original page before the view (in the converting entry)

referrer_searchQuery

STRING

If applicable - the search query a visitor used to get to the original page before the view (in the converting entry)

referrer_label

STRING

The label of the page a visitor has come from (in the converting entry)

referrer_domain

STRING

The domain of the page a visitor has come from (in the converting entry)

referrer_matchType

STRING

The match type of the page a visitor has come from (in the converting entry)

first_click_value

FLOAT

This column will only have a value for entries that were first in purchase cycle. They bear the full transactional value of purchase cycle.

even_click_value

FLOAT

The transactional value divided by the number of entries in purchase cycle

last_click_value

FLOAT

This column will only have a value for entries that were last in purchase cycle. They bear the full transactional value of purchase cycle.

pageviews_in_entry

INTEGER

The total number of views in entry

entries_in_purchase_cycle

INTEGER

The total number of entries in purchase cycle

behavioural_value

FLOAT

The transactional value attributed to entry based on its behavioural value

conversion_value_in_purchase_cycle

FLOAT

The total transactional value

touchpoint

STRING

The label for entry, that is, First, Last, Assit and Single entry - designates where in purchase cycle the entry happened

true_referrer_type

STRING

Normalised referrer_type

purchase_cycle_group

STRING

Purchase cycle group bucketed into 5 values: 1,2,3,4, 5+,

property_event_ts

TIMESTAMP

The timestamp of transaction in client property local time

qp_bi_visitor_pulse

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
property_event_ts	TIMESTAMP	The timestamp of event in client property local time
context_id	STRING	Visitor ID based on browser cookie
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone
meta_ts	INTEGER	Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_url	STRING	URL on which view was emitted
survey_id	STRING	The ID of visitor pulse survey
question_text	STRING	Text of question asked in the Visitor pulse survey
question_type	STRING	The type of a question in survey
question_order	INTEGER	The order of a question in survey
question_id	STRING	The ID of a question in survey
survey_totalQuestions	INTEGER	The total number of question in the survey
answer	STRING	The answer given by the visitor to a question in the survey
question_freetext	STRING	The answer given by the visitor to a question in the survey (if it was a free text type)
view_id	STRING	Unique ID of view
user_survey_response_id	STRING	Unique ID of response in a survey
unique_row_id	STRING	Unique ID of the row
user_survey_answer_1	STRING	Answer given to question number 1
user_survey_answer_2	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_3	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_4	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_5	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_6	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_7	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_8	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_9	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_10	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_11	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_12	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_13	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_14	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_15	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_16	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_17	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_18	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_19	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions
user_survey_answer_20	STRING	Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

Column Name

BQ Data Type

Column Description

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone

meta_ts

INTEGER

Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_url

STRING

URL on which view was emitted

survey_id

STRING

The ID of visitor pulse survey

question_text

STRING

Text of question asked in the Visitor pulse survey

question_type

STRING

The type of a question in survey

question_order

INTEGER

The order of a question in survey

question_id

STRING

The ID of a question in survey

survey_totalQuestions

INTEGER

The total number of question in the survey

answer

STRING

The answer given by the visitor to a question in the survey

question_freetext

STRING

The answer given by the visitor to a question in the survey (if it was a free text type)

view_id

STRING

Unique ID of view

user_survey_response_id

STRING

Unique ID of response in a survey

unique_row_id

STRING

Unique ID of the row

user_survey_answer_1

STRING

Answer given to question number 1

user_survey_answer_2

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_3

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_4

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_5

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_6

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_7

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_8

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_9

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_10

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_11

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_12

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_13

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_14

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_15

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_16

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_17

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_18

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_19

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

user_survey_answer_20

STRING

Answer given to question number (if question exists). This column has been added to support filtering answers given to other questions

Ecommerce

qp_bi_product

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
property_event_ts	TIMESTAMP	The timestamp of event in client property local time
view_id	STRING	Unique ID of view
session_id	STRING	Unique ID of session
entrance_id	STRING	Unique ID of entrance
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_ts	INTEGER	Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
context_id	STRING	Visitor ID based on browser cookie
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all sessions) up to the current view
transaction_id	STRING	Transaction ID if user submitted order on the page view
product_rows	INTEGER	The number of products a visitor has interacted with on the view
concat_product_id	STRING	product_productId and product_sku joined together
product_productId	STRING	Product ID
product_sku	STRING	Product SKU
product_category	STRING	Product Category
product_subcategory	STRING	Product Subcategory
product_manufacturer	STRING	Product Manufacturer
product_color	STRING	Product Colour
product_size	STRING	Product Size
product_stock	INTEGER	Product Stock Level
product_rating	FLOAT	Product Rating
product_price_baseValue	FLOAT	Product sales price (if on sales) or product regular price
product_originalPrice_baseValue	FLOAT	Product original price (if on sales) or product regular price
product_price_baseCurrency	STRING	Currency of product price
product_name	STRING	Product Name
subtotal_baseValue	FLOAT	If product was purchased, the subtotal paid for product (that is, total excluding discounts etc)
quantity	INTEGER	Quantity of items if purchased
unique_row_id	STRING	Unique row ID
product_interaction_type	STRING	Interaction type such as product view, basket addition, purchase, recommendation view etc.
meta_type	STRING	The name of the original QP event the row comes from

Column Name

BQ Data Type

Column Description

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

view_id

STRING

Unique ID of view

session_id

STRING

Unique ID of session

entrance_id

STRING

Unique ID of entrance

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_ts

INTEGER

Epoch time of event - as emitted by user’s browser. NB epoch time is in UTC timezone

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all sessions) up to the current view

transaction_id

STRING

Transaction ID if user submitted order on the page view

product_rows

INTEGER

The number of products a visitor has interacted with on the view

concat_product_id

STRING

product_productId and product_sku joined together

product_productId

STRING

Product ID

product_sku

STRING

Product SKU

product_category

STRING

Product Category

product_subcategory

STRING

Product Subcategory

product_manufacturer

STRING

Product Manufacturer

product_color

STRING

Product Colour

product_size

STRING

Product Size

product_stock

INTEGER

Product Stock Level

product_rating

FLOAT

Product Rating

product_price_baseValue

FLOAT

Product sales price (if on sales) or product regular price

product_originalPrice_baseValue

FLOAT

Product original price (if on sales) or product regular price

product_price_baseCurrency

STRING

Currency of product price

product_name

STRING

Product Name

subtotal_baseValue

FLOAT

If product was purchased, the subtotal paid for product (that is, total excluding discounts etc)

quantity

INTEGER

Quantity of items if purchased

unique_row_id

STRING

Unique row ID

product_interaction_type

STRING

Interaction type such as product view, basket addition, purchase, recommendation view etc.

meta_type

STRING

The name of the original QP event the row comes from

qp_bi_transaction

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
transaction_id	STRING	Order ID
context_id	STRING	Visitor ID based on browser cookie
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
context_entranceNumber	INTEGER	The sequential entrance number of a user in their lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all sessions) up to the current view
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone
meta_ts	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
basket_total_baseValue	FLOAT	Total paid for order in base currency
basket_tax_baseValue	FLOAT	Total tax paid for order in base currency
basket_total_universalValue	FLOAT	Total paid for order in universal currency
basket_subtotal_baseValue	FLOAT	Subtotal paid for order in base currency
basket_quantity	INTEGER	Quantity of items in basket
basket_discount_baseValue	FLOAT	Total value discounted from order in base currency
transaction_total	FLOAT	Equal to basket_total_baseValue
basket_shippingPrice_baseValue	FLOAT	Total paid for shipping in base currency
session_id	STRING	Unique ID of session
entrance_id	STRING	Unique ID of entrance
view_id	STRING	Unique ID of view
previous_transaction_ts	INTEGER	Epoch time of previous transaction
property_event_ts	TIMESTAMP	The timestamp of event in client property local time

Column Name

BQ Data Type

Column Description

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

transaction_id

STRING

Order ID

context_id

STRING

Visitor ID based on browser cookie

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_entranceNumber

INTEGER

The sequential entrance number of a user in their lifetime (across all sessions)

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all sessions) up to the current view

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone

meta_ts

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

basket_total_baseValue

FLOAT

Total paid for order in base currency

basket_tax_baseValue

FLOAT

Total tax paid for order in base currency

basket_total_universalValue

FLOAT

Total paid for order in universal currency

basket_subtotal_baseValue

FLOAT

Subtotal paid for order in base currency

basket_quantity

INTEGER

Quantity of items in basket

basket_discount_baseValue

FLOAT

Total value discounted from order in base currency

transaction_total

FLOAT

Equal to basket_total_baseValue

basket_shippingPrice_baseValue

FLOAT

Total paid for shipping in base currency

session_id

STRING

Unique ID of session

entrance_id

STRING

Unique ID of entrance

view_id

STRING

Unique ID of view

previous_transaction_ts

INTEGER

Epoch time of previous transaction

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

eGaming

qp_bi_betslip_item

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
context_id	STRING	Visitor ID based on browser cookie
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
meta_id	STRING	Unique ID of event
meta_type	STRING	The name of the original QP event the row comes from
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all sessions) up to the current view
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in their lifetime (across all sessions)
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone
meta_ts	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
transaction_id	STRING	Transaction ID if user submitted order on the page view
betslip_totalOdds	FLOAT	Bet total odds
bet_price_baseValue	FLOAT	Bet sales price if on sales or regular price otherwise
bet_originalPrice_baseValue	FLOAT	Bet original price if on sales or regular price otherwise
bet_potentialReturn_baseValue	FLOAT	Bet potential return value
bet_odds	FLOAT	Bet odds
bet_startTime	INTEGER	Bet start time
bet_multiBet	BOOLEAN	True if bet is multi bet
bet_betType	STRING	Bet type
bet_inplay	BOOLEAN	True if bet is in play
bet_sport	STRING	Bet sport
bet_competition	STRING	Bet competition
bet_eventName	STRING	Bet event name
bet_selection	STRING	Bet selection
bet_marketName	STRING	Bet market name
basket_interaction_type	STRING	Basket interaction type such as addition or removal
unique_row_id	STRING	Unique row ID
view_id	STRING	Unique view ID
session_id	STRING	Unique session ID
entrance_id	STRING	Unique entrance ID
interaction_type	STRING	Interaction type such as bet view, bet addition, purchase etc.

Column Name

BQ Data Type

Column Description

qp_bi_view_name

STRING

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

meta_id

STRING

Unique ID of event

meta_type

STRING

The name of the original QP event the row comes from

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all sessions) up to the current view

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_entranceNumber

INTEGER

The sequential entrance number of a user in their lifetime (across all sessions)

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone

meta_ts

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

transaction_id

STRING

Transaction ID if user submitted order on the page view

betslip_totalOdds

FLOAT

Bet total odds

bet_price_baseValue

FLOAT

Bet sales price if on sales or regular price otherwise

bet_originalPrice_baseValue

FLOAT

Bet original price if on sales or regular price otherwise

bet_potentialReturn_baseValue

FLOAT

Bet potential return value

bet_odds

FLOAT

Bet odds

bet_startTime

INTEGER

Bet start time

bet_multiBet

BOOLEAN

True if bet is multi bet

bet_betType

STRING

Bet type

bet_inplay

BOOLEAN

True if bet is in play

bet_sport

STRING

Bet sport

bet_competition

STRING

Bet competition

bet_eventName

STRING

Bet event name

bet_selection

STRING

Bet selection

bet_marketName

STRING

Bet market name

basket_interaction_type

STRING

Basket interaction type such as addition or removal

unique_row_id

STRING

Unique row ID

view_id

STRING

Unique view ID

session_id

STRING

Unique session ID

entrance_id

STRING

Unique entrance ID

interaction_type

STRING

Interaction type such as bet view, bet addition, purchase etc.

qp_bi_user_action

QP Business View Name Field Name Data Type

QP Business View Name	Field Name	Data Type
`qp_bi_user_action`	context_id	STRING
`qp_bi_user_action`	context_viewNumber	INTEGER
`qp_bi_user_action`	meta_id	STRING
`qp_bi_user_action`	context_sessionNumber	INTEGER
`qp_bi_user_action`	context_entranceNumber	INTEGER
`qp_bi_user_action`	context_sessionViewNumber	INTEGER
`qp_bi_user_action`	context_entranceViewNumber	INTEGER
`qp_bi_user_action`	context_conversionNumber	INTEGER
`qp_bi_user_action`	context_lifetimeValue_baseValue	FLOAT
`qp_bi_user_action`	meta_trackingId	STRING
`qp_bi_user_action`	meta_type	STRING
`qp_bi_user_action`	meta_recordDate	STRING
`qp_bi_user_action`	meta_ts	INTEGER
`qp_bi_user_action`	meta_serverTs	INTEGER
`qp_bi_user_action`	meta_url	STRING
`qp_bi_user_action`	user_id	STRING
`qp_bi_user_action`	user_title	STRING
`qp_bi_user_action`	user_firstName	STRING
`qp_bi_user_action`	user_lastName	STRING
`qp_bi_user_action`	user_gender	STRING
`qp_bi_user_action`	user_username	STRING
`qp_bi_user_action`	user_email	STRING
`qp_bi_user_action`	user_language	STRING
`qp_bi_user_action`	user_firstSession	BOOLEAN
`qp_bi_user_action`	user_hasTransacted	BOOLEAN
`qp_bi_user_action`	user_address_addressee	STRING
`qp_bi_user_action`	user_address_company	STRING
`qp_bi_user_action`	user_address_lines	STRING
`qp_bi_user_action`	user_address_region	STRING
`qp_bi_user_action`	user_address_postalCode	STRING
`qp_bi_user_action`	user_address_country	STRING
`qp_bi_user_action`	user_address_countryCode	STRING
`qp_bi_user_action`	user_isGuest	STRING
`qp_bi_user_action`	user_age	INTEGER
`qp_bi_user_action`	user_loyalty_tierPoints	INTEGER
`qp_bi_user_action`	user_loyalty_membershipType	STRING
`qp_bi_user_action`	user_loyalty_membershipPoints	INTEGER
`qp_bi_user_action`	user_loyalty_id	STRING
`qp_bi_user_action`	user_loyalty_tier	STRING
`qp_bi_user_action`	user_phoneNumber	STRING
`qp_bi_user_action`	user_countryCallingCode	STRING
`qp_bi_user_action`	user_deviceId	STRING
`qp_bi_user_action`	user_balance_value	FLOAT
`qp_bi_user_action`	user_balance_currency	STRING
`qp_bi_user_action`	user_balance_baseValue	FLOAT
`qp_bi_user_action`	user_balance_baseCurrency	STRING
`qp_bi_user_action`	user_balance_universalValue	FLOAT
`qp_bi_user_action`	user_balance_universalCurrency	STRING
`qp_bi_user_action`	entrance_id	STRING
`qp_bi_user_action`	session_id	STRING
`qp_bi_user_action`	view_id	STRING
`qp_bi_user_action`	qp_bi_view_name	STRING
`qp_bi_user_action`	ts	TIMESTAMP

qp_bi_user_action

context_id

STRING

qp_bi_user_action

context_viewNumber

INTEGER

qp_bi_user_action

meta_id

STRING

qp_bi_user_action

context_sessionNumber

INTEGER

qp_bi_user_action

context_entranceNumber

INTEGER

qp_bi_user_action

context_sessionViewNumber

INTEGER

qp_bi_user_action

context_entranceViewNumber

INTEGER

qp_bi_user_action

context_conversionNumber

INTEGER

qp_bi_user_action

context_lifetimeValue_baseValue

FLOAT

qp_bi_user_action

meta_trackingId

STRING

qp_bi_user_action

meta_type

STRING

qp_bi_user_action

meta_recordDate

STRING

qp_bi_user_action

meta_ts

INTEGER

qp_bi_user_action

meta_serverTs

INTEGER

qp_bi_user_action

meta_url

STRING

qp_bi_user_action

user_id

STRING

qp_bi_user_action

user_title

STRING

qp_bi_user_action

user_firstName

STRING

qp_bi_user_action

user_lastName

STRING

qp_bi_user_action

user_gender

STRING

qp_bi_user_action

user_username

STRING

qp_bi_user_action

user_email

STRING

qp_bi_user_action

user_language

STRING

qp_bi_user_action

user_firstSession

BOOLEAN

qp_bi_user_action

user_hasTransacted

BOOLEAN

qp_bi_user_action

user_address_addressee

STRING

qp_bi_user_action

user_address_company

STRING

qp_bi_user_action

user_address_lines

STRING

qp_bi_user_action

user_address_region

STRING

qp_bi_user_action

user_address_postalCode

STRING

qp_bi_user_action

user_address_country

STRING

qp_bi_user_action

user_address_countryCode

STRING

qp_bi_user_action

user_isGuest

STRING

qp_bi_user_action

user_age

INTEGER

qp_bi_user_action

user_loyalty_tierPoints

INTEGER

qp_bi_user_action

user_loyalty_membershipType

STRING

qp_bi_user_action

user_loyalty_membershipPoints

INTEGER

qp_bi_user_action

user_loyalty_id

STRING

qp_bi_user_action

user_loyalty_tier

STRING

qp_bi_user_action

user_phoneNumber

STRING

qp_bi_user_action

user_countryCallingCode

STRING

qp_bi_user_action

user_deviceId

STRING

qp_bi_user_action

user_balance_value

FLOAT

qp_bi_user_action

user_balance_currency

STRING

qp_bi_user_action

user_balance_baseValue

FLOAT

qp_bi_user_action

user_balance_baseCurrency

STRING

qp_bi_user_action

user_balance_universalValue

FLOAT

qp_bi_user_action

user_balance_universalCurrency

STRING

qp_bi_user_action

entrance_id

STRING

qp_bi_user_action

session_id

STRING

qp_bi_user_action

view_id

STRING

qp_bi_user_action

qp_bi_view_name

STRING

qp_bi_user_action

TIMESTAMP

qp_bi_transaction

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
transaction_total	FLOAT	Equal to betslip_total_baseValue
context_id	STRING	Visitor ID based on browser cookie
transaction_id	STRING	Order ID
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in their lifetime (across all sessions)
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all sessions) up to the current view
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone
meta_ts	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
betslip_quantity	INTEGER	Quantity of betslips
betslip_tax_baseValue	FLOAT	Total tax paid for order in base currency
betslip_total_baseValue	FLOAT	Total order value in base currency
betslip_discount_baseValue	FLOAT	Total discount value in base currency
betslip_totalStake_baseValue	FLOAT	Total stake value in base currency
betslip_totalPotential_baseValue	FLOAT	Total potential value in base currency
session_id	STRING	Unique session ID
entrance_id	STRING	Unique entrance ID
view_id	STRING	Unique view ID
property_event_ts	TIMESTAMP	The timestamp of event in client property local time

Column Name

BQ Data Type

Column Description

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

transaction_total

FLOAT

Equal to betslip_total_baseValue

context_id

STRING

Visitor ID based on browser cookie

transaction_id

STRING

Order ID

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_entranceNumber

INTEGER

The sequential entrance number of a user in their lifetime (across all sessions)

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all sessions) up to the current view

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone

meta_ts

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

betslip_quantity

INTEGER

Quantity of betslips

betslip_tax_baseValue

FLOAT

Total tax paid for order in base currency

betslip_total_baseValue

FLOAT

Total order value in base currency

betslip_discount_baseValue

FLOAT

Total discount value in base currency

betslip_totalStake_baseValue

FLOAT

Total stake value in base currency

betslip_totalPotential_baseValue

FLOAT

Total potential value in base currency

session_id

STRING

Unique session ID

entrance_id

STRING

Unique entrance ID

view_id

STRING

Unique view ID

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

Travel

qp_bi_package_item

Column Name	BQ Data Type	Column Description
qp_bi_view_name	STRING	Name of BI view, static field
ts	TIMESTAMP	The time the record was materialised. NB do not use for purpose of analytics
property_event_ts	TIMESTAMP	The timestamp of event in client property local time
context_id	STRING	Visitor ID based on browser cookie
context_viewNumber	INTEGER	The sequential pageview number of a user in their lifetime (across all sessions)
meta_type	STRING	The name of the original QP event the row comes from
package_id	STRING	Unique ID of package
meta_id	STRING	The original ID of event
context_conversionNumber	INTEGER	The number of conversions visitor has had (across all sessions) up to the current view
context_sessionNumber	INTEGER	The sequential session number of a user in their lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in their lifetime (across all sessions)
meta_recordDate	STRING	The date pageview was received by the server in client property’s timezone
meta_ts	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_serverTs	INTEGER	Epoch time of event - as received by server. NB epoch time is in UTC timezone
meta_trackingId	STRING	The ID used that uniquely identifies client brand in Qubit
transaction_id	STRING	Transaction ID if user submitted order on the page view
eventType	STRING	Original QP event type
packageItem_accommodation_address_country	STRING	Country of accommodation - if visitor booked accommodation as part of package
packageItem_accommodation_address_countryCode	STRING	Country code of accommodation - if visitor booked accommodation as part of package
packageItem_accommodation_boardBasis	STRING	Board basis - if visitor booked accommodation as part of package
packageItem_accommodation_id	STRING	Accommodation ID - if visitor booked accommodation as part of package
packageItem_accommodation_maxOccupancy	INTEGER	Accommodation Max Occupancy - if visitor booked accommodation as part of package
packageItem_accommodation_name	STRING	Accommodation Name - if visitor booked accommodation as part of package
packageItem_accommodation_normalizedRating	FLOAT	Accommodation Rating - if visitor booked accommodation as part of package
packageItem_accommodation_rooms	INTEGER	Accommodation number of rooms - if visitor booked accommodation as part of package
packageItem_accommodation_stars	INTEGER	Accommodation stars - if visitor booked accommodation as part of package
packageItem_ancillary_ancillaryType	STRING	Ancillary type - if one was purchased
packageItem_ancillary_insurance_type	STRING	Inusrance type ancillary - if one was purchased
packageItem_ancillary_luggage_type	STRING	Luggage type ancillary - if one was purchased
packageItem_ancillary_meal_type	STRING	Meal type ancillary - if one was purchased
packageItem_ancillary_parking_type	STRING	Parking type ancillary - if one was purchased
packageItem_ancillary_pet_type	STRING	Pet type ancillary - if one was purchased
packageItem_ancillary_priority_type	STRING	Priority type ancillary - if one was purchased
packageItem_ancillary_seats_type	STRING	Seats type ancillary - if one was purchased
packageItem_ancillary_sportsEquipment_type	STRING	Sports equipment type ancillary - if one was purchased
packageItem_ancillary_towel_quantity	INTEGER	Towel quantity ancillary - if one was purchased
packageItem_ancillary_transfers_type	STRING	Transfer type - if one was purchased
packageItem_ancillary_transfers_vendor	STRING	Transfer vendor ancillary - if transfer was purchased
packageItem_ancillary_vehicleRental_type	STRING	Transfer vehicle rental type - if applicable
packageItem_ancillary_wifi_id	STRING	Wifi ID- if applicable
packageItem_id	STRING	Package item ID
packageItem_journey_destinationCode	STRING	destination code - if package included journey
packageItem_journey_destinationCountry	STRING	destination country - if package included journey
packageItem_journey_destinationName	STRING	destination name - if package included journey
packageItem_journey_id	STRING	Journey ID - if package included journey
packageItem_journey_journeyType	STRING	Journey type - if package included journey
packageItem_journey_operator	STRING	Journey operator - if package included journey
packageItem_journey_originCode	STRING	Origin code - if package included journey
packageItem_journey_originCountry	STRING	Origin country - if package included journey
packageItem_journey_originName	STRING	Origin name - if package included journey
packageItem_journey_price_baseValue	FLOAT	Journey price - if package included journey
packageItem_journey_travelTime	INTEGER	Journey time - if package included journey
packageItem_onSale	BOOLEAN	True if package on sale
packageItem_originalPrice_baseValue	FLOAT	Total package original price
packageItem_packageItemType	STRING	Type of item being part of the package
packageItem_packageType	STRING	Package type
packageItem_price_baseValue	FLOAT	Total package price
package_boardBasis	STRING	Package board basis if applicable
package_deposit_baseValue	FLOAT	Package deposit
package_discount_baseValue	FLOAT	Package discount
package_duration	INTEGER	Package duration
package_holidayType	STRING	Package holiday type
package_lineOfBusiness	STRING	Package line of business
package_name	STRING	Package name
package_numberOfAdults	INTEGER	Number of adults
package_numberOfChildren	INTEGER	Number of children
package_numberOfInfants	INTEGER	Number of infants
package_originalPrice_baseValue	FLOAT	Package total original price
package_packageProvider	STRING	Package provider
package_price_baseCurrency	STRING	Package price currency
package_price_baseValue	FLOAT	Package price in base currency
package_subtotalIncludingTax_baseValue	FLOAT	Package subtotal including tax
package_subtotal_baseValue	FLOAT	Package subtotal excluding tax
package_tax_baseValue	FLOAT	Package tax
package_total_baseValue	FLOAT	Package total value
package_type	STRING	Type of package
package_startTs	INTEGER	Package start timestamp (in epoch unix time)
unique_row_id	STRING	Unique ID for this row
view_id	STRING	Unique view ID
session_id	STRING	Unique session ID
entrance_id	STRING	Unique entrance ID
interaction_type	STRING	Type of interaction

Column Name

BQ Data Type

Column Description

qp_bi_view_name

STRING

Name of BI view, static field

TIMESTAMP

The time the record was materialised. NB do not use for purpose of analytics

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

The sequential pageview number of a user in their lifetime (across all sessions)

meta_type

STRING

The name of the original QP event the row comes from

package_id

STRING

Unique ID of package

meta_id

STRING

The original ID of event

context_conversionNumber

INTEGER

The number of conversions visitor has had (across all sessions) up to the current view

context_sessionNumber

INTEGER

The sequential session number of a user in their lifetime (across all sessions)

context_entranceNumber

INTEGER

The sequential entrance number of a user in their lifetime (across all sessions)

meta_recordDate

STRING

The date pageview was received by the server in client property’s timezone

meta_ts

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_serverTs

INTEGER

Epoch time of event - as received by server. NB epoch time is in UTC timezone

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

transaction_id

STRING

Transaction ID if user submitted order on the page view

eventType

STRING

Original QP event type

packageItem_accommodation_address_country

STRING

Country of accommodation - if visitor booked accommodation as part of package

packageItem_accommodation_address_countryCode

STRING

Country code of accommodation - if visitor booked accommodation as part of package

packageItem_accommodation_boardBasis

STRING

Board basis - if visitor booked accommodation as part of package

packageItem_accommodation_id

STRING

Accommodation ID - if visitor booked accommodation as part of package

packageItem_accommodation_maxOccupancy

INTEGER

Accommodation Max Occupancy - if visitor booked accommodation as part of package

packageItem_accommodation_name

STRING

Accommodation Name - if visitor booked accommodation as part of package

packageItem_accommodation_normalizedRating

FLOAT

Accommodation Rating - if visitor booked accommodation as part of package

packageItem_accommodation_rooms

INTEGER

Accommodation number of rooms - if visitor booked accommodation as part of package

packageItem_accommodation_stars

INTEGER

Accommodation stars - if visitor booked accommodation as part of package

packageItem_ancillary_ancillaryType

STRING

Ancillary type - if one was purchased

packageItem_ancillary_insurance_type

STRING

Inusrance type ancillary - if one was purchased

packageItem_ancillary_luggage_type

STRING

Luggage type ancillary - if one was purchased

packageItem_ancillary_meal_type

STRING

Meal type ancillary - if one was purchased

packageItem_ancillary_parking_type

STRING

Parking type ancillary - if one was purchased

packageItem_ancillary_pet_type

STRING

Pet type ancillary - if one was purchased

packageItem_ancillary_priority_type

STRING

Priority type ancillary - if one was purchased

packageItem_ancillary_seats_type

STRING

Seats type ancillary - if one was purchased

packageItem_ancillary_sportsEquipment_type

STRING

Sports equipment type ancillary - if one was purchased

packageItem_ancillary_towel_quantity

INTEGER

Towel quantity ancillary - if one was purchased

packageItem_ancillary_transfers_type

STRING

Transfer type - if one was purchased

packageItem_ancillary_transfers_vendor

STRING

Transfer vendor ancillary - if transfer was purchased

packageItem_ancillary_vehicleRental_type

STRING

Transfer vehicle rental type - if applicable

packageItem_ancillary_wifi_id

STRING

Wifi ID- if applicable

packageItem_id

STRING

Package item ID

packageItem_journey_destinationCode

STRING

destination code - if package included journey

packageItem_journey_destinationCountry

STRING

destination country - if package included journey

packageItem_journey_destinationName

STRING

destination name - if package included journey

packageItem_journey_id

STRING

Journey ID - if package included journey

packageItem_journey_journeyType

STRING

Journey type - if package included journey

packageItem_journey_operator

STRING

Journey operator - if package included journey

packageItem_journey_originCode

STRING

Origin code - if package included journey

packageItem_journey_originCountry

STRING

Origin country - if package included journey

packageItem_journey_originName

STRING

Origin name - if package included journey

packageItem_journey_price_baseValue

FLOAT

Journey price - if package included journey

packageItem_journey_travelTime

INTEGER

Journey time - if package included journey

packageItem_onSale

BOOLEAN

True if package on sale

packageItem_originalPrice_baseValue

FLOAT

Total package original price

packageItem_packageItemType

STRING

Type of item being part of the package

packageItem_packageType

STRING

Package type

packageItem_price_baseValue

FLOAT

Total package price

package_boardBasis

STRING

Package board basis if applicable

package_deposit_baseValue

FLOAT

Package deposit

package_discount_baseValue

FLOAT

Package discount

package_duration

INTEGER

Package duration

package_holidayType

STRING

Package holiday type

package_lineOfBusiness

STRING

Package line of business

package_name

STRING

Package name

package_numberOfAdults

INTEGER

Number of adults

package_numberOfChildren

INTEGER

Number of children

package_numberOfInfants

INTEGER

Number of infants

package_originalPrice_baseValue

FLOAT

Package total original price

package_packageProvider

STRING

Package provider

package_price_baseCurrency

STRING

Package price currency

package_price_baseValue

FLOAT

Package price in base currency

package_subtotalIncludingTax_baseValue

FLOAT

Package subtotal including tax

package_subtotal_baseValue

FLOAT

Package subtotal excluding tax

package_tax_baseValue

FLOAT

Package tax

package_total_baseValue

FLOAT

Package total value

package_type

STRING

Type of package

package_startTs

INTEGER

Package start timestamp (in epoch unix time)

unique_row_id

STRING

Unique ID for this row

view_id

STRING

Unique view ID

session_id

STRING

Unique session ID

entrance_id

STRING

Unique entrance ID

interaction_type

STRING

Type of interaction

qp_bi_transaction

Column Name	BQ Data Type	Column Description
context_id	STRING	Visitor ID
transaction_id	STRING	Order ID (if order was made on page)
context_sessionNumber	INTEGER	Sequential session number
context_entranceNumber	INTEGER	Sequential entrance number
context_viewNumber	INTEGER	Sequential view number
meta_recordDate	STRING	Date record was received (in property timezone)
meta_trackingId	STRING	Brand - unique property ID used by Qubit
meta_ts	INTEGER	Timestamp of pageview emitted by visitor’s browser (in epoch unix time)
context_conversionNumber	INTEGER	Sequential conversion number
package_id	STRING	Id of package
package_name	STRING	Name of package
package_packageProvider	STRING	Provider of package
package_type	STRING	Type of package
package_brochureCode	STRING	Brochure code of package
package_boardBasis	STRING	Board basis of package
package_holidayType	STRING	Holiday type of package
package_lineOfBusiness	STRING	Line of business
package_price_baseValue	FLOAT	Package price value
package_price_baseCurrency	STRING	Base currency
package_tax_baseValue	FLOAT	Package tax value
package_numberOfAdults	INTEGER	Number of adults in package
package_numberOfChildren	INTEGER	Number of children
package_numberOfInfants	INTEGER	Number of infants
package_totalNumber	INTEGER	Total number of people in package
package_duration	INTEGER	Total duration of holiday
package_originalPrice_baseValue	FLOAT	Total original price value
package_deposit_baseValue	FLOAT	Deposit value
package_subtotal_baseValue	FLOAT	Subtotal value
package_total_baseValue	FLOAT	Package total
package_discount_baseValue	FLOAT	Package discount
package_subtotalIncludingTax_baseValue	FLOAT	Package subtotal including tax
meta_serverTs	INTEGER	Timestamp of pageview as received by server (in unix epoch time)
session_id	STRING	Unique session ID
entrance_id	STRING	Unique entrance ID
view_id	STRING	Unique view ID
transaction_total	FLOAT	Transaction total paid
qp_bi_view_name	STRING	Name of business view
ts	TIMESTAMP	Date and time when table was materialised. This is NOT time of pageview
property_event_ts	TIMESTAMP	Date and time event was received in property’s local time - can be used for analytics

Column Name

BQ Data Type

Column Description

context_id

STRING

Visitor ID

transaction_id

STRING

Order ID (if order was made on page)

context_sessionNumber

INTEGER

Sequential session number

context_entranceNumber

INTEGER

Sequential entrance number

context_viewNumber

INTEGER

Sequential view number

meta_recordDate

STRING

Date record was received (in property timezone)

meta_trackingId

STRING

Brand - unique property ID used by Qubit

meta_ts

INTEGER

Timestamp of pageview emitted by visitor’s browser (in epoch unix time)

context_conversionNumber

INTEGER

Sequential conversion number

package_id

STRING

Id of package

package_name

STRING

Name of package

package_packageProvider

STRING

Provider of package

package_type

STRING

Type of package

package_brochureCode

STRING

Brochure code of package

package_boardBasis

STRING

Board basis of package

package_holidayType

STRING

Holiday type of package

package_lineOfBusiness

STRING

Line of business

package_price_baseValue

FLOAT

Package price value

package_price_baseCurrency

STRING

Base currency

package_tax_baseValue

FLOAT

Package tax value

package_numberOfAdults

INTEGER

Number of adults in package

package_numberOfChildren

INTEGER

Number of children

package_numberOfInfants

INTEGER

Number of infants

package_totalNumber

INTEGER

Total number of people in package

package_duration

INTEGER

Total duration of holiday

package_originalPrice_baseValue

FLOAT

Total original price value

package_deposit_baseValue

FLOAT

Deposit value

package_subtotal_baseValue

FLOAT

Subtotal value

package_total_baseValue

FLOAT

Package total

package_discount_baseValue

FLOAT

Package discount

package_subtotalIncludingTax_baseValue

FLOAT

Package subtotal including tax

meta_serverTs

INTEGER

Timestamp of pageview as received by server (in unix epoch time)

session_id

STRING

Unique session ID

entrance_id

STRING

Unique entrance ID

view_id

STRING

Unique view ID

transaction_total

FLOAT

Transaction total paid

qp_bi_view_name

STRING

Name of business view

TIMESTAMP

Date and time when table was materialised. This is NOT time of pageview

property_event_ts

TIMESTAMP

Date and time event was received in property’s local time - can be used for analytics

Live Tap derived measures - semantic model subject areas

Attribution

Subject Area Calculation Description Field Type SQL Expression Source View #1 Source View #2

Subject Area	Calculation	Description	Field Type	SQL Expression	Source View #1
Attribution	Attribution Entry Count	- Number of unique entries * QP fields: dervied	Measure	COUNT(DISTINCT qp_bi_attribution.entry_id, 1000000)	`qp_bi_attribution`
Attribution	Attribution Order Count	- Number of attribution orders * N.B. An order in attribution is a set of transactions processed in the same session * QP fields: derived	Measure	EXACT_COUNT_DISTINCT( qp_bi_attribution.order_ids_in_purchase_cycle)	`qp_bi_attribution`
Attribution	Behavioral Value	- Monetary value attributed to an entry based on the proportion of pageviews in current entry compared to the number of pageviews in all entries in a purchase cycle * The more pageviews an entry had, the higher the behavioral value * QP fields: derived - basket_total_baseValue	Measure	SUM(qp_bi_attribution.behavioural_value / qp_bi_attribution.pageviews_in_entry)	`qp_bi_attribution`
Attribution	Distinct Referrer Types	- Number of distinct referrers * QP fields: referrer_type	Measure	EXACT_COUNT_DISTINCT( qp_bi_attribution.true_referrer_type)	`qp_bi_attribution`
Attribution	Even Click Value	- Monetary value attributed to an entry * The value of an entry based ratio of this entry to the total number of entries * QP fields: derived - basket_total_baseValue	Measure	SUM(qp_bi_attribution.even_click_value / qp_bi_attribution.pageviews_in_entry )	`qp_bi_attribution`
Attribution	First Click Value	- Monetary value attributed to an entry * Set to full conversion value if the entry was first * QP fields: derived - basket_total_baseValue	Measure	SUM(qp_bi_attribution.first_click_value / qp_bi_attribution.pageviews_in_entry )	`qp_bi_attribution`
Attribution	Last Click Value	- Monetary value attributed to an entry * Set to full conversion value if the entry was last * QP fields: derived - basket_total_baseValue	Measure	SUM(qp_bi_attribution.last_click_value / qp_bi_attribution.pageviews_in_entry )	`qp_bi_attribution`

Attribution

Attribution Entry Count

- Number of unique entries

* QP fields: dervied

Measure

COUNT(DISTINCT qp_bi_attribution.entry_id, 1000000)

qp_bi_attribution

Attribution

Attribution Order Count

- Number of attribution orders

* N.B. An order in attribution is a set of transactions processed in the same session

* QP fields: derived

Measure

EXACT_COUNT_DISTINCT( qp_bi_attribution.order_ids_in_purchase_cycle)

qp_bi_attribution

Attribution

Behavioral Value

- Monetary value attributed to an entry based on the proportion of pageviews in current entry compared to the number of pageviews in all entries in a purchase cycle

* The more pageviews an entry had, the higher the behavioral value

* QP fields: derived - basket_total_baseValue

Measure

SUM(qp_bi_attribution.behavioural_value / qp_bi_attribution.pageviews_in_entry)

qp_bi_attribution

Attribution

Distinct Referrer Types

- Number of distinct referrers

* QP fields: referrer_type

Measure

EXACT_COUNT_DISTINCT( qp_bi_attribution.true_referrer_type)

qp_bi_attribution

Attribution

Even Click Value

- Monetary value attributed to an entry

* The value of an entry based ratio of this entry to the total number of entries

* QP fields: derived - basket_total_baseValue

Measure

SUM(qp_bi_attribution.even_click_value / qp_bi_attribution.pageviews_in_entry )

qp_bi_attribution

Attribution

First Click Value

- Monetary value attributed to an entry

* Set to full conversion value if the entry was first

* QP fields: derived - basket_total_baseValue

Measure

SUM(qp_bi_attribution.first_click_value / qp_bi_attribution.pageviews_in_entry )

qp_bi_attribution

Attribution

Last Click Value

- Monetary value attributed to an entry

* Set to full conversion value if the entry was last

* QP fields: derived - basket_total_baseValue

Measure

SUM(qp_bi_attribution.last_click_value / qp_bi_attribution.pageviews_in_entry )

qp_bi_attribution

Entrances

Subject Area Calculation Description Field Type SQL Expression Source View #1 Source View #2

Subject Area	Calculation	Description	Field Type	SQL Expression	Source View #1	Source View #2
Entrances	Entrance Visitors	- Count of unique context_id, a unique Id to identify the visitor * If above 1.000.000, the result is approximated * QP fields:context_id	Measure	COUNT(DISTINCT qp_bi_entrance.context_id, 1000000)	`qp_bi_entrance`
Entrances	Entrances	- Count of unique combinations of a context_id and an entrance_number * If above 1.000.000, the result is approximated * QP fields: context_id, context_entranceNumber	Measure	COUNT(DISTINCT qp_bi_entrance.entrance_id, 1000000)	`qp_bi_entrance`

Entrances

Entrance Visitors

- Count of unique context_id, a unique Id to identify the visitor

* If above 1.000.000, the result is approximated

* QP fields:context_id

Measure

COUNT(DISTINCT qp_bi_entrance.context_id, 1000000)

qp_bi_entrance

Entrances

- Count of unique combinations of a context_id and an entrance_number

* If above 1.000.000, the result is approximated

* QP fields: context_id, context_entranceNumber

Measure

COUNT(DISTINCT qp_bi_entrance.entrance_id, 1000000)

qp_bi_entrance

Experiences

Subject Area Calculation Description Field Type SQL Expression Source View #1 Source View #2

Subject Area	Calculation	Description	Field Type	SQL Expression	Source View #1	Source View #2
Experiences	Distinct Experiences	- Count of unique experience_ids * If above 1.000.000, the result is approximated * QP fields: experienceId	Measure	COUNT(DISTINCT qp_bi_experience.experienceId, 1000000)	`qp_bi_experience`
Experiences	Experience Converters	- Count of unique context_ids on views that are labeled with any non-null transaction_id * If above 1.000.000, the result is approximated * Only for views on which an experience was seen or views that happened after an experience was seen * QP fields: context_id, transaction_id	Measure	COUNT(DISTINCT IF(qp_bi_transaction.transaction_id IS NOT NULL,qp_bi_experience.context_id,NULL), 1000000)	`qp_bi_transaction`	'qp_bi_experience'
Experiences	Experience Views	- Count of unique combinations of a context_id and a view_number * If above 1.000.000, the result is approximated * Only for views on which an experience was seen or views that happened after an experience was seen * QP fields: context_id, context_viewNumber	Measure	COUNT(DISTINCT qp_bi_experience.view_id, 1000)	`qp_bi_experience`
Experiences	Experience Visitors	- Count of unique context_ids * If above 1.000.000, the result is approximated * Only for views on which an experience was seen or views that happened after an experience was seen * QP fields: context_id, experienceId	Measure	COUNT(DISTINCT IF(qp_bi_experience.experienceId IS NOT NULL,qp_bi_experience.context_id,NULL), 1000000)	`qp_bi_experience`
Experiences	Revenue per Visitor	- Sum of transaction_total divided by count of unique context_ids * If count of context_ids is above 1.000.000, the result is approximated * Only for views on which an experience was seen or views that happened after an experience was seen * QP fields: basket_total_baseValue, context_id, experienceId	Measure	(COALESCE(CAST(SUM(FLOAT(REGEXP_EXTRACT(UNIQUE(CONCAT(STRING(qp_bi_transaction.transaction_id ), '\|\|', STRING(INTEGER(CASE WHEN qp_bi_experience.experienceId IS NOT NULL THEN qp_bi_transaction.transaction_total END * 1000000)))), r'\|\|(-?\d+)$')) / 1000000) AS FLOAT), 0)) / (COUNT(DISTINCT IF(qp_bi_experience.experienceId IS NOT NULL,qp_bi_experience.context_id,NULL), 1000000))	`qp_bi_transaction`	'qp_bi_experience'
Experiences	Transaction Total	- Sum of transaction_total * Only for views on which an experience was seen or views that happened after an experience was seen * QP fields: experienceId, basket_total_baseValue	Measure	COALESCE(CAST(SUM(FLOAT(REGEXP_EXTRACT(UNIQUE(CONCAT(STRING(qp_bi_transaction.transaction_id ), '\|\|', STRING(INTEGER(CASE WHEN qp_bi_experience.experienceId IS NOT NULL THEN qp_bi_transaction.transaction_total END * 1000000)))), r'\|\|(-?\d+)$')) / 1000000) AS FLOAT), 0)	`qp_bi_transaction`	'qp_bi_experience'
Experiences	Transactions	- Count of unique transaction_ids (always exact count) * Only for views on which an experience was seen or views that happened after an experience was seen * QP fields: transaction_id, experienceId	Measure	EXACT_COUNT_DISTINCT(CASE WHEN qp_bi_experience.experienceId IS NOT NULL THEN qp_bi_transaction.transaction_id END)	`qp_bi_experience`	'qp_bi_transaction'
Experiences	Visitor Conversion Rate	- Share of unique visitors on views that are labeled with any non-null transaction_id in all visitors * For counting, when figures are above 1.000.000, the result is approximated * Only for views on which an experience was seen or views that happened after an experience was seen * QP fields: context_id, transaction_id	Measure	(COUNT(DISTINCT IF(qp_bi_transaction.transaction_id IS NOT NULL,qp_bi_experience.context_id,NULL), 1000000)) / COUNT(DISTINCT qp_bi_experience.context_id, 1000000)	`qp_bi_transaction`	'qp_bi_experience'

Experiences

Distinct Experiences

- Count of unique experience_ids

* If above 1.000.000, the result is approximated

* QP fields: experienceId

Measure

COUNT(DISTINCT qp_bi_experience.experienceId, 1000000)

qp_bi_experience

Experiences

Experience Converters

- Count of unique context_ids on views that are labeled with any non-null transaction_id

* If above 1.000.000, the result is approximated

* Only for views on which an experience was seen or views that happened after an experience was seen

* QP fields: context_id, transaction_id

Measure

COUNT(DISTINCT IF(qp_bi_transaction.transaction_id IS NOT NULL,qp_bi_experience.context_id,NULL), 1000000)

qp_bi_transaction

'qp_bi_experience'

Experiences

Experience Views

- Count of unique combinations of a context_id and a view_number

* If above 1.000.000, the result is approximated

* Only for views on which an experience was seen or views that happened after an experience was seen

* QP fields: context_id, context_viewNumber

Measure

COUNT(DISTINCT qp_bi_experience.view_id, 1000)

qp_bi_experience

Experiences

Experience Visitors

- Count of unique context_ids

* If above 1.000.000, the result is approximated

* Only for views on which an experience was seen or views that happened after an experience was seen

* QP fields: context_id, experienceId

Measure

COUNT(DISTINCT IF(qp_bi_experience.experienceId IS NOT NULL,qp_bi_experience.context_id,NULL), 1000000)

qp_bi_experience

Experiences

Revenue per Visitor

- Sum of transaction_total divided by count of unique context_ids

* If count of context_ids is above 1.000.000, the result is approximated

* Only for views on which an experience was seen or views that happened after an experience was seen

* QP fields: basket_total_baseValue, context_id, experienceId

Measure

(COALESCE(CAST(SUM(FLOAT(REGEXP_EXTRACT(UNIQUE(CONCAT(STRING(qp_bi_transaction.transaction_id ), '||', STRING(INTEGER(CASE WHEN qp_bi_experience.experienceId IS NOT NULL THEN qp_bi_transaction.transaction_total END * 1000000)))), r'||(-?\d+)$')) / 1000000) AS FLOAT), 0)) / (COUNT(DISTINCT IF(qp_bi_experience.experienceId IS NOT NULL,qp_bi_experience.context_id,NULL), 1000000))

qp_bi_transaction

'qp_bi_experience'

Experiences

Transaction Total

- Sum of transaction_total

* Only for views on which an experience was seen or views that happened after an experience was seen

* QP fields: experienceId, basket_total_baseValue

Measure

COALESCE(CAST(SUM(FLOAT(REGEXP_EXTRACT(UNIQUE(CONCAT(STRING(qp_bi_transaction.transaction_id ), '||', STRING(INTEGER(CASE WHEN qp_bi_experience.experienceId IS NOT NULL THEN qp_bi_transaction.transaction_total END * 1000000)))), r'||(-?\d+)$')) / 1000000) AS FLOAT), 0)

qp_bi_transaction

'qp_bi_experience'

Experiences

Transactions

- Count of unique transaction_ids (always exact count)

* Only for views on which an experience was seen or views that happened after an experience was seen

* QP fields: transaction_id, experienceId

Measure

EXACT_COUNT_DISTINCT(CASE WHEN qp_bi_experience.experienceId IS NOT NULL THEN qp_bi_transaction.transaction_id END)

qp_bi_experience

'qp_bi_transaction'

Experiences

Visitor Conversion Rate

- Share of unique visitors on views that are labeled with any non-null transaction_id in all visitors

* For counting, when figures are above 1.000.000, the result is approximated

* Only for views on which an experience was seen or views that happened after an experience was seen

* QP fields: context_id, transaction_id

Measure

(COUNT(DISTINCT IF(qp_bi_transaction.transaction_id IS NOT NULL,qp_bi_experience.context_id,NULL), 1000000)) / COUNT(DISTINCT qp_bi_experience.context_id, 1000000)

qp_bi_transaction

'qp_bi_experience'

Goal achieved

Subject Area Calculation Description Field Type SQL Expression Source View #1 Source View #2

Subject Area	Calculation	Description	Field Type	SQL Expression	Source View #1
Goal Achieved	Distinct Goals	- Count of unique goal_ids * Only for views on which a goal was achieved or views that happened after a goal was achieved * QP fields: goalId	Measure	COUNT(DISTINCT qp_bi_goal_achieved.goalId, 1000000)	`qp_bi_goal_achieved`
Goal Achieved	Goal Achieved Views	- Count of unique views * If above 1.000.000, the result is approximated * Only for views on which a goal was achieved or views that happened after a goal was achieved * QP fields: context_id, context_viewNumber, goalId	Measure	COUNT(DISTINCT IF(qp_bi_goal_achieved.goalId IS NOT NULL,qp_bi_goal_achieved.view_id,NULL), 1000000)	`qp_bi_goal_achieved`
Goal Achieved	Goal Achieved Visitors	- Count of unique context_ids * If above 1.000.000, the result is approximated * Only for views on which a goal was achieved or views that happened after a goal was achieved * QP fields: context_id, goalId	Measure	COUNT(DISTINCT IF(qp_bi_goal_achieved.goalId IS NOT NULL,qp_bi_goal_achieved.context_id,NULL), 1000000)	`qp_bi_goal_achieved`

Goal Achieved

Distinct Goals

- Count of unique goal_ids

* Only for views on which a goal was achieved or views that happened after a goal was achieved

* QP fields: goalId

Measure

COUNT(DISTINCT qp_bi_goal_achieved.goalId, 1000000)

qp_bi_goal_achieved

Goal Achieved

Goal Achieved Views

- Count of unique views

* If above 1.000.000, the result is approximated

* Only for views on which a goal was achieved or views that happened after a goal was achieved

* QP fields: context_id, context_viewNumber, goalId

Measure

COUNT(DISTINCT IF(qp_bi_goal_achieved.goalId IS NOT NULL,qp_bi_goal_achieved.view_id,NULL), 1000000)

qp_bi_goal_achieved

Goal Achieved

Goal Achieved Visitors

- Count of unique context_ids

* If above 1.000.000, the result is approximated

* Only for views on which a goal was achieved or views that happened after a goal was achieved

* QP fields: context_id, goalId

Measure

COUNT(DISTINCT IF(qp_bi_goal_achieved.goalId IS NOT NULL,qp_bi_goal_achieved.context_id,NULL), 1000000)

qp_bi_goal_achieved

Product interaction and sales

Subject Area Calculation Description Field Type SQL Expression Source View #1 Source View #2

Subject Area	Calculation	Description	Field Type	SQL Expression	Source View #1
Product Interaction and Sales	Basket Views	- Count of unique combinations of a context_id and a view_number * If above 1.000.000, the result is approximated * Only for views flagged with 'View with Product in Basket' interaction type * QP fields: meta_type, context_id, context_viewNumber	Measure	COUNT(DISTINCT(IF(qp_bi_product.meta_type LIKE '%ecBasketItem', qp_bi_product.view_id,NULL)), 1000000)	`qp_bi_product`
Product Interaction and Sales	Converters	- Count of unique context_ids on views that are labeled with 'Product Purchase' interaction type * If above 1.000.000, the result is approximated * QP fields: meta_type, context_id, transaction_id	Measure	COUNT(DISTINCT IF(qp_bi_product.transaction_id IS NOT NULL, qp_bi_product.context_id, NULL), 1000000)	`qp_bi_product`
Product Interaction and Sales	Distinct Product Ids	- Count of unique product_ids that were displayed, interacted with, or purchased * If above 1.000.000, the result is approximated * QP fields: product_productId	Measure	COUNT(DISTINCT qp_bi_product.product_productId, 1000000)	`qp_bi_product`
Product Interaction and Sales	Product Orders	- Count of unique transaction_ids (always exact count) * QP fields: transaction_id	Measure	EXACT_COUNT_DISTINCT(qp_bi_product.transaction_id)	`qp_bi_product`
Product Interaction and Sales	Product Purchased Value	- Sum of final price on views that are labeled with 'Product Purchase' interaction type * QP fields: transaction_id, product_price_baseValue	Measure	SUM(IF(qp_bi_product.transaction_id IS NOT NULL, qp_bi_product.product_price_baseValue ,0))	`qp_bi_product`
Product Interaction and Sales	Product Views	- Count of unique combinations of a context_id and a view_number * If above 1.000.000, the result is approximated * Only for views flagged with 'Product Detail View' or 'Product Listing View' interaction type * QP fields: meta_type, context_id, context_viewNumber	Measure	COUNT(DISTINCT(IF(qp_bi_product.meta_type LIKE '%ecProduct', qp_bi_product.view_id,NULL)), 1000000)	`qp_bi_product`
Product Interaction and Sales	Product Visitors	- Count of unique context_ids * If above 1.000.000, the result is approximated * Only for views flagged with 'Product Detail View' or 'Product Listing View' interaction type * QP fields: meta_type, context_id	Measure	COUNT(DISTINCT qp_bi_product.context_id, 1000000)	`qp_bi_product`

Product Interaction and Sales

Basket Views

- Count of unique combinations of a context_id and a view_number

* If above 1.000.000, the result is approximated

* Only for views flagged with 'View with Product in Basket' interaction type

* QP fields: meta_type, context_id, context_viewNumber

Measure

COUNT(DISTINCT(IF(qp_bi_product.meta_type LIKE '%ecBasketItem', qp_bi_product.view_id,NULL)), 1000000)

qp_bi_product

Product Interaction and Sales

Converters

- Count of unique context_ids on views that are labeled with 'Product Purchase' interaction type

* If above 1.000.000, the result is approximated

* QP fields: meta_type, context_id, transaction_id

Measure

COUNT(DISTINCT IF(qp_bi_product.transaction_id IS NOT NULL, qp_bi_product.context_id, NULL), 1000000)

qp_bi_product

Product Interaction and Sales

Distinct Product Ids

- Count of unique product_ids that were displayed, interacted with, or purchased

* If above 1.000.000, the result is approximated

* QP fields: product_productId

Measure

COUNT(DISTINCT qp_bi_product.product_productId, 1000000)

qp_bi_product

Product Interaction and Sales

Product Orders

- Count of unique transaction_ids (always exact count)

* QP fields: transaction_id

Measure

EXACT_COUNT_DISTINCT(qp_bi_product.transaction_id)

qp_bi_product

Product Interaction and Sales

Product Purchased Value

- Sum of final price on views that are labeled with 'Product Purchase' interaction type

* QP fields: transaction_id, product_price_baseValue

Measure

SUM(IF(qp_bi_product.transaction_id IS NOT NULL, qp_bi_product.product_price_baseValue ,0))

qp_bi_product

Product Interaction and Sales

Product Views

- Count of unique combinations of a context_id and a view_number

* If above 1.000.000, the result is approximated

* Only for views flagged with 'Product Detail View' or 'Product Listing View' interaction type

* QP fields: meta_type, context_id, context_viewNumber

Measure

COUNT(DISTINCT(IF(qp_bi_product.meta_type LIKE '%ecProduct', qp_bi_product.view_id,NULL)), 1000000)

qp_bi_product

Product Interaction and Sales

Product Visitors

- Count of unique context_ids

* If above 1.000.000, the result is approximated

* Only for views flagged with 'Product Detail View' or 'Product Listing View' interaction type

* QP fields: meta_type, context_id

Measure

COUNT(DISTINCT qp_bi_product.context_id, 1000000)

qp_bi_product

Segment membership

Subject Area Calculation Description Field Type SQL Expression Source View #1 Source View #2

Subject Area	Calculation	Description	Field Type	SQL Expression	Source View #1	Source View #2
Segment Membership	Distinct Segments	- Count of unique segment_ids * If above 1.000.000, the result is approximated. QP fields: segmenId	Measure	COUNT(DISTINCT qp_bi_segment.segmentId, 1000000)	`qp_bi_segment`
Segment Membership	Revenue per Visitor	- Sum of transaction_total divided by count of unique context_ids * If count of context_ids is above 1.000.000, the result is approximated * Only for views labeled with any non-null segment_id * QP fields: basket_total_baseValue, context_id	Measure	(COALESCE(CAST(SUM(FLOAT(REGEXP_EXTRACT(UNIQUE(CONCAT(STRING(qp_bi_transaction.transaction_id ), '\|\|', STRING(INTEGER(CASE WHEN qp_bi_segment.segmentId IS NOT NULL THEN qp_bi_transaction.transaction_total END * 1000000)))), r'\|\|(-?\d+)$')) / 1000000) AS FLOAT), 0)) / (COUNT(DISTINCT IF(qp_bi_segment.segmentId IS NOT NULL,qp_bi_segment.context_id,NULL), 1000000))	`qp_bi_transaction`	'qp_bi_segment'
Segment Membership	Segment Converters	- Count of unique context_ids on views labeled with any non-null segment_id -If above 1.000.000, the result is approximated * QP fields: context_id, transaction_id	Measure	COUNT(DISTINCT IF(qp_bi_transaction.transaction_id IS NOT NULL,qp_bi_segment.context_id,NULL), 1000000)	`qp_bi_transaction`	'qp_bi_segment'
Segment Membership	Segment Views	- Count of unique combinations of a context_id and a view_number * If above 1.000.000, the result is approximated * Only for views labeled with any non-null segment_id * QP fields: context_id, context_viewNumber	Measure	COUNT(DISTINCT qp_bi_segment.view_id, 1000000)	`qp_bi_segment`
Segment Membership	Segment Visitors	- Count of unique context_ids * If above 1.000.000, the result is approximated * Only for views labeled with any non-null segment_id * QP fields: context_id	Measure	COUNT(DISTINCT IF(qp_bi_segment.segmentId IS NOT NULL,qp_bi_segment.context_id,NULL), 1000000)	`qp_bi_segment`
Segment Membership	Transaction Total	- Sum of transaction_total * Only for views labeled with any non-null segment_id * QP fields: basket_total_baseValue, segmentId	Measure	COALESCE(CAST(SUM(FLOAT(REGEXP_EXTRACT(UNIQUE(CONCAT(STRING(qp_bi_transaction.transaction_id ), '\|\|', STRING(INTEGER(CASE WHEN qp_bi_segment.segmentId IS NOT NULL THEN qp_bi_transaction.transaction_total END * 1000000)))), r'\|\|(-?\d+)$')) / 1000000) AS FLOAT), 0)	`qp_bi_transaction`	'qp_bi_segment'
Segment Membership	Transactions	- Count of unique transaction_ids (always exact count) * Only for views labeled with any non-null segment_id * QP fields: transaction_id, segmentId	Measure	EXACT_COUNT_DISTINCT(CASE WHEN qp_bi_segment.segmentId IS NOT NULL THEN qp_bi_transaction.transaction_id END)	`qp_bi_segment`	'qp_bi_transaction'
Segment Membership	Visitor Conversion Rate	- Share of unique visitors on views that are labeled with any non-null transaction_id in all visitors * For counting, when figures are above 1.000.000, the result is approximated * Only for views labeled with any non-null segment_id * QP fields: transaction_id, context_id	Measure	(COUNT(DISTINCT IF(qp_bi_transaction.transaction_id IS NOT NULL,qp_bi_segment.context_id,NULL), 1000000)) / COUNT(DISTINCT qp_bi_segment.context_id , 1000000)	`qp_bi_transaction`	'qp_bi_segment'

Segment Membership

Distinct Segments

- Count of unique segment_ids

* If above 1.000.000, the result is approximated. QP fields: segmenId

Measure

COUNT(DISTINCT qp_bi_segment.segmentId, 1000000)

qp_bi_segment

Segment Membership

Revenue per Visitor

- Sum of transaction_total divided by count of unique context_ids

* If count of context_ids is above 1.000.000, the result is approximated

* Only for views labeled with any non-null segment_id

* QP fields: basket_total_baseValue, context_id

Measure

(COALESCE(CAST(SUM(FLOAT(REGEXP_EXTRACT(UNIQUE(CONCAT(STRING(qp_bi_transaction.transaction_id ), '||', STRING(INTEGER(CASE WHEN qp_bi_segment.segmentId IS NOT NULL THEN qp_bi_transaction.transaction_total END * 1000000)))), r'||(-?\d+)$')) / 1000000) AS FLOAT), 0)) / (COUNT(DISTINCT IF(qp_bi_segment.segmentId IS NOT NULL,qp_bi_segment.context_id,NULL), 1000000))

qp_bi_transaction

'qp_bi_segment'

Segment Membership

Segment Converters

- Count of unique context_ids on views labeled with any non-null segment_id -If above 1.000.000, the result is approximated

* QP fields: context_id, transaction_id

Measure

COUNT(DISTINCT IF(qp_bi_transaction.transaction_id IS NOT NULL,qp_bi_segment.context_id,NULL), 1000000)

qp_bi_transaction

'qp_bi_segment'

Segment Membership

Segment Views

- Count of unique combinations of a context_id and a view_number

* If above 1.000.000, the result is approximated

* Only for views labeled with any non-null segment_id

* QP fields: context_id, context_viewNumber

Measure

COUNT(DISTINCT qp_bi_segment.view_id, 1000000)

qp_bi_segment

Segment Membership

Segment Visitors

- Count of unique context_ids

* If above 1.000.000, the result is approximated

* Only for views labeled with any non-null segment_id

* QP fields: context_id

Measure

COUNT(DISTINCT IF(qp_bi_segment.segmentId IS NOT NULL,qp_bi_segment.context_id,NULL), 1000000)

qp_bi_segment

Segment Membership

Transaction Total

- Sum of transaction_total

* Only for views labeled with any non-null segment_id

* QP fields: basket_total_baseValue, segmentId

Measure

COALESCE(CAST(SUM(FLOAT(REGEXP_EXTRACT(UNIQUE(CONCAT(STRING(qp_bi_transaction.transaction_id ), '||', STRING(INTEGER(CASE WHEN qp_bi_segment.segmentId IS NOT NULL THEN qp_bi_transaction.transaction_total END * 1000000)))), r'||(-?\d+)$')) / 1000000) AS FLOAT), 0)

qp_bi_transaction

'qp_bi_segment'

Segment Membership

Transactions

- Count of unique transaction_ids (always exact count)

* Only for views labeled with any non-null segment_id

* QP fields: transaction_id, segmentId

Measure

EXACT_COUNT_DISTINCT(CASE WHEN qp_bi_segment.segmentId IS NOT NULL THEN qp_bi_transaction.transaction_id END)

qp_bi_segment

'qp_bi_transaction'

Segment Membership

Visitor Conversion Rate

- Share of unique visitors on views that are labeled with any non-null transaction_id in all visitors

* For counting, when figures are above 1.000.000, the result is approximated

* Only for views labeled with any non-null segment_id

* QP fields: transaction_id, context_id

Measure

(COUNT(DISTINCT IF(qp_bi_transaction.transaction_id IS NOT NULL,qp_bi_segment.context_id,NULL), 1000000)) / COUNT(DISTINCT qp_bi_segment.context_id , 1000000)

qp_bi_transaction

'qp_bi_segment'

Sessions

Subject Area Calculation Description Field Type SQL Expression Source View #1 Source View #2

Subject Area	Calculation	Description	Field Type	SQL Expression	Source View #1	Source View #2
Sessions	Session Visitors	- Count of unique context_ids * If above 1.000.000, the result is approximated * QP fields: context_id	Measure	COUNT(DISTINCT qp_bi_session.context_id, 1000000)	`qp_bi_session`
Sessions	Sessions	- Count of unique combinations of a context_id and an session_number * If above 1.000.000, the result is approximated * QP fields: context_id, context_sessionNumber	Measure	COUNT(DISTINCT qp_bi_session.session_id, 1000000)	`qp_bi_session`

Sessions

Session Visitors

- Count of unique context_ids

* If above 1.000.000, the result is approximated

* QP fields: context_id

Measure

COUNT(DISTINCT qp_bi_session.context_id, 1000000)

qp_bi_session

Sessions

- Count of unique combinations of a context_id and an session_number

* If above 1.000.000, the result is approximated

* QP fields: context_id, context_sessionNumber

Measure

COUNT(DISTINCT qp_bi_session.session_id, 1000000)

qp_bi_session

Transactions

Subject Area Calculation Description Field Type SQL Expression Source View #1 Source View #2

Subject Area	Calculation	Description	Field Type	SQL Expression	Source View #1	Source View #2
Transactions	New Vs Returning Purchased Status	- Returns 'new' if this is the first purchase of the visitor, otherwise 'returning' * QP fields: context_conversionNumber	Dimension	IF(qp_bi_transaction.transaction_id IS NOT NULL, IF(qp_bi_transaction.context_conversionNumber > 0, "returning", "new" ), NULL)	`qp_bi_transaction`
Transactions	Weeks Since First Entry	- Number of weeks between the first view of a visitor and a certain transaction * QP fields: property_event_ts, meta_recordDate	Dimension	DATEDIFF(MSEC_TO_TIMESTAMP(qp_bi_transaction.property_event_ts), TIMESTAMPDATE((DATE(qp_bi_view.visitor_first_entry_date )))) / 7	`qp_bi_transaction`	'qp_bi_view'
Transactions	Average Order Value	- Average of transaction value of all transactions * QP fields: basket_total_baseValue	Measure	AVG(qp_bi_transaction.basket_total_baseValue)	`qp_bi_transaction`
Transactions	Average Products per Order	- Average value of Transaction Item Quantity divided by exact count of transaction_id * QP fields: basket_quantity, transaction_id	Measure	SUM(qp_bi_transaction.basket_quantity) / (EXACT_COUNT_DISTINCT(qp_bi_transaction.transaction_id))	`qp_bi_transaction`
Transactions	Converters	- Count of unique context_ids on views that are labeled with any non-null transaction_id * If above 1.000.000, the result is approximated * QP fields: context_id, transaction_id	Measure	COUNT(DISTINCT qp_bi_transaction.context_id, 1000000)	`qp_bi_transaction`
Transactions	Days Since Previous Purchase	- Number of days between the current transaction and previous transaction by the same visitor * QP fields: property_event_ts	Measure	AVG((qp_bi_transaction.property_event_ts - qp_bi_transaction.last_transaction_ts) / (10006060*24) )	`qp_bi_transaction`
Transactions	Hours Since Previous Purchase	- Number of hours between the current transaction and previous transaction by the same visitor * QP fields: property_event_ts	Measure	AVG((qp_bi_transaction.property_event_ts - qp_bi_transaction.last_transaction_ts) / (10006060) )	`qp_bi_transaction`
Transactions	Revenue per Converter	- Sum of transaction_total divided by count of unique context_ids * If count of context_ids is above 1.000.000, the result is approximated * Only for views that are labeled with any non-null transaction_id. QP fields: basket_total_baseValue, context_id	Measure	SUM(qp_bi_transaction.basket_total_baseValue) / COUNT(DISTINCT qp_bi_transaction.context_id, 1000000)	`qp_bi_transaction`
Transactions	Revenue per Visitor	- Sum of transaction_total divided by count of unique context_ids * If count of context_ids is above 1.000.000, the result is approximated * QP fields: basket_total_baseValue, context_id	Measure	(COALESCE(CAST(SUM(qp_bi_transaction.basket_total_baseValue ) AS FLOAT), 0)) / (COUNT(DISTINCT qp_bi_view.context_id, 1000000))	`qp_bi_transaction`	'qp_bi_view'
Transactions	Session Conversion Rate	- Share of unique sessions containing views that are labeled with any non-null transaction_id in all sessions * For counting, when figures are above 1.000.000, the result is approximated * QP fields: context_sessionNumber, context_id	Measure	COUNT(DISTINCT qp_bi_transaction.session_id, 1000000) / COUNT(DISTINCT qp_bi_view.session_id, 1000000)	`qp_bi_transaction`	'qp_bi_view'
Transactions	Transactions	- Count of unique transaction_ids (always exact count) * QP fields: transaction_id	Measure	EXACT_COUNT_DISTINCT(qp_bi_transaction.transaction_id)	`qp_bi_transaction`
Transactions	Visitor Conversion Rate	- Share of unique visitors on views that are labeled with any non-null transaction_id in all visitors * For counting, when figures are above 1.000.000, the result is approximated * QP fields: context_id	Measure	COUNT(DISTINCT qp_bi_transaction.context_id, 1000000) / COUNT(DISTINCT qp_bi_view.context_id, 1000000)	`qp_bi_transaction`	'qp_bi_view'
Transactions	Weeks Since Previous Purchase	- Number of weeks between the current transaction and previous transaction by the same visitor * QP fields: property_event_ts	Measure	AVG((qp_bi_transaction.property_event_ts - qp_bi_transaction.last_transaction_ts) / (10006060247) )	`qp_bi_transaction`

Transactions

New Vs Returning Purchased Status

- Returns 'new' if this is the first purchase of the visitor, otherwise 'returning'

* QP fields: context_conversionNumber

Dimension

IF(qp_bi_transaction.transaction_id IS NOT NULL, IF(qp_bi_transaction.context_conversionNumber > 0, "returning", "new" ), NULL)

qp_bi_transaction

Transactions

Weeks Since First Entry

- Number of weeks between the first view of a visitor and a certain transaction

* QP fields: property_event_ts, meta_recordDate

Dimension

DATEDIFF(MSEC_TO_TIMESTAMP(qp_bi_transaction.property_event_ts), TIMESTAMPDATE((DATE(qp_bi_view.visitor_first_entry_date )))) / 7

qp_bi_transaction

'qp_bi_view'

Transactions

Average Order Value

- Average of transaction value of all transactions

* QP fields: basket_total_baseValue

Measure

AVG(qp_bi_transaction.basket_total_baseValue)

qp_bi_transaction

Transactions

Average Products per Order

- Average value of Transaction Item Quantity divided by exact count of transaction_id

* QP fields: basket_quantity, transaction_id

Measure

SUM(qp_bi_transaction.basket_quantity) / (EXACT_COUNT_DISTINCT(qp_bi_transaction.transaction_id))

qp_bi_transaction

Transactions

Converters

- Count of unique context_ids on views that are labeled with any non-null transaction_id

* If above 1.000.000, the result is approximated

* QP fields: context_id, transaction_id

Measure

COUNT(DISTINCT qp_bi_transaction.context_id, 1000000)

qp_bi_transaction

Transactions

Days Since Previous Purchase

- Number of days between the current transaction and previous transaction by the same visitor

* QP fields: property_event_ts

Measure

AVG((qp_bi_transaction.property_event_ts - qp_bi_transaction.last_transaction_ts) / (10006060*24) )

qp_bi_transaction

Transactions

Hours Since Previous Purchase

- Number of hours between the current transaction and previous transaction by the same visitor

* QP fields: property_event_ts

Measure

AVG((qp_bi_transaction.property_event_ts - qp_bi_transaction.last_transaction_ts) / (10006060) )

qp_bi_transaction

Transactions

Revenue per Converter

- Sum of transaction_total divided by count of unique context_ids

* If count of context_ids is above 1.000.000, the result is approximated

* Only for views that are labeled with any non-null transaction_id. QP fields: basket_total_baseValue, context_id

Measure

SUM(qp_bi_transaction.basket_total_baseValue) / COUNT(DISTINCT qp_bi_transaction.context_id, 1000000)

qp_bi_transaction

Transactions

Revenue per Visitor

- Sum of transaction_total divided by count of unique context_ids

* If count of context_ids is above 1.000.000, the result is approximated

* QP fields: basket_total_baseValue, context_id

Measure

(COALESCE(CAST(SUM(qp_bi_transaction.basket_total_baseValue ) AS FLOAT), 0)) / (COUNT(DISTINCT qp_bi_view.context_id, 1000000))

qp_bi_transaction

'qp_bi_view'

Transactions

Session Conversion Rate

- Share of unique sessions containing views that are labeled with any non-null transaction_id in all sessions

* For counting, when figures are above 1.000.000, the result is approximated

* QP fields: context_sessionNumber, context_id

Measure

COUNT(DISTINCT qp_bi_transaction.session_id, 1000000) / COUNT(DISTINCT qp_bi_view.session_id, 1000000)

qp_bi_transaction

'qp_bi_view'

Transactions

- Count of unique transaction_ids (always exact count)

* QP fields: transaction_id

Measure

EXACT_COUNT_DISTINCT(qp_bi_transaction.transaction_id)

qp_bi_transaction

Transactions

Visitor Conversion Rate

- Share of unique visitors on views that are labeled with any non-null transaction_id in all visitors

* For counting, when figures are above 1.000.000, the result is approximated

* QP fields: context_id

Measure

COUNT(DISTINCT qp_bi_transaction.context_id, 1000000) / COUNT(DISTINCT qp_bi_view.context_id, 1000000)

qp_bi_transaction

'qp_bi_view'

Transactions

Weeks Since Previous Purchase

- Number of weeks between the current transaction and previous transaction by the same visitor

* QP fields: property_event_ts

Measure

AVG((qp_bi_transaction.property_event_ts - qp_bi_transaction.last_transaction_ts) / (10006060247) )

qp_bi_transaction

Views

Subject Area Calculation Description Field Type SQL Expression Source View #1 Source View #2

Subject Area	Calculation	Description	Field Type	SQL Expression	Source View #1
Views	Bounced Entrance (Yes / No)	- Did visitor leave on their first view in entrance? * QP fields: context_entranceViewNumber, context_entranceNumber, context_id	Dimension	CASE WHEN if(qp_bi_view.views_in_entrance=1,true,false) THEN 'Yes' ELSE 'No' END	`qp_bi_view`
Views	Bounced Session (Yes / No)	- Did visitor leave on their first view in session? * QP fields: context_sessionViewNumber, context_sessionNumber, context_id	Dimension	CASE WHEN if(qp_bi_view.views_in_session=1,true,false) THEN 'Yes' ELSE 'No' END	`qp_bi_view`
Views	Record Received Date	- Date of view (in the timezone configured for the tracking ID, format yyyy-MM-dd) * QP fields: meta_recordDate	Dimension	DATE(TIMESTAMP(qp_bi_view.meta_recordDate))	`qp_bi_view`
Views	Returning Purchaser Yes No	- Returns 'new' if this is the first session of the visitor, otherwise 'returning' * QP fields: context_conversionNumber	Dimension	IF(qp_bi_view.context_conversionNumber IS NOT NULL, IF(qp_bi_view.context_conversionNumber > 0, "yes", "no" ), NULL)	`qp_bi_view`
Views	Time Data Points Date	- Timestamp of the page view * QP fields: property_event_ts	Dimension	DATE(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )	`qp_bi_view`
Views	Time Data Points Day of Week	- Timestamp of the page view * QP fields: property_event_ts	Dimension	CASE WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=0 THEN 'Sunday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=1 THEN 'Monday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=2 THEN 'Tuesday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=3 THEN 'Wednesday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=4 THEN 'Thursday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=5 THEN 'Friday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=6 THEN 'Saturday' END,(CASE WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7 < 0 THEN -1 * (ABSDAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) - (ABS(7) * CAST(FLOOR(ABS(((DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) / (7) AS INTEGER))) ELSE ABSDAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) - (ABS(7) * CAST(FLOOR(ABS(((DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) / (7) AS INTEGER)) END)	`qp_bi_view`
Views	Time Data Points Hour of Day	- Timestamp of the page view * QP fields: property_event_ts	Dimension	HOUR(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )	`qp_bi_view`
Views	Time Data Points Month	- Timestamp of the page view * QP fields: property_event_ts	Dimension	STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ), '%Y-%m')	`qp_bi_view`
Views	Time Data Points Month Name	- Timestamp of the page view * QP fields: property_event_ts	Dimension	CASE WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 1 THEN 'January' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 2 THEN 'February' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 3 THEN 'March' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 4 THEN 'April' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 5 THEN 'May' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 6 THEN 'June' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 7 THEN 'July' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 8 THEN 'August' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 9 THEN 'September' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 10 THEN 'October' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 11 THEN 'November' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 12 THEN 'December’END	`qp_bi_view`
Views	Time Data Points Quarter of Year	- Timestamp of the page view * QP fields: property_event_ts	Dimension	CONCAT(CAST('Q' AS STRING), CAST(QUARTER(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) AS STRING))	`qp_bi_view`
Views	Time Data Points Time	- Timestamp of the page view * QP fields: property_event_ts	Dimension	SUBSTR(STRING(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ),1,19)	`qp_bi_view`
Views	Time Data Points Week	- Timestamp of the page view * QP fields: property_event_ts	Dimension	DATE(DATE_ADD(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) , (0 - (CASE WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7 < 0 THEN -1 * (ABSDAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) - (ABS(7) * CAST(FLOOR(ABS(((DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) / (7) AS INTEGER))) ELSE ABSDAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) - (ABS(7) * CAST(FLOOR(ABS(((DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) / (7) AS INTEGER)) END)), 'DAY'))	`qp_bi_view`
Views	Time Data Points Week of Year	- Timestamp of the page view * QP fields: property_event_ts	Dimension	INTEGER(STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(TIMESTAMP(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )), "%V"))	`qp_bi_view`
Views	Time Data Points Year	- Timestamp of the page view * QP fields: property_event_ts	Dimension	YEAR(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )	`qp_bi_view`
Views	Weeks Since First Entry	- Number of weeks between the first view of a visitor and the current view * QP fields: property_event_ts, meta_recordDate, context_id	Dimension	DATEDIFF(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts), TIMESTAMPDATE((DATE(qp_bi_view.visitor_first_entry_date )))) / 7	`qp_bi_view`
Views	Daily Visitors	- Count of unique combinations of a context_id and page view date * If above 1.000.000, the result is approximated * QP fields: context_id, meta_recordDate	Measure	COUNT(DISTINCT CONCAT(qp_bi_view.context_id, qp_bi_view.meta_recordDate), 1000000)	`qp_bi_view`
Views	Entrance Bounce Rate	- Share of entrances that consisted of one view in all entrances * For counting, when figures are above 1.000.000, the result is approximated * QP fields: context_entranceViewNumber, context_entranceNumber, context_id	Measure	COUNT(DISTINCT IF(qp_bi_view.views_in_entrance = 1, qp_bi_view.entrance_id, NULL ), 1000000) / COUNT(DISTINCT qp_bi_view.entrance_id, 1000000)	`qp_bi_view`
Views	Session Bounce Rate	- Share of sessions that consisted of one view in all sessions * For counting, when figures are above 1.000.000, the result is approximated * QP fields: context_sessionViewNumber, context_sessionNumber, context_id	Measure	COUNT(DISTINCT IF(qp_bi_view.views_in_session = 1, qp_bi_view.session_id, NULL), 1000000) / COUNT(DISTINCT qp_bi_view.session_id, 1000000)	`qp_bi_view`
Views	Unique Visitors	- Count of unique context_ids * If above 1.000.000, the result is approximated * QP fields: context_id	Measure	COUNT(DISTINCT qp_bi_view.context_id, 1000000)	`qp_bi_view`
Views	Views	- Count of unique combinations of a context_id and a view_number * If above 1.000.000, the result is approximated * QP fields: context_id, context_viewNumber	Measure	COUNT(DISTINCT qp_bi_view.view_id, 1000000)	`qp_bi_view`
Views	Visitor Bounce Rate	- Share of visitors that saw only a single page view in all visitors * For counting, when figures are above 1.000.000, the result is approximated * QP fields: context_viewNumber, context_id	Measure	COUNT(DISTINCT IF(qp_bi_view.total_visitor_views = 1, qp_bi_view.context_id, NULL ), 1000000) / COUNT(DISTINCT qp_bi_view.context_id, 1000000)	`qp_bi_view`
Views	Visitor Bounce Rate on Page	- Share of visitors that had a session consisting of a single page view in all visitors * For counting, when figures are above 1.000.000, the result is approximated * QP fields: context_sessionViewNumber, context_id, context_sessionNumber	Measure	COUNT(DISTINCT IF(qp_bi_view.last_view_in_session IS TRUE, qp_bi_view.context_id, NULL ), 1000000) / COUNT(DISTINCT qp_bi_view.context_id, 1000000)	`qp_bi_view`
Views	Visitor Return Rate	- Share of visitors considered as returning visitors in all visitors * For counting, when figures are above 1.000.000, the result is approximated * QP fields: context_sessionNymber, context_id	Measure	COUNT(DISTINCT IF(qp_bi_view.new_vs_returning = "returning", qp_bi_view.context_id , NULL), 1000000) / COUNT(DISTINCT qp_bi_view.context_id, 1000000)	`qp_bi_view`

Views

Bounced Entrance (Yes / No)

- Did visitor leave on their first view in entrance?

* QP fields: context_entranceViewNumber, context_entranceNumber, context_id

Dimension

CASE WHEN if(qp_bi_view.views_in_entrance=1,true,false) THEN 'Yes' ELSE 'No' END

qp_bi_view

Views

Bounced Session (Yes / No)

- Did visitor leave on their first view in session?

* QP fields: context_sessionViewNumber, context_sessionNumber, context_id

Dimension

CASE WHEN if(qp_bi_view.views_in_session=1,true,false) THEN 'Yes' ELSE 'No' END

qp_bi_view

Views

Record Received Date

- Date of view (in the timezone configured for the tracking ID, format yyyy-MM-dd)

* QP fields: meta_recordDate

Dimension

DATE(TIMESTAMP(qp_bi_view.meta_recordDate))

qp_bi_view

Views

Returning Purchaser Yes No

- Returns 'new' if this is the first session of the visitor, otherwise 'returning'

* QP fields: context_conversionNumber

Dimension

IF(qp_bi_view.context_conversionNumber IS NOT NULL, IF(qp_bi_view.context_conversionNumber > 0, "yes", "no" ), NULL)

qp_bi_view

Views

Time Data Points Date

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

DATE(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )

qp_bi_view

Views

Time Data Points Day of Week

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

CASE WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=0 THEN 'Sunday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=1 THEN 'Monday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=2 THEN 'Tuesday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=3 THEN 'Wednesday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=4 THEN 'Thursday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=5 THEN 'Friday' WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1)=6 THEN 'Saturday' END,(CASE WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7 < 0 THEN -1 * (ABSDAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) - (ABS(7) * CAST(FLOOR(ABS(((DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) / (7) AS INTEGER))) ELSE ABSDAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) - (ABS(7) * CAST(FLOOR(ABS(((DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) / (7) AS INTEGER)) END)

qp_bi_view

Views

Time Data Points Hour of Day

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

HOUR(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )

qp_bi_view

Views

Time Data Points Month

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ), '%Y-%m')

qp_bi_view

Views

Time Data Points Month Name

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

CASE WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 1 THEN 'January' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 2 THEN 'February' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 3 THEN 'March' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 4 THEN 'April' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 5 THEN 'May' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 6 THEN 'June' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 7 THEN 'July' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 8 THEN 'August' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 9 THEN 'September' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 10 THEN 'October' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 11 THEN 'November' WHEN MONTH(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) = 12 THEN 'December’END

qp_bi_view

Views

Time Data Points Quarter of Year

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

CONCAT(CAST('Q' AS STRING), CAST(QUARTER(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ) AS STRING))

qp_bi_view

Views

Time Data Points Time

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

SUBSTR(STRING(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) ),1,19)

qp_bi_view

Views

Time Data Points Week

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

DATE(DATE_ADD(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) , (0 - (CASE WHEN (DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7 < 0 THEN -1 * (ABSDAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) - (ABS(7) * CAST(FLOOR(ABS(((DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) / (7) AS INTEGER))) ELSE ABSDAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) - (ABS(7) * CAST(FLOOR(ABS(((DAYOFWEEK(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )-1) - 1 + 7) / (7) AS INTEGER)) END)), 'DAY'))

qp_bi_view

Views

Time Data Points Week of Year

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

INTEGER(STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(TIMESTAMP(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )), "%V"))

qp_bi_view

Views

Time Data Points Year

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

YEAR(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )

qp_bi_view

Views

Weeks Since First Entry

- Number of weeks between the first view of a visitor and the current view

* QP fields: property_event_ts, meta_recordDate, context_id

Dimension

DATEDIFF(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts), TIMESTAMPDATE((DATE(qp_bi_view.visitor_first_entry_date )))) / 7

qp_bi_view

Views

Daily Visitors

- Count of unique combinations of a context_id and page view date

* If above 1.000.000, the result is approximated

* QP fields: context_id, meta_recordDate

Measure

COUNT(DISTINCT CONCAT(qp_bi_view.context_id, qp_bi_view.meta_recordDate), 1000000)

qp_bi_view

Views

Entrance Bounce Rate

- Share of entrances that consisted of one view in all entrances

* For counting, when figures are above 1.000.000, the result is approximated

* QP fields: context_entranceViewNumber, context_entranceNumber, context_id

Measure

COUNT(DISTINCT IF(qp_bi_view.views_in_entrance = 1, qp_bi_view.entrance_id, NULL ), 1000000) / COUNT(DISTINCT qp_bi_view.entrance_id, 1000000)

qp_bi_view

Views

Session Bounce Rate

- Share of sessions that consisted of one view in all sessions

* For counting, when figures are above 1.000.000, the result is approximated

* QP fields: context_sessionViewNumber, context_sessionNumber, context_id

Measure

COUNT(DISTINCT IF(qp_bi_view.views_in_session = 1, qp_bi_view.session_id, NULL), 1000000) / COUNT(DISTINCT qp_bi_view.session_id, 1000000)

qp_bi_view

Views

Unique Visitors

- Count of unique context_ids

* If above 1.000.000, the result is approximated

* QP fields: context_id

Measure

COUNT(DISTINCT qp_bi_view.context_id, 1000000)

qp_bi_view

Views

- Count of unique combinations of a context_id and a view_number

* If above 1.000.000, the result is approximated

* QP fields: context_id, context_viewNumber

Measure

COUNT(DISTINCT qp_bi_view.view_id, 1000000)

qp_bi_view

Views

Visitor Bounce Rate

- Share of visitors that saw only a single page view in all visitors

* For counting, when figures are above 1.000.000, the result is approximated

* QP fields: context_viewNumber, context_id

Measure

COUNT(DISTINCT IF(qp_bi_view.total_visitor_views = 1, qp_bi_view.context_id, NULL ), 1000000) / COUNT(DISTINCT qp_bi_view.context_id, 1000000)

qp_bi_view

Views

Visitor Bounce Rate on Page

- Share of visitors that had a session consisting of a single page view in all visitors

* For counting, when figures are above 1.000.000, the result is approximated

* QP fields: context_sessionViewNumber, context_id, context_sessionNumber

Measure

COUNT(DISTINCT IF(qp_bi_view.last_view_in_session IS TRUE, qp_bi_view.context_id, NULL ), 1000000) / COUNT(DISTINCT qp_bi_view.context_id, 1000000)

qp_bi_view

Views

Visitor Return Rate

- Share of visitors considered as returning visitors in all visitors

* For counting, when figures are above 1.000.000, the result is approximated

* QP fields: context_sessionNymber, context_id

Measure

COUNT(DISTINCT IF(qp_bi_view.new_vs_returning = "returning", qp_bi_view.context_id , NULL), 1000000) / COUNT(DISTINCT qp_bi_view.context_id, 1000000)

qp_bi_view

Visitor pulse

Subject Area Calculation Description Field Type SQL Expression Source View #1 Source View #2

Subject Area	Calculation	Description	Field Type	SQL Expression	Source View #1
Visitor pulse	Surveys Count	- Count of unique survey_ids (always exact count) * QP fields: survey_id	Measure	EXACT_COUNT_DISTINCT(qp_bi_visitor_pulse.survey_id)	`qp_bi_visitor_pulse`
Visitor pulse	Total Answered Questions	- Count of unique answer IDs, only for answered questions * If a survey contains 3 questions, but one answer was left blank, the result for a single submission will be 2 * QP fields: context_id, context_viewNumber, question_order, question_type, value_integer, value_text, question_type, value_boolean, choice_text	Measure	EXACT_COUNT_DISTINCT(IF(qp_bi_visitor_pulse.answer IS NOT NULL, qp_bi_visitor_pulse.unique_row_id,NULL))	`qp_bi_visitor_pulse`
Visitor pulse	Total Answers	- Count of unique answer IDs * If a survey contains 3 questions, the result for a single submission will be 3 * If there are 3 submissions, 3 questions each, the result will be 9, etc * QP fields: context_id, context_viewNumber, question_order, question_type, value_integer, value_text, question_type, value_boolean, choice_text	Measure	EXACT_COUNT_DISTINCT(qp_bi_visitor_pulse.unique_row_id)	`qp_bi_visitor_pulse`
Visitor pulse	Total Unanswered Questions	- Count of unique answer IDs, only for unanswered questions * If a survey contains 3 questions, but one answer was left blank, the result for a single submission will be 1 * QP fields: context_id, context_viewNumber, question_order, question_type, value_integer, value_text, question_type, value_boolean, choice_text	Measure	EXACT_COUNT_DISTINCT(IF(qp_bi_visitor_pulse.answer IS NULL, qp_bi_visitor_pulse.unique_row_id,NULL))	`qp_bi_visitor_pulse`

Visitor pulse

Surveys Count

- Count of unique survey_ids (always exact count)

* QP fields: survey_id

Measure

EXACT_COUNT_DISTINCT(qp_bi_visitor_pulse.survey_id)

qp_bi_visitor_pulse

Visitor pulse

Total Answered Questions

- Count of unique answer IDs, only for answered questions

* If a survey contains 3 questions, but one answer was left blank, the result for a single submission will be 2

* QP fields: context_id, context_viewNumber, question_order, question_type, value_integer, value_text, question_type, value_boolean, choice_text

Measure

EXACT_COUNT_DISTINCT(IF(qp_bi_visitor_pulse.answer IS NOT NULL, qp_bi_visitor_pulse.unique_row_id,NULL))

qp_bi_visitor_pulse

Visitor pulse

Total Answers

- Count of unique answer IDs

* If a survey contains 3 questions, the result for a single submission will be 3

* If there are 3 submissions, 3 questions each, the result will be 9, etc

* QP fields: context_id, context_viewNumber, question_order, question_type, value_integer, value_text, question_type, value_boolean, choice_text

Measure

EXACT_COUNT_DISTINCT(qp_bi_visitor_pulse.unique_row_id)

qp_bi_visitor_pulse

Visitor pulse

Total Unanswered Questions

- Count of unique answer IDs, only for unanswered questions

* If a survey contains 3 questions, but one answer was left blank, the result for a single submission will be 1

* QP fields: context_id, context_viewNumber, question_order, question_type, value_integer, value_text, question_type, value_boolean, choice_text

Measure

EXACT_COUNT_DISTINCT(IF(qp_bi_visitor_pulse.answer IS NULL, qp_bi_visitor_pulse.unique_row_id,NULL))

qp_bi_visitor_pulse