Live Tap

On this page

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 have 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 are 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 cannot see your Qubit project, follow these steps:

Step 1

Select the blue down arrow next to the project name:

datasets

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 e.g. `meta_batchTs`
 - context_* e.g. `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 e-Commerce 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

datasets

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

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

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
`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, e.g. 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 e.g. segment name, value, etc
`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, e.g. UTM, etc
`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, e.g. 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:

datasets

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

eGaming industry business model

datasets

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:

datasets

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:

datasets

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:

datasets

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:

datasets

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:

datasets

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:

datasets

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 will need appropriate permissions on Looker to see Admin panel. In addition, you will need the following information, which will be provided by Qubit:

Looker Connection Name - e.g. client-connection-100000
Qubit Project Name - e.g. 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 top-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 will need the link to a read-only GitHub repo holding your configured Live Tap model files. This will be provided by Qubit.

You will 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 will 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 e.g. 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 have forked the GitHub repo and that you have repo URL. You will 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 content of the key and copy it to your clipboard. Navigate to your repo in GitHub and select Settings. In 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. Please 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:

datasets

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
Entrances	Views that arrived first in visitors's entrance	`qp_bi_entrance`
Atrribution	A collection of entries which happened before the visitor completed the transction.	`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 website for each visit, i.e. 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 e.g. which channel is usually the first to refer users to site, etc

- _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

datasets

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, i.e. 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, i.e. 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)

datasets

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.

datasets

Cross-field filter:

datasets

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:

datasets

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:

datasets

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:

datasets

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

datasets

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:

datasets

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

datasets

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:

datasets

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 top-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_bi_all_ 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, please reach out to Customer Support at Qubit 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:

datasets

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:

datasets

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:

datasets

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 will 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:

datasets

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 are using is not 10.1, change the version to the Tableau Desktop version you are 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
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 i.e. 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 her/his lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in her/his lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in her/his 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 website
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
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 i.e. 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 her/his lifetime (across all sessions)
context_viewNumber	INTEGER	The sequential pageview number of a user in her/his lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in her/his 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 e.g. affiliate.
referrer_network	STRING	If applicable - the network of the page a visitor has come from e.g. affiliate window.
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	STRING	The value of Google UTM campaign term - extracted from landing page (meta_url)

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 her/his lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in her/his 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 her/his lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in her/his 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 her/his lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in her/his 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
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 i.e. 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 her/his lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in her/his lifetime (across all sessions)
context_viewNumber	INTEGER	The sequential pageview number of a user in her/his 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 e.g. computer, mobile, tablet, etc
deviceName	STRING	The name of device user initiated the session with e.g. Iphone, Sony etc
appType	STRING	The application type used on the device user initiated the session with e.g. browser
appName	STRING	The application name used on the device user initiated the session with e.g. Chrome
appVersion	STRING	The application version used on the device user initiated the session with e.g. 1.3
osName	STRING	The OS type used on the device user initiated the session with e.g. Windows
osVersion	STRING	The OS version used on the device user initiated the session with e.g. 7
ipLocation_country	STRING	The country in which user initiated the session e.g. United Kingdon
ipLocation_country_code	STRING	The code of country in which user initiated the session e.g. 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 e.g. 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 her/his lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in her/his 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. E.g. 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 is 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 e.g. affiliate.
referrer_network	STRING	If applicable - the network of the page a visitor has come from in the converting entry e.g. 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 i.e. 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 her/his 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 her/his lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in her/his 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 (i.e. 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 her/his lifetime (across all sessions)
context_sessionNumber	INTEGER	The sequential session number of a user in her/his lifetime (across all sessions)
context_viewNumber	INTEGER	The sequential pageview number of a user in her/his 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 her/his 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 her/his lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in her/his 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_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_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 her/his lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in her/his lifetime (across all sessions)
context_viewNumber	INTEGER	The sequential pageview number of a user in her/his 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 her/his 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 her/his lifetime (across all sessions)
context_entranceNumber	INTEGER	The sequential entrance number of a user in her/his 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

Live Tap Derived Measures - Semantic model subject areas

Attribution

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`

Entrances

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<br- qp="" fields:="" context_id="" <="" td="">	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`

Experiences

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'

Goal Achieved

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`

Product interaction and sales

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`

Segment membership

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'

Sessions

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`

Transactions

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), TIMESTAMP((DATE((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`

Views

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 (ABS((DAYOFWEEK(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 ABS((DAYOFWEEK(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 (ABS((DAYOFWEEK(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 ABS((DAYOFWEEK(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), TIMESTAMP((DATE((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`

Visitor pulse

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`

Last updated: January 2023

Next: Overview

Get full access to your data with Livetap

Did you find this article useful?