At enterprises across industries, documents are at the center of core business processes. Documents store a treasure trove of valuable information whether it’s a company’s invoices, HR documents, tax forms and much more. However, the unstructured nature of documents make them difficult to work with as a data source. We call this “dark data” or unstructured data that businesses collect, process and store but do not utilize for purposes such as analytics, monetization, etc. These documents in pdf or image formats, often trigger complex processes that have historically relied on fragmented technology and manual steps. With compute solutions on Google Cloud and Document AI, you can create seamless integrations and easy to use applications for your users. Document AI is a platform and a family of solutions that help businesses to transform documents into structured data backed by machine learning. In this blog post we’ll walk you through how to use Serverless technology to process documents with Cloud Functions, and with workflows of business processes orchestrating microservices, API calls, and functions, thanks to Workflows.

At Cloud Next 2021, we presented how to build easy AI-powered applications with Google Cloud. We introduced a sample application for handling incoming expense reports, analyzing expense receipts with Procurement Document AI, a DocAI solution for automating procurement data capture from forms including invoices, utility statements and more. Then organizing the logic of a report approval process with Workflows, and used Cloud Functions as glue to invoke the workflow, and do analysis of the parsed document.

We also open sourced the code on this Github repository, if you’re interested in learning more about this application.

In the above diagram, there are two user journeys: the employee submitting an expense report where multiple receipts are processed at once, and the manager validating or rejecting the expense report. 

First, the employee goes to the website, powered by Vue.js for the frontend progressive JavaScript framework and Shoelace for the library of web components. The website is hosted via Firebase Hosting. The frontend invokes an HTTP function that triggers the execution of our business workflow, defined using the Workflows YAML syntax. 

Workflows is able to handle long-running operations without any additional code required, in our case we are asynchronously processing a set receipt files. Here, the Document AI connector directly calls the batch processing endpoint for service. This API returns a long-running operation: if you poll the API, the operation state will be “RUNNING” until it has reached a “SUCCEEDED” or “FAILED” state. You would have to wait for its completion. However, Workflows’ connectors handle such long-running operations, without you having to poll the API multiple times till the state changes. Here’s how we call the batch processing operation of the Document AI connector:

  - invoke_document_ai:
    call: googleapis.documentai.v1.projects.locations.processors.batchProcess
    args:
        name: ${"projects/" + project + "/locations/eu/processors/" + processorId}
        location: "eu"
        body:
            inputDocuments:
                gcsPrefix:
                    gcsUriPrefix: ${bucket_input + report_id}
            documentOutputConfig:
                gcsOutputConfig: 
                    gcsUri: ${bucket_output + report_id}
            skipHumanReview: true
    result: document_ai_response

Machine learning uses state of the art Vision and Natural Language Processing models to intelligently extract schematized data from documents with Document AI. As a developer, you don’t have to figure out how to fine tune or reframe the receipt pictures, or how to find the relevant field and information in the receipt. It’s Document AI’s job to help you here: it will return a JSON document whose fields are: line_item, currency, supplier_name, total_amount, etc. Document AI is capable of understanding standardized papers and forms, including invoices, lending documents, pay slips, driver licenses, and more.

A cloud function retrieves all the relevant fields of the receipts, and makes its own tallies, before submitting the expense report for approval to the manager. Another useful feature of Workflows is put to good use: Callbacks, that we introduced last year. In the workflow definition we create a callback endpoint, and the workflow execution will wait for the callback to be called to continue its flow, thanks to those two instructions:

  - create_callback:
    call: events.create_callback_endpoint
    args:
        http_callback_method: "POST"
    result: callback_details
...
- await_callback:
    try:
        call: events.await_callback
        args:
            callback: ${callback_details}
            timeout: 3600
        result: callback_request
    except:
        as: e
        steps:
            - update_status_to_error:
              ...

In this example application, we combined the intelligent capabilities of Document AI to transform complex image documents into usable structured data, with Cloud Functions for data transformation, process triggering, and callback handling logic, and Workflows enabled us to orchestrate the underlying business process and its service call logic.

Going further 

If you’re looking to make sense of your documents, turning dark data into structured information, be sure to check out what Document AI offers. You can also get your hands on a codelab to get started quickly, in which you’ll get a chance at processing handwritten forms. If you want to explore Workflows, quickstarts are available to guide you through your first steps, and likewise, another codelab explores the basics of Workflows. As mentioned earlier, for a concrete example, the source code of our smart expense application is available on Github. Don’t hesitate to reach out to us at @glaforge and @asrivas_dev to discuss smart scalable apps with us.

Intro

