Live Tap overview

This is for:

Developer

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

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

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

Getting started

Connecting to the data warehouse

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

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

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

Step 1

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

Step 2

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

Finding your Qubit project

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

Step 1

Select the blue down arrow next to the project name:

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:

  1. 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 aren’t usually populated:

    • meta_* fields (for example, meta_batchTs)

    • context_* (for example, context_latestViewTs, context_latestSessionTs)

    • store_address*

    • user_address*

  2. 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

  3. "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 isn’t yet present in the business views.

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

  • Views

  • Sessions

  • Entrances

  • Transactions

  • Segments Membership

  • Experiences

  • Goal Achieved

  • Attribution

  • Visitor pulse

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

  • Product interaction and sales for ecommerce industry customers

  • Package interaction and sales for travel industry customers

  • Bet interaction and sales for e-Gaming industry customers

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

Ecommerce industry business model

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, for example, to capture views that happened in an experience or transaction events that may not have come through to the original ecView event

product

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

* Combine in one view all product-related QP events

* Enrich basic product data with derived metrics and attributes

segment

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

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

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

experience_v01

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

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

* Bring in ‘goal achieved immediately’ fields

transaction

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

session_v01

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

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

entrance

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

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

attribution_v01

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

visitor_pulse

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

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

Travel industry business model

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

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 they’re 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:

  1. Setting up the BigQuery connection in Looker

  2. Forking GitHub Repo

  3. Setting up a project in Looker

Setting up the BigQuery connection in Looker

Before getting started:

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

  • Looker Connection Name: For example, client-connection-100000.

  • Qubit Project Name: For example, qubit-client-10000.

  • Qubit Dataset: The name of one of the datasets in your project.

  • Service Account Name: Address corresponding to the service account. This will start with qubit-client-bi@qubit-client.

  • P12 Key File: Encrypted file needed to authenticate with Google.

  • Service Account Password: Password for the service account.

Configuring the connection:
Step 1

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

Step 2

Enter the following connection details:

  • Name - Looker Connection Name

  • Dialect - Google BigQuery (make sure you do not pick Standard SQL)

  • Project Name - Qubit Project Name

  • Dataset - Qubit Dataset (the connection will work for all datasets in the project, not just the one specified)

  • OAuth Credentials Email - Service Account Name

  • OAuth Credentials P12 File - upload P12 Key File

  • Password - Service Account Password

  • Max Billing Tier - 1

Step 3

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

Forking the GitHub Repo

Before getting started:

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

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

Forking the repo
Step 1

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

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

Step 2

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

Setting up a project in Looker

Before getting started:

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

Setting up a Looker project
Step 1

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

Step 2

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

Step 3

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

Step 4

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

Step 5

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

Step 6

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

Finally, select Sync Development Mode

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

Looker internal

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

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

Setting up Live Tap on Looker on your own Looker instance

Step 1

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

Step 2

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

Step 3

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

  1. Select Persistent Derived Tables

  2. Set Temp Dataset to qubit_cs_bi_cache

  3. In the new Looker project, configure git, pointing to the repository forked in point 1

  4. Synchronize with production

  5. Replace all tracking_id instances in file names. You can do this manually under Looker’s Develop or in GitHub

  6. Synchronize once again with production

  7. Use the Find & Replace in Project option to change all instances of {TRACKING_ID} in the new project. 233 occurences in 15 files

  8. Use the Find & Replace in Project option to change all instance of {PROJECT_ID} in the new project. 5 occurences in 4 files..

  9. Select Validate LookML, then commit and push to GitHub

  10. In BigQuery’s client project create a new dataset in BigQuery called qubit_cs_bi_cache

  11. 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 transaction.

qp_bi_attribution

Visitor pulse

Survey responses

qp_bi_visitor_pulse

Experience

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

qp_bi_visitor_first_seen_experience qp_bi_post_goal_achieved_views qp_bi_transaction

Product Interaction and Sales

Views where products were displayed, interacted with, or purchased

qp_bi_product

Segment Membership

Views that happened on or after a visitor entered a segment

qp_bi_segment qp_bi_transaction

Sessions

Views that arrived first in user’s session

qp_bi_session

Transactions

Views on which purchase happened

qp_bi_transaction qp_bi_view

Views

All Visitor view events

qp_bi_view qp_bi_visitor_first_seen_experience

Attribution subject area

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

