githoov

- view: outlier
  derived_table:
    sql: |
      SELECT (PERCENTILE_DISC(0.75) WITHIN GROUP(ORDER BY days_between_posts) OVER() 
                + (PERCENTILE_DISC(0.75) WITHIN GROUP(ORDER BY days_between_posts) OVER() 
                - PERCENTILE_DISC(0.25) WITHIN GROUP(ORDER BY days_between_posts) OVER()) * 1.5)::INT AS cutoff
      FROM ${consumer_posts.SQL_TABLE_NAME} AS consumer_posts
      LIMIT 1
    sql_trigger_value: SELECT CURRENT_DATE
    sortkeys: [cutoff]

githoov

- base_view: cohorts
  always_filter:
    event_date: 'this week'
    type: activity, user

- view: cohorts
  derived_table:
    sql: |
      SELECT 'activity' AS type
        , user_id

githoov

Quite often it's beneficial to place a restriction on an SQL query while proptyping/testing a transformation. To make this a bit easier, one has the ability to place an in-line comment within a derived table to place a restriction while in developer mode, and, once the derived table is pushed, have it not apply or simply differ in some way.

In the following example, I would like to do a funnel analysis, but limit this transformation to the past 30 days while in developer mode. No resitriction exists once pushed to production.

- view: funnel
  derived_table:
    sql: |
      SELECT user_id
        , COUNT(*) AS lifetime_events

githoov

- view: day_sequence
  derived_table:
    sql: |
      SELECT DATE(DATE_ADD('2010-01-01', INTERVAL @i := @i + 1 day)) AS series
      FROM orders, (SELECT @i := 0) AS i_table
      WHERE @i < DATEDIFF(CURDATE(), '2010-01-01') + 1
    sql_trigger_value: SELECT CURDATE()
    indexes: [series]
  fields:

githoov

githoov

githoov

---

#Custom Visualization Framework

##The Basics

githoov

# PRELIMINARIES #

- connection: meta
- scoping: true           # for backward compatibility
- include: "dwh.mrr.view.lookml"
- include: "dwh.mrr_planned.view.lookml"
- include: "dwh.account.view.lookml"
- include: "dwh.account_facts.view.lookml"
- include: "dwh.opportunity.view.lookml"
- include: "dwh.opportunity_actualized.view.lookml"

githoov

aws:
  access_key_id: my_id
  secret_access_key: my_key
  s3:
    region: us-east-1
    buckets:
      assets: s3://snowplow-hosted-assets # DO NOT CHANGE unless you are hosting the jarfiles etc yourself in your own bucket
      jsonpath_assets: s3://snowplow-looker/jsonpaths
      log: s3n://snowplow-looker-emr-log/
      raw:

githoov

# preliminaires
library("ggplot2")
library("zoo")
set.seed(111)

# generate plot of survival curve
x <- sort(dexp(seq(0, 1, 0.01)), decreasing = TRUE)
ggplot(data.frame(x = c(0, 5)), aes(x)) + stat_function(fun = dexp, args = list(rate = 1)) + scale_x_continuous(labels=c(expression(t["0"], t["1"], t["2"], t["3"], t["4"], t["5"]))) + labs(x = "Time", y = expression(y = P(T > t["i"])), title = "Survival Function")

# simulate subscription data