CXL Institute Growth Marketing Minidegree review Part 7

Understanding Excel for marketers and Marketing Attribution

Picture from CXL course on Attribution

After an amazing learning session on Google Analytics and Google Tag Manager, we dived into understanding excel for marketers and attribution basics.

With CXL Institute I have learned so much about the topics I thought I knew, some great pro tips learnt with some of the best instructors and top 1% marketers

Let’s dive in to the learning material by understanding the excel basics:

a) Slicer: Analyse>Insert slicer. The slicer works well with limited category fields. With slicer, we can really see the filter applied. We can add slicer by going to data>slicer and then adding the column list we want to use. Slicer in sheets gives a great view of understanding what the filters are being added.

b) Filters: Filters can work great with a long list of category fields. Filter command in excel can be added through data>filter, in sheets it is down below once we are in the pivot table view. Filters in sheets work together.

c) Timeline: Timeline works only with the date field and you can specify months, quarters and years. Analyse>timeline. Timeline is unavailable for sheets.

d) Calculated fields: This was such a fresh find. For some of the calculations in pivot table the sum or the average might not make sense. So you can add a new calculated field as a part of the pivot table. You can do this by Analyze>fields,forms and sets>insert calculated field>create formula>set up the new field (in excel). In sheets, this can be done right from where we add the columns, choose the last option> calculated fields.

e) Filter view in sheets: We can create a filter view to easily navigate between different important filters. We can now easily switch and also share this by using the FVID and using the =Hyperlink(“URL”, “range_name”). This can be then pasted anywhere and the link will take to that particular view.

f) Power tips in Pivot table in excel: Sheets for pivot is a little less powerful as compared to the excel version. Some power tools that I learnt is:

— Start the pivot table after converting the data in a, excel table, this makes it easier to get the new data fields which will automatically show up when refreshed in the pivot table.
— The detailed view of a number in the pivot table can be learnt by clicking on the number that will open the composition of that number, if any. This does open a new sheet which can be controlled through Pivot table options. (Analyse>Options>data)
— You can create individual spreadsheets based on the filters. This can only be done when you have a small set of filters.
— The show value as can be used to correlate the data in many different ways. This is a great feature which is generally not used.
— Turning off auto fit columns: Analyse>Options>turn off auto fit option. This makes the experience smooth for everyone viewing the data when flipping through the filters.
— Pivot chart: Though this is a cool feature, I think this leads to more cleaning of the chart. However, pivot chart is tied to the pivot table which means all the filters and slicers are attached to the chart.

We then moved to different other features for excel and sheets including vlookup, let’s look at some of the cool points learned:

search_key - The value to search for. For example, 42, "Cats", or I24.
range - The range to consider for the search. The first column in the range is searched for the key specified in search_key.
index - The column index of the value to be returned, where the first column in range is numbered 1.

If index is not between 1 and the number of columns in range, #VALUE! is returned.
is_sorted - [TRUE by default] - Indicates whether the column to be searched (the first column of the specified range) is sorted. FALSE is recommended in most cases.

3. Index and Match: Sometimes it might happen that the information you are looking for is not in the first column from the range. We can then use the combination of index and match where we can curb this challenge. With the match command we can locate where the range is. The index then uses this information and returns the value that we are looking for.
=INDEX(reference, [row], [column])
reference
- The array of cells to be offset into.
row - [OPTIONAL - 0 by default] - The number of offset rows.
column - [OPTIONAL - 0 by default] - The number of offset columns.

INDEX and MATCH gives us more flexibility when what we’re matching on is not in the very first column that we are doing the VLOOKUP on.

4. Xlookup: This is one up from Vlookup and takes into consideration the shortcomings of Vlookup. Though, Xlookup is not available everywhere but it was fascinating to learn about this command. It can take care of the challenge of:
a) The data not being in the left hand side column
b) The challenge of specifying exact match
c) Vlookup cannot handle column insertions
d) Vlookup can’t do vertical lookups
e) Vlookup has weird approximate values
f) Vlookup requires the complete reference cells

5. Sparklines: A one cell chart that can pictorially represent the data. This is easy to set up tricky to delete. In excel it is added through chart on the toolbar but in sheets it is done through the command:
=SPARKLINE(data, [options])

data - The range or array containing the data to plot.
options - [ OPTIONAL ] - A range or array of optional settings and associated values used to customise the chart.

6. Conditional formatting: This helps to highlight on some of the data on the basis of the formatting options available with excel. Conditional formatting is very easy to use with excel but no so much with sheets. One pro tip I found was to define named ranges that will make it easier to select and apply the formatting. In sheets, we can apply multiple rules and use filters to make more sense of data.

Understanding string (text) functions

Understanding Error Trapping

Attribution

Attribution is not just another number on the spreadsheet but more about the overall customer journey. Marketing attribution is linked to the different platforms and an end point — conversions. We refer to conversions as actions that relate to monetary values when we talk about attribution. We then start to talk about different attribution solutions like Google attribution, Visual IQ etc.

Attribution is not just online. It was great to learn the new way offline attribution is measured and the advancements happening around POS attribution and TV advertisements.

Another key part is to maintain uniformity in using the attribution models. Though, it’s not more about the math but more about the uniformity in understanding the consumer. Furthermore, understanding the need for attribution depends on the stage of the company, skills at hand and technology investment. It is after-all understanding the touchpoints of a consumer leading to the conversion, understanding the number of times a consumer interacted with the brand before conversion.

We then dived into the attribution models and touched upon visits (last conversion where visits leads to conversion). Attribution is going to visualize what happened before that and we’re going to simply show models in a four touch vision.

However, both of them are not that great at understanding the complete journey of the consumer (awareness, behaviour part)

3. Linear Attribution: Distributing the weight to every interaction equally. However, this doesn’t help us as this is always equal for every interaction.

4. Time decay: This model has a lot of value for many organisations. This reduces the weight allotted to an interaction depending on the time when that interaction happened to when the conversion happened.

5. Position based: Here the first and last get the major value and the minor value is distributed to the middle interactions. This is one of the most helpful one as it actually gives us the understanding of the challenges of acquisition and the challenges of conversion.

6. Custom Attribution: This is the model where every touchpoint can be set to have a different value. However, this is very dangerous as it gets the marketer to decide what channel has what value. Hence, there are a lot of machine learning being used to decide this.

The way we want to do things moving forward and the way the industry is going, is using an algorithm or machine learning to define what we’re giving value to within that journey.

We then moved to tactical understanding against every channel

Understanding the strategic shift due to attribution

Thoughts:

These modules followed a short packed content approach which I really liked as compared to long 50–55min sessions. This made sure that the attention doesn’t dwindles and coupled with the amazing trainers it was a treat to learn from them. Attribution was an eye opener in terms of the depth that it touched upon.

If this makes you interested in growth marketing and you wish to learn more, I can definitely recommend CXL Institutes’s Growth Marketing Minidegree or any of their other courses.

--

--

Startup Growth | Business Development and Sales | Creative thinker

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alakshyendra

Startup Growth | Business Development and Sales | Creative thinker