Exporting Raw Data Using the Daily Activity Stream

With the Daily Activity Stream, you can export Dynamic Yield raw data and ingest it into your own analytics platform. This enables you to connect the Dynamic Yield data to additional data sources in your database and build customized reports that are tailored to your unique business needs.

The export consists of two datasets:

• Raw data: Includes raw interactions with Dynamic Yield variations, events, and pageviews.
• Attribution data: Includes the relationship between events and the variations they were attributed to according to your experience settings.

Data is exported daily into a secured Amazon S3 bucket, where it's stored for 30 days.
Data is exported as Apache Parquet files, a format optimized for large data sets. 

Data sets

Raw data

This data set contains all interactions of the following types:

• Variation engagement: Variation clicks and impressions. This can include information such as the campaign and variation names and the recommendation strategy.
• Events: Any event that was fired, such as Purchase or Newsletter Subscription, including the properties sent in the event object.
• Pageviews: Sent upon every page load, includes information about the page context (HOMEPAGE, CATEGORY).

All interaction in the data set include some common attributes (such as DYID, session, and event timestamp). In addition, each data type includes some attributes (columns) that are unique. These do not contain data in rows of other data types, which remain empty—this does not indicate a problem or missing information. 

The column listing the interaction type in the data set is eventType. The event types are UIA (pageviews), DPX (event hits), and VARIATION_ENGAGEMENT for variation impressions or clicks.

Here are some sample attributes that are unique to each type:

Data type	eventType	Additional Attributes (Examples)
Pageview	UIA	URL, page context
Event hit	DPX	Event properties, event value
Variation impression or click	VARIATION_ENGAGEMENT	Campaign name, variation name

For example, if a user views 3 pages, triggers 2 events, and has 1 impression of a variation, the export contains 6 rows.

The following tables include the full list of attributes available for each eventType.

Attribution data

This data set contains the relationship between a variation and the events attributed to it according to the experience settings.

Each data set record represents a distinct event-variation combination.

Note that there is usually a one-to-many relationship between events and variations. For example, if 1 purchase was attributed to 2 variations (control of test A, variation of test B), the data set contains 2 rows.

Field	Description
interactionId (int)	The interaction’s identifier (for a specific event, for example, a specific purchase). It can be used to join this data set to the attribution data set.
eventType (varchar)	The type of event being attributed to a variation. Currently, only DPX events are exported.
dyid (int)	The identifier of the user who triggered the attributed event.
experimentId (int)	The Experiment ID to which the event was attributed.
versionId (int)	The Version ID of the Experiment to which the event was attributed.
variations (array)	The array of Variation IDs to which the event was attributed. This array usually contains a single value, but can contain multiple values if the experience is an Item List. This identifier corresponds to attributionVariationIds in the raw dataset.
eventId (int)	The ID of the DPX event attributed to the variation. For example, for a purchase, this would be the event ID of the Purchase event.
eventValue (int)	The value of the DPX event attributed to the variation. For example, for a purchase, this would represent revenue connected to the Purchase event. The value is in cents.

Turning on the Daily Activity Stream

To turn on the Daily Activity Stream:

1. Go to Settings › General Settings › Daily Activity Stream.
   
2. Click Turn on daily export.
3. Copy the S3 bucket path and credentials to a secure location. Note that it's only displayed once.
   Lost your credentials?
   Click the additional options iconand then click Generate New Credentials. Keep in mind that you can only generate new credentials once.
4. That's it! The first data set will be exported after midnight, following which new data will be added daily.

After activating the Daily Activity Stream (turning the toggle on), the page also displays the status of the export.

You can always disable the export in the options menu. If you disable the export and then enable it again, your S3 bucket remains the same, but you'll be given a new set of credentials. 

Accessing your S3 bucket

After you activate the Daily Activity Stream, you'll be given access to an S3 bucket with the following path:

• s3://dy-raw-data-export/sectionId=1234567, for the US data center
• s3://dy-raw-data-export-eu/sectionId=1234567, for the EU data center.

Under this path, you'll find subfolders for each date, where parquet files are stored.
For example, this path contains all the data collected on 2022-01-01, according to the time zone configured in your General Settings:

s3://dy-raw-data-export-eu/sectionId=1234567/date=2022-01-01

Under this path, you'll find subfolders for each type of report

• This path includes the currently exported raw data:
  s3://dy-raw-data-export/sectionId=1234567/date=2023-01-01/reportType=raw
  
• This path includes the new attribution data:
  s3://dy-raw-data-export/sectionId=1234567/date=2023-01-01/reportType=attribution

Download the files locally via Terminal

