Cannot find the relationship between Clickstream Datafeed and Data WareHouse

[huang_kuanyao]

Hi everyone,

I have data from Adobe Analytics coming from "Clickstream Datafeed" and "Data WareHouse" separately (same Report Suite ID, same Time period).

As I began to analyze the data,  I just found it is difficult to integrate the two data source.

For example, I have [mobile_device_type] and [Last Touch Channel]  in "Data WareHouse",

but I cannot find the two columns in the "Clickstream Datafeed".

How do I get the relationship of  [mobile_device_type] and [Last Touch Channel] between the two data sources ?

BTW, I found there are [mobile_id] and [va_closer_id] in "Clickstream Datafeed", but since they are all numbers, I don't know whether it is useful or not.

(I am appreciate it if there is any reference table regarding the relationship between the two data sources)

[TanmayMathur]

Hi, 

I suppose this DataFeed Column Reference Document will help you out - https://marketing.adobe.com/resources/help/en_US/sc/clickstream/datafeeds_reference.html

Let me know if this fulfills your mapping requirements. Thanks!

Tanmay

[huang_kuanyao]

Hi,

I have read that, but there is no mention between  

 [mobile_device_type] and [Last Touch Channel] in "Data WareHouse"

AND  [mobile_id] and [va_closer_id] in "Clickstream Datafeed"., 

--> so I cannot get the  [mobile_device_type] and [Last Touch Channel] information from "Clickstream Datafeed".

Do you know where can I find it ?

Thank you so much!

[TanmayMathur]

Hi, 

Along with the feed you must have received zipped .TSV files. That folder contains the lookup tables required for the columns. Do you see a related lookup file in that folder?

Tanmay

[huang_kuanyao]

Hi Tanmay,

Thank you for your reply.

I have checked the files you mentioned, including

browser.tsv
browser_type.tsv
color_depth.tsv
column_headers.tsv
connection_type.tsv
country.tsv
event.tsv
hit_data.tsv
javascript_version.tsv
languages.tsv
list.txt
operating_systems.tsv
plugins.tsv
referrer_type.tsv
resolution.tsv
search_engines.tsv

, but there is no lookup files I need. (I have checked all the tsv files, different from what I am searching for in the Dataware house)

Huang

[arnold_k]

For mobile device data, you need to speak to a Client Care person and ask for mobile attributes to be enabled. This will give you mobile_attributes.tsv and carrier.tsv. You can then lookup mobile_id in the clickstream and map it to a Mobile Device Type in mobile_attributes.

In terms of the relationship though, Last Touch Channel is a calculated field, based on search_engine, referrer_type, and other rules, so you won't find it in the Clickstream in a readily-usable format. If you really need them to join, then you'll need to emulate the Last Touch Channel calculation in Clickstream, e.g. if referrer_type = 3 and campaign is null, then "Natural Search".

[Gigazelle · Accepted solution]

The numeric lookup to va_closer_id can only be found in the Marketing Channel manager in the admin console. If you go there, you'll see all numbers and their associated channel names.

[paulas50095593]

when there is a 0 in the data feed tied to va_closer_id, what does this stand for?  we're assuming that no value was set for this and shows up in reporting as None, but want to confirm.