The ecommerce playing field has been hard to navigate for most retailers, and Cart.com is on a mission to change that. Traditionally, retailers needing to run their online store, order fulfillment, customer service, marketing, and other essential activities have had to cobble together systems to get the capabilities they need – much less having access to analytics across these functions. The result is costly, siloed ecommerce operations that are difficult to manage and scale.

It’s clearly not a formula for success, yet that’s the reality facing most retailers. Cart.com, in contrast, has set out to democratize ecommerce by giving brands of all sizes the full capabilities they need to take on the world’s largest online retailers. Our end-to-end environment empowers retailers to keep more of their revenue, set up proven strategies for managing all aspects of their business, and act on valuable insights from customer data every step of the way.

Together with our talented team, we’re building a unified ecommerce platform that already provides value to many leading or up and coming brands including Whataburger, GUESS, Dr. Scholl’s, Rowing Blazers, and Howler Bros. 

We’re excited about the opportunity ahead as we reimagine traditional approaches to online sales, fulfillment, marketing, accessing growth capital, providing a unified view of all ecommerce and marketing analytics, and other activities. Expectations for Cart.com are high, and we are building a company that can scale to $100B in revenue and beyond. Supported by the Startup Program by Google Cloud and Google Cloud solutions, we’re establishing a technology platform to transform all aspects of ecommerce for brands worldwide. 

Partner in disruption

At Cart.com, we’re currently targeting an underserved market. Our ideal customer is beyond demonstrating product-market-fit and is now at an inflection point seeking a growth opportunity. Typically, those companies are generating between $1M and $100M in annual revenue. We’ve seen an enthusiastic response from brands and retailers as well as investors, with backing from investors in just over a year totaling $143 million in three funding rounds.

Our strategy is to build an integrated ecommerce model that combines best-of-breed solutions, many of which we gain through acquisitions and then build upon to provide a streamlined and fully integrated experience for our brands. We’ve made seven acquisitions so far to round out our online store, order fulfillment, marketing services, customer service, and we have launched some integral partnerships including easy access to growth capital through our relationship with Clearco and product protection for customers on every purchase with Extend. Instead of acquiring a data company, we’re building our data platform on Google Cloud, across each operating function for a single-view for brands to harness actionable data. We see Google Cloud as the leader for data management, analytics, machine learning (ML) and artificial intelligence (AI).

Other reasons why we’re building our business on Google Cloud include scalability, excellence, security, reach, and data analytics that are far superior to other environments.

We also feel a cultural and mission alignment with Google Cloud and envision leaning into a long-term partnership of marketing, selling, and disrupting the disruptors together. Equally important to us are the investments Google Cloud is willing to make in early-stage companies like ours. The support through the Google Cloud for Startups program has been outstanding.

Built on Google Cloud

A wide range of Google Cloud solutions provide the foundation for our platform. For instance, Cloud Pub/Sub keeps our services communicating with one another. We rely on fully managed relational databases, like Cloud SQL and Cloud Spanner, to securely handle the huge volume of brand and shopper data generated every day.

Cloud Run allowed us to develop inside of containers before our Kubernetes infrastructure was ready to go. Now, we are taking advantage of all the capabilities in Google Kubernetes Engine. BigQuery integrates with all Google Cloud solutions and offers true data streaming natively out of the box, along with Dataflow for advanced analytics. We also use Container Registry to store and manage our Docker container images. Right now, we’re testing Cloud Composer to evaluate using it for data workflow orchestration instead of Apache Airflow.

The openness of the Google Cloud environment is further enabled by Anthos, which we may deploy soon to perform data integrations quickly as we acquire more companies over the next year. For example, if we acquire a company using Azure, we can easily align it with our Google Cloud ecosystem.

Enabling ecommerce 2.0

Recently, our team has been experimenting with Google Cloud Vertex AI and the fully managed services of AI deployment and ML operations. The capabilities would save us substantial time in the management of the ML lifecycle which allows us to focus more on developing proprietary AI that will transform commerce at scale.

