Digital Analytics Scholarship @ CXL Institute — 7th Week Review

BigQuery has taught me that if you want to be a true technical marketer, you need to learn about SQL as well. Working with data and analyzing it lacks something without SQL. BigQuery is heavily based on SQL and we are going to see what it has to teach us. Hope it is easy enough and encourages me to go learn it. Let’s start.

7- BigQuery by Khrystyna Grynko

• This is one of the courses that I’ve been waiting for from the beginning. It is about one of the big products in the google cloud platform (GCP) named BigQuery.
• The teacher is a pretty girl with a Russian accent. I’ve known her from a virtual summit called Measure Summit and she was a presenter there.
• She says that BigQuery helps you in the process of collecting data, visualizing it, and making automated data processing. Seems interesting, huh? It absolutely is.
• Believe me, I have no prior experience in working with Google BigQuery and I am just getting exposed to it. I have no idea about what is about to happen the next minute.
• BigQuery stores data in Datasets and in those datasets, we have tables in which data is actually being stored. All of these datasets and tables are located inside a project. It is kinda like the hierarchy we have in google ads which has ad groups and in those ad groups, we have some ads.
• In BigQuery, we have access to public datasets that can be seen for free and we can learn how to use this tool by them.
• Khrystyna says that we will use three tools that are available in the google cloud platform. One of them is BigQuery and the other two consist of Could Functions and Cloud Storage.
• Google will give you 300 dollars credit upon entering your credit card and it can be used within 12 months. Unfortunately, I don’t have a credit card and this free credit can’t be activated for me.
• And again, unfortunately, could function, and cloud storage can only be used after activating this $300 credit! How lucky I am!
• Anyway, so far I have learned that I have to first build a project and within that project, I have to make some datasets. Then those datasets can be filled with tables and real data resides in the tables.
• The question that is still unanswered for me is this: what is a query in BigQuery? Is it like SQL tables? Because I don’t have any experience in SQL as well and things are a little bit vague for me so far.
• The next lesson was about importing data into BigQuery. The previous steps (creating a project and dataset) are still in place. After that, you need to import your tables from a file. It could be a file on your computer or something that is on the web.
• Khrystyna used various sources like google cloud storage (which I didn’t have access to) and google drive. I also uploaded a file on my computer to BigQuery and it functioned as expected.
• One thing that you need to do in the process of uploading data is clarifying the schema. You can either tell BigQuery to automatically detect the schema or manually tell it what type of data there are in each row and column. For the latter, you need to use a JSON file that has very simple syntax (I still don’t know how to use it!).
• So far everything was simple and clear. I am eager to know how this BigQuery thing works. Khrystyna once used querying, but I still don’t know how it works.
• Oh, I forgot to say that the tutor used google could functions to automate uploading files to BigQuery. She used a python code to tell cloud functions every time a specific file is uploaded to cloud storage, upload it to BigQuery as well.
• Now is the time for real calculation. BigQuery is using SQL functions and among the most useful of them are SELECT, FROM, and WHERE.
• For filtering the data in BigQuery, we can use WHERE and HAVING. But HAVING can be used when the calculation is done lately. Before using HAVING, you have to use GROUP BY to group every column again.
• You can also merge two different tables using UNION ALL. for using UNION ALL, you have to write two lines of code with select and from.
• We can also query a query! Isn’t it practical? We say WITH UNION_TABLE AS and then, put the query inside parentheses. After the closing parentheses, we can write another query with a new set of SELECT and FROM statements.
• If you want to join two tables in BigQuery, it is rather easy to do. Just use JOIN and after that, reference the second table. After that, use ON and mention the column from table a = column from table b. It is a bit hard to explain, you have to see it in action.
• Once you make a complex query, you can save it with a name for future usage. It is excellent that you can save queries at a personal level and also at a project level. The latter will allow your teammates to access the queries you have saved.
• Besides saving queries, you can save views that are like google analytics views and they have your queries saved in them.
• You can also build scheduled reports that repeat themselves at certain times. You can set the time of reports and what they need to do at that time.
• I really enjoyed this lesson, although it was very heavy and hard to swallow. It definitely needs rewatching again and again.
• Khrystyna talked about something called nested fields which I didn’t understand that much. But I think it was clear that all the data that is coming from a tool like Google analytics is nested.
• The next query that I learned about in this course was date queries. The ones that you need to specify a metric based on date. I don’t know why Khrystyna used “_TABLE_SUFFIX” in the condition, but it worked perfectly. You need to know that date in BigQuery is presented in this form: YYYYMMDD.
• If you want to rename a column after specifying its formula (rule), you can use “as” and after that, put your desired name. It will change the name of the column.
• And after that, the tutor used a formula that she copied from google documentation and I didn’t understand the meaning. It had an UNNEST function and was pretty unclear to me.
• And then, we went through the fun part of the visualization. Khrystyna used google sheets and google data studio to show us how to import data from BigQuery into those tools.
• In google sheets, she used an item in the data menu which directly connected sheets to BigQuery. But she said that this option is only available for 360 users and free users don’t have access to it.
• But connecting BigQuery to GDS was very easy. Just selecting BigQuery in GDS and starting to use the data was very straightforward. I like Data Studio like nothing else. You can also import data from BigQuery into tools like Tableau and PowerBI as well.
• Finally, Khrystyna advised us to use google BigQuery frequently and efficiently. Denormalizing data and putting all of your data in one table was repetitive advice here.
• And now we’ve come to the end of this course. It was an introductory course to merely get us excited about BigQuery and pique our interests toward learning and using SQL language in it.
• I actually liked it and think that it did its job.

