{"queries":[{"id":"company_list_data","compiledQueryString":"SELECT run_name, focus_merchant as merchant_name, count(*)\nFROM consumer_segment_switching_attributes_sql\nGROUP BY run_name, focus_merchant","inputQueryString":"SELECT run_name, focus_merchant as merchant_name, count(*)\nFROM consumer_segment_switching_attributes_sql\nGROUP BY run_name, focus_merchant","compiled":false,"inline":true},{"id":"naics_lookup","compiledQueryString":"SELECT \n merchant_id, \n merchant_name,\n merchant_category_code_description,\n naics_description,\n industry_description\nFROM merchants_mcc_naics_list_sql\nLIMIT 1","inputQueryString":"SELECT \n merchant_id, \n merchant_name,\n merchant_category_code_description,\n naics_description,\n industry_description\nFROM merchants_mcc_naics_list_sql\nLIMIT 1","compiled":false,"inline":true},{"id":"segment_data","compiledQueryString":"SELECT\n merchant_name,\n run_name,\n reference_period_label,\n baseline_period_label,\n segment,\n mcc_code_description,\n cast(mcc_code as int)::string as mcc_code,\n max(total_segment_size) as total_segment_size,\n sum(segment_size) as segment_size,\n sum(n_customers) as n_customers,\n sum(segment_size) / sum(total_segment_size) as pct_segment,\n sum(spend) as spend,\n sum(trips) as trips,\n sum(total_spend) as total_spend,\n sum(total_trips) as total_trips,\n sum(spend) / sum(trips) as aov,\n sum(spend) / sum(total_spend) as share_of_spend,\n sum(trips) / sum(total_trips) as share_of_trips\nFROM consumer_segment_switching_metrics_sql\nWHERE \nfocus_merchant ='IPSUM'\nAND run_name ='IPSUM'\nAND merchant_name = 'IPSUM'\nGROUP BY merchant_name, run_name, reference_period_label, baseline_period_label, segment, mcc_code_description, mcc_code\nORDER BY segment DESC","inputQueryString":"SELECT\n merchant_name,\n run_name,\n reference_period_label,\n baseline_period_label,\n segment,\n mcc_code_description,\n cast(mcc_code as int)::string as mcc_code,\n max(total_segment_size) as total_segment_size,\n sum(segment_size) as segment_size,\n sum(n_customers) as n_customers,\n sum(segment_size) / sum(total_segment_size) as pct_segment,\n sum(spend) as spend,\n sum(trips) as trips,\n sum(total_spend) as total_spend,\n sum(total_trips) as total_trips,\n sum(spend) / sum(trips) as aov,\n sum(spend) / sum(total_spend) as share_of_spend,\n sum(trips) / sum(total_trips) as share_of_trips\nFROM consumer_segment_switching_metrics_sql\nWHERE \nfocus_merchant ='IPSUM'\nAND run_name ='IPSUM'\nAND merchant_name = 'IPSUM'\nGROUP BY merchant_name, run_name, reference_period_label, baseline_period_label, segment, mcc_code_description, mcc_code\nORDER BY segment DESC","compiled":false,"inline":true},{"id":"segment_growth","compiledQueryString":"with churned_segment as (\n SELECT merchant_name, focus_merchant, \n segment_size / total_segment_size as pct_churned\nFROM consumer_segment_switching_metrics_sql \nWHERE\nfocus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n AND merchant_name = 'IPSUM'\n AND segment='CHURNED'\n)\nSELECT\n round((new.segment_size / new.total_segment_size) - churned.pct_churned,3) as growth\nFROM consumer_segment_switching_metrics_sql as new\nINNER JOIN churned_segment AS churned ON new.focus_merchant = churned.focus_merchant AND new.merchant_name = churned.merchant_name\nWHERE\nnew.focus_merchant ='IPSUM'\nAND new.run_name ='IPSUM'\nAND new.merchant_name = 'IPSUM'\nAND new.segment = 'NEW'","inputQueryString":"with churned_segment as (\n SELECT merchant_name, focus_merchant, \n segment_size / total_segment_size as pct_churned\nFROM consumer_segment_switching_metrics_sql \nWHERE\nfocus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n AND merchant_name = 'IPSUM'\n AND segment='CHURNED'\n)\nSELECT\n round((new.segment_size / new.total_segment_size) - churned.pct_churned,3) as growth\nFROM consumer_segment_switching_metrics_sql as new\nINNER JOIN churned_segment AS churned ON new.focus_merchant = churned.focus_merchant AND new.merchant_name = churned.merchant_name\nWHERE\nnew.focus_merchant ='IPSUM'\nAND new.run_name ='IPSUM'\nAND new.merchant_name = 'IPSUM'\nAND new.segment = 'NEW'","compiled":false,"inline":true},{"id":"segment_metrics","compiledQueryString":"SELECT \n*, \nsum(spend) OVER() / sum(total_spend) OVER() AS share_of_spend_total,\nsum(trips) OVER() / sum(total_trips) OVER() AS share_of_trips_total,\nsum(spend) OVER() / sum(trips) OVER() as aov_total \nfrom (SELECT\n merchant_name,\n run_name,\n reference_period_label,\n baseline_period_label,\n segment,\n mcc_code_description,\n cast(mcc_code as int)::string as mcc_code,\n max(total_segment_size) as total_segment_size,\n sum(segment_size) as segment_size,\n sum(n_customers) as n_customers,\n sum(segment_size) / sum(total_segment_size) as pct_segment,\n sum(spend) as spend,\n sum(trips) as trips,\n sum(total_spend) as total_spend,\n sum(total_trips) as total_trips,\n sum(spend) / sum(trips) as aov,\n sum(spend) / sum(total_spend) as share_of_spend,\n sum(trips) / sum(total_trips) as share_of_trips\nFROM consumer_segment_switching_metrics_sql\nWHERE \nfocus_merchant ='IPSUM'\nAND run_name ='IPSUM'\nAND merchant_name = 'IPSUM'\nGROUP BY merchant_name, run_name, reference_period_label, baseline_period_label, segment, mcc_code_description, mcc_code\nORDER BY segment DESC)\norder by segment DESC","inputQueryString":"SELECT \n*, \nsum(spend) OVER() / sum(total_spend) OVER() AS share_of_spend_total,\nsum(trips) OVER() / sum(total_trips) OVER() AS share_of_trips_total,\nsum(spend) OVER() / sum(trips) OVER() as aov_total \nfrom ${segment_data}\norder by segment DESC","compiled":true,"inline":true},{"id":"age_summary_demographics","compiledQueryString":"SELECT\n segment,\n 'Age' AS attribute_name,\n attribute, \n attribute_level,\n CASE \n WHEN attribute_level='18-24' THEN 1\n WHEN attribute_level='25-34' THEN 2\n WHEN attribute_level='35-44' THEN 3\n WHEN attribute_level='45-54' THEN 4\n WHEN attribute_level='55-64' THEN 5\n WHEN attribute_level='65+' THEN 6 \n END AS attribute_level_index,\n pct_customers\nFROM consumer_segment_switching_attributes_sql\nWHERE focus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n AND attribute='AGE'\nORDER BY segment DESC, attribute_level_index","inputQueryString":"SELECT\n segment,\n 'Age' AS attribute_name,\n attribute, \n attribute_level,\n CASE \n WHEN attribute_level='18-24' THEN 1\n WHEN attribute_level='25-34' THEN 2\n WHEN attribute_level='35-44' THEN 3\n WHEN attribute_level='45-54' THEN 4\n WHEN attribute_level='55-64' THEN 5\n WHEN attribute_level='65+' THEN 6 \n END AS attribute_level_index,\n pct_customers\nFROM consumer_segment_switching_attributes_sql\nWHERE focus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n AND attribute='AGE'\nORDER BY segment DESC, attribute_level_index","compiled":false,"inline":true},{"id":"income_summary_demographics","compiledQueryString":"SELECT\n segment,\n 'Income' AS attribute_name,\n attribute, \n attribute_level,\n CASE \n WHEN attribute_level='Less than $20K' THEN 1\n WHEN attribute_level='$21K-40K' THEN 2\n WHEN attribute_level='$41K-60K' THEN 3\n WHEN attribute_level='$61K-80K' THEN 4\n WHEN attribute_level='$81K-100K' THEN 5\n WHEN attribute_level='$101K-150K' THEN 6\n WHEN attribute_level='$151K+' THEN 7 \n END AS attribute_level_index,\n pct_customers\nFROM consumer_segment_switching_attributes_sql\nWHERE focus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n AND attribute='INCOME'\nORDER BY segment DESC, attribute_level_index","inputQueryString":"SELECT\n segment,\n 'Income' AS attribute_name,\n attribute, \n attribute_level,\n CASE \n WHEN attribute_level='Less than $20K' THEN 1\n WHEN attribute_level='$21K-40K' THEN 2\n WHEN attribute_level='$41K-60K' THEN 3\n WHEN attribute_level='$61K-80K' THEN 4\n WHEN attribute_level='$81K-100K' THEN 5\n WHEN attribute_level='$101K-150K' THEN 6\n WHEN attribute_level='$151K+' THEN 7 \n END AS attribute_level_index,\n pct_customers\nFROM consumer_segment_switching_attributes_sql\nWHERE focus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n AND attribute='INCOME'\nORDER BY segment DESC, attribute_level_index","compiled":false,"inline":true},{"id":"switching_to_sankey","compiledQueryString":"WITH rank_order AS (\n SELECT\n focus_merchant,\n segment,\n merchant_name,\n SUM(spend) AS spend,\n SUM(trips) AS trips,\n AVG(aov) AS aov_metric,\n AVG(share_of_spend) AS share_of_spend_metric,\n AVG(share_of_trips) AS share_of_trips_metric,\n row_number() OVER (PARTITION BY segment ORDER BY aov_metric DESC) AS aov_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_spend_metric DESC) AS share_of_spend_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_trips_metric DESC) AS share_of_trips_metric_rank\n FROM consumer_segment_switching_metrics_sql\n WHERE \n focus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n GROUP BY focus_merchant, segment, merchant_name\n)\nSELECT\n csc.focus_merchant,\n csc.period_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN csc.reference_period_label ELSE csc.baseline_period_label END AS quarter_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN 1 ELSE 0 END AS period_sort_index,\n csc.segment,\n csc.merchant_name,\n csc.spend,\n csc.trips,\n csc.aov,\n csc.share_of_spend,\n csc.share_of_trips,\n rnk.aov_metric_rank,\n rnk.share_of_spend_metric_rank,\n rnk.share_of_trips_metric_rank\nFROM consumer_segment_switching_metrics_sql csc\nLEFT JOIN rank_order rnk \nON csc.focus_merchant = rnk.focus_merchant AND csc.merchant_name = rnk.merchant_name AND csc.segment = rnk.segment\nWHERE \n csc.focus_merchant ='IPSUM'\n AND csc.run_name ='IPSUM'\nORDER BY csc.segment DESC","inputQueryString":"WITH rank_order AS (\n SELECT\n focus_merchant,\n segment,\n merchant_name,\n SUM(spend) AS spend,\n SUM(trips) AS trips,\n AVG(aov) AS aov_metric,\n AVG(share_of_spend) AS share_of_spend_metric,\n AVG(share_of_trips) AS share_of_trips_metric,\n row_number() OVER (PARTITION BY segment ORDER BY aov_metric DESC) AS aov_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_spend_metric DESC) AS share_of_spend_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_trips_metric DESC) AS share_of_trips_metric_rank\n FROM consumer_segment_switching_metrics_sql\n WHERE \n focus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n GROUP BY focus_merchant, segment, merchant_name\n)\nSELECT\n csc.focus_merchant,\n csc.period_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN csc.reference_period_label ELSE csc.baseline_period_label END AS quarter_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN 1 ELSE 0 END AS period_sort_index,\n csc.segment,\n csc.merchant_name,\n csc.spend,\n csc.trips,\n csc.aov,\n csc.share_of_spend,\n csc.share_of_trips,\n rnk.aov_metric_rank,\n rnk.share_of_spend_metric_rank,\n rnk.share_of_trips_metric_rank\nFROM consumer_segment_switching_metrics_sql csc\nLEFT JOIN rank_order rnk \nON csc.focus_merchant = rnk.focus_merchant AND csc.merchant_name = rnk.merchant_name AND csc.segment = rnk.segment\nWHERE \n csc.focus_merchant ='IPSUM'\n AND csc.run_name ='IPSUM'\nORDER BY csc.segment DESC","compiled":false,"inline":true},{"id":"switching_share_viz","compiledQueryString":"WITH placeholder_record AS (\n SELECT\n 0 AS metric_sort_index,\n segment,\n quarter_name,\n period_sort_index,\n '${inputs.merchant_name_select.value}' AS merchant_name,\n 0 AS metric\n FROM (WITH rank_order AS (\n SELECT\n focus_merchant,\n segment,\n merchant_name,\n SUM(spend) AS spend,\n SUM(trips) AS trips,\n AVG(aov) AS aov_metric,\n AVG(share_of_spend) AS share_of_spend_metric,\n AVG(share_of_trips) AS share_of_trips_metric,\n row_number() OVER (PARTITION BY segment ORDER BY aov_metric DESC) AS aov_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_spend_metric DESC) AS share_of_spend_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_trips_metric DESC) AS share_of_trips_metric_rank\n FROM consumer_segment_switching_metrics_sql\n WHERE \n focus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n GROUP BY focus_merchant, segment, merchant_name\n)\nSELECT\n csc.focus_merchant,\n csc.period_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN csc.reference_period_label ELSE csc.baseline_period_label END AS quarter_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN 1 ELSE 0 END AS period_sort_index,\n csc.segment,\n csc.merchant_name,\n csc.spend,\n csc.trips,\n csc.aov,\n csc.share_of_spend,\n csc.share_of_trips,\n rnk.aov_metric_rank,\n rnk.share_of_spend_metric_rank,\n rnk.share_of_trips_metric_rank\nFROM consumer_segment_switching_metrics_sql csc\nLEFT JOIN rank_order rnk \nON csc.focus_merchant = rnk.focus_merchant AND csc.merchant_name = rnk.merchant_name AND csc.segment = rnk.segment\nWHERE \n csc.focus_merchant ='IPSUM'\n AND csc.run_name ='IPSUM'\nORDER BY csc.segment DESC) \n WHERE merchant_name <>'${inputs.merchant_name_select.value}'\n AND segment='NEW' AND period_sort_index = 0\n LIMIT 1\n) \n, force_merchant_position AS (\n SELECT\n 0 AS metric_sort_index,\n segment,\n quarter_name,\n period_sort_index,\n merchant_name,\n ${inputs.metric_to_use} AS metric\n FROM (WITH rank_order AS (\n SELECT\n focus_merchant,\n segment,\n merchant_name,\n SUM(spend) AS spend,\n SUM(trips) AS trips,\n AVG(aov) AS aov_metric,\n AVG(share_of_spend) AS share_of_spend_metric,\n AVG(share_of_trips) AS share_of_trips_metric,\n row_number() OVER (PARTITION BY segment ORDER BY aov_metric DESC) AS aov_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_spend_metric DESC) AS share_of_spend_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_trips_metric DESC) AS share_of_trips_metric_rank\n FROM consumer_segment_switching_metrics_sql\n WHERE \n focus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n GROUP BY focus_merchant, segment, merchant_name\n)\nSELECT\n csc.focus_merchant,\n csc.period_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN csc.reference_period_label ELSE csc.baseline_period_label END AS quarter_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN 1 ELSE 0 END AS period_sort_index,\n csc.segment,\n csc.merchant_name,\n csc.spend,\n csc.trips,\n csc.aov,\n csc.share_of_spend,\n csc.share_of_trips,\n rnk.aov_metric_rank,\n rnk.share_of_spend_metric_rank,\n rnk.share_of_trips_metric_rank\nFROM consumer_segment_switching_metrics_sql csc\nLEFT JOIN rank_order rnk \nON csc.focus_merchant = rnk.focus_merchant AND csc.merchant_name = rnk.merchant_name AND csc.segment = rnk.segment\nWHERE \n csc.focus_merchant ='IPSUM'\n AND csc.run_name ='IPSUM'\nORDER BY csc.segment DESC) \n WHERE merchant_name ='${inputs.merchant_name_select.value}'\n)\n, by_merchant AS (\n SELECT\n ${inputs.metric_to_use}_metric_rank AS metric_sort_index,\n segment,\n quarter_name,\n period_sort_index,\n merchant_name,\n ${inputs.metric_to_use} AS metric\n FROM (WITH rank_order AS (\n SELECT\n focus_merchant,\n segment,\n merchant_name,\n SUM(spend) AS spend,\n SUM(trips) AS trips,\n AVG(aov) AS aov_metric,\n AVG(share_of_spend) AS share_of_spend_metric,\n AVG(share_of_trips) AS share_of_trips_metric,\n row_number() OVER (PARTITION BY segment ORDER BY aov_metric DESC) AS aov_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_spend_metric DESC) AS share_of_spend_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_trips_metric DESC) AS share_of_trips_metric_rank\n FROM consumer_segment_switching_metrics_sql\n WHERE \n focus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n GROUP BY focus_merchant, segment, merchant_name\n)\nSELECT\n csc.focus_merchant,\n csc.period_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN csc.reference_period_label ELSE csc.baseline_period_label END AS quarter_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN 1 ELSE 0 END AS period_sort_index,\n csc.segment,\n csc.merchant_name,\n csc.spend,\n csc.trips,\n csc.aov,\n csc.share_of_spend,\n csc.share_of_trips,\n rnk.aov_metric_rank,\n rnk.share_of_spend_metric_rank,\n rnk.share_of_trips_metric_rank\nFROM consumer_segment_switching_metrics_sql csc\nLEFT JOIN rank_order rnk \nON csc.focus_merchant = rnk.focus_merchant AND csc.merchant_name = rnk.merchant_name AND csc.segment = rnk.segment\nWHERE \n csc.focus_merchant ='IPSUM'\n AND csc.run_name ='IPSUM'\nORDER BY csc.segment DESC) \n WHERE ${inputs.metric_to_use} >= 0.01 and ${inputs.metric_to_use}_metric_rank <= 10\n AND merchant_name <> '${inputs.merchant_name_select.value}'\n),\nby_all_other as (\n SELECT\n 9999999 as metric_sort_index,\n segment,\n quarter_name,\n period_sort_index,\n 'ALL OTHER' AS merchant_name,\n sum(${inputs.metric_to_use}) as metric_other,\n sum(spend) / sum(trips) as aov_other\n FROM (WITH rank_order AS (\n SELECT\n focus_merchant,\n segment,\n merchant_name,\n SUM(spend) AS spend,\n SUM(trips) AS trips,\n AVG(aov) AS aov_metric,\n AVG(share_of_spend) AS share_of_spend_metric,\n AVG(share_of_trips) AS share_of_trips_metric,\n row_number() OVER (PARTITION BY segment ORDER BY aov_metric DESC) AS aov_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_spend_metric DESC) AS share_of_spend_metric_rank,\n row_number() OVER (PARTITION BY segment ORDER BY share_of_trips_metric DESC) AS share_of_trips_metric_rank\n FROM consumer_segment_switching_metrics_sql\n WHERE \n focus_merchant ='IPSUM'\n AND run_name ='IPSUM'\n GROUP BY focus_merchant, segment, merchant_name\n)\nSELECT\n csc.focus_merchant,\n csc.period_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN csc.reference_period_label ELSE csc.baseline_period_label END AS quarter_name,\n CASE WHEN csc.period_name ='SEGMENT' THEN 1 ELSE 0 END AS period_sort_index,\n csc.segment,\n csc.merchant_name,\n csc.spend,\n csc.trips,\n csc.aov,\n csc.share_of_spend,\n csc.share_of_trips,\n rnk.aov_metric_rank,\n rnk.share_of_spend_metric_rank,\n rnk.share_of_trips_metric_rank\nFROM consumer_segment_switching_metrics_sql csc\nLEFT JOIN rank_order rnk \nON csc.focus_merchant = rnk.focus_merchant AND csc.merchant_name = rnk.merchant_name AND csc.segment = rnk.segment\nWHERE \n csc.focus_merchant ='IPSUM'\n AND csc.run_name ='IPSUM'\nORDER BY csc.segment DESC)\n WHERE (${inputs.metric_to_use} < 0.01 OR ${inputs.metric_to_use}_metric_rank > 10)\n AND merchant_name <>'${inputs.merchant_name_select.value}'\n group by period_sort_index, quarter_name, segment\n)\nSELECT * FROM placeholder_record WHERE segment='${inputs.segment_to_use}'\nUNION ALL \nSELECT * FROM force_merchant_position WHERE segment='${inputs.segment_to_use}'\nUNION ALL\nSELECT * FROM by_merchant WHERE segment='${inputs.segment_to_use}'\nUNION ALL\nSELECT metric_sort_index, segment, quarter_name, period_sort_index, merchant_name,\nCASE WHEN '${inputs.metric_to_use}'='aov' THEN aov_other ELSE metric_other END AS metric\nFROM by_all_other WHERE segment='${inputs.segment_to_use}'\nORDER BY segment, metric_sort_index, period_sort_index","inputQueryString":"WITH placeholder_record AS (\n SELECT\n 0 AS metric_sort_index,\n segment,\n quarter_name,\n period_sort_index,\n '${inputs.merchant_name_select.value}' AS merchant_name,\n 0 AS metric\n FROM ${switching_to_sankey} \n WHERE merchant_name <>'${inputs.merchant_name_select.value}'\n AND segment='NEW' AND period_sort_index = 0\n LIMIT 1\n) \n, force_merchant_position AS (\n SELECT\n 0 AS metric_sort_index,\n segment,\n quarter_name,\n period_sort_index,\n merchant_name,\n ${inputs.metric_to_use} AS metric\n FROM ${switching_to_sankey} \n WHERE merchant_name ='${inputs.merchant_name_select.value}'\n)\n, by_merchant AS (\n SELECT\n ${inputs.metric_to_use}_metric_rank AS metric_sort_index,\n segment,\n quarter_name,\n period_sort_index,\n merchant_name,\n ${inputs.metric_to_use} AS metric\n FROM ${switching_to_sankey} \n WHERE ${inputs.metric_to_use} >= 0.01 and ${inputs.metric_to_use}_metric_rank <= 10\n AND merchant_name <> '${inputs.merchant_name_select.value}'\n),\nby_all_other as (\n SELECT\n 9999999 as metric_sort_index,\n segment,\n quarter_name,\n period_sort_index,\n 'ALL OTHER' AS merchant_name,\n sum(${inputs.metric_to_use}) as metric_other,\n sum(spend) / sum(trips) as aov_other\n FROM ${switching_to_sankey}\n WHERE (${inputs.metric_to_use} < 0.01 OR ${inputs.metric_to_use}_metric_rank > 10)\n AND merchant_name <>'${inputs.merchant_name_select.value}'\n group by period_sort_index, quarter_name, segment\n)\nSELECT * FROM placeholder_record WHERE segment='${inputs.segment_to_use}'\nUNION ALL \nSELECT * FROM force_merchant_position WHERE segment='${inputs.segment_to_use}'\nUNION ALL\nSELECT * FROM by_merchant WHERE segment='${inputs.segment_to_use}'\nUNION ALL\nSELECT metric_sort_index, segment, quarter_name, period_sort_index, merchant_name,\nCASE WHEN '${inputs.metric_to_use}'='aov' THEN aov_other ELSE metric_other END AS metric\nFROM by_all_other WHERE segment='${inputs.segment_to_use}'\nORDER BY segment, metric_sort_index, period_sort_index","compiled":true,"inline":true},{"id":"switching_metrics_data","compiledQueryString":"SELECT\n ref.segment,\n ref.merchant_name,\n ref.share_of_spend,\n ref.${inputs.metric_to_use} as metric_post,\n base.${inputs.metric_to_use} as metric_pre,\n ref.${inputs.metric_to_use} - COALESCE(base.${inputs.metric_to_use}, 0) as pt_delta,\n ref.${inputs.metric_to_use} / base.${inputs.metric_to_use} - 1 as pct_delta\nFROM consumer_segment_switching_metrics_sql AS ref\nLEFT JOIN consumer_segment_switching_metrics_sql as base \nON ref.segment = base.segment AND ref.merchant_name = base.merchant_name\nAND ref.run_name = base.run_name\nAND ref.focus_merchant = base.focus_merchant\nWHERE \nref.focus_merchant ='IPSUM'\nAND ref.run_name ='IPSUM'\nAND base.focus_merchant ='IPSUM'\nAND ref.period_name = 'SEGMENT' AND base.period_name = 'BASE'","inputQueryString":"SELECT\n ref.segment,\n ref.merchant_name,\n ref.share_of_spend,\n ref.${inputs.metric_to_use} as metric_post,\n base.${inputs.metric_to_use} as metric_pre,\n ref.${inputs.metric_to_use} - COALESCE(base.${inputs.metric_to_use}, 0) as pt_delta,\n ref.${inputs.metric_to_use} / base.${inputs.metric_to_use} - 1 as pct_delta\nFROM consumer_segment_switching_metrics_sql AS ref\nLEFT JOIN consumer_segment_switching_metrics_sql as base \nON ref.segment = base.segment AND ref.merchant_name = base.merchant_name\nAND ref.run_name = base.run_name\nAND ref.focus_merchant = base.focus_merchant\nWHERE \nref.focus_merchant ='IPSUM'\nAND ref.run_name ='IPSUM'\nAND base.focus_merchant ='IPSUM'\nAND ref.period_name = 'SEGMENT' AND base.period_name = 'BASE'","compiled":false,"inline":true},{"id":"switching_metrics_viz","compiledQueryString":"SELECT\n segment, \n merchant_name,\n metric_pre,\n metric_post,\n CASE WHEN '${inputs.metric_to_use}' = 'aov' THEN pct_delta ELSE pt_delta END AS delta\n FROM (SELECT\n ref.segment,\n ref.merchant_name,\n ref.share_of_spend,\n ref.${inputs.metric_to_use} as metric_post,\n base.${inputs.metric_to_use} as metric_pre,\n ref.${inputs.metric_to_use} - COALESCE(base.${inputs.metric_to_use}, 0) as pt_delta,\n ref.${inputs.metric_to_use} / base.${inputs.metric_to_use} - 1 as pct_delta\nFROM consumer_segment_switching_metrics_sql AS ref\nLEFT JOIN consumer_segment_switching_metrics_sql as base \nON ref.segment = base.segment AND ref.merchant_name = base.merchant_name\nAND ref.run_name = base.run_name\nAND ref.focus_merchant = base.focus_merchant\nWHERE \nref.focus_merchant ='IPSUM'\nAND ref.run_name ='IPSUM'\nAND base.focus_merchant ='IPSUM'\nAND ref.period_name = 'SEGMENT' AND base.period_name = 'BASE')\n WHERE segment = '${inputs.segment_to_use}'\n -- Hack to make viz behind blur paywall look good because it's fake data\n QUALIFY ROW_NUMBER(*) OVER (PARTITION BY merchant_name ORDER BY merchant_name) = 1\n ORDER BY metric_post DESC","inputQueryString":"SELECT\n segment, \n merchant_name,\n metric_pre,\n metric_post,\n CASE WHEN '${inputs.metric_to_use}' = 'aov' THEN pct_delta ELSE pt_delta END AS delta\n FROM ${switching_metrics_data}\n WHERE segment = '${inputs.segment_to_use}'\n -- Hack to make viz behind blur paywall look good because it's fake data\n QUALIFY ROW_NUMBER(*) OVER (PARTITION BY merchant_name ORDER BY merchant_name) = 1\n ORDER BY metric_post DESC","compiled":true,"inline":true}]}

Switching Analysis

Module for enterprise clients.

Overview

Switching Trends