Quick Pivot Table Tutorial in Google Sheets | Part 3

Pivot Tables are great for quick Analysis WITHOUT using Formulas. You simply need to have your data organized in Columns and can start using the Pivot Table functionality in Google Sheets.

In this video we are going to discover how to use Pivot Tables for Data Analysis in Google Sheets

๐Ÿ”— Links mentioned in the video:
Sheet with Demo Data: https://docs.google.com/spreadsheets/d/1TeTfO6G0zj9aGSSVgh5nZ13FSXKl2JAkHxNMPQT4pps/copy

#PivotTable
#GoogleSheets
#DataAnalysis

๐ŸŽ“ Learn more from Measureschool: http://measureschool.com/products

๐Ÿš€Looking to kick-start your data journey? Hire us: https://measureschool.com/services/

๐Ÿ“š Recommended Measure Books: https://kit.com/Measureschool/recommended-measure-books

๐Ÿ“ท Gear we used to produce this video: https://kit.com/Measureschool/measureschool-youtube-gear

๐Ÿ‘ FOLLOW US
Facebook: http://www.facebook.com/measureschool
Twitter: http://www.twitter.com/measureschool

In this video, we’re going to talk about how you can use pivot tables within Google Sheets for your data analysis. All and more coming up.

Hey there and welcome back to another video of measureschool.com teaching you the data driven way of digital marketing. My name is Julian and today we want to take a look at pivot tables within Google Sheets for our data analysis.

Now this is a part of our ongoing series on Google Sheets and data analysis. And we already talked about the different mechanisms of analyzing data within Google Sheets. We talked about functions, we talked about sorting, we talked about quick sum functions and so on and so on. But pivot tables is another tool set that let’s us do quick analysis, which we want to discover today. Now we got lots to cover, so let’s dive in.

All right, back in our Google Sheets, we have our data from last time here. In order to do pivot tables, the data needs to be organized in a certain way. And I just found that this data doesn’t really work too well with a pivot table, so I’ve come up with a little easier example here in a new sheet where we have just a few rows here and the data grouped by date, the item SKU that was sold, for example, the category, quantity, price, and revenue. Now your data needs to be organized in columns in that way because for the pivot table, the header is really playing an important part in how to organize it later on. Now in order to build a pivot table, you just need to click into one of the cells of your data. And then can go up here to data and click on pivot table. This will open up a new sheet here that you can freely rename if you choose so. And you see this new table, consisting of rows and columns. And these are freely configurable via this menu right here.

Now what can we actually do with this? We can first of all, choose our data that we want to build our pivot table on, and in our case, that is in our sales report right here. All that data was automatically pulled right in. We can then add rows and columns and we have our column headers here available. So for example, for the rows you would like to have the date, and in our column, we want to have the different categories that were sold. now we just need to fill this all with data and this is where the values come in. So click on add here and let’s say we want to have the full revenue. And already, you can see that it gives us quick analysis here because we have this grand total row at the end where we can see what category has made us the most money in these given dates. So we can see here, from our three categories, the hats seem to be doing really well. Now we also have this grand total column here at the end. That actually let’s us see which day was the most successful. So here we can go with the 11th where we made most of our revenue. And of course our full revenue for these different days over all the categories. So without using any kind of formulas, we have now been able to see which category performs the best, which day performed the best and the full revenue.

Now the cool thing is that we can actually drill down deeper into the data by adding more rows or columns. So for example, if you wanted to get a closer look at the categories and see what actual SKU numbers, what items were sold, we can click here on add and add the SKU numbers and that will give us these menus here where we can open and close our different categories. So for example, the hats here, we can look at the different SKU numbers and see how much revenue on which given day they have generated. If you wanted to change this all around, because we actually want to look at this in a more natural way, that actually opens up to the bottom, we can simply switch the rows and columns and that is simply done by dragging and dropping. So we can put the date here. Let’s put our category here and our SKU numbers, as well. Now we have that here opening up to the bottom and you can see quite nicely, how our pants performed, on which given day again, and which SKU number was most successful. There was this SKU number, on the 11th has sold this many of revenue. But also this is the total revenue of that day. You could also add the quantity here. For example, let’s say in the values, you would like to add quantity. So now we have even more overview, more detail in our data. You can see how many of these SKU’s, so for example, this pants here was sold one time and therefore generated that much revenue.