The key characteristics of an entry are:

  • Entry Referrer group of dimensions - tells us how the visitor landed on the site for each visit, that is, marketing channel, campaign, referrer URL, etc. These reside in the ‘Entry Referrer’ group of dimensions.

  • Views in Attribution Entry dimension - indicates how many views there were in an entry. The more views an entry has, the more impact it likely has on the purchase. You can combine this with Page Type (from the View subject area) to see the categories of pages the visitor viewed before converting. The same applies when combining with Product to inspect the products that the visitor viewed before converting, etc.

  • Additional attributes of Entry:

    • Entry Number: Consecutive number incrementing on each new entry, and always starting from 1. Combined with other fields, could answer various questions (for example, which channel is usually the first to refer users to site).

    • Touchpoint: Classification of entries based on Entry Number. It has following values:

      • Single entry visitor had just one entry in that purchase cycle before they converted.

      • First: Tag for first entry in a purchase cycle, in a sequence of 2 entries or more.

      • Last: Tag for last entry in a purchase cycle, in a sequence of 2 entries or more.

      • Assist: Represents an intermediate entry in a purchase cycle (obviously - which wasn’t the first or last one).

  • Entrance and Session Number: Corresponds to the actual context session and entrance numbers in Qubit Project (QP).

Naturally, some dimensions are repeated 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

    info-icon All transactions that occurred in the same session are treated as a single attribution order - their totals are summed, and order IDs are stitched together.

  • Purchase Cycle Group: Corresponds to Purchase Cycle (above) for values 1- 4. Values 5 or above set to ‘5+’. We’ve 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, that is, where Touchpoint = ‘First’ or ‘Single’ and will be 0.00 for all.

      • Last Click Value: Full order value is only attributed to last entry in sequence, that is, where Touchpoint = ‘Last’ or ‘Single’.

      • Even Click Value: Full order value divided by number of all entries user had in that purchase cycle.

      • Behavioral Value: The amount of revenue proportional to the number of views a visitor had in that entry had.

  • Measures describing ‘Order’: Attribution order is all transactions completed by visitor in the same session:

    • Order Count: Returns the number of ‘attribution orders’.

    • Conversion Value: Summed transactional total (basket total).

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’s recommended to avoid this combination. If you require attribution combined with transactional analysis, it’s 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’s 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 the following:

  • 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 four looks (Looker reports) that you can customize for your environment and use as examples of 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.

info-icon 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.

info-icon 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 upper-right hand corner. Repeat steps 2-5 for each remaining data source to complete the data source remapping to your Live Tap dataset

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

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

  1. By selecting one of the individual Business Views or QProtocol event views

  2. Through creating a custom SQL query

  3. 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.

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

Other BI tools that support Google BigQuery connections

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

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

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

Joining business views within a BI Tool Data source definition

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

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

  • qp_bi_view.view_id = qp_bi_product.view_id

  • qp_bi_view.view_id = qp_bi_segment.view_id

  • qp_bi_view.view_id = qp_bi_experience.view_id

  • qp_bi_view.session_id = qp_bi_session.session_id

  • qp_bi_view.entrance_id = qp_bi_entrance.entrance_id

  • qp_bi_view.view_id = qp_bi_transaction.view_id

  • qp_bi_view.view_id = qp_bi_visitor_pulse.view_id

  • qp_bi_view.view_id = qp_bi_goal_achieved.view_id

Useful derived measures and dimension attributes for BI tools

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

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

Using Live Tap business views with Tableau

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

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

Step 1

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

info-icon 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

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

Step 3

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

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

info-icon 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.

info-icon 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

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

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

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

context_id context_viewNumber segmentName transaction_id transaction_total

123xyz

14

UK Desktop

tr4

700$

123xyz

14

VIP Customers

tr4

700$

123xyz

14

Returning Visitors

tr4

700$

123xyz

14

Frequent purchasers

tr4

700$

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

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

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

Step 1

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

Step 2

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

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

And as shown in this example:

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’re using is not 10.1, change the version to the Tableau Desktop version you’re using. You can check the version number by selecting *Tableau > About Tableau * .

Step 3

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

Step 4

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

info-icon 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 that is, the referrer, is on a different domain or if the url of the View is typed in manually in the address bar. Reloading the browser or navigating between pages on the same domain will not cause entrance events to be emitted

session_id

STRING

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

entry_id

STRING

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

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

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

context_sessionNumber

INTEGER

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

context_entranceNumber

INTEGER

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

context_sessionViewNumber

