Monthly Archives: September 2016

Using BigQuery and Firebase Analytics to understand your mobile app

Originally posted on Google Cloud Platform Blog

At Google I/O this May, Firebase announced a new suite of products to help developers build mobile apps. Firebase Analytics, a part of the new Firebase platform, is a tool that automatically captures data on how people are using your iOS and Android app, and lets you define your own custom app events. When the data's captured, it’s available through a dashboard in the Firebase console. One of my favorite cloud integrations with the new Firebase platform is the ability to export raw data from Firebase Analytics to Google BigQuery for custom analysis. This custom analysis is particularly useful for aggregating data from the iOS and Android versions of your app, and accessing custom parameters passed in your Firebase Analytics events. Let’s take a look at what you can do with this powerful combination.

How does the BigQuery export work?


After linking your Firebase project to BigQuery, Firebase automatically exports a new table to an associated BigQuery dataset every day. If you have both iOS and Android versions of your app, Firebase exports the data for each platform into a separate dataset. Each table contains the user activity and demographic data automatically captured by Firebase Analytics, along with any custom events you’re capturing in your app. Thus, after exporting one week’s worth of data for a cross-platform app, your BigQuery project would contain two datasets, each with seven tables:


Diving into the data


The schema for every Firebase Analytics export table is the same, and we’ve created two datasets (one for iOS and one for Android) with sample user data for you to run the example queries below. The datasets are for a sample cross-platform iOS and Android gaming app. Each dataset contains seven tables  one week’s worth of analytics data.

The following query will return some basic user demographic and device data for one day of usage on the iOS version of our app:

SELECT
user_dim.app_info.app_instance_id,
user_dim.device_info.device_category,
user_dim.device_info.user_default_language,
user_dim.device_info.platform_version,
user_dim.device_info.device_model,
user_dim.geo_info.country,
user_dim.geo_info.city,
user_dim.app_info.app_version,
user_dim.app_info.app_store,
user_dim.app_info.app_platform
FROM
[firebase-analytics-sample-data:ios_dataset.app_events_20160601]

Since the schema for every BigQuery table exported from Firebase Analytics is the same, you can run any of the queries in this post on your own Firebase Analytics data by replacing the dataset and table names with the ones for your project.

The schema has user data and event data. All user data is automatically captured by Firebase Analytics, and the event data is populated by any custom events you add to your app. Let’s take a look at the specific records for both user and event data.

User data


The user records contain a unique app instance ID for each user (user_dim.app_info.app_instance_id in the schema), along with data on their location, device and app version. In the Firebase console, there are separate dashboards for the app’s Android and iOS analytics. With BigQuery, we can run a query to find out where our users are accessing our app around the world across both platforms. The query below makes use of BigQuery’s union feature, which lets you use a comma as a UNION ALL operator. Since a row is created in our table for each bundle of events a user triggers, we use EXACT_COUNT_DISTINCT to make sure each user is only counted once:
SELECT
user_dim.geo_info.country as country,
EXACT_COUNT_DISTINCT( user_dim.app_info.app_instance_id ) as users
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601],
[firebase-analytics-sample-data:ios_dataset.app_events_20160601]
GROUP BY
country
ORDER BY
users DESC

User data also includes a user_properties record, which includes attributes you define to describe different segments of your user base, like language preference or geographic location. Firebase Analytics captures some user properties by default, and you can create up to 25 of your own.

A user’s language preference is one of the default user properties. To see which languages our users speak across platforms, we can run the following query:

SELECT
user_dim.user_properties.value.value.string_value as language_code,
EXACT_COUNT_DISTINCT(user_dim.app_info.app_instance_id) as users,
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601],
[firebase-analytics-sample-data:ios_dataset.app_events_20160601]
WHERE
user_dim.user_properties.key = "language"
GROUP BY
language_code
ORDER BY
users DESC

Event data


Firebase Analytics makes it easy to log custom events such as tracking item purchases or button clicks in your app. When you log an event, you pass an event name and up to 25 parameters to Firebase Analytics and it automatically tracks the number of times the event has occurred. The following query shows the number of times each event in our app has occurred on Android for a particular day:

SELECT 
event_dim.name,
COUNT(event_dim.name) as event_count
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601]
GROUP BY
event_dim.name
ORDER BY
event_count DESC

If you have another type of value associated with an event (like item prices), you can pass it through as an optional value parameter and filter by this value in BigQuery. In our sample tables, there is a spend_virtual_currency event. We can write the following query to see how much virtual currency players spend at one time:

