How to create the tool
A step-by-step guide
A step-by-step guide
The following step-by-step guide navigates you through all the steps required to create your own Assortment Analytics tool.
Note: in the process of doing so, you will be using BigQuery's computing- and storage-capacity and will incur respective costs.
In order to set up your own version of the tool, you will need:
Access to BigQuery's graphic user interface.
Access to a Merchant Center product feed.
Access to Google's Data Studio.
Go to BigQuery
Expand the BigQuery menu, hit "Data Transfers" and then "+ Create transfer"
Select "Google Merchant Center" as the source
Specify a display name for your data transfer (example: "Merchant Center data transfer")
Specify the schedule frequency of your preference (example: weekly)
Select the dataset you want the data to be imported to or create a new one (example: "merchant_center_dataset")
Enter the ID of the Merchant Center you want to use as the product feed
Select (at least) the following tables: "Products & product issues", "Price competitiveness", "Best Sellers"
Hit "Save"
Check out this Github and copy the content of the file 01_top_products.sql
Go to BigQuery
Expand the BigQuery menu, hit "SQL workspace" and then "Compose new query"
Paste the script into the query editor and replace the list of parameters mentioned in the header of the scripts (for example by using the "Find and replace all"-function)
Define the name of the table you want the query to be stored in by clicking "More" > "Query settings" > "Set destination table for query results", specifying the table name to be "top_products", activating the "Overwrite table"-option and then hitting "Save"
Run the query - after this, you should be able to see the top_products table in your specified data set
[OPTIONAL] Schedule the query if you want the tool to update regularly. Do this by clicking "Schedule" > "Create new scheduled query", specifying a name (example: "Assortment Analytics top products") and the schedule frequency (e.g. weekly) and selecting "Overwrite table".
Repeat all steps for the other two scripts on Github (02_top_products.sql and 03_price_benchmark.sql) by specifying the table names to be "top_brands" and "product_benchmark" respectively.
Open this dashboard-template
Click "Use template" and then "Copy report" (without editing any of the data sources)
Enter the "Edit"-mode
Click "Resource" > "Manage added data sources"
Find the "Top products" data source and hit the "Edit"-option on the right side of it. Then navigate to the BigQuery-project and dataset you specified in step 2, select the "top_products"-table and hit "Reconnect". Repeat this step for the other two data sources "Top brands" and "Price benchmark" by selecting the according tables.
Done! Don't forget to share the tool with your colleagues to make sure they have access!