So I hope you can see how you can expand the data set, drill down even further, depending on the question you might have that comes up through your data analysis already. Now looking at this data, we could even go further into changing the actual calculations here. So this is all summarized data. And we might want to have the average volume here. So we could change this around. So now we are looking at averages. Or as you might see, minimum, maximum, the median, and so on. So really flexible, without using any kind of formulas to do quick calculations. And the last menu point down here, we can see the filter menu. So if you only wanted to look at one given date, we could also achieve that. Let’s clear this all and just look at this date here. And our table would collapse into numbers that are only derived for this particular date. So really nice, flexible tool to combine and match data and get to your answer quite fast with a pivot table.

The one thing I need to mention is that you can’t actually change any data inside of this pivot table. You would need to go back to your sales report and actually change your underlying data in order to change data in this pivot table. It’s also not possible to actually change around the values here. You can change the category apparently. But once you have a grayed-out field, you need to change the underlying data. Now obviously, you could also build something around this pivot table. But again, if it expands or collapses, then that data might be overwritten. So really use this as a analysis tool rather than a visualization tool within your Google Sheets. All right, I hope you can see now how pivot tables can really be another tool that you can use for data analysis. Ask a question, find out the answer with a quick analysis in pivot tables.

Now I would encourage you to actually check out pivot tables. Go ahead and take some data and then try out these pivot tables. Put it into rows, into columns and change around the data and see what you can do with the data. Really think through it. It takes a little bit of time to getting used to. And if you need a little bit of data, then I’ve linked up this sheet with the data that we’ve used today in this lesson down below as well. So that’s already it with this week’s lesson.

Now if you like this video and found it useful, then please give us a thumbs up and also share to a friend or a colleague. That would really help us out to grow this channel and make more awesome videos, as well. And if you want to follow along on our journey, then head over to the subscribe button right over there because we’ll bring you new videos just like this one, every week. Now my name is Julian, until next time.

SHOW MORE...

Introduction to Formulas in Google Sheets | Part 2

Formulas in Sheets make up powerful tools to analyse data dynamically in a Google Sheets. In this video we take a look at how we can use them to do basic data analysis and where you can find more formulas.

#GoogleSheets
#Formulas
#Measure

๐Ÿ”— Links mentioned in the video:

All Sheets formulas: https://support.google.com/docs/table/25273?hl=en
๐ŸŽ“ Learn more from Measureschool: http://measureschool.com/products

๐Ÿš€Looking to kick-start your data journey? Hire us: https://measureschool.com/services/

๐Ÿ“š Recommended Measure Books: https://kit.com/Measureschool/recommended-measure-books

๐Ÿ“ท Gear we used to produce this video: https://kit.com/Measureschool/measureschool-youtube-gear

๐Ÿ‘ FOLLOW US
Facebook: http://www.facebook.com/measureschool
Twitter: http://www.twitter.com/measureschool

SHOW MORE...

Quick Data Analysis with Google Sheets | Part 1

Spreadsheet software like Excel or Google Sheets are still a very widely used toolset for analyzing data. Sheets has some built-in Quick analysis features that can help you to get a overview on your data and very fast get to insights.

#DataAnalysis
#GoogleSheet
#measure

๐Ÿ”— Links mentioned in the video:
Supermetrics: http://supermetrics.com/?aff=1014
GA Demo account: https://support.google.com/analytics/answer/6367342?hl=en
๐ŸŽ“ Learn more from Measureschool: http://measureschool.com/products

๐Ÿš€Looking to kick-start your data journey? Hire us: https://measureschool.com/services/

๐Ÿ“š Recommended Measure Books: https://kit.com/Measureschool/recommended-measure-books

๐Ÿ“ท Gear we used to produce this video: https://kit.com/Measureschool/measureschool-youtube-gear

