quicksight-notes.md

Quicksight Notes

Pipeline query

SELECT user_id, MAX(count) AS progress, MAX(created) AS "timestamp"
FROM live_analytics
WHERE type = 'ext_progress'
GROUP BY user_id

Timezone

formatDate({timestamp}, 'MM/dd/yyyy HH:mm', 'US/Eastern')

Histogram bucketing

Add a calculated field based on elapsed to bucket the data

ifelse(
    {elapsed} < 60, "A - 0s-60s",
    {elapsed} < 300, "B - 60s-5m",
    {elapsed} < 600, "C - 5m-10m",
    {elapsed} < 3600, "D - 10m-1h",
    "E - +1hr"
)

Progress Labels

ifelse(
    {progress} = 0, "1) Sent Link",
    {progress} = 1, "2) Link Clicked",
    {progress} = 2, "3) Code Entered Incorrectly",
    {progress} = 3, "4) Code Entered",
    "5) Replied"
)

Tips

For athena queries in QS - it needs a column name

Can i suggest labeling them a little differently? I know my labels are slightly less accurate.

ifelse(
{elapsed} < 60, "0-1m",
{elapsed} < 300, "1-5m",
{elapsed} < 600, "5-10m",
{elapsed} < 3600, "10m-1h",
"1hr+"
)