INTEGER

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

context_entranceViewNumber

INTEGER

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

context_conversionNumber

INTEGER

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

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

meta_recordDate

STRING

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

context_lifetimeValue_baseValue

FLOAT

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

user_id

STRING

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

user_username

STRING

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

user_title

STRING

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

user_email

STRING

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

user_loyalty_tier

STRING

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

user_gender

STRING

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

user_age

INTEGER

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

user_phoneNumber

STRING

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

user_isGuest

STRING

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

user_firstName

STRING

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

user_lastName

STRING

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

is_user_sign_up_view

BOOLEAN

True if visitor registered on the view

meta_ts

INTEGER

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

meta_serverTs

INTEGER

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

meta_url

STRING

URL on which view was emitted

page_type

STRING

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

page_subtype

STRING

Page category / breadcrumb of view - as emitted by website

transaction_id

STRING

Transaction ID if user submitted order on the page view

views_in_session

INTEGER

The number of views user has had in a session

first_view_in_session_ts

INTEGER

Epoch time of first view that was emitted in the session

last_view_in_session_ts

INTEGER

Epoch time of last view that was emitted in the session

views_in_entrance

INTEGER

Total number of views in user’s entrance

total_visitor_views

INTEGER

Total number of views in user’s livetime

visitor_first_entry_date

STRING

The first date visitor has entered the site

last_view_in_session

BOOLEAN

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

last_view_in_entrance

BOOLEAN

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

last_visitor_view

BOOLEAN

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

new_vs_returning

STRING

Label for new_vs_returning status

session_duration_seconds

FLOAT

The number of seconds the session was active for

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

qp_bi_entrance

Column Name BQ Data Type Column Description

qp_bi_view_name

STRING

Name of BI view, static field.

ts

TIMESTAMP

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

view_id

STRING

Unique ID of view.

entrance_id

STRING

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

session_id

STRING

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

property_event_ts

TIMESTAMP

The timestamp of event in client property local time.

context_id

STRING

Visitor ID based on browser cookie.

context_entranceNumber

INTEGER

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

context_viewNumber

INTEGER

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

context_sessionNumber

INTEGER

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

context_conversionNumber

INTEGER

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

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit.

meta_recordDate

STRING

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

meta_serverTs

INTEGER

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

meta_ts

INTEGER

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

meta_url

STRING

URL on which view was emitted.

referrer_url

STRING

The URL of the page a visitor has come from.

referrer_type

STRING

The type of the page a visitor has come from affiliate, for example.

referrer_network

STRING

If applicable - the network of the page a visitor has come from affiliate window, for example.

referrer_mediaType

STRING

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

referrer_campaign

STRING

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

referrer_adGroup

STRING

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

referrer_content

STRING

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

referrer_keywords

STRING

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

referrer_searchQuery

STRING

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

referrer_label

STRING

The label of the page a visitor has come from.

referrer_domain

STRING

The domain of the page a visitor has come from.

referrer_matchType

STRING

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

utm_source

STRING

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

utm_medium

STRING

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

utm_content

STRING

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

utm_campaign

STRING

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

utm_term

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 client property timezone)

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

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

context_sessionNumber

INTEGER

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

context_conversionNumber

INTEGER

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

meta_ts

INTEGER

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

meta_serverTs

INTEGER

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

experienceId

INTEGER

The numeric ID of experience.

experienceName

STRING

The name of experience.

variationMasterId

INTEGER

The numeric ID of master variation.

variationName

STRING

The name of variation

iterationId

INTEGER

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

isControl

INTEGER

True if variation represents the group control

first_view_meta_ts

INTEGER

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

first_view_meta_recordDate

STRING

First day when the visitor has seen the iteration of variation

first_view_in_iteration

INTEGER

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

last_view_in_iteration

INTEGER

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

is_post_experience_view

INTEGER

False if experience was delivered on the view. True if the experience fired prior to the view.
qp_bi_goal_achieved
Column Name BQ Data Type Column Description

qp_bi_view_name

STRING

Name of BI view, static field

ts

TIMESTAMP

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

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

view_id

STRING

Unique ID of view

meta_recordDate

STRING

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

meta_ts

INTEGER

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

meta_serverTs

INTEGER

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

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

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

context_sessionNumber

INTEGER

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

context_conversionNumber

INTEGER

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

experienceId

INTEGER

The numeric ID of experience.

experienceName

STRING

The name of experience.

variationMasterId