Our tracking stack:
Google Analytics: https://analytics.google.com/analytics/web/
Google Tag Manager: https://tagmanager.google.com/
Supermetrics: http://supermetrics.com/?aff=1014
ActiveCampaign: https://www.activecampaign.com/?_r=K93ZWF56

๐Ÿ‘ FOLLOW US
Facebook: http://www.facebook.com/measureschool
Twitter: http://www.twitter.com/measureschool

SHOW MORE...

"

How to get started with Google Apps Script (feat. Ben Collins)

How to get started with Google Apps Script (feat. Ben Collins)

How can you get started with Google Apps Script?

Google Apps Script allows you to create add-ons for Sheets, Docs, or Forms, automate your workflow, integrate with external APIs and a lot more.

In this episode we invited Data Analyst and Apps Script Expert Ben Collins and asked him how he got started in using App Script when developing solutions that helped his clients to automate their workflows.

#GoogleAppsScript
#Interview
#MarketingTech

๐Ÿ”— MENTIONED LINKS

Full length video: http://techmarketer.io/bencollins
Benโ€™s Website: http://benlcollins.com
Benโ€™s Dashboard Course: http://bit.ly/2qBFCXu
Ben on Twitter: http://twitter.com/benlcollins

Apps Script Developer Docs: https://developers.google.com/apps-script/overview
How to pull in mailchimp data into Apps Script video: https://www.youtube.com/watch?v=OnsxGxgNVKY
Beginners Guide to starting Apps Script: http://bit.ly/2sgKaUk
Google Developerโ€™s Channel: http://bit.ly/2r6PCLM
Totally Unscripted Show: http://bit.ly/2riURs6
Google Cloud Conference Videos: http://bit.ly/2sgY60n
Google Apps Script Forum: http://bit.ly/2rQHOPq

๐Ÿ‘Œ MORE FROM MEASURESCHOOL
GTM Resource Guide: http://measureschool.com/guide
Free GTM Beginner course: https://measureschool.com/emailcourse
Courses: http://measureschool.com/products

๐Ÿš€Looking to kick-start your data journey? Hire us: https://measureschool.com/services/
๐Ÿ“—Recommended Books: https://kit.com/Measureschool/recommended-measure-books
๐Ÿ“ธ Gear we use to record these videos: https://kit.com/Measureschool/measureschool-youtube-gear

๐Ÿ’ฌ FOLLOW US
Facebook: http://www.facebook.com/measureschool
Twitter: http://www.twitter.com/measureschool

– In this video David is gonna tell you what formulas to use to analyze your data in Google Sheets. All and more coming up right after this. Hi there and welcome to another video of MeasureSchool.com where we teach you the data-driven way of digital marketing. My name is Julian and on this channel we do marketing tech reviews, tutorials, and give you tips on better analysis just like this one. So if you haven’t yet, consider subscribing. Now, you might know I am a big fan of Google Sheets because I can take data from different datasets, such as Google Analytics, Facebook, AdWords, and put them all together in one Google sheet, then analyze my data there and build a dashboard of it as well. Now, once you pull in all that data from these different sources, you will probably have a spreadsheet of raw data and then you need to work with that data in order to get it in the right format, get insights out of it, and maybe also build a dashboard of that. And the tools that you are using to do all this are formulas. Now, I’ve asked my friend David from Coding For Losers, he also has a YouTube channel right over there, to come up and answer the question what are the most used formulas for marketers nowadays to do analysis within Google Sheets, and here’s what he came up with. David, take it away.

