Changes between Version 13 and Version 14 of AdvancedReports

Timestamp:

06/05/26 16:32:30 (6 days ago)

Author:

232012

Comment:

--

AdvancedReports

@@ -330 +330 @@
 {{{
 DiscountEvents <-
-σ m.type_of_modification = 'DISCOUNT'
-(
-  MODIFICATIONS m ⨝ (m.modification_id = mp.modification_id) MODIFICATION_PRODUCTS mp
+π m.modification_id,
+  m.admin_id,
+  m.date_modified,
+  discount_percentage := m.discount,
+  mp.product_id
+(
+  σ m.type_of_modification = 'DISCOUNT'
+  (
+    MODIFICATIONS m ⨝ (m.modification_id = mp.modification_id) MODIFICATION_PRODUCTS mp
+  )
 )
 
 PrePromoSales <-
-γ modification_id, product_id;
-  units_sold_before := SUM(op.quantity),
-  revenue_before := SUM(op.quantity * op.price_at_purchase)
-(
-  σ o.purchase_date ∈ [m.date_modified - 30 days, m.date_modified)
-    ∧ o.status ∈ {PAID, SHIPPED, DELIVERED}
-  (
-    DiscountEvents de ⨝ ORDER_PRODUCTS op ⨝ ORDERS o
-  )
+γ de.modification_id, de.product_id;
+  units_sold_before := COALESCE(SUM(op.quantity), 0),
+  revenue_before := COALESCE(SUM(op.quantity * op.price_at_purchase), 0.00)
+(
+  (
+    DiscountEvents de
+    ⟕ (de.product_id = op.product_id) ORDER_PRODUCTS op
+  )
+  ⟕ (
+      op.order_id = o.order_id
+      ∧ o.purchase_date >= de.date_modified - 30 days
+      ∧ o.purchase_date < de.date_modified
+      ∧ o.status ∈ {PAID, SHIPPED, DELIVERED}
+    ) ORDERS o
 )
 
 PostPromoSales <-
-γ modification_id, product_id;
-  units_sold_after := SUM(op.quantity),
-  revenue_after := SUM(op.quantity * op.price_at_purchase)
-(
-  σ o.purchase_date ∈ (m.date_modified, m.date_modified + 30 days]
-    ∧ o.status ∈ {PAID, SHIPPED, DELIVERED}
-  (
-    DiscountEvents de ⨝ ORDER_PRODUCTS op ⨝ ORDERS o
-  )
+γ de.modification_id, de.product_id;
+  units_sold_after := COALESCE(SUM(op.quantity), 0),
+  revenue_after := COALESCE(SUM(op.quantity * op.price_at_purchase), 0.00)
+(
+  (
+    DiscountEvents de
+    ⟕ (de.product_id = op.product_id) ORDER_PRODUCTS op
+  )
+  ⟕ (
+      op.order_id = o.order_id
+      ∧ o.purchase_date > de.date_modified
+      ∧ o.purchase_date <= de.date_modified + 30 days
+      ∧ o.status ∈ {PAID, SHIPPED, DELIVERED}
+    ) ORDERS o
 )
 
 PromoSummary <-
-π product_id,
+π de.product_id,
   release_title := r.title,
   product_format := p.format,
   promotion_start_date := de.date_modified,
   discount_applied := de.discount_percentage,
+  pre.units_sold_before,
+  post.units_sold_after,
   volume_change := post.units_sold_after - pre.units_sold_before,
-  net_revenue_impact := post.revenue_after - pre.revenue_before,
-  units_sold_before := pre.units_sold_before,
-  units_sold_after := post.units_sold_after,
-  revenue_before := pre.revenue_before,
-  revenue_after := post.revenue_after
+  pre.revenue_before,
+  post.revenue_after,
+  net_revenue_impact := post.revenue_after - pre.revenue_before
 (
   DiscountEvents de
-  ⨝ PRODUCTS p
-  ⨝ RELEASES r
-  ⨝ PrePromoSales pre
-  ⨝ PostPromoSales post
+  ⨝ (de.product_id = p.product_id) PRODUCTS p
+  ⨝ (p.release_id = r.release_id) RELEASES r
+  ⨝ (de.modification_id = pre.modification_id ∧ de.product_id = pre.product_id) PrePromoSales pre
+  ⨝ (de.modification_id = post.modification_id ∧ de.product_id = post.product_id) PostPromoSales post
 )
 
 Result <-
-τ promotion_start_date ↓, net_revenue_impact ↓
-(
-  π *,
-    promotion_verdict :=
-      CASE
-        WHEN net_revenue_impact > 0 ∧ volume_change > 0
-          THEN 'SUCCESS'
-        WHEN net_revenue_impact < 0 ∧ volume_change > 0
-          THEN 'MARGIN LOSS'
-        WHEN volume_change <= 0
-          THEN 'FAILURE'
-        ELSE 'NEUTRAL'
-      END
-  (
-    PromoSummary
-  )
-)
+π product_id,
+  release_title,
+  product_format,
+  promotion_start_date,
+  discount_applied,
+  units_sold_before,
+  units_sold_after,
+  volume_change,
+  revenue_before,
+  revenue_after,
+  net_revenue_impact,
+  promotion_verdict :=
+    CASE
+      WHEN net_revenue_impact > 0 ∧ volume_change > 0
+        THEN 'SUCCESS: Volume generated profit'
+      WHEN net_revenue_impact < 0 ∧ volume_change > 0
+        THEN 'MARGIN LOSS: Volume rose but lost overall revenue'
+      WHEN volume_change <= 0
+        THEN 'FAILURE: No demand increase observed'
+      ELSE 'NEUTRAL'
+    END
+(
+  PromoSummary
+)
+
+τ promotion_start_date ↓, net_revenue_impact ↓ (Result)
 }}}
 
@@ -480 +506 @@
 {{{
 UserPurchaseMetrics <-
-γ user_id;
-  total_orders_placed := COUNT(order_id),
-  lifetime_points_earned := SUM(points_earned),
-  lifetime_points_burned := SUM(points_used),
-  total_monetary_spend := SUM(op.quantity * op.price_at_purchase)
-(
-  σ o.purchase_date >= NOW - 2 years
+γ o.user_id;
+  total_orders_placed := COUNT(o.order_id),
+  lifetime_points_earned := COALESCE(SUM(o.points_earned), 0),
+  lifetime_points_burned := COALESCE(SUM(o.points_used), 0),
+  total_monetary_spend := COALESCE(SUM(op.quantity * op.price_at_purchase), 0.00)
+(
+  σ o.purchase_date >= CURRENT_DATE - 2 years
     ∧ o.status ∈ {PAID, SHIPPED, DELIVERED}
   (
-    ORDERS o ⟕ ORDER_PRODUCTS op
+    ORDERS o ⟕ (o.order_id = op.order_id) ORDER_PRODUCTS op
   )
 )
@@ -500 +526 @@
   registration_date := u.date_created,
   orders_count_2yr := COALESCE(um.total_orders_placed, 0),
-  lifetime_spend_amount := COALESCE(um.total_monetary_spend, 0),
+  lifetime_spend_amount := ROUND(COALESCE(um.total_monetary_spend, 0.00), 2),
   current_unspent_points_balance := c.points_collected,
   points_earned_2yr := COALESCE(um.lifetime_points_earned, 0),
   points_burned_2yr := COALESCE(um.lifetime_points_burned, 0)
 (
-  CONSUMERS c ⨝ USERS u ⟕ UserPurchaseMetrics um
+  σ u.date_created >= CURRENT_DATE - 2 years
+  (
+    (CONSUMERS c ⨝ (c.user_id = u.user_id) USERS u)
+    ⟕ (c.user_id = um.user_id) UserPurchaseMetrics um
+  )
 )
 
@@ -521 +551 @@
 
 Result <-
-τ account_creation_vintage ↓, current_unspent_points_balance ↓
-(
-  π user_id, username, email,
-    account_creation_vintage,
-    registration_date,
-    orders_count_2yr,
-    lifetime_spend_amount,
-    current_unspent_points_balance,
-    points_earned_2yr,
-    points_burned_2yr,
-    customer_retention_segment :=
-      CASE
-        WHEN is_vip = 1 THEN 'VIP'
-        WHEN is_hoarder = 1 THEN 'HOARDER'
-        WHEN is_churned = 1 THEN 'CHURNED'
-        ELSE 'STANDARD'
-      END
-  (
-    UserFlags
-  )
-)
-}}}
-
-
+π user_id,
+  username,
+  email,
+  account_creation_vintage,
+  registration_date,
+  orders_count_2yr,
+  lifetime_spend_amount,
+  current_unspent_points_balance,
+  points_earned_2yr,
+  points_burned_2yr,
+  customer_retention_segment :=
+    CASE
+      WHEN is_vip = 1 THEN 'VIP Tier: High Future Discount Liability'
+      WHEN is_hoarder = 1 THEN 'Points Hoarder: Inactive Burn (High Risk)'
+      WHEN is_churned = 1 THEN 'Churned After Reward Use (No Retention)'
+      ELSE 'Standard Active Engagement Profile'
+    END
+(
+  UserFlags
+)
+
+τ account_creation_vintage ↓, current_unspent_points_balance ↓ (Result)
+}}}
+
+