Because Google Cloud is so far ahead in data science, our teams benefit from deep Google Cloud expertise as we look to provide brands with unmatched insights into customers to improve services and revenue. We’re also planning to test Recommendations AI among other tools to deploy customer product recommendations and personalization as turnkey productized offerings. Moving forward, we will likely use Bigtable to aid in serving machine learning to hundreds of thousands of brands due to its low latency and scalability.

Fanatical about brand success

We know that our work with Google Cloud for Startups and use of Google Cloud solutions for best-in-class data management, analytics, ML, and AI will enable us to offer even more transformative services to brands.

We also see the opportunity to use our platform and customer insights to break down barriers between brands, enabling retailers to share information and work better together when it’s in their best interests. What we’re building today on Google Cloud is fundamentally changing what’s possible for retailers of any size everywhere. 

As a startup, when recruiting talent or working with prospective customers, it helps to share our success with Google Cloud. We view them as an extension of the Cart.com team. It also validates our business as we continue building a more integrated, holistic approach to commerce that opens new opportunities and drives growth for brands worldwide.

For more details about Cart.com’s vision for unified ecommerce, check out our video.

If you want to learn more about how Google Cloud can help your startup, visit our page here to get more information about our program, and sign up for our communications to get a look at our community activities, digital events, special offers, and more.

For more than 10 years, NMK Textile Mills has manufactured bed linens for major retailers in the United States and Canada. As e-commerce exploded, the company’s co-founder saw an opportunity to grow the business. So, in addition to manufacturing bed linens wholesale for retail customers, NMK Textile Mills reworked its complete supply chain to manufacture products for California Design Den, which became an e-commerce retailer selling its own fashion-forward products directly to consumers online.

With California Design Den’s push into e-commerce, it became apparent the SMB company (with about 250 global employees) faced the same tough supply chain questions as large retail customers, including maintaining enough inventory to meet customer demand in a timely, efficient way.

California Design Den depended upon a myriad of systems to track its complex forecasting and reordering processes. Team members typically planned inventory manually using desktop spreadsheet software, which could lead to excess inventory. Accurately forecasting demand and supply was essential to the company’s financial success—but it was also a challenge.

A couple years ago, California Design Den partnered with Pluto7, a technology solutions provider that offers a software as a service (SaaS) called Planning In A Box. Leveraging Google Cloud Platform machine learning and artificial intelligence, Planning In A Box intelligently helps predict demand and balances it with supply.

But that was just the beginning. “Along the way, we realized that to compete with larger retailers, make quicker decisions, and move faster, we needed to go further,” says Deepak Mehrotra, Co-founder and Chief Adventurer at California Design Den.

With guidance from Pluto7, California Design Den began migrating its database to Google Cloud Platform. Using Google BigQuery, Google Compute Engine, Google Cloud SQL, and Google Cloud Storage, and experimenting with Google Cloud Vision and Google Cloud AutoML, the company is reducing inventory carryovers by more than 50%, improving the accuracy of demand planning quarter over quarter, and gaining granular insights into how individual SKUs are performing.

“We would need an army of data scientists to make faster decisions on pricing and inventory levels. With Google Cloud Platform machine learning and artificial intelligence, we don’t need that. We can make much faster pricing decisions to optimize profitability and move inventory.”

—Deepak Mehrotra, Co-founder and Chief Adventurer, California Design Den

No need for army of data scientists

“Using Google Cloud Platform machine learning and AI was essential for California Design Den if it was to compete successfully with larger retailers,” Deepak says.

For example, tastes and fashions in bed linens can change quickly and consumer prices fluctuate. With more than 2,500 SKUs, it wasn’t possible for California Design Den’s team to continuously monitor product demand and experiment with competitive pricing in real time.

“We would need an army of data scientists to make faster decisions on pricing and inventory levels,” says Deepak. “With Google Cloud Platform machine learning and artificial intelligence, we don’t need that. We can make much faster pricing decisions to optimize profitability and move inventory.”

By integrating all its data onto Google Cloud Platform, California Design Den’s team gains deeper insights into product sales over time, which in turn helps the company improve demand planning by better determining which styles to manufacture and sell in the future.