– I’m not that good at guitar, just started learning, but to me learning guitar and learning Google Sheets, you know, when I first started learning it five or six years ago, they’re really similar, similar to learning anything. You have to put in time practicing, or else you won’t get any better. And there’s learning curve. You really have to kind of learn things in a specific order, otherwise you really end up confusing yourself, more than you really have to, and having less fun. And that’s the third thing. If you don’t have fun doing it, if you don’t have fun practicing and you don’t have fun moving up that learning curve, you won’t enjoy doing it and it’s, honestly, not even worth doing. So, stick to Excel if you feel like you get it and you don’t want to get into another learning curve and have fun with it. But Google Sheets, over the last couple of years, has really blossomed in terms of what it can do. You can now use all kinds of add-ons, like Blockspring or Supermetrics, or the Google Analytics add-on. It seems like every service now, there’re so many different add-ons that you can use to pull data into sheets and really make use of it there, without having to do CSV exports and all that kind of clunky stuff. And on the other side, in terms of like display of data, Google Data Studio is now this really great free, sexy dashboarding tool and reporting tool. So, you can use Google Sheets as this kind of hub to sit in the middle of the work that we do as digital marketers, pull data in using add-ons, use sheets as your hub to calculate all your metrics and all your changes overtime and all of your stats, all the stuff you want to look at, and then push them up to Data Studio so you have a nice, clean dashboard there to share with your team or other kinds of reports. So, Google Sheets has really become the Swiss Army knife, I would say, for us, digital marketers, anyone working in startups or tech, recently, over the last couple years. But, there is that learning curve. There is a fair amount that you need to learn to be comfortable and be fluent working in Google Sheets. So, let’s talk about Google Sheets formulas specifically and kind of how you can limit your vision in Google Sheets formulas, just learn the ones that you really need to know upfront and potentially never even expand your vision outside of this set of, I would say, about 10 formulas that you really need to know to kick ass in Google Sheets. The first one to narrow your focus, when thinking about sheets formulas, are to think about the jobs they do, because they really only perform, as I’ve seen, three jobs, and those three jobs are: they’re wrangle data in terms of plucking out just the pieces of data that you need removing everything else. They snipe data for you, so something like VLOOKUP would be sniping out just the value that you need. And they count, they help you come up with all of the averages and sums and counts for all the metrics that you need to calculate, the kind of aggregation functions. So those are the three jobs really, and once you think about formulas in that way you can pick a few formulas per job and forget about all the rest of them. So, if you have QUERY you don’t need COUNTIF and examples like that. So the first job of formulas, wrangling data. Those are doing things like processing text and dates, turning a date string into just a year or a month, filtering data that you don’t need, and merging data together so taking data that’s in two tabs and putting it into one tab. And there’re a few formulas that we use to do that. For processing text we’ll use things like, formulas like LEFT and RIGHT, the TEXT function itself for working with dates frequently, date formulas like TODAY. So if you want to calculate the last week you can always do like TODAY minus seven, stuff like that. For filtering data I really frequently use IF and IFERROR, which are kind of logic functions but let you, say you have a referring URL path and you want to boil it down to a channel, you can use IF statements to classify certain texts in URL as a channel in the UTM tags. Just one kind of offhand example. Merging data, I’ll often use formulas like ARRAYFORMULA to pull together large datasets into one tab, and also these curly braces that you see here are really really helpful in Google Sheets, and we’ll get into why later. So the second job of formulas is sniping. That’s you have a specific value you’re looking for from a table of other values and you want to look it up. So, the most common usage of sniping is the VLOOKUP formula and I also, in times where VLOOKUP doesn’t work, say you’re looking, you want to look left in a table versus to the right, you can use INDEX for that instead. So VLOOKUP and INDEX are really kind of the same type of formula. And the last job of formulas is counting. Now, this is where you can get kind of crazy with all the different formulas you want to use, you can use. You can combine things like FILTER with COUNT or SUMIF, but I honestly choose to just use queries for counting. For all averages, counts, sums, max, min, all of this aggregation work I hire query to do that job. So now you’re up to speed on the three jobs that formulas do, wrangling, sniping, and counting, and about 10 formulas that you can use to do those jobs in Google Sheets. So hopefully, this will really help narrow your focus when you’re learning sheets formulas and make it a lot more fun to get started. There is a cheat sheet that Julian will link to in this video in the description and you can use that cheat sheet to really dive into the needy-greedy of learning all these formulas, work through a bunch of examples, and get some of that fun practice that I talked about with guitar. Take care.

