Case study / Analytics

Olist Payment & Installment Behavior Analytics

An econometric payment investigation of 103,886 Brazilian transactions (R$ 16.0M GMV) proving that credit card installment financing drives a 3.3x surge in Average Order Value (r = 0.37) and diagnosing the 10x checkout anomaly.

SQLPython (Pandas & Seaborn)Power BI (DAX & Power Query)Econometric ModelingStatistical Hypothesis Testing

01. Overview

Problem

Marketplace conversion and basket size growth were constrained by fragmented payment method preferences and uncertainty around credit installment economics, risking margin erosion from high financing fees without clear visibility into category AOV elasticity.

Approach

Engineered an order-level payment aggregation and econometric modeling pipeline across 103,886 payment records using SQL, Python (Pandas/Seaborn), and Power BI (DAX); modeled installment elasticity across 74,975 credit card orders and mapped category financing sensitivity across durable vs consumable goods.

Key Finding

Demonstrated that credit cards drive 78.4% of total GMV with extended installments (7–10x) generating a 3.3x higher basket size (R$ 336.44 vs R$ 100.91), isolated a 5,328-order checkout heuristic anomaly at 10x installments, and established targeted 0% interest promotional frameworks for high-volume durable categories (Watches, Computers, Home Furniture).

02. Flowchart Architecture

PIPELINE ARCHITECTURE • 3-STAGE RELATIONAL FLOW

Ingestion, Aggregation & Category Attribution Flowchart

103,886 ROWS ➔ 99,440 ORDERS

STAGE 01Raw Transaction Schema Grain

INPUT & AGGREGATION LAYER

INPUT REPOSITORIES:olist_order_payments.csv (103,886 rows)STAGE DELIVERABLE:

Resolves multi-payment splits (e.g. Voucher + Credit Card co-payments)

TRANSFORMATION & NORMALIZATION LOGIC:

Group by order_id: SUM(payment_value) → Order Total GMV
Compute MAX(payment_installments) → Order Tenor Depth
Assign Dominant Method via argmax(payment_value)

STAGE 02Order-Level Analytical Entity

CORE ANALYTICAL GRAIN

INPUT REPOSITORIES:orders + payments merged (99,440 delivered orders)STAGE DELIVERABLE:

Clean 1-row-per-order analytical dataset with financial & installment attributes

TRANSFORMATION & NORMALIZATION LOGIC:

Filter on order_status = 'delivered' (R$ 16.01M total marketplace GMV)
Isolate Credit Card dominant cohort: 74,975 orders (78.4% GMV)
Segment cash & alternative rails: 19.2k Boleto, 3.8k Voucher, 1.5k Debit

STAGE 03First-Item Category & Econometric Attribution

TAXONOMY & SENSITIVITY MAPPING

INPUT REPOSITORIES:order_items (min order_item_id) + product_category_name_translationSTAGE DELIVERABLE:

Complete econometric modeling dataset for installment elasticity & category sensitivity

TRANSFORMATION & NORMALIZATION LOGIC:

Attribute primary basket category via min(order_item_id)
Map 71 Portuguese catalog taxonomy keys to English classifications
Tag High-Ticket Durables vs Fast-Moving Consumables cohorts

03. Interactive Payment Mix

FINTECH & BEHAVIORAL ECONOMICS • OLIST BRAZIL (103.9K TRANSACTIONS)

Payment Method Mix & Installment Elasticity Engine

💳 Credit Cards drive 78.4% of total marketplace GMV (R$ 12.54M), while Boleto Bancário acts as the vital cash-based alternative (17.9% GMV). Hover over any pie slice to inspect live floating telemetry.N = 103,886 PAYMENTS (R$ 16.01M GMV)

INTERACTIVE PIE DISTRIBUTION

Credit Card (78.4%)

Boleto Bancário (17.9%)

Voucher (2.4%)

Debit Card (1.4%)

CHANNEL TELEMETRY HUD

Credit Card

High-Ticket Conversion & Installment Financing Rail

TOTAL GMV VALUER$ 12.54M78.4% of total GMV

TOTAL TRANSACTIONS76,795 txns73.9% volume share

