yalisassoon

-- For every event, identify the previous event timestamp and session index

SELECT
  dvce_tstamp AS current_tstamp,
  LAG(dvce_tstamp, 1)
  	OVER (PARTITION BY domain_userid ORDER BY dvce_tstamp) AS previous_tstamp,
  domain_sessionidx AS current_session_index,
  LAG(domain_sessionidx, 1)
  	OVER (PARTITION BY domain_userid ORDER BY dvce_tstamp) AS previous_session_index
FROM atomic.events;

yalisassoon

-- The following query orders events by `domain_userid`, `domain_sessionidx` and `dvce_tstamp`, then checks the difference 
-- (in minutes) between corresponding timestamps. If these are greater than 30 minutes for events in the same session
-- (i.e. `domain_userid` is the same on both rows and `domain_sessionidx` is the same on both rows) then there must be a problem
-- in the Javascript that determines when to increment the `domain_sessionidx` value.

SELECT 
  domain_userid, 
  LAG(domain_userid) OVER (ORDER BY domain_userid, domain_sessionidx, dvce_tstamp) AS previous_duserid,
  domain_sessionidx AS current_dsessionidx, 
  LAG(domain_sessionidx) OVER (ORDER BY domain_userid, domain_sessionidx, dvce_tstamp) AS previous_dsessionidx,

yalisassoon

CREATE EXTERNAL TABLE cloudfront_access_logs (
  dt STRING,
  tm STRING,
  x_edge_location STRING,
  sc_bytes STRING,
  c_ip STRING,
  cs_method STRING,
  cs_host STRING,
  cs_uri_stem STRING,
  sc_status STRING,

yalisassoon

Custom event functionality: initial specification

1. Defining custom events

Snowplow users specifies their own custom event dictionary as a YAML. This is a list of all the different event types in their own custom dictionary:

- event_type: product_view
- event_type: add_to_basket
- event_type: submit_transaction

yalisassoon

// Alias. Worth keeping in because it's so verbose. 
var encodeWrapper = window.encodeURIComponent;

// This is the crust ol' code for building a request.
// Two things:
// 1. You won't be able to populate all of these
// 2. You will need to come up with some custom values - e.g. for tv (tracker version), maybe no-js-0.1.0
// 3. Rather than this crusty code, you can build the whole thing up using requestStringBuilder, see below for code and example
request += 

yalisassoon

itemViewTrack

Details

Reference: snowplow/snowplow#113
Idea from: https://github.com/kingo55

Summary

This is a new feature to track views of products on ecommerce sites, or suggested articles on a media site, similar to how Google AdWords tracks ad views in different positions on a page.