– Alright, so there you have it. These are the 10 formulas that you should know as a marketer to analyze your data in Google Sheets. Pretty interesting, right? Now, we’ve got the links that David mentioned in the description below, but he also did a video on how to use these formulas on his YouTube channel right over there, and you can also probably click right here right now to view this video. Now if you haven’t yet, consider subscribing to our channel over there, because we’ll bring you new videos every Wednesday. My name’s Julian. Till next time!

SHOW MORE...

"

Google Sheets Formulas Every Marketer Needs (feat. David Krevitt)

Google Sheets Formulas Every Marketer Needs (feat. David Krevitt)

What are the most used formulas for marketers to do data analysis?

Google Sheets can be a great tool when it comes to collecting data from different sources. It gives you the opportunity to connect to a variety of tools like Google Analytics and Google AdWords and allows you to pull in the data from there, then analyze it and build a dashboard of it.
To get your raw data into the right format you need something that is called โ€˜formulasโ€™.

In this video, we invited David from Coding for Losers to teach you what formulas you really need to analyze data in Google Sheets.

#Formulas
#GoogleSheet
#Measure

๐Ÿ‘‰ Links mentioned in the video

[SHEET] http://bit.ly/2qaQAYq
[YT CHANNEL] Coding for Losers: http://bit.ly/2qNrjAP
[VIDEO] Google Sheets Formula Hacks for Marketers: http://bit.ly/2rsD2aw
Google Sheets: https://www.google.com/sheets/about/
Blockspring: https://www.blockspring.com/
Supermetrics: https://supermetrics.com/
Google Data Studio: https://www.google.com/analytics/data-studio/

๐Ÿ‘‰ More from Measureschool

Correct Google Analytics Setup Course: https://measureschool.com/products/google-analytics-course/
GTM Resource Guide: http://measureschool.com/guide
Free GTM Beginner course: https://measureschool.com/emailcourse
Courses: http://measureschool.com/products

๐Ÿš€Looking to kick-start your data journey? Hire us: https://measureschool.com/services/

๐Ÿ“š Recommended Measure Books: https://kit.com/Measureschool/recommended-measure-books

๐Ÿ“ท Gear we used to produce this video: https://kit.com/Measureschool/measureschool-youtube-gear

๐Ÿ‘‰ FOLLOW US

FACEBOOK: http://www.facebook.com/measureschool
TWITTER: http://www.twitter.com/measureschool

– In this video David is gonna tell you what formulas to use to analyze your data in Google Sheets. All and more coming up right after this. Hi there and welcome to another video of MeasureSchool.com where we teach you the data-driven way of digital marketing. My name is Julian and on this channel we do marketing tech reviews, tutorials, and give you tips on better analysis just like this one. So if you haven’t yet, consider subscribing. Now, you might know I am a big fan of Google Sheets because I can take data from different datasets, such as Google Analytics, Facebook, AdWords, and put them all together in one Google sheet, then analyze my data there and build a dashboard of it as well. Now, once you pull in all that data from these different sources, you will probably have a spreadsheet of raw data and then you need to work with that data in order to get it in the right format, get insights out of it, and maybe also build a dashboard of that. And the tools that you are using to do all this are formulas. Now, I’ve asked my friend David from Coding For Losers, he also has a YouTube channel right over there, to come up and answer the question what are the most used formulas for marketers nowadays to do analysis within Google Sheets, and here’s what he came up with. David, take it away.

