Calculating Spend Averages with Excel for Online Ads
A while back while talking to a friend about his career changes he uttered the following phrase: “Yes it’s different but it’s also the same, everything leads to Excel”, me a self declared fan of Excel and anything that might make my work life easier smiled and laughed. This phrase is so real and there are still many who can’t seem to accept it, people still struggling with this basic but so powerful program. Having already accepted this as a general truth, I have been slowly trying to dominate this program as much as possible, so today I share two simple concepts that completely changed Excel for me and made me be a lot more creative when using this program. The conceptS I want to share are: Every cell is a representation of the formula bar, what this means is that what you are seeing in your cell does not necessarily mean that the cell holds exactly that but a formula that has that as a result. This concept is essential when troubleshooting and doing quality assurance. If there is an error look at the formula bar. Excel is not a numbers/math program but a logic and programming program with an excellent User interface. This change in mentality will allow you to test the limits of this program and try to automate and resolve issues in a formula/function way instead of a manual way. One particular example that holds these two concepts is a running 7 average on spend. This type of math is done constantly to decide if you are overspending or under spending and forecast spend for the future. The number of days might change for specific decisions, maybe it’s a 3, 5 or 10 day average. The options are to manually add the spend for the desired amount of days and divide it. All good up until now, but when we are doing this multiple times a day and we have to repeat this every day this simple task can become cumbersome. We can be more creative and use Excel to directly solve this for us. 7 day average spend that restarts every month, column A holds the dates and column B holds the daily spend. =IF(TODAY()-7>(TODAY()-(DAY(TODAY()))+1),(SUMIF(A2:A32,”>=”&(TODAY()-7),B2:B32)/7),(SUM(B2:B32))/(DAY(TODAY())-1)) Here we are using some basic everyday functions (if, sumif, sum, today, day and some other basic math like +, – and / ) This is the translation into English of every section. This cell will hold the result of the following operation = if the date is less than seven days before yesterday do the following step if not jump to the next one IF(TODAY()-7>(TODAY()-(DAY(TODAY()))+1) Sum the spend of the days that are less or 7 days ago and divide that into 7 ,(SUMIF(A2:A32,”>=”&(TODAY()-7),B2:B32)/7) Find the monthly average by adding up all the spend held in column B rows 2 to 32 and divide them by the number of days in today’s date minus 1, so if today it’s the 29 we will divide them in 28. ,(SUM(B2:B32))/(DAY(TODAY())-1)) If your data doesn’t start over every month you can keep this formula and just keep the sumif section. The important thing here is to be creative and explore Excel, a bit of logic and creativity will make you dominate this tool but the best will be that you will get time back.
Design Thinking Process:
When doing a task or activity for the first time, it usually takes longer than on following iterations. The first couple of times you are still struggling to remember how you did it and why you did it that way. By the 100th time you don’t think about it anymore, you are a well oiled machine, but what if our process is not the best? For me there are moments of doubt and self assurance, but as time goes by, the latter becomes stronger and I believe that my way is the best. But if you are like me, that first, second, third, etc times that you had to think and decipher how to successfully achieve your goal, you never paused and thought “let’s analyze this”. Process building is an integral part of being successful and error free. Every process can be enhanced and until you have done it enough times, you will not fully understand how to make it better. To keep our process from becoming stagnant we can use the design process, this process, often used for consumer centric issues, is also really applicable to any other issue. It consists of 5 steps and executing them every time we are going through an activity. Empathize: Meant to understand your customers, but in our case is to understand why we want to do something and gather the necessary information to be able to answer this question. Define: Clearly define what needs to be done, still not thinking of how to do it, but having a clear understanding of this will be key to improve the methods you generate to complete your objective. Ideate: Think of possible ways to complete your process. Prototype: Select one idea that you think is a suitable solution and proceed to complete the task following that idea. Test: Check what could have been done better, differently or what other piece of data might help your process better. Then, we repeat, but understanding that just because something achieved the goal originally set it does not mean that it is perfect, the objective here is to make things a bit better in each iteration and not settle with the current way.
The Importance of Tracking the Right Event
As Google shifts more and more into automation and allowing its algorithm to take over almost full control of a campaign (Performance Max) it’s becoming increasingly more important to give Google the right signals to optimize for. We’ve seen time and time again that Google is able to hit its tCPA or tROAS goals if it has enough of the right data. Here’s an example of Google maintaining the same cost per conversion across various match types for one of our clients. We introduced broad match keywords during this time frame and Google was able to maintain a consistent cost / conversion in comparison to Phrase and Exact. With that said, if you’re optimizing to an event that has little to no value to the business you are missing out on a lot! Yes, you need enough conversion volume to make the algorithm truly work, but you need to find a conversion event farthest down the funnel that would allow for this. For example, instead of optimizing to webinar registrations can you create a conversion event that fires after someone watches the first 90 seconds of the webinar? We’ve all signed up for a webinar and then not attended! The likelihood of a user making a purchase grows tenfold if they’ve actually watched 90+ seconds of the webinar, so can we get that data to Google and use its billion dollar algorithm to make appropriate bidding decisions in real time to get more 90+ second webinar watchers? This is by no means a make or break thing, but can help you scale at a more efficient rate. Google also offers ‘Offline Line’ imports to help B2B companies pass in SQOs, Opportunities, and other sales funnel events that come from all the form fills you’ve driven. Is there enough volume to optimize to SQOs? This could be a big win for you account! As data privacy becomes more relevant across the paid media industry it’s going to make this a bit more difficult, but also, I think, will really help you maintain and scale.
The Power of Data visualization and Storytelling with Data
When we think of data analysis, we think of numbers, spreadsheets, and insights, but does data analysis end with just finding insights? Well, the answer is ”No”. What if I say there is an even harder task than the current method of simply finding insights – it is referred to as the “Visualization and Communication”. Often in school we listen to stories which consist of letters, words and sentences put together and delivered to us, but has your math teacher taught those tedious algebraic calculations in the form of a story? Probably note No one has ever taught us how to tell stories with numbers. This leaves us poorly prepared to deliver those crucial data insights. Well currently if you’re thinking that anyone can put some data into graphs and charts and communicate the insights that they have analyzed by just showing those graphs, then let me tell you it is no longer as effortless as we think. Storytelling with Data is an Art, and it takes a lot of exercise to master this art. There are 3 pillars to Storytelling with Data: Data: This is like your foundation that’ll support your story. The hard work of collecting, preparing, cleaning, analyzing and findings comes under this one component. Storyline: This is the context around your analysis and your findings. What happened in the past, what is happening and what actions to be taken in the future everything is included here. Visualization: The pie charts, graphs, heatmaps that you will be using to pass your message to the viewers. In this Blog, I will give you initial steps on how you can improve at storytelling with data. Understand the context: Before anything you should first think, “who is your audience? Who are you going to deliver this story to? Do they like details or overview? What would they like to know from this story? etc.” Select Appropriate Visuals: This is a whole new chapter in itself. There are so many do’s and don’ts to this that I will save the details for later. But to give you an overview, use the right kind of visual for every insight. For example, if you want to communicate sales for the past few years, use line charts, so that a viewer can see ups and downs of the trend line. Keep it simple. Avoid using fancy visuals like 3D charts. Avoid clutter: Nobody likes a room filled with too many things. We all like open, clean and clutter free space. Similarly with visuals, avoid having too many graphs and charts in one view can look like a cramped-up space. The idea is less is more. Capture your audiences’ attention: Get your audiences’ attention to where you want them to focus. Think of it like this, when you open your laptop where do your eyes fall first? Is it to the center of the screen or to the left where your icons are? Or when you open Google chrome, does the search bar capture your attention first or the big, bold, colorful GOOGLE Logo. It is all about the pre-attentive attributes like size, color, and position. Time to tell your story: A story always has a clear beginning, middle and end. Your story should always start with the background and end with what you want your audience to do with this story. Just like our marketing strategy we have a call to action before every conversion, we need to have a call to action to ensure that our story is sold. I hope this blog gave you an overview of what are the attributes and the steps involved in mastering the art of storytelling with data.
Automation 101: Macros
When we think about automation or automating tasks we either think of a magic solution that will do our work and let us sip margaritas while a computer does our job for us, or, if your experience has been bad, you might think of automation as too complex of a solution for a simple issue that constantly breaks. The reality is that automation can come in many forms, it can be as complex as creating a new communication system to as simple as creating filters that will optimize your time. There are many simple tools that can help create these solutions from basic excel functions and formulas to paid software like supermetrics. A great tool to help you do this is Excel’s Macros. Don’t worry, no coding is coming up. We all have recurring tasks that have the same processes, maybe we download the file from Google, we delete a certain number of rows, then we create a filter, order by a specific row and then keep on doing the same steps exactly like we did the last time.“Exactly” is an important word here Macros allow us to automate this process, we teach Excel certain specific steps and Excel will repeat them exactly on any given file. So if your file is coming from a different source or your columns are in a different order your Macro will not do the job correctly. The first step to access macros is to activate the developer tab in excel. To do so, you will have to go to File / Options, then from the right column click on Customize Ribbon and under Main Tabs check the Developer box. Now you will have the Developer tab in our menu! to create our first Macro, click on “Record Macro” under the Developer Tab. This will open up the “Record Macro” popup, this simple menu contains four fields: Macro name: Here you insert the macro’s name, it has to start with a letter and have no spaces, I suggest using the underscore for space but camel casing is another option to separate words. Shortcut key: If this task is something you do daily you can assign it a shortcut (Ctrl + one key of the keyboard) Store macro in: This is the important part. You will be given three possibilities of where to store the macro, if it’s something you will do repeatedly I suggest you store it on the Personal Macro Workbook, as you will be able to summon the Macro when you open different files. If it’s something you do on a single file that you always work on you can save it there as it is a file unique Macro. Description: A Short description of what the Macro does Once you have finished setting up you press “ok” and you will see that the “Record Macro” button on the Developer tab has changed to “Stop Recording”, this shows that Excel is recording your moves. If you hide a column, delete a row, format numbers or do a Vlookup, Excel is recording. Once you are done with your process, click on Stop Recording. Now, if you press on the “Macros” button next to the “Record Macro” button a new popup menu will appear showing your previously recorded Macros, here you can run, edit or delete your Macros. The next step is to close the excel file and a new popup will appear press Save so the Macro is recorded on your system to be reused.
Set Up An Automated Budget Pacing Report With Supermetrics
As busy professionals, we all wish we could have a bit more time.Time is always a limiting factor. But in a world of automation, there’s always a way to free up your minutes and make your life a little easier. Let’s get to it! We use Supermetrics to help with this. Here’s the simple equation to calculating your budget pacing =(Total MTD Spend/count(Total Delta Spend))*Number of Days in Current Month Now that you have the equation above, you need to set up your Google spreadsheet with the necessary information. All you need is 4 columns: Date, Total Spend, Total Budget, and Total Delta. Perfect! Let’s say your client, GenelikesJeans provides you with a monthly budget of $31,000 for the month of March on their Google platform. Fill up the individual dates in March in the Date column (or not since Supermetrics will do that for you), and fill the Total Budget column with the planned daily spend (= Total monthly budget / # of Days in March) Time to pull up Supermetrics. Navigate to Extensions in the top column > Supermetrics > Launch sidebar. Set up your Supermetric queries with the appropriate information. If your client uses more than 1 platform, you will need to create extra columns (Date, Total Spend, Total Delta) for each platform. Then include all those data into an Overall Total Spend and Overall Total Delta column. View below for reference regarding 3 platforms: Google, Facebook, and Microsoft. But it’s the beginning of the work week, so we’ll keep it simple. After you fill up your query on Supermetrics, click on the first row in your Date column THEN click the Get Data to Table button on Supermetrics. The data for Total Spend column will be pulled into the spreadsheet by Supermetrics Next, create the total delta equation in each row in the Total Delta column by subtracting a row in the Total Spend and Total Budget columns. And then, input a Total for all columns at the bottom Now with the given information, calculate the pacing report using the equation given at the very top. Note that you will manually have to pull down the Total Delta column to the same row the Total Spend column stops at for the day, since that will not be automated. And that’s that! You can now quickly update GenelikesJeans with a monthly pacing report. Cheers to more time, less math.
Pixel Based Tracking versus 1st Party Data Attribution
The more sources of marketing attribution and tracking data you use, the more confused you are likely to become, but why is that? As online marketers increase ad spend, marketing channels, and complexity of campaigns, the need for accurate marketing performance becomes critical. The increased ad spend adds more pressure to perform. The dreams of scaling your ad spend in lockstep with your business growth creates dreams of freedom to the business owner/client, and when those dreams suddenly are dashed, there is a need for answers. Why isn’t the marketing scaling with the ad spend? The quest for answers leads people to look more deeply into their Facebook tracking, Google Ad tracking, and Google Analytics reports. The numbers don’t usually add up. The problem is that you are looking for answers in data that is not designed to answer your question. Here is a recent email from a Wicked Reports prospect that prompted this blog post: “We’re seeing Revenue of 156K total in our profit and loss statement – that is without tax. FB PPC is reporting 94K rev and google PPC is reporting 141K rev (total incl tax 235k) take the tax off and that leaves: 188k. Analytics is reporting Total revenue of $227k (including tax). I’m getting insanely confused as no numbers stack up, I know you don’t know the business, but we’ve increased marketing spend, generated good ROAS from FB, but it doesn’t seem to show anywhere?” First of all, hats off to the person behind this email. They are taking the time to do the work to dig into why their numbers don’t add up. The fruits of that labor are plentiful: Going back to the email above, how come the profit & loss statement does not match the combination of Google Ad PPC and Facebook Ad PPC pixel tracking results? And why is Google Analytics so far off? The answer lies in the pixel-based tracking’s limitations and/or bad setup. Here are the top problems and limitations we see at Wicked Reports: Some or all of these 9 top pixel risks can lead to your Facebook Ad, Google Ad, and Google Analytics conversion revenue being different than your actual business revenue. Let’s suppose you have your pixels correctly firing once on each page. Will your numbers add up? Not if you are a multi-channel marketer using email, Facebook PPC, and Google Ads, plus with some organic SEO traffic added to the marketing mix. Both Facebook and Google ads will sometimes claim credit for a sale, when the actual credit belongs to email, organic, or the other ad platform! Let’s look at this scenario that could apply to us at Wicked Reports: This one sale can show up as follows: In this one simple scenario, (and yes, this only has a few clicks and campaigns to look at, generally there is a lot more going on!), if you are trying to reconcile your Facebook, Google Ad, and CRM sales tracking, you will see 3 sales, when you actually only had 1 sale. This can lead you down a rabbit hole of Google Tag Manager sophisticated installations (could work if you get a top dollar expert who installs it flawlessly, and you pay them to maintain it), attribution click time windows, what the hell to do with view-through conversions vs clicks, and the agony of still never having your tracked sales equal your actual sales. Wicked Reports solves all these challenges using 1st party data tied to real business outcomes with true ROI. First, we start with your 1st party data within your CRM and shopping cart. 1st party data is the ONLY way to know a lead or sale is real. All of our patents and algorithms are based on the premise that we need to start with real, verifiable data first. Otherwise, you are guessing or acting on data that is only semi-accurate. That’s like building a house where the foundation is 60% solid – how can you sleep at night without worrying the house is going to implode on you? 2nd, we match attribution models to specific marketing goals. You need to measure marketing results based on the intent of the marketing. I can’t stress this enough. What good is looking at some complex mathematician’s attribution modeling mix if you can’t understand it, and worse, if the attribution model doesn’t understand you or what you are trying to achieve? If you are doing cold traffic lead gen, your goal is to find leads that become high value customers over time. We have a specific marketing attribution model designed precisely for this situation. When your marketing finds a new lead, and that new lead ends up buying over and over again, either with one-time sales or subscription re-bills, that is literally GOLD for your business. 3rd, all of our attribution data is verifiable. You shouldn’t trust a black box with your job security! We show the data behind the ROI numbers of your marketing campaigns. Since we start from real sales and leads, it makes it a lot easier to give you data you can verify. 4th, we filter out the noise and give you only the strongest signals. Sure, it’s an interesting read over at thinkwithgoogle.com about how different ecommerce shoppers have 142 touch points before they buy. What the hell are you going to do with 142 touchpoints of data when you can’t even reconcile your shopping cart sales against your ad platform tracking and ad spend? Wicked’s premise is that a sale is the strongest signal, a lead email submission is the next strongest signal, and a click is the 3rd strongest signal. We only report ROI, revenue, leads, and clicks against attribution models tied to verifiable 1st party data. This approach limits the time to understanding and acting on the data. It also takes into account the reality that you are already a busy marketing professional who does not likely have 30 additional hours a week to try to comprehend why
How to Setup Google Ads Conversion Action Sets
What are and how to set up Google Ads conversion action sets, allowing advertisers to set different events by campaign in Google Ads (released in 2019).
Fix Avg. % Calculation Error in Google Data Studio
Data Studio does not always calculate percentages correctly by default. Some metrics need additional manipulation.
Introduction to Google Analytics Segments
Learn about the power of custom segments in Google Analytics with this quick guide. You’ll be able to create your own segments in no-time!