Question

Combining multiple transactions into a single event table

  • 12 February 2015
  • 2 replies
  • 180 views

Userlevel 5
Badge

Event tables are useful for deep analysis on user retention and for identifying how to improve the product or service. Not all datasets have raw event tables, but there are plenty of other transactional data tables we can utilize to generate an event table. With this event table in hand, we can then answer questions like:



  • How long does a user spend on site?

  • What is the conversion funnel between all the steps of all of our defined workflows?

  • What effects do attributes about the experience, such as click source or quoted prices, have on the retention or purchase frequency?



###Pre-transformation




###Post-tranformation


###Derived Table for Transformation


- view: trial_funnel
derived_table:
sql: |
SELECT
a.click_id AS click_id
, 'click' AS event
, a.username AS username
, a.created_at AS created_at
FROM click a
UNION ALL
SELECT
b.purchase_id AS purchase_id
, 'purchase' AS event
, b.username AS username
, b.created_at AS created_at
FROM purchase b
UNION ALL
SELECT
c.quote_id AS quote_id
, 'quote' AS event
, c.username AS username
, c.created_at AS created_at
FROM quote c

fields:

- dimension: click_id
type: int
sql: ${TABLE}.click_id

- dimension: purchase_id
type: int
sql: ${TABLE}.purchase_id

- dimension: quote_id
type: int
sql: ${TABLE}.quote_id

- dimension: event

- dimension: username

- dimension_group: created
type: time
timeframes: [time, date, dow, dow_num, week, month_num, month, year]
sql: ${TABLE}.created_at

2 replies

This is awesome thank you! But quick question, where/how do you create the seperate event_id in this query?

Userlevel 6
Badge

event_id isn’t in the query, but here is how you might do it. A ROW_NUMBER() window function will number each row in most dialects of SQL.


   SELECT
ROW_NUMBER() OVER () as event_id
, *
FROM (
SELECT
a.click_id AS click_id
, 'click' AS event
, a.username AS username
, a.created_at AS created_at
FROM click a
UNION ALL
SELECT
b.purchase_id AS purchase_id
, 'purchase' AS event
, b.username AS username
, b.created_at AS created_at
FROM purchase b
UNION ALL
SELECT
c.quote_id AS quote_id
, 'quote' AS event
, c.username AS username
, c.created_at AS created_at
FROM quote c)

If you are looking to generate a primary key, you could CONCAT(<event_type>,event_id) and that would also give you a unique event_id that would work as a primary key.

Reply