SELECT 
event_dim.params.value.int_value as virtual_currency_amt,
COUNT(*) as num_times_spent
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601]
WHERE
event_dim.name = "spend_virtual_currency"
AND
event_dim.params.key = "value"
GROUP BY
1
ORDER BY
num_times_spent DESC

Building complex queries


What if we want to run a query across both platforms of our app over a specific date range? Since Firebase Analytics data is split into tables for each day, we can do this using BigQuery’s TABLE_DATE_RANGE function. This query returns a count of the cities users are coming from over a one week period:

SELECT
user_dim.geo_info.city,
COUNT(user_dim.geo_info.city) as city_count
FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
GROUP BY
user_dim.geo_info.city
ORDER BY
city_count DESC

We can also write a query to compare mobile vs. tablet usage across platforms over a one week period:

SELECT
user_dim.app_info.app_platform as appPlatform,
user_dim.device_info.device_category as deviceType,
COUNT(user_dim.device_info.device_category) AS device_type_count FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
GROUP BY
1,2
ORDER BY
device_type_count DESC

Getting a bit more complex, we can write a query to generate a report of unique user events across platforms over the past two weeks. Here we use PARTITION BY and EXACT_COUNT_DISTINCT to de-dupe our event report by users, making use of user properties and the user_dim.user_id field:

SELECT 
STRFTIME_UTC_USEC(eventTime,"%Y%m%d") as date,
appPlatform,
eventName,
COUNT(*) totalEvents,
EXACT_COUNT_DISTINCT(IF(userId IS NOT NULL, userId, fullVisitorid)) as users
FROM (
SELECT
fullVisitorid,
openTimestamp,
FORMAT_UTC_USEC(openTimestamp) firstOpenedTime,
userIdSet,
MAX(userIdSet) OVER(PARTITION BY fullVisitorid) userId,
appPlatform,
eventTimestamp,
FORMAT_UTC_USEC(eventTimestamp) as eventTime,
eventName
FROM FLATTEN(
(
SELECT
user_dim.app_info.app_instance_id as fullVisitorid,
user_dim.first_open_timestamp_micros as openTimestamp,
user_dim.user_properties.value.value.string_value,
IF(user_dim.user_properties.key = 'user_id',user_dim.user_properties.value.value.string_value, null) as userIdSet,
user_dim.app_info.app_platform as appPlatform,
event_dim.timestamp_micros as eventTimestamp,
event_dim.name AS eventName,
event_dim.params.key,
event_dim.params.value.string_value
FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
), user_dim.user_properties)
)
GROUP BY
date, appPlatform, eventName

If you have data in Google Analytics for the same app, it’s also possible to export your Google Analytics data to BigQuery and do a JOIN with your Firebase Analytics BigQuery tables.


Visualizing analytics data


Now that we’ve gathered new insights from our mobile app data using the raw BigQuery export, let’s visualize it using Google Data Studio. Data Studio can read directly from BigQuery tables, and we can even pass it a custom query like the ones above. Data Studio can generate many different types of charts depending on the structure of your data, including time series, bar charts, pie charts and geo maps.

For our first visualization, let’s create a bar chart to compare the device types from which users are accessing our app on each platform. We can paste the mobile vs. tablet query above directly into Data Studio to generate the following chart:
From this chart, it’s easy to see that iOS users are much more likely to access our game from a tablet. Getting a bit more complex, we can use the above event report query to create a bar chart comparing the number of events across platforms:
Check out this post for detailed instructions on connecting your BigQuery project to Data Studio.

What’s next?

If you’re new to Firebase, get started here. If you’re already building a mobile app on Firebase, check out this detailed guide on linking your Firebase project to BigQuery. For questions, take a look at the BigQuery reference docs and use the firebase-analytics and google-bigquery tags on Stack Overflow. And let me know if there are any particular topics you’d like me to cover in an upcoming post.

Google Container Engine now on Kubernetes 1.4



Today, Kubernetes 1.4 is available to all Google Container Engine customers. In addition to all the new features Kubernetes 1.4 — including multi-cluster federation, simplified setup and one-command install for popular workloads like MySQL, MariaDB and Jenkins — we’ve also taken big steps to make Google Cloud Platform (GCP) the best place to run your Kubernetes workloads.

