Here's a quick how-to on getting an account set up. It only requires a gmail account: https://cloud.google.com/bigquery/quickstart-web-ui 

Here is a query to access the dataset: SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` 

This dataset contains example analytics data. Not all of it will be relevant to analysts in digital marketing, so don't worry if all of the columns don't seem relevant to the work. 

Some tips and info:

 ● BigQuery reference docs are pretty solid: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

 ● There's two BigQuery SQL dialects: "Legacy" and "Standard". Use "Standard" unless there's a good reason to use "Legacy". 

● BigQuery allows you to query your first terabyte free. Stay conscientious of data queried. This should be enough for the assessment. 

● There's a circular "validator" icon under the query window on the right that tells you how much data you're about to query. Click this to see why your query is not valid.

 ● You can "Preview" the entire table without paying for it. This is great for getting a feel for the columns and data. 

● There's no right way to do anything below. This is an ad-hoc effort, so go with whatever works! For the below data questions, please provide the queries used and a summary of the findings. Any format you prefer is fine. Answer whatever you can. Feel free to shoot over any questions you have! ------------------------------------------------------------------------------------------------ 

Back-story: You work at an advertising agency as a data analyst. A large change to your strategy was implemented in Jan 2017 and the executives want to know if the changes have positively affected performance. There is also a chance that everything went horribly wrong and there are major issues with the data. You're asked to validate that Jan & Feb 2017 data looks normal based on historical data, however the stakeholder completely forgot to tell you and you only have 1 hour before they need to present your findings.

 

 Question 1: Assuming you can't possibly validate everything in the tight time range, what are the 3 or 4 most effective queries to validate the product is properly reporting? List the queries, starting with the most impactful, and a brief explanation of your thought process and findings. 

 

Question 2: If you had more time to validate that the product and reporting is functioning properly after the big update, what are some key qualitative/quantitative checks you would perform?

 

 Question 3: Now that the stakeholder has what they need, it's time to look at performance changes. Pull out some KPIs that you think will show any performance changes across 2016 to 2017. In addition to the queries, please include an explanation of why you chose the KPIs you chose and how they indicate performance changes. As a part of this question please create a dashboard of 3-4 visualizations of the different KPIs or metrics that you have explored. Google Data Studio is a visualization tool that is free to use with the data set that you have been using. Please attempt to build out your dashboard using this platform and include a shareable link to the dashboard in the materials you provide back to us. If you are struggling to build it out in Data Studio, you may use a visualization tool you are more familiar with. In that case please include a PDF of the visualizations. In the next round of interviews you will present your dashboard and your findings to the team.

 

 Question 4 : Part 1: Please pull hits.product.isImpression, hits.product.isClick and hits.product.productRevenue broken out by trafficSource.campaign and date. Part 2: Please include a column that gives a running total over time for the isImpression, isClicks and productRevenue columns broken out by campaign and date. Ex. Date Clicks Campaign Impressions Revenue 08-01 1 x 1 1000 08-02 3 y 5 4000 08-03 6 z 7 10000