📈 2021 Year Review
My annual review is the result of wanting to create a page that displays all analytics from online projects in the last twelve months. There are two core Notion databases here. One is for a monthly review where all data is gathered, and the other is for three main areas to analyze: my blog, Twitter, and YouTube. Here is how I used the data from each monthly review to create a 2021 review.
** Template available below **
1. The Monthly Review DAtabase
I collected information to add to a monthly review database. As the months went by, I gathered data to input from my blog, Twitter, and YouTube into each entry from January to December. Here is the data I collected:
You’ll notice some relation properties indicated by an arrow symbol. Relation properties allowed me to connect one database to another. Here are the connections:
Blog Google Search: connection to a Keyword database
Y.T Search: connection to the same Keyword database
Areas: connection to an Areas database
The Areas database is being used to calculate the data from my Monthly Review database.
2. Sort Monthly Review Database
In the Monthly Review database I created two formula properties named Month and Year to grab the title of each entry and extract the month number and the year from this syntax:
Next, I sorted the Year Ascending and Month Ascending in all views.
Month Formula
if(contains(prop("Name"), "Jan"), 1, if(contains(prop("Name"), "Feb"), 2, if(contains(prop("Name"), "Mar"), 3, if(contains(prop("Name"), "Apr"), 4, if(contains(prop("Name"), "May"), 5, if(contains(prop("Name"), "Jun"), 6, if(contains(prop("Name"), "Jul"), 7, if(contains(prop("Name"), "Aug"), 8, if(contains(prop("Name"), "Sep"), 9, if(contains(prop("Name"), "Oct"), 10, if(contains(prop("Name"), "Nov"), 11, if(contains(prop("Name"), "Dec"), 12, toNumber("")))))))))))))
Year Formula
toNumber(replaceAll(prop("Name"), ".* ", ""))
3. Connect Monthly Review To Areas
The Areas database has three entries; Website, Twitter, and YouTube.
I connected every monthly review to the Areas database by adding this filter to every view in the Monthly Review database:
Inside the Areas database, a backlink of all connected months appears. It is important to make sure this window is ordered from earliest to most recent month.
4. Compare Monthly Reviews
Next, I use the connection between Monthly Review and Areas to calculate the progression between the latest month and the month prior. For example, I increased 265 more YouTube subscribers in December than the total subscribers gained in November. This calculation will occur automatically.
For each Area, I wanted to see the following calculations between the latest month and the month prior:
Blog Trends
Change in monthly visits
Change in monthly pageviews
Twitter Trends
Change in monthly followers
Change in monthly tweets
YouTube Trends
Change in monthly views
Change in monthly subscribers
To do this I created a Rollup property for each trend described above. That’s six in total that look like this:
For a rollup to operate properly, I needed at least one Relation property. The relation property here is coming from the month entries in Monthly Review.
A Rollup in Notion is a property that acts like a window into the database it is connected to. For example, I can now grab all data from properties inside the Monthly Review database and use it inside the Areas database like here:
All rollups grab the corresponding data from Monthly Review, and the Calculation is always “Show Original.” This allows for the extraction of the last 2 months’ data.
Next, I created six formulas for each trend that grabs the numbers from only the last two months and finds the difference between them.
The Formula Syntax
If you want to add more trend formulas, replace all instances of prop("Rollup Name") with the name of the corresponding rollup property. For instance, my Visit Trend formula is communicating with my Visits (hide) rollup property.
if(not empty(prop("Rollup Name")), if(toNumber(replace(prop("Rollup Name"), ".*[,]", "")) - toNumber(replace(replace(prop("Rollup Name"), ",(?:.(?!,))+$", ""), ".*[,]", "")) >= 0, "🟩🟩🟩🟩🟩🟩🟩🟩🟩🟩 +" + format(toNumber(replace(prop("Rollup Name"), ".*[,]", "")) - toNumber(replace(replace(prop("Rollup Name"), ",(?:.(?!,))+$", ""), ".*[,]", ""))) + " Visits", "🟥🟥🟥🟥🟥🟥🟥🟥🟥🟥 " + format(toNumber(replace(prop("Rollup Name"), ".*[,]", "")) - toNumber(replace(replace(prop("Rollup Name"), ",(?:.(?!,))+$", ""), ".*[,]", ""))) + " Visits"), "")
5. My Keyword Database
I have a second connection in my Monthly Review database to a Keyword Database. This is a list of search terms from Google that redirect to my website or YouTube videos.
For every monthly review, I record the top keywords via these two relation properties in the Monthly Review database:
There are 6 formulas in the Keyword database that grabb data provided by the backlink created from the Monthly Review relation here:
The first 2 formula properties return a checkbox that indicate whether the keyword directs to my blog or YouTube (or both).
To Blog Formula
not empty(prop("Key Blog Months"))
To YouTube Formula
not empty(prop("Key YouTube Months"))
There are 4 more formulas in the Keyword database that determine if the keyword was popular in a particular Quarter of the year from 1-4.
Quarter 1 Formula
or(contains(prop("Key Blog Months"), "January"), contains(prop("Key YouTube Months"), "January")) or or(contains(prop("Key Blog Months"), "February"), contains(prop("Key YouTube Months"), "February")) or or(contains(prop("Key Blog Months"), "March"), contains(prop("Key YouTube Months"), "March"))
Quarter 2 Formula
or(contains(prop("Key Blog Months"), "April"), contains(prop("Key YouTube Months"), "April")) or or(contains(prop("Key Blog Months"), "May"), contains(prop("Key YouTube Months"), "May")) or or(contains(prop("Key Blog Months"), "June"), contains(prop("Key YouTube Months"), "June"))
Quarter 3 Formula
or(contains(prop("Key Blog Months"), "July"), contains(prop("Key YouTube Months"), "July")) or or(contains(prop("Key Blog Months"), "August"), contains(prop("Key YouTube Months"), "August")) or or(contains(prop("Key Blog Months"), "September"), contains(prop("Key YouTube Months"), "September"))
Quarter 4 Formula
or(contains(prop("Key Blog Months"), "October"), contains(prop("Key YouTube Months"), "October")) or or(contains(prop("Key Blog Months"), "November"), contains(prop("Key YouTube Months"), "November")) or or(contains(prop("Key Blog Months"), "December"), contains(prop("Key YouTube Months"), "December"))