<?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 Khristina Rustanovich on Medium]]></title>
        <description><![CDATA[Stories by Khristina Rustanovich on Medium]]></description>
        <link>https://medium.com/@khristinarustanovich?source=rss-81d04fac4bbb------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*78aUeml7SNv7RhhN08VX6Q.jpeg</url>
            <title>Stories by Khristina Rustanovich on Medium</title>
            <link>https://medium.com/@khristinarustanovich?source=rss-81d04fac4bbb------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 00:09:12 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@khristinarustanovich/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[Google Ads data transfer to BigQuery: step by step]]></title>
            <link>https://khristinarustanovich.medium.com/google-ads-data-transfer-to-bigquery-step-by-step-d42cac1dd62b?source=rss-81d04fac4bbb------2</link>
            <guid isPermaLink="false">https://medium.com/p/d42cac1dd62b</guid>
            <category><![CDATA[google-adwords]]></category>
            <category><![CDATA[google-bigquery]]></category>
            <category><![CDATA[api]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Khristina Rustanovich]]></dc:creator>
            <pubDate>Thu, 14 Jul 2022 15:27:10 GMT</pubDate>
            <atom:updated>2022-07-14T15:50:55.651Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/967/1*3pJL1mujTlKLXJg5bQnCkA.jpeg" /><figcaption>SLUMDOG MILLIONAIRE (2008) from Shotdeck</figcaption></figure><p>Google Ads data transfer is free (you pay only for using Cloud services), simple and stable way to load Google Ads data to BigQuery.</p><p><strong>Step 1</strong> is to create a dataset in BigQuery. For example, <em>raw_google_ads</em></p><p><strong>Step 2</strong> is to set up the daily transfer. Here we just need to go to the <strong>‘Data Transfer’</strong> section in Google BigQuery, press <strong>‘Create Transfer’ </strong>and choose <em>Google Ads</em> as a Source</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/606/1*9G63ViW3oTVSbkHcstGxWA.jpeg" /></figure><p>The settings here are more or less straightforward. Dataset name is the name of the dataset we created on the Step 1. The <em>Customer ID </em>is Google Ads Customer ID. It is possible to define the lookback window or just skip that field.</p><p>That’s all for the daily transfer. Google will handle it daily and automatically.</p><p><strong>Step 3 </strong>is to set up backfills. There are no limitations on how much historical data can be loaded, but we can do it only in 180-day maximum chunks: one after another.</p><p>So the process is:</p><p>Schedule a 180 day (max) backfill -&gt; wait a few days till it is fully loaded -&gt; schedule a new 180 day (max) backfill -&gt; wait a few days till it is fully loaded -&gt; repeat from the beginning.</p><p>It usually takes a week minimum for two 180-day chunks. So loading 2 years of historical data can take up to 2–3 weeks.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/497/1*edZwJ5iIdwFjRB7Kgxsihw.jpeg" /></figure><p>The backfill is completed when all requested days are green in ‘Run history’:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/736/1*kga3RIgR2JLy_0PPt8bFxg.jpeg" /></figure><p><strong>Step 4 </strong>is to query the data. The Google Ads schema is not very clear. So it requires some time to get familiar with it.</p><p>An example query to get started:</p><pre>with stats as (select <br>    date, <br>    CampaignId as campaign_id,<br>    Impressions as impressions, <br>    Clicks as clicks, <br>    (Cost/1000000) as cost,<br>    Conversions as conversions,<br>    ViewThroughConversions as post_view_conversions<br>from `raw_google_ads.p_CampaignBasicStats_YOUR_GOOGLE_ADS_ID`),</pre><pre>campaigns as (<br>select <br>  CampaignId,<br>  CampaignName,<br>  AdvertisingChannelType<br>from `raw_google_ads.p_Campaign_YOUR_GOOGLE_ADS_ID`<br>group by 1,2,3<br>)</pre><pre>select <br>  date,<br>  campaign_id,<br>  b.CampaignName as campaign,<br>  b.AdvertisingChannelType,<br>  impressions, <br>  clicks,<br>  cost,<br>  conversions<br>from stats a<br>left join campaigns b<br>on a.campaign_id = b.CampaignId</pre><p>As we can see here <em>Cost</em> should be divided by 1000000 to get the correct number. I always compare cost data returned by the query with the cost in Google Ads interface to be sure. Nobody is ready to see expenses with 6 zeros more, than real ones on the stakeholder dashboard!</p><p>That’s all here. The Google Ads dataset contains a lot of data on different levels. Happy reporting and analysis!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d42cac1dd62b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Unnest GA4 user properties in BigQuery]]></title>
            <link>https://khristinarustanovich.medium.com/unnest-ga4-user-properties-in-bigquery-99dce1f9957e?source=rss-81d04fac4bbb------2</link>
            <guid isPermaLink="false">https://medium.com/p/99dce1f9957e</guid>
            <category><![CDATA[google-analytics]]></category>
            <category><![CDATA[google-analytics-4]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Khristina Rustanovich]]></dc:creator>
            <pubDate>Thu, 14 Jul 2022 10:02:34 GMT</pubDate>
            <atom:updated>2022-07-14T10:47:10.588Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="“The trouble with Harry”, 1955" src="https://cdn-images-1.medium.com/max/967/1*QohRk43wykB-lFWm9P2eTg.jpeg" /><figcaption>“The trouble with Harry”, 1955</figcaption></figure><p>User properties in Google Analytics 4 (GA4) is a kind of replacement of user scope custom dimensions in Google Analytics 3. Now with GA4 and BigQuery free import the sky is the limit when it comes to flexibility of data modelling. However user properties are nested in the BigQuery dataset. Let’s dig deeper into the GA4 data modelling and unnest them.</p><p>Here I just describe a relatively simple model that allows to create a dynamic report using Data Studio or any other BI tools. Let’s imagine we have an ecommerce website that sell clothes (cool ones, of course!). For each customer we send a user dimension with a favorite color. It is called <em>user_favorite_color</em>. Of course, we want to know who buy more: fans of black, white, orange, green or yellow.</p><p><strong>The first step</strong> is to unnest the user property <em>user_favorite_color</em>. For each <em>user_pseudo_id</em> we get the value of the <em>user_favorite_color </em>property<em>.</em></p><pre>select<br>  parse_date(&#39;%Y%m%d&#39;,event_date) as date,<br>  user_pseudo_id,<br>  user_properties.value.string_value as user_favorite_color<br>from<br>  `your-project.analytics_xxxx.events_*`, <br>  unnest(user_properties) as user_properties<br>where <br>  user_properties.key = &#39;user_favorite_color&#39; <br>  and user_properties.value.string_value is not null<br>group by 1,2,3</pre><p><strong>The second step</strong> is to get events we are interested in for each <em>user_pseudo_id</em>. I don’t count events here, but set <em>user_pseudo_id</em> for each event. So I can count distinct users for a dynamic range automatically using a dashboard tool (Data Studio, Metabase, Tableau etc.)</p><pre>select<br>    parse_date(&#39;%Y%m%d&#39;,event_date) as date,<br>    user_pseudo_id,<br>    ecommerce.purchase_revenue as revenue,<br>    case when event_name = &#39;begin_checkout&#39; then user_pseudo_id end   as begin_checkout,<br>    case when event_name = &#39;purchase&#39; then user_pseudo_id end as purchase<br>from<br>    `your-project. analytics_xxxx.events_*`,</pre><p><strong>The third step </strong>is to put it all together into a query. Note: this query can be shorter and more efficient. We are sacrificing efficiency for clarity here.</p><pre>create or replace table `ga4_models.user_colors` as (<br>with user_colors as (<br>select<br>    parse_date(&#39;%Y%m%d&#39;,event_date) as date,<br>    user_pseudo_id,<br>    user_properties.value.string_value as user_favorite_color<br>from<br>    `your-project. analytics_xxxx.events_*`, <br>     unnest(user_properties) as user_properties<br>where <br>     user_properties.key = &#39;user_favorite_color&#39; and     user_properties.value.string_value is not null<br>group by 1,2,3<br>),</pre><pre>ecommerce_events as (<br>  select<br>    parse_date(&#39;%Y%m%d&#39;,event_date) as date,<br>    user_pseudo_id,<br>    ecommerce.purchase_revenue as revenue,<br>    case when event_name = &#39;begin_checkout&#39; then user_pseudo_id end  as begin_checkout,<br>    case when event_name = &#39;purchase&#39; then user_pseudo_id end as purchase<br>from<br>    `your-project. analytics_xxxx.events_*`<br>)</pre><pre>select <br>   a.date,<br>   a.user_pseudo_id,<br>   b.user_favorite_color,<br>   a.begin_checkout,<br>   a.purchase,<br>   max(revenue) as revenue<br>from ecommerce_events a<br>left join user_colors b on a.date = b.date and a.user_pseudo_id = b.user_pseudo_id<br>group by 1,2,3,4,5<br>)</pre><p><strong>The fourth step</strong> is to connect the BigQuery table to a BI tool.</p><p>Here is a Data Studio example of a possible output (data is not real):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/886/1*ea2sRr403otAvynY3jRJ4A.jpeg" /></figure><p>Customers who prefer the yellow color buy more. We should definitely sell more yellow T-Shirts!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=99dce1f9957e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[8 most actionable metrics for self-service analytics]]></title>
            <link>https://khristinarustanovich.medium.com/8-most-actionable-metrics-for-self-service-analytics-12ef7538d617?source=rss-81d04fac4bbb------2</link>
            <guid isPermaLink="false">https://medium.com/p/12ef7538d617</guid>
            <category><![CDATA[kpi]]></category>
            <category><![CDATA[bi-dashboards]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Khristina Rustanovich]]></dc:creator>
            <pubDate>Thu, 16 Jun 2022 07:21:30 GMT</pubDate>
            <atom:updated>2022-06-16T11:12:38.947Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/967/1*YOaQL38YIHFmJveiO4cgQw.jpeg" /></figure><p>When I was studying at school my literature teacher told us that there shouldn’t be more than 8 thoughts in an essay. So, the trick was to formulate these 8 thoughts and only then start writing something. It was completely OK to have 5 or even 3 thoughts, but no more than 8.</p><p>Why is that? Communication is not an easy thing. You can’t communicate too much in one shot.</p><p>Isn’t it the same with dashboards and analytics? Companies invest money in implementation of tens if not hundreds Google Analytics events, expensive Tableau dashboards and ETL pipelines, more complex than a skyscraper water system. After it nothing usually happens. Nobody uses this data. It is tempting to say that people are too lazy or not educated enough. But do you need a special education to know that your car is running out of gas? No, it is the metric you know, understand and care. Why it should be different for marketing or customer analytics?</p><p>Every person within an organization probably has metrics they really care about. May be 3 metrics or 5, but not 20. There is also a very high possibility that these metrics are simple, don’t require AI and Python. These metrics shouldn’t be defined by data engineers or data collection experts. These are metrics that should be important for someone who look at them every day.</p><p>How to define such metrics? We can, of course, open Google Analytics documentation and see tens of metrics in there. If you work with GA a lot you will even memorize how they are calculated and can participate in intellectual discussions related to Bounce Rate calculations or sampling, but in the end of the day we need metrics we really care about.</p><p>Which user actions would you consider as an indicator of your website success? Bounce rate or engaged time spent on the product page? Transactions or LTV? Scrolled 75% or video watched 90%? Newsletter subscription or product list view? There are so many of them. Choose no more than 8, put them into a dashboard and see if it works better than 20+ complex metrics which were defined by a technical expert team 2 years ago and nobody remember why.</p><p>So to say 8 most actionable metrics in analytics are the ones we defined ourselves. There are no magic packages or ready solutions.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=12ef7538d617" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[3 ways to divide safely in BigQuery]]></title>
            <link>https://khristinarustanovich.medium.com/3-ways-to-divide-safely-in-bigquery-5e3e5c9302e3?source=rss-81d04fac4bbb------2</link>
            <guid isPermaLink="false">https://medium.com/p/5e3e5c9302e3</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[google-bigquery]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Khristina Rustanovich]]></dc:creator>
            <pubDate>Mon, 14 Jun 2021 15:22:04 GMT</pubDate>
            <atom:updated>2021-06-14T15:51:03.434Z</atom:updated>
            <content:encoded><![CDATA[<p>We all learned in school that division by zero is an impossible operation in ordinary arithmetic. George Berkeley learned it much earlier and wrote about it in his book “Analyst” (1734). Many people disagreed with him. As he was a satirical writer he called such people ‘’free-thinkers”.</p><p>Probably he was right as in September 1997 a division by zero error on USS Yorktown cruiser broke all the machines in the network and as result, the cruiser was broken.</p><p>Such trivial at first glance errors can cause a huge headache in an analyst’s life as well.</p><p>Let’s say we have a table of customers and their regular cat food orders over the last month. We just want to calculate the average order value (AOV) for last month.</p><p>Our dataset is simple.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/294/0*kIxNYitTG-tyhitR" /></figure><p>It’s just a table with the customer’s name, revenue and number of orders made during the last month.</p><p>Our query to calculate AOV isn’t very complicated either.</p><pre>SELECT customer_, (revenue / orders) as aov FROM `dummy_customers.customers`</pre><p>However, it generates an error.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/578/0*78XZbaMpBrGO09So" /></figure><p>Not all of the clients in our dataset made orders in the last month. Mary didn’t make an order!</p><p>One of the ways to solve the problem is to use the CASE function:</p><pre>SELECT</pre><pre>customer_,</pre><pre>(CASE</pre><pre>WHEN orders &gt; 0 THEN revenue / orders</pre><pre>ELSE</pre><pre>0</pre><pre>END</pre><pre>) AS aov</pre><pre>FROM</pre><pre>`dummy_customers.customers`</pre><p>Query results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/333/0*ZqMcakudMCtaQ95p" /></figure><p>Even though it works perfectly it doesn’t seem like an elegant technical solution. Too much code!</p><p>Another way to handle this problem is a mathematical function IEEE_DIVIDE which divides X by Y, but never fails if Y is 0. I love the phrase ‘’never fails”.</p><p>Let’s run this query. Our X will be revenue and our Y will be orders</p><pre>SELECT</pre><pre>customer_,</pre><pre>IEEE_DIVIDE(revenue, orders) AS aov</pre><pre>FROM</pre><pre>`dummy_customers.customers`</pre><p>The result is:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/453/0*GK3Twndyx7RpwPc3" /></figure><p>We are good now as we get results without any errors and we also don’t have to write a long case statement just to handle zero.</p><p>SAFE_DIVIDE function can handle this issue elegantly as well. What it does is return NULL in case of error.</p><pre>SELECT</pre><pre>customer_,</pre><pre>SAFE_DIVIDE(revenue, orders) AS aov</pre><pre>FROM</pre><pre>`dummy_customers.customers`</pre><p>Query results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/303/0*h4Qu5mxxAjm5MqFi" /></figure><p>The results are amazing. Our query returns NULL for customers who didn’t have any orders last month.</p><p>Basically the prefix SAFE_ can be added to other BigQuery functions to handle errors. For example, SAFE_CAST or SAFE_SUBSTR</p><p>We reviewed 3 ways to handle the “division by zero” error in BigQuery: CASE WHEN, IEEE_DIVIDE and SAFE_DIVIDE. So we will never break our analytics cruiser in BigQuery.</p><p>That leaves only one question. Why didn’t Mary order anything from us last month?</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5e3e5c9302e3" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>