markrittman

```SELECT
   date_trunc(date(creation_time),MONTH) as billing_month,
  count(distinct job_id) as query_runs,
  sum(total_bytes_billed) as total_bytes_billed,
  SUM(total_bytes_billed)/1e12*5 costInDollars
FROM
  `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  date_trunc(date(creation_time),MONTH) = '2021-04-01'
  AND job_type = "QUERY"

markrittman

SELECT
   date_trunc(date(creation_time),MONTH) as billing_month,
   user_email,
  query,
  count(distinct job_id) as query_runs,
  sum(total_bytes_billed) as total_bytes_billed,
  SUM(total_bytes_billed)/1e12*5 costInDollars
FROM
  `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE

markrittman

<script type="text/javascript">
  (function(){
    // Create a queue, but don't obliterate an existing one!
    var analytics = window.analytics = window.analytics || [];
    // If the real analytics.js is already on the page return.
    if (analytics.initialize) return;
    // If the snippet was invoked already show an error.
    if (analytics.invoked) {
      if (window.console && console.error) {
        console.error('Segment snippet included twice.');

markrittman

WITH
  events AS (
  SELECT
    events_true_tstamp_time as event_ts,
    events_user_fingerprint AS user_pseudo_id,
    events_user_id as user_id,
    mkt_network as channel,	
    events_mkt_medium as medium,	
    events_mkt_source as source,	
    events_mkt_campaign as campaign,

markrittman

SELECT
  event_timestamp,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    event_name = 'page_view'
    AND key = 'page_location') AS page_path,

markrittman

SELECT
  event_name,
  event_params.key AS event_params_key
FROM
  `ra-development.analytics_277223877.events_*` AS t
  CROSS JOIN UNNEST (t.event_params) AS event_params
  group by 1,2
  order by 1,2

markrittman

with
  query_cost as (
  select
    date_trunc(date(creation_time),month) as billing_month,
    user_email,
    query,
    job_type,
    project_id,
    priority,
    concat(destination_table.dataset_id,'.',destination_table.table_id) as destination_table,

markrittman

use role accountadmin;

create or replace role loader;
create or replace role transformer;
create or replace role reporter;
create or replace role looker_role;
grant role loader to role sysadmin;
grant role transformer to role sysadmin;
grant role reporter to role sysadmin;
grant role looker_role to role sysadmin;

markrittman

with
  meeting_bookings as # meetings have names and email addresses, but no anonymous_id
  (
  select
    # union of three events that are logically the same but were recorded as three different events historically
    id,
    timestamp,
    email as anonymous_id,
    cast(null as string) as context_ip,
    cast(null as string) as context_page_path,

markrittman

with events as (
    select 
      anonymous_id,
      user_id,
      cast(null as string) as name,
      cast(null as string) as email,
      timestamp,
      'page_view' as event_type,
      concat(split(context_ip,'.')[safe_offset(0)],'.***.***.',split(context_ip,'.')[safe_offset(3)]) as context_ip,
      context_page_path,