[Professional] Performance dashboard (with OWOX BI Streaming events)

Overview

This solution is for you if you want to get the CMO Dashboard, designed specifically for marketing leaders so you keep cross-channel campaign performance under control based on accurate acquisition campaign tracking, based on reliable data at a granular level, without the hassle of manual calculations or switching between different reporting tools.

If you want to get 20+ dashboards based on your own advertising and website user behavior data, then watch our Webinar with step-by-step instructions and explanations.

Before you start, please, make sure that:

• You are using the Google Cloud Platform and have an active project (including BigQuery Admin or Data Owner role). If you are not using GCP yet — please read this guide on how to set up GCP.
• Active Trial or Paid Subscription is available in your plan. If not, please contact us at bi@owox.com or start the chat at the bottom right corner of your screen.

Solution steps

Step	Task	Interface	Timing
0	Set up Google BigQuery if you don't have it yet	Google Cloud Platform	≈ 2 minutes
1	Set up cost data collection to BigQuery with OWOX BI Pipelines	OWOX BI Pipeline	≈ 5 minutes
2	Create and set up the GA4-Like event-based web streaming	OWOX BI Streaming	≈ 2 minutes
3	Merge cost data into a unified structure	OWOX BI Transformation with Templates Gallery	≈ 5 minutes
4	Group raw events into meaningful user' sessions	OWOX BI Transformation with Templates Gallery	≈ 5 minutes
5	Attribute ad costs to sessions	OWOX BI Transformation with Templates Gallery	≈ 2 minutes
6*	Calculate standard attribution models	OWOX BI Transformation with Templates Gallery	≈ 2 minutes
7	Prepare your data for CMO Dashboard	OWOX BI Transformation with Templates Gallery	≈ 5 minutes
8	Build your dashboard in Looker Studio	Looker Studio	≈ 2 minutes

*Optional step. You won’t be able to get 1 report with Attribution comparison

Note: If you already have your advertising cost data collected into Google BigQuery tables via OWOX BI Pipelines, you can skip Step 1.

Step 0. Set up Google BigQuery

If you don't use BigQuery, please, follow this instruction to create a Google Cloud Platform project and grant the necessary permissions (BigQuery Admin or BigQuery Data Owner role) to the team members who will work with your data in OWOX BI.

How to check the result: If this step is done correctly, the user’s emails will appear in the list of Principals on the IAM & Admin page. Proceed to the next step.

Step 1. Set up cost data collection to BigQuery with OWOX BI Pipelines

1.1. Go to bi.owox.com, sign in with your Google account, and create a new OWOX BI project with a 7-days free trial (no card required).
1.2. Please, create the dataset with the default name 'OWOXBI_BlendedAdSpend' and choose it in each pipeline for collecting your data.

Critical important: We recommend using the default dataset, 'OWOXBI_BlendedAdSpend', for importing cost data in all your Pipelines.
If you have specific requirements for the data location in Google BigQuery, such as legal or performance-related considerations, please create a new dataset in the desired region/location and select it during the Pipeline setup.

1.3. On the Workspace page, create as many Pipelines as you need from each Ad account in Ads platforms to collect cost data daily into Google BigQuery.

List of the most popular integrations:

• Facebook Ads & Instagram Ads → Google BigQuery
• LinkedIn Ads → Google BigQuery
• Google Ads → Google BigQuery *
• Bing Ads → Google BigQuery
• Twitter Ads → Google BigQuery
• Criteo → Google BigQuery

Note: You can import your Google Ads data in two ways: Google Ads by-request pipeline or Data Transfer. Both of these methods will work well with the CMO Dashboard and allow you to access your Google Ads data in an organized, easy-to-view format.

By clicking the links above, you will find step-by-step instructions on how to set up each integration. When setting up, pay attention to two important points:

– Specify the 'OWOXBI_BlendedAdSpend' dataset you created earlier.

– You can set the start date of the initial data import for a maximum of up to 2 months. The advertising cost data will be uploaded to the BigQuery dataset the next day at the scheduled time.

How to check the result:

– In the OWOX BI interface (on the Workspace page), you will have a diagram with your pipelines and destination datasets/tables.
– After 5 minutes, you will receive a bunch of empty tables for each Source you’ve set up in your BigQuery dataset. The following day, the tables will be filled with the raw cost data.

Proceed to the next step.