“When experienced employees leave, their knowledge leaves with them. By having all data in one place, and with machine learning and AI, California Design Den can go back in its history, look at products made or sold years ago, and analyze product performance.”

—Deepak Mehrotra, Co-founder and Chief Adventurer, California Design Den

Merging visuals with data

Before Google Cloud Platform, team members had to dig through spreadsheets and run scenarios to get a sense of how particular products had sold. The next step was to perform keyword searches across the company’s photo library in the cloud to find each product’s image. From there, a team member would insert the product images into a presentation, along with relevant data points, to provide a report for stakeholders on how particular styles performed.

Today, California Design Den, with the help of Pluto7, is integrating its entire product image library with its database on Google Cloud Platform. Experimenting with Google Cloud Vision and Google Cloud AutoML, California Design Den is moving towards a day when team members can run sales scenarios and get deep background data on individual product performance while viewing images of the relevant products.

Merging product visuals with data will help designers and team members better understand sales patterns over time and in context. In the past, making correlations between things like which sheet colors sold well in California, compared to how the same sheet colors performed on the East Coast, was something that California Design Den employees primarily did in their heads.

“When experienced employees leave, their knowledge goes with them,” says Manjunath Devadas, Founder and CEO at Pluto7. “By having all data in one place, and with machine learning and AI, California Design Den can go back in its history, look at products made or sold years ago, and analyze product performance.”

“We are literally growing the complexity of our business on all levels, including designing, manufacturing, selling, reordering, inventory holding—everything.”

—Deepak Mehrotra, Co-founder and Chief Adventurer, California Design Den

Reimagining supply-demand balancing

Pluto7’s mission statement is to democratize supply demand balancing with machine learning and AI. California Design Den is a case in point, as the combination of Planning In A Box and Google Cloud Platform gives the company greater control over its destiny.

“Big retailers used to tell us what to manufacture and how much they would pay for it,” says Deepak. “That was our primary business, and if we didn’t accept the terms, a competitor would.” Today, in addition to continuing to make products for retailers, California Design Den can design, make, and sell a variety of designs for itself, including custom and limited-edition products, thanks to Google Cloud Platform and Pluto7 software offerings. The payoff is not only in having a more diversified business. California Design Den also receives more favorable profit margins by selling its own products.

“We are literally growing the complexity of our business on all levels, including designing, manufacturing, selling, reordering, inventory holding—everything,” Deepak says. “We can make smaller batches. We can connect directly with consumers. We can identify the missing pieces—what should we produce next, when, and how much? We otherwise couldn’t afford the level of talent it would take to do this.”

Cutting through the noise

Google and Pluto7 software helped California Design Den reduce inventory carryovers by more than 50%. Inventory tracking and distribution, along with insights and visibility into product sales, are faster, more efficient, and accurate. Google Cloud Platform flexible pricing, speed, reliability, security, and scalability enable California Design Den to stay relevant and be more competitive.

In addition to benefiting from Google Cloud Platform, California Design Den relies on G Suite—also part of Google Cloud—to enhance collaboration among its global teams. Previously, the company’s email server would sometimes crash, due to the heavy load of sharing product photos and other data. “Gmail and Google Drive handle the everyday demands on the business effortlessly and reliably,” Deepak says.

“Google machine learning and AI enable us to cut through all the noise from raw data, so we can see what’s important. We can focus on analytics to guide us to success today and in the future.”

—Deepak Mehrotra, Co-founder and Chief Adventurer, California Design Den

The company is exploring additional ways to leverage Google Cloud Platform in the near future. For example, one possibility is to import customer reviews from sites where products are sold into Google BigQuery, and to use that data to perform sentiment analysis via Google Cloud Natural Language. It could provide another valuable data source to help California Design Den’s team decide where to focus future designs.

“Google machine learning and AI enable us to cut through all the noise from raw data, so we can see what’s important,” Deepak says. “We can focus on analytics to guide us to success today and in the future.”

When you build a data warehouse, the important question is how to ingest data from the source system to the data warehouse. If the table is small you can fully reload a table on a regular basis, however, if the table is large a common technique is to perform incremental table updates. This post demonstrates how you can enhance incremental pipeline performance when you ingest data into BigQuery.