One of the time consuming tasks for DBAs is to maximize query performance. To optimize slow-running queries, they often have to do detailed analysis, understand optimizer plans, and go through a lot of trial and error before getting to the best set of indexes that help with improved query performance. However, even with expert knowledge of a database’s internals, it is challenging to choose an effective set of indexes, especially when workloads change over time. The complexity of queries including several joins, filters, and subqueries make the process of identifying the right set of indexes very challenging for DBAs. Also, the effect of an index on the query plan needs to be taken into account when considering further indexes. Creating an index may cause the query optimizer to select completely different join orders and join/scan methods. This effect is hard to predict and quantify for DBAs. 

Imagine if the database itself is intelligent enough to identify such queries, and recommends creating specific B-tree indexes? 

Google Cloud’s AlloyDB for PostgreSQL is a fully-managed and fully PostgreSQL-compatible database for demanding transactional and analytical workloads that provides enterprise-grade performance and availability.  AlloyDB offers Index Advisor, a built-in feature that helps alleviate the guesswork of tuning query performance with deep analysis of the different parts of a query including subqueries, joins, and filters. It periodically analyzes the database workload, identifies queries that can benefit from indexes, and recommends new indexes that can increase query performance. 

How the AlloyDB Index Advisor works

First, let’s review a few AlloyDB terms relevant to the Index Advisor’s work.

• PostgreSQL’s system catalog has schema metadata, such as information about tables and columns, and internal bookkeeping information.
• HypoPG is an open source PostgreSQL extension that helps with hypothetical indexes without actually creating them to see if the index helps with query execution.
• Query Optimizer generates optimal execution plan. 

1. AlloyDB’s Index Advisor tracks the user query workload and analyzes it using statistics from the system catalog; it then identifies queries that could be improved significantly and potential candidate indexes for those cases. Note that there could be a large number of candidate indexes.

2. The Index Advisor evaluates each of these queries using hypothetical indexes based on the HypoPG, an open source extension and AlloyDB’s Query Optimizer. 

3. The results of this evaluation are used to intelligently select the best set of indexes for the workload and to generate recommendations. 

You can then simply copy and run the suggested index creation SQL commands. The Index Advisor consumes minimal resources and analyzes queries at a frequency that you define. It can also be constrained to have a user-specified maximum storage budget for the new indexes it recommends.

How to use the Index Advisor

1. Index Advisor is enabled by default. Its recommendation engine analyzes your workload at the specified frequency (every 24 hours by default) to capture any potential new index recommendations. 

2. Run your queries that are representative of your workload to the instance. Index advisor tracks these queries automatically. 

3. To request an index recommendation immediately, you can use the google_db_advisor_recommend_indexes() function. This function performs on-demand analysis and recommends indexes for the top 100 queries. SELECT * FROM google_db_advisor_recommend_indexes();

4. To view the recommended indexes and the queries based on periodic analysis, use the following query (also see the Usage Example section). 
SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r 
JOIN google_db_advisor_workload_statements s
ON r.query_id = s.query_id;

Note that Index advisor analyzes queries issued by the connected user. For the user with the pg_read_all_stats role, it analyzes all tracked queries. 

Currently, Index advisor only recommends new indexes. In future, the Index Advisor could be used to report unused indexes as well; dropping these can reduce index maintenance overhead for your transactional workload.

Evaluation

We will discuss two cases in this section.

1. Decision support benchmark: We used a sample internal benchmark that modeled a decision support system. The benchmark dataset contained 450+ columns across 24 tables. The result quantified the performance of 100+ complex analytical queries. The benchmark initially had a minimal number of indexes and the normalized total query execution time was around 200+ minutes. We then enabled the AlloyDB Index Advisor and it recommended an additional 17 indexes. Creating those indexes and re-running the queries reduced the total execution time by half to ~100 minutes (1.8x).

2. Real-world AlloyDB customer workload: This is a real-world workload from a large financial customer. They have a portfolio of complex analytical queries and business insights demand fast response times. Before using Index Advisor, DBAs had already created a few indexes that they considered to be useful to speed up these queries. Many of their queries joined 30+ tables, had 10+ filters and  multiple subqueries. Those made it complex for DBAs  to correctly identify the most beneficial indexes to create. AlloyDB Index Advisor analyzed these complex structures and identified four additional indexes. By adding the suggested indexes, performance of 17 queries were up by 5x –  73x. The response time of these queries dropped from seconds to milliseconds.

Usage Example

An example: We use the following example to demonstrate the use of Index Advisor. The example simulates a retail application performing analytics on its orders table. 

1. Create a sample orders table.

