{"queries":[{"id":"p","compiledQueryString":"select * FROM bigquery.inventory_value","inputQueryString":"select * FROM bigquery.inventory_value","compiled":false,"inline":true},{"id":"metrics_inventory","compiledQueryString":"SELECT \ncast(Date as date) as month,\nsum(cast(Quantity as float4))*2.34 as inventory_volume,\nsum(cast(Value as float4))*2.34 as inventory_value,\nsum(unit_sold_volume_abs)*2.34 as unit_sold_volume,\nsum(unit_sold_value_abs)*2.34 as unit_sold_value,\nsum(cast(COGS as float4))*2.34 as cogs\nFROM bigquery.inventory_value\nGROUP BY month,\nORDER BY month ASC","inputQueryString":"SELECT \ncast(Date as date) as month,\nsum(cast(Quantity as float4))*2.34 as inventory_volume,\nsum(cast(Value as float4))*2.34 as inventory_value,\nsum(unit_sold_volume_abs)*2.34 as unit_sold_volume,\nsum(unit_sold_value_abs)*2.34 as unit_sold_value,\nsum(cast(COGS as float4))*2.34 as cogs\nFROM bigquery.inventory_value\nGROUP BY month,\nORDER BY month ASC","compiled":false,"inline":true},{"id":"big_metrics_inventory","compiledQueryString":"SELECT \nsum(cast(Quantity as float4))*2.34 as inventory_volume,\nsum(cast(Value as float4))*2.34 as inventory_value,\nsum(unit_sold_volume_abs)*2.34 as unit_sold_volume,\nsum(unit_sold_value_abs)*2.34 as unit_sold_value,\nsum(cast(COGS as float4))*2.34 as cogs\nFROM bigquery.inventory_value WHERE DATE between '${inputs.date.start}'AND '${inputs.date.end}'","inputQueryString":"SELECT \nsum(cast(Quantity as float4))*2.34 as inventory_volume,\nsum(cast(Value as float4))*2.34 as inventory_value,\nsum(unit_sold_volume_abs)*2.34 as unit_sold_volume,\nsum(unit_sold_value_abs)*2.34 as unit_sold_value,\nsum(cast(COGS as float4))*2.34 as cogs\nFROM bigquery.inventory_value WHERE DATE between '${inputs.date.start}'AND '${inputs.date.end}'","compiled":false,"inline":true},{"id":"unit_sold","compiledQueryString":"SELECT \ncast(Date as date) as month,\nSKU,\nsum(cast(Quantity as float4))*2.34 as inventory_quantity,\nsum(cast(Value as float4))*2.34 as inventory_value,\nsum(unit_sold_volume_abs)*2.34 as unit_sold_volume,\nsum(unit_sold_value_abs)*2.34 as unit_sold_value,\nsum(cast(COGS as float4))*2.34 as cogs\nFROM bigquery.inventory_value\nGROUP BY month, SKU, \nORDER BY month ASC","inputQueryString":"SELECT \ncast(Date as date) as month,\nSKU,\nsum(cast(Quantity as float4))*2.34 as inventory_quantity,\nsum(cast(Value as float4))*2.34 as inventory_value,\nsum(unit_sold_volume_abs)*2.34 as unit_sold_volume,\nsum(unit_sold_value_abs)*2.34 as unit_sold_value,\nsum(cast(COGS as float4))*2.34 as cogs\nFROM bigquery.inventory_value\nGROUP BY month, SKU, \nORDER BY month ASC","compiled":false,"inline":true},{"id":"inventory","compiledQueryString":"WITH inventory_data AS (\n SELECT \n cast(Date as date) as month,\n SKU,\n sum(cast(Quantity as float4))*2.34 as inventory_quantity,\n sum(cast(Value as float4))*2.34 as inventory_value,\n sum(unit_sold_volume_abs)*2.34 as unit_sold_volume,\n sum(unit_sold_value_abs)*2.34 as unit_sold_value,\n sum(cast(COGS as float4))*2.34 as cogs\n FROM bigquery.inventory_value\n WHERE cast(Date as date) < current_date\n GROUP BY month, SKU\n),\navg_inventory AS (\n SELECT\n month,\n SKU,\n inventory_quantity,\n inventory_value,\n unit_sold_volume,\n unit_sold_value,\n cogs,\n (LAG(inventory_quantity, 2) OVER (PARTITION BY SKU ORDER BY month) +\n LAG(inventory_quantity, 1) OVER (PARTITION BY SKU ORDER BY month) +\n inventory_quantity) / 3.0 AS avg_inventory_quantity,\n (LAG(inventory_value, 2) OVER (PARTITION BY SKU ORDER BY month) +\n LAG(inventory_value, 1) OVER (PARTITION BY SKU ORDER BY month) +\n inventory_value) / 3.0 AS avg_inventory_value,\n (LAG(unit_sold_value, 2) OVER (PARTITION BY SKU ORDER BY month) +\n LAG(unit_sold_value, 1) OVER (PARTITION BY SKU ORDER BY month) +\n unit_sold_value) AS total_unit_sold_value_3_months\n FROM inventory_data\n)\nSELECT \n month,\n SKU,\n inventory_quantity,\n inventory_value,\n unit_sold_volume,\n unit_sold_value,\n cogs,\n avg_inventory_quantity,\n avg_inventory_value,\n total_unit_sold_value_3_months,\n (total_unit_sold_value_3_months / avg_inventory_value) * 90 AS DOH\nFROM avg_inventory\nWHERE avg_inventory_quantity IS NOT NULL AND avg_inventory_value IS NOT NULL\nAND SKU LIKE '${inputs.d_sku.value}'\nORDER BY month ASC","inputQueryString":"WITH inventory_data AS (\n SELECT \n cast(Date as date) as month,\n SKU,\n sum(cast(Quantity as float4))*2.34 as inventory_quantity,\n sum(cast(Value as float4))*2.34 as inventory_value,\n sum(unit_sold_volume_abs)*2.34 as unit_sold_volume,\n sum(unit_sold_value_abs)*2.34 as unit_sold_value,\n sum(cast(COGS as float4))*2.34 as cogs\n FROM bigquery.inventory_value\n WHERE cast(Date as date) < current_date\n GROUP BY month, SKU\n),\navg_inventory AS (\n SELECT\n month,\n SKU,\n inventory_quantity,\n inventory_value,\n unit_sold_volume,\n unit_sold_value,\n cogs,\n (LAG(inventory_quantity, 2) OVER (PARTITION BY SKU ORDER BY month) +\n LAG(inventory_quantity, 1) OVER (PARTITION BY SKU ORDER BY month) +\n inventory_quantity) / 3.0 AS avg_inventory_quantity,\n (LAG(inventory_value, 2) OVER (PARTITION BY SKU ORDER BY month) +\n LAG(inventory_value, 1) OVER (PARTITION BY SKU ORDER BY month) +\n inventory_value) / 3.0 AS avg_inventory_value,\n (LAG(unit_sold_value, 2) OVER (PARTITION BY SKU ORDER BY month) +\n LAG(unit_sold_value, 1) OVER (PARTITION BY SKU ORDER BY month) +\n unit_sold_value) AS total_unit_sold_value_3_months\n FROM inventory_data\n)\nSELECT \n month,\n SKU,\n inventory_quantity,\n inventory_value,\n unit_sold_volume,\n unit_sold_value,\n cogs,\n avg_inventory_quantity,\n avg_inventory_value,\n total_unit_sold_value_3_months,\n (total_unit_sold_value_3_months / avg_inventory_value) * 90 AS DOH\nFROM avg_inventory\nWHERE avg_inventory_quantity IS NOT NULL AND avg_inventory_value IS NOT NULL\nAND SKU LIKE '${inputs.d_sku.value}'\nORDER BY month ASC","compiled":false,"inline":true},{"id":"abc","compiledQueryString":"-- Calculer les ventes totales par SKU\nWITH total_sales AS (\n SELECT \n SKU, \n SUM(Unit_Sold)*2.34 AS total_units_sold\n FROM bigquery.stocks\n where date between '${inputs.dateABC.start}'AND '${inputs.dateABC.end}'\n GROUP BY SKU\n),\n\n-- Calculer les pourcentages de ventes et les ventes cumulées\nsales_percentage AS (\n SELECT \n SKU, \n total_units_sold, \n total_units_sold / SUM(total_units_sold) OVER () AS sales_percentage,\n COUNT(*) OVER () AS total_skus\n FROM total_sales\n),\n\n-- Ajouter les ventes cumulées\ncumulative_sales AS (\n SELECT \n SKU,\n total_units_sold,\n sales_percentage,\n 100.0 / total_skus AS percentage_of_total_items,\n SUM(sales_percentage) OVER (ORDER BY sales_percentage DESC) AS cumulative_sales_percentage\n FROM sales_percentage\n),\n\n-- Classifier en catégories ABC\nabc_classification AS (\n SELECT \n SKU,\n sales_percentage,\n percentage_of_total_items,\n cumulative_sales_percentage,\n CASE\n WHEN cumulative_sales_percentage <= 80 THEN 'A'\n WHEN cumulative_sales_percentage <= 95 THEN 'B'\n ELSE 'C'\n END AS category\n FROM cumulative_sales\n)\n\n-- Sélectionner le résultat final avec ordre inversé\nSELECT \n SKU, \n sales_percentage,\n percentage_of_total_items,\n cumulative_sales_percentage,\n category\nFROM abc_classification\nORDER BY cumulative_sales_percentage asc","inputQueryString":"-- Calculer les ventes totales par SKU\nWITH total_sales AS (\n SELECT \n SKU, \n SUM(Unit_Sold)*2.34 AS total_units_sold\n FROM bigquery.stocks\n where date between '${inputs.dateABC.start}'AND '${inputs.dateABC.end}'\n GROUP BY SKU\n),\n\n-- Calculer les pourcentages de ventes et les ventes cumulées\nsales_percentage AS (\n SELECT \n SKU, \n total_units_sold, \n total_units_sold / SUM(total_units_sold) OVER () AS sales_percentage,\n COUNT(*) OVER () AS total_skus\n FROM total_sales\n),\n\n-- Ajouter les ventes cumulées\ncumulative_sales AS (\n SELECT \n SKU,\n total_units_sold,\n sales_percentage,\n 100.0 / total_skus AS percentage_of_total_items,\n SUM(sales_percentage) OVER (ORDER BY sales_percentage DESC) AS cumulative_sales_percentage\n FROM sales_percentage\n),\n\n-- Classifier en catégories ABC\nabc_classification AS (\n SELECT \n SKU,\n sales_percentage,\n percentage_of_total_items,\n cumulative_sales_percentage,\n CASE\n WHEN cumulative_sales_percentage <= 80 THEN 'A'\n WHEN cumulative_sales_percentage <= 95 THEN 'B'\n ELSE 'C'\n END AS category\n FROM cumulative_sales\n)\n\n-- Sélectionner le résultat final avec ordre inversé\nSELECT \n SKU, \n sales_percentage,\n percentage_of_total_items,\n cumulative_sales_percentage,\n category\nFROM abc_classification\nORDER BY cumulative_sales_percentage asc","compiled":false,"inline":true},{"id":"limit","compiledQueryString":"-- Calculer les ventes totales par SKU\nWITH total_sales AS (\n SELECT \n SKU, \n SUM(Unit_Sold) AS total_units_sold\n FROM bigquery.stocks\n where date between '${inputs.dateABC.start}'AND '${inputs.dateABC.end}'\n GROUP BY SKU\n),\n\n-- Calculer les pourcentages de ventes et les ventes cumulées\nsales_percentage AS (\n SELECT \n SKU, \n total_units_sold, \n total_units_sold * 100.0 / SUM(total_units_sold) OVER () AS sales_percentage,\n COUNT(*) OVER () AS total_skus\n FROM total_sales\n),\n\n-- Ajouter les ventes cumulées\ncumulative_sales AS (\n SELECT \n SKU,\n total_units_sold,\n sales_percentage,\n 100.0 / total_skus AS percentage_of_total_items,\n SUM(sales_percentage) OVER (ORDER BY sales_percentage DESC) AS cumulative_sales_percentage\n FROM sales_percentage\n),\n\n-- Classifier en catégories ABC\nabc_classification AS (\n SELECT \n SKU,\n sales_percentage,\n percentage_of_total_items,\n cumulative_sales_percentage,\n CASE\n WHEN cumulative_sales_percentage <= 80 THEN 'A'\n WHEN cumulative_sales_percentage <= 95 THEN 'B'\n ELSE 'C'\n END AS category,\n ROW_NUMBER() OVER (ORDER BY sales_percentage DESC) AS sales_rank\n FROM cumulative_sales\n),\n\n-- Sélectionner les SKU spécifiques\nsku_selection AS (\n SELECT\n (SELECT SKU FROM abc_classification ORDER BY sales_rank LIMIT 1) AS SKU1,\n (SELECT SKU FROM abc_classification WHERE cumulative_sales_percentage >= 79 ORDER BY cumulative_sales_percentage LIMIT 1) AS SKU2,\n (SELECT SKU FROM abc_classification WHERE cumulative_sales_percentage > 95 ORDER BY cumulative_sales_percentage LIMIT 1) AS SKU3\n)\n\n-- Sélectionner les SKU spécifiques avec catégories\nSELECT \n SKU1,\n 'A' AS category1,\n SKU2,\n 'B' AS category2,\n SKU3,\n 'C' AS category3\nFROM sku_selection","inputQueryString":"-- Calculer les ventes totales par SKU\nWITH total_sales AS (\n SELECT \n SKU, \n SUM(Unit_Sold) AS total_units_sold\n FROM bigquery.stocks\n where date between '${inputs.dateABC.start}'AND '${inputs.dateABC.end}'\n GROUP BY SKU\n),\n\n-- Calculer les pourcentages de ventes et les ventes cumulées\nsales_percentage AS (\n SELECT \n SKU, \n total_units_sold, \n total_units_sold * 100.0 / SUM(total_units_sold) OVER () AS sales_percentage,\n COUNT(*) OVER () AS total_skus\n FROM total_sales\n),\n\n-- Ajouter les ventes cumulées\ncumulative_sales AS (\n SELECT \n SKU,\n total_units_sold,\n sales_percentage,\n 100.0 / total_skus AS percentage_of_total_items,\n SUM(sales_percentage) OVER (ORDER BY sales_percentage DESC) AS cumulative_sales_percentage\n FROM sales_percentage\n),\n\n-- Classifier en catégories ABC\nabc_classification AS (\n SELECT \n SKU,\n sales_percentage,\n percentage_of_total_items,\n cumulative_sales_percentage,\n CASE\n WHEN cumulative_sales_percentage <= 80 THEN 'A'\n WHEN cumulative_sales_percentage <= 95 THEN 'B'\n ELSE 'C'\n END AS category,\n ROW_NUMBER() OVER (ORDER BY sales_percentage DESC) AS sales_rank\n FROM cumulative_sales\n),\n\n-- Sélectionner les SKU spécifiques\nsku_selection AS (\n SELECT\n (SELECT SKU FROM abc_classification ORDER BY sales_rank LIMIT 1) AS SKU1,\n (SELECT SKU FROM abc_classification WHERE cumulative_sales_percentage >= 79 ORDER BY cumulative_sales_percentage LIMIT 1) AS SKU2,\n (SELECT SKU FROM abc_classification WHERE cumulative_sales_percentage > 95 ORDER BY cumulative_sales_percentage LIMIT 1) AS SKU3\n)\n\n-- Sélectionner les SKU spécifiques avec catégories\nSELECT \n SKU1,\n 'A' AS category1,\n SKU2,\n 'B' AS category2,\n SKU3,\n 'C' AS category3\nFROM sku_selection","compiled":false,"inline":true},{"id":"mm","compiledQueryString":"WITH \n -- Récupère l'inventaire actuel à partir de 2024-06-08\n current_inventory AS (\n SELECT\n sku,\n available AS inventory_start,\n date\n FROM\n bigquery.daily_inventory\n WHERE\n date = '2024-06-08'\n ),\n -- Récupère les unités vendues\n unit_sold_data AS (\n SELECT\n CAST(date AS DATE) AS date,\n SKU,\n CAST(Unit_Sold AS FLOAT4) AS Unit_Sold\n FROM\n bigquery.unit_sold\n ),\n -- Récupère les commandes d'achat\n purchase_order_data AS (\n SELECT\n CAST(date AS DATE) AS date,\n SKU,\n CAST(Quantity AS FLOAT4) AS Quantity\n FROM\n bigquery.repurchase\n\n ),\n -- Calcule l'inventaire avant le 8 juin 2024\n inventory_before AS (\n SELECT\n us.date,\n us.SKU,\n us.Unit_Sold,\n LAG(ci.inventory_start) OVER (PARTITION BY us.SKU ORDER BY us.date) +\n COALESCE(SUM(po.Quantity) OVER (PARTITION BY us.SKU ORDER BY us.date), 0) -\n SUM(us.Unit_Sold) OVER (PARTITION BY us.SKU ORDER BY us.date) AS inventory_end,\n LAG(ci.inventory_start) OVER (PARTITION BY us.SKU ORDER BY us.date) +\n COALESCE(SUM(po.Quantity) OVER (PARTITION BY us.SKU ORDER BY us.date), 0) -\n SUM(us.Unit_Sold) OVER (PARTITION BY us.SKU ORDER BY us.date) + us.Unit_Sold AS inventory_start\n FROM\n unit_sold_data us\n LEFT JOIN current_inventory ci ON us.SKU = ci.sku\n LEFT JOIN purchase_order_data po ON us.SKU = po.SKU AND us.date = po.date\n WHERE\n us.date < '2024-06-08'\n ),\n -- Calcule l'inventaire à partir du 8 juin 2024\n inventory_after AS (\n SELECT\n us.date,\n us.SKU,\n us.Unit_Sold,\n IFNULL(LAG(ci.inventory_start) OVER (PARTITION BY us.SKU ORDER BY us.date), ci.inventory_start) + \n COALESCE(SUM(po.Quantity) OVER (PARTITION BY us.SKU ORDER BY us.date), 0) - \n SUM(us.Unit_Sold) OVER (PARTITION BY us.SKU ORDER BY us.date) AS inventory_end,\n IFNULL(LAG(ci.inventory_start) OVER (PARTITION BY us.SKU ORDER BY us.date), ci.inventory_start) + \n COALESCE(SUM(po.Quantity) OVER (PARTITION BY us.SKU ORDER BY us.date), 0) - \n SUM(us.Unit_Sold) OVER (PARTITION BY us.SKU ORDER BY us.date) + us.Unit_Sold AS inventory_start\n FROM\n unit_sold_data us\n LEFT JOIN current_inventory ci ON us.SKU = ci.sku\n LEFT JOIN purchase_order_data po ON us.SKU = po.SKU AND us.date = po.date\n WHERE\n us.date >= '2024-06-08'\n ),\n -- Combine les deux jeux de données\n combined_inventory AS (\n SELECT * FROM inventory_before\n UNION ALL\n SELECT * FROM inventory_after\n ),\n -- Ajoute les colonnes month et month_type\n final_inventory AS (\n SELECT \n CAST(date AS DATE) AS date,\n SKU,\n Unit_Sold,\n inventory_start,\n inventory_end,\n CASE\n WHEN date < '2024-06-08' THEN 'Actuals'\n ELSE 'Forecasted'\n END AS date_type,\n strftime('%Y-%m-01', date) AS month,\n CASE\n WHEN date < '2024-06-01' THEN 'Actual'\n WHEN strftime('%Y-%m', date) = '2024-06' THEN 'Estimate'\n ELSE 'Forecast'\n END AS month_type\n FROM\n combined_inventory\n )\nSELECT \n fi.date,\n fi.SKU,\n fi.Unit_Sold,\n fi.inventory_start,\n fi.inventory_end,\n fi.date_type,\n fi.month,\n fi.month_type,\n pod.Quantity AS purchase_order\nFROM\n final_inventory fi\n LEFT JOIN purchase_order_data pod ON fi.date = pod.date AND fi.SKU = pod.SKU\nORDER BY\n fi.date DESC,\n fi.SKU","inputQueryString":"WITH \n -- Récupère l'inventaire actuel à partir de 2024-06-08\n current_inventory AS (\n SELECT\n sku,\n available AS inventory_start,\n date\n FROM\n bigquery.daily_inventory\n WHERE\n date = '2024-06-08'\n ),\n -- Récupère les unités vendues\n unit_sold_data AS (\n SELECT\n CAST(date AS DATE) AS date,\n SKU,\n CAST(Unit_Sold AS FLOAT4) AS Unit_Sold\n FROM\n bigquery.unit_sold\n ),\n -- Récupère les commandes d'achat\n purchase_order_data AS (\n SELECT\n CAST(date AS DATE) AS date,\n SKU,\n CAST(Quantity AS FLOAT4) AS Quantity\n FROM\n bigquery.repurchase\n\n ),\n -- Calcule l'inventaire avant le 8 juin 2024\n inventory_before AS (\n SELECT\n us.date,\n us.SKU,\n us.Unit_Sold,\n LAG(ci.inventory_start) OVER (PARTITION BY us.SKU ORDER BY us.date) +\n COALESCE(SUM(po.Quantity) OVER (PARTITION BY us.SKU ORDER BY us.date), 0) -\n SUM(us.Unit_Sold) OVER (PARTITION BY us.SKU ORDER BY us.date) AS inventory_end,\n LAG(ci.inventory_start) OVER (PARTITION BY us.SKU ORDER BY us.date) +\n COALESCE(SUM(po.Quantity) OVER (PARTITION BY us.SKU ORDER BY us.date), 0) -\n SUM(us.Unit_Sold) OVER (PARTITION BY us.SKU ORDER BY us.date) + us.Unit_Sold AS inventory_start\n FROM\n unit_sold_data us\n LEFT JOIN current_inventory ci ON us.SKU = ci.sku\n LEFT JOIN purchase_order_data po ON us.SKU = po.SKU AND us.date = po.date\n WHERE\n us.date < '2024-06-08'\n ),\n -- Calcule l'inventaire à partir du 8 juin 2024\n inventory_after AS (\n SELECT\n us.date,\n us.SKU,\n us.Unit_Sold,\n IFNULL(LAG(ci.inventory_start) OVER (PARTITION BY us.SKU ORDER BY us.date), ci.inventory_start) + \n COALESCE(SUM(po.Quantity) OVER (PARTITION BY us.SKU ORDER BY us.date), 0) - \n SUM(us.Unit_Sold) OVER (PARTITION BY us.SKU ORDER BY us.date) AS inventory_end,\n IFNULL(LAG(ci.inventory_start) OVER (PARTITION BY us.SKU ORDER BY us.date), ci.inventory_start) + \n COALESCE(SUM(po.Quantity) OVER (PARTITION BY us.SKU ORDER BY us.date), 0) - \n SUM(us.Unit_Sold) OVER (PARTITION BY us.SKU ORDER BY us.date) + us.Unit_Sold AS inventory_start\n FROM\n unit_sold_data us\n LEFT JOIN current_inventory ci ON us.SKU = ci.sku\n LEFT JOIN purchase_order_data po ON us.SKU = po.SKU AND us.date = po.date\n WHERE\n us.date >= '2024-06-08'\n ),\n -- Combine les deux jeux de données\n combined_inventory AS (\n SELECT * FROM inventory_before\n UNION ALL\n SELECT * FROM inventory_after\n ),\n -- Ajoute les colonnes month et month_type\n final_inventory AS (\n SELECT \n CAST(date AS DATE) AS date,\n SKU,\n Unit_Sold,\n inventory_start,\n inventory_end,\n CASE\n WHEN date < '2024-06-08' THEN 'Actuals'\n ELSE 'Forecasted'\n END AS date_type,\n strftime('%Y-%m-01', date) AS month,\n CASE\n WHEN date < '2024-06-01' THEN 'Actual'\n WHEN strftime('%Y-%m', date) = '2024-06' THEN 'Estimate'\n ELSE 'Forecast'\n END AS month_type\n FROM\n combined_inventory\n )\nSELECT \n fi.date,\n fi.SKU,\n fi.Unit_Sold,\n fi.inventory_start,\n fi.inventory_end,\n fi.date_type,\n fi.month,\n fi.month_type,\n pod.Quantity AS purchase_order\nFROM\n final_inventory fi\n LEFT JOIN purchase_order_data pod ON fi.date = pod.date AND fi.SKU = pod.SKU\nORDER BY\n fi.date DESC,\n fi.SKU","compiled":false,"inline":true}]}

Inventory

Overall

Inventory metrics by sku

Inventory financials

ABC Analysis