7th Week Review-Learning From CXL Gowth Marketing Course

This week I learnt Excel and sheets for marketers. I mainly focus on Sheets for marketers, every marketers should know how to use sheets for their daily analysis work and team cooperation.

Sort and Filter

I learnt how to export GSC data without limitation using a add-on search analytics for sheets. Once it’s installed, open up a new sheet and look under the add-ons menu. You’ll see Search Analytics for Sheets. Go ahead and open the sidebar. Note that if we ask for a lot of information, such as many months worth of data, it can take a long time for the request.

Then we can add some order to this spreadsheet. Get rid of domain name in the page column using Find and replace. Sort by page, we need to highlight the whole block to let Sheets know it’s a table, click on the Data command, Sort range, tell it it has headers, choose page. Add a filter, click on the Data command, Create a filter, we can have filters on each column. In Sheets, once we have the filters set up, the best way to sort is by using the filters.

SUM-Variations

• SUM(sums eveything)
• SUMIF(sums based on one condition)
• SUMIFS(sums based on multiple conditions)

They are case insensitive. I learnt relative cell reference, absolute cell reference, put F4 key can make a absolute cell reference. we can also use a mix of relative and absolute using a single dollar sign.

The SUMIFS command is set up a little bit differently than the SUM and SUMIF, the first thing we’re going to specify is the sum range, then we are going to specify the criteria range and the criteria we’re looking for.

COUNT-Variations

Count command is similar to the Sum command above.

• Counta(counts the number of text items)
• CountIF(counts based on one condition)
• CountIFS(counts based on multiple conditions)

For example, if we want to know how many queries we get during certain timeframe, to do that, we’re going to use a counta command.

The difference between Count and Counta command is that count command will count cells that have numbers in them.

Pivot Tables-Set Up

Set up a basic pivot table:

Have a cursor in the mass of data, all the data we’re about to put into pivot table has to be contiguous. That means there can’t be any empty columns or empty rows. Data command, then Pivot Table, and create, we are now in the pivot table sheet, the pivot table editor at the sidebar will let us add the rows and columns and values. Also Filters option is very important. Notice there are some suggested pivot tables already.

One of the greate advantage of a pivot table is that we can move things around(move the fields between the different options), we can keep playing the format until everything is in the order that we want.

Pivot Tables-Calculated Fields

How to create a calculated field in a pivot table?

Author took an example of CTR calculation, CTR is calculated from this formula: Clicks/Impressions, but when we want to know the average CTR across a certain period, using Sum or Average command not gonna work, so we’re going to add a new field, a calculated field that will give us accurate information.

Use the Calc in the name of calculated field makes it apparent to us that that’s a calculated field, as opposed to one that comes from the underlying source data.

Pivot Tables-Filters, Slicers

Segmenting the pivot table:

There are 2 ways to segment pivot table: Filter and Slicer, Slicer is a way that can make the segmentation more visible, we know what are actually filtering on. Slicer is a fairly new addition to Sheets.

Data command -> Slicer, we can filter the data by using the pop up selection bar which can be anchored at the top of the page. We can add more than one slicer as well.

Filter view is a very powerful feature in Sheets, it provides a convinient way to take a look at the metrics/dimentions the we are really focused on. Data command -> Filter views -> Create new filter view. Use the hyperlink command to post the filter view as a link in the spreadsheet. Pivot table is not a transitory table, if we deleted it, we would no longer to access to the filter view again, it is no longer exist. Even if we recreate the pivot table, although it looks like it was before, notice that the filter view ID is a different ID.

De-Dup & Text To Columns

Data command -> Remove duplicate: remove the duplicates

Text to column command help get rid of specific text involved in that column. Data command -> split text into columns. There is no separate that it automatically recognizes. We’re going to have to create a custom one.

There’s a quicker way to delete the specific text involved in the column, it is filter method, select them all, right click to delete the selected rows. Which is what we ended up with the Text-to-column command.

Vlookup

If pivot tables are one of the things that differentiate the pro from the amateur, so are the Vlookup.

Vlookup is short for vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.Data command -> Named ranges, check for the ranges.

It accepts 4 variables, 1st is the the value to search for, 2nd is the range to consider for the search, 3rd variable is the column index of the value to be returned, the 4th indicates whether the column to be searched is sorted.

IFNA function will look for N/A error and avoid that error, a border formular to use for error checking is IFERROR.

Note: we can also do a Find and replace command to the formula, check Also search within formulas.

Index and Match

Vlookup is pretty powerful yet it has limitations, Index and Match address those weaknesses.

One of the critical weaknesses to the VLOOKUP table is that the thing we’re looking up has to be in the very first column. Match command can address this but it only returns a row number where the value to search for locates. Then we need to use Index command to find the actual value.

Sparklines

Sparkline is a nice little tool to have in our tool belt. Sparkline is a one cell mini graph, and we have different options for what type of graph to insert.

The sparklines in Sheets are controlled through a function (SPARKLINE) rather than through menu options.

Conditional Formatting

First, named ranges is convenient for the conditional formatting, second, Conditional formatting is done under the Format menu or from with that named range by right click, then a side panel pop up.

Besides create a heat map, we can create format rules, custom formula can be very complex.

After applying conditional formatting, we can create a filter by color, that’s going to give us much more meaning information.

String Functions

• len
• substitute
• find (case sensitive)
• search (case insensitive)
• mid
• left
• right
• If we are working with languages that are double byte languages like Japanese, Chinese, or Korean, you can use FindB, SearchB, MidB, LeftB…
• Wild card searches: ? represents one character, * represents all characters, the downfall of wild card searches is that it doesn’t scale well

How to approach a formula from doing a search on Google?

When we put above functions in a long formula, they become a little bit less understandable, we can break down the formula step by step from the most inside to outside.

Error Trapping

• Data validation
• IF Function
• Locking a Spreadsheet

Additional resources

1. Almost every tool that we use, has a way to export related data.
2. Google analytics add-on
3. www.benlcollins.com