This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import requests, json, math, datetime | |
# global variables | |
api_base_url = 'https://fivetran.namely.com/api/v1' | |
response_per_page = 20 | |
def handler(request): | |
# get the credentials needed securely from Fivetran function // no storing credentials in code | |
request_json = request.get_json(silent=True) | |
bearer_token = request_json['secrets']['bearer_token'] |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create or replace table tmp_sla_analysis.combined_arr_3_mo as | |
WITH revenue AS (with churn_flag_components as ( | |
select | |
* except(days_delta), | |
-- if we have new_business, then it is one new customer, if we gave a reactivation event, then it's a new customer | |
if(new_business_legacy <> 0, 1, 0) as new_cust_count, | |
-- a customer is considered lost it it does not comeback in the next 180 days | |
if((churn_legacy <> 0 and arr_legacy = 0 and (lead(days_delta) over account_to_this_day > 180)) | |
or lead(days_delta) over account_to_this_day is null, 1, 0) as lost_cust_count | |
from `digital-arbor-400.looker_pdts.LR_CQM6Z1597852267935_z_dt_churn_logic` |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- create or replace table scratch.credit_consumption as | |
with identify_credit_type_to_use as ( | |
select | |
*, | |
case | |
-- when count of trials > count of non-cbp customer records then this is a new cbp customer and we should use credits instead of assumed_credits | |
when (count(case when status like 'Trial%' then 1 else null end) over account_month) >= count(case when status = 'Customer' and coalesce(platform_tier, '') not like 'Usage%' then 1 else null end) over account_month | |
and count(case when status = 'Customer' and coalesce(platform_tier, '') like 'Usage%' then 1 else null end) over account_month > 0 | |
then true | |
else false |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--create or replace table scratch.credit_consumption as | |
with pct_time_customer as ( | |
select | |
account_id, | |
date_trunc(timeline_date, month) as month, | |
round( | |
sum( | |
case when status = 'Customer' then 1 else 0 end | |
/ date_diff(date_add(date_trunc(timeline_date, month), interval 1 month), date_trunc(timeline_date, month), day) | |
), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with inputs as ( | |
select | |
account_id as fivetran_account_id, | |
month, | |
connector as excess_service, | |
excess_mar as excess_total_mar | |
from scratch.credit_giveback_qa_input | |
), pull_credit_and_mar_data as ( | |
select | |
mar.account_id, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with interview_events as ( | |
select | |
scheduled_interview.id as sch_interview_id, | |
scheduled_interview.application_id, | |
date(scheduled_interview.start) as activity_date, | |
interview.name as interview_name, | |
case | |
when lower(job_stage.name) = 'initial screen' then 'Initial Screen' | |
when lower(job_stage.name) = 'second screen' then 'Second Screen' | |
when lower(job_stage.name) like '%screen%' then 'Other Screen' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- find all jobs with prod target name by dbt | |
with jobs_with_dbt_prod_target as ( | |
select distinct | |
job_id | |
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT | |
where query like '%"target_name": "prod"%' | |
--find all tables/views that were created (or refreshed) in the last 5 days | |
), recent_tables_in_prod_by_dbt_transform_layer as ( | |
select distinct | |
concat(destination_table.project_id, '.', destination_table.dataset_id, '.', destination_table.table_id) as pkey, |