Create REQUEST DataModel in CubeJS Schema

[freddyDOTCMS]

Parent Issue

• Spike: Determine changes needed to Clickhouse and CubeJS schemas needed to support Content Analytics #29481
• Spike: Define specification and scope to configure Content Analytics and perform raw BE queries on the data #29477

User Story

Currently, we have a DataModel that is entirely linked to the Experiment Goal, but for our initial CA version, we will not be supporting the Goal. Therefore, we can leave the existing DataModel as is and create a new one. In this new DataModel, we can group data by request using the new request_id attribute that we need to create and it going to be crated in another issue.

this new DataModel can have a syntax like this one here

Acceptance Criteria

We need to be allow to query using this new DataModel using the new attributes describe in this document .

You can test using the query describe here, but don't limit for just these you can use and document other ones.

Proposed Objective

Core Features

Proposed Priority

Priority 2 - Important

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

No response

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

No response

[freddyDOTCMS]

You need to run this script to make de ClickHouse Database enabled to this changes

ALTER TABLE events ADD COLUMN object_id String;
ALTER TABLE events ADD COLUMN object_title String;
ALTER TABLE events ADD COLUMN object_content_type_id String;
ALTER TABLE events ADD COLUMN object_content_type_name String;
ALTER TABLE events ADD COLUMN object_content_type_var_name String;
ALTER TABLE events ADD COLUMN object_response String;
ALTER TABLE events ADD COLUMN object_forward_to String;
ALTER TABLE events ADD COLUMN object_detail_page_url String;
ALTER TABLE events ADD COLUMN object_url String;
ALTER TABLE events ADD COLUMN comefromvanityurl bool;
ALTER TABLE events ADD COLUMN request_id String;
ALTER TABLE events ADD COLUMN host String;
ALTER TABLE events ADD COLUMN sessionid String;
ALTER TABLE events ADD COLUMN sessionnew bool;

This Script is going to be run automatically after this issue:

• Create Idempotent SQL Scripts for ClickHouse Schema Changes in Analytics Infrastructure #29878

To connect with the CH Database you can use the follow parameters:

[freddyDOTCMS]

PR: #30039

[freddyDOTCMS]

Note to QA: it should be test with #29712

[freddyDOTCMS]

Pass Internal QA: We just have problems retrieving the detail page data, but we already work it on other issues:

• feat(UVE) Adding Time Machine capabilities to PageResource Refs: #30027 #30069
• Fix/Improve some stuff in CA #30179

[josemejias11]

Approved: Tested on trunk_0028f3c, Docker, macOS 14.5, FF v126.0.1