– I’m not that good at guitar, just started learning, but to me learning guitar and learning Google Sheets, you know, when I first started learning it five or six years ago, they’re really similar, similar to learning anything. You have to put in time practicing, or else you won’t get any better. And there’s learning curve. You really have to kind of learn things in a specific order, otherwise you really end up confusing yourself, more than you really have to, and having less fun. And that’s the third thing. If you don’t have fun doing it, if you don’t have fun practicing and you don’t have fun moving up that learning curve, you won’t enjoy doing it and it’s, honestly, not even worth doing. So, stick to Excel if you feel like you get it and you don’t want to get into another learning curve and have fun with it. But Google Sheets, over the last couple of years, has really blossomed in terms of what it can do. You can now use all kinds of add-ons, like Blockspring or Supermetrics, or the Google Analytics add-on. It seems like every service now, there’re so many different add-ons that you can use to pull data into sheets and really make use of it there, without having to do CSV exports and all that kind of clunky stuff. And on the other side, in terms of like display of data, Google Data Studio is now this really great free, sexy dashboarding tool and reporting tool. So, you can use Google Sheets as this kind of hub to sit in the middle of the work that we do as digital marketers, pull data in using add-ons, use sheets as your hub to calculate all your metrics and all your changes overtime and all of your stats, all the stuff you want to look at, and then push them up to Data Studio so you have a nice, clean dashboard there to share with your team or other kinds of reports. So, Google Sheets has really become the Swiss Army knife, I would say, for us, digital marketers, anyone working in startups or tech, recently, over the last couple years. But, there is that learning curve. There is a fair amount that you need to learn to be comfortable and be fluent working in Google Sheets. So, let’s talk about Google Sheets formulas specifically and kind of how you can limit your vision in Google Sheets formulas, just learn the ones that you really need to know upfront and potentially never even expand your vision outside of this set of, I would say, about 10 formulas that you really need to know to kick ass in Google Sheets. The first one to narrow your focus, when thinking about sheets formulas, are to think about the jobs they do, because they really only perform, as I’ve seen, three jobs, and those three jobs are: they’re wrangle data in terms of plucking out just the pieces of data that you need removing everything else. They snipe data for you, so something like VLOOKUP would be sniping out just the value that you need. And they count, they help you come up with all of the averages and sums and counts for all the metrics that you need to calculate, the kind of aggregation functions. So those are the three jobs really, and once you think about formulas in that way you can pick a few formulas per job and forget about all the rest of them. So, if you have QUERY you don’t need COUNTIF and examples like that. So the first job of formulas, wrangling data. Those are doing things like processing text and dates, turning a date string into just a year or a month, filtering data that you don’t need, and merging data together so taking data that’s in two tabs and putting it into one tab. And there’re a few formulas that we use to do that. For processing text we’ll use things like, formulas like LEFT and RIGHT, the TEXT function itself for working with dates frequently, date formulas like TODAY. So if you want to calculate the last week you can always do like TODAY minus seven, stuff like that. For filtering data I really frequently use IF and IFERROR, which are kind of logic functions but let you, say you have a referring URL path and you want to boil it down to a channel, you can use IF statements to classify certain texts in URL as a channel in the UTM tags. Just one kind of offhand example. Merging data, I’ll often use formulas like ARRAYFORMULA to pull together large datasets into one tab, and also these curly braces that you see here are really really helpful in Google Sheets, and we’ll get into why later. So the second job of formulas is sniping. That’s you have a specific value you’re looking for from a table of other values and you want to look it up. So, the most common usage of sniping is the VLOOKUP formula and I also, in times where VLOOKUP doesn’t work, say you’re looking, you want to look left in a table versus to the right, you can use INDEX for that instead. So VLOOKUP and INDEX are really kind of the same type of formula. And the last job of formulas is counting. Now, this is where you can get kind of crazy with all the different formulas you want to use, you can use. You can combine things like FILTER with COUNT or SUMIF, but I honestly choose to just use queries for counting. For all averages, counts, sums, max, min, all of this aggregation work I hire query to do that job. So now you’re up to speed on the three jobs that formulas do, wrangling, sniping, and counting, and about 10 formulas that you can use to do those jobs in Google Sheets. So hopefully, this will really help narrow your focus when you’re learning sheets formulas and make it a lot more fun to get started. There is a cheat sheet that Julian will link to in this video in the description and you can use that cheat sheet to really dive into the needy-greedy of learning all these formulas, work through a bunch of examples, and get some of that fun practice that I talked about with guitar. Take care.

– Alright, so there you have it. These are the 10 formulas that you should know as a marketer to analyze your data in Google Sheets. Pretty interesting, right? Now, we’ve got the links that David mentioned in the description below, but he also did a video on how to use these formulas on his YouTube channel right over there, and you can also probably click right here right now to view this video. Now if you haven’t yet, consider subscribing to our channel over there, because we’ll bring you new videos every Wednesday. My name’s Julian. Till next time!