8- Facebook Analytics by Chris Mercer

• We’ve done a lot of work and again, we are here again with my lovely Chris Mercer. He likes to be called Mercer and I will call him this way in the remainder of the course.
• This course is all about Facebook Analytics and I have to admit that I don’t know anything about it. This is my first encounter with FBA and I think it will be fun because Mercer teaches it.
• Mercer says you will need Facebook analytics for your Facebook pages and Instagram accounts. These accounts are not accessible for tools like google tag manager and google analytics. You kinda are forced to learn how to use them in order to gain insights into your Facebook properties.
• Luckily, Facebook analytics has a free publicly available demo account, just like the one google analytics has. Mercer says that he is going to use this demo account for teaching this course.
• Everything in Facebook Analytics is an event. Every page view, every purchase, everything is counted as an event. Events come from sources of information and these sources can be grouped together.
• On Facebook, we have three types of groupings. One of them is Event Source Groups (ESGs) which group sources of information (Facebook pages, Instagram accounts, apps, and pixels) together. This kind of group is shareable between users and you can grant access to others. ESGs are only accessible through Facebook Analytics.
• The next type of grouping is Business Asset Groups (BAGs) which are accessible from different parts of the Facebook ecosystem. BAG and ESG are pretty much like each other, but one is more general than the other one. BAGs can also be shared with other users.
• The third kind is Private Groups (PGs) which are only limited to you as the creator. They are similar to ESGs and BAGs, except you can’t share them with others and only you can use them. Chris says these are pretty much like adding two or more properties to google analytics and doing cross-domain tracking.
• The next thing that Mercer talked about was the reports that are available on Facebook Analytics. The major part of these reports will remind you of Google Analytics. It can be said that it is the Facebook version of Google Analytics.
• The reports in Facebook analytics are divided into two major sections: people reports and activity reports. The people part is focused on the technology, demographics, and interests which is very similar to GA.
• The other part which is activity reports consists of cohorts, funnels, retention, outcomes (sth like GA’s goals). Mercer promised to go through funnels in the following lessons.
• If you google analytics very well, you will find that metrics and dimensions are available in Facebook Analytics as well. They work the same way that you have learned and this is awesome.
• The next lesson was centered on building funnels on Facebook Analytics. Mercer did his magic again in this lesson and made a simple but effective funnel in FB Analytics.
• The cool part was that the funnels went back in retrospect and applied to previous data as well. Something that we don’t have access to in google analytics.
• And it is great that you have access to your web pages, Facebook pages, and Instagram pages in one place to make funnels. I actually love it. You can say if somebody liked your Instagram post and after that, viewed your web page and then added sth to the cart and purchased. Isn’t this wizardry?
• And filters in FB analytics that work like segments in GA are cool. They are based on logical statements and can be very well understood.
• We have come to the end of this course. It was rather easy and short, but full of insights and taught me things that I didn’t know before. Facebook Analytics is a great place to look for marketing data and a lot of marketers use it on a daily basis. It gives you the data that you need as a fuel for your marketing machine.
• Facebook itself supports the tool and has rather extensive helps both for you and your developers. But you have to know what goals you are pursuing and then try to find answers for your questions.
• And that’s it. Facebook Analytics course has come to its end. See you later guys.