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

access.png

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.

Note: By default, the dataset is created in a US multi-region. Should you wish to select a different region, please use the BigQuery interface to create the dataset using the appropriate region. Please make sure you apply the same multi-region (US/EU) when creating each dataset since it is essential for data collection to work properly.

image (4).png

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:

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.

set_dataset_v2.png

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

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.

step_3_blended.png

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.

ignore_running.png

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.

image (3).png

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 ‘Sessionization’ template. Choose this template to get a BigQuery table with session data.
4.3. Click ‘Create & Setup’ button.

sessionization.png

4.4. The ‘Sessionization’ 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 ‘Sessionization’ 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.

cost_attribution.png

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 ‘Sessionization’ transformations as Dependency items.

trigger_1.png

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.

st_atr_model.png

6.4. The ‘Standard Attribution Models’ uses the data stored in the owoxbi_ga4_sessions table, which is a result of the Sessionization 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 Sessionization.

trigger_attributrion_models.png

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.

CMO_dashboard.png

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.

trigger_dashboard.png

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

workspace--solution.png

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.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.