Streamlining Business Processes with Google Cloud's Application Integration - Build What's Next
Blog

Streamlining Business Processes with Google Cloud’s Application Integration

882

Of your peers have already read this article.

3:30 Minutes

The most insightful time you'll spend today!

Dive into Google Cloud's Application Integration, a groundbreaking IPaaS that visually connects applications without code. Discover how it's streamlining business processes and fostering no-code integrations in this dynamic enterprise landscape.

According to a recent Accenture report, large businesses deploy an average of 500 different applications. This trend shows no signs of slowing down, with the majority of respondents signaling plans to acquire even more applications. No surprise, all these applications present a number of integration challenges, which the impending wave of generative AI in software development only promises to further intensify.

Two years ago, we introduced Apigee Integration as an add-on capability to Apigee API Management. From conversations with customers, it’s become clear that you want to access these capabilities directly from the Google Cloud console. Today, we’re announcing the general availability of Application Integration, a standalone Integration Platform as a Service (IPaaS) designed to help you connect your applications visually, with no code. Best of all, you can now get started with Application Integration with no upfront financial commitment, even if you’re not an existing Apigee customer. 

What is Application Integration?

As a cloud-native product, Application Integration helps you automate business processes by connecting any application — both home-grown and third-party SaaS — with simple point-and-click configurations, so you can quickly and easily construct, maintain, and scale your integrations. Application Integration offers the following features:

Visual integration designer: An Intuitive drag-and-drop interface allows anyone to build workflows with no complex coding or manual processes. With the visual integration designer, anyone can simply drag-and-drop individual control elements (such as edgesforks, and joins) to build custom integration patterns of any complexity.

https://storage.googleapis.com/gweb-cloudblog-publish/original_images/Trigger_only.gif

Plug-and-play connectors: A library of 75+ pre-built connectors makes it easy to connect to a number of Google Cloud services (e.g., BigQuery, Pub/Sub) and many third-party applications, including Salesforce, MongoDB, and NetSuite. 

https://storage.googleapis.com/gweb-cloudblog-publish/original_images/Connector.gif

Automated triggers and transformations: With built-in triggers, you can execute integrations on an API callCloud Pub/Sub eventSalesforce events, or using Cloud Scheduler. An intuitive data mapping editor and comprehensive mapping functions enable you to address complex data transformation requirements in no time.

https://storage.googleapis.com/gweb-cloudblog-publish/original_images/Trigger__Data_mapping.gif

Unified platform: Whether you’re building APIs in Apigee, ingesting data using Pub/Sub, receiving events from Cloud Storage using EventArc, or something else, Application Integration works seamlessly with other integration services to provide comprehensive capabilities for your use case. 

Customer praise for Application Integration

In the enterprise, different teams adopt applications to support a task in their business processes. However, the need for information exchange among these teams can spawn complex integrations that require a lot of arduous planning.

Application Integration abstracts these development complexities, so customers  can focus on building value. OVO Energy uses Application Integration to automate communications in their critical business processes. “We were looking for an integration solution that was very low-code yet configurable, robust, and scalable,” said Chris Parker, Product Manager at OVO Energy. “We needed a simple solution that allowed us to get data from multiple parts of the company, supplement an existing data point with relevant information and funnel it to the Salesforce Marketing Cloud,” added Graham Vosper, Technology lead at OVO Energy.

But changes in business processes are inevitable, forcing continuous shifts to existing integrations – sometimes even rendering them obsolete. “Using Application Integration was very simple. With just a couple of clicks, our team quickly adapted to this new product and became productive,” said Graham.

A growing library of connectors in Application Integration makes it easy to connect to new applications and ensures any changes in connecting to the end application is automatically supported. OVO Energy has taken notice. “Configurability and traceability in Application Integration allows our developers to build and maintain our integrations without in-depth skills. We now have much better visibility into our information flow and failure rates,” said Graham. 

Get started today

Today’s integration problems require a comprehensive approach that spans applications, systems, and data. Application Integration is part of Google Cloud’s Integration Services portfolio, designed to address your end-to-end integration needs. The portfolio covers a wide range of use cases like data integration, API Management, and event-driven architectures. You can get started with Application Integration with no financial commitment by using the free tier. Learn more, explore a sample e-commerce integration, or jumpstart your development with quickstart tutorials.

Research Reports

Google Cloud named a Leader in API Management Solutions in The Forrester Wave

DOWNLOAD RESEARCH REPORTS

3536

Of your peers have already downloaded this article

10:00 Minutes

The most insightful time you'll spend today!