Container Engine has continued its rapid growth, doubling in usage every 90 days, while still providing a fully managed Kubernetes service with 99.5% uptime for applications large and small. We’ve also made a number of improvements to the platform to make it even easier to manage and more powerful to use:

  • One-click alpha clusters can be spun up as easily as a regular cluster, so testing Kubernetes’ alpha features like persistent application support is a one-click operation.
  • Support for AppArmor in the base image gives applications deployed to Container Engine multiple layers of defense-in-depth.
  • Integration with Kubernetes Cluster Federation allows you to add a Container Engine cluster to your existing federation, greatly simplifying cloud bursting and multi-cloud deployments.
  • Rich support for Google Cloud Identity & Access Management allows you to manage GKE clusters with the same multi-faceted roles you use across your GCP projects.
  • A new Google Container-VM Image makes upgrading a breeze and allows new upgrades to be automatically installed with a simple reboot.
  • Monitoring of all cluster add-ons ensures that all key functions for your cluster are available and ready to use — one less thing to think about when running a large distributed application.

From new startups to the largest organizations, we’ve seen tremendous adoption of Container Engine, here are a few unique highlights:

  • Niantic - creators of the global phenomenon Pokémon GO, relies on Container Engine to power their extraordinary growth.
  • Philips - smart connected lighting system Hue, receives 200 million transactions a day that are easily handled by Container Engine.
  • Google Cloud ML - the new Cloud Machine Learning service from GCP is also running fully on Container Engine.
  • And many more companies, from Box to Pearson, are choosing Kubernetes to manage their production workloads.

As always, if you’d like to help shape the future of Kubernetes, please participate in the Kubernetes community; we’d love to have you! Please join the google-containers mailing list or on the kubernetes-users or google-containers Slack channels.

Finally, if you’ve never tried GCP before, getting started is easy. Sign up for your free trial here.

Thank you for your support!

Introducing Google Container-VM Image



This spring, we announced Container-VM Image as a beta product under Google Cloud Platform (GCP). If you're a developer interested in deploying your application or a service provider on Google Compute Engine, we recommend taking a few moments to understand how it can help you.

Linux containers help developers to focus on their application without worrying about the underlying infrastructure. A secure and up-to-date base image is a critical building block of any container-based infrastructure. Container-VM Image represents the best practices we here at Google have learned over the past decade running containers at scale.

Container-VM Image design philosophy

Container-VM Image is designed from the ground up to be a modern operating system for running containers on GCP. Read on for more information about the design choices behind Container-VM Image and its attributes.

Build environment

Container-VM Image is based on the open-source Chromium OS project. Chromium OS is a reliable and vetted source code base for this new operating system. In addition, its allows us to use the powerful build and test infrastructure built by the ChromeOS team.


Designed for containers

The Docker container runtime is pre-installed on Container-VM Image. A key feature of containers is that the software dependencies can be packaged in the container image along with the application. With this in mind, Container-VM Image’s root file system is kept to a minimum by only including the software that's necessary to run containers.

More secure by design

Container-VM Image is designed with security in mind, rather than as an afterthought. The minimal root file system keeps the attack surface small. The root file system is mounted as read-only, and its integrity is verified by the kernel during boot up. Such hardening features make it difficult for attackers to permanently exploit the system.

Software updates

Having full control over the build infrastructure combined with a minimal root file system allows us to patch vulnerabilities and ship updated software versions very quickly. Container-VM Image also ships with an optional “in-place update” feature that allows users to stay up-to-date with minimal manual intervention.

Getting started

The Container-VM Images are available in the “google-containers” GCP project. Here are a few commands to get you started:

Here’s how to list currently available images:

$ gcloud compute images list --project google-containers --no-standard-images

Note: All new Container-VM Images have “gci-” prefix in their names.

Here’s how to start a new instance:
$ gcloud compute instances create  \
--zone us-central1-a \
--image-family gci-stable --image-project google-containers

Once the instance is ready, you can ssh into it:

$ gcloud compute ssh  --zone us-central1-a

You can also start an instance using Cloud-Config, the primary API for configuring an instance running Container-VM Image. You can create users, configure firewalls, start Docker containers and even run arbitrary commands required to configure your instance from the Cloud-Config file.

You can specify Cloud-Config as Compute Engine metadata at the time of instance creation with the special `user-data` key:

$ gcloud compute instances create  \
--zone us-central1-a \
--image-family gci-stable --image-project google-containers \
--metadata-from-file user-data=