CREATE TABLE orders (
    o_orderkey bigint NOT NULL,
    o_custkey int NOT NULL,
    o_orderstatus "char" NOT NULL,
    o_totalprice numeric(13,2) NOT NULL,
    o_orderdate date NOT NULL,
    o_orderpriority character varying(15) NOT NULL,
    o_clerk character varying(15) NOT NULL,
    o_shippriority int NOT NULL,
    o_comment character varying(79) NOT NULL
);

2. Insert 100K random orders into the table.

INSERT INTO orders 
SELECT col, col, substr(md5(random()::text), 1, 1), random(), date '2023-01-01' + col * interval '1 hour', substr(md5(random()::text), 1, 1), concat('clerk', col), col%10, concat('comment', col)
 FROM generate_series(1,1000000) g(col);

3. Analyze the table to populate statistics.

ANALYZE orders;

4. Run a query that counts the number of orders in the second week of Jan. You can run the query with timing on so that you can see how long it takes to run the query before and after the index is created.

\timing
SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';

Time: 42.196 ms

5. Manually request an index recommendation.

SELECT * FROM google_db_advisor_recommend_indexes();
                      index                       | estimated_storage_size_in_mb 
--------------------------------------------------+------------------------------
 CREATE INDEX ON "public"."orders"("o_orderdate") |                           25
(1 row)

6. View recommended indexes for tracked queries.

SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id AND recommended_indexes != '';
               recommended_indexes                |                                             query                                              
--------------------------------------------------+------------------------------------------------------------------------------------------------
 CREATE INDEX ON "public"."orders"("o_orderdate") | SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';
(1 row)

6. View recommended indexes for tracked queries.

SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id AND recommended_indexes != ”;
recommended_indexes | query
————————————————–+————————————————————————————————
CREATE INDEX ON “public”.”orders”(“o_orderdate”) | SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= ‘2023-01-09’ and o_orderdate <= ‘2023-01-15’;
(1 row)

7. Add the recommended index.

CREATE INDEX ON "public"."orders"("o_orderdate");

8. Re-run the query.

\timing
SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';

Time: 1.403 ms  (compared to 42.196ms before the index)

Learn more

• To learn more about AlloyDB’s Index Advisor, see Enable and use the index advisor | AlloyDB for PostgreSQL | Google Cloud

• To learn about AlloyDB, read AlloyDB for PostgreSQL intelligent scalable storage | Google Cloud Blog
• Start building on Google Cloud with $300 in free credits and 20+ always free products. https://cloud.google.com/free

Over 2.14 billion people worldwide are expected to buy online this year, according to Statista. Online retail sales will account for 22% of all purchases by 2023. But in a competitive retail landscape, positive interactions can mean the difference between a sale and an abandoned shopping cart.

One of the leading global marketplaces – Kinguin.net is a haven for gamers. Their bustling ecommerce business conducts over 500,000 new transactions monthly. Users will encounter over 50,000 unique digital products, from video games, gift cards, in-game items to computer software and services. With over 10 million registered users, Kinguin improved their experience by helping users find items quickly and deliver service at scale.

Helping customers find what they want, fast

Because of Kinguin’s high volume of users—both buyers and sellers—and breadth of digital products, browsing and shopping can be challenging. “Customers shop online for choice and convenience, but it can sometimes be overwhelming. We want anyone who shops at Kinguin to find what they are looking for quickly and easily,” says Viktor Romaniuk Wanli, Kinguin CEO and Founder.

Today’s retailers know that creating personalized shopping experiences is crucial for establishing and maintaining customer loyalty. Kinguin discovered their users were getting a rather standard retail experience. They wondered how they could offer them a more tailored, personalized experience.

They knew product recommendations were a great way to personalize experiences because they help customers discover products that match their tastes and preferences. But it’s not that easy to recommend products. Various shifting factors make recommendations much more complex:

• Customer behavior. Understanding customers is tough. How do you recommend something to a cold start user who’s never been to your site before? What happens when their behavior changes?
• Omnichannel context. According to Harvard Business Review, 73% of all customers use many channels when they buy. What happens when they go from desktop to mobile or from social media shopping to a proprietary app?
• Product data challenges. How do you recommend new products within a large catalog of items? What if your product data has sparse labeling or unstructured metadata?

Data wasn’t a problem for Kinguin. They had data orders, history, wishlists, and could collect events based on their platform interactions. It was the machine learning model expertise they lacked. So rather than building their own solution, they determined it was more cost effective for them to find a reliable partner. It was also essential that the solution integrated easily with Kubernetes, which enabled their global network.

