Excel and Sheets for marketers
This is a course in my Digital Analytics Minidegree from CXL Institute. After taking this course I realize that Excel and Sheet is a crucial topic to master as an analyst. This course is by Fred Pike an Experienced presenter, teacher, and blogger on GA, GA4, and GTM topics and a Guitarist and five-string banjoist.
This course objective is to teach to Find actionable marketing insights using Excel and Google Sheets. As it’s a pratical course, we use Google search Console and Google Analytics data exported through add-ons.
Summary:
GSC — Sort and Filter
SUM — Variations
COUNT — Variations
Tables and Calculated Columns
Pivot Tables — Set Up — Sheets
Pivot Tables — Set Up — Excel
Pivot Tables — Calculated Fields
Pivot Tables — Filters, Slicers, and Timeline — Excel
Pivot Tables — Filters, Slicers, and Timeline — Sheets
Power Tips for Pivot Tables — Excel
De-Dup & Text To Columns
Vlookup
Index and Match
XLookup — Excel
Sparklines
Conditional Formatting
String Functions
Error Trapping
Additional Resources
Before going into lesson details, I will present 2 addons we install in Google Sheets to get offline access to Google data.
Search Analytics for Sheets is used to retrieve data and create automatic backups from Google Search Console™ into Google Sheets™. It allows you to:
1. Retrieve query, page, clicks and other data on-demand for any of your verified websites from Google Search Console’s Search Analytics directly into Google Sheets.
2. Create automatic data backups in Google Sheets
The Google Analytics spreadsheet add-on brings you the power of the Google Analytics API combined with the power of data manipulation in Google Spreadsheets. With this tool, you can:
- Query data from multiple views (profiles).
- Create custom calculations from your report data.
- Create dashboards with embedded data visualizations.
- Schedule reports to run automatically so your data is always current.
- Easily control who can see these data and visualizations by leveraging Google Spreadsheet’s existing sharing and privacy features.
Google Search Console — Sort and Filter
You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list you create (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set.
SUM — Variations
3 types of SUM commands
SUM (sums everything)
SUMIF (sums based on one condition)
SUMIFS (sums based on multiple conditions)
COUNT — Variations
Very similar to SUM commands
3 types of COUNT commands
COUNTA (counts the number of text items)
COUNTIF (counts based on one condition)
COUNTIFS (counts based on multiple conditions)
Tables and Calculated Columns
Excel has default formatting based on column content type: text, date, number…
You can keep this default or change it but be consistent with your choice to help anyone looking at your work to understand it easily.
You can insert table in excel and table are scanned in sequential way
Formula presentation/reference change if you use tables
Pivot Tables — Set Up — Sheets
For Pivot Tables to work, data has to be contiguous and have headers
Insert >Pivot Table
You can apply filters and format fields.
By default fields with text are placed inside row and fields with number are put in value
Setup a basic Pivot Table
Pivot Table fields
- Adding fields to Rows or Values
- Designoptions — Compact, Outline, Tabular
- Analyze — Expand and Collapse fields
Pivot Tables — Calculated Fields
Analyze > Fields > Calculated field
One must know its data, how they are calculated by default so you can see problems where they occur.
Pivot Tables — Filters, Slicers, and Timeline — Excel
Segmenting your Pivot Table
- Filters
- Slicers
- Timeline
Power Tips for Pivot Tables — Excel
Power tips for Pivot Table (Excel only)
- Tables and Named Ranges
- Refresh the data source
- Detailed view
- Page per Filter item
- “Show Values As” in fields settings
Create a table with you data before creating Pivot Table. One new
You can changed the name of table.
Formulas > Name Manager > Select the table, click edit.
On this table, you can’t use dash (-) or space but underscore can be used.
De-Dup & Text To Columns
From this lessons and later ones, the teacher used Google Analytics data.
To remove duplicate Data > Remove duplicate. We select the column containing duplicate and click OK
Convert text to column: Data > Text to column
Vlookup
Pivot Table and VLOOKUP differentiate pro and amateur in Excel and Sheets
Syntax: VLOOKUP([value], [range], [column number], [false or true])
Index and Match
This address some weaknesses of VLOOKUP
XLookup — Excel
This is for Excel only. It’s a new function in earlier version of Excel from
XLOOKUP address many VLOOKUP shortcomings:
- The Default Setting for VLOOKUP is an Approximate Match
- VLOOKUP Only Finds the First Match
- Inserting a Column will Give Wrong Results
- VLOOKUP Only Looks to the Right
The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Sparklines
One-cell mini graph that can be customized using a custom menu.
Insert > Sparlines > Data range — Ok
In Sheets Sparklines are controlled by a function but a menu controlled in Excel.
Conditional Formatting
There is several different options to do Conditional Formatting
Conditional Formatting > Manage rules
Inside Sheets, Conditional Formatting is done in Format menu and it’s not as intuitive like Excel Conditional Formatting
String Functions
As an analyst or marketer, we will have to do a lot of string (text, address, product name…) manipulation in Excel and Sheets using these functions:
- LEN
- SUBSTITUTE
- FIND
- SEARCH
- MID
- LEFT
- RIGHT
If you work with languages tht support DBCS (Japanese, chinese, Korean…) you will use FindB, SearchB, MidB, LeftB, RightB
Almost all the free or paid tools used by an analyst (SEMRuch, Spyfu, Google Analytics…) offer a method to download files that can be imported into Excel or Sheets.