AVERAGE TICKET (AOV)R$ 163.32per transaction

AVG INSTALLMENT TENOR3.51xmonths duration

STRATEGIC BEHAVIORAL INSIGHT:

The primary marketplace growth engine. Generates 78.4% of platform GMV and serves as the sole rail supporting multi-month installment financing across Brazil.

04. 10x Checkout Anomaly

DIAGNOSTIC TELEMETRY • NON-LINEAR CHECKOUT BEHAVIOR

The 10x Installment Spike Anomaly Investigation

TRANSACTION VOLUME DISTRIBUTION BY INSTALLMENT DEPTH (N = 103,886 TXNS)10x ANOMALY: +727% SPIKE OVER 9x

52.5k

12.4k

10.5k

7.1k

5.2k

3.9k

1.6k

4.3k

644

5.3k

10x

326

11–24x

SELECTED INSTALLMENT DEPTH TELEMETRY

10x Installment Tier

⚠️ NON-LINEAR CHECKOUT ANOMALY (+727% vs 9x)

TRANSACTION VOLUME5,3285.13% share

AVERAGE ORDER VALUER$ 415.82mean basket size

DELTA VS PREVIOUS TIER+727.3%volume trajectory

ESTIMATED TOTAL GMVR$ 2.22Mcohort revenue

ROOT-CAUSE TRIANGULATION ARCHITECTURE:

UX ARCHITECTURE

1. Checkout UI Default & Truncation

The marketplace checkout dropdown menu historically truncated or pre-selected 10 installments as the maximum standard option, funneling shoppers into a default clustering pattern.

→ High artificial volume concentration at 10x

FINANCIAL INFRASTRUCTURE

2. 'Sem Juros' (0% Interest) Banking Ceiling

In Brazilian retail banking, 10 installments is the traditional merchant-subsidized interest-free threshold. Selecting 11x+ incurs immediate compound revolving interest, creating an acute demand cliff.

→ -93.9% drop from 10x to 11x+

BEHAVIORAL ECONOMICS

3. Decimal Psychological Anchoring

Consumers exhibit strong cognitive bias toward round base-10 calculations (e.g. R$ 41.58/mo for 10 months) when evaluating personal monthly liquidity allocation.

→ Preference for 10x over awkward 7x or 9x tenors

05. Category Sensitivity Matrix

CATALOG ECONOMETRICS • DURABLES VS CONSUMABLES SPECTRUM

Category Financing Sensitivity & Installment Matrix

TOP 15 HIGH-INSTALLMENT DURABLES (X-AXIS: 1.0x TO 8.0x)HOVER TO INSPECT

Computers

7.41x

R$1289

Home Appliances 2

5.55x

R$610

Home Comfort

5.18x

R$184

Office Furniture

4.78x

R$277

Kitchen / Garden Furniture

4.49x

R$254

Musical Instruments

4.49x

R$363

Agro Industry & Commerce

4.46x

R$439

Watches & Gifts

4.46x

R$244

Small Appliances

4.35x

R$338

Furniture Living Room

4.34x

R$216

Bed, Bath & Table

4.31x

R$137

Construction Tools Safety

4.27x

R$282

Construction Tools

4.12x

R$238

Home Construction

4.10x

R$211

Luggage & Accessories

4.07x

R$169

CATEGORY FINANCING PROFILEDURABLE GOODS

Computers

High-ticket tech & hardware

AVG INSTALLMENTS7.41xtenor duration

AVERAGE ORDER VALUER$ 1288.65mean basket size

TOTAL CREDIT ORDERS149order volume

ESTIMATED GMVR$ 192.0kcohort revenue

COMMERCIAL FINANCING ACTION:

Subsidize 10x 0% interest ('sem juros') to prevent cart abandonment on high-ticket tech.

06. Interactive Console

Payment & Installment Behavior Console

Explore monthly method trends, category average installments, and order-level audit records.

01 / TOTAL PAYMENT VALUER$ 16.008.872,12

99,437 total orders recorded across 27 Brazilian states.

02 / CREDIT CARD REVENUE SHARE78.3%