INTEGER

The numeric ID of master variation.

variationName

STRING

The name of variation

iterationId

INTEGER

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

isControl

INTEGER

True if variation represents the group control

goalId

INTEGER

The numeric ID of experience goal

goalValue

STRING

The value emitted for experience goal

goalType

STRING

The type emitted for experience goal

first_view_meta_ts

INTEGER

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

first_view_meta_recordDate

STRING

First day when the visitor has seen the iteration of variation

first_view_in_iteration

INTEGER

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

last_view_in_iteration

INTEGER

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

is_post_goal_achieved_view

INTEGER

False if goal was achieved on the view. True if the goal was achieved prior to the view.
qp_bi_segment
Column Name BQ Data Type Column Description

qp_bi_view_name

STRING

Name of BI view, static field

ts

TIMESTAMP

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

view_id

STRING

Unique ID of view

meta_recordDate

STRING

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

meta_ts

INTEGER

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

meta_serverTs

INTEGER

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

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

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

context_sessionNumber

INTEGER

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

context_conversionNumber

INTEGER

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

segment_rows

INTEGER

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

segmentId

STRING

The numeric ID of segment

segmentName

STRING

The name of segment
qp_bi_session
Column Name BQ Data Type Column Description

qp_bi_view_name

STRING

Name of BI view, static field

ts

TIMESTAMP

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

session_id

STRING

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

view_id

STRING

Unique ID of view

entrance_id

STRING

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

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

meta_recordDate

STRING

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

meta_ts

INTEGER

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

meta_url

STRING

URL on which view was emitted

meta_serverTs

INTEGER

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

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_sessionNumber

INTEGER

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

context_entranceNumber

INTEGER

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

context_viewNumber

INTEGER

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

context_conversionNumber

INTEGER

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

deviceType

STRING

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

qp_bi_view_name

STRING

Name of BI view, static field

ts

TIMESTAMP

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

meta_recordDate

STRING

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

meta_serverTs

INTEGER

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

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_sessionNumber

INTEGER

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

context_entranceNumber

INTEGER

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

context_conversionNumber

INTEGER

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

purchase_timestamp

TIMESTAMP

The timestap of order

entry_timestamp

TIMESTAMP

The timestap of entry

ranked_entry_number_reverse

INTEGER

The sequential number of entry - reversed. For example

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

ranked_purchase_number

INTEGER

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

ranked_entry_number

INTEGER

The sequential number of entry in purchase cycle.

entry_id

STRING

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

order_ids_in_purchase_cycle

STRING

The number of orders made in purchase cycle.

referrer_type

STRING

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

affiliate).

referrer_network

STRING

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

affiliate window).

referrer_mediaType

STRING

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

referrer_campaign

STRING

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

referrer_adGroup

STRING

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

referrer_content

STRING

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

referrer_keywords

STRING

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

referrer_searchQuery

STRING

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

referrer_label

STRING

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

referrer_domain

STRING

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

referrer_matchType

STRING

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

first_click_value

FLOAT

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

even_click_value

FLOAT

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

last_click_value

FLOAT

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

pageviews_in_entry

INTEGER

The total number of views in entry

entries_in_purchase_cycle

INTEGER

The total number of entries in purchase cycle

behavioural_value

FLOAT

The transactional value attributed to entry based on its behavioural value

conversion_value_in_purchase_cycle

FLOAT

The total transactional value

touchpoint

STRING

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

true_referrer_type

STRING

Normalised referrer_type

purchase_cycle_group

STRING

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

property_event_ts

TIMESTAMP

The timestamp of transaction in client property local time
qp_bi_visitor_pulse
Column Name BQ Data Type Column Description

qp_bi_view_name

STRING

Name of BI view, static field

ts

TIMESTAMP

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

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

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

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

meta_recordDate

STRING

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

meta_ts

INTEGER

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

meta_serverTs

INTEGER

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

meta_url

STRING

URL on which view was emitted

survey_id

STRING

The ID of visitor pulse survey

question_text

STRING

Text of question asked in the Visitor pulse survey

question_type

STRING

The type of a question in survey

question_order

INTEGER

The order of a question in survey

question_id

STRING

The ID of a question in survey

survey_totalQuestions

INTEGER

The total number of question in the survey

answer

STRING

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

question_freetext

STRING

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

view_id

STRING

Unique ID of view

user_survey_response_id

STRING

Unique ID of response in a survey

unique_row_id