SHOW MORE...

"

How to pull Mailchimp Data into Google Sheets with Google Apps Script

How to pull Mailchimp Data into Google Sheets with Google Apps Script

Universal Event Tracking is Bingโ€™s tracking script to track conversions and setup Remarketing on the Bing Ad Platform. The UET Tag can be deployed easily with GTM to track pageviews and interactions.

In this video, we’re going to show you how to install Bing’s UET tag on your website with the help of Google Tag Manager.

#BingAds
#ConversionTracking
#GoogleTagManager

๐Ÿ‘‰ Helpful Links

UET Tag Helper: https://chrome.google.com/webstore/detail/uet-tag-helper-by-bing-ad/naijndjklgmffmpembnkfbcjbognokbf
Bing Ads: https://secure.azure.bingads.microsoft.com/
Google Tag Manager: https://www.google.com/analytics/tag-manager/
Our GTM Playlist: https://goo.gl/MfGcRR

๐Ÿ‘‰ More from Measureschool

GTM Resource Guide: http://measureschool.com/guide
Free GTM Beginner course: https://measureschool.com/emailcourse
Courses: http://measureschool.com/products

๐Ÿš€Looking to kick-start your data journey? Hire us: https://measureschool.com/services/

๐Ÿ“š Recommended Measure Books: https://kit.com/Measureschool/recommended-measure-books

๐Ÿ“ท Gear we used to produce this video: https://kit.com/Measureschool/measureschool-youtube-gear

๐Ÿ‘‰ FOLLOW US

FACEBOOK: http://www.facebook.com/measureschool
TWITTER: http://www.twitter.com/measureschool

– How can you get your Mail Chimp campaign data into a Google Sheet? Well, in this video we’re going to show you how to build an app script that connects to the Mail Chimp API and pulls the campaign information into Google Sheets so you can analyze and visualize it. All the more, coming up right after this. Hi there and welcome to another video of Measure School.com where we teach you the data driven way of digital marketing. My name is Julian and on this channel we do marketing tech reviews, how to videos, and tutorials, just like this one. So if you haven’t yet, consider subscribing. Now pulling all your data together so you can analyze it and visualize it in one tool is sometimes really hard to do.

Now as you might now, I’m a big fan of Google Sheets because it lives in the cloud and we can connect to different tools to pull the data in regularly. But unfortunately there’s not always a direct connection between the tools that you want to get the data from and pull them into your spreadsheet so you can analyze them. And this is where Appscript comes in.

Appscript is like VBA for Excel where you can write a little bit of Javascript and pull the data in from various sources. So lately I’ve gotten a lot of questions how you can pull data from Mail Chimp into Google Spreadsheets. So I asked my friend Ben Collins who’s really knowledgeable in Appscript, Google Sheets, but also data analysis, to come up with a solution here for us. Now you can check out his stuff at his YouTube Channel, Ben Collins, but also at his blog Ben Collins.com and today Ben is going to show us how we can build a little bit of an Appscript to pull data from the Mail Chimp API and import it to Google Sheets so we can analyze our campaigns there. We’ve got lots to cover, so Ben take it away.

– [Ben] Hi everyone. This is Ben from benlcollins.com. And today I’m going to show you how to bring your Mail Chimp data into a Google Sheets using their API. So open up a blank sheets and then rename it to Mail Chimp for this project. What I’m going to do is set up the headings in the Sheets, then we’ll open up the script editor and start writing the code and then we’ll jump into Mail Chimp to get the API key that I need and then come back to the Sheets to finish it all off. So that’s the strategy. In the interest of time, I’m just going to copy in these headings from another sheet that I’ve been working on. And I’ll just show you what they look like. I’m going to wrap the text there and center them all. So these are the details, the data that I want to get from the API. For example, when the campaign was sent, what the title was, the subject line, how many recipients, the clicks, the open rate, that sort of stuff, and then finally, actually we can calculate these two.