With these considerations in mind, they applied for the Google Recommendations AI beta program. Kinguin became the first gaming e-commerce platform in Europe to use Recommendations AI when it launched in 2020.

Pro gamer move: using a fully managed AI service 

Google Recommendations AI uses algorithms to deliver highly personalized suggestions tailored to a customer’s preferences. Google Cloud based these algorithms on the same research that powers models by YouTube search and Google Shopping. Algorithms are always being tuned and adjusted to focus on individuals themselves—not just items.

Many shopping AIs rely on manually provisioning infrastructure and training machine learning models. Instead, Recommendations AI’s deep learning models use item and user metadata to gain insights. It processes Kinguin’s thousands of products at scale, iterating in real time. First, Kinguin pieces together a customer’s history and shopping journey. Then, using Recommendations AI, they can serve up personalized products—even for long-tail products and cold-start users. 

By leveraging internal tools, Kinguin didn’t need to start implementation from scratch. After a few trial sessions with Google Cloud engineers, they got started right away. Due to the fast-paced nature of a marketplace—i.e., price changes, out-of-stock items—Kinguin needed their recommendations to be as close to real time as possible. They used internal event buses to stream events and their product catalog directly to the recommendations API.

Kinguin rolled out in high-traffic areas, including their home page, product page, and category pages. They analyzed heat maps and scroll maps to figure out where to test placements. They also experimented with different recommendation models such as “recently bought together” and “you may like.” Engineers also factored in where they were implementing the models. For example, the “others you might like” model would fit best on the homepage, while “frequently bought together” made sense at checkout.

Understanding how product recommendations influence financials is critical for demonstrating the impact of personalization. Using BigQuery, Kinguin could analyze different cost projection models. BigQuery helped them dig into specific financial data to understand their margins and revenue gains.

Playing to win: enhanced customer experience

Since adopting Recommendations AI, Kinguin has improved both customer experience and satisfaction. Search times have shortened by 20 seconds. Additionally, their average cart value has increased by 5 EUR. Conversion rates have quadrupled since the outset. Click-thru rates have doubled, increasing by 2.16 on product pages and 2.8 times on recommendations pages.

“Google Recommendations AI has helped us evolve our service, increase customer loyalty and satisfaction. It has also contributed to a significant rise in sales,” says Wanli. Kinguin is already thinking about other ways of enhancing user experiences with recommendations. Ideas include their checkout process, other landing pages, and email marketing.

Kinguin’s journey with Google Cloud shows how companies can leverage AI to optimize sales and deliver high-performing, low-latency recommendations to any customer touchpoint. 

Learn more about Recommendations AI and Google Cloud AI and machine learning solutions.

Editor’s note: PedidosYa is the market leader for online food ordering in Latin America, serving 15 markets and over 400 cities. It’s also one of the largest brands within the German multinational company Delivery Hero SE. With over 20 million app downloads, PedidosYa provides the best online delivery experience through 71,000+ online partners, including restaurants, shops, drugstores, and specialized markets. 

Having constant access to fresh customer data is a key requirement for PedidosYa to improve and innovate our customer’s experience. Our internal stakeholders also require faster insights to drive agile business decisions. Back in early 2020, PedidosYa’s leadership tasked the data team to make the impossible possible. Our team’s mission was to democratize data by providing universal and secure access while creating a comprehensive information ecosystem across PedidosYa. We also had to achieve this goal while keeping costs under control— even during the migration stage and removing operational bottlenecks. 

Challenges with legacy cloud infrastructure

PedidosYa first built its data platform on top of AWS. Our data warehouse ran on Redshift, and our data lake was in S3. We used Presto and Hue as the user interfaces for our data analysts. However, maintaining this infrastructure was a daunting task. Our legacy platform couldn’t keep up with the increasing analytics demands. For example, the data stored on S3 complemented by Presto/Hue required high operational overhead. This was because Presto and our IAM (identity access management) didn’t integrate well in our legacy ecosystem. Managing individual users and mapping IAM roles with groups and Kerberos was operationally time-consuming and costly. Further, sharding access on the S3 files was far too complicated to enable seamless ACLs (access control lists).  

There were also challenges with workload management. Our data warehouse had batch data loaded overnight. If one analyst scheduled a query to run during the overnight ETL (extract, transform, load) workload, it would disrupt the current ETL task. This could stop the entire data pipeline. We’d have to wait until data engineers intervened with a manual fix.

It was also difficult to understand whether a query error was due to performance issues or platform resource exhaustion. This lack of clarity affected our data analysts’ ability to autonomously improve querying efficiency. Data team members needed to manually inspect personal queries looking for performance issues. Also,  the current architecture was prone to a ‘tragedy of the commons’ situation; it was seen as an unlimited and free resource. As a result, it was impossible to disentangle the infrastructure from different stakeholder teams, as all had very different needs. 

