La solicitud
De Robert Kim, Director de Operaciones
“El margen bruto por canal se ve bien, pero ignora todo lo que devolvemos en las devoluciones. Para 2025, ¿cuánto aporta realmente cada canal una vez que se restan el costo de la mercancía y los reembolsos, en total y por pedido?”
Tu objetivo
El CTE costed del starter tiene una fila por cada línea válida de un pedido confirmado de 2025 con costo unitario conocido: order_id, channel, net, cogs y refund (0 cuando la línea no se devolvió). Devuelve channel, orders (pedidos distintos), net_sales, cogs, refunds, contribution (net_sales − cogs − refunds), contribution_pct (contribution × 100 / net_sales) y contribution_per_order (contribution / orders), con cada columna de dinero y de porcentaje redondeada a 2, ordenado por canal de A a Z.
- Lee de la tabla
productspendiente - Lee de la tabla
orderspendiente - Muestra exactamente
channelordersnet_salescogsrefundscontributioncontribution_pctcontribution_per_orderpendiente
Tu consulta
Cargando el editor…
Solución de referencia
| product_id | name | category | subcategory | unit_price |
|---|---|---|---|---|
| 101 | Vale Velvet Loveseat | Furniture | Sofas | 1659.00 |
| 102 | Alder Oak Coffee Table | Furniture | Tables | 919.00 |
| 103 | Orchard Rattan Dining Chair | Furniture | Chairs | 789.00 |
| 104 | Upland Oak Bookcase | Furniture | Storage | 429.00 |
Mote revisa tu resultado
Presiona Ejecutar. Cada fila que cumple el objetivo se vuelve una luciérnaga en tu linterna.
Lo que recibe Robert, y por qué
Tablas
Datos de la tienda Harbor & Pine, listos para consultar.
products120 filas · 8 columnas
product_idintegernamevarcharcategoryvarcharsubcategoryvarcharunit_pricedecimalunit_costdecimallaunched_ondateis_activeboolean
| product_id | name | category | subcategory | unit_price | unit_cost | launched_on | is_active |
|---|---|---|---|---|---|---|---|
| 101 | Vale Velvet Loveseat | Furniture | Sofas | 1659.00 | NULL | 2023-09-11 | true |
| 102 | Alder Oak Coffee Table | Furniture | Tables | 919.00 | 503.50 | 2024-05-16 | true |
| 103 | Orchard Rattan Dining Chair | Furniture | Chairs | 789.00 | 429.87 | 2023-05-27 | true |
| 104 | Upland Oak Bookcase | Furniture | Storage | 429.00 | 214.70 | 2022-12-20 | true |
| 105 | Avery Leather Bed Frame | Furniture | Beds | 449.00 | 232.70 | 2021-08-31 | true |
orders12,000 filas · 7 columnas
order_idintegercustomer_idintegerorder_datetimestampstatusvarcharchannelvarcharshipping_feedecimalcoupon_codevarchar
| order_id | customer_id | order_date | status | channel | shipping_fee | coupon_code |
|---|---|---|---|---|---|---|
| 1001 | 125 | 2023-01-01 20:51:19 | completed | web | 0.00 | NULL |
| 1002 | 156 | 2023-01-02 07:58:46 | completed | web | 0.00 | NULL |
| 1003 | 22 | 2023-01-02 10:25:27 | completed | marketplace | 4.99 | WELCOME10 |
| 1004 | 48 | 2023-01-02 11:21:51 | completed | marketplace | 4.99 | WELCOME10 |
| 1005 | 82 | 2023-01-02 11:43:36 | completed | web | 0.00 | WELCOME10 |
order_items30,338 filas · 6 columnas
order_item_idintegerorder_idintegerproduct_idintegerquantityintegerunit_pricedecimaldiscount_pctdecimal
| order_item_id | order_id | product_id | quantity | unit_price | discount_pct |
|---|---|---|---|---|---|
| 1 | 1001 | 121 | 9 | 31.99 | 0.00 |
| 2 | 1001 | 154 | 3 | 11.99 | 0.00 |
| 3 | 1002 | 154 | 4 | 11.99 | 0.00 |
| 4 | 1002 | 194 | 1 | 149.00 | 0.00 |
| 5 | 1002 | 193 | 2 | 19.99 | 0.00 |
returns1,673 filas · 5 columnas
return_idintegerorder_item_idintegerreturned_ondatereasonvarcharrefund_amountdecimal
| return_id | order_item_id | returned_on | reason | refund_amount |
|---|---|---|---|---|
| 1 | 14 | 2023-01-10 | damaged | 83.69 |
| 2 | 42 | 2023-01-10 | wrong_item | 19.99 |
| 3 | 105 | 2023-01-14 | wrong_item | 10.99 |
| 4 | 24 | 2023-01-16 | changed_mind | 26.99 |
| 5 | 126 | 2023-01-16 | late | 20.99 |
customers2,018 filas · 9 columnas
customer_idintegerfirst_namevarcharlast_namevarcharemailvarcharcityvarcharstatevarcharsignup_datedatesegmentvarcharreferral_sourcevarchar
| customer_id | first_name | last_name | city | state | signup_date | segment | referral_source | |
|---|---|---|---|---|---|---|---|---|
| 1 | Owen | Hudson | owen.hudson@icloud.com | San Francisco | CA | 2022-06-02 | consumer | search |
| 2 | Douglas | Mendoza | dmendoza69@gmail.com | Atlanta | GA | 2022-06-02 | consumer | NULL |
| 3 | Henry | Brooks | hbrooks@lakesidecafe.com | Los Angeles | CA | 2022-06-04 | business | NULL |
| 4 | Brian | Graham | bgraham@bluecedarhomes.com | Seattle | WA | 2022-06-06 | business | |
| 5 | Kyle | Jimenez | kjimenez27@yahoo.com | Indianapolis | IN | 2022-06-07 | consumer | NULL |
employees60 filas · 8 columnas
employee_idintegerfirst_namevarcharlast_namevarchardepartmentvarchartitlevarcharmanager_idintegerhire_datedatesalaryinteger
| employee_id | first_name | last_name | department | title | manager_id | hire_date | salary |
|---|---|---|---|---|---|---|---|
| 1 | Catherine | Hale | Executive | Chief Executive Officer | NULL | 2016-03-01 | 285000 |
| 2 | Robert | Kim | Executive | Chief Operating Officer | 1 | 2017-01-09 | 235000 |
| 3 | Megan | Collins | Sales | Head of Sales | 1 | 2018-04-16 | 172000 |
| 4 | Chloe | Gutierrez | Sales | Account Executive | 3 | 2020-01-21 | 72000 |
| 5 | Frank | Bell | Sales | Account Executive | 3 | 2023-05-21 | 73000 |
web_sessions40,000 filas · 7 columnas
session_idintegercustomer_idintegerstarted_attimestampdevicevarcharsourcevarcharpages_viewedintegerconvertedboolean
| session_id | customer_id | started_at | device | source | pages_viewed | converted |
|---|---|---|---|---|---|---|
| 1 | NULL | 2023-01-01 23:02:24 | mobile | paid | 1 | false |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid | 1 | false |
| 3 | NULL | 2023-01-01 16:55:46 | desktop | direct | 7 | false |
| 4 | NULL | 2023-01-01 12:43:28 | mobile | organic | 3 | false |
| 5 | NULL | 2023-01-01 13:06:21 | mobile | social | 2 | false |
marketing_spend3,276 filas · 3 columnas
spend_datedatechannelvarcharamountdecimal
| spend_date | channel | amount |
|---|---|---|
| 2023-01-01 | paid | 492.99 |
| 2023-01-01 | 37.38 | |
| 2023-01-01 | social | 200.53 |
| 2023-01-02 | paid | 709.13 |
| 2023-01-02 | 64.13 |
La lección
Del margen bruto a la contribución
El margen bruto resta un costo a las ventas: la mercancía. El margen de contribución resta todos los costos que crecen con cada venta, así que responde a “¿qué deja un pedido más para pagar los costos fijos?”. Qué costos cuentan depende de lo que el negocio pueda medir. Los datos de Harbor & Pine tienen dos:
- COGS: quantity × costo unitario.
- Reembolsos: el dinero devuelto en las líneas devueltas (
returns.refund_amount).
contribution = SUM(net) - SUM(cogs) - SUM(refund)En una empresa real también restarías las comisiones de pago, el empaque, el costo del envío de salida y las comisiones de marketplace. El método es el mismo: una columna más por cada costo variable.
Por qué los reembolsos van aquí
Una línea reembolsada se vendió, así que está en las ventas netas, y después su dinero volvió. El margen bruto la sigue contando como venta. La contribución quita el reembolso, que se acerca más al efectivo que la tienda realmente se quedó.
Un LEFT JOIN returns le da a cada línea su reembolso, o NULL cuando no se devolvió.
COALESCE(r.refund_amount, 0) convierte ese NULL en 0 para que la suma funcione. Con un JOIN
simple solo conservarías las líneas devueltas y perderías casi todas las ventas.
Por pedido: cuenta pedidos, no líneas
El CTE tiene una fila por línea de pedido. Para dividir entre pedidos, cuenta los ids de pedido distintos:
SUM(net - cogs - refund) / COUNT(DISTINCT order_id)COUNT(*) contaría unas 2,5 líneas por pedido y encogería el resultado en ese mismo factor.
Cómo leer el resultado
Los tres canales quedan a menos de medio punto entre sí en contribución %, así que ninguno es más barato de atender en mercancía y devoluciones. La cifra por pedido difiere más, porque los pedidos web son un poco más grandes. Ese es el número que debes comparar con lo que le cuesta a cada canal ganar un pedido, algo que esta tabla todavía no puede ver.
Errores comunes
- Unir returns con un join interno. Solo sobreviven las líneas devueltas y los totales por canal se desploman.
- Dividir entre líneas en vez de pedidos. Las cifras por pedido salen demasiado bajas.
- Llamar “ganancia” al margen bruto. Solo los reembolsos lo mueven seis puntos aquí.
¡Linterna encendida!
Practicaste: contribution margingross marginrefundsper ordercte
- Resuelto
- Sin pistas
- Al primer intento