Generates 78.3% of GMV via 76.8k transactions. Only method offering installment flexibility.

Boleto: 17.9%Voucher: 2.4%Debit: 1.4%

03 / AVG CREDIT CARD INSTALLMENTS3.55x

Strongest volume in 1x (50.6%), with a notable secondary concentration at 10x (5,328 orders).

Anomaly: 10x volume jump (+727% vs 9x)

04 / INSTALLMENT VS ORDER VALUE3.3x

Pearson correlation r = 0.37 (descriptive). 7–10x orders average R$ 336.44 vs R$ 100.91 for 1x.

7–10x vs 1x: +233%Computers AOV: R$ 1.28k

Temporal Dynamics (2017–2018)

Monthly Payment Trajectory & Installment Overlay

Credit Card (78.3%) Boleto (17.9%) Voucher (2.4%) Debit Card (1.4%) Avg Installments (Right Axis)

Category Elasticity Hierarchy

Top 15 Categories by Installment Length

Durable goods (Computers, Furniture, Home Appliances) exhibit high installment counts (>5x) and high ticket sizes (up to R$ 1,288), whereas consumables (Food, Drinks) average <2.5x.

Computers180 orders • R$ 1.285,52 AOV

7.41x

Home appliances 2233 orders • R$ 530,98 AOV

5.55x

Home confort375 orders • R$ 181,23 AOV

5.18x

Office furniture1,265 orders • R$ 269,90 AOV

4.78x

Musical instruments624 orders • R$ 336,66 AOV

4.49x

Kitchen dining laundry garden furniture246 orders • R$ 238,15 AOV

4.49x

Watches gifts5,601 orders • R$ 232,67 AOV

4.46x

Agro industry and commerce182 orders • R$ 430,65 AOV

4.46x

Small appliances627 orders • R$ 329,12 AOV

4.35x

Furniture living room417 orders • R$ 210,01 AOV

4.34x

Bed bath table9,311 orders • R$ 133,28 AOV

4.31x

Construction tools safety162 orders • R$ 270,20 AOV

4.27x

Construction tools construction740 orders • R$ 224,85 AOV

4.12x

Home construction473 orders • R$ 203,51 AOV

4.10x

Luggage accessories1,023 orders • R$ 165,96 AOV

4.07x

Descriptive Correlation (r = 0.37)

Installment Tier vs Average Order Value

3.3x vs 1x Baseline

The data shows a consistent positive relationship: orders in 7–10 installments average R$ 336.44 (3.33x the 1x baseline of R$ 100.91).

1xR$ 100,91

24,004 orders (32%)

1x vs 1x

2-3xR$ 135,58

22,649 orders (30.2%)

1.34x vs 1x

4-6xR$ 182,56

16,160 orders (21.6%)

1.81x vs 1x

7-10xR$ 336,44

11,819 orders (15.8%)

3.33x vs 1x

11-24xR$ 358,82

343 orders (0.5%)

3.56x vs 1x

Data Control & Explorer

Multi-Parameter Filter Toolbar

Active Records: 1,200 / 1,200

Granular Audit View

Representative Order Payment Transactions

Order ID	Date ▼	Category	Method	Installments	Gross Value (R$)	Location
21b9bb429b09...	2018-08-27	Health beauty	CREDIT CARD	6x	R$ 138,97	Sao Paulo, SP
53dccb14668d...	2018-08-27	Health beauty	CREDIT CARD	10x	R$ 369,54	Sao Paulo, SP
5cd1f67d76b6...	2018-08-26	Health beauty	DEBIT CARD	1x	R$ 87,88	Rio De Janeiro, RJ
d2ce9688abb1...	2018-08-26	Health beauty	CREDIT CARD	1x	R$ 37,37	Jundiai, SP
7d65ecd2eaff...	2018-08-25	Housewares	CREDIT CARD	1x	R$ 150,28	Osasco, SP
8c65d56ff533...	2018-08-25	Cool stuff	CREDIT CARD	5x	R$ 107,09	Carapicuiba, SP
45494452d88e...	2018-08-25	Perfumery	VOUCHER	1x	R$ 60,41	Belo Horizonte, MG
3a94d54e3301...	2018-08-24	Bed bath table	CREDIT CARD	7x	R$ 77,59	Sao Paulo, SP
f9f88b0c2d64...	2018-08-24	Food	DEBIT CARD	1x	R$ 144,78	Socorro, SP
cf38c31c1883...	2018-08-22	Cool stuff	CREDIT CARD	10x	R$ 258,70	Sao Paulo, SP

