3e:null 3f:["$","title",null,{"children":"Transactions by acquirer_resp_msg | Homam Hosseini | Observable"}] 45:Ta38,{ const div = DOM.element("div") const trace1 = { x: dataset.map(d => d._d_date), y: dataset.map(d => d.total), name: 'Total Transactions', type: 'bar', mode: 'lines+markers', yaxis: 'y1', line: { color: 'blue' }, marker: { opacity: 0.5 }, } const trace2 = { x: dataset.map(d => d._d_date), y: dataset.map(d => d.delivered_transactions), name: 'Delivered Transactions', type: 'scatter', mode: 'lines+markers', yaxis: 'y2', line: { color: 'green' } } const trace3 = { x: dataset.map(d => d._d_date), y: dataset.map(d => d.delivered_ratio), name: 'Delivered Ratio', type: 'scatter', mode: 'lines+markers', xaxis: 'x2', yaxis: 'y3', line: { color: 'red' }, hovertemplate: '%{y:.1%}Delivered Ratio' } const trace4 = { x: dataset.map(d => d._d_date), y: mkTrendLine(dataset.map(d => d.delivered_ratio)), type: 'scatter', mode: 'lines', line: { color: 'orange', dash: 'dot' }, yaxis: 'y3', xaxis: 'x2', name: 'Trend' } const layout = { title: "" //`${new Map([...analyzing].map(x => x.reverse())).get(selectedAnalyzing)}: ${selectedGateway} / ${selectedCountry} / ${selectedPaymentType}` , width: Math.min(width, 1280), height: 600, grid: { rows: 2, columns: 1, subplots: [['xy', 'xy2']] }, yaxis: { title: { text: 'Total Transactions', font: { color: 'blue' } }, tickfont: { color: 'blue' }, showgrid: true, gridcolor: '#ccc', rangemode: 'tozero', zeroline: true, zerolinecolor: '#888', domain: [0.3, 1], }, yaxis2: { title: { text: 'Delivered Transactions', font: { color: 'green' } }, tickfont: { color: 'green' }, overlaying: 'y', side: 'right', showgrid: false, rangemode: 'tozero', zeroline: true, zerolinecolor: '#888' }, xaxis2: { anchor: 'y3' }, yaxis3: { title: { text: 'Delivery Rate', font: { color: 'red' } }, tickfont: { color: 'red' }, domain: [0, 0.25], anchor: 'x2', rangemode: 'tozero', domain: [0, 0.25], tickformat: '.0%', }, legend: { x: 0.01, y: 0.99, borderwidth: 1 }, margin: { l: 50, // left margin r: 50, // right margin b: 40, // << Increase bottom margin significantly to make space for the legend t: 30, // top margin pad: 4 // padding } } Plotly.newPlot(div,[trace1, trace2, trace3, trace4], layout); return div; }46:T827,{ const div = DOM.element("div") const traces = statusDs.map(({key, parent_key, timeline}) => ({ x: dataset.map(d => d._d_date), y: timeline.map(d => d.total), name: key, type: 'scatter', mode: 'lines+markers', yaxis: 'y1', stackgroup: "one", groupnorm: selectedGroupnorm === 'default' ? '' : selectedGroupnorm, line: { color: colors(parent_key, key) }, hovertemplate: '%{fullData.name}
' + // Display Trace Name (selectedGroupnorm === 'default' ? '%{y}' : '%{y:.2f}%') + // Display normalized Y value, formatted to 2 decimals, add '%' '', // Hide the secondary box/trace info })); const layout = { title: ``, width: Math.min(width, 1280), height: 500, // grid: { rows: 2, columns: 1, subplots: [['xy', 'xy2']] }, yaxis: { title: { text: selectedGroupnorm === 'default' ? 'Count' : 'Percent', }, showgrid: true, gridcolor: '#ccc', rangemode: 'tozero', zeroline: true, zerolinecolor: '#888', ticksuffix: selectedGroupnorm === 'default' ? '' : '%', // domain: [0.3, 1], }, legend: { x: 0.5, // Center the legend horizontally (0=left, 0.5=center, 1=right) y: -0.15, // Position anchor point below the plot area (0 = bottom edge). Adjust value as needed. xanchor: 'center',// Anchor the legend's horizontal center to the 'x' coordinate yanchor: 'top', // Anchor the legend's top edge to the 'y' coordinate (so it sits *below* y) orientation: 'h', // Display legend items horizontally borderwidth: 1 // Optional styling }, // IMPORTANT: Adjust margins so the legend doesn't overlap axis labels or get cut off margin: { l: 50, // left margin r: 50, // right margin b: 120, // << Increase bottom margin significantly to make space for the legend t: 0, // top margin pad: 4 // padding } } Plotly.newPlot(div,traces, layout); return div; }47:T4a6,colors = { const groups = d3.group(statusDs, d => d.parent_key) const parentKeys = Array.from(groups.keys()) const parentScale = d3 .scaleOrdinal() .domain(parentKeys) .range(d3.schemeCategory10) const childScales = new Map() groups.forEach((entries, parent) => { const childKeys = entries.map(e => e.key) const baseColor = d3.color(parentScale(parent)) const hslBase = d3.hsl(baseColor) const delta = 0.05 const minL = Math.max(0, hslBase.l - delta) const maxL = Math.min(1, hslBase.l + delta) const childRange = childKeys.length === 1 ? [hslBase.formatHex()] : childKeys.map((_, i) => { const t = i / (childKeys.length - 1) const shade = d3.hsl( hslBase.h, hslBase.s, minL + t * (maxL - minL) ) return shade.formatHex() }) const childScale = d3 .scaleOrdinal() .domain(childKeys) .range(childRange) childScales.set(parent, childScale) }) return (parent, key) => { const childScale = childScales.get(parent) return childScale ? childScale(key) : parentScale(parent) } }48:T963,with billings_status_by_key as ( with a as ( from billings_where select _d_status_text as parent_key , _d_status_text_acquirer_resp_msg as key , sum(total) :: float as total , sum(sum(total)) over (partition by parent_key) as parent_total , sum(total) :: float / sum(sum(total)) over () as ratio group by 1, 2 order by parent_total desc, total desc ) select * from a where ratio > 0.01 or '200%' like _d_status_text_acquirer_resp_msg ) , billings_status_timeline_1 as ( from billings_where bw left join billings_status_by_key bsk on bsk.parent_key = bw._d_status_text and bsk.key = bw._d_status_text_acquirer_resp_msg select _d_date , coalesce(bsk.parent_key, 'OTHERS') as parent_key , coalesce(bsk.key, 'OTHERS') as key , sum(bw.total) as total , sum(delivered_transactions) as delivered_transactions , sum(delivered_transactions) :: float / nullif(sum(bw.total), 0) as delivered_ratio , sum(sum(bw.total)) over (partition by coalesce(bsk.parent_key, 'OTHERS')) as parent_overall_total , sum(sum(bw.total)) over (partition by coalesce(bsk.parent_key, 'OTHERS'), coalesce(bsk.key, 'OTHERS')) as overall_total group by 1, 2, 3 order by 1 desc, parent_overall_total desc, overall_total desc ) , billings_status_dims as ( with keys as ( from billings_status_timeline_1 select key , parent_key , sum(total) as overall_total , sum(sum(total)) over (partition by parent_key) as parent_overall_total group by 1, 2 order by parent_overall_total desc, overall_total desc ) select parent_key , key , d._d_date , overall_total , parent_overall_total from keys cross join (select distinct _d_date from billings_where) d order by d._d_date , parent_overall_total desc nulls last , overall_total desc nulls last ) , billings_status_timeline_2 as ( select d._d_date , d.parent_key , d.key as key , t.delivered_ratio , t.delivered_transactions , d.overall_total , d.parent_overall_total , t.total , t.total / NULLIF(SUM(total) OVER (partition by d._d_date), 0) :: float as ratio from billings_status_dims d left join billings_status_timeline_1 t on d._d_date = t._d_date and d.parent_key = t.parent_key and d.key = t.key order by d._d_date , d.parent_overall_total desc, d.overall_total desc ) select * from billings_status_timeline_249:[] 43:{"id":"613be682b957b8a8","slug":null,"title":"Transactions by acquirer_resp_msg","thumbnail":"e5b564b68cfd2d019551870fe65c7d608b5c70ff08f48f70889efa7eaf46fdd6","access_level":"public","create_time":"$D2025-05-13T14:10:58.736Z","update_time":"$D2025-07-30T11:26:17.321Z","view_time":null,"first_public_time":"$D2025-05-13T21:32:22.295Z","has_unpublished_edits":null,"fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"94cdb8fcfaa70289","login":"homam","name":"Homam Hosseini","avatar_url":"https://avatars-next.observableusercontent.com/e0def13806509253b7525280313ec25e10226ac9e6db096f202ba32c26977e45","tier":"starter_2024","bio":"","following":false},"version":472,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"2dc4aa8d6c8e9871","login":"homam","name":"Homam Hosseini","avatar_url":"https://avatars-next.observableusercontent.com/e0def13806509253b7525280313ec25e10226ac9e6db096f202ba32c26977e45","tier":null,"bio":"","following":false}],"members":[{"type":"individual","id":"2dc4aa8d6c8e9871","login":"homam","name":"Homam Hosseini","avatar_url":"https://avatars-next.observableusercontent.com/e0def13806509253b7525280313ec25e10226ac9e6db096f202ba32c26977e45","tier":null,"role":"editor"},{"type":"team","id":"94cdb8fcfaa70289","login":"homam","name":"Homam Hosseini","avatar_url":"https://avatars-next.observableusercontent.com/e0def13806509253b7525280313ec25e10226ac9e6db096f202ba32c26977e45","tier":"starter_2024","role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Transactions by acquirer_resp_msg","cells":[{"id":0,"value":"# Transactions by acquirer_resp_msg","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":154,"value":"viewof selectedAnalyzing = Inputs.select(new Map([['Trial', 0]].concat(d3.range(1, 6).map(r => [`Renewal ${r}`, r]))), {value: searchParams.get(\"analyzing\") || \"1\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":63,"value":"viewof selectedGateway = Inputs.select(gateways, {label: \"Select a Gateway\", value: searchParams.get(\"gateway\")})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":111,"value":"viewof selectedCountry = Inputs.select(await getDimensionValues('_d_user_country'), {label: \"Select a Country\", value: searchParams.get(\"country\")})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":145,"value":"viewof selectedPaymentType = Inputs.select(await getDimensionValues('_d_payment_type'), {label: \"Select a Payment Type\", value: searchParams.get(\"payment-type\")})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":158,"value":"viewof selectedBank = Inputs.select(await getDimensionValues('_d_bank_name'), {label: \"Select a Bank\", value: searchParams.get(\"bank\")})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":419,"value":"viewof selectedGoogleCampaign = Inputs.select(await getDimensionValuesGoogleCampaignId(), {label: \"Select a Google Campaign\", value: searchParams.get(\"google_campaigns\")})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":166,"value":"viewof fromDate = Inputs.date({label: \"From\", value: searchParams.get(\"from-date\") || joda.LocalDate.now().minusMonths(2).withDayOfMonth(1).toString()})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":179,"value":"viewof toDate = Inputs.date({label: \"To\", value: searchParams.get(\"to-date\") || joda.LocalDate.now().toString()})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":267,"value":"viewof selectedGroupnorm = Inputs.radio([\"default\", \"percent\"], {label: \"Select group normalization for status\", value: searchParams.get(\"groupnorm\") || \"default\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":58,"value":"$45","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":270,"value":"$46","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":272,"value":"colors_ = d3.scaleOrdinal().range(d3.schemeCategory10)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":392,"value":"d3.group(statusDs, d => d.parent_key)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":383,"value":"$47","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":29,"value":"allDimensions = Object.keys((await client.query(`select * from billings limit 1`))[0]).filter(d => d.startsWith('_d_'))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":139,"value":"gateways = ['ANY'].concat((await client.query(`select _d_gateway as d from billings \n group by 1 order by count(*) desc`)).map(d => d.d))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":69,"value":"async function getDimensionValues(key) {\n return ['ANY'].concat((await client.query(`select ${key} as d from billings \n WHERE ${selectedGateway != 'ANY' ? `_d_gateway = '${selectedGateway}'` : true}\n\n group by 1 order by count(*) desc`)).map(d => d.d))\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":433,"value":"async function getDimensionValuesGoogleCampaignId() {\n const key = '_d_google_campaign_id';\n return ['ANY'].concat((await client.query(`select ${key} as d from billings \n WHERE ${selectedGateway != 'ANY' ? `_d_gateway = '${selectedGateway}'` : true}\n AND ${selectedCountry != 'ANY' ? `_d_user_country = '${selectedCountry}'` : true}\n AND ${selectedPaymentType != 'ANY' ? `_d_payment_type = '${selectedPaymentType}'` : true}\n group by 1 order by count(*) desc`)).map(d => d.d))\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":13,"value":"filters = [\n ['_d_gateway', selectedGateway]\n, ['_d_user_country', selectedCountry]\n, ['_d_payment_type', selectedPaymentType]\n, ['_d_billing_order', selectedAnalyzing]\n, ['_d_bank_name', selectedBank]\n, ['_d_google_campaign_id', selectedGoogleCampaign]\n].filter(([k, v]) => v != 'ANY')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":15,"value":"where = `_d_date >= '${fromDate.toJSON().split('T')[0]}'\nAND _d_date < '${toDate.toJSON().split('T')[0]}'\nAND ` + [...filters].map(([k, v]) => `${k} = '${v}'`).join('\\n AND ') || 'true'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":4,"value":"client = {\n const client = await DuckDBClient.of({})\n\n await client.query(`CREATE TABLE 'billings' AS FROM 'https://mobirun.s3.amazonaws.com/cc/transactions_d7/result-named.parquet?cache-buster=${new Date().toJSON().split('T')[0]}v7';`);\n return client;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":468,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:client"},{"id":23,"value":"billings_where = client.query(`select * from billings where ${where}`)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":437,"value":"from dates select max(_d_date)","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:dates"},{"id":348,"value":"$48","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"status_timeseries","database":"var:billings_where"},{"id":352,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"billings_status_timeline_1","database":"var:billings_where"},{"id":257,"value":"select key, parent_key, array_agg({date: _d_date::text, total: coalesce(total :: int, 0)} order by _d_date) as timeline \n from status_timeseries u\n group by key, parent_key\norder by sum(parent_overall_total) desc nulls last, sum(overall_total) desc","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"statusDsQ","database":"var:status_timeseries"},{"id":275,"value":"statusDs = statusDsQ.map(d => ({key: d.key, parent_key: d.parent_key, timeline: d.timeline.toArray()}))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":6,"value":"select \n _d_date\n , SUM(s.delivered_transactions) :: float / NULLIF(SUM(total), 0) :: int as delivered_ratio\n , SUM(s.delivered_transactions) :: int as delivered_transactions\n , SUM(s.total) :: int as total \nfrom billings_where s\ngroup by _d_date\norder by _d_date","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"dataset","database":"var:billings_where"},{"id":42,"value":"----\n### Imports","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":65,"value":"searchParams = new URL(location.href).searchParams","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":46,"value":"Plotly = require(\"https://cdn.plot.ly/plotly-latest.min.js\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":49,"value":"joda = require(\"@js-joda/core\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":119,"value":"B = import(\"@mobily/ts-belt\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":52,"value":"ss = require(\"simple-statistics\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":55,"value":"function mkTrendLine(y) {\n\tconst x = [...Array(y.length).keys()] // [0, 1, 2, ...] same length as y\n\tconst points = x.map((xi, i) => [xi, y[i]])\n\tconst regression = ss.linearRegression(points)\n\tconst trendLine = x.map(xi => ss.linearRegressionLine(regression)(xi))\n return trendLine\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W49","stdlib":"1"}} 44:"$43:body"