Setting up a standard incremental data ingestion pipeline

We will use the below example to illustrate a common ingestion pipeline that incrementally updates a data warehouse table. Let’s say that you ingest data into BigQuery from a large and frequently updated table in the source system, and you have Staging and Reporting areas (datasets) in BigQuery.

The Reporting area in BigQuery stores the most recent, full data that has been ingested from the source system tables. Usually you create the base table as a full snapshot of the source system table. In our running example, we use BigQuery public data as the source system and create reporting.base_table as shown below. In our example each row is identified by a unique key which consists of two columns: block_hash and log_index.

  CREATE TABLE reporting.base_table  --156 GB processed
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, DAY) AS
SELECT log_index, data, topics, block_timestamp, block_hash
FROM bigquery-public-data.crypto_ethereum.logs
WHERE block_timestamp BETWEEN TIMESTAMP '2020-01-01' AND TIMESTAMP '2020-11-30';

In data warehouses it is common to partition a large base table by a datetime column that has a business meaning. For example, it may be a transaction timestamp, or datetime when some business event happened, etc. The idea is that data analysts who use the data warehouse usually need to analyze only some range of dates and rarely need the full data. In our example, we partition the base table by block_timestamp which comes from the source system.

After ingesting the initial snapshot you need to capture changes that happen in the source system table and update the reporting base table accordingly. This is when the Staging area comes into the picture. The staging table will contain captured data changes that you will merge into the base table. Let’s say that in our source system on a regular basis we have a set of new rows and also some updated records. In our example we mock the staging data as follows: first, we create new data, than we mock the updated records:

  CREATE TABLE staging.load_delta AS --5 GB processed
SELECT log_index, data, topics, block_timestamp, block_hash
FROM bigquery-public-data.crypto_ethereum.logs
WHERE block_timestamp BETWEEN TIMESTAMP '2020-12-01' AND TIMESTAMP '2020-12-07';
 
INSERT INTO staging.load_delta --2 GB processed
SELECT log_index, CONCAT(data, RAND()), topics, block_timestamp, block_hash
FROM bigquery-public-data.crypto_ethereum.logs TABLESAMPLE SYSTEM (5 PERCENT)
WHERE block_timestamp BETWEEN TIMESTAMP '2020-10-01' AND TIMESTAMP '2020-11-30';

Next, the pipeline merges the staging data into the base table. It joins two tables by unique key and than updates the changed value or inserts a new row

  MERGE INTO reporting.base_table T --161 GB processed
USING staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);

It is often the case that the staging table contains keys from various partitions but the number of those partitions are relatively small. It holds, for instance, because in the source system the recently added data may get changed due to some initial errors or ongoing processes but older records are rarely updated. However, when the above MERGE gets executed, BigQuery scans all partitions in the base table and processes 161 GB of data. You might add additional join condition on block_timestamp:

  MERGE INTO reporting.base_table T --161 GB processed
USING staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp = S.block_timestamp
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);

But BigQuery would still scan all partitions in the base table because condition T.block_timestamp = S.block_timestamp is a dynamic predicate and BigQuery doesn’t automatically push such predicates down from one table to another in MERGE.

Can you improve the MERGE efficiency by making it scan less data? The answer is Yes. 

As described in the MERGE documentation, pruning conditions may be located in a subquery filter, a merge_condition filter, or a search_condition filter. In this post we show how you can leverage the first two. The main idea is to turn a dynamic predicate into a static predicate.

Steps to enhance your ingestion pipeline

The initial step is to compute the range of partitions that will be updated during the MERGE and store it in a variable. As was mentioned above, in data ingestion pipelines, staging tables are usually small so the cost of the computation is relatively low.

  DECLARE src_range STRUCT<date_min TIMESTAMP, date_max TIMESTAMP> --115 MB processed
DEFAULT(SELECT STRUCT(
  MIN(block_timestamp) AS date_min,  
  MAX(block_timestamp) AS date_max) FROM staging.load_delta);