The right API strategy is a key element of your digital business success, so choosing the best API management solution is critical – but often challenging. Organizations like yours need to address a wide range of criteria to support an effective digital business strategy, and that requires a robust API management solution that not only meets your immediate needs, but also supports your future digital initiatives.

The Forrester Wave: API Management Solutions, Q3 2020, provides an analysis of the most significant vendors that make up the API management market and explains why Google Cloud’s Apigee API management platform is a Leader. In addition to being named a Leader, Google Cloud received the highest score possible in criteria such as market presence, product vision, and planned enhancements.

Blog

Creation of Go applications on Google Cloud Made Easy

896

Of your peers have already read this article.

2:30 Minutes

The most insightful time you'll spend today!

Discover how Google Cloud is enhancing Go development with its new templates and gonew tool, allowing developers to effortlessly create Go applications for Cloud Functions, Cloud Run, and more. Explore the streamlined process and future plans for Go.

Go is the world’s leading programming language for cloud-based development, used by millions of developers to build and scale their cloud applications and business-critical cloud infrastructure. Whether it’s building CLIs, web applications, or cloud and network services, developers find Go easy to learn, easy to maintain, and packed with useful features such as built in concurrency and a robust standard library.

And now, getting started with Go on Google Cloud is a little bit easier. Following Go’s recent announcement of gonew, an experimental tool for instantiating new projects in Go from predefined templates, Google Cloud is releasing four templates for gonew that developers can use to bootstrap their Go applications across several Google Cloud products. This includes common use cases such as creating a simple HTTP handler Cloud Function, subscribing to a Cloud Pub/Sub topic, or creating an HTTP Server on Cloud Run.

Google Cloud Go templates

  • httpfn: A basic HTTP handler (Cloud Function)
  • pubsubfn: A function that is subscribed to a PubSub topic handling a Cloud Event (Cloud Function)
  • microservice: An HTTP server that can can be deployed to a serverless runtime (Cloud Run)
  • taskhandler: An basic app that handles tasks from requests (App Engine)

Let’s get started

  • Make sure you have Go installed on your machine, if not you need to download/install from here .
  • Start by installing gonew using go install:

    $ go install golang.org/x/tools/cmd/gonew@latest
    To generate a basic HTTP Handler Cloud Function, instantiate the existing httpfn template by running gonew in your new project’s parent directory. As of now, gonew’s syntax expects two arguments: first, the path to the template you wish to invoke, and second, the module name of the project you are creating. For example:
    $ gonew github.com/GoogleCloudPlatform/go-templates/functions/httpfn yourdomain.com/httpfn
  • Deploy this new go module to Cloud Functions by following the steps listed here, you can also try this in the Cloud Shell editor.

What’s next?

We have big plans for Go on Google Cloud, with several new enhancements on our roadmap. In the meantime, please try out these new templates, deploy them to Google Cloud using the instructions provided and let us know how we can make them better. Please use this to report any issues.

Blog

Plainsight Vision AI Available for Google Cloud Customers to Unlock Accurate, Actionable Insights

4666

Of your peers have already read this article.

4:00 Minutes

The most insightful time you'll spend today!

Learn how Plainsight makes data visualization a reality with the launch of Enterprise Vision AI on Google Cloud Marketplace to help businesses unlock visual data value!

Data-savvy businesses increasingly rely on images and videos for critical functions, and yet are challenged by the sheer mass of information—more than 3.2 billion images and 720,000 hours of video are created daily. This explosion in visual data has paved the way for the growth of computer vision, a form of artificial intelligence (AI) that enables computers to “see” the world similarly to the way people do, but with unblinking consistency, and greater accuracy. 

The transformational impact and value of computer vision solutions are significant and has been a guiding objective for companies and AI developers. And yet, even as the applications for computer vision increase dramatically, architecting and implementing vision AI solutions remain highly complex. Visual data, such as images and video, are made up of thousands of pixels of information that represent millions of different patterns and meanings, which can make interpreting even a single image overwhelming from a computational perspective.

Many organizations struggle with deployments and fail to operationalize vision AI solutions due to development delays, machine learning and data science hiring challenges, inaccurate output, a lack of integration with existing infrastructure, difficulty of use, and high cost. Plainsight, with the power of Google Cloud resources, is addressing all these challenges and helping businesses by enabling the deployment of vision AI within enterprise private networks that can be managed easily and scaled economically.

Plainsight has announced availability of its vision AI platform on Google Cloud Marketplace. Businesses can now easily deploy end-to-end vision AI to private clouds to realize the full value of their video and other visual data for accurate, actionable insights across diverse use cases.

Delivering on the Promise of AI: Seeing What’s Hiding In Plain Sight