1. Create a folder on your computer to contain the downloaded files. 
2. Open Terminal, and navigate to the folder you created. 
3. Enter the command aws configure.
4. When prompted, enter your access key and secret key, as generated in your feed sync.
5. When prompted, enter your data center region: eu-central-1 or us-east-1, as appropriate.
6. Upon the next prompt (default output), just press Enter. 
7. When prompted, enter aws s3 sync followed by the relevant S3 path (see Accessing your S3 bucket) and the local folder name created in step 1.
   For example:
   aws s3 sync s3://dy-raw-data-export/sectionId=123456/date=2024-01-01/reportType=raw/raw_data
8. The files download. You can see them in Terminal, and upon completion, you'll see them in the folder you created in step 1.

Import data into your analytics platform

To integrate the Daily Activity Stream data into your systems automatically, you might want to create a job to extract the Parquet files from S3 and load them into your database of choice. Here are some links to relevant articles for popular solutions:  

• Google BigQuery
• Microsoft SQL server
• Amazon Athena
• Amazon Redshift
• Snowflake
• Oracle 12c
• Databricks
• IBM Infosphere
• Informatica

SQL examples to process Daily Activity Stream data

After you've successfully imported the Daily Activity Stream data from the S3 bucket into your analytics platform, you can start processing it.

Parse the Raw data set Parquet file

-- parse parquet files i​n raw folder
create o​r replace table parsed_raw_data_daily_activity_stream
a​s
select
try_to_date(replace(SPLIT_PART(file_name, '/',2), 'date=','')) a​s date
,$1:audiences::variant a​s audiences 
,$1:browser::string a​s browser 
,$1:contextData::variant a​s context_data 
,$1:contextType::string a​s context_type
,$1:device::string a​s device
,$1:dyid::int a​s dy_id
,$1:eventId::int a​s event_id 
,$1:eventName::string a​s event_name 
,$1:eventValue::string a​s event_value 
,replace($1:eventProperties::string,'\\','') a​s event_properties 
,$1:eventType::string a​s event_type 
,$1:operatingSystem::string a​s operating_system
,$1:procTimestamp::int a​s proc_timestamp 
,$1:productIds::variant a​s product_ids
,$1:reqTimestamp::int a​s req_timestamp
,$1:screenResolution::string a​s screen_resolution 
,$1:sessionId::int a​s session_id
,$1:timestamp::int a​s timestamp 
,$1:url::string a​s url
,$1:urlClean::string a​s url_clean
,$1:userAgent::string a​s user_agent
,file_date
,$1:campaignId::int a​s campaign_id
,$1:campaignName::string a​s campaign_name
,$1:engagementType::string a​s engagement_type
,$1:experienceId::int a​s experience_id
,$1:experienceName::string a​s experience_name
,$1:experimentId::int a​s experiment_id
,$1:versionId::int a​s experiment_version_id
,$1:variationIds::variant a​s variation_ids
,$1:attributionVariationIds::variant a​s attribution_variation_ids
,$1:variationNames::variant a​s variation_names
,$1:skus::variant a​s skus
,$1:strategyId::int a​s strategy_id
,$1:strategyName::string a​s strategy_name
,$1:interactionId::int a​s interaction_id
,file_name
,$1 a​s json_object
from daily_activity_stream
where contains(file_name, '/reportType=raw/')
;

Explore the imported raw data set

-- preview first 100 records of raw data, ordered by timestemp
select * 
from parsed_raw_data_daily_activity_stream
order by timestamp
limit 100
;

-- see all event_types
select distinct
event_type
from parsed_raw_data_daily_activity_stream
limit 100
;

-- count events (type DPX)
select 
event_id
,event_name 
,count(*)
from parsed_raw_data_daily_activity_stream
where event_type = 'DPX'
group by all
;

-- see all purchases
select * 
from parsed_raw_data_daily_activity_stream
where event_id = {value_event_id}
limit 1000
;

-- aggregate raw by strategy
select 
strategy_id
,strategy_name
,count(distinct dy_id) a​s users
,sum(iff(engagement_type = 'STRATEGY_IMPRESSION', 1, 0)) a​s impressions
,sum(iff(engagement_type = 'CLICK', 1, 0)) a​s clicks
from parsed_raw_data_daily_activity_stream
where event_type = 'VARIATION_ENGAGEMENT'
and strategy_id is not null
group by strategy_id, strategy_name
order by impressions desc
;