Step 2. Create and set up the GA4-Like event-based web streaming

Please, follow this instruction to create and set up the GA4-Like event-based web streaming. We recommend creating a dataset with ‘OWOXBI_GA4_Streaming’ name.

Choose how to send data from your website to the OWOX BI access point and follow the instructions below:

• Instructions on how to configure your GTM container (recommended)
• Instructions on how to change the standard gtag.js file
• Instructions on how to send events from your server

After successfully setting up the data-sending process to the OWOX BI access point and initiating it, you will see the first events_intraday_YYYYMMDD’ table in your BigQuery dataset.

Step 3. Merge cost data into a unified structure

3.1. Open the Workspace page, click the ‘New’ button, and select the ‘Transformation’ item.
3.2. Now, you see a bunch of prebuilt data transformation templates. Select the ‘Blended AdSpend + GA4 Cost Data Import’ template.
Choose this template, and you will get a BigQuery table with a unified structure.
3.3. Click ‘Create & Setup’ button.

3.4. The ‘Blended AdSpend + GA4 Cost Data Import’ template is ready to transform the data stored in the ‘OWOXBI_BlendedAdSpend’ dataset (the one you’ve selected in Step 1.2 ). If your data is collected into another dataset, please share it with OWOX BI by following this instruction.
3.5. We recommend disabling any operations that are not being used. For example, if you don’t have Bing pipelines, you have to choose ‘A - Bing’ operation and press the ‘Ignore for running’ option. Additionally, please disable the ‘O - GA4 CostData Import’ operation. It won’t use in the CMO Dashboard.

3.6. To specify values for variables in the template, please follow this instruction.
3.7. Please, check the Used dataset tab in the transformation. If you see the ‘No access to datasets’ error, share the access to the dataset with the ‘Share used dataset’ button.

3.8. Run the Transformation manually. Click on the “Run” button to start the sequential execution of the Operations in this Transformation.
3.9. If the Transformation run is completed successfully, on the “Run history” tab, you will see the status ‘Done’. If the status is ‘Failed’, please read the error message and try to fix your Transformation. Contact bi@owox.com if you have questions.
3.10. Open the ‘Triggers’ tab and add a Time trigger by following this instruction. We recommend selecting the 'Start everyday' option and specifying the hours and minutes between 7 and 8 a.m. based on your timezone.
3.11. (Optional) If you have additional costs collected in another Google Sheet document, then follow this guide to use it in your Transformation as a new BigQuery table. Contact bi@owox.com if you have questions.

If you want to learn more about ‘Blended AdSpend + GA4 Cost Data Import’ template — please, check the article by the link.

How to check the result: In the BigQuery interface, reload the page, and you will get the ‘BlendedAdSpend’ table with cost data prepared in a unified structure.

Proceed to the next step.

Step 4. Group raw events into meaningful user' sessions

4.1. Open the Workspace page, click the ‘New’ button, and select the ‘Transformation’ item.
4.2. Select ‘Merge Events into Sessions’ template. Choose this template to get a BigQuery table with session data.
4.3. Click ‘Create & Setup’ button.

4.4. The ‘Merge Events into Sessions’ template is ready to transform the data stored in the ‘OWOXBI_GA4_Streaming’ dataset (which is our recommendation in Step 2 of this solution). If your data is collected in other datasets, please share it with OWOX BI by following this instruction.
4.5. Please follow this instruction to specify values for other variables in the template.
4.6. Run the Transformation manually. Click on the “Run” button to start the sequential execution of the Operations in this Transformation.
4.7. If the Transformation run is completed successfully, on the “Run history” tab, you will see the status ‘Done’. If the status is ‘Failed’, please read the error message and try to fix your Transformation. Contact bi@owox.com if you have questions.
4.8. Open the ‘Triggers’ tab and add a Time trigger by following this instruction. We recommend selecting the 'Start everyday' option and specifying the hours and minutes after midnight by your timezone when the events_intraday_ table has already been created.

If you want to learn more about the ‘Merge Events into Sessions’ template, please check the article by the link.

How to check the result: In the BigQuery interface, reload the page, and you will get the ‘owoxbi_ga4_sessions’ table with session data.

Proceed to the next step.

Step 5. Attribute ad costs to sessions