STRING

Unique ID of the row

user_survey_answer_1

STRING

Answer given to question number 1

user_survey_answer_2

STRING

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

user_survey_answer_3

STRING

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

user_survey_answer_4

STRING

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

user_survey_answer_5

STRING

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

user_survey_answer_6

STRING

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

user_survey_answer_7

STRING

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

user_survey_answer_8

STRING

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

user_survey_answer_9

STRING

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

user_survey_answer_10

STRING

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

user_survey_answer_11

STRING

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

user_survey_answer_12

STRING

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

user_survey_answer_13

STRING

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

user_survey_answer_14

STRING

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

user_survey_answer_15

STRING

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

user_survey_answer_16

STRING

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

user_survey_answer_17

STRING

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

user_survey_answer_18

STRING

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

user_survey_answer_19

STRING

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

user_survey_answer_20

STRING

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

Ecommerce

qp_bi_product

Column Name BQ Data Type Column Description

qp_bi_view_name

STRING

Name of BI view, static field

ts

TIMESTAMP

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

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

view_id

STRING

Unique ID of view

session_id

STRING

Unique ID of session

entrance_id

STRING

Unique ID of entrance

meta_recordDate

STRING

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

meta_serverTs

INTEGER

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

meta_ts

INTEGER

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

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

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

context_sessionNumber

INTEGER

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

context_conversionNumber

INTEGER

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

transaction_id

STRING

Transaction ID if user submitted order on the page view

product_rows

INTEGER

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

concat_product_id

STRING

product_productId and product_sku joined together

product_productId

STRING

Product ID

product_sku

STRING

Product SKU

product_category

STRING

Product Category

product_subcategory

STRING

Product Subcategory

product_manufacturer

STRING

Product Manufacturer

product_color

STRING

Product Colour

product_size

STRING

Product Size

product_stock

INTEGER

Product Stock Level

product_rating

FLOAT

Product Rating

product_price_baseValue

FLOAT

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

product_originalPrice_baseValue

FLOAT

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

product_price_baseCurrency

STRING

Currency of product price

product_name

STRING

Product Name

subtotal_baseValue

FLOAT

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

quantity

INTEGER

Quantity of items if purchased

unique_row_id

STRING

Unique row ID

product_interaction_type

STRING

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

meta_type

STRING

The name of the original QP event the row comes from

qp_bi_transaction

Column Name BQ Data Type Column Description

qp_bi_view_name

STRING

Name of BI view, static field

ts

TIMESTAMP

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

transaction_id

STRING

Order ID

context_id

STRING

Visitor ID based on browser cookie

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_entranceNumber

INTEGER

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

context_sessionNumber

INTEGER

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

context_viewNumber

INTEGER

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

context_conversionNumber

INTEGER

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

meta_recordDate

STRING

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

meta_ts

INTEGER

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

meta_serverTs

INTEGER

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

basket_total_baseValue

FLOAT

Total paid for order in base currency

basket_tax_baseValue

FLOAT

Total tax paid for order in base currency

basket_total_universalValue

FLOAT

Total paid for order in universal currency

basket_subtotal_baseValue

FLOAT

Subtotal paid for order in base currency

basket_quantity

INTEGER

Quantity of items in basket

basket_discount_baseValue

FLOAT

Total value discounted from order in base currency

transaction_total

FLOAT

Equal to basket_total_baseValue

basket_shippingPrice_baseValue

FLOAT

Total paid for shipping in base currency

session_id

STRING

Unique ID of session

entrance_id

STRING

Unique ID of entrance

view_id

STRING

Unique ID of view

previous_transaction_ts

INTEGER

Epoch time of previous transaction

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

eGaming

qp_bi_betslip_item

Column Name BQ Data Type Column Description

qp_bi_view_name

STRING

ts

TIMESTAMP

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

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

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

meta_id

STRING

Unique ID of event

meta_type

STRING

The name of the original QP event the row comes from

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

context_conversionNumber

INTEGER

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

context_sessionNumber

INTEGER

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

context_entranceNumber

INTEGER

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

meta_recordDate

STRING

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

meta_ts

INTEGER

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

transaction_id

STRING

Transaction ID if user submitted order on the page view

betslip_totalOdds

FLOAT

Bet total odds

bet_price_baseValue

FLOAT

Bet sales price if on sales or regular price otherwise

bet_originalPrice_baseValue

FLOAT

Bet original price if on sales or regular price otherwise