What’s next

We're working hard on improving and adding new features to Container-VM Image to make it the best way to run containers on GCP.  Stay tuned for future blogs and announcements. In the meantime, you can find more documentation and examples at the Container-VM Image homepage, and send us your feedback at google-containers@google.com .

Last chance for 2016! Enter to win a spot at YouTube NextUp!

In February we launched YouTube NextUp, a contest to give creators the opportunity to attend a week-long creator camp to accelerate their channels. Since then, over 200 up-and-coming channels have been selected to attend camps at YouTube Spaces in LA, NY, London, Tokyo, Berlin, Mumbai and Sao Paulo. These up-and-coming channels took a 5-day crash course in video production, learned from top creators and NextUp grads and received vouchers for new equipment.
Now YouTube NextUp is going more global than ever before. We’re excited to announce the launch of our final round of 2016 contests. If you live in the following countries, you’re invited to enter:
North America
NextUp LA + NY - USA
NextUp Toronto - Canada
South America
NextUp Sao Paulo - Brazil
Europe
NextUp London - UK (including Northern Ireland), Ireland, Finland, Sweden, Norway, Denmark, and the Netherlands
NextUp Paris - France, Belgium, and Luxembourg
NextUp Berlin - Germany, Austria, and Switzerland
Middle East and North Africa
NextUp Amman - Algeria, Bahrain, Egypt, Iraq, Jordan, Kuwait, Lebanon, Morocco, Oman, Palestine, Qatar, Saudi Arabia, Tunisia, United Arab Emirates and Yemen
Asia
NextUp Tokyo - Japan
NextUp Bangkok - Thailand


What’s it like winning YouTube NextUp? Check out what the latest NextUp London class experienced in the video below:



Channels selected to join the NextUp class of 2016 are eligible to receive:
  • A $2,500 voucher for production equipment (or local equivalent) to help you make your video dreams a reality.
  • A spot at a week-long creator camp at YouTube Spaces around the world. You’ll team up with production experts to harness new techniques in camera, lighting, and sound and receive coaching from YouTube Partnerships team on how to grow your audience.
  • Mentorship by YouTube NextUp grads, plus the opportunity to meet and work with other fast-rising creators.

To see if you’re qualified to enter the YouTube NextUp contest and check out all the creators in the NextUp class of 2016, visit youtube.com/nextup.


Elizabeth Hartnett, YouTube Global Program Manager - NextUp, recently watched "Ew! With Hannah Hoffman and Natalie Barbu" from NextUp NY grad Joey Ahern

Now you can see your Google Calendar events in Google Maps

In our latest update, Google Maps teams up with Google Calendar to make it even easier to get where you need to go. Starting today, Android users worldwide will start to see their Google Calendar events on Google Maps. So whether you're heading to a family dinner or getting subway directions to a birthday party, you can now quickly and easily access your events directly from the map.

To ensure that your events appear on Google Maps, enter the address in the “Where” box when you create an event in Google Calendar. As long as you’re signed into both Google Calendar and Google Maps, the next time you open Google Maps you’ll see your Google Calendar events right there on the map. We've also introduced a new "Upcoming" tab where you'll see a list of your upcoming events. Simply open the sidebar menu, tap “Your Places” and then tap “Upcoming.” If you don’t want to see specific events on the map, you can hide them by tapping on the event from the map and then tapping “Dismiss.” 

Mapping-Voice-Command.gif

Seeing your Google Calendar events on Google Maps is the newest way to save time and make the most of every day.  Here's a list of existing Google Maps features that help you personalize your experience by surfacing important information where it matters most. 

Save home and work addresses

A lot of trips start or end from home and work, so save your home and work addresses to get directions in seconds. You can also add a little flair to your map by using your favorite sticker to mark home and work on the map. Search for “Home” in Google Maps or open “Your Places” to get started.

Label frequently visited places or upcoming destinations

Want to be able to simply say “OK Google, navigate to daycare?” You can. Just open Google Maps, search for the address you want to label, press the label button, and enter a name. The next time you type the name into the search box in Google Maps, your label will be a suggested result.

Your bookings and reservations from Gmail

When you receive confirmation emails for hotel, flight or restaurant reservations in Gmail, we show that information in Google Maps for easy access when on the go. These events also appear in the new “Upcoming” tab in “Your Places.”

See your Google contacts on the map