5.1. Open the Workspace page, click the ‘New’ button, and select the ‘Transformation’ item.
5.2. Select  ‘Costs Attribution’ template. You will get dispersed costs allocated to each session. 
5.3. Click ‘Create & Setup’ button.

5.4. The ‘Costs Attribution’ template is ready to transform the data stored in the ‘OWOXBI_GA4_Streaming’ dataset (which is our recommendation in Step 2 of this solution). If your data is collected in another dataset, make sure that you granted access to the dataset in Step 3.7
5.5. Please follow this instruction to specify values for other variables in the template.
5.6. Run the Transformation manually. Click on the “Run” button to start the sequential execution of the Operations in this Transformation.
5.7. If the Transformation run is completed successfully, on the “Run history” tab, you will see the status ‘Done’. If the status is ‘Failed’, please read the error message and try to fix your Transformation. Contact bi@owox.com if you have questions.
5.8. Open the ‘Triggers’ tab and add a Dependency trigger by following this instruction. Please choose ‘Blended AdSpend + GA4 Cost Data Import’ and ‘Merge Events into Sessions’ transformations as Dependency items.

If you want to learn more about the ‘Costs Attribution’ template, please check the article by the link.

How to check the result: In the BigQuery interface, reload the page, and you will get the ‘CostAttributionCheck’ table.

Proceed to the next step.

Step 6. (Optional) Calculate standard attribution models

Create this transformation if you want to compare the impact of different standard attribution models on your business data. Otherwise, skip this step and move on to the next one.

6.1. Open the Workspace page, click the ‘New’ button, and select the ‘Transformation’ item.
6.2. Click on ‘Standard Attribution Models’ template.
6.3. Click ‘Create & Setup’ button.

6.4. The ‘Standard Attribution Models’ uses the data stored in the owoxbi_ga4_sessions table, which is a result of the Merge Events into Sessions template work (Step 4).
6.5. Please follow this instruction to specify values for other variables in the template.
6.6. Run the Transformation manually. Click on the “Run” button to start the sequential execution of the Operations in this Transformation.
6.7. If the Transformation run is completed successfully, on the “Run history” tab, you will see the status ‘Done’. If the status is ‘Failed’, please read the error message and try to fix your Transformation. Contact bi@owox.com if you have questions.
6.8. Open the ‘Triggers’ tab and add a Dependency trigger to run this transformation after Merge Events into Sessions.

If you want to learn more about ‘Standard Attribution Models’ template — please, check the article by the link.

How to check the result: In the BigQuery interface, reload the page, and you will get the list of tables:

• attributionLNDC table
• attributionFirstClick table
• attributionLinear table
• attributionPositionBased table
• attributionTimeDecay table
• attribution table
• attributionCheck table.

Proceed to the next step.

Step 7. Prepare your data for CMO Dashboard

CMO Dashboard data preparation template uses the results of several previous transformations and aggregates data in one, flat and suitable for dataset.

7.1. Open the Workspace page, click the ‘New’ button, and select the ‘Transformation’ item.
7.2. Select ‘CMO Dashboard’ template.
7.3. Click ‘Create & Setup’ button.

7.4. To specify values for other variables in the template, please follow this instruction.
7.5. Run the Transformation manually. Click on the “Run” button to start the sequential execution of the Operations in this Transformation.
7.6. If the Transformation run is completed successfully, on the “Run history” tab, you will see the status ‘Done’. If the status is ‘Failed’, please read the error message and try to fix your Transformation. Contact bi@owox.com if you have questions.
7.7. Open the ‘Triggers’ tab and add a Dependency trigger to run this transformation after Cost Attribution. Thus, you will get fresh data without any mistakes.

How to check the result: In the BigQuery interface, reload the page, and you will get the ‘CMO_dashboard_table’ table. Proceed to the next step.

Step 8. Build your dashboard in Looker Studio

8.1. Make a copy of THIS dashboard.
8.2. Connect Google BigQuery as the data source to Looker Studio following this instruction. Based on the data in CMO_dashboard_table, you'll receive the dashboard filled with your data.

Solution on the Workspace

With our brand-new OWOX BI Workspace, you will get access to a comprehensive overview of your solution, allowing you to control and troubleshoot any issues that may arise easily. Moreover, the diagram view presents an interactive data flow visualization that you can expand by adding new Pipelines, Streaming, and Transformations with just a few clicks using our Templates gallery.