mattm

{% macro source(table_name) %}

	{% if target.name == 'prod' %}
	  {{ return("preceden_heroku." ~ table_name) }}
	{% else %}
	  {{ return(table_name) }}
	{% endif %}

{% endmacro %}

mattm

select
	id,
	email
from {{ var('base.users') }}

mattm

vars:
  "base.users" : "users"

mattm

AANDG
AAS
ABILENE
ABRASIVE
ACCOMPLICE
ACENTO
ACTIONSPORTS
ADAGE
ADAIR
ADAY

mattm

; The use of the Data contained in Verisign Inc.'s aggregated
; .com, and .net top-level domain zone files (including the checksum
; files) is subject to the restrictions described in the access Agreement
; with Verisign Inc.

$ORIGIN COM.
$TTL 900
@ IN    SOA     a.gtld-servers.net. nstld.verisign-grs.com. (
				  1526140941 ;serial
				  1800 ;refresh every 30 min

mattm

; The use of the Data contained in Verisign Inc.'s aggregated
; .com, and .net top-level domain zone files (including the checksum
; files) is subject to the restrictions described in the access Agreement
; with Verisign Inc.

$ORIGIN COM.
$TTL 900
@ IN    SOA     a.gtld-servers.net. nstld.verisign-grs.com. (
				  1526140941 ;serial
				  1800 ;refresh every 30 min

mattm

SELECT 
  current_url,
  CASE
    WHEN STRPOS(current_url, "?") > 0 THEN SUBSTR(current_url, 0, STRPOS(current_url, "?") - 1)
    WHEN STRPOS(current_url, "#") > 0 THEN SUBSTR(current_url, 0, STRPOS(current_url, "#") - 1)
    ELSE current_url
  END AS url
FROM (
  SELECT 'https://www.helpscout.net/blog/beacon-preview-ui/' AS current_url
  UNION ALL SELECT 'https://www.helpscout.net/blog/beacon-preview-ui/?utm_content=1234&utm_medium=social&utm_source=twitter' AS current_url

mattm

SELECT
  current_url,
  COUNT(*) AS page_views
FROM mp.event
GROUP BY 1

mattm

SELECT
  current_url,
  MIN(TIMESTAMP_TRUNC(time, DAY)) AS published_at
FROM mp.event
WHERE
  current_url like "https://www.helpscout.net/blog/%"
GROUP BY 1
HAVING
  COUNT(*) > 200 AND
  MIN(time) >= "2018-04-04"

mattm

view: mp_events {
  sql_table_name: mp.event ;;
  
  # ...

  measure: unique_visitors {
    type: count_distinct
    sql: ${distinct_id} ;;
  }
}