bet_potentialReturn_baseValue

FLOAT

Bet potential return value

bet_odds

FLOAT

Bet odds

bet_startTime

INTEGER

Bet start time

bet_multiBet

BOOLEAN

True if bet is multi bet

bet_betType

STRING

Bet type

bet_inplay

BOOLEAN

True if bet is in play

bet_sport

STRING

Bet sport

bet_competition

STRING

Bet competition

bet_eventName

STRING

Bet event name

bet_selection

STRING

Bet selection

bet_marketName

STRING

Bet market name

basket_interaction_type

STRING

Basket interaction type such as addition or removal

unique_row_id

STRING

Unique row ID

view_id

STRING

Unique view ID

session_id

STRING

Unique session ID

entrance_id

STRING

Unique entrance ID

interaction_type

STRING

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

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 their lifetime (across all sessions)

context_entranceNumber

INTEGER

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

context_viewNumber

INTEGER

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

context_conversionNumber

INTEGER

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

meta_recordDate

STRING

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

meta_ts

INTEGER

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

meta_serverTs

INTEGER

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

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

betslip_quantity

INTEGER

Quantity of betslips

betslip_tax_baseValue

FLOAT

Total tax paid for order in base currency

betslip_total_baseValue

FLOAT

Total order value in base currency

betslip_discount_baseValue

FLOAT

Total discount value in base currency

betslip_totalStake_baseValue

FLOAT

Total stake value in base currency

betslip_totalPotential_baseValue

FLOAT

Total potential value in base currency

session_id

STRING

Unique session ID

entrance_id

STRING

Unique entrance ID

view_id

STRING

Unique view ID

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

Travel

qp_bi_package_item

Column Name BQ Data Type Column Description

qp_bi_view_name

STRING

Name of BI view, static field

ts

TIMESTAMP

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

property_event_ts

TIMESTAMP

The timestamp of event in client property local time

context_id

STRING

Visitor ID based on browser cookie

context_viewNumber

INTEGER

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

meta_type

STRING

The name of the original QP event the row comes from

package_id

STRING

Unique ID of package

meta_id

STRING

The original ID of event

context_conversionNumber

INTEGER

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

context_sessionNumber

INTEGER

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

context_entranceNumber

INTEGER

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

meta_recordDate

STRING

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

meta_ts

INTEGER

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

meta_serverTs

INTEGER

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

meta_trackingId

STRING

The ID used that uniquely identifies client brand in Qubit

transaction_id

STRING

Transaction ID if user submitted order on the page view

eventType

STRING

Original QP event type

packageItem_accommodation_address_country

STRING

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

packageItem_accommodation_address_countryCode

STRING

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

packageItem_accommodation_boardBasis

STRING

Board basis - if visitor booked accommodation as part of package

packageItem_accommodation_id

STRING

Accommodation ID - if visitor booked accommodation as part of package

packageItem_accommodation_maxOccupancy

INTEGER

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

packageItem_accommodation_name

STRING

Accommodation Name - if visitor booked accommodation as part of package

packageItem_accommodation_normalizedRating

FLOAT

Accommodation Rating - if visitor booked accommodation as part of package

packageItem_accommodation_rooms

INTEGER

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

packageItem_accommodation_stars

INTEGER

Accommodation stars - if visitor booked accommodation as part of package

packageItem_ancillary_ancillaryType

STRING

Ancillary type - if one was purchased

packageItem_ancillary_insurance_type

STRING

Inusrance type ancillary - if one was purchased

packageItem_ancillary_luggage_type

STRING

Luggage type ancillary - if one was purchased

packageItem_ancillary_meal_type

STRING

Meal type ancillary - if one was purchased

packageItem_ancillary_parking_type

STRING

Parking type ancillary - if one was purchased

packageItem_ancillary_pet_type

STRING

Pet type ancillary - if one was purchased

packageItem_ancillary_priority_type

STRING

Priority type ancillary - if one was purchased

packageItem_ancillary_seats_type

STRING

Seats type ancillary - if one was purchased

packageItem_ancillary_sportsEquipment_type

STRING

Sports equipment type ancillary - if one was purchased

packageItem_ancillary_towel_quantity

INTEGER

Towel quantity ancillary - if one was purchased

packageItem_ancillary_transfers_type

STRING

Transfer type - if one was purchased

packageItem_ancillary_transfers_vendor

STRING

Transfer vendor ancillary - if transfer was purchased

