[Growth] Performance Dashboard (with GA4 BigQuery Export)

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 an export of events from GA4 Google Cloud Platform, GA4 ≈ 10 minutes
3 Merge cost data into a unified structure (one currency) OWOX BI Pipeline
OWOX BI Transformation with Templates Gallery
≈ 5 minutes
3* Merge cost data into a unified structure (multiple currencies) OWOX BI Transformation with Templates Gallery ≈ 10 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

*Choosing step. Use this step in case you have several currencies, that need convertation
**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.

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.

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 an export of events from GA4

Please, follow this instruction to create and set up the daily streaming export from GA4 to Google Big Query.

After successfully setting up the daily streaming export to the Google Big Query and initiating it, you will see several 'events_YYYYMMDD' tables 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.

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

share_dataset.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 fourth step.

Step 3*. Merge cost data into a unified structure (multiple currencies)

3.1 Create the Openexchangerates.org  → Google BigQuery pipeline for having a table with currencies rates, divided by days.  

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

blended_currency.png

3.5. The ‘Blended AdSpend + GA4 Cost Import (With Currency Convert)’ 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.6. 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 be used in the CMO Dashboard.

ignore_running.png

3.7. To specify values for variables in the template, please follow this instruction. During this part, you should set up the final currency, in which all calculations would be made. 
3.8. 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.

share_dataset.png

3.9. Run the Transformation manually. Click on the “Run” button to start the sequential execution of the Operations in this Transformation.
3.10. 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.11. 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.12. (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 Import (With Currency Convert)’ 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 and unified currency.

Proceed to the fourth 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 (GA4 BigQuery Export)’ template. Choose this template to get a BigQuery table with session data.
4.3. Click ‘Create & Setup’ button.

sessions_template.png

4.4. The ‘Merge Events into Sessions (GA4 BigQuery Export)’ template is ready to transform the data stored in the BigQuery export dataset, which you created in Step 2. If you haven't done this in previous steps, 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_ table has already been created.

If you want to learn more about the ‘Merge Events into Sessions (GA4 BigQuery Export)’ 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 ‘export_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 data set with GA4 export data. If your data is collected in another dataset, make sure that you granted access to the dataset in Step 3.7 (or 3.8 for multi currencies).
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 (GA4 BigQuery Export)’ transformations as Dependency items.

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

attribution_model.png

6.4. The ‘Standard Attribution Models’ uses the data stored in the export_ga4_sessions table, which is a result of the Merge Events into Sessions (GA4 BigQuery Export) 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 (GA4 BigQuery Export).

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

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

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.