Showing 1–10 of 1,200 transactions

Page 1 of 120

07. Data Preparation Pipeline

0101. Transaction Aggregation

Normalizes 103,886 multi-payment sequential rows into 99,440 order-level entities with dominant payment tagging

0202. Payment Channel Distribution

Analyzes share of wallet across Credit Card (78.4% GMV), Boleto (17.9% GMV), Voucher (2.4%), and Debit (1.4%)

0303. Installment Elasticity Model

Models Pearson correlation (r = 0.37) between installment length and order value, discovering a 3.3x AOV multiplier

0404. Category Sensitivity Matrix

Classifies 70+ product categories by financing dependency, isolating high-consideration durable goods from consumables

08. Detailed Analysis & Recommendations

NOTE

Executive Summary & Fintech Context:

- Core Challenge: Brazilian digital commerce is anchored by fragmented payment rails and consumer installment financing (*parcelamento*). Marketplace operators lacked empirical visibility into whether extended installment plans expand purchasing power or simply fragment working capital.

- Technical Solution: Ingested and modeled 103,886 payment records across 99,440 orders (R$ 16.01M GMV) via Python and SQL, calculating installment elasticity curves across 74,975 credit transactions and cross-tabulating 70+ product categories.

- Quantified Impact: Demonstrated that credit card installment plans drive 78.4% of total GMV with long tenors (7–10x) generating a 3.33x higher basket size (R$ 336.44 vs R$ 100.91), isolated a 5,328-order UI default anomaly at 10x, and formulated category-specific 0% interest financing frameworks.

Across 103,886 payment transaction records totaling R$ 16,008,872.12 GMV, transaction volumes and values divide across four distinct payment rails:

Payment Method Rail	Transaction Count	Volume Share (%)	Total Value (R$)	GMV Share (%)	Average Ticket (R$)	Avg Installment Tenor
Credit Card	76,795	73.9%	R$ 12,542,084.20	78.4%	R$ 163.32	3.51x
Boleto Bancário	19,784	19.0%	R$ 2,869,361.30	17.9%	R$ 145.03	1.00x
Voucher	5,775	5.6%	R$ 379,436.90	2.4%	R$ 65.70	1.00x
Debit Card	1,529	1.5%	R$ 217,989.80	1.4%	R$ 142.57	1.00x
Total Baseline	103,886	100.0%	R$ 16,008,872.20	100.0%	R$ 154.10	—

Channel Ecosystem Insights:

Credit Card Dominance (78.4% GMV): The only payment rail supporting multi-month installment plans, making it the indispensable conversion vehicle for high-ticket catalog items.
Boleto Bancário as Essential Cash Rail (19.0% Volume): Capturing nearly a fifth of transactions, Boleto serves unbanked consumers and disciplined shoppers avoiding credit debt.
Vouchers as Secondary Co-Payment Rails (5.6% Volume): Low average ticket (R$ 65.70), reflecting customer service credits and promotional cashbacks paired with primary credit cards.

02. Multi-Payment Ingestion & Normalization Rules

MERMAID
5 LINES

flowchart TD
    A["Raw Olist Payments<br/>(103,886 Transaction Records)"] --> B["Multi-Payment Sequential Aggregation<br/>(Sum Values & Max Installments)"]
    B --> C["Dominant Payment Type Assignment<br/>(Credit Card / Boleto / Voucher / Debit)"]
    C --> D["Installment Elasticity & AOV Multiplier<br/>(1x vs 7-10x: 3.33x Basket Surge)"]
    D --> E["Fintech Working Capital Architecture<br/>(0% Interest Subsidy Thresholds & Merchant P&L)"]

In Brazilian e-commerce, customers frequently combine promotional vouchers with secondary credit cards. The data pipeline executes three normalization rules:

Multi-Payment Sequential Aggregation (payment_sequential > 1):

Mathematical Model • Econometric FormulationSPECIFICATION

Order Total Value = ∑(i=1..k payment_valueᵢ, Order Max Installments = max(payment_installments₁, ..., payment_installmentsₖ))

Dominant Payment Type Assignment:

Mathematical Model • Econometric FormulationSPECIFICATION

Dominant Method = argmax(payment_value_type)

Category Attribution:

Mapped via min(order_item_id) and translated to standardized English taxonomy.

03. Installment Elasticity Model & Basket Size Multiplier

Across the cohort of 74,975 credit card dominant orders, we model the relationship between installment depth (X) and total order value (Y). The empirical data reveals a statistically robust correlation of r = 0.37:

Installment Length Tier	Dominant Order Count	Volume Share (%)	Mean AOV (R$)	Median AOV (R$)	Multiplier vs 1x	Growth Surge Interpretation
1x (Full Payment)	24,004	32.0%	`R$ 100.91`	`R$ 71.62`	`1.00x`	Baseline reference basket
2–3x Installments	22,649	30.2%	`R$ 135.58`	`R$ 111.38`	`1.34x`	+34.4% Basket expansion
4–6x Installments	16,160	21.6%	`R$ 182.56`	`R$ 128.28`	`1.81x`	+80.9% Basket expansion
7–10x Installments	11,819	15.8%	`R$ 336.44`	`R$ 206.78`	`3.33x`	+233.4% (High-Ticket Surge)
11–24x (Long-Tail)	341	0.5%	`R$ 360.37`	`R$ 216.05`	`3.57x`	+257.1% Financing Ceiling

TIP

Behavioral Finding: Customers choosing 7–10 installments spend 3.33x more per order than single-payment customers (R$ 336.44 vs R$ 100.91). Installments act as purchasing power catalysts.

04. Diagnostic Investigation: The 10x Checkout Anomaly

Analyzing the installment depth curve reveals an abrupt spike at 10x installments:

Installment Depth	Transaction Count	Distribution Share (%)	Curve Classification	Anomaly Diagnosis
1x	52,546	50.58%	Natural Modal Peak	Single payment & cash vouchers
2x – 6x	39,131	37.66%	Natural Exponential Decay	Standard short-to-mid financing
7x	1,626	1.57%	Monotonic Decrease	Natural decay trough
8x	4,268	4.11%	Minor Secondary Bump	Common merchant promo threshold
9x	644	0.62%	Sharp Valley Trough	Steep drop before boundary
10x	5,328	5.13%	⚠️ NON-LINEAR SPIKE	+727% Surge over 9x (0% Interest Ceiling & UI Default)
11–24x	326	0.31%	Ultra Long-Tail	Interest-bearing debt cliff

7 DATA ROWS • TOP-DOWN SCROLL↕ SCROLL TABLE (STICKY HEADER)

Root-Cause Triangulation:

Merchant 0% Interest (*Sem Juros*) Ceiling: In Brazilian retail banking, 10 installments is the traditional maximum ceiling for interest-free financing. Beyond 10x, compound interest fees create an acute demand cliff.
Checkout UI Configuration: Dropdown menus frequently set 10x as a highlighted preset.
Cognitive Decimal Anchoring: Consumers exhibit psychological preference for round 10-month amortizations when calculating personal cash flows.

05. Category Financing Sensitivity Matrix

High-consideration durable goods exhibit profound dependence on multi-month installment financing:

Category Name	Credit Orders	Avg Installments	Avg Order Value (R$)	Category Classification
Computers	149	7.41x	R$ 1,288.65	High-ticket tech & hardware
Home Appliances (Major)	179	5.55x	R$ 609.51	Major household appliances
Home Comfort	293	5.18x	R$ 183.53	Home improvement durables
Office Furniture	873	4.78x	R$ 276.90	Commercial & workspace equipment
Musical Instruments	455	4.49x	R$ 363.37	Specialty durable assets
Watches & Gifts	4,485	4.46x	R$ 243.97	High-volume revenue anchor
Bed, Bath & Table	7,265	4.31x	R$ 137.18	High-volume home textile
Drinks (Consumables)	230	1.95x	R$ 91.15	Fast-moving consumable