packageItem_ancillary_vehicleRental_type

STRING

Transfer vehicle rental type - if applicable

packageItem_ancillary_wifi_id

STRING

Wifi ID- if applicable

packageItem_id

STRING

Package item ID

packageItem_journey_destinationCode

STRING

destination code - if package included journey

packageItem_journey_destinationCountry

STRING

destination country - if package included journey

packageItem_journey_destinationName

STRING

destination name - if package included journey

packageItem_journey_id

STRING

Journey ID - if package included journey

packageItem_journey_journeyType

STRING

Journey type - if package included journey

packageItem_journey_operator

STRING

Journey operator - if package included journey

packageItem_journey_originCode

STRING

Origin code - if package included journey

packageItem_journey_originCountry

STRING

Origin country - if package included journey

packageItem_journey_originName

STRING

Origin name - if package included journey

packageItem_journey_price_baseValue

FLOAT

Journey price - if package included journey

packageItem_journey_travelTime

INTEGER

Journey time - if package included journey

packageItem_onSale

BOOLEAN

True if package on sale

packageItem_originalPrice_baseValue

FLOAT

Total package original price

packageItem_packageItemType

STRING

Type of item being part of the package

packageItem_packageType

STRING

Package type

packageItem_price_baseValue

FLOAT

Total package price

package_boardBasis

STRING

Package board basis if applicable

package_deposit_baseValue

FLOAT

Package deposit

package_discount_baseValue

FLOAT

Package discount

package_duration

INTEGER

Package duration

package_holidayType

STRING

Package holiday type

package_lineOfBusiness

STRING

Package line of business

package_name

STRING

Package name

package_numberOfAdults

INTEGER

Number of adults

package_numberOfChildren

INTEGER

Number of children

package_numberOfInfants

INTEGER

Number of infants

package_originalPrice_baseValue

FLOAT

Package total original price

package_packageProvider

STRING

Package provider

package_price_baseCurrency

STRING

Package price currency

package_price_baseValue

FLOAT

Package price in base currency

package_subtotalIncludingTax_baseValue

FLOAT

Package subtotal including tax

package_subtotal_baseValue

FLOAT

Package subtotal excluding tax

package_tax_baseValue

FLOAT

Package tax

package_total_baseValue

FLOAT

Package total value

package_type

STRING

Type of package

package_startTs

INTEGER

Package start timestamp (in epoch unix time)

unique_row_id

STRING

Unique ID for this row

view_id

STRING

Unique view ID

session_id

STRING

Unique session ID

entrance_id

STRING

Unique entrance ID

interaction_type

STRING

Type of interaction

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 Source View #2

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

* QP fields:context_id

Measure

COUNT(DISTINCT qp_bi_entrance.context_id, 1000000)

qp_bi_entrance

Entrances

Entrances

- Count of unique combinations of a context_id and an entrance_number

* If above 1.000.000, the result is approximated

* QP fields: context_id, context_entranceNumber

Measure

COUNT(DISTINCT qp_bi_entrance.entrance_id, 1000000)

qp_bi_entrance

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 Source View #2

Goal Achieved

Distinct Goals

- Count of unique goal_ids

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

* QP fields: goalId

Measure

COUNT(DISTINCT qp_bi_goal_achieved.goalId, 1000000)

qp_bi_goal_achieved

Goal Achieved

Goal Achieved Views

- Count of unique views

* If above 1.000.000, the result is approximated

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

* QP fields: context_id, context_viewNumber, goalId

Measure

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

qp_bi_goal_achieved

Goal Achieved

Goal Achieved Visitors

- Count of unique context_ids

* If above 1.000.000, the result is approximated

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

* QP fields: context_id, goalId

Measure

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

qp_bi_goal_achieved

Product interaction and sales

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

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), TIMESTAMPDATE((DATE(qp_bi_view.visitor_first_entry_date )))) / 7

qp_bi_transaction

'qp_bi_view'

Transactions

Average Order Value

- Average of transaction value of all transactions

* QP fields: basket_total_baseValue

Measure

AVG(qp_bi_transaction.basket_total_baseValue)

qp_bi_transaction

Transactions

Average Products per Order

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

* QP fields: basket_quantity, transaction_id

Measure

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

qp_bi_transaction

Transactions

Converters

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

* If above 1.000.000, the result is approximated

* QP fields: context_id, transaction_id

Measure

COUNT(DISTINCT qp_bi_transaction.context_id, 1000000)

