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:

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.

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. A message will appear, informing you that "The first activity stream export will run tonight."
3. To generate your credentials for the S3 bucket, click the additional options iconand then click Manage S3 Bucket.

4. Copy the S3 bucket path and credentials to a secure location. Note that the Secret Key is only displayed once.

   

5. Lost your credentials?
   Click the additional options iconand then click Manage S3 Bucket > Create Access Key. If you already have two access keys, deactivate one and then delete it. Subsequently, the option to generate a new access key will once again become available. Learn more about managing S3 credentials in Experience OS.
   
6. 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, the page also displays the status of the export and when it was last exported.

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

To parse the raw dataset, use the following query. Make sure to change the table name in the FROM clause to the table where you have copied the raw data.

-- parse parquet files i​n raw folder
create o​r replace table parsed_raw_data_daily_activity_stream
a​s
select
$1:eventType::string a​s event_type 
,$1:timestamp::int a​s timestamp 
,$1:reqTimestamp::int a​s req_timestamp
,$1:procTimestamp::int a​s proc_timestamp 
,$1:sectionDate::date as section_date
,$1:dyid::int a​s dy_id
,$1:internalId::int as internal_id
,$1:globalTestGroup::string as global_test_group
,$1:sessionId::int a​s session_id
,$1:audiences::variant a​s audiences 
,$1:audiencesNames::variant a​s audiences_names 
,$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: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:operatingSystem::string a​s operating_system
,$1:productIds::variant a​s product_ids
,$1:screenResolution::string a​s screen_resolution 
,$1:trafficSource::string as traffic_source
,$1:referringDomain::string as referring_domain
,$1:url::string a​s url
,$1:urlClean::string a​s url_clean
,$1:userAgent::string a​s user_agent
,$1:campaignId::int a​s campaign_id
,$1:campaignName::string a​s campaign_name
,$1:parentCampaignId::int as parent_campaign_id // relevant for multi-touch campaign type
,$1:parentCampaignName::string as parent_campaign_name // relevant for multi-touch campaign type
,$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:reportVersionId as report_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
,$1 a​s json_object
from daily_activity_stream_raw
;

Explore the imported raw data set

To explore the parsed raw data, use the following queries.

-- preview first 100 records of raw data, ordered by timestamp
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 event_id, event_name
;

-- 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

To parse the attribution dataset, use the following query. Make sure to change the table name in the FROM clause to the table where you have copied the raw data.

-- parse parquet files i​n attribution folder
create o​r replace table parsed_attribution_daily_activity_stream
a​s
select
$1:dyid::int a​s dy_id
,$1:sectionDate::date as section_date
,$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
,$1:reportVersionId::int as report_version_id
,file_name
from daily_activity_stream_attribution
;

Explore the imported attribution data set

To explore the parsed attribution data, use the following queries.

-- preview first 100 records of attribution
select * from parsed_attribution_daily_activity_stream
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_stream
where version_id = {value_version_id}
group by variation_id, event_id
;

Join the Raw and Attribution data sets together

The following queries include the steps to join the data sets. 

To create a connection between the tables based on date, utilize the timestamp field after converting it to a date format.

-- aggregate raw by variation
select
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 campaign_id, campaign_name, experience_name, experiment_version_id, attribution_variation_id, variation_name, strategy_id, strategy_name
;

-- aggregate attribution by variation
select 
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_stream
group by version_id, variation_id, event_id
; 

-- put it together - this query already include the aggregations as CTEs
with engagement a​s
(
  select 
  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 campaign_id, campaign_name, experience_name, experiment_version_id, attribution_variation_id
)

,attribution a​s
(
  select 
  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_stream
  group by 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.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.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
;

Sample raw data file