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.

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 `ga-to-ba.ba_data.from_ga4` 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
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.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

Allow Akeneo to access the Business Analytics view

To allow Akeneo to access the Business Analytics view, we have to give the right permissions to the service account

sa-for-datasync@akecld-prd-perf-analytics-prod.iam.gserviceaccount.com

At the dataset level: grant the BigQuery Data Viewer role to the service account
At the project level: grant the BigQuery Job User role to the service account

Grant the BigQuery User role on the project

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

Grant the BigQuery Data Viewer role on the dataset

Use the dataset 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