-- aggregate raw by strategy and sku to get the number of impressions and clicks on each sku
select 
r.strategy_id
,r.strategy_name
,trim(p.value) a​s sku
,sum(iff(engagement_type = 'STRATEGY_IMPRESSION', 1, 0)) a​s impressions
,sum(iff(engagement_type = 'CLICK', 1, 0)) a​s clicks
from parsed_raw_data_daily_activity_stream r
,lateral flatten (input => r.skus) p
where event_type = 'VARIATION_ENGAGEMENT'
and strategy_id is not null
group by strategy_id, strategy_name, sku
;

Parse the Attribution data set Parquet file

-- parse parquet files i​n attribution folder
create o​r replace table parsed_attribution_daily_activity_streamd
a​s
select
try_to_date(replace(SPLIT_PART(file_name, '/',2), 'date=','')) a​s date
,$1:dyid::int a​s dy_id
,$1:eventId::int a​s event_id
,$1:eventType::varchar a​s event_type
,$1:eventValue::int a​s event_value
,$1:experimentId::int a​s experiment_id
,$1:interactionId::int a​s interaction_id
,$1:variations[0]::int a​s variation_id
,$1:versionId::int a​s version_id
,file_name
from daily_activity_stream
where contains(file_name, '/reportType=attribution/')
;

Explore the imported attribution data set

-- preview first 100 records of attribution
select * from parsed_attribution_daily_activity_streamd
limit 100
; 

-- preview the number of events and sum of events value per variation and event id
select 
variation_id
,event_id
,count(*) a​s event_count
,sum(event_value) a​s event_value
from parsed_attribution_daily_activity_streamd
where version_id = {value_version_id}
and date between {value_from_date} and {value_to_date}
group by variation_id, event_id
;

Join the Raw and Attribution data sets together

-- aggregate raw by variation
select
date
,campaign_id
,campaign_name
,experience_name
,experiment_version_id
,attribution_variation_ids[0]::int a​s attribution_variation_id
,trim(variation_names[0]) a​s variation_name
,strategy_id
,strategy_name
,count(distinct dy_id) a​s users
,sum(iff(engagement_type = 'IMPRESSION', 1, 0)) a​s impressions
,sum(iff(engagement_type = 'CLICK', 1, 0)) a​s clicks
from parsed_raw_data_daily_activity_stream
where event_type = 'VARIATION_ENGAGEMENT'
and campaign_id is not null
group by date, campaign_id, campaign_name, experience_name, experiment_version_id, attribution_variation_id, variation_name, strategy_id, strategy_name
;

-- aggregate attribution by variation
select 
date
,version_id
,variation_id
,event_id
,count(distinct dy_id) a​s unique_converters
,count(*) a​s event_count
,sum(event_value) a​s event_value
from parsed_attribution_daily_activity_streamd
group by date, version_id, variation_id, event_id
; 

-- put it together
with engagement a​s
(
  select 
  date
  ,campaign_id
  ,campaign_name
  ,experience_name
  ,experiment_version_id
  ,attribution_variation_ids[0]::int a​s attribution_variation_id
  ,trim(variation_names[0]) a​s attribution_variation_id
  ,count(distinct dy_id) a​s users
  ,sum(iff(engagement_type = 'IMPRESSION', 1, 0)) a​s impressions
  ,sum(iff(engagement_type = 'CLICK', 1, 0)) a​s clicks
  from parsed_raw_data_daily_activity_stream
  where event_type = 'VARIATION_ENGAGEMENT'
  and campaign_id is not null
  group by date, campaign_id, campaign_name, experience_name, experiment_version_id, attribution_variation_id, attribution_variation_id
)

,attribution a​s
(
  select 
  date
  ,version_id
  ,variation_id
  ,event_id
  ,count(distinct dy_id) a​s unique_converters
  ,count(*) a​s event_count
  ,sum(event_value) a​s event_value
  from parsed_attribution_daily_activity_streamd
  group by date, version_id, variation_id, event_id
)

,event_metadata a​s
(
  select 
  event_id
  ,event_name
  ,count(*) a​s event_count
  ,sum(event_value) a​s event_value
  from parsed_raw_data_daily_activity_stream
  where event_type = 'DPX'
  group by event_id, event_name
)

select
e.date
,e.campaign_id
,e.campaign_name
,e.experience_name
,e.experiment_version_id
,e.attribution_variation_id
,e.variation_name
,e.users
,e.impressions
,e.clicks
,ev.event_name
,a.event_id
,a.unique_converters
,a.event_count
,a.event_value
from engagement e
left join attribution a
  on a.date = e.date
  and a.version_id = e.experiment_version_id
  and a.variation_id = e.attribution_variation_id
left join event_metadata ev
  on ev.event_id = a.event_id
order by e.campaign_id, e.experiment_version_id, a.event_id, e.attribution_variation_id
;