How to use Google Analytics as the source of Sales Channel data

Summary

The Akeneo app for Business Analytics (BA) requires a specific format for collecting business data. If you use Google Analytics 4 (GA), you can map the data with a BigQuery View.

A BigQuery view executes its associated query when it is acceded. Every time a new file from Google Analytics 4 is added, its data will be in the view the next time the view is requested.

The dataset containing the Google Analytics data must be on the same region as your PIM. We don't manage multi-region (it won't work if you set up the “EU" location whereas your PIM is located in “europe-west1”).

1. Google Analytics (GA) data in BigQuery

First, you need to have Google Analytics data in a BigQuery table.

Create a view to the Business Analytics (BA) format

We will create a view to map the Google Analytics format to the Business Analytics format.

The dataset where we will create the view has to be in the same region as the dataset of the GA table.

Google Analytics source

Business Analytics destination

To create a view, you can use this request:

CREATE VIEW `<YOUR_GCP_PROJECT>.<YOUR_BA_DATASET>.<CHOOSE_A_TABLE_NAME>` AS (
  SELECT
    CAST(
        PARSE_DATE ('%Y%m%d', event_date) AS DATE
    ) AS date,
    item_id as product_id,
    SUM(
        IF(
            event_name = "purchase",
            item_revenue,
            0
        )
    ) AS total_revenue,
    SUM(
        IF(
            event_name = "purchase",
            quantity,
            0
        )
    ) AS units_sold,
    SUM(
        IF(
            event_name = "add_to_cart",
            quantity,
            0
        )
    ) AS add_to_cart,
    COUNTIF (event_name = 'view_item') AS page_views,
    COUNTIF (event_name = "purchase") AS number_of_orders

FROM
    `<YOUR_GCP_PROJECT>.<YOUR_GA4_DATASET>.events_*`,
    UNNEST (items)
GROUP BY
    1,
    2
HAVING
    item_id != "(not set)"
    AND item_id IS NOT NULL
);

Example of the request with the previous screenshot values:

CREATE VIEW `akeneo_business_analytics.view_data_from_google_analytics` AS (
  SELECT
    CAST(
        PARSE_DATE ('%Y%m%d', event_date) AS DATE
    ) AS date,
    item_id as product_id,
    SUM(
        IF(
            event_name = "purchase",
            item_revenue,
            0
        )
    ) AS total_revenue,
    SUM(
        IF(
            event_name = "purchase",
            quantity,
            0
        )
    ) AS units_sold,
    SUM(
        IF(
            event_name = "add_to_cart",
            quantity,
            0
        )
    ) AS add_to_cart,
    COUNTIF (event_name = 'view_item') AS page_views,
    COUNTIF (event_name = "purchase") AS number_of_orders


FROM
    `google_analytics.events_*`,
    UNNEST (items)
GROUP BY
    1,
    2
HAVING
    item_id != "(not set)"
    AND item_id IS NOT NULL
);

The new view should be created in the dataset

Authorize the view to access the source dataset

We have to give to the view the permission to access the source dataset . To do it, we select the source dataset, here google_analytics, then click on ‘SHARING’ → ‘Authorize Views’

In the new opened panel, fill the field Authorize view with the name of the new created view, here ga-to-ba.akeneo_business_analytics.view_data_from_google_analytics.

Allow Akeneo to access the Business Analytics view

To allow Akeneo to access the BA view, we have to give the right permission to the service account sa-for-datasync@akecld-prd-perf-analytics-prod.iam.gserviceaccount.com

At the view level: grant the BigQuery Data Viewer role to the service account.

Grant the BigQuery Data Viewer role on the dataset

Use the view details view

Add a new access

Grant the BigQuery Data Viewer role to the sa-for-datasync@akecld-prd-perf-analytics-prod.iam.gserviceaccount.com service account

Use the BA view as the source of the Sales Channel in the BA App

Now, we can create a new Sales Channel in the BA App with the ID of the new BA view.

“Et voilà!” You can now wait for the next day to see your business data in the Analytics dashboard of your PIM!

Akeneo Help Center