8 DATA ROWS • TOP-DOWN SCROLL↕ SCROLL TABLE (STICKY HEADER)

06. Strategic Spotlight: "Watches & Gifts" Commercial Blueprint

The Watches & Gifts category uniquely combines massive transaction velocity (4,485 orders) with elevated basket sizes (R$ 243.97 AOV) and high installment adoption (4.46x average installments):

Performance Dimension	Watches & Gifts Benchmark	Marketplace Overall Mean	Variance vs Baseline
Credit Card Share	80.1%	73.9%	+6.2%
Average Order Value (AOV)	R$ 243.97	R$ 161.04	+51.5%
Average Installment Tenor	4.46x	3.51x	+27.1%
Boleto Cash Share	16.4%	19.0%	-2.6%

Targeted Strategic Actions:

Subsidized 6x–10x "Sem Juros" Campaigns: Co-sponsor interest-free financing with top watch merchants during seasonal gifting peaks.
Monthly Price Anchoring in Checkout: Display *"10x of R$ 24.40"* instead of lump-sum *"R$ 243.97"* on product detail cards.
Cross-Sell Warranty Bundles: Pair timepieces with accessory straps using split voucher/credit co-payment options.

07. Strategic Action Recommendations

Priority	Strategic Pillar	Operational Action	Expected Business Impact
P0	Checkout UI Optimization	A/B test dynamic installment selectors displaying exact monthly costs ("10x of R$ 33.64" vs total amount).	Eliminates checkout drop-off and clarifies monthly affordability.
P0	Category 0% Interest Financing	Partner with merchant banking acquirers to offer targeted 0% interest promotions on durables (Computers, Appliances, Furniture).	Expands average order values by 20–35% on high-ticket inventory.
P1	Boleto Settlement Acceleration	Streamline Boleto workflows through automated WhatsApp barcode delivery and instant QR generation.	Reduces Boleto non-payment drop-off rate (historically 30–40%).

08. Strategic Fintech Lessons

Installments As Growth Engines: Customers actively leverage 7–10x installments to acquire high-ticket items that would otherwise be abandoned.
Anomalies Reveal Heuristics: Volume spikes at round numbers (10x) reflect checkout UI defaults and banking interest thresholds rather than smooth organic demand.
Boleto Remains Essential: Cash vouchers protect revenue from underbanked populations and credit-averse consumers.

09. Impact

10. Lessons Learned

In Latin American e-commerce, installment financing is an essential AOV growth engine: customers actively leverage 7–10x installments to acquire high-ticket items that would otherwise be abandoned.
Abrupt volume spikes at round installment numbers (e.g. 5,328 orders at 10x) reveal checkout UI defaults and psychological anchoring rather than organic consumer preference.
Boleto Bancário is an irreplaceable cash-based financial lifeline (19.0% volume) serving underbanked demographics and price-sensitive shoppers avoiding credit interest.

Olist Payment & Installment Behavior Analytics

Ingestion, Aggregation & Category Attribution Flowchart

Payment Method Mix & Installment Elasticity Engine

Credit Card

The 10x Installment Spike Anomaly Investigation

10x Installment Tier

Category Financing Sensitivity & Installment Matrix

Computers

Payment & Installment Behavior Console

Monthly Payment Trajectory & Installment Overlay

Top 15 Categories by Installment Length

Installment Tier vs Average Order Value

Multi-Parameter Filter Toolbar

Representative Order Payment Transactions

01. Brazilian Payment Infrastructure & Wallet Share Matrix

Channel Ecosystem Insights:

02. Multi-Payment Ingestion & Normalization Rules

03. Installment Elasticity Model & Basket Size Multiplier

04. Diagnostic Investigation: The 10x Checkout Anomaly

Root-Cause Triangulation:

05. Category Financing Sensitivity Matrix

06. Strategic Spotlight: "Watches & Gifts" Commercial Blueprint

Targeted Strategic Actions:

07. Strategic Action Recommendations

08. Strategic Fintech Lessons