Advertisement
Guest User

Untitled

a guest
Mar 19th, 2021
317
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*Revenue / TotalCustomers / MRR / ARPU / ChurnRate / LTV by Country Calculation*/
  2.  
  3. SELECT arpu.paymentBillingCountry, AllTime2.AllTimeTotalRevenue, AllTime2.AllTimeTotalCustomers, arpu.Total_Current_Customers, arpu.CurrentMRR, AllTime2.AllTimeARPU, (arpu.CurrentMRR/arpu.Total_Current_Customers) AS CurrentAvgMRR_ARPU, ROUND(churn.ChurnedMRR) AS AlltimeChurnedMRR, churn.ChurnedCustomers as AllTimeChurnedCustomers, (churn.ChurnedCustomers / AllTime2.AllTimeTotalCustomers) AS ChurnRate, avgLT.LT AS LifeTime, ULT1.AvgLifeTimeByCountry AS LifeTime2, (avgLT.LT * (arpu.CurrentMRR/arpu.Total_Current_Customers)) AS LTV, (ULT1.AvgLifeTimeByCountry * (arpu.CurrentMRR/arpu.Total_Current_Customers)) AS LTV2, (AllTime2.AllTimeARPU / (churn.ChurnedCustomers / AllTime2.AllTimeTotalCustomers)) AS LTV3
  4. FROM
  5.  
  6. /*Alltime ARPU by Country calculation*/
  7. (SELECT paymentBillingCountry, count(DISTINCT userID) Total_Current_Customers, ROUND(SUM(paymentAmount / paymentPeriod)) AS CurrentMRR
  8. FROM payments
  9. WHERE paymentDateTime < CURDATE() AND DATE_ADD(paymentDateTime, INTERVAL paymentPeriod MONTH) >= CURDATE() AND paymentStatus = 1 and paymentPeriod > 0 AND userID IS NOT NULL
  10. GROUP BY paymentBillingCountry
  11. ORDER BY CurrentMRR DESC) as arpu
  12.  
  13. LEFT JOIN
  14.  
  15. /*Alltime Churned MRR & Users by Country*/
  16. (SELECT paymentBillingCountry, sum(paymentAmount/paymentPeriod) as ChurnedMRR, count(DISTINCT p1.userID) as ChurnedCustomers
  17. FROM payments p1 LEFT JOIN
  18.  
  19. (select userID, MAX(DATE_ADD(paymentDateTime, INTERVAL paymentPeriod MONTH)) AS max
  20. FROM payments
  21. WHERE paymentStatus = 1 AND paymentPeriod > 0 AND userID IS NOT NULL
  22. GROUP BY userID
  23. HAVING max < CURDATE()) AS p2
  24.  
  25. ON (p1.userID = p2.userID and DATE_ADD(paymentDateTime, INTERVAL paymentPeriod MONTH) = p2.max)
  26. WHERE p2.userID IS NOT NULL
  27.  
  28. GROUP BY paymentBillingCountry) as churn
  29.  
  30. on arpu.paymentBillingCountry = churn.paymentBillingCountry
  31.  
  32. LEFT JOIN
  33.  
  34. /*AVG LifeTime by Country based on MAX - MIN Last Payment + PaymentPeriod)*/
  35. (SELECT paymentBillingCountry, AVG(LT.LifeTime) AS LT
  36. FROM
  37. /*LifeTime last Last - First Payment Difference by User ID with PaymentBilling Country */
  38.  
  39. (SELECT MinP.userID, MinP.paymentBillingCountry, MIN(MinP.paymentDateTime), MAX(MinP.paymentDateTime), ((DATEDIFF(MAX(MinP.paymentDateTime), MIN(MinP.paymentDateTime))/ 30.5) + LP.paymentPeriod) AS LifeTime
  40.     FROM payments MinP
  41.     LEFT JOIN
  42.  
  43. /*last payment with Payment Period*/
  44. (select p1.userID, p1.paymentBillingCountry, DATE_FORMAT(p1.paymentDateTime, '%d/%m/%Y') as paymentDateTime, p1.paymentPeriod
  45. from payments p1
  46. left join payments p2 on (p1.userID = p2.userID and p1.paymentDateTime < p2.paymentDateTime AND p2.paymentType = 2)
  47. where p2.paymentDateTime IS NULL AND p1.paymentDateTime IS NOT NULL AND p1.paymentPeriod > 0
  48. group by p1.userID
  49. order by p1.paymentID DESC) LP
  50.  
  51. on MinP.userID = LP.userID
  52.  
  53. GROUP BY MinP.userID) LT
  54.  
  55. GROUP BY paymentBillingCountry
  56. ORDER BY paymentBillingCountry) avgLT
  57.  
  58. on avgLT.paymentBillingCountry = arpu.paymentBillingCountry
  59.  
  60. LEFT JOIN
  61.  
  62. /*AVG LifeTime by Country based on SUM of PaymentPeriod)*/
  63.  
  64. (SELECT ULT.paymentBillingCountry, AVG(ULT.UserLifeTime) AvgLifeTimeByCountry
  65. FROM
  66.     (SELECT userID, SUM(paymentPeriod) UserLifeTime, paymentBillingCountry
  67.     FROM payments
  68.     GROUP BY userID
  69.     ORDER BY userID) ULT
  70. GROUP BY paymentBillingCountry) ULT1
  71.  
  72. on ULT1.paymentBillingCountry = arpu.paymentBillingCountry
  73.  
  74. LEFT JOIN
  75.  
  76. /* AllTime Total Revenue, Customers & ARPU by Country*/
  77. (SELECT AllTime.paymentBillingCountry, AllTime.AllTimeTotalRevenue, AllTime.AllTimeTotalCustomers, (AllTime.AllTimeTotalRevenue / AllTime.AllTimeTotalCustomers) as AllTimeARPU
  78. FROM(
  79.  
  80. SELECT TotalUserPaymentAmount.paymentBillingCountry, SUM(TotalUserPaymentAmount.paymentAmount) as AllTimeTotalRevenue, count(DISTINCT userID) AllTimeTotalCustomers
  81. FROM
  82. (SELECT userID, SUM(paymentAmount) as paymentAmount, paymentBillingCountry
  83. FROM payments
  84. GROUP BY userID) as TotalUserPaymentAmount
  85.  
  86. GROUP BY TotalUserPaymentAmount.paymentBillingCountry) AllTime) AllTime2
  87.  
  88. on AllTime2.paymentBillingCountry = arpu.paymentBillingCountry
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement