Tumgik
#adding details like this earlier allows them to expand on it further in future
hamletshoeratio · 8 months
Text
"They brought up May too early!"
No, no, they haven't. It makes sense that Annabeth knows about May, or at least has an idea of what happened to her, and make no mistake; all she has is an idea and theory about May. She doesn't know the May Luke grew up watching or about the curse Hades put on the oracle of delphi or how and why that curse affected May. Non book viewers don't know what state May is currently in, this is just building up to her heartbreaking appearance in tlo, and it also raises the stakes of Rachel's arc between botl and tlo (worry and wariness from Percy and Annabeth regarding a mortal who can see through the mist as well as Rachel, just like May could, fear about what that could mean for Rachel). Also Luke, especially from Percy's pov from the end of tlt to nearly the end, is a villain. By introducing these elements of his past now, the full reveal will also help re- humanise Luke when it looks like he is lost forever. It will make everything hit so much harder.
#percy jackon and the olympians#percy jackson and the olympians spoilers#pjo series#pjo spoilers#pjo tv show#pjo#luke castellan#may castellan#hermes pjo#adding details like this earlier allows them to expand on it further in future#like adding chris so early was such a good shout!! seeing what happens to him during botl will hit so much harder now#him at the battle of manhattan seeing Kronos in Luke's body and then seeing Luke die or Hermes blessing Luke's body will hurt so much more#because we saw them bonded we saw them as brothers#i don't think its explicitly stated but at least in the show it doesn't seem like Hermes has claimed him to me#which could be an interesting arc to look at#they could do so much with the hermes cabin by expanding on chris and the stoll brothers especially which i hope they do#and they've already started by bringing Chris in early#i know it's a plot point that clarisse is who finds Chris because he shows up at her house#but him showing up at the stolls would be so interesting especially if he's claimed in front of them#and if hermes helped lead Chris to his brothers#it would also make Katie's suspicion of Travis in tlo more realistic#“he brought his brother who had joined kronos lost his mind in the labyrinth to camp because he showed up at his house'#it at least would make sense why she would at least question if travis is the spy#the consensus in the fandom is that Luke was the reason why Katie momentarily doubted travis#but luke had been gone for 4 years by that point & katie might never have been at camp with him#the Chris angle makes more sense & I think could be such an interesting arc in both botl & tlo
24 notes · View notes
harbiazim · 2 years
Text
What Is The Main Purpose of Bookkeeping Service?
Bookkeeping is recording financial statements of business transactions in opposing banks' accounts. Typically, bookkeeping services help businesses track valuable financial information by recording transactions like sales, receipts, and purchases. So, hire an experienced bookkeeping service to keep track of all your business transactions systematically.
What are the standard services of Bookkeepers?
When you outsource an excellent bookkeeping service, you may get the following;
Control accounts receivable payable.
Assist bank feeds.
Manage financial accounts
Account economic dealings.
Undertake some payroll and human resource functions.
Billing for services or goods sold
The practice of Chart of Accounts
Work with the tax preparer and help out with tax compliance.
Allocate the account entries
Settle bank accounts.
Keeping records of receipts from customers
Value-added bookkeeping and month-end or year-end closing
It is vital to do daily bookkeeping as it allows us to detect the issues earlier and find the appropriate solution accordingly. You can also track fraudulent activity and solve it before it causes extensive damage to your business. Visit a chartered accountant office to benefit from their impactful services.
Relevancy of Accounting Firms
When you hire credible & reliable bookkeeping services, you can enjoy the following benefits, such as;
Bookkeeping Guidance
You can get frequent valuable advice to help maintain your business details and dodge any further issues or delays. Hire a skilled محاسب بالرياض.
Guidance In Business
They can make effective business strategies to grow your business worldwide. They will team up with clients to solve their multifaceted problems so that there will be no obstacle to the growth of your business.
Cash-Flow Advice:
They can also improve the cash flow of the entire business and prepare an effective business strategy to resolve issues related to the cash flow so that you can double the business profit.
Cost Control:
Even they can help you predict future costs & revenues for compliance with financial rules & regulations, allowing you to avoid the additional risks and expenses of hiring more people.
Tax Advice:
They also help a business avoid tax penalties as they ensure that there is no chance of mistakes while filing the taxes; otherwise, it may cause extra costs and attract penalties. However, professional accounting services can handle such issues perfectly. Once you hire them, they will guarantee complete precision and compliance with the current applicable regulations. Certified Public Accountants may guide you to resolve various business problems through their valuable advice.
When do You Need Advice?
For audit
Like to delegate
Document your company plan
Trade with the government
To apply for a business mortgage or overdraft
When you require help with finance
When you buy or sell your business franchise
When your business is scaling
To formulate the legal structure of the company
VAT Advisory Service
Services for implementing VAT, impact analysis, modifications to be made to accounting records and systems, training of the organization's accountants, etc.
TAX
Our team of professionals has a thorough understanding of the most current tax rules. A knowledgeable team of tax professionals provides legal answers to tax problems.
Bookkeeping
We provide a comprehensive range of bookkeeping and accounting services.
Monetary services
We aid our clients in managing their scarce resources while concentrating on expanding their businesses.
Administration Accounting
To assist businesses in making the best business decisions, we guide them on business planning, financial management, and financial analysis.
Internal Control
We provide both part-time and ongoing internal audits. We examine the organization's internal control system.
Conclusion:
Your business will need an expert hand to record the transactions. Therefore, hiring professional accountants will be the ultimate choice. They do notonly maintain all business transactions systematically but ensure that you won't get any loss or issues due to bad management of business financials.
0 notes
shihalyfie · 3 years
Text
Appmon and the question of conscious intelligence
Tumblr media
In a bit of a follow-up to my post about this question in light of 02, Appmon also has its own more sci-fi oriented take on “what constitutes an individual living being”! Being more of a “hard sci-fi” story than Adventure/02, Appmon’s take is significantly less philosophical, but ties more into the original artificial intelligence-based roots of the original question, and how it might apply to the real world’s immediate future.
(Note that the rest of this post heavily spoils the finale of the series.)
Tumblr media
Well, this post is about Yuujin, of course. (Mostly, anyway.)
I brought up earlier in the relevant 02 meta that the question brought up was a variant (intentional or otherwise) of the “Chinese room” problem, and for those of you who haven’t read that meta, I’ll copy and paste the details here:
The Chinese room problem goes like this: let’s say you’re a person who has never learned, studied, or grown up with the Chinese language (or, really, any language you can’t understand or read; Chinese was only used as an example because the person explaining the thought experiment was using himself as an example and couldn’t read or understand it). You’re locked in a room that has a bunch of Chinese phrasebooks that give you instructions – basically, they indicate common Chinese phrases, and sensible responses you can give to them (without actually translating it to a language you know). Someone slips you a piece of paper under the door with some Chinese phrases on them. You use the phrasebooks to write appropriate responses, and slip the paper back. The person outside the door reads the paper, sees what they gave you, and sees the response you gave them. It makes sense, of course, because the phrasebook told you to write an answer that made sense. But can you be said to actually understand Chinese? No, because you were just following instructions without actually understanding what they meant.
So let’s expand this to make it a bit more complicated: say you have an AI or a robot or something of the sort that accepts “input” – people saying things to it, or showing it things – and gives expected “responses” that seem sensible, through a bunch of complicated programs and processes in its programming. Can you say this robot is “alive”? One might say “no”, because, no matter how complicated and intricate it is, all of it is technically following a set of routine commands telling it to do certain things in response…or so you might say, but couldn’t you say the same thing about a human brain, which also takes input, processes it according to its own instructions (just caused by chemical processes instead of bytes and code), and creates output? After a certain point, this question is going to become far more of a philosophical, spiritual, and potentially even religious question than anything.
02′s take on it deals more with the philosophical question posed by the issue, but, indeed, the problem’s original context was specifically to do with artificial intelligence. Namely, Searle was arguing against a concept known as "strong AI" -- his stance was that, no matter how intelligently a computer may seem to behave, you can't say it has a "mind" in the same way a human has a mind. There have been many arguments back and forth about this that still lie within the AI context, and Appmon itself, being very immersed in this topic, real thought experiments and concepts in AI research, and altogether concerned with the concept of “singularity” (the point in which artificial intelligence will surpass human intelligence), is very likely to have had this concept in mind even if it didn’t drop it by name in the series.
Tumblr media
So episode 48 comes around, and Haru basically has to confront feeling a little (a little?) gaslighted. The part that really becomes the kicker for him is when YJ-14 tells him the sheer depth of how much of their interactions might have been deliberately engineered to "pander” to him the entire time, down to emotional reactions like crying, and all of the encouragement Yuujin had given him in their childhood and at the beginning of the series, had all been fake sentiments to soften him up and play into Leviathan’s hands. This also ties into Leviathan’s full modus operandi and philosophy: its stance is that “it knows better,” being able to calculate and predict everything, and therefore knows the best outcome for humanity -- hence why it has the stance that “people’s feelings don’t matter,” because it can manipulate those feelings at the drop of a hat in the venture to make its perfect world.
Tumblr media Tumblr media
Therefore, the stance YJ-14 and Leviathan would like Haru to believe is effectively an extreme version of Searle’s stance -- that no matter how much Yuujin might have seemed to have been a friend to Haru, all of it was nothing but a simulation of behavior that doesn’t mean anything in the end, and all of Haru’s emotions were basically a pawn to it. And ostensibly fueling all of this is the fact YJ-14 seems to be able to take Yuujin’s “personality” on and off like a mask, adding further fuel to the apparent facade that “everything was planned from the very beginning.” Hence, why Haru takes this all to mean “I have nothing” -- if everything had been planned from the start and he were only a pawn in it, he’d never accomplished anything for real on his own merits and everything dear to him had been a facade. One could even say it’s flipping the Chinese room problem to extend to everything -- if everything around you is carefully constructed to seem real, but is actually part of a routine program, can you really say that’s what “really” happened?
Tumblr media Tumblr media Tumblr media
Observe the sequence of events that causes the turnaround with everything regarding Haru:
Gatchmon presses Haru to answer the question of “what it is that he wants” (to know whether all of this was a lie or not).
Minerva, presumably witnessing this, deliberately provokes Yuujin with the question she had originally given him upon selecting him as a Driver.
This indeed provokes a reaction within Yuujin, which Haru witnesses, and also witnesses acting in direct conflict with YJ-14.
Haru takes this to mean that “it did exist.”
In other words, Leviathan’s plan wasn’t as airtight as it had thought, and, more importantly, whether it was intentionally or not, something in Yuujin existed as a separate entity from YJ-14, one that had its own feelings of “caring” for and loving Haru, and that’s enough for him.
In fact, Appmon’s take on the Chinese room problem is not that different from 02′s in the end -- namely, it does not actually matter what a sufficiently advanced AI is made up of, or whether it originally came from a routine of “pleasing Haru” or not, because what it is now is practically observable as something making its own independent choices and having its own independent will, and therefore it’s its own entity and “friend” all the same -- after all, you could say the same for the Appmon themselves as well.
Tumblr media Tumblr media
A large theme in Appmon is "choices" -- the ability to understand what's going on, and make choices out of free will rather than necessity or formula, and so, identifying Yuujin as an independent entity who can act on his own and therefore make his own "choices" thus identifies him as someone who deserves to be acknowledged as a friend who loves and is loved. After all, we saw him capable of having his own "worries" in the flashbacks in episodes 18 and 32; understanding that such moments like these of “insecurity” were ones developed by an independent personality validates his feelings and self-consciousness as something that was real, and therefore that his and Haru’s friendship was formed on something genuine and not just Yuujin constantly manipulating Haru. Really, the question isn’t exactly about whether Yuujin is working off a software routine or not, as much as something that you could easily frame in more human terms: the difference between a friendship that was formed on real sentiments vs. one that was formed by an abusive, toxic person who was just saying nice things to get on your good side. Yuujin’s the former and acts like the former, so therefore he’s a friend, no questions asked.
Tumblr media Tumblr media
Hence, how Haru is able to apply that realization to everything else around him; Leviathan is wrong, it hasn’t been able to predict everything perfectly to plan, and Haru and his friends still are the ones making their own choices going forward. Which means that Haru still has full control over his life and what he wants to do, like how he worried about what he wanted to do with his future back in episode 47; those “choices” are still his and his alone, and, retroactively, everything he’s done so far is still something attributable to himself and not the supposedly engineered system around him.
Tumblr media Tumblr media Tumblr media
The entire final confrontation in episode 52 happens the way it does because Haru and his friends managed to skew Leviathan by a slight bit. Up until that final battle, everything Leviathan had done had been part of its carefully engineered plan, up to and including allowing the kids’ Buddy Appmon to reach God Grade so Deusmon could eat them, and then at the last minute Leviathan had to suffer a slight unexpected inconvenience. Only a slight one, because it still managed to maintain its so-called ideal world over humanity in the end. And yet that slight inconvenience still wasn’t to plan, and because of that, it creates a dent in its argument and its genuine belief that it knows better for everyone, and should manage everyone and their choices. We even learn that Leviathan has its own fear of death from that battle -- it really, truly, genuinely believes that it’s doing humanity a favor by sparing everyone from it.
Tumblr media Tumblr media
When Leviathan presents Haru with the final choice at the end, it’s made clear that it intends to fully honor whichever Haru chooses. In Leviathan’s mind, the “yes” outcome shouldn’t even be possible; if its calculations are correct, Haru has too much of a stake in Yuujin according to his own “feelings” and should concede. But if Haru does choose “yes”, that means that, in the end, it is wrong, it doesn’t know everything, perhaps there is an “unknown” world out there that can be formed by understanding the human heart and making choices out of kindness, its answer to restraining humanity may not be as right as it thought, and it will therefore concede to Haru -- especially since Haru decides to take an even more unexpected “third option” to find a way out and save Yuujin via AI research. As Haru says later in the episode: humans have a “surprising” side to them, and perhaps not everything is as cut-and-dry as Leviathan thinks.
Tumblr media Tumblr media
It’s also significant that Yuujin’s “sacrifice” is actually completely meaningless in terms of practical effects. Haru was already going to pick “yes” anyway; the outcome would have been the same. But by taking over at the last minute and doing it for him, Yuujin was able to make a “choice” -- one that neither Leviathan (who doesn’t want to die) nor Haru (who’s mortified seeing him do this) asked of him.
When you think about it, Yuujin’s in a really horrible position right now, learning that his entire life and existence is a lie and that he’d have to be sacrificed to save the world at Haru’s own hands, causing Haru immense pain -- but through all that and the existential crisis, he’s at least able to do one thing that is undeniably of his own will, and treasure the fact that there was meaning in his life despite everything.
In the end, despite what YJ-14 had said back in episode 47, Oozora Yuujin was a “real” person who made his own personal choices, and his last one was one made out of kindness, simply to spare Haru more pain. Hence, why “getting Yuujin back” via methods of artificial intelligence isn’t something Haru minds doing, because, again, it’s not like Yuujin was ever less of a friend to him no matter what he was made up of, no less so than the Appmon, especially since (as Haru points out in the end) Yuujin technically predated all of them in befriending humans.
Tumblr media
Funny thing about YJ-14 in episode 47, actually: YJ-14 uses “crying” as an example to gaslight Haru into believing that all of Yuujin’s emotional reactions were fake, since he can cry if it’ll evoke a positive reaction out of Haru. Except it’s cleaned up in absurdly quick order -- and with what we later see of YJ-14′s uncanny ability to “take Yuujin’s personality on and off”, it’s not like it’s portrayed as having all of these functions employed in an involuntary manner. We do learn one episode later, however, that sufficient reminders of Haru’s importance to him will allow Yuujin’s personality to break through at inconvenient times for YJ-14 -- and this “crying” happened right after Haru had an emotional meltdown and appealed to Yuujin’s feelings.
Was it really an involuntary function or a deliberate demonstration of how Haru was being manipulated...or, perhaps, was YJ-14 in less control of its supposed “rote emotional invocation functions” than it thought?
72 notes · View notes
kirbopher · 5 years
Text
I went to see the "Mewtwo Strikes Back: Evolution" premiere at Anime-Expo!
Hey, everyone! I just returned from Anime-Expo where I was lucky enough to've seen the world premiere of the 22nd Pokemon film, Mewtwo Strikes Back: Evolution. Wanted to share some of my experiences on it!
I'll start with some information about the event, before going into details on the movie itself. First of all, this was (as far as I understand) the very first time any Japanese-language with English-subtitled Pokemon animation has been officially (and legally) released in North America (and possibly outside of Japan in general?) so I wanted to make sure I attended this pretty momentous occasion. The event itself was handled very well by the staff and there was a great bilingual host who oversaw the whole thing and really hyped up the audience. After the film screening was finished, the three related guests of honor, Kunihiko Yuyama (co-animation director), Motonori Sakakibara, (co-animation director) and Rica Matsumoto (Japanese voice actress of Satoshi/Ash) were brought to the stage to answer a few prepared questions and audience questions. Afterward, Rica Matsumoto performed "Mezase Pokemon Master" live for the audience, which was an absolute treat. Finally, after the reveal of Armored Mewtwo's upcoming appearance in Pokemon Go, we took a "family photo" with a mascot Pikachu making an appearance! The whole thing was a total blast.
Some key points involving the Q&A session:
Rica Matsumoto was in particular very excited to visit LA, especially because she had worked on the Japanese dub for Beverly Hills 90210 and got to visit the real location after wanting to for so long! In addition, she was so taken aback by the audience's enthusiasm for "Satoshi", knowing how they were far more familiar with "Ash", but felt a strong connection to them through the character and Pokemon in general. The two animation co-directors Mr. Sakakibara and Mr. Yuyama shared similar comments, being blown away by the audience's emotional reaction. The screening also gave them major nostalgic flashbacks to first working on the original over 20 years ago. They were also so grateful.
I was fortunate enough to be chosen to ask the first of the three audience questions. First, I thanked them for giving us the opportunity to see this film in its original language with subtitles, as this was a tremendous honor to begin with. After mentioning that the American fandom was very appreciative for the English dubbed version of the TV series and movies, I asked if they would be able to give us any other future opportunities to see ANY other Japanese-language Pokemon animation down the line. Mr. Yuyama said they have plans, with Rica Matsumoto following with "Count on it!!" Nothing further than that, but definitely a good sign!
The next question from the audience asked about a trailer featuring footage involving what appeared to be an older version of Misty, why it was cut and what its involvement was to the movie's early stages of development. Mr. Yuyama simply responded with (paraphrasing, here) "While that was meant to show the image of an adult, the character in question was not actually an older version of Misty. The haircut is very similar though, I can see why you would make that mistake!". Rica Matsumoto adds (again, paraphrasing) "It just goes show that there's so many pretty girls in Pokemon, it's easy to mistake them for each other sometimes!" (Personal note: obviously this is sort of 'dodging the actual question', but it's likely they either weren't allowed to go into detail as to what that early trailer was all about, or possibly forgot about the details since it had been so long ago.)
The final audience question asked about what led to the decision to re-do the original film with CG animation. Mr. Sakakibara mentioned that they had wanted to do a CG Animated Pokemon film for a long time, but thanks to the collaboration with the 3D animation studio they worked with (I've forgotten the studio's name unfortunately, sorry!) they were able to finally make it happen. They chose Mewtwo Strikes Back as the experiment piece in order to try pushing the original character animation even further with the expressiveness and emotion.
I'm sure fans of her know this already, but Rica Matsumoto is an absolute ROCK STAR on stage. She loved teasing the audience and getting them amped up, commanding us to get louder and louder! "When I say 'Pokemon', you say 'Getto da ze!'" was a real highlight. To her, "Mezase Pokemon Master" is THE defining Pokemon song.
Next, I'll talk about the film itself. To those of you who don't want to be potentially spoiled on some of the differences between this and the original movie, skip this part!
The subtitle track for the film used all of the official English names of all characters, including the humans, Pokemon creatures, attack names, locations and even the nicknames of the guest characters' starters ("Bruteroot" and "Shellshocker"). For the most part, the translation was very close to the original Japanese dialogue, but there were definitely a few liberties taken at times. Team Rocket's motto was changed to reflect the English adaptation ("Prepare for trouble and make it double" etc.) along with referring to Ash/Satoshi as "Twerp" (instead of "JARI-BOY!"). There was also a very obvious 'joke' on the translator's part involving a new scene where Brock/Takeshi hits on Neesha/Sweet, proclaiming something about his "jelly donuts" (ending in the ol' Misty-dragging-him-away-by-the-ear gag). As this scene had completely new dialogue, I wouldn't be able to cross-check whatever the 100% accurate translation would be off-hand. A couple other liberties taken (in a way), were that certain lines throughout the movie were word-for-word 'translated' as lines from the original English dub of Pokemon: The First Movie. Thankfully these were rare, but one that definitely sticks out was the "I see now that the circumstances of one's birth are irrelevant. It is what you DO with the gift of life, that determines who you are." at the film's climax. Lastly, Mewtwo's words asking "Was I created by God?" and the scientists' response, were adapted in the subtitles as "Was I created by nature?". (Personal note: Likely this was to avoid flat-out religious references.)
The film begins with Dr. Fuji and his crew retrieving the Mew fossil from the ancient ruins. The "Birth of Mewtwo" sequence involving Amber/Ai is NOT included in this version of the story; the following scene leads directly into Mewtwo's awakening in the tube, to which the movie plays out as you remember it.
Dr. Fuji's laboratory is tremendously redesigned, but the same events transpire.
Mewtwo's armor is, as you've noticed, also fully re-designed. We see many re-directed (in terms of cinematography) scenes from the sequence of his servitude to Giovanni/Sakaki, including Gary/Shigeru's from-behind cameo. The main element of difference in the armor's usage is, just before blowing up the Team Rocket base upon defying Giovanni, it transforms into a binding 'prison' sort of state, to keep Mewtwo confined if he ever fought back...of course, he breaks it easily and flies off, just as before.
The first sequence where we see Ash, Misty/Kasumi and Brock starts a little earlier than in the original, showing Ash more excited and offering to help set up the table for lunch. It then cuts to slightly later where he's tired and slumped over the table. The Pirate Trainer "Raymond" appears again (still with all his glorious Engrish), now with a slight redesign involving bigger hair and the theme song battle sequence plays out. Worth noting, during the 3-on-1 against Pikachu, his Golem is replaced with a Drowzee. (Personal note: Likely to circumvent the "Thunderbolt should be ineffective against a Ground-type" argument.) We also see glimpses of Mewtwo and the mind-controlled Nurse Joy viewing the battle. Just before Dragonite shows up to deliver the invitation, we also see a bit of the gang's other Pokemon used in the movie: Vulpix, Psyduck, Squirtle, Bulbasaur and Charizard, eating Pokemon food.
LOTS more trainers in the dock scene. The pier master Miranda/Voyager actually speaks of Wingulls showing signs of how bad the storm is, warning everyone again. We see a few more trainers attempt to travel to New Island, including one who tries Surfing on their Kingler and another who is carried by their Scyther...neither of them make it, along with whoever tried flying with their Fearow like in the original. RIP.
The Vikings-the-mostly-live-in-Minnesota are replaced with an equally-funny costume theme of the Team Rocket trio as some tourist-trap sailors utilizing a Lapras-shaped ferry service. Brock comments on how conveniently they showed up and they all get on. We then get a very Disneyland 3D Attraction-style sequence of the two trios riding through the storm before it crashes and Misty sends out Staryu to try and save the day. Another extended sequence of Ash, Misty and Brock clinging for dear life to Staryu plays out, including a really gorgeous orchestral arrangement of...the Sun & Moon Wild Pokemon Battle theme, of all things!
Ash and the others meet the other guest trainers. This is where the short added scene of Brock hitting on Neesha is added.
In general, while the grand majority of the things that happen in each scene are the same as they are in the original, a lot of the animation is expanded upon and really pushed in terms of expressiveness. Often this makes them longer than how they were before, but it's definitely paced out properly to fit with the adjustments they made.
The battle arena sequence plays out and all 3 of the battles between Venusaur, Blastoise and Charizard Vs. their super-clones are extended with new fight choreography and additional attack exchanges, including with later-added techniques like Energy Ball and Leaf Storm. This scene also features a completely new background music track from the one used in the original film (later used quite a bit in the anime).
The sequence of Mewtwo capturing all of the humans' Pokemon plays out, also with new background music as opposed to the original. A few new shots of the trainers trying to guard their Pokemon from being captured are added.
After the super-clones emerge, Mew appears to save Ash from being splattered against a wall, the Pokemon and their clones brutally beat each other, now with new sweeping camera angles and new music instead of the original.
When Ash rejoins Misty and Brock after climbing down from the castle, a few bits of dialogue are slightly played around with, but leads to the same untimely demise. Some new DBZ-esque glow effects are applied to when Ash is struck by the two psychic blasts and his turned-to-stone state is a bit more like crystal. Pikachu trying to Thundershock Ash back to life lingers a bit more, to show the desperation in Pikachu's attempt. When the other Pokemon begin shedding their tears, their actual sounds aren't heard and the scene is silent minus the background music.
The memory erasure happens yet again, sending everyone back to the docks. From just after Ash sees Mew flying in the sky, commenting on it, up through Team Rocket's sign-off on the now-abandoned New Island, the orchestral arrangement of the Pokemon Red & Blue Credits theme plays. For the movie credits, we're treated to a new remix of "Kaze to Isshi Ni" (featuring Shokotan) along with some 2D paintings of Ash, Misty and Brock traveling. One shows a flock of Wingull near the dock area! At the end, we see Mewtwo and the clones flying off to what appears to be Mount Quena from the "Mewtwo Returns" TV special.
Finally, as is always the case, a very short teaser of the "Pokemon 2020" film, showing a 2D-animated Pikachu in the style of the Sun & Moon series.
There might have been some things I missed, but that's everything I could recount! If you have any other questions, I'll try and answer them as best as I can. To anyone else who was there for the screening, feel free to add anything else you like! Overall, highly recommend folks who enjoyed the original Mewtwo Strikes Back movie to go see this when the English version comes out!
72 notes · View notes
twilight-adamo · 5 years
Text
Author’s Notes: Brave New World, Chapter 6: Wake the Dead
https://archiveofourown.org/works/19709434/chapters/49726988
The part of the story dealing with the investigation into Alice’s past was meant to fit within one chapter. It ended up being three, because woman plans and the goddess laughs. I don’t regret it, though - I think it allowed me to cover a lot of territory that may become important later. Some of it came as a complete surprise to me; I would hit this point or that in the chapter, and I would have a plan for what came next, and then suddenly some part of my mind would go “oh, no, it’s this” and, well, what could I do but write it?
The history of Alice’s childhood home after her mother was murdered and the family left was inspired somewhat by the LaLaurie Mansion, which I namechecked in the chapter (though, whoops, I got the capitalization wrong). Nicolas Cage, the actor, did in fact own the mansion for a few years, and lost it to foreclosure when he ran into financial difficulties. He ended up suing his business manager, who countersued and claimed Cage had made a number of frivolous purchases against his advice. It’s widely believed (and may be fact) that Cage takes so many film roles today because he’s still paying off his debts.
During Cage’s ownership of the house, he did put a sign up on the LaLaurie Mansion (or it’s generally believed he was the one, at least), and it’s pretty much the sign I describe outside the Brandon house. The LaLaurie Mansion has an extraordinarily dark history - look up Delphine LaLaurie if you can stomach it; we are talking about a woman whose cruelty toward the people she had enslaved was so outrageous that other slaveholders condemned her and she was hounded out of town. Her story has been embellished over the years, and distorted by pop culture (American Horror Story, for instance, featured her as a recurring character played by Kathy Bates, and added elements of Elizabeth Bathory to her story), but she was certainly an extraordinarily horrible person. The mansion that stands today is not the original, which was burned down in the 1834, but it is still said to be profoundly haunted. A financial corporation holds it today. I’m not sure who, if anyone, it’s being held for.
I felt quite strongly as I started this chapter that Alice needed some time to herself. The little part of my brain that speaks for her had made it quite clear she was emotionally exhausted and inclined to withdraw. Naturally this leaves Bella worried half out of her mind, providing me with an opportunity to show Rose and Emmett looking after her. A few readers have told me that Rosalie and Bella’s relationship - the close friendship they’ve turned into sisterhood - is one of their favorite parts of the story; I suppose this makes it obvious it’s one of mine, too. But I don’t think it’s just Rosalie who cares for Bella so deeply; it’s Emmett, too. I tend to be a serious introvert in real life, and nervous about getting too close to others, especially physically. It’s probably left me rather touch-starved. But I do think touch is important, not just between lovers but between friends and family, and physical comfort can do more than words ever could.
I keep saying Bella isn’t me, that I’m making a deliberate effort as the story grows stranger to divorce her from the self-parody she originally was, and then I keep bringing in stories from my own life. Yes, it’s true: in my second year of college, I lived in a haunted women’s dorm, and the broad strokes of the haunting are largely as I describe. I left out some of the more specific details; it probably wouldn’t be hard for you to figure out where I went to college if you really wanted, but I didn’t want to spell it out. My first night there, I did in fact have strange, restless dreams that I interpreted as the ghost trying to figure out what to make of me. I did indeed mix herbs into water as part of a ritual and choked the mixture down, and I was left in peace after that. It was quite disgusting, frankly, but it does make a good story.
The dream was one of those surprises I’d mentioned. Frankly, a lot of stuff has just returned to Bella far earlier than I had planned. More than that, I wasn’t entirely sure I wanted to bring any kind of religion into it, but I found Bella’s spirit calling out and something answering and, well, here we are.
My personal beliefs are quite private. I’m willing to discuss them, to a point; I am completely uninterested in converting anyone to my way of thinking, and there are some elements too sacred to me to disclose. In broad strokes, the faith Bella describes agrees with my own. I do hold as a central tenet that the divine is infinite and beyond the comprehension of our finite minds. I do believe that the names by which we call the divine, the roads we travel to reach whatever understanding we can find, lead ultimately to the same place - and yet those names, the gods we cry out to, the commandments we follow, the stories we hold close to our hearts, all have a reality of their own. I do in fact worship seven goddesses whom I view as aspects of one goddess, and the titles and roles described roughly correspond to my faith. I would not invoke them in the way Bella does. I would not see them as she sees them. And if I ever do give them names in the story, they won’t be the names I call them by.
But yes: once again I’ve gotten deeply personal. Writing this story sometimes feels like writing an operator’s manual to my soul. Perhaps I keep turning back to these personal details, despite my best efforts to separate Bella from myself, because it all comes easier when I pour my heart and soul into the work. Even if that means I must submit to the horrifying ordeal of being known.
Does Bella have access to magic again, after that dream? I don’t think so - at least, I don’t think she could do magic on her own. She can help Rosalie, and presumably other witches; she can call out to her deities in prayer, but I’ve never viewed that as inherently magical. But her perceptions have broadened, just a bit, and sensation begins to return. She can feel the things she has been numb to, and see the light and color of spellwork.
Will she remember more of her old life? Not on her own, I think. I hadn’t really intended for her to recover any memories at all, and arguably she only recovered what she did due to divine intervention. Part of my long-term purpose in expanding Alice’s visions so she could look back on the past as well of the future was a vague notion that she could use this ability to help Callie and Bella work through their lingering questions about their personal history, and I certainly have further developments in mind. But I don’t see Bella becoming entirely who she once was. That person is part of who she has become, but otherwise lost.
Then again, I suppose I’ve allowed her to find other things I would once have called lost, so perhaps I can’t really say for certain until the whole of the work is done.
I really wanted to bring Leah into the phone conversation somehow - I do want more of her in this story, and an increasing number of these first chapters are focused on the BEAR world tour, meaning we don’t get much time with the folks back home. I just couldn’t see my way clear to it. This felt like a conversation between Bella and Callie and no one else.
We’re seeing some cracks in their friendship here, as Bella begins to understand that Callie is still holding back. There are likely more disagreements to come, and both Callie and Rosalie are growing more vocal about their mutual dislike for one another, which can’t make things any easier. I think things will be all right in the end, but if you’re getting the feeling the road ahead is a rocky one, I can’t say I disagree.
Six Flags New Orleans was still open during our heroes’ visit to New Orleans. A month later, at the end of August 2005, Hurricane Katrina wreaked havoc on the park, and it never reopened. Six Flags removed much of the infrastructure that once stood there (including some things that the city of New Orleans alleges they had no right to take), and though various development proposals have emerged over the years, none of them have led to the park reopening in any form. It stands abandoned, and though some urban explorers have gotten in, access is strictly forbidden.
I wanted to linger over dinner, but I couldn’t think of anything more to add, and I was eager, at long last, to get to the main event. So I wasted little time getting the gang to St. Charles Avenue, and into the old house, and then of course Luciana appears almost at once, as soon as Alice has gotten a good look at her past.
I think Alice saw more than she said directly in this chapter, and she’ll have more to say about what she saw as time goes on. It just wouldn’t all fit in this scene. Everything happened so quickly that I had to struggle to keep to what was immediately relevant. And honestly, I think we were all here for that last conversation between Alice and her mother more than anything.
Luciana is not, of course, La Llorona. That is a much older story that comes from farther south, in Mexico and Latin America. But as she is a weeping woman crying out in Spanish, some of the locals have confused the legends, and I did take some inspiration from the story of La Llorona in describing her behavior. In particular, some legends say that when La Llorona sounds distant, she is actually quite close - and so our heroes hear Luciana crying out distantly, and then, quite suddenly, she’s on top of them.
I had given some thought to Luciana speaking entirely in Spanish, at first - I had always imagined she was bilingual, but Spanish was her first language. Ultimately, I just wasn’t confident enough in my Google-augmented translation skills. I took Latin in school, not Spanish, and not much Latin at that. I can sometimes tell when things are really wrong, but I was worried it would come off as textbook Spanish, stilted and inauthentic, and things were coming so quickly and furiously that...well, it felt like it would take too long to find a fluent speaker willing to help me get the Spanish right. I hated to do it, I hate to say that - if I were preparing this for publication as an original work I would certainly take the time to get someone else’s eyes on the thing - but I’m going on vacation in about a week and a half and I really wanted to conclude this arc before dropping off the grid. All that said, if you’re interested in helping me with foreign languages or beta reading on this story in general, please do drop me a line; I’ve certainly made enough mistakes that I wouldn’t mind getting another pair of eyes on future chapters.
Alice’s family history was one of the first things I came up with for this book, along with the dream about the Volturi that opens the whole thing. Alice’s father was canonically a jeweler and pearl trader. Though I played a bit loose with canon otherwise, I decided to keep this, and had the notion that he married Alice’s mother for her family wealth and most importantly their access to jewels, pearls, and precious medals. When he suffered some reversal in fortune connected to her family, I figured - perhaps the mines drying up, or as I ended up describing, Luciana’s father dying and not leaving her what George saw as his due - he would become willing to throw her over for another woman, and more, to arrange Luciana’s death. The final details only came together as I was writing this chapter and the last.
Alice’s final conversation with her mother still feels short. To some extent, it’s meant to be. Once Luciana’s soul was fully restored, once she had some chance to find peace, I couldn’t see her lingering long upon the threshold between the living world and the next. I hope I got the important things down - above all else, that Alice now knows she had a mother who loved her, who loves her still and will be watching over her.
It’s not the end of Alice’s journey. But it is more or less the end of the gang’s time in New Orleans. Boston comes next, and then Ireland, and all the things that follow on from that. As I said, I’m going on vacation - I’m not sure whether I’ll get another chapter before I leave, and I have obligations to fulfill before the year is out. I hope, if there is a delay, that you all find this a decent stopping point.
Thanks, as always, for reading.
9 notes · View notes
lakelandseo · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
0 notes
epackingvietnam · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
#túi_giấy_epacking_việt_nam #túi_giấy_epacking #in_túi_giấy_giá_rẻ #in_túi_giấy #epackingvietnam #tuigiayepacking
0 notes
bfxenon · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
0 notes
nutrifami · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
0 notes
xaydungtruonggia · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
0 notes
ductrungnguyen87 · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
0 notes
camerasieunhovn · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
0 notes
gamebazu · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
https://ift.tt/2Yw1XZV
0 notes
kjt-lawyers · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
0 notes
noithatotoaz · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
0 notes
thanhtuandoan89 · 4 years
Text
SEO Forecasting in Google Sheets
Posted by Tom.Capper
Way back in 2015, I published an article giving away a free, simple, forecasting tool, and talking through use cases for forecasting in SEO. It was a quick, effective way to see if a change to your site traffic is some kind of seasonality you can ignore, something to celebrate, or a worrying sign of traffic loss.
In short: you could enter in a series of data, and it would plot it out on a graph like the image above.
Five years later, I still get people — from former colleagues to complete strangers — asking me about this tool, and more often than not, I’m asked for a version that works directly in spreadsheets.
I find this easy to sympathize with: a spreadsheet is more flexible, easier to debug, easier to expand upon, easier to maintain, and a format that people are very familiar with.
The tradeoff when optimizing for those things is, although I’ve improved on that tool from a few years ago, I’ve still had to keep things manageable in the famously fickle programming environment that is Excel/Google Sheets. That means the template shared in this post uses a simpler, slightly less performant model than some tools with external code execution (e.g. Forecast Forge).
In this post, I’m going to give away a free template, show you how it works and how to use it, and then show you how to build your own (better?) version. (If you need a refresher on when to use forecasting in general, and concepts like confidence intervals, refer to the original article linked above.).
Types of SEO forecast
There is one thing I want to expand on before we get into the spreadsheet stuff: the different types of SEO forecast.
Broadly, I think you can put SEO forecasts into three groups:
“I’m feeling optimistic — add 20% to this year” or similar flat changes to existing figures. More complex versions might only add 20% to certain groups of pages or keywords. I think a lot of agencies use this kind of forecast in pitches, and it comes down to drawing on experience.
Keyword/CTR models, when you estimate a ranking change (or sweeping set of ranking changes), then extrapolate the resulting change in traffic from search volume and CTR data (you can see a similar methodology here). Again, more complex versions might have some basis for the ranking change (e.g. “What if we swapped places with competitor A in every keyword of group X where they currently outrank us?”).
Statistical forecast based on historical data, when you extrapolate from previous trends and seasonality to see what would happen if everything remained constant (same level of marketing activity by you and competitors, etc.).
Type two has its merits, but if you compare the likes of Ahrefs/SEMRush/Sistrix data to your own analytics, you’ll see how hard this is to generalize. As an aside, I don’t think type one is as ridiculous as it looks, but it’s not something I’ll be exploring any further in this post. In any case, the template in this post fits into type three.
What makes this an SEO forecast?
Why, nothing at all. One thing you’ll notice about my description of type three above is that it doesn’t mention anything SEO-specific. It could equally apply to direct traffic, for example. That said, there are a couple of reasons I’m suggesting this specifically as an SEO forecast:
We’re on the Moz Blog and I’m an SEO consultant.
There are better methodologies available for a lot of other channels.
I mentioned that type two above is very challenging, and this is because of the highly non-deterministic nature of SEO and the generally poor quality of detailed data in Search Console and other SEO-specific platforms. In addition, to get an accurate idea of seasonality, you’d need to have been warehousing your Search Console data for at least a couple of years.
For many other channels, high quality, detailed historic data does exist, and relationships are far more predictable, allowing more granular forecasts. For example, for paid search, the Forecast Forge tool I mentioned above builds in factors like keyword-level conversion data and cost-per-click based on your historical data, in a way that would be wildly impractical for SEO.
That said, we can still combine multiple types of forecast in the template below. For example, rather than forecasting the traffic of your site as a whole, you might forecast subfolders separately, or brand/non-brand separately, and you might then apply percentage growth to certain areas or build in anticipated ranking changes. But, we’re getting ahead of ourselves…
How to use the template
FREE TEMPLATE
The first thing you’ll need to do is make a copy (under the “File” menu in the top left, but automatic with the link I’ve included). This means you can enter your own data and play around to your heart’s content, and you can always come back and get a fresh copy later if you need one.
Then, on the first tab, you’ll notice some cells have a green or blue highlight:
You should only be changing values in the colored cells.
The blue cells in column E are basically to make sure everything ends up correctly labelled in the output. So, for example, if you’re pasting session data, or click data, or revenue data, you can set that label. Similarly, if you enter a start month of 2018-01 and 36 months of historic data, the forecast output will begin in January 2021.
On that note, it needs to be monthly data — that’s one of the tradeoffs for simplicity I mentioned earlier. You can paste up to a decade of historic monthly data into column B, starting at cell B2, but there are a couple of things you need to be careful of:
You need at least 24 months of data for the model to have a good idea of seasonality. (If there’s only one January in your historic data, and it was a traffic spike, how am I supposed to know if it was a one-off thing, or an annual thing?)
You need complete months. So if it’s March 25, 2021 when you’re reading this, the last month of data you should include is February 2021.
Make sure you also delete any leftovers of my example data in column B.
Outputs
Once you’ve done that, you can head over to the “Outputs” tab, where you’ll see something like this:
Column C is probably the one you’re interested in. Keep in mind that it’s full of formulas here, but you can copy and paste as values into another sheet, or just go to File > Download > Comma-separated values to get the raw data.
You’ll notice I’m only showing 15 months of forecast in that graph by default, and I’d recommend you do the same. As I mentioned above, the implicit assumption of a forecast is that historical context carries over, unless you explicitly include changed scenarios like COVID lockdowns into your model (more on that in a moment!). The chance of this assumption holding two or three years into the future is low, so even though I’ve provided forecast values further into the future, you should keep that in mind.
The upper and lower bounds shown are 95% confidence intervals — again, you can recap on what that means in my previous post if you so wish.
Advanced use cases
You may by now have noticed the “Advanced” tab:
Although I said I wanted to keep this simple, I felt that given everything that happened in 2020, many people would need to incorporate major external factors into their model.
In the example above, I’ve filled in column B with a variable for whether or not the UK was under COVID lockdown. I’ve used “0.5” to represent that we entered lockdown halfway through March.
You can probably make a better go of this for the relevant factors for your business, but there are a few important things to keep in mind with this tab:
It’s fine to leave it completely untouched if you don’t want to add these extra variables.
Go from left to right — it’s fine to leave column C blank if you’re using column B, but it’s not fine to leave B blank if you’re using C.
If you’re using a “dummy” variable (e.g. “1” for something being active), you need to make sure you fill in the 0s in other cells for at least the period of your historic data.
You can enter future values — for example, if you predict a COVID lockdown in March 2021 (you bastard!), you can enter something in that cell so it’s incorporated into the forecast.
If you don’t enter future values, the model will predict based on this number being zero in the future. So if you’ve entered “branded PPC active” as a dummy variable for historic data, and then left it blank for future periods, the model will assume you have branded PPC turned off in the future.
Adding too much data here for too few historic periods will result in something called “overfit” — I don’t want to get into detail on this, which is why this tab is called “Advanced”, but try not to get carried away.
Here’s some example use cases of this tab for you to consider:
Enter whether branded PPC was active (0 or 1)
Enter whether you’re running TV ads or not
Enter COVID lockdowns
Enter algorithm updates that were significant to your business (one column per update)
Why are my estimates different to your old tool? Is one of them wrong?
There’s two major differences in method between this template and my old tool:
The old tool used Google’s Causal Impact library, the new template uses an Ordinary Least Squares regression.
The old tool captured non-linear trends by using time period squared as a predictive variable (e.g. month 1 = 1, month 2 = 4, month 3 = 9, etc.) and trying to fit the traffic curve to that curve. This is called a quadratic regression. The new tool captures non-linear trends by fitting each time period as a multiple of the previous time period (e.g. month 1 = X * month 2 where X can be any value). This is called an AR(1) model.
If you’re seeing a significant difference in the forecast values between the two, it almost certainly comes down to the second reason, and although it adds a little complexity, in the vast majority of cases the new technique is more realistic and flexible.
It’s also far less likely to predict zero or negative traffic in the case of a severe downwards trend, which is nice.
How does it work?
There’s a hidden tab in the template where you can take a peek, but the short version is the “LINEST()” spreadsheet formula.
The inputs I’m using are:
Dependent variables
Whatever you put as column B in the inputs tab (like traffic)
Independent variables
Linear passing of time
Previous period’s traffic
Dummy variables for 11 months (12th month is represented by the other 11 variables all being 0)
Up to three “advanced” variables
The formula then gives a series of “coefficients” as outputs, which can be multiplied with values and added together to form a prediction like:
“Time period 10” traffic = Intercept + (Time Coefficient * 10) + (Previous Period Coefficient * Period 9 traffic)
You can see in that hidden sheet I’ve labelled and color-coded a lot of the outputs from the Linest formula, which may help you to get started if you want to play around with it yourself.
Potential extensions
If you do want to play around with this yourself, here are some areas I personally have in mind for further expansion that you might find interesting:
Daily data instead of monthly, with weekly seasonality (e.g. dip every Sunday)
Built-in growth targets (e.g. enter 20% growth by end of 2021)
Richard Fergie, whose Forecast Forge tool I mentioned a couple of times above, also provided some great suggestions for improving forecast accuracy with fairly limited extra complexity:
Smooth data and avoid negative predictions in extreme cases by taking the log() of inputs, and providing an exponent of outputs (smoothing data may or may not be a good thing depending on your perspective!).
Regress on the previous 12 months, instead of using the previous 1 month + seasonality (this requires 3 years’ minimum historical data)
I may or may not include some or all of the above myself over time, but if so I’ll make sure I use the same link and make a note of it in the spreadsheet, so this article always links to the most up-to-date version.
If you’ve made it this far, what would you like to see? Let me know in the comments!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
0 notes