Based on your existing ETL/ELT pipeline, you can add the above code as-is to your pipeline or you can compute date_min, data_max as part of some already existing transformation step. Alternatively, date_min, data_max can be computed on the Source System side while capturing the next ingestion data batch.

After computing date_min, date_max you pass those values to the MERGE statement as static predicates. There are several ways to enhance the MERGE and prune partitions in the base table based on precomputed date_min, data_max. 

If your initial MERGE statement uses a subquery, you can incorporate a new filter into it:

  BEGIN 
DECLARE src_range STRUCT<date_min TIMESTAMP, date_max TIMESTAMP> --115 MB processed
DEFAULT(SELECT STRUCT(
  MIN(block_timestamp) AS date_min,  
  MAX(block_timestamp) AS date_max) FROM staging.load_delta);

MERGE INTO reporting.base_table T --41 GB processed
USING (
  SELECT *
  FROM staging.load_delta
  WHERE block_timestamp BETWEEN src_range.date_min AND src_range.date_max) S 
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp = S.block_timestamp
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);
END;

Note that you add the static filter to the staging table and keep T.block_timestamp = S.block_timestamp to convey to BigQuery that it can push that filter to the base table. This MERGE processes 41 GB of data in contrast to the initial 161 GB. You can see in the query plan that BigQuery pushes the partition filter from the staging table to the base table:

This type of optimization, when a pruning condition is pushed from a subquery to a large partitioned or clustered table, is not unique for MERGE. It also works for other types of queries. For instance:

  SELECT * -- 41 GB processed
FROM reporting.base_table T
INNER JOIN staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp = S.block_timestamp
WHERE S.block_timestamp BETWEEN TIMESTAMP '2020-10-05' AND TIMESTAMP '2020-12-07'

And you can check the query plan to verify that BigQuery pushed down the partition filter from one table to another.

Moreover, for SELECT statements, BigQuery can automatically infer a filter predicate on a join column and push it down from one table to another if your query meets the following criteria:

• The target table must be clustered or partitioned. 
• The result size of the other table, i.e. after applying all filters, must qualify for broadcast join. Namly, the result set must be relatively small, less than ~100MB.

In our running example, reporting.base_table is partitioned by block_timestamp. If you define a selective filter on staging.load_delta and join two tables, you can see an inferred filter on the join key pushed to the target table

  SELECT * 
FROM reporting.base_table T
INNER JOIN staging.load_delta S
ON T.block_timestamp = S.block_timestamp
WHERE S.block_hash = '0x0c1caa16b34d94843aabfebc0d5a961db358135988f7498a6fdc450ad55f0870'

There is no requirement to join tables by partitioning or clustering key to kick off this type of optimization. However, in this case the pruning effect on the target table would be less significant.

But let us get back to the pipeline optimizations. Another way to enhance MERGE is to modify the merge_condition filter by adding static predicate on the base table:

  BEGIN 
DECLARE src_range STRUCT<date_min TIMESTAMP, date_max TIMESTAMP> --115 MB processed
DEFAULT(SELECT STRUCT(
  MIN(block_timestamp) AS date_min,  
  MAX(block_timestamp) AS date_max) FROM staging.load_delta);

MERGE INTO reporting.base_table T --41 GB processed
USING staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp BETWEEN src_range.date_min AND src_range.date_max
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);
END;

To summarize, here are the steps that you can perform to enhance incremental ingestion pipelines in BigQuery. First you compute the range of updated partitions based on the small staging table. Next, you tweak the MERGE statement a bit to let BigQuery know to prune data in the base table.

All the enhanced MERGE statements scanned 41 GB of data, and setting up the src_range variable took 115 MB.  Compare it with the initial 161 GB scan. Moreover, given that computing src_range may be incorporated into some existing transformation in your ETL/ELT, it results in a good performance improvement which you can leverage in your pipelines. 

In this post we described how to enhance data ingestion pipelines by turning dynamic filter predicates into static predicates and letting BiQuery prune data for us. You can find more tips on BigQuery DML tuning here.

Special thanks to Daniel De Leo, who helped with examples and provided valuable feedback on this content.