For organizations to integrate AI and machine learning into their businesses successfully, the technology must be powerful enough to solve real challenges, yet fast, easy, and accessible enough to ensure the innovation potential is realized. Plainsight on Google Cloud delivers the power of enterprise vision AI that’s quick and easy to use with Google Cloud resources that enable global scale, increased security, bolstered privacy, unified billing, and cost savings. 

To streamline vision AI workflows, Plainsight facilitates the entire pipeline, from visual data ingestion and annotation, through continuous model training, deployment, and monitoring for easier innovation and faster time-to-production. Our platform accelerates vision AI development in a manner that is complete, accurate, and accessible to non-technical business leaders. We believe that AI should be available and accessible to anyone and everyone—so that teams across entire organizations can reap the benefits. 

By integrating Plainsight into their private networks, companies worldwide can now leverage one intuitive platform for centralized control of streamlined vision AI model creation and training with optimized visual data handling for diverse enterprise solutions. These use cases include: social distancing monitoring, medical imaging, drug compound screening, defect detection in manufacturing processes, identifying gas leaks, or even livestock counting and crop health monitoring for agriculture, to name a few.https://www.youtube.com/embed/A7U_0UkjvEg?enablejsapi=1&

We enable customers so they can create successful solutions that enable them to clearly see their business from all angles and to take advantage of the knowledge visual data can reveal by simply and quickly operationalizing practical vision AI applications.

AI-Powered Dataset Creation, Automated Model Training & Easy Deployment Without A Single Line Of Code 

For vision AI applications, success is inextricably dependent on the quality and quantity of the datasets required to train the relevant models. To aid enterprises in this vital stage, the Plainsight platform provides built-in data annotation for the fast and easy creation of datasets. This includes AI-powered features that accelerate the speed and quality of labeling such as SmartPoly, for the automated polygon masking of objects, TrackForward, to predict and automatically label objects from frame to frame in video annotations, and AutoLabel for automated object recognition and labeling based on pre-trained machine learning models, to highlight a few.

vision AI application.gif

In addition, to ensure the success of AI integration, we significantly reduce time-intensive processes with Plainsight vision AI’s automated machine learning with continuous model training and easy deployment capabilities. In just a few clicks, users can leverage optimizations for the most reliable model training without endless experimentation cycles. And, models are easily deployed at scale all within one, easy-to-manage model operationalization process for the business.

Growing With Google Cloud

Plainsight is a vision AI innovation leader, developing solutions that address unmet needs for challenger brands and Fortune 500s across vertical markets. As a team recognized for succeeding where others have failed, our expanding partnership with Google Cloud provides a powerful combination that helps customers see and activate the value of their visual data with a suite of services in a secure and private manner.

Our vision AI Platform simplifies building and operationalizing AI to solve business problems enterprises are facing every day—and the demand is increasing. To accelerate our journey to faster, more accessible AI for enterprises, we knew we needed strong support to grow Plainsight and scale our backend tools to match our vision. 

Google Cloud delivered everything, and more, in one program. The Startup Program by Google Cloud provided the technology and services for scale and the support we needed to maximize the value the Program provided us. The Startup Program has been a springboard for architecting Plainsight vision AI in the cloud, accelerating our goals and optimizing innovation, efficiency, and growth. The team also helped us optimize Google Ads campaigns, fueling adoption of Plainsight. 

After launching the SaaS version of Plainsight Data Annotation in November 2020, we grew our user base by nearly 110x in just three short months. Google Ads has also dramatically increased website traffic, growing new users by nearly 5.75X and page views by over 5X. The Google team helped us identify where Google Cloud offerings could be leveraged instead of developing in-house solutions and offered best practices that enabled us to deliver faster on our initiatives. 

Kubernetes was already the underlying component of our platform and leveraging Google Kubernetes Engine (GKE) as a managed service removed a layer of complexity. By combining GKE and Anthos, we were able to standardize our deployments, aligning to how our customers leverage Anthos for enterprise applications in their own organizations. In addition, as a fast-moving, customer-centric company we use Google Workspace to help us centralize and manage our day-to-day work internally. By leveraging multiple products across Google’s ecosystem, we take advantage of a holistic partnership that has helped our business tremendously as we scale. 

Leveraging Google’s Partners for Strategic Consultation

To facilitate this expansion of our partnership with Google and to maximize our use of Google Cloud services, we are working with DoiT International, a Google Managed Services Provider and 2020 Global Reseller Partner of the Year. DoiT provides us with ongoing technical consultation for cloud-native architecture, Google Cloud Marketplace integration, production-grade Kubernetes support, Google Cloud cost optimization, and technical support. The DoiT team has been invaluable in compiling best practices, tips, and strategies from their vast experience with various cloud customers to ease our Marketplace integration and is providing input for infrastructure strategy to support our continued rapid growth.