The decision to modernize our data warehouse

Given the growing challenges from our legacy platform, our tech team decided to transform our analytics environment with a modern data warehouse. They required the following key criteria from their next data platform: 

• Scalability – The ability to grow with elastic infrastructure.
• Cost control – Cost management and transparency. These factors promote efficiency and ownership—both key aspects of data democratization.
• Metadata management – Intuitive data platform focusing on users’ previous SQL knowledge. Plus, being able to enrich the informational ecosystem with metadata,  to diminish data gatekeepers.
• Ease of management – The team needed to reduce operational costs with a serverless solution. Data engineers wanted to focus on their key roles rather than acting as database administrators and infrastructure engineers. The team also wanted much higher availability, and to reduce the impact of maintenance windows and vacuum/analysis.
• Data governance and access rights – With a growing employee base with varying data access requirements, the team needed a simple yet comprehensive solution to understand and track user access to data.

Migrating to Google Cloud

After exploring other alternatives, we concluded Google Cloud had an answer to each of our decision drivers. Google Cloud’s serverless, managed, and integrated data platform, coupled with its seamless integration across open-source solutions, was the perfect answer for our organization. In particular, the natural integration with Airflow as a job orchestrator and Kubernetes for flexible on-demand infrastructure was key.  

We  used Dataflow together with Pub/Sub and Cloud Functions for our data ingestion requirements, which has made our deployment process with Terraform seamless. Because we set up everything in our environment programmatically, operation time has diminished. Google Cloud reduced the deployment process from about 16 hours in our legacy platform to 4 hours.  This is partly due to the friendliness of automating the deployment (such as schema check, load test, table creation, build.) process with Terraform, Cloud Functions, Pub/Sub, Dataflow, and BigQuery on GCP. Input messages processed with Dataflow allow us to abstract and plan the schema changes according to the needs of the functional team. For example, schema changes raise an alarm, and then we can modify the raw layer table schema. By doing this, we ensure that backend modifications that we don’t control do not affect upper layers.

A key reason why we picked Google Cloud was because of its advanced cost and workload management coupled with its transparent log analytics. This information gives us a complete view into any query performance issues to make improvements on the fly. Further, we achieved a significant amount of cost savings by consolidating multiple tools to BigQuery.With BigQuery, we’ve been able to reduce our total cost per query by 5x.

This was due to a number of reasons:

• Automating pipeline deployment made it much simpler to maintain the data processing processes. 
• Analysts are conscious about what queries they’re running, resulting in running better, more optimized queries. 
• Analysts use a Data Studio dashboard to see their queries and all the associated costs. As a result, there’s a lot more transparency for each persona.

 With these changes, we can easily manage and assign costs associated with each workload with their own cost centers using specific Google Cloud projects.

Change management is always challenging. However, BigQuery is intuitive and doesn’t have a steep learning curve from Hue/Hive on SQL basics. BigQuery also allowed the team to expand its capabilities and enabled them to properly work with nested structures, avoiding unnecessary joins and improving query efficiency. Additionally, we now use Data Catalog as our unique point of truth for metadata management. This allows our team to break the data access barriers and enable federation of data across the organization. By using Airflow to orchestrate everything, we keep track of every data stream. With this information, each end user can see their regularly used data entities’ status via the dashboard. This also adds transparency to our everyday data processes.

Finally, with Google Cloud’s IAM rules applied across the different products, data sharing and access is close to a noOps experience. We have programmatically implemented access according to roles and level access within the company. This allows certain pre-validated roles to view more sensitive information. These solutions help drive a more automated data governance experience. 

Up next: Google Cloud AI/ML

The new stack based on BigQuery has created significant productivity gains. Freed from the burden of operational management, PedidosYa’s data team can now focus on adding value through data tools and products.  

• Our data engineers are better equipped to integrate constantly changing transactional and operational data.
• The dataOps team can automate the infrastructure and provide autonomy to the end user.
• Our data quality team can focus on bringing added value to data stakeholders. 
• Data scientists and data analytics can spend more time analyzing data and less time asking data gatekeepers for data access.

PedidosYa can now democratize data access with a well-governed architecture. We are still at the beginning of our journey, but we are closer to achieving our vision of building a data-driven organization. Up next: expanding our artificial intelligence and machine learning capabilities.

Tune in to Google Cloud’s Applied ML Summit on June 10th, 2021, or listen on-demand later, to learn how to apply groundbreaking machine learning technology in your projects.