Your Google contacts will appear on Google Maps as well. Rather than memorizing your coworker or cousin’s address, you can get directions by typing their name in the search box (as long as you have an address attached to their contact info).

Settings_PC.png

To manage all your personal content and how it appears on Google Maps, you can use the new personal content manager under the settings tab in the side menu. Simply toggle off what you don’t want to show and toggle on what’s most helpful to you.

Source: Google LatLong


Beta Channel Update for Chrome OS

The Beta channel has been updated to 54.0.2840.43 (Platform version: 8743.44.0) for all Chrome OS devices. This build contains a number of bug fixes, security updates and feature enhancements. Systems will be receiving updates over the next few days.

If you find new issues, please let us know by visiting our forum or filing a bug. Interested in switching channels? Find out how. You can submit feedback using ‘Report an issue...’ in the Chrome menu (3 vertical dots in the upper right corner of the browser).

Josafat Garcia
Google Chrome

Stable Channel Update for Chrome OS


The Stable channel 53.0.2785.144 (Platform version: 8530.93.0) has been released for all Chrome OS devices. This build contains a number of bug fixes, security updates and a kernel key version update in the TPM. Systems will be receiving updates over the next several days.

Security Fixes:

This update includes these security fixes. Special note and congratulations to an anonymous security researcher for an excellent Pwnium entry: a chain of exploits that gains code execution in guest mode across reboots, delivered via web page. We anticipate landing additional changes and hardening measures for these vulnerabilities in the near future.

[$100,000][648971] Persistent code execution on Chrome OS. Credit to anonymous.
  • [649039] High CVE-2016-5179: Incorrect validation of writes to paths on stateful partition 
  • [649040] Critical CVE-2016-5180: Heap overflow in c-ares 
Note: Access to bug details and links may be kept restricted until a majority of users are updated with a fix. We will also retain restrictions if the bug exists in a third party library that other projects similarly depend on, but haven’t yet fixed.

If you find new issues, please let us know by visiting our forum or filing a bug. Interested in switching channels? Find out how. You can submit feedback using ‘Report an issue...’ in the Chrome menu (3 vertical dots in the upper right corner of the browser).


Ketaki Deshpande
Google Chrome

Dev Channel Update for Chrome OS

The Dev channel has been updated to 55.0.2869.0 (Platform version: 8838.0.0, 8838.1.0) for all Chrome OS devices. This build contains a number of bug fixes, security updates and feature enhancements. A list of changes can be found here.

If you find new issues, please let us know by visiting our forum or filing a bug. Interested in switching channels? Find out how. You can submit feedback using ‘Report an issue...’ in the Chrome menu (3 vertical dots in the upper right corner of the browser).

Bernie Thompson
Google Chrome

Introducing the Open Images Dataset



In the last few years, advances in machine learning have enabled Computer Vision to progress rapidly, allowing for systems that can automatically caption images to apps that can create natural language replies in response to shared photos. Much of this progress can be attributed to publicly available image datasets, such as ImageNet and COCO for supervised learning, and YFCC100M for unsupervised learning.

Today, we introduce Open Images, a dataset consisting of ~9 million URLs to images that have been annotated with labels spanning over 6000 categories. We tried to make the dataset as practical as possible: the labels cover more real-life entities than the 1000 ImageNet classes, there are enough images to train a deep neural network from scratch and the images are listed as having a Creative Commons Attribution license*.

The image-level annotations have been populated automatically with a vision model similar to Google Cloud Vision API. For the validation set, we had human raters verify these automated labels to find and remove false positives. On average, each image has about 8 labels assigned. Here are some examples:
Annotated images form the Open Images dataset. Left: Ghost Arches by Kevin Krejci. Right: Some Silverware by J B. Both images used under CC BY 2.0 license
We have trained an Inception v3 model based on Open Images annotations alone, and the model is good enough to be used for fine-tuning applications as well as for other things, like DeepDream or artistic style transfer which require a well developed hierarchy of filters. We hope to improve the quality of the annotations in Open Images the coming months, and therefore the quality of models which can be trained.

The dataset is a product of a collaboration between Google, CMU and Cornell universities, and there are a number of research papers built on top of the Open Images dataset in the works. It is our hope that datasets like Open Images and the recently released YouTube-8M will be useful tools for the machine learning community.


* While we tried to identify images that are licensed under a Creative Commons Attribution license, we make no representations or warranties regarding the license status of each image and you should verify the license for each image yourself.