Plainsight Delivers Enterprise Vision AI Through The Google Cloud Platform Marketplace

Plainsight vision AI is now available to Google Cloud Customers on Google Cloud Marketplace enabling organizations across industries to deploy private Plainsight instances within their own environments. Marketplace customers will benefit from Google Cloud privacy, security, scalability and unified billing through their Google Cloud account. 

Combining the powerful benefits provided by Google Cloud resources with Plainsight’s vision AI Platform into private networks, enterprises worldwide can now leverage one intuitive platform for centralized control of streamlined vision AI model creation and training with optimized visual data handling for diverse enterprise solutions. 

Through our Google partnership, we’re able to leverage a powerful foundation that allows us to rapidly innovate, scale and accelerate delivery on our vision AI platform capabilities. By executing on our vision to make AI easier, faster and more accessible for all users across entire enterprises, we’re helping businesses see more and by seeing more, they’ll have the power to solve more. 

If you want to learn more about how Google Cloud can help your startup, visit our page here where you can apply for our Startup Program, and sign up for our monthly startup newsletter to get a peek at our community activities, digital events, special offers, and more.

Case Study

Wunderkind Leverages Google Cloud to Address the Growing Needs of its Customer Base

5322

Of your peers have already read this article.

1:30 Minutes

The most insightful time you'll spend today!

Performance marketing channel, Wunderkind after having issues with legacy database deployed Google Cloud solutions and Cloud Bigtable for flexibility and scalability to meet the growing needs of their data use cases.

Editor’s note: We’re hearing here how martech provider Wunderkind easily met the scaling demands of their growing customer base on multiple use cases with Cloud Bigtable and other Google Cloud data solutions.

Wunderkind is a performance marketing channel and we mostly have two kinds of customers: online retailers, and publishers like Gizmodo Media Group, Reader’s Digest, The New York Post and more. We help  retailers boost their e-commerce revenue through real-time messaging solutions designed for email, SMS, onsite, and advertising. Brands want to provide a one-to-one experience to more of their customers, and we use our extensive history with best practices in email marketing and technology to help brands reach more customers through targeted messaging and personalized shopping experiences. With  publishers, it’s a different value proposition, we use the same platform to provide a non disruptive and personalized ad experience for their website. For example, if you are on their site and then you left, we might show an ad tailored to you when you come back later – depending on the campaign. 

After running into limitations with our legacy database system, we turned to Cloud Bigtable and Google Cloud, which helped us be more flexible and easily scale for high traffic demand – which can be a stable 40,000 requests per second, and meet the needs of our growing number of data use cases. 

Three different databases power our core product

In our core offering, companies send us user events from their websites. We store these events and later decide (using our secret sauce) if and how to reach out to those users on behalf of our customers. Because many of our customers are retailers, Black Friday and Cyber Monday are big traffic days for us as. On such days, we can get 31 billion events, sometimes as many as  200K events per second. We show 1.6 billion impressions that have seen close to 1 billion pageviews. And at the end of all this, we securely send about 100 million emails. We noticed the same thing for election time; traffic reached the same high volume. We need scalable solutions to support this level of traffic as well as the elasticity to let us pay only for what we use, and that’s where Google Cloud comes in.

So how does this work? Our externally facing APIs, which are running on Google Kubernetes Engine, receive those user events—up to hundreds of thousand per second. All the components in our architecture need to be able to handle this demand. So from our APIs, those events go to Pub/SubDataflow and from there they are written to Bigtable and BigQuery, Google Cloud’s serverless, and highly scalable data warehouse. This business user activity data underpins almost all our products. Events can be things like product views or additions to shopping carts. When we store this data in Bigtable, we use a combination of email address and the customer ID as the Bigtable key and we record the event details in that record. 

What do we do with this information next? It’s important to mention that we also mark the last time we received an event about a user in Memorystore for Redis, Google Cloud’s fully managed Redis service. This is important because we have another service that is periodically checking Memorystore for users that have not been active for a campaign-specific period of time (it can be 30 minutes, for example), then deciding whether to reach out to them.

How we decide when we reach out is an intelligent part of our product offering, based on the channel, message, product, etc. When we do reach out, we use Memorystore for Redis as a rate limiter or token bucket. In order not to overwhelm the email or texting providers we send API requests to, we throttle those requests using Memorystore. (We prefer to preemptively throttle the outgoing API requests as opposed to handling errors later.)

