At Saleduck we are a big fan of Google Sheets to make sense of data. Below you can find four examples of a great time saving spreadsheet tips for online marketers.
1. Make your own heatmap
When you have a spreadsheet full of data, at first glance it does not tell you much. Google Sheets has a great little trick for this. For example, let’s take our visitor numbers from our Belgian website Soldesduck.be of the last 12 months. It might take you a few seconds/minutes to find outliers just by looking at the data, especially when you have a huge dataset to analyse.
You can use conditional formatting to have a better image of deviating values.
Just select all the data that you want to include, go to ‘format’ in the menu and choose ‘conditional formatting’.
There are a few different default options. At ‘preview’ you can choose what color-scale you want to use for relatively low or relatively high numbers.
If you look at the dataset now, you can immediately see in one glance what days there were very few or a lot of visitors on your website.
You can also set rules so only the format of the cells that meet this rule will change. For example a red color for all cells with a value below 2000 and a green color for all cells above 6000.
It’s good to mention here that the order of rules determines which rule counts. If you have a rule that says >2000 and one that says >6000, you sometimes have the case that both applies, hence you need to consider the order of the rules in this scenario.
2. Merge data for the same variables by creating a new table
Sometimes when you have a datasheet you have several numbers for the same variable.
For example, in the image above maybe you would like to know the total users for desktop, mobile and tablet, no matter what device they used. This might not seem like a great trick for such a small dataset, but it can save you a lot of time in a larger one.
To create a new table first you put the names of the variables in a cell, like below:
=sumif(Search-Range;Searchterm;Sum-Range)
On the place of the first dots you select the range of cells that contains the variables, on the second place of the dots you select the cell that contains the variable you are looking for, and the third place contains the range of cells that has the data that belongs to the variables.
In this case it would be:
=sumif(B16:B32;E15;C16:C32)
We want to use the same range of cells for all three variables, so we put a $ sign in front of the ranges so it will stay the same: =sumif($B16:$B32;E15;$C16:$C32)
You do this for all three variables. And then you have your new table:
You can quickly calculate the percentage too:
This table is a lot easier to read and understand if you are only looking for the total visitors per device.
3. Extract domains from a list of urls
Most marketers will be familiar with receiving spammy backlinks. So are we. Therefore every once a while we upload a disavow file in the Google Search Console. We don’t only want to exclude those exact urls, but the whole domain. You have tools to extract domains from a list of urls, but they will also include subdomains and that makes it less efficient. For example, this is a small part of the links (less than 1%) that we want to disavow for our domain.
In Google Sheets you can extract the domains from this list following these steps:
Step 1: Import urls
Paste/import all the urls that you want to extract the domains from in a new sheet (the first url starting at A:2).
Step 2: Paste this formula in cell B:2
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(TRIM(LEFT(SUBSTITUTE(TRIM(IFERROR(MID
(A2,FIND(“://”,A2)+3,99),A2))&”/”,”/”,REPT(” “,99)),99))),”.”,REPT(” “,99)),99*(1+(LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(TRIM(LEFT(SUBSTITUTE(TRIM(IFERROR(MID(A2,FIND
(“://”,A2)+3,99),A2))&”/”,”/”,REPT(” “,99)),99)))&”.”,”.”,REPT(” “,99)),198)))=2)))),” “,”.”)
As you can see, first we are extracting the extension.
You can now extend the section by dragging the formula down to all the other cells. Just select the cell, hover your mouse over the block in the right bottom corner.
The formula will automatically adapt the cells.
Step 3: Paste this formula in cell C:2
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(TRIM(LEFT(SUBSTITUTE(TRIM(IFERROR(MID(A2,FIND
(“://”,A2)+3,99),A2))&”/”,”/”,REPT(” “,99)),99))),”.”,REPT(” “,99)),99*(2+(LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(TRIM(LEFT(SUBSTITUTE(TRIM(IFERROR(MID(A2,FIND
(“://”,A2)+3,99),A2))&”/”,”/”,REPT(” “,99)),99)))&”.”,”.”,REPT(” “,99)),198)))=2)))),” “,”.”)
Drag the formula again down to all the other cells.
What happens now is that all text after a dot (.) but before a slash (/) is extracted.
This is not what we want though, because now it also extracts subdomains:
Step 4: Paste this formula in cell D:2
We paste this formula in D:2.
=Countif(B2,”*.*”)
So Sheets will check if there is a dot (.) in the B column results (meaning it’s a subdomain).
Step 5: Paste this formula in cell E:2
We paste this formula in E:2.
=IF(D2=0,””,B2)
Step 6: Paste this formula in cell F:2
We paste this formula in F:2.
=If(D2=0,C2,B2)
It’s quite some work to set it up at first, but you only have to do it once and then you can use the file forever to create neat domain:example.com lists.
Step 7: Paste “domain:” in cell H:1
Step 8: Paste this formula in G:2
=$H$1&F2
There you have it in Column G, all the domains in the right format to upload.
4. Count words with a formula
Sometimes you may have a string of text that you want to analyze. For example chatbot data or search field data from your website.
You may want to check what topics people are talking about to chatbots for example. In Sheets you can count words with a formula. The formula in this example is only limited to 1999 words, just to show the possibilities with it.
To set this up it goes like this:
Step 1: cell A2 should get your range of words.
Step 2: cell A3 should get the following formula:
=IFERROR(RIGHT(A2,LEN(A2)-SEARCH(” “,A2)),””)
This formula makes sure the first word of the above range of words is deleted in this cell. Drag this formula all the way down in column A.
You will get something like this:
Step 3: cell B2 should get the following formula:
=IFERROR(LEFT(A2, SEARCH(” “,A2,1)),A2)
This formula will extract the first word of the data range in column A. You can drag the formula down to the rest of column B.
With the previous example it will look like this:
Step 4: now in cell C3 we paste the following formula:
=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX
($B$2:$B$2000,MATCH(0,COUNTIF($C$1:C1,$B$2:$B$2000&””)+IF($B$2:$B$2000=””,1,0),0)),””)), 1, 1).
This formula will check if there is a duplicate of this word, and if there is, it won’t use it twice.
Also this formula we drag it down onto the whole column.
Step 5: in cell D3 we paste the following formula:
=IF(C2=””,””,COUNTIF(B:B,C2))
Again we drag the formula down the column.
Now the formula counts how many times a word is in the data range of words.
And then this is how it will look:
Once you have set this up, you can use it as many times as you like. Just change the text in A2 and the formula’s will do the rest of the work for you.
Summary
There are many great uses of Google Sheets, also for non technical marketers. Once you are a little bit familiar with some of the functions you will be able to make more sense out of data in less time.