ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
Before you proceed, DON'T TRY TO EDIT OR REQUEST ACCESS TO THIS SHEET. Click on "File" > "Make a copy" and edit the new version.
2
3
Thank you for requesting the Customer 30-Day Cohorts (LTV) template! 🎉
4
Please make sure you follow the instructions below carefully. For any questions, please reach out to @almeidalexandr on Twitter
5
6
See an example LTV document with dummy data here: https://docs.google.com/spreadsheets/d/17tgYEa5L96PB3ziwnR3JdW-1DIZsiEnt/edit#gid=1304651689
7
8
WHAT YOU'LL NEED
9
1. Total orders/purchases broken down by day
10
2. A unique identifier such as an email, customer name, or customer/user ID (email preferred)
11
3. Revenue for each unique order
12
13
BEFORE YOU BEGIN
14
This spreadsheet is automatically formated to include data for up to 12 months exactly. If you add more than 12 months (365 days) worth of data, some cells might break.
15
If you're experienced with Excel/Google Sheets, you might be able to easily fix this.
16
If you're not able to fix this yourself and want to look at your data on a longer time frame (2 years; 3 years, etc) feel free to reach out!
17
18
INSTRUCTIONS
19
1. Download the data from the last 12 months from your preferred data sources such as Shopify, WooCommerce, or Google Analytics.
20
2. In your downloaded source data file, sort your "date" column by "A-Z" (oldest to newest orders). Make sure dates are formatted to include only day, month, and year. (correct format: dd/mm/yy or mm/dd/yy)
21
3. Copy the date column and paste it into column A in tab "2. Data HERE" in the same order.
22
4. Copy the unique customer identifier (email, name, ID) into column B in the tab "2. Data HERE" in the same order.
23
5. Copy the order revenue from your data file and paste them in into column D in the same order. Make sure these values are formated as numbers and don't include currency symbols ($, €, ...)
24
7. Make sure all cells highlighted in green (columns C, F, G, H, I, J) have been filled out until your last row of data (simply 'drag' the formulas down to the last cell)
25
8. Sort column B "Client ID" by "A-Z"
26
9. Your results will automatically show up on tab "3. Customer Lifetime Value Cohorts"
27
28
PS: If you're having issues, see an example LTV document with dummy data here: https://docs.google.com/spreadsheets/d/17tgYEa5L96PB3ziwnR3JdW-1DIZsiEnt/edit#gid=1304651689
29
30
DO NOT EDIT ANY CELLS/ROWS/COLUMNS HIGHLIGHTED IN GREEN.
31
32
Enjoy!
33
34
Follow me on Twitter!
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