Digital Analytics MiniDegree from CXL — every marketer must try — Part 10 Review
CXL Institute’s Digital Analytical Mini degree has become quite helpful in knowing from very little to knowing much more.
As always I start by saying analytics is the sexiest word, for at least the professionals who understand it, most of the students and professionals are following this as a career, rather for the pay than its actual understanding and passion. Let’s not argue over this, since it has lots of points balancing on either side of the equation.
Before I go any further I will just give a brief on myself, I am Nischith a sales & marketing professional working in the health sector. I live in Bangalore India and I am from Mysore, City of Heritage.
The information shared below is based on the learnings from CXL Institute, you need to have a bare understanding of what Digital Marketing or Google analytics is, as CXL themselves say their courses are not for beginners. Let’s dive in, I hope I give you a good insight into their teaching.
Today we will see review the next part on how we need to present data that we collected and stored. Let's discuss one such tool used Excel(also Google Spread Sheets). You can download data from the google search console and play around or use your own data elsewhere.
Here we will not go in-depth but will mention things wherever necessary. CXL’s In-depth course is the right for you to understand better and learn from the experts and be among the 1% digital marketers.
Before going ahead you should be aware of the $ symbol, which is used as a relative reference,
“$ Symbol is used to lock a specific row or column, the shortcut to used is by pressing F4, this feature is used while working in formulas when we do not want the reference to be changed when we copy or drag down the formula to other cell references, this feature will keep the reference same for all the further calculations.”
As mentioned earlier we will run through only the surface, first and most important function is SUM and its variations.
Commands
SUM function
=SUM(number 1, number 2….number n), quite a helpful function to sum numbers
SUMIF function
=SUM(range, criteria, [sum_range]), you can use this if you have anything to sum with one condition, for example, Pageviews from the USA, USA being the condition or criteria.
SUMIFS function
=SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, criteria_range3, criteria3, … criteria_range_n, criteria_n] ), this is used when there are multiple criterias for example pageviews from the USA, INDIA, BRAZIL, here USA,INDIA,BRAZIL is the condition.
Note:
Range: Where is the condition you’re looking for
Criteria: What are you looking for
Sum_range: what are you going to sum?
Next Command COUNT, we will see its variation too,
COUNT
=COUNT(value1, value2….), counts the number of numeric items
COUNTA
=COUNTA(value1, [value2], …)counts the number of alphanumeric items
COUNTIF(counts based on one condition)
=COUNTIF (range, criteria), used to count with one criteria
COUNTIFS (counts based on multiple conditions)
=COUNTIFS (range1, criteria1, [range2], [criteria2], …), used to count with multiple conditions
Next, we will see a few more functions and operations that will be helpful for marketers to clean the data, I will be mentioning them but the same can be referred to google for in-depth syntax and for practical learning again asking you people to join the CXL institute’s Digital Analytics,
- Proper function
=proper(), Used to correct the sentence order example if a cell has “leaRNing” you can use the proper function to make “Learning” - Length function
=Len(), Used to check the number of characters in a cell for example “Learning” has 8 characters(not considering “ ”) - Duplicate removal Operation
go to DATA tab, there click on remove duplicates and click on the column to remove the duplicates, this is quite useful in identifying duplicates and finding “unique” values - Highlighting Duplicates
Select the cells to check for duplicates, then go to the Home tab(which is the default data), there click on Conditional Formatting >then Highlight cell rules> Duplicate values, now you get to know the repeated values which are highlighted by color if it is the column you can sort or filter. - Sort/Filter Operation
This is quite an important operation to identify a group of values, you can use the conditional statements for further drilling of segments in a column. - Vlookup/Hlookup Operations
VLOOKUP is also called ‘Vertical Lookup’. It is used to search for a certain value in a column, in order to return a value from a different column in the same row. Hlookup as the letter goes H for ‘Horizontal Lookup’. - Text to columns Operations
This is the operation used when there are 2 or more values required from a single cell. - IFERROR()
This is basically used not to avoid showing #errors in a formula. - Index and Match
VLOOKUP/HLOOKUP requires the match to be in the very first column; INDEX and MATCH are specifically used to get around this VLOOKUP/HLOOKUP limitation. - Named Ranges
Named ranges, which are one of the distinguishing marks of the spreadsheet-power-user, can be used all over the place, not just in pivot tables. VLOOKUP, XLOOKUP, and multiple other applications come to mind. The one thing they can’t have is spaces — or dashes — in their name.
Now we come to an important operation, PIVOT Tables
Wikipedia — “A pivot table is a table of grouped values that aggregates the individual items of a more extensive table within one or more discrete categories. This summary might include sums, averages, or other statistics, which the pivot table groups together using a chosen aggregation function applied to the grouped values”
The main component of the Pivot table,
Pivot Table Fields -Adding Fields to Rows or Values
To add a pivot table go to the Insert tab>Pivot table>Ok for Newworksheet>now add Rows, columns and values you can refer to the image below for what rows, columns and values,
There is another operation called slicer, this is helpful to filter for small variables in the pivot table.
Click anywhere in the table or PivotTable.
- On the Home tab, go to Insert > Slicer.
- Click the Slicers, select the checkboxes for the fields you want to display, then select OK.
- A slicer will be created for every field that you selected.
Excel is quite an important part of every marketer in cleaning the data you can also use excel to create visual dashboards like Pie Chart, Scatter plot Chart, Linear Chart etc.,
Thanks to Fred Pike
It was excellent learning from CXL Institute’s Minidegree Program.