forked from CodebuffAI/codebuff
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcalculate-average-spend.ts
More file actions
142 lines (123 loc) · 4.7 KB
/
Copy pathcalculate-average-spend.ts
File metadata and controls
142 lines (123 loc) · 4.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
import { stripeServer } from '@codebuff/internal/util/stripe'
import type Stripe from 'stripe'
async function calculateAverageSpend() {
console.log('Calculating spend per subscriber...')
let hasMore = true
let startingAfter: string | undefined = undefined
let totalSpend = 0
let customerSpends = new Map<string, number>()
let customerFirstInvoiceDates = new Map<string, number>()
// batchCount was previously used for debugging but is no longer needed
try {
// Get all invoices from the last 2 months to establish customer history
const twoMonthsAgo = Date.now() - 1000 * 60 * 60 * 24 * 60
const oneMonthAgo = Date.now() - 1000 * 60 * 60 * 24 * 30
// First get all invoices to establish customer history
while (hasMore) {
// batch processing iteration
const invoices: Stripe.Response<Stripe.ApiList<Stripe.Invoice>> =
await stripeServer.invoices.list({
starting_after: startingAfter,
created: {
gte: Math.floor(twoMonthsAgo / 1000),
},
status: 'paid' as Stripe.Invoice.Status,
limit: 100,
})
// Process each invoice
for (const invoice of invoices.data) {
if (!invoice.customer) continue
const customerId =
typeof invoice.customer === 'string'
? invoice.customer
: invoice.customer.id
// Track customer's first invoice date
const currentFirstDate =
customerFirstInvoiceDates.get(customerId) || Infinity
const invoiceDate = invoice.created
customerFirstInvoiceDates.set(
customerId,
Math.min(currentFirstDate, invoiceDate),
)
const currentSpend = customerSpends.get(customerId) || 0
// Only count spend from last month
if (invoice.created >= Math.floor(oneMonthAgo / 1000)) {
customerSpends.set(customerId, currentSpend + invoice.amount_paid)
}
}
hasMore = invoices.has_more
if (hasMore && invoices.data.length > 0) {
startingAfter = invoices.data[invoices.data.length - 1].id
}
}
// Calculate total spend and identify new vs existing customers
totalSpend = 0
let newCustomerSpend = 0
let existingCustomerSpend = 0
let newCustomers = 0
let existingCustomers = 0
for (const [customerId, spend] of customerSpends.entries()) {
totalSpend += spend
const firstInvoiceDate = customerFirstInvoiceDates.get(customerId)
if (
firstInvoiceDate &&
firstInvoiceDate >= Math.floor(oneMonthAgo / 1000)
) {
newCustomerSpend += spend
newCustomers++
} else {
existingCustomerSpend += spend
existingCustomers++
}
}
// Convert from cents to dollars
const totalCustomers = customerSpends.size
console.log(
`Total unique customers found: ${totalCustomers} (${newCustomers} new, ${existingCustomers} existing)`,
)
console.log(`Total monthly spend: $${(totalSpend / 100).toFixed(2)}`)
console.log(` New customers: $${(newCustomerSpend / 100).toFixed(2)}`)
console.log(
` Existing customers: $${(existingCustomerSpend / 100).toFixed(2)}`,
)
console.log(`Note: Only includes paid invoices`)
if (existingCustomers > 0) {
console.log(
`Average spend per existing customer: $${(existingCustomerSpend / (existingCustomers * 100)).toFixed(2)}`,
)
}
// Print distribution of spend
console.log('\nSpend distribution:')
const spendRanges = new Map<string, number>()
for (const spend of customerSpends.values()) {
const spendInDollars = spend / 100
if (spendInDollars <= 50)
spendRanges.set('$0-50', (spendRanges.get('$0-50') || 0) + 1)
else if (spendInDollars <= 100)
spendRanges.set('$51-100', (spendRanges.get('$51-100') || 0) + 1)
else if (spendInDollars <= 500)
spendRanges.set('$101-500', (spendRanges.get('$101-500') || 0) + 1)
else spendRanges.set('$500+', (spendRanges.get('$500+') || 0) + 1)
}
// Print spend ranges in decreasing order
const sortedRanges = Array.from(spendRanges.entries()).sort(
(a, b) => b[1] - a[1],
)
for (const [range, count] of sortedRanges) {
console.log(`${range}: ${count} customers`)
}
// Print individual overages in decreasing order
console.log('\nTop spenders:')
const sortedSpends = Array.from(customerSpends.values())
.map((spend) => spend / 100)
.filter((spend) => spend > 49)
.sort((a, b) => b - a)
for (const spend of sortedSpends) {
console.log(`$${spend.toFixed(2)}`)
}
} catch (error) {
console.error('Error calculating average spend:', error)
}
}
// Run the script
calculateAverageSpend()