Combining multiple transactions into a single event table

  • 12 February 2015
  • 2 replies
  • 314 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