When we do reach out, often we will need details for a specific product—let’s say if the website belongs to a retailer. We usually get that information from the retailer through various channels and we store product information in Cloud SQL for MySQL. We pull that information when we need to send an email with product information, and we use Memorystore for Redis to cache that information, since many of the products are repeatedly called. Our Cloud SQL instance has 16 vCPUs, 60GBs of memory and 0.5TB of disk space and when we perform those product information updates, we have about a thousand write transactions per second. We are also in the process of migrating some tables from a self managed MySQL instance, and we keep those tables synchronized with Cloud SQL using Datastream. 

Our user history database was originally stored in AWS DynamoDB, but we were running into problems with how they structured the data, and we’d often get hot shards but with no way to determine how or why. That led to our decision to migrate to Bigtable. We set up the migration first by writing the data to two locations from Pub/Sub, performed some backfill of data until that was up and running, and then started working on the reading. We performed this over a few short months, then switched everything to Bigtable. 

So, as mentioned, we are using Bigtable for multiple databases. The instance that stores our user events has about 30 TB with about 50 nodes.

Profile management

A second use case for Bigtable is for user profile management, where we track, for example, user attributes based on subscription activity, whether they’ve opted in or out of various lists, and where we apply list-specific rules that determine which targeted emails we send out to users. 

Our very own URL shortener

Our third use case for Bigtable is our URL shortener. When our customers build out campaigns and choose a URL, we append tracking information to the query string of the URLs and they become long. Many times, we are sending them via SMS texts, so the URLs need to be short. We originally used an external solution, but made the determination that they couldn’t support our future demands. Our calls tend to be very bursty in nature, and we needed to plan for a future state of supporting higher throughput. We use a separate table in Bigtable for this shortened URL. We generate the short slug that is 62 bit-encoded and use it as the rowkey. We use the long slug as a Protobuf-encoded data structure in one of the row cells and we also have a cell for counting how many times it was used. We use Bigtable’s atomic increment to increase that counter to track how many times the short slug was used. 

When the user receives a text message on their phone, they click the short URL, which goes through to us, and we expand it to the long slug (from Bigtable) and redirect them to the appropriate site location. Obviously, for the URL shortener use case, we need to make the conversion very quickly. Bigtable’s low latency helps us meet that demand and we can scale it up to meet higher throughput demands.

Meeting the future with Google Cloud

Our business has grown considerably, and as we keep signing up new clients, we need to scale up accordingly, and Bigtable has met our scaling demands easily. With Bigtable and other Google Cloud products powering our data architecture, we’ve met the demand of incredibly high traffic days in the last year, including Black Friday and Cyber Monday. Traffic for these events went much higher than expected, and Bigtable was there, helping us easily scale on demand. 

We are working on leveraging a more cloud native approach and using Google Cloud managed services like GKE, Dataflow, pub/sub, Cloud SQL , Memorystore, BigQuery and more. Google has those 1st party products and we don’t see the value in rolling out or self managing such solutions ourselves..

Thanks to Google Cloud, we now have reliable and flexible data solutions that will help us meet the needs of our growing customer base, and delight their users with fast, responsive, personalized shopping messaging and experiences. 

Learn more about Wunderkind and Cloud Bigtable. Or check out our recent blog exploring the differences between Bigtable and BigQuery.

10136

Of your peers have already watched this video.

1:00 Minutes

The most insightful time you'll spend today!

How-to

Predict User Churn on Gaming Apps with Google Analytics Data using BigQuery ML

User retention can be a major challenge for mobile game developers. According to the Mobile Gaming Industry Analysis in 2019, most mobile games only see a 25% retention rate for users after the first day. To retain a larger percentage of users after their first use of an app, developers can take steps to motivate and incentivize certain users to return. But to do so, developers need to identify the propensity of any specific user returning after the first 24 hours. 

In this blog post, we will discuss how you can use BigQuery ML to run propensity models on Google Analytics 4 data from your gaming app to determine the likelihood of specific users returning to your app.

You can also use the same end-to-end solution approach in other types of apps using Google Analytics for Firebase as well as apps and websites using Google Analytics 4. To try out the steps in this blogpost or to implement the solution for your own data, you can use this Jupyter Notebook

Using this blog post and the accompanying Jupyter Notebook, you’ll learn how to:

  • Explore the BigQuery export dataset for Google Analytics 4
  • Prepare the training data using demographic and behavioural attributes
  • Train propensity models using BigQuery ML
  • Evaluate BigQuery ML models
  • Make predictions using the BigQuery ML models
  • Implement model insights in practical implementations

Google Analytics 4 (GA4) properties unify app and website measurement on a single platform and are now default in Google Analytics. Any business that wants to measure their website, app, or both, can use GA4 for a more complete view of how customers engage with their business. With the launch of Google Analytics 4, BigQuery export of Google Analytics data is now available to all users. If you are already using a Google Analytics 4 property, you can follow this guide to set up exporting your GA data to BigQuery.

