<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Krisjan Oldekamp on Medium]]></title>
        <description><![CDATA[Stories by Krisjan Oldekamp on Medium]]></description>
        <link>https://medium.com/@krisjanoldekamp?source=rss-eb4d5f7e9290------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*q9GY_VJybtK1MDuTvWgzBw.jpeg</url>
            <title>Stories by Krisjan Oldekamp on Medium</title>
            <link>https://medium.com/@krisjanoldekamp?source=rss-eb4d5f7e9290------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 05:59:43 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@krisjanoldekamp/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Back Up your Valuable BigQuery Views and Scheduled Queries]]></title>
            <link>https://medium.com/data-science/back-up-your-valuable-bigquery-views-and-scheduled-queries-424791b6f2c7?source=rss-eb4d5f7e9290------2</link>
            <guid isPermaLink="false">https://medium.com/p/424791b6f2c7</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[google-big-query]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Krisjan Oldekamp]]></dc:creator>
            <pubDate>Thu, 14 Oct 2021 04:53:54 GMT</pubDate>
            <atom:updated>2021-10-20T08:44:06.882Z</atom:updated>
            <content:encoded><![CDATA[<h4>Your precious SQL needs some love too</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eRBa3I58PrihVqiqunt2JQ.jpeg" /><figcaption>Original image <a href="https://unsplash.com/@karishea">Kari Shea</a></figcaption></figure><p><strong>Making data backups is important. But what about your precious SQL code, hidden away in views and scheduled queries? This tutorial will show you how to back up all the SQL code into a Git repository using Python.</strong></p><p>In many cases, views and scheduled queries contain the result of all the hard work (and many hours spend). They basically contain a lot of building blocks. Think of data transformations, feature engineering, selections or even the models themselves.</p><blockquote><strong><em>Note: </em></strong><em>Do you like this article? Read </em><a href="https://stacktonic.com/article/backup-your-valuable-big-query-views-and-scheduled-queries-using-python"><em>the original article</em></a><em> (and many other hands-on marketing technology related articles) on </em><a href="https://stacktonic.com/"><em>stacktonic.com</em></a><em> </em>🚀</blockquote><p>Also, when a lot users are actively participating in the development of your Google Cloud Platform (GCP) environment, it’s hard to track changes to views and scheduled queries.</p><p>The solution provided in this tutorial will;</p><ul><li><strong>Backup</strong> your BigQuery <strong>views</strong> and <strong>scheduled queries</strong> to .sql files on your local filesystem.</li><li>Ability to commit changes to a <strong>Git</strong> repository. When running the script regularly, you can track <strong>change history</strong>.</li><li>Supports <strong>multiple</strong> GCP projects.</li></ul><h3>Preparation</h3><p>We will use a Service Account to authenticate with your Google Cloud project(s).</p><blockquote><em>You can use the same service account for all the projects by adding this service account with the correct role(s) to all the GCP projects you would like to backup.</em></blockquote><p>If you’re not familiar with setting up a Service Account, read the <a href="https://cloud.google.com/iam/docs/creating-managing-service-accounts">Google documentation</a> first.</p><ul><li>Create a Service Account and enable the BigQuery User role for the account.</li><li>Generate and download the JSON keyfile for the Service Account.</li><li>Enable the Data Transfer API within your GCP project</li></ul><h3>How the script works</h3><ul><li>The Python script will fetch the SQL code of all the views and the scheduled queries within the GCP projects configured in the script.</li><li>Files are downloaded to your local filesystem.</li><li>When you enable the REPO_COMMIT setting, the script will clone a (remote) repository first, fetches the views and scheduled query SQL and commits the changes to the repo. Before it will clone the remote repository, it will delete an old version of the repo first</li></ul><blockquote><em>When using a Git repository to backup your SQL and track change history, you can use a SSH key for authentication. See instructions for </em><a href="https://docs.github.com/en/github/authenticating-to-github/connecting-to-github-with-ssh/adding-a-new-ssh-key-to-your-github-account"><em>GitHub</em></a><em> or </em><a href="https://support.atlassian.com/bitbucket-cloud/docs/set-up-an-ssh-key/"><em>Bitbucket</em></a></blockquote><p><strong>File structure</strong></p><p>The script will create and populate according to the following folder / file structure:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/752/1*tUNtLoefqsZaDVgqYFEfXw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/268/0*c-oWN6EhyXk2Gp3L.PNG" /></figure><h3>Installation and running the code</h3><p><strong>Installing packages</strong></p><p>The script requires some dependencies. Use pip to install these packages:</p><pre>pip install google-auth<br>pip install google-cloud-bigquery<br>pip install GitPython</pre><p>Last step is to adjust the settings in the script:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/744/1*s8jBmkoNFylShRkGQtmk0g.png" /></figure><p>Now you’re all set to run the code:</p><pre>python backup_views_scheduled_queries.py</pre><h3>The Python script</h3><p>Save the code to a file manually or download the code <a href="https://gist.github.com/krisjan-oldekamp/78869851421af2f75325c32302fa2137">directly from our GitHub repository</a>.</p><p><strong>backup_views_scheduled_queries.py</strong></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f267e33e9045e3082d3c2b622b97cb1e/href">https://medium.com/media/f267e33e9045e3082d3c2b622b97cb1e/href</a></iframe><h3>Some last tips</h3><ul><li>You probably want to schedule this script daily. You can use your favorite scheduling tool like <a href="https://stacktonic.com/article/apache-airflow-2-0-examples-a-basic-dag-template-for-any-project">Apache Airflow</a> or good ol’ cronjobs.</li><li>On windows, configuring SSH keys can give you some headaches. See the <a href="https://docs.github.com/en/github/authenticating-to-github/connecting-to-github-with-ssh/generating-a-new-ssh-key-and-adding-it-to-the-ssh-agent">GitHub</a> and the <a href="https://support.atlassian.com/bitbucket-cloud/docs/troubleshoot-ssh-issues/">Bitbucket</a> documentation for troubleshooting.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=424791b6f2c7" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/back-up-your-valuable-bigquery-views-and-scheduled-queries-424791b6f2c7">Back Up your Valuable BigQuery Views and Scheduled Queries</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Create Advanced Google Shopping Insights using Merchant Center BigQuery exports]]></title>
            <link>https://medium.com/data-science/create-advanced-google-shopping-insights-using-merchant-center-bigquery-exports-f81f65399a2c?source=rss-eb4d5f7e9290------2</link>
            <guid isPermaLink="false">https://medium.com/p/f81f65399a2c</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-shopping-ads]]></category>
            <category><![CDATA[insights]]></category>
            <category><![CDATA[google]]></category>
            <category><![CDATA[merchant-center]]></category>
            <dc:creator><![CDATA[Krisjan Oldekamp]]></dc:creator>
            <pubDate>Fri, 10 Sep 2021 12:48:43 GMT</pubDate>
            <atom:updated>2021-10-20T08:45:08.687Z</atom:updated>
            <content:encoded><![CDATA[<h4>Leverage product level <strong>price competitiveness</strong>,<strong> demand and popularity data, based </strong>on all Google Shopping merchants</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hl0heuJ7GmKoKIdT_-hZgg.jpeg" /><figcaption>Original Photo by <a href="https://unsplash.com/@dylu">Jacek Dylag</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p><strong>Product level price competitiveness and benchmark data, already available in the Merchant Center interface, can be exported to Google BigQuery. Leveraging these exports, you can create advanced insights into your product catalog that can be used for automation as well.</strong></p><p>In this tutorial you will learn how to:</p><ul><li>Export product and brand level market insights data, based on data <strong>from all the merchants on Google Shopping</strong> to Google BigQuery</li><li>Enrich your product feed with Merchant Center data like <strong>price competitiveness</strong>,<strong> relative demand </strong>or <strong>product ranking </strong>to create valuable and advanced insights.</li><li>Use the <a href="#2842">example query</a> to combine and join the data to your product feed.</li><li>Use the exports for other <a href="#aceb">practical use cases</a>.</li></ul><blockquote><strong><em>Note: </em></strong><em>Do you like this article? Read </em><a href="https://stacktonic.com/article/create-advanced-google-shopping-insights-using-merchant-center-big-query-exports"><em>the original article</em></a><em> (and many other hands-on marketing technology related articles) on </em><a href="https://stacktonic.com/"><em>stacktonic.com</em></a><em> </em>🚀</blockquote><h3>Connecting Merchant Center to Google BigQuery</h3><p>First step, you will need a Google Cloud Project with an active billing account. If not present already, you can check out <a href="https://cloud.google.com/resource-manager/docs/creating-managing-projects">these instructions</a>.</p><p>The official <a href="https://cloud.google.com/bigquery-transfer/docs/merchant-center-transfer#setting_up_a_google_merchant_center_transfer">Google documentation</a> provides all the info to set up the data transfer service between Merchant Center and Google BigQuery. When the connection is set up, Merchant Center will run a daily export of several reports.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/736/1*fB8qbLNlFyobxGN8yH1DtQ.png" /><figcaption>Source: Google</figcaption></figure><h3><strong>The Merchant Center exports explained</strong></h3><p>Several report types can be exported by the transfer service. You don’t have to export all the reports (can be configured when setting up the export).</p><blockquote>Be aware that the Merchant Center exports are not that small in terms of GBs. For every available Google product category and country, it will export 10.000 top products for example. So it could generate some costs in your Google Cloud project. See the tips in the last chapter of this article.</blockquote><p>The most important exports;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/742/1*cseWBjVQZwECbSRbqkJIpw.png" /></figure><p><em>Also see </em><a href="https://cloud.google.com/bigquery-transfer/docs/merchant-center-transfer"><em>this overview</em></a><em> in the official Google documentation.</em></p><p>Some of the most interesting properties and definition;</p><ul><li><strong>price_benchmark_value</strong>: The average (click-weighted) price of a specific product, based on all merchants who advertise that same product on Shopping ads. Matching products between merchants is based on GTIN.</li><li><strong>rank</strong>: The popularity of the product on Shopping ads, on Google product category and country level. Popularity is based on the estimated number of products sold. The rank updates daily, but data in the exports can be delayed up to 2 days.</li><li><strong>previous_rank</strong>: The change in rank over the previous 7 days.</li><li><strong>relative_demand.min / .max / .bucket</strong>: A product’s current estimated demand in relation to the product with the highest popularity rank in the same category and country. Also includes buckets (e.g. Very High)</li><li><strong>previous_relative_demand.min / .max / .bucket</strong>: Same as above, only calculated on the last 7 days.</li><li><strong>price_range.min` / `price_range.max</strong>: The lower and upper pricerange without decimals. Doesn’t include shipping costs.</li></ul><h3>Practical use cases</h3><p>Combining the different reports, you can think of the following use cases;</p><ul><li><strong>Product feed status</strong>: Get Merchant Center and Shopping errors and notifications related to your product feeds.</li><li><strong>Product inventory optimization</strong>: Using the best seller reports, you can check which popular products are already in your inventory and what potential products could be added, based on the complete list of top products per Google product categories AND country.</li><li><strong>Price and bid optimization:</strong> Using the price competitiveness benchmark information, you can check how you are doing in terms of price against competitors.</li><li><strong>Trending products and brands</strong>: Identify trending products / brands based on the changes in best seller rank over the last X days / weeks / months. Differentiate per country</li><li><strong>Product feed enrichment</strong>: Enrich you product feed with Google market insights data</li><li><strong>Rule based automation</strong>: Based on the product feed enrichment, pause and enable campaigns / products in channels (e.g. Google Shopping) or adjust bids. For example based on price competitiveness.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/836/1*-cNNp12j21fwANvh7iNyRw.png" /><figcaption>Enrich product data with Merchant Center market insights</figcaption></figure><h3>Example query</h3><p>To show you how the data can be combined, I’ve constructed a query that will join and combine data out of different Merchant Center tables on product level.</p><p>Some remarks:</p><ul><li><strong>Google doesn’t provide data for every product in your feed</strong>. Either a product is not in the bestsellers list or Google just doesn’t provide or calculate it (not enough data and probably some other reasons). Or you have unique products that other merchants don’t offer.</li><li>Use the <em>BestSellers_TopProducts_Inventory</em> as a mapping table to join the <em>BestSellers_TopProducts</em> data with your <em>Product </em>data by joining on <em>rank_id</em> (available in both tables)</li><li>The “original” product-ID as known in your systems is not directly available in the different exports. It’s available in the <em>product_id</em> field but you have te split the value and select the last part since Google added additional information to it, example: <em>online:nl:NL:&lt;original-product-id&gt;</em></li><li>The reports contain a lot of data. For example the best seller reports contains 10.000 best sellers in every Google product category and country. For efficiency reasons, try to select only the countries needed. Also important because the same product can be duplicated over different countries in the table Google provides.</li><li>The reports contain nested and repeated data, make sure you understand the table structures. For example the product errors and notifications are nested.</li></ul><h4>Example: Enrich product feed with benchmark and price competitiveness data</h4><p>The following query will result in this table that you could match on your original product feed and could serve as input for automation rules (on product level)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/836/1*-cNNp12j21fwANvh7iNyRw.png" /></figure><blockquote>Make sure you replace the dataset / table names with the correct values in your situation</blockquote><p><a href="https://gist.github.com/krisjan-oldekamp/631e2f029d89538da45f4946f7d5be4f">Code is also available on GitHub</a></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/bcd4dc2ca9790ce29affc6438e5d289e/href">https://medium.com/media/bcd4dc2ca9790ce29affc6438e5d289e/href</a></iframe><h4>Closing thoughts</h4><p>Using the BigQuery Merchant Center exports, you can generate insights at scale and use it for automation. But keep in mind, the exports hold a lot of data, so it could generate some costs in your Google Cloud project (and you probably won’t need all the data). The tables are date partitioned, so you could write a Python script or use Airflow to delete partitions older than X days to keep the amount of data in BigQuery (probably after you’ve created some aggregate reports).</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f81f65399a2c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/create-advanced-google-shopping-insights-using-merchant-center-bigquery-exports-f81f65399a2c">Create Advanced Google Shopping Insights using Merchant Center BigQuery exports</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>