These are going to be calculations that we’re going to use then to create a chart showing the performance of all the campaigns over time. That’s the idea with this analysis. So let’s go and open up the Script Editor. So I go to Tools, to Script editor, that, that opens up. You can immediately just clear out the code that’s there. We don’t need that stuff. And again, let’s just rename this project Mail Chimp. And then I’m going to start by just creating the necessary API key and list ID that I need to then run all my code so let’s just for the moment put these in and then we’re going to actually retrieve them from Mail Chimp. So I need an API key and then a list ID as well.

We’re going to go into Mail Chimp now and get those two. So open up a new tab, open up Mail Chimp, log in. And then Choose a profile, and under extras here is this tab called, “API Keys.” And then you come down here to create a key, click that, it’s going to create a new key for you and it will be this new one at the top here. So let’s just click that one and rename it to be “mailchimpsheets” or some other name and then its ready use.

What we’re going to do is copy that key there and insert that in between these two little hyphens here which gives me my API key as a text string ready to use in my application here. Now it’s really important that you don’t actually give this out to anyone which is why it’s blurred on my screen right now, you won’t be able to see that one.

Because anyone that has that API key can write a little bit of code then to actually infiltrate your email list and delete subscribers or add subscribers or send out spam campaigns. So it’s really important that only you have access and only you see this API key, so don’t share that around. It’s perfectly okay to have it here in your code but don’t save this code, for example, onto the web without removing your API key first. Now the other thing we need is this List ID so let’s go back to Mail Chimp. Let’s go to the menu here to Lists. Navigate to your list and then click on your list here, open the list up and under settings, you’ll see this one List name and defaults, just click that.

And then here under List ID, you’ll see the List ID here. This one here. So, we’re going to need that number as well but that’s just very easy to get under Lists, Settings. And I’m just going to add that in there and that’s me ready to go now with the two pieces of code I need to make this function run, or this application run. So what I’m going to do now is actually create a function.

We’re going to create one function initially that just retrieves some data from the API and I’m going to take a look at it and see what I get back and then from there we can start to actually build out the analysis that we want to do. What I’m going to do is just actually move to the compact controls here so we get a bit more space. And I’m just going to copy in some code there so you’ll see just under the List ID, I’ve copied in this code, just some comments to start with. Then a function called Mailchimp Campaign where I specify the root of the API and then the end points of the API that I want to access.

And then this is the really important one here, the parameters I need. The important one is this API key here. You can see this. What this does is when we request the data from Mail Chimp, we pass it the API key we specified up here at the top and that’s what allows Mail Chimp, that’s what lets Mail Chimp know that we’re a legitimate application and we’re okay to receive some data from them.

Let’s just put this in. So what this is going to do is call the API, and then just pass the data that’s returned and then we’ll be able to display the output here in this Logger function. So let’s just close that function off. Hit save, and then what we’re going to do now is run this function called mailchimpCampaign and it’s going to ask us for permission first because it hasn’t done this before so it needs to access our data. Let’s see review, and it wants to connect to an external service, that’s fine, we’ll click on Allow. We come up to View the Logs, we should see some stuff in there so let’s click that. And there we go, we can actually now see this is what the Mail Chimp API is returned to us. It’s obviously a lot of data, it’s kind of difficult to read in this format but you can see if we look in a little bit of detail there’s things like, there for example, send_time.

As a next step, let’s just go ahead and get a little bit of the campaign data that we actually want and just, again, we’ll just log it. I’m just going to say find out how many campaigns I have. And then, also, I’m going to extract all the subject lines from those campaigns and display them. So let’s run it. Let’s view logs again. And there we go. This time 27 campaigns. And you can see the subject lines of the different campaigns I’ve run so it looks like that’s great. It’s working now. It’s now pulling out specific pieces of data that we want and that’s exactly the sort of data we’re going to now put into the Google Sheet here. So what I want to do is just put the word tripe here and all of this stuff here, I’m just going to tab in which will just move, indents it.

And then we’re going to do a catch and this catch is the real way we should be making sure that we catch any errors, we try this and if we fail at some p

SHOW MORE...