Once you have set up the BigQuery export, you can explore the data in BigQuery. Google Analytics 4 uses an event-based measurement model. Each row in the data is an event with additional parameters and properties. The Schema for BigQuery Export can help you to understand the structure of the data.  

In this blogpost, we use the public sample export data from an actual mobile game app called “Flood It!” (AndroidiOS) to build a churn prediction model. But you can use data from your own app or website. 

Here’s what the data looks like. Each row in the dataset is a unique event, which can contain nested fields for event parameters.

  SELECT *
FROM `firebase-public-project.analytics_153293282.events_*`
TABLESAMPLE SYSTEM (1 PERCENT)
table

This dataset contains 5.7M events from over 15k users.

  SELECT 
    COUNT(DISTINCT user_pseudo_id) as count_distinct_users,
    COUNT(event_timestamp) as count_events
FROM
  `firebase-public-project.analytics_153293282.events_*
count

Our goal is to use BigQuery ML on the sample app dataset to predict propensity to user churn or not churn based on users’ demographics and activities within the first 24 hours of app installation.

data

In the following sections, we’ll cover how to:

  1. Pre-process the raw event data from GA4
    1. Identify users & the label feature
    2. Process demographic features
    3. Process behavioral features
  2. Train classification model using BigQuery ML
  3. Evaluate the model using BigQueryML
  4. Make predictions using BigQuery ML
  5. Utilize predictions for activation

Pre-process the raw event data

You cannot simply use raw event data to train a machine learning model as it would not be in the right shape and format to use as training data. So in this section, we’ll go through how to pre-process the raw data into an appropriate format to use as training data for classification models.

This is what the training data should look like for our use case at the end of this section:

user id

Notice that in this training data, each row represents a unique user with a distinct user ID (user_pseudo_id). 

Identify users & the label feature

We first filtered the dataset to remove users who were unlikely to return the app anyway. We defined these ‘bounced’ users as ones who spent less than 10 mins with the app. Then we labeled all remaining users:

  • churned: No event data for the user after 24 hours of first engaging with the app.
  • returned: The user has at least one event record after 24 hours of first engaging with the app.

For your use case, you can have a different definition of bounce and churning. Also you can even try to predict something else other than churning, e.g.:

  • whether a user is likely to spend money on in-game currency 
  • likelihood of completing n-number of game levels
  • likelihood of spending n amount of time in-game etc.

In such cases, label each record accordingly so that whatever you are trying to predict can be identified from the label column.

From our dataset, we found that ~41% users (5,557) bounced. However, from the remaining users (8,031),  ~23% (1,883) churned after 24 hours:

  SELECT
    bounced,
    churned, 
    COUNT(churned) as count_users
FROM
    bqmlga4.returningusers
GROUP BY 1,2
ORDER BY bounced
boucned

To create these bounced and churned columns, we used the following snippet of SQL code. 

  ...
#churned = 1 if last_touch within 24 hr of app installation, else 0
IF (user_last_engagement < TIMESTAMP_ADD(user_first_engagement, 
      INTERVAL 24 HOUR),
    1,
    0 ) AS churned,
#bounced = 1 if last_touch within 10 min, else 0
IF (user_last_engagement <= TIMESTAMP_ADD(user_first_engagement, 
      INTERVAL 10 MINUTE),
    1,
    0 ) AS bounced,
...

You can view the Jupyter Notebook for the full query used for materializing the bounced and churned labels. 

Process demographic features

Next, we added features both for demographic data and for behavioral data spanning across multiple columns. Having a combination of both demographic data and behavioral data helps to create a more predictive model. 

We used the following fields for each user as demographic features:

  • geo.country
  • device.operating_system
  • device.language

A user might have multiple unique values in these fields — for example if a user uses the app from two different devices. To simplify, we used the values from the very first user engagement event.

  CREATE OR REPLACE VIEW bqmlga4.user_demographics AS (
  WITH first_values AS (
      SELECT
          user_pseudo_id,
          geo.country as country,
          device.operating_system as operating_system,
          device.language as language,
          ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC) AS row_num
      FROM `firebase-public-project.analytics_153293282.events_*`
      WHERE event_name="user_engagement"
      )
  SELECT * EXCEPT (row_num)
  FROM first_values
  WHERE row_num = 1 #first engagement
);

Process behavioral features

There is additional demographic information present in the GA4 export dataset, e.g. app_info, device, event_params, geo etc. You may also send demographic information to Google Analytics through each hit via user_properties. Furthermore, if you have first-party data on your own system, you can join that with the GA4 export data based on user_ids. 

To extract user behavior from the data, we looked into the user’s activities within the first 24 hours of first user engagement. In addition to the events automatically collected by Google Analytics, there are also the recommended events for games that can be explored to analyze user behavior. For our use case, to predict user churn, we counted the number of times the follow events were collected for a user within 24 hours of first user engagement: 

  • user_engagement
  • level_start_quickplay
  • level_end_quickplay
  • level_complete_quickplay
  • level_reset_quickplay
  • post_score
  • spend_virtual_currency
  • ad_reward
  • challenge_a_friend
  • completed_5_levels
  • use_extra_steps

The following query shows how these features were calculated:

  WITH
  events_first24hr AS (
    SELECT
      e.*
    FROM
      `firebase-public-project.analytics_153293282.events_*` e
    JOIN
      bqmlga4.returningusers r
      ON
        e.user_pseudo_id = r.user_pseudo_id
    WHERE
      TIMESTAMP_MICROS(e.event_timestamp) <= r.ts_24hr_after_first_engagement
  )
SELECT
  user_pseudo_id,
  SUM(IF(event_name = 'user_engagement', 1, 0)) AS cnt_user_engagement,
  # ... repeated for all behavior data ... 
  SUM(IF(event_name = 'use_extra_steps', 1, 0)) AS cnt_use_extra_steps,
FROM
  events_first24hr
GROUP BY
  1

View the notebook for the query used to aggregate and extract the behavioral data. You can use different sets of events for your use case. To view the complete list of events, use the following query:

  SELECT
    event_name,
    COUNT(event_name) as event_count
FROM
    `firebase-public-project.analytics_153293282.events_*`
GROUP BY 1
ORDER BY
   event_count DESC

After this we combined the features to ensure our training dataset reflects the intended structure. We had the following columns in our table:

  • User ID:
    • user_pseudo_id
  • Label:
    • churned
  • Demographic features
    • country
    • device_os
    • device_language
  • Behavioral features
    • cnt_user_engagement
    • cnt_level_start_quickplay
    • cnt_level_end_quickplay
    • cnt_level_complete_quickplay
    • cnt_level_reset_quickplay
    • cnt_post_score
    • cnt_spend_virtual_currency
    • cnt_ad_reward
    • cnt_challenge_a_friend
    • cnt_completed_5_levels
    • cnt_use_extra_steps
    • user_first_engagement

At this point, the dataset was ready to train the classification machine learning model in BigQuery ML. Once trained, the model will output a propensity score between churn (churned=1) or return (churned=0) indicating the probability of a user churning based on the training data.

Train classification model 

When using the CREATE MODEL statement, BigQuery ML automatically splits the data between training and test. Thus the model can be evaluated immediately after training (see the documentation for more information).

For the ML model, we can choose among the following classification algorithms where each type has its own pros and cons:

model

Often logistic regression is used as a starting point because it is the fastest to train. The query below shows how we trained the logistic regression classification models in BigQuery ML.

  CREATE OR REPLACE MODEL bqmlga4.churn_logreg
TRANSFORM(
  EXTRACT(MONTH from user_first_engagement) as month,
  EXTRACT(DAYOFYEAR from user_first_engagement) as julianday,
  EXTRACT(DAYOFWEEK from user_first_engagement) as dayofweek,
  EXTRACT(HOUR from user_first_engagement) as hour,
  * EXCEPT(user_first_engagement, user_pseudo_id)
)
OPTIONS(
  MODEL_TYPE="LOGISTIC_REG",
  INPUT_LABEL_COLS=["churned"]
) AS
SELECT
  *
FROM
  bqmlga4.train

We extracted monthjulianday, and dayofweek  from datetimes/timestamps as one simple example of additional feature preprocessing before training. Using TRANSFORM() in your CREATE MODEL query allows the model to remember the extracted values. Thus, when making predictions using the model later on, these values won’t have to be extracted again. View the notebook for the example queries to train other types of models (XGBoost, deep neural network, AutoML Tables).

Evaluate model

Once the model finished training, we ran ML.EVALUATE to generate precisionrecallaccuracy and f1_score for the model:

  SELECT
  *
FROM
  ML.EVALUATE(MODEL bqmlga4.churn_logreg)
row

The optional THRESHOLD parameter can be used to modify the default classification threshold of 0.5. For more information on these metrics, you can read through the definitions on precision and recallaccuracyf1-scorelog_loss and roc_auc. Comparing the resulting evaluation metrics can help to decide among multiple models.Furthermore, we used a confusion matrix to inspect how well the model predicted the labels, compared to the actual labels. The confusion matrix is created using the default threshold of 0.5, which you may want to adjust to optimize for recall, precision, or a balance (more information here).

  SELECT
  expected_label,
  _0 AS predicted_0,
  _1 AS predicted_1
FROM
  ML.CONFUSION_MATRIX(MODEL bqmlga4.churn_logreg)
expected

This table can be interpreted in the following way:

actual

Make predictions using BigQuery ML

Once the ideal model was available, we ran ML.PREDICT to make predictions. For propensity modeling, the most important output is the probability of a behavior occurring. The following query returns the probability that the user will return after 24 hrs. The higher the probability and closer it is to 1, the more likely the user is predicted to return, and the closer it is to 0, the more likely the user is predicted to churn.

  SELECT
  user_pseudo_id,
  returned,
  predicted_returned,
  predicted_returned_probs[OFFSET(0)].prob as probability_returned
FROM
  ML.PREDICT(MODEL bqmlga4.churn_logreg,
  (SELECT * FROM bqmlga4.train)) #can be replaced with a proper test dataset

Utilize predictions for activation

Once the model predictions are available for your users, you can activate this insight in different ways. In our analysis, we used user_pseudo_id as the user identifier. However, ideally, your app should send back the user_id from your app to Google Analytics. In addition to using first-party data for model predictions, this will also let you join back the predictions from the model into your own data.

  • You can import the model predictions back into Google Analytics as a user attribute. This can be done using the Data Import feature for Google Analytics 4. Based on the prediction values you can Create and edit audiences and also do Audience targeting. For example, an audience can be users with prediction probability between 0.4 and 0.7, to represent users who are predicted to be “on the fence” between churning and returning.
  • For Firebase Apps, you can use the Import segments feature. You can tailor user experience by targeting your identified users through Firebase services such as Remote Config, Cloud Messaging, and In-App Messaging. This will involve importing the segment data from BigQuery into Firebase. After that you can send notifications to the users, configure the app for them, or follow the user journeys across devices.
  • Run targeted marketing campaigns via CRMs like Salesforce, e.g. send out reminder emails.

You can find all of the code used in this blogpost in the Github repository:

https://github.com/GoogleCloudPlatform/analytics-componentized-patterns/tree/master/gaming/propensity-model/bqml

What’s next? 

Continuous model evaluation and re-training

As you collect more data from your users, you may want to regularly evaluate your model on fresh data and re-train the model if you notice that the model quality is decaying.

Continuous evaluation—the process of ensuring a production machine learning model is still performing well on new data—is an essential part in any ML workflow. Performing continuous evaluation can help you catch model drift, a phenomenon that occurs when the data used to train your model no longer reflects the current environment. 

To learn more about how to do continuous model evaluation and re-train models, you can read the blogpost: Continuous model evaluation with BigQuery ML, Stored Procedures, and Cloud Scheduler

More resources

If you’d like to learn more about any of the topics covered in this post, check out these resources:

Or learn more about how you can use BigQuery ML to easily build other machine learning solutions:

Let us know what you thought of this post, and if you have topics you’d like to see covered in the future! You can find us on Twitter at @polonglin and @_mkazi_.Thanks to reviewers: Abhishek Kashyap, Breen Baker, David Sabater Dinter.

More Relevant Stories for Your Company

How-to

Reactive Programming on Google Maps Platform: Watch Video to Learn

The Google Maps Platform Android SDK supports extensions for reactive programming, which helps you write code to handle asynchronous operations. Write reactive and responsive mapping applications with Google Maps Platform In mobile apps, asynchronous events can happen at any point in time: user touch events, waiting for network calls to complete, or

Whitepaper

A CIO’s Guide to Application Modernization

Even before the current crisis, IT organizations saw pressure to be more agile and innovative. Customer demographics and expectations are changing. Competition is emerging faster and from unexpected sources. Business models are being reinvented. Digital technology was at the heart of many of these challenges, and its adoption was key

Blog

Optimizing Cloud Load Balancing in Hybrid and Multicloud Architectures

Today’s enterprise applications are often assembled across distributed environments. This includes the integration of services across multi-cloud, multi-SaaS and on-premises environments. While the approach has the advantage of enabling enterprises to choose the best service available to support their applications, it adds the complexity of delivering services across heterogeneous environments.

How-to

Reference Guide to Get You Started with Development on GKE

Getting started with Kubernetes is often harder than it needs to be. While working with a cluster “from scratch” can be a great learning exercise or a good solution for some highly specialized workloads, often the details of cluster management can be made easier by utilizing a managed service offering. Google Kubernetes Engine (GKE)

SHOW MORE STORIES