Hello everyone,
I'm currently working on a project that involves modeling Adobe Clickstream data, and I'm hoping to get some help and guidance from the experts in this forum.
I'm relatively new to Adobe Clickstream data modeling and would appreciate any advice or resources that can help me get started. Specifically, I'm looking to better understand how to structure and organize the data to achieve the following requirements.
The customer has categorized the reporting outcomes into 4 buckets - OOTB, Baseline, Custom and E-Commerce.
- OOTB (Out of the Box)
Traffic by Device Type, by Country, by City, by Month, by Day of Week, by Referring Domain, by Weekday/Weekend, by Browser & Browser Detail, by Pages, by Server, by Screen Orientation, by Operating System Type, by Mobile Carrier
OOTB Metrics common for all the above reports: Unique Visitors, Visits, Page Views, Total Seconds Spent, Bounces, Single Page Visits
Problem is How and where to model logic behind these metrics because one-to-one mapping between clickstream and metrics does not exist.
Kindly advise.
- Baseline
Traffic by ECID, by Domains, by Channel, Link Tracking, Site Exit, Download, Pages Reports
Metrics commmon to above reports are denoted by
Custom Event 101, 102, 103, and so on.
- Custom
Form Tracking, Site Search Tracking, Video Tracking, Survey Tracking, Webinar Tracking, Login Tracking Reports
Metrics commmon to above reports are denoted by
Custom Event 92, 93, 94, and so on.
- E-Commerce
Journey Type by Journey Stage,
Product Name by Product Detail & Price per dose
Metrics commmon to above reports are denoted by
event 175
event 176
event 177;
How to model Clickstream data to show these events as Metrics;
The Problem is that the Clickstream data does not have a one-to-one mapping between columns and metrics.
The Clickstream data has a column (post_event_list) in which for every hit we have references such as 1, 2, 3 and so on.
1 indicates Custom Event 1
2 indicates Custom Event 2
3 indicates Custom Event 3
For e.g.
when user1 opened the webpage in the morning and downloaded a form and watched a video, it resulted in following hit#1
userid=1, session1, post_event_list=1,2,3
after 5 minutes, the same user1 opened another webpage and listened to an audio and filled out the form, it would result in following hit#2
userid=1, session2, post_event_list=4,5
Looking up the SDR will tell us the meaning of Custom Event 1, 2 and 3.
These events just find a mention in the Solution Design Reference (SDR)
Adobe Clickstream data exists at the second level, meaning, that for every 1 ECID (Adobe Experience Cloud ID), we have multiple records.
For 1 day, we have 100,000 records (hits) out of which for almost every ECID (Adobe Experience Cloud ID) we have 3 records. There are ~30,000 unique session IDs for 1 day, which means that one user who spent 5 minutes on the website doing activities such as browing, watching video, audio, downloading forms, filling out form has resulting in multiple hits.
Using the data warehouse as base, the customer is looking to build reports similar to the ones available in Adobe Analytics Workspace.
I am trying to understand how to model the clickstream data such that reporting tools such as Tableau, MicroStrategy can make reports on top of them.
How do I create an Entity-Relationship diagram of the Clickstream data and break it into Facts and Dimensions?
If anyone in this forum has experience with Adobe Clickstream data modeling or can point me to some resources or communities where I can learn more, I would greatly appreciate it.
Thank you in advance for your help!
I really appreciate it.
