SPEAKER: Determining the right price for your retail products
is a difficult task.
Price items too high, and you might lose business.
Price them too low, and you lower your profit margin.
So how do you incorporate different data sources
to analyze historical pricing trends
and make predictions on future demand?
Today we'll show you how using Google's data cloud.
[MUSIC PLAYING]
There are lots of different data sources
that can help you understand how to optimally price items.
You might have information on transactions,
details on products, like categories and list prices,
and data on the customers themselves.
However, the data required to drive pricing insight
is often disparate, potentially coming from different systems
with their own formats.
One way to make working with these sources more effective
is to combine key attributes into a single cleaned
and standardized schema, which we'll refer to as the Common
Data Model, or CDM.
In this tutorial, we'll use Dataprep by Trifecta
to standardize data coming from different sources
and unify the necessary fields in the CDM table, which
will live in BigQuery, our fully managed, scalable data
warehouse.
Once data is formatted for analysis,
we'll show how analysts can explore historical trends
to understand the impact of prior pricing changes.
Additionally, we'll use BigQuery's machine learning
capabilities to create predictive models that
estimate future sales.
Finally, we'll use the output of these models
within a Looker dashboard to create what-if scenarios, where
business users can analyze what sales
might look like with certain price changes.
So let's jump in.
Inside of Dataprep, you create flows, which
represent a data pipeline.
Within this flow, we have connected
each of our data sets.
In this case, we're using Google Sheets and a table
containing our customer descriptions in BigQuery.
But you can connect a variety of different data sources,
including flat files, transactional databases,
and SaaS tools, like Salesforce.
You might notice that we have one transaction
file for each month, representing the orders that
were placed in that period.
The first thing you'll want to do is combine those files.
In Dataprep, you use recipes to transform and join data
together.
Opening this recipe, you can see that there are actually
two things happening.
First, we're unioning or combining the transactions data
into one single set.
But then we're standardizing customer descriptions.
This is because the names of customers
are sometimes misspelled or represented differently
across transactions.
But with Dataprep, you can use a fuzzy matching algorithm
to standardize customer names with similar strings
where values with characters in common are clustered together
or pronunciation where values that sound alike
are clustered together.
This way, we make sure customers are represented consistently.
Outside of the transactions recipe,
we have other recipes to calculate accurate pricing
using functions that pivot, aggregate,
and join to customer data, and a recipe that automatically
reformats the prepared data structure
to match the existing CDM table schema,
using Dataprep's rapid target functionality.
Finally, the CDM is pushed into BigQuery.
And not only can we manually run this flow,
but we can also set it to run on a scheduled basis,
so the CDM table is always fresh.
With our prepared data in BigQuery,
we're ready to take a look at what's
happened historically when we've changed the price of an item.
Here, we're using standard SQL to create a temporary table
with one row for each price change,
and it contains relevant information,
like the total number of orders at the current price
point compared to orders at the previous price point.
With this temp table in place, we can do things like look
at the average price change or see
how sales have changed relative to the price difference.
Now that we have historical analysis squared away,
let's incorporate machine learning to predict
what will happen in the future.
With BigQueryML, we can build an ARIMA model,
one of the most widely used approaches
for statistical forecasting with time series.
And this will use historical sales
for each product to predict the future demand, all
in just a few lines of SQL.
How cool is that?
So using BigQuery, we're able to draw insights
from huge amounts of data in no time.
But to empower business decision makers,
we want to create a report accessible by the broader team.
This is where Looker comes into play.
With Looker, we create a data model
using Looker's semantic layer, LookML,
that stores the definitions of our metrics using SQL.
Looker uses this model to compile and send
SQL queries back to BigQuery on behalf of non-technical users.
This means we take full advantage of the power
and intelligence that BigQuery offers,
all while giving business users the ability
to explore governed data without needing an analyst
to write SQL for them.
With our LookML model in place, we
can build out a dashboard to influence pricing decisions.
Here, we've built a report that services historical trends,
building off of that temp table we created earlier.
And because Looker compiles SQL on the fly,
we can drill all the way to SKU level detail from any tile.
Scrolling down on the report, we've
also created a what-if tool where decision makers
can see what might happen if they
change the price of products.
For example, if you wanted to see
what would happen if the order volume went down
to 75% of the predicted value and pricing
across all products was raised by 25%.
And the best part, this is all just the beginning.
There are lots more ways that Google Cloud can
help you optimize your retail pricing,
like leveraging geospatial analytics features
to see how the proximity of competitors impacts sales
or incorporating publicly available data,
like weather and search trends into your analysis.
So if you're interested in learning more
about Google's data cloud, then make
sure to check out the links below,
including a step-by-step tutorial of what
we showed today.
[MUSIC PLAYING]