qp_bi_transaction

Transactions

Days Since Previous Purchase

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

* QP fields: property_event_ts

Measure

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

qp_bi_transaction

Transactions

Hours Since Previous Purchase

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

* QP fields: property_event_ts

Measure

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

qp_bi_transaction

Transactions

Revenue per Converter

- Sum of transaction_total divided by count of unique context_ids

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

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

Measure

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

qp_bi_transaction

Transactions

Revenue per Visitor

- Sum of transaction_total divided by count of unique context_ids

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

* QP fields: basket_total_baseValue, context_id

Measure

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

qp_bi_transaction

'qp_bi_view'

Transactions

Session Conversion Rate

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

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

* QP fields: context_sessionNumber, context_id

Measure

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

qp_bi_transaction

'qp_bi_view'

Transactions

Transactions

- Count of unique transaction_ids (always exact count)

* QP fields: transaction_id

Measure

EXACT_COUNT_DISTINCT(qp_bi_transaction.transaction_id)

qp_bi_transaction

Transactions

Visitor Conversion Rate

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

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

* QP fields: context_id

Measure

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

qp_bi_transaction

'qp_bi_view'

Transactions

Weeks Since Previous Purchase

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

* QP fields: property_event_ts

Measure

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

qp_bi_transaction

Views

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

Views

Bounced Entrance (Yes / No)

- Did visitor leave on their first view in entrance?

* QP fields: context_entranceViewNumber, context_entranceNumber, context_id

Dimension

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

qp_bi_view

Views

Bounced Session (Yes / No)

- Did visitor leave on their first view in session?

* QP fields: context_sessionViewNumber, context_sessionNumber, context_id

Dimension

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

qp_bi_view

Views

Record Received Date

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

* QP fields: meta_recordDate

Dimension

DATE(TIMESTAMP(qp_bi_view.meta_recordDate))

qp_bi_view

Views

Returning Purchaser Yes No

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

* QP fields: context_conversionNumber

Dimension

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

qp_bi_view

Views

Time Data Points Date

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

DATE(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )

qp_bi_view

Views

Time Data Points Day of Week

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

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

qp_bi_view

Views

Time Data Points Hour of Day

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

HOUR(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )

qp_bi_view

Views

Time Data Points Month

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

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

qp_bi_view

Views

Time Data Points Month Name

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

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

qp_bi_view

Views

Time Data Points Quarter of Year

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

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

qp_bi_view

Views

Time Data Points Time

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

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

qp_bi_view

Views

Time Data Points Week

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

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

qp_bi_view

Views

Time Data Points Week of Year

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

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

qp_bi_view

Views

Time Data Points Year

- Timestamp of the page view

* QP fields: property_event_ts

Dimension

YEAR(MSEC_TO_TIMESTAMP(qp_bi_view.property_event_ts) )

qp_bi_view

Views

Weeks Since First Entry

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

* QP fields: property_event_ts, meta_recordDate, context_id

Dimension

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

qp_bi_view

Views

Daily Visitors

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

* If above 1.000.000, the result is approximated

* QP fields: context_id, meta_recordDate

Measure

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

qp_bi_view

Views

Entrance Bounce Rate

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

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

* QP fields: context_entranceViewNumber, context_entranceNumber, context_id

Measure

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

qp_bi_view

Views

Session Bounce Rate

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

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

* QP fields: context_sessionViewNumber, context_sessionNumber, context_id

Measure

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

qp_bi_view

Views

Unique Visitors

- Count of unique context_ids

* If above 1.000.000, the result is approximated

* QP fields: context_id

Measure

COUNT(DISTINCT qp_bi_view.context_id, 1000000)

qp_bi_view

Views

Views

- Count of unique combinations of a context_id and a view_number

* If above 1.000.000, the result is approximated

* QP fields: context_id, context_viewNumber

Measure

COUNT(DISTINCT qp_bi_view.view_id, 1000000)

qp_bi_view

Views

Visitor Bounce Rate

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

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

* QP fields: context_viewNumber, context_id

Measure

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

qp_bi_view

Views

Visitor Bounce Rate on Page

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

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

* QP fields: context_sessionViewNumber, context_id, context_sessionNumber

Measure

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

qp_bi_view

Views

Visitor Return Rate

- Share of visitors considered as returning visitors in all visitors

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

* QP fields: context_sessionNymber, context_id

Measure

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

qp_bi_view

Visitor pulse

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

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