<?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 Lace Chantelle Rogers on Medium]]></title>
        <description><![CDATA[Stories by Lace Chantelle Rogers on Medium]]></description>
        <link>https://medium.com/@lace-chantelle-rogers?source=rss-a7b267f4c117------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*mpf8wXItY7KleXK6aTTXuA@2x.jpeg</url>
            <title>Stories by Lace Chantelle Rogers on Medium</title>
            <link>https://medium.com/@lace-chantelle-rogers?source=rss-a7b267f4c117------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 11:48:53 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@lace-chantelle-rogers/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[From Measurement to Optimisation: How the Mobile App Stack Fits Together]]></title>
            <link>https://lace-chantelle-rogers.medium.com/from-measurement-to-optimisation-how-the-mobile-app-stack-fits-together-c489ff798bca?source=rss-a7b267f4c117------2</link>
            <guid isPermaLink="false">https://medium.com/p/c489ff798bca</guid>
            <category><![CDATA[firebase]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[digital-marketing]]></category>
            <category><![CDATA[mobile-app-development]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Lace Chantelle Rogers]]></dc:creator>
            <pubDate>Sat, 26 Sep 2026 19:30:31 GMT</pubDate>
            <atom:updated>2026-09-26T19:30:31.722Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JO10uIMqAucYLumOIhnWXQ.png" /></figure><p>In my previous post, I focused on why app measurement should start before instrumentation — defining what success looks like, mapping the core customer journey and understanding what needs to be measured.</p><p>The next challenge is understanding the measurement ecosystem itself. App measurement rarely sits in one place; ASO, MMPs, app-store analytics, Firebase and experimentation all answer different questions across the customer journey.</p><p>In this post, I’ll look at how these tools fit together, when to use them and how they support a continuous cycle of <strong>measure, learn, optimise and grow</strong>.</p><h4>Firstly, what do the acronyms actually mean?</h4><ul><li><strong>ASO — App Store Optimisation:</strong> Improving how easily your app is discovered in the App Store or Google Play and how effectively the store listing converts visitors into installs.</li><li><strong>MMP — Mobile Measurement Partner:</strong> A platform that helps attribute app installs and downstream activity back to the marketing source or channel that drove the user.</li><li><strong>In-app measurement:</strong> Not technically an acronym, but this covers measuring what users do once they enter the app, including onboarding, activation, conversion, feature usage and retention.</li></ul><h4>Discovery and Store Performance — App Store Optimisation (ASO)</h4><p>Before a user can engage with your app, they first need to find it and decide whether it is worth downloading. That makes app-store discovery and conversion a key part of the measurement journey.</p><p>ASO helps you understand how visible your app is and how effectively the store listing converts interest into installs. This becomes particularly valuable when you are investing in acquisition. Your ads may be performing well, but if the store page, reviews or searchability are weak, that can become an unseen bottleneck.</p><p>ASO helps separate those issues. If users are reaching the store page but not downloading, the problem may sit with the proposition, creative or reviews rather than the campaign itself. Equally, if ads are driving traffic but users are not engaging with the store listing, there may be a mismatch in audience or messaging.</p><p>I saw this first-hand while working on a game that was struggling to convert. Analysis showed users were being lost at the store stage. After redesigning the landing page, downloads improved. Without ASO measurement, it would have been much harder to identify the problem or quantify the impact of the change.</p><p>That is why ASO should be treated as an ongoing optimisation activity, not just a pre-launch task.</p><h4>Acquisition and Attribution — Mobile Measurement Partners (MMPs)</h4><p>MMPs are often introduced once paid acquisition begins, but where possible, I would consider them much earlier in the measurement journey.</p><p>Their value is not limited to paid installs. MMPs can help connect a broader range of activity — including email, push notifications, owned channels and campaign activity — to installs and downstream behaviour. Bringing this data into your measurement stack from the start gives you a more complete view of how users are arriving and what they do afterwards.</p><p>They can also help you understand your earliest users. Early adopters may be some of your most engaged or brand-loyal customers, and understanding how they were acquired, how they onboard and which behaviours they exhibit can help inform both product design and future audience strategy.</p><p>The real value of an MMP is therefore not simply answering <strong>“which campaign drove the install?”</strong> It is helping you understand <strong>which sources are driving users who activate, engage, retain and ultimately create value</strong>.</p><h3>In-App Behaviour — Product Analytics and Firebase</h3><p>Getting the install is only the beginning. In-app measurement helps you understand what users do once they arrive and, more importantly, whether they are reaching the value you designed the app to deliver.</p><p>Before collecting large volumes of events, it is important to define a clear measurement framework. Be explicit about what you want to understand, which behaviours indicate success, and how the data will be used to inform product decisions.</p><p>Early on, I would put particular focus on the onboarding funnel, core conversion drivers and the events that represent meaningful progress through the app. These need to be tracked accurately because they form the foundation for understanding where users engage, where they drop out and what ultimately drives activation or conversion.</p><p>Tools such as Firebase and GA4 can then help you measure those behaviours through events, funnels, audiences and retention analysis. And most importantly connect your digital real estate — from your website to your app.</p><p>I’ll go into much more detail on measurement frameworks, event design and implementation in a future article, but the key principle is simple: <strong>define the questions and outcomes first, then design the tracking around them.</strong></p><h3>Bringing It All Together</h3><ul><li><strong>ASO helps improve discovery and store conversion.</strong> It shows whether users are finding the app and whether the store listing is persuading them to install.</li><li><strong>MMPs connect acquisition to outcomes.</strong> They help you understand which sources and channels are driving installs, activation and higher-value users.</li><li><strong>In-app measurement shows what users do after install.</strong> It helps you understand onboarding, activation, feature usage, conversion, retention and drop-off.</li><li><strong>Together, they reduce blind spots.</strong> Instead of optimising each stage in isolation, you can connect discovery, acquisition and behaviour into one measurement cycle.</li><li><strong>That connection makes optimisation actionable.</strong> You can identify where performance is breaking down, focus effort on the right part of the journey, and measure whether changes are improving outcomes.</li></ul><p>App measurement becomes much more valuable when ASO, attribution and in-app behaviour are treated as connected parts of the same journey rather than separate reporting streams.</p><p>The goal is not to collect more data. It is to understand where users are succeeding, where they are dropping out, and which signals should influence the next product or marketing decision.</p><p>In the next post, I’ll take this a step further and look at how to build a practical mobile app measurement framework — from defining the core journey and activation points through to choosing the events and metrics that actually matter.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c489ff798bca" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Design Your App for Measurement from Day One]]></title>
            <link>https://lace-chantelle-rogers.medium.com/design-your-app-for-measurement-from-day-one-7eca686af73f?source=rss-a7b267f4c117------2</link>
            <guid isPermaLink="false">https://medium.com/p/7eca686af73f</guid>
            <category><![CDATA[mobile-measurement]]></category>
            <category><![CDATA[mobile-app-development]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[product-design]]></category>
            <category><![CDATA[apps]]></category>
            <dc:creator><![CDATA[Lace Chantelle Rogers]]></dc:creator>
            <pubDate>Sat, 26 Sep 2026 11:53:20 GMT</pubDate>
            <atom:updated>2026-09-26T11:53:20.426Z</atom:updated>
            <content:encoded><![CDATA[<h4>Before You Build Your App, Define How You’ll Measure Success</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2VixLbgTJJHxOPBt9C_T6g.png" /></figure><p>Let’s face it, the digital marketing landscape is changing. Apps have become an integral part of how companies build their brands, engage customers, drive conversion and improve retention. We are no longer in a world where websites and apps can be treated in isolation; they are increasingly part of the same connected customer journey.</p><p>While deciding that your business needs an app can seem straightforward, defining what success looks like is much more complex. Measuring an app requires a different approach to what you may be used to with a website, particularly when the customer journey spans acquisition, app-store discovery, download, onboarding and ongoing engagement.</p><p>I have been working with apps since 2014, and in that time I have seen both app product design and measurement evolve significantly. I have seen where the right combination of clear objectives, thoughtful product design and effective measurement can drive real growth. I have also seen where apps can struggle or fail when measurement is treated as something to add later, when the product becomes overloaded with features, or when teams are unable to clearly identify where users are dropping out of the journey.</p><p>This series will look at that end-to-end journey, from app product design through to deployment and ongoing optimisation, exploring how measurement and qualitative insight can support better product decisions along the way.</p><h3>Getting Back to Basics</h3><p>Before getting into the detail, it is worth getting back to basics.</p><p>When building an app, it can be easy to become focused on features, technology and delivery timelines, but the most important questions are often the simplest ones.</p><p>What is the app there to achieve? Who is it for? What does a successful customer journey look like? And, importantly, how will you know if it is working?</p><p>Answering these questions early creates the foundation for how the app is designed, measured and optimised over time.</p><h3>Define What Success Looks Like Before You Build</h3><p>The first step is to be clear on why you need an app and, more importantly, what the first iteration needs to achieve.</p><p>It is easy for an initial roadmap to become a long list of features, but not everything needs to be delivered at launch. Instead, focus on the core customer journey and the behaviour you ultimately want to influence.</p><p>Think of the first version as an MVP: focused enough to deliver the core experience well, while giving you the opportunity to measure, learn and iterate before expanding the product.</p><p>Before defining features, ask yourself four key questions:</p><ol><li><strong>What business problem is the app trying to solve?</strong><br>Is the goal to increase revenue, improve retention, reduce service costs, increase engagement or create a new digital channel?</li><li><strong>Who is the app primarily for?</strong><br>Be clear on the audience you are designing for and the need or problem you are helping them solve.</li><li><strong>What is the single most important journey the first version needs to deliver well?</strong><br>This helps separate the core experience from features that can be added later.</li><li><strong>What would success look like for both the customer and the business?</strong><br>This could be a completed purchase, successful onboarding, repeat usage, subscription, booking or another meaningful outcome.</li></ol><p>Answering these questions gives you a clearer foundation for deciding what to build and what to measure.</p><p>It also reduces noise. If the first version has a focused purpose and journey, it becomes much easier to understand whether it is working and whether future changes or features are actually improving the experience.</p><h3>Map the Core Customer Journey</h3><p>Once you are clear on what success looks like, the next step is to map the journey that gets users there.</p><p>This does not need to include every feature or every possible use case. Focus on the journey that matters most in the first iteration of the app.</p><p>What needs to happen from the moment someone discovers your app through to the point where they experience its core value?</p><p>For many apps, this could look something like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*anzz9eX_wQjUWgSHTB4mcg.png" /></figure><p>Mapping this journey early helps identify the moments that matter most, as well as the points where users may encounter friction or leave altogether.</p><p>At each stage, ask:</p><ul><li><em>What is the user trying to do?</em></li><li><em>What does success look like at this point?</em></li><li><em>Where could friction occur?</em></li><li><em>What would I need to measure to understand whether this stage is working?</em></li></ul><p>This is where measurement starts to become part of product design.</p><p>Rather than adding analytics after the app is built, you are designing the journey with a clear understanding of what you will need to observe, learn from and optimise.</p><h3>Define How You Will Measure the Journey</h3><p>Once you have mapped the core customer journey, the next step is to define how each stage will be measured.</p><p>The important point is not to begin with a list of tools. Start with the questions you need to answer.</p><p>For example:</p><ul><li>How are customers finding your website or landing page?</li><li>Which campaigns or channels are driving app installs?</li><li>How are users discovering and converting through the app store?</li><li>Are users completing onboarding?</li><li>Which actions indicate activation or value?</li><li>Are users returning and continuing to engage?</li></ul><p>Different tools will help answer different parts of that journey.</p><p>Website analytics can help you understand pre-app behaviour. App Store and Google Play data can help you understand store performance and conversion. Mobile Measurement Partners can provide attribution across acquisition channels, while tools such as Firebase can help you understand what users do once they are inside the app.</p><p>The important point is that no single tool gives you the full picture.</p><p>App measurement is an ecosystem. Acquisition, attribution, app-store performance and in-app behaviour each provide a different part of the customer journey, and your measurement approach should bring those pieces together.</p><h3>Measure, Learn and Build From There</h3><p>Once you have defined the purpose of the app, mapped the core journey and identified what needs to be measured at each stage, you can bring this together into a simple measurement plan.</p><p>It does not need to be overly complicated.</p><p>The objective is to create a clear view of what you are trying to achieve, what you need to observe and how those insights will influence future product decisions.</p><p>A simple measurement plan should capture:</p><ul><li><strong>The business objective</strong> — what the app is ultimately expected to achieve.</li><li><strong>The measurement question</strong> — what you need to understand or learn.</li><li><strong>The core customer journey</strong> — the key stages users need to move through.</li><li><strong>The success measure</strong> — the outcome that indicates the journey is working.</li><li><strong>The key drop-off points</strong> — where users may leave or encounter friction.</li><li><strong>The data source</strong> — where the evidence will come from.</li><li><strong>The action</strong> — how the insight could influence optimisation, testing or future development.</li></ul><p>For example, if one of your objectives is to increase the number of users who successfully complete onboarding, your measurement plan should not stop at the completion rate.</p><p>You should also be able to understand where users are dropping out, whether certain acquisition sources behave differently, and which parts of the onboarding journey may need to be improved.</p><p>This creates a continuous cycle:</p><p><strong>Measure → Learn → Optimise → Iterate</strong></p><p>Rather than treating launch as the end of the product-development process, measurement becomes an input into the next iteration.</p><p>Start by improving the core journey. Then use what you learn to prioritise enhancements, new functionality and additional customer journeys.</p><p>The key is to keep the initial measurement plan focused. You do not need to measure everything from day one. Start with the questions that matter most to the success of the first version, then expand your measurement approach as the product grows.</p><h3>This Is Only the Starting Point</h3><p>A measurement strategy becomes much more valuable once you begin connecting acquisition, attribution, app-store performance, onboarding and in-app behaviour.</p><p>In the next posts in this series, I will break down each of these areas in more detail, looking at the tools, metrics and approaches you can use to understand performance, identify opportunities and turn measurement into an ongoing optimisation process.</p><p>If you are interested in deeper measurement with Firebase do check out my post: <a href="https://medium.com/@lace-chantelle-rogers/getting-started-on-the-bigquery-ga4-and-firebase-datas-57acc7801823">https://medium.com/@lace-chantelle-rogers/getting-started-on-the-bigquery-ga4-and-firebase-datas-57acc7801823</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7eca686af73f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Become a BigQuery pro by knowing the basics]]></title>
            <link>https://lace-chantelle-rogers.medium.com/become-a-bigquery-pro-by-knowing-the-basics-00485a153426?source=rss-a7b267f4c117------2</link>
            <guid isPermaLink="false">https://medium.com/p/00485a153426</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[gcp]]></category>
            <dc:creator><![CDATA[Lace Chantelle Rogers]]></dc:creator>
            <pubDate>Sat, 23 Mar 2024 12:05:52 GMT</pubDate>
            <atom:updated>2024-03-23T12:05:52.612Z</atom:updated>
            <content:encoded><![CDATA[<p>Often, we’re eager to tackle complex challenges in programming, but laying a solid foundation in the fundamentals ensures our more advanced code functions seamlessly. No matter how long you’ve been writing SQL, it’s easy to overlook the basics.</p><p>With that in mind, In this series of 10 blogs, I would like to share my favourite tips for mastering BigQuery like a pro. These insights aim to reconnect you with essential practices that might have slipped your mind, ensuring your projects are not just ambitious but also impeccably executed.</p><h4>1:BigQuery Dremel</h4><p>BigQuery’s exceptional speed in processing massive queries is largely due to Dremel, its foundational technology. Dremel enables BigQuery to process data by columns rather than by rows, which is a departure from the row-based data storage you might be accustomed to with SQL Server and other databases. This columnar processing means that data retrieval is optimised for speed and efficiency, particularly beneficial for analytical queries.</p><p>However, to fully leverage BigQuery’s capabilities, it’s crucial to adapt your approach to data management. This includes minimising the number of columns your queries read— as each additional column can affect performance — and reconsidering the reliance on common SQL functions like LIMIT, which does not have the same performance implications in a columnar storage context. By aligning your practices with BigQuery’s strengths, you can achieve more efficient data processing and quicker insights</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/0*QV0LFXTrFBu3Npy_.PNG" /></figure><p>This is still one of my favourite articles on what is under the hood on BigQuery and I highly recommend a read.</p><p><a href="https://cloud.google.com/blog/products/bigquery/bigquery-under-the-hood">BigQuery under the hood: Google&#39;s serverless cloud data warehouse | Google Cloud Blog</a></p><h4>2:Limit does not reduce the amount of data processed:</h4><p>In many SQL dialects, using LIMIT can help reduce the amount of data your query processes, which can save on processing time and costs. However, this isn’t the case with BigQuery. Due to its columnar-based structure, BigQuery processes the full dataset before applying the LIMIT clause, so your data processing costs remain the same regardless.</p><p>This makes it crucial to rethink when and why you use LIMIT. Personally, I find LIMIT most useful for scenarios where I need to sort data and retrieve only the top <strong><em>X</em></strong> records. It’s a great tool for these specific cases, but it’s important to understand that it won’t help reduce your query’s cost in BigQuery.</p><p><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#limit_and_offset_clause">https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#limit_and_offset_clause</a></p><h4>3:Do not use Select *</h4><p>While it may be tempting to use SELECT * to quickly grab all columns from a table, this approach can significantly increase the cost of your queries in BigQuery. Remember, BigQuery charges based on the amount of data processed, and selecting unnecessary columns means processing more data than needed. Instead, it’s wise to carefully consider which columns you actually need for your analysis. Developing a clear analysis plan can help with this, guiding you to identify the specific data and columns that will answer your key questions.</p><p>Fortunately, BigQuery offers robust preview and schema viewing capabilities for all native tables, making it straightforward and cost-free to explore the contents of your tables before running your queries. By leveraging these features, you can gain a deep understanding of your data’s structure and ensure that your queries are both cost-effective and tailored to your analysis needs.</p><p><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_">https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_</a></p><h4>4:Use Dry run</h4><p>Utilising BigQuery’s dry run functionality before executing your queries is a simple yet crucial step in managing your data analysis costs effectively. A dry run allows you to estimate the amount of data your query will process, without incurring any charges or actually running the query. If you notice that a dry run estimates more than 10GB of data processing, it’s a strong signal to reevaluate and optimise your code.</p><p>This practice is especially important when integrating BigQuery with external tools like Python or Looker Studio, as inefficient queries can lead to unnecessary spending regardless of the interface used.</p><p>Optimising a query may involve refining your selection criteria, limiting the scope of data processed, or leveraging more efficient functions and joins. Beyond cost savings, these optimisations can also improve query performance and execution speed, enhancing your overall data analysis workflow.</p><p>Remember, the goal is not just to reduce costs but to ensure that your queries are as efficient and effective as possible, aligning with your analytical objectives while minimising resource consumption.</p><p><a href="https://cloud.google.com/bigquery/docs/running-queries#dry-run">Run a query | BigQuery | Google Cloud</a></p><pre>from google.cloud import bigquery<br><br># Construct a BigQuery client object.<br>client = bigquery.Client()<br><br>job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)<br><br># Start the query, passing in the extra configuration.<br>query_job = client.query(<br>    (<br>        &quot;SELECT name, COUNT(*) as name_count &quot;<br>        &quot;FROM `bigquery-public-data.usa_names.usa_1910_2013` &quot;<br>        &quot;WHERE state = &#39;WA&#39; &quot;<br>        &quot;GROUP BY name&quot;<br>    ),<br>    job_config=job_config,<br>)  # Make an API request.<br><br># A dry run query completes immediately.<br>print(&quot;This query will process {} bytes.&quot;.format(query_job.total_bytes_processed))</pre><h4>5:Distinct is not your friend</h4><p>The DISTINCT keyword is a powerful tool in SQL for removing duplicate rows from your results, making it invaluable for de-duplicating data. However, it’s essential to understand why duplicates exist in your dataset in the first place. Duplicates might not always be true duplicates; they could be updated records, entries with minor misspellings, or records that differ only in timestamps and might be better handled through aggregation.</p><p>Before resorting to DISTINCT, thoroughly examine your data to understand the nature of these duplicates. For instance, what seems like duplicate entries might represent data points collected at different times, indicating a need for a more nuanced approach to data cleaning.</p><p>An effective alternative to blanket de-duplication is the QUALIFY function, which allows for more precise control over which duplicates to remove based on specific criteria. This can be particularly useful in situations where you want to keep only the most recent record or when duplicates have significant differences.</p><p>When using DISTINCT or QUALIFY, it’s a best practice to include comments in your code explaining why you’re removing duplicates in this manner. This not only aids in your understanding when revisiting the code but also helps others who may work with your queries to grasp the logic behind your data-cleaning process.</p><p>In summary, while DISTINCT can clean your data effectively, a deeper understanding of your data’s unique characteristics and strategic use of functions like QUALIFY can lead to more accurate and insightful data analysis.</p><ul><li><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_distinct">Query syntax | BigQuery | Google Cloud</a></li><li><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause">Query syntax | BigQuery | Google Cloud</a></li></ul><p>So that’s it for now but keep an eye out for the next post on my top tips for working with BigQuery to help you BigQuery like a pro.</p><p>I’d love to hear about your own journey with BigQuery. What challenges have you faced, and what tips have you found indispensable? Let’s embark on this journey to BigQuery mastery together, ensuring our fundamentals are as strong as our aspirations.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=00485a153426" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GA4 and BigQuery: how to match the Console traffic grouping metrics]]></title>
            <link>https://lace-chantelle-rogers.medium.com/ga4-and-bigquery-how-to-match-the-console-traffic-grouping-metrics-59b2caeff294?source=rss-a7b267f4c117------2</link>
            <guid isPermaLink="false">https://medium.com/p/59b2caeff294</guid>
            <category><![CDATA[google-analytics]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[digital-marketing]]></category>
            <category><![CDATA[ppc]]></category>
            <dc:creator><![CDATA[Lace Chantelle Rogers]]></dc:creator>
            <pubDate>Mon, 04 Sep 2023 17:17:26 GMT</pubDate>
            <atom:updated>2023-09-04T17:17:26.850Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Google Analytics 4 offers excellent functionality with its GA4 BigQuery dataset. However, you might find it challenging to align your GA4 UI figures with the unnested sources, mediums, and campaigns in BigQuery.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/744/1*pACHC5Fa1rXjpSHLL5BXMw.png" /><figcaption>The GA4 demo account</figcaption></figure><h4>So what happens in BigQuery compared to GA4?</h4><p>When data is generated, it’s sent directly to BigQuery as raw data without any pre-processing. However, once it’s in GA4, Google applies various models and transformations. This includes joining data, modeling session traffic, adding cost data, and replacing ISO2 codes with language names.</p><h3>Matching up your numbers</h3><p>If you unnest the data in GA4 to extract the session source, you’ll notice significant discrepancies between your GA4 UI and GA4 BigQuery data, particularly if you have extensive paid and social activity.</p><p>The sample code below displays the raw traffic source, resulting in more users appearing as ‘direct’ or ‘organic’ than in the UI. This discrepancy arises because the data is unprocessed.</p><pre>SELECT <br>DISTINCT<br>(select value.string_value from unnest(event_params) where key = &#39;source&#39;)  AS source, <br>(select value.string_value from unnest(event_params) where key = &#39;medium&#39;)  AS medium,<br>count(distinct(concat(user_pseudo_id,&#39;.&#39;,<br>(select cast(value.int_value as string) from unnest(event_params) where key = &#39;ga_session_id&#39;)))) AS session_count<br> FROM `analytics_123456789.events_20230831` <br> WHERE event_name = &#39;page_view&#39;<br> AND (select value.int_value from unnest(event_params) where key = &#39;entrances&#39;) =1 <br> GROUP BY 1,2<br> ORDER BY 3 DESC<br> ;</pre><p>To mirror the GA4 User Interface, you’ll need to further customize this code. This is due to Google’s handling of direct and organic traffic for sessions in the UI. Specifically, if a user comes from a direct source, the system will use the user’s traffic source.</p><p>This observation is based on my extensive testing and data analysis. By making this adjustment, the figures align more closely with the GA4 UI, clarifying why CPC mediums might appear much lower in GA4 BigQuery compared to the GA4 UI.</p><pre>SELECT <br>DISTINCT  <br>   ( case when  (select value.string_value from unnest(event_params) where key = &#39;medium&#39;) is null then traffic_source.source <br>  when  (select value.string_value from unnest(event_params) where key = &#39;medium&#39;) = &#39;organic&#39; and regexp_contains(traffic_source.medium, &#39;(cpc|paid|social|email|referral)&#39;) then traffic_source.source  <br>  else <br>     (select value.string_value from unnest(event_params) where key = &#39;source&#39;) end) as source,<br>  ( case when  (select value.string_value from unnest(event_params) where key = &#39;medium&#39;) is null then traffic_source.medium <br>  when  (select value.string_value from unnest(event_params) where key = &#39;medium&#39;) = &#39;organic&#39; and regexp_contains(traffic_source.medium, &#39;(cpc|paid|social|email|referral)&#39;) then traffic_source.medium  <br>  else <br>     (select value.string_value from unnest(event_params) where key = &#39;medium&#39;) end) as medium,<br>   count(distinct(concat(user_pseudo_id,&#39;.&#39;,(select cast(value.int_value as string) from unnest(event_params) where key = &#39;ga_session_id&#39;)))) as session_count<br><br><br>FROM `analytics_123456789.events_20230831` <br>WHERE event_name = &#39;page_view&#39;<br>AND (select value.int_value from unnest(event_params) where key = &#39;entrances&#39;) =1 <br> #and ( (select value.string_value from unnest(event_params) where key = &#39;medium&#39;) is not null<br> #and (select value.string_value from unnest(event_params) where key = &#39;medium&#39;)  &lt;&gt; &#39;organic&#39;)<br> GROUP BY 1,2<br> ORDER BY 3 DESC<br> ;</pre><p>An important point to mention is the joining of session_id with user_pseudo_id. I’ve done this because session_id isn’t unique on its own, so pairing it with user_pseudo_id addresses this issue. Even after this adjustment, you’ll observe some discrepancies, though they’re more aligned with the GA4 UI. These discrepancies arise from various reasons, including:</p><ul><li><strong>Googles data driven models: </strong><a href="https://support.google.com/google-ads/answer/6394265?hl=en-GB">https://support.google.com/google-ads/answer/6394265?hl=en-GB</a></li><li><strong>The use of approx. count in the UI:</strong> <a href="https://developers.google.com/analytics/blog/2022/hll#hll_implementation_in_google_analytics_4_properties">https://developers.google.com/analytics/blog/2022/hll#hll_implementation_in_google_analytics_4_properties</a></li><li><strong>The reporting ID you have set:</strong> <a href="https://support.google.com/analytics/answer/10976610?hl=en">https://support.google.com/analytics/answer/10976610?hl=en</a></li><li><strong>Timezone differences</strong>, by default the event_timestamp is in UTC on your BigQuery dataset.</li></ul><p>It’s essential to consider several factors. While modelling your data to align with the GA4 UI is advantageous for reporting, there’s value in understanding the genuine origins of your sessions without any modelling.</p><p>For instance, if a user session is from a direct source, it might indicate that users have bookmarked or favourited your website and are potentially more engaged. Always keep in mind the specific scope of your analysis and reporting, and choose the approach that delivers the data you need.</p><p>This summary is based on my research, and I welcome any insights or discoveries you might have regarding GA4 UI replication. My next post will be how to replicate the default channel grouping consistently on your GA4 session and traffic attribution.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=59b2caeff294" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery and GA4 removing expiry dates across your dataset]]></title>
            <link>https://lace-chantelle-rogers.medium.com/bigquery-and-ga4-removing-expiry-dates-across-your-dataset-a5d0820ee473?source=rss-a7b267f4c117------2</link>
            <guid isPermaLink="false">https://medium.com/p/a5d0820ee473</guid>
            <category><![CDATA[google-analytics]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[ga4]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Lace Chantelle Rogers]]></dc:creator>
            <pubDate>Mon, 21 Aug 2023 17:32:38 GMT</pubDate>
            <atom:updated>2023-08-21T17:32:38.401Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/198/0*B7ShHNQLNzbpU2Xq" /><figcaption><a href="https://support.google.com/analytics/answer/9823238#step1&amp;zippy=%2Cin-this-article">https://support.google.com/analytics/answer/9823238#step1&amp;zippy=%2Cin-this-article</a></figcaption></figure><p>As you’ll probably already be aware, Google Analytics 4 (GA4) provides an fantastic dataset for BigQuery for all your raw user and event level data. This data is super easy to connect to Google cloud platform (GCP). However if you connect your GA4 property to a sandbox mode, the tables will automatically have a 60 day expiry window.</p><p>Firstly, I always recommend setting up the GA4 to BigQuery connection, even if you cannot add a billing account. Ensure you plan to add a billing account in the first 60 days to benefit from pay as you go features.</p><p>You can track your billing easily via billing reports and often if you’re simply storing GA4 data for a small property you will be unlikely to spend more the $0.5 per day. There are a number of methods you can use to manage spend including alerts and quota limits set on your billing account.</p><p>However, something you may not realise when you do upgrade your sandbox account to a paid for version, <strong>the 60 day expiry date on you historical tables is not automatically removed</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/398/0*nLU05Q-6sJSJVV_i" /></figure><h3>So how do you resolve this?</h3><p>There are a number of ways to remove expiry, however I tend to like the SQL approach as I remain in the same interface, however if you want to read about these in more detail, Google helpfully provide this <a href="https://cloud.google.com/bigquery/docs/managing-tables#updating_a_tables_expiration_time">useful guide</a>.</p><h4>Step 1: dataset level expiry:</h4><p>Navigate to the console and click on the dataset in question and untoggle the enable table expiration</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/509/1*lmXn58Ts3kQ8NvsluBspMA.png" /></figure><h4>Step 2: Manual approach-removing the expiry on the tables already created</h4><p>You will now need to remove the expiry of the table, you can do this in the interface via the console by clicking edit details and then selecting none.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/521/1*I4ibvptpZG9Q0pANAZwQ5g.png" /></figure><h4>Step 2: Automated approach-removing the expiry on the tables already created</h4><p>If you have more then a couple of tables I strongly advise against doing this manually, as you may find you forget tables or you simply do not want to sit for hours doing this. In comes DDL to the rescue.</p><p>The code below will need editing by:</p><ul><li>Replacing the project name and dataset name in the <strong>concat</strong> statement</li><li>Replacing the project name and dataset name in the <strong>from</strong> statement</li></ul><pre>declare sql string;<br>set sql =<br>(with base as<br>(<br>select concat(&quot; ALTER TABLE  `projectname.analytics_12345678.&quot;, table_id, &quot;` SET   OPTIONS(expiration_timestamp=null)&quot;) as string<br>from<br> `projectname.analytics_12345678`.__TABLES__)<br> select STRING_AGG( string, &#39;;&#39; )<br> from base);</pre><p>Once you have run your code, navigate to the results and copy these into another query window, make sure you remove the quotation marks which will be at the beginning and end of your code.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/451/1*sGvujBZpc6iRAsLB30Yr8Q.png" /></figure><p>Now select run, et voila you now have removed all expiry dates without having to go through each table. Now to double check expiries have been removed</p><p>There are more ways of automating this process further using tools such as python and other dynamic code, however in this case I prefer the approach of manually running the final code to ensure you have a clear log of each action run.</p><p>I will be writing a further blog on how to save deleted tables and datasets in the near future in case you have gone beyond the 60 days window or accidentally delete datasets. But in the meantime — have fun with your SQL :)</p><p>It’s also worth checking out my previous blog on timetravelling with BigQuery (it’s possible) <a href="https://medium.com/@lace-chantelle-rogers/how-to-time-travel-with-bigquery-and-undelete-tab-62be6159ab8b">https://medium.com/@lace-chantelle-rogers/how-to-time-travel-with-bigquery-and-undelete-tab-62be6159ab8b</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a5d0820ee473" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why does my Google Analytics 4 Conversion attribution differ from Universal analytics?]]></title>
            <link>https://lace-chantelle-rogers.medium.com/why-does-my-google-analytics-4-conversion-attribution-differ-from-universal-analytics-ab8a8e9ad63a?source=rss-a7b267f4c117------2</link>
            <guid isPermaLink="false">https://medium.com/p/ab8a8e9ad63a</guid>
            <category><![CDATA[google-ads]]></category>
            <category><![CDATA[google-analytics]]></category>
            <category><![CDATA[ga4]]></category>
            <dc:creator><![CDATA[Lace Chantelle Rogers]]></dc:creator>
            <pubDate>Fri, 07 Jul 2023 13:44:40 GMT</pubDate>
            <atom:updated>2023-07-07T13:44:40.525Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Qb_zndMDOQcRhRCA.png" /></figure><h3>1. Why are my conversions attributed differently?</h3><p>Firstly we are looking at two very different tools, UA was set to a specific logic whereas GA4 now takes advantage of advanced AI and machine learning, coupled with a customisable attribution model for conversions.</p><h4>How does universal analytics model attribution?</h4><p>UA allocates create to the last click source and medium. This means whatever the last source of the click the attribution will be credited to this. The default look back window is 6 months. If you want to review a different type of attribution model such as first touch in the model comparison tool. <a href="https://support.google.com/analytics/answer/1662518?hl=en">Link</a></p><h4>How does google analytics 4 model attribution?</h4><p>GA4 has multiple scopes of attribution, this means there will be differences between your UA and GA4 conversion attribution. The default look back window is 90 days. <a href="https://support.google.com/analytics/answer/11080067?hl=en#zippy=%2Cin-this-article">Link</a></p><p><em>· User</em></p><p><em>· Session</em></p><p><em>· Event</em></p><h4>Customisations in GA4</h4><p><strong>Within GA4, you also have data driven attribution, which you can adjust 4 aspect of the model </strong><a href="https://support.google.com/analytics/answer/10597962?hl=en-GB&amp;utm_id=ad#zippy=%2Cin-this-article"><strong>link</strong></a></p><p>· The model used (default is data driven)</p><p>· Channels eligible to receive credit with web conversions shared with Google ads</p><p>· Conversion window for acquisition conversion events</p><p>· Conversion window for all other conversion events</p><h3>What is recommended?</h3><p>I highly recommend using the data driven model, whilst the results are harder to explain, the outcome is that you will see a truer reflection of your marketing efforts and how this impacts conversions. For example if you do paid search and then the next day a user searches for your website as an organic user, it is likely the paid search activity drove that organic search.</p><p>By using the data driven model, you are able to accurately view the performance of mix marketing models and then benefit by using these more accurate converting users in your marketing efforts. <a href="https://support.google.com/google-ads/answer/6394265?hl=en-GB">Detailed explanation of data driven attribution</a></p><h4>What are the models <a href="https://support.google.com/analytics/answer/10596866?hl=en#zippy=%2Cin-this-article">Link</a></h4><ul><li><strong>Last click: </strong>This is the historical default that gives all credit to the final click prior to the conversion.</li><li><strong>Time decay: </strong>Gives credit based on the time between interactions.</li><li><strong>Linear:</strong> Gives equal credit across each step of the conversion path.(soon to be removed)</li><li><strong>Position-based: </strong>Gives credit to specific steps in the conversion path (typically first and last).</li><li><strong>First click</strong>: All credit to the first interaction (soon to be removed)</li><li><strong>Data-driven</strong>: Uses historical data to determine the attribution credit. The model will shift depending on the unique path. This is default and uses intelligent AI and machine learning to attribute the data.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/48/0*BccANGSv2gTt6jjO" /></figure><h4>Impact of the data driven model</h4><p>This feature impacts all conversion and revenue data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*bD0lDwHl8EMzOS551H4J5g.png" /></figure><p><strong>There are two main parts of the data driven methodology:</strong></p><p>· Analyzing the available path data to develop conversion rate models for each of your conversion events</p><p>· Using the conversion rate model predictions as input to an algorithm that attributes conversion credit to ad interactions</p><p>Which Algorithmically assign fractional conversion credit to marketing touchpoints. Which in layman’s terms means it uses lots of touch points to show the impact of the channels on likelihood to convert. For this reason, you may see increases in Paid Search conversions over UA.</p><p>An example would be where a user entered you website yesterday via paid search, but today via social, in UA you will see Social as the channel of attribution but on GA4 with a data driven model you may see paid search or the conversion distributed across paid search and organic channels.</p><h4>Example</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/718/1*oOR5zT8Jd1XjqLFVBZBIsw.png" /></figure><h4>Channels that receive credit</h4><p>You are able to set which channels get credit for conversion, it’s always worth having a peak at the handy infographics on the console to see how the model will then work as shown in these images.</p><p>This feature impacts all conversions shared with Google Ads.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*LW480bml905_qL2hD4n8gQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*3Uic570Qs2Hgxlv_3YmiTQ.png" /></figure><p><strong>What this means:</strong></p><p>· Google paid channels: Only Google Ads paid channels are eligible to receive conversion credit.</p><p>· Paid and organic channels: Paid and organic channels are eligible to receive conversion credit, but only credit assigned to Google Ads channels will appear in your Google Ads accounts.</p><p>In layman’s terms, this means if you select paid and organic channels, your conversion will include all channels in the user interface, however you will only see the Google Ads credit in your Google ads account.</p><h4>Conversion window</h4><p>This impacts all data in your GA4 console</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*2mj_taLk9z7MhcCHYT-x5Q.png" /></figure><p>The default lookback window in GA4 is 90 days, while in Universal Analytics, it was six months by default. This is to reflect the changing landscape of cookie policies and retention. Additionally this feature is impacted by Google Signals which provides better attribution but does require review from your GDPR expert before being implemented.</p><p>Currently I strongly suggest retaining the recommended defaults on this feature, and if you do choose to change it, please note it does not apply retrospectively.</p><h4>If you want to reflect UA (not recommended)</h4><p>· Change the model to last touch</p><p>· Change the channel credit to paid and organic channels</p><h4>More possible reasons for variances in conversion</h4><p>Google are compiling useful material to help migration of reporting to GA4. This link provides incredibly useful material to explain all manner of variations in numbers. <a href="https://support.google.com/analytics/answer/13672281?hl=en#:~:text=Conversion%20counting%3A%20UA%20counts%20one,GA4%20will%20usually%20show%205.">Link</a></p><h4>What about BigQuery?</h4><p>There are several ways of reflecting different types of attribution models using your GA4 data, however you should ensure before attempting to do so that you fully understand how the different attribution types are assigned in the BigQuery data. If you would like to somewhat reflect the GA4 Data driven attribution model, Markov Chains and Shapley are likely to be the most reflective of these models. Currently I would recommend performing this in Python.</p><p>I’ll be writing a blog very soon on how to accurately attribute data and default channel groupings in BigQuery from the GA4 BigQuery dataset.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ab8a8e9ad63a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GA4: Why are my Looker Studio dashboards broken?]]></title>
            <link>https://lace-chantelle-rogers.medium.com/ga4-why-are-my-looker-studio-dashboards-broken-80679b764c7f?source=rss-a7b267f4c117------2</link>
            <guid isPermaLink="false">https://medium.com/p/80679b764c7f</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-data-studio]]></category>
            <category><![CDATA[ga4]]></category>
            <category><![CDATA[looker-studio]]></category>
            <category><![CDATA[google-analytics]]></category>
            <dc:creator><![CDATA[Lace Chantelle Rogers]]></dc:creator>
            <pubDate>Sun, 27 Nov 2022 14:52:47 GMT</pubDate>
            <atom:updated>2022-11-27T19:08:11.764Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Google recently announced it would be enforcing its GA4 API quota limits, so what does this mean in plain English?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/967/0*JoRS8P6cdhdZhZY-" /><figcaption>Have you seen this warning?</figcaption></figure><p>On November 10th, the limit for the quotas of the Looker Studio (and in fact any other connector) using GA4 API came into force. This means that if you have previously built dashboards with multiple widgets connecting to your GA4 API, you may now get an error message starting data set config error. This does not mean your report is broken, it likely means you have 10 or more concurrent API calls or used 1.25K of tokens in the given hour.</p><h4><strong>Concurrent requests</strong></h4><ul><li>You are allowed a maximum of 10 API calls concurrently ( calls=piece of data – eg a graph)</li><li>The number of calls being made at the same time to GA4 API, this includes if more than one person is refreshing the connector at a given time or if you have 10 or more calls in the page</li><li>An API call includes all widgets and for graphs will include 2 API calls for the legend and the data itself</li></ul><p><strong>Tokens</strong></p><ul><li>You are allowed 1250 token slots per hour, these reset every hour. There are also daily limits in place of 25K so you should avoid refreshing the data every hour too.</li><li>When data is refreshed you will consume tokens, these tokens are based on the complexity, amount of data, the date ranges and any filters applied to data.</li><li>This also includes blended data sources refreshing where you may also make more than one concurrent call and use up more tokens due to the complexity of joins</li><li>Tokens are consumed every time you change filters, refresh the data or change date ranges, this creates a huge challenge when designing reports using the API</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*WOolNv1mypH3-LyQ" /></figure><h4>How to resolve this issue?</h4><ul><li>Reduce and limit access to the report, ensuring users are viewers, with limited filter controls available. Automate PDF emails from Looker Studio if users do not need to filter or edit data</li><li>Remove legends if they are unlikely to change and write these as text boxes</li><li>Remove any unused or blended data sources or hidden pages with widgets to reduce token usage</li><li>Set your data source refresh to the maximum rate of 12 hours</li><li>Spread out your widget and graphs to ensure no page has more than 10 calls (not ideal I know)</li><li>Use the “Extract” data source, however, this will still be subject to limits and requires more management to refresh. It’s also subject to 100MB limits, which can quickly be exceeded. <a href="https://support.google.com/looker-studio/answer/9019969">https://support.google.com/looker-studio/answer/9019969</a></li><li>Use the BigQuery connector, which is my personal recommendation as it prevents sampling and gives you control of your data removing sampling, cardinality and thresholding issues. It also means you will be able to easily customise the data before it is ingested. This does however come with some upskilling.</li></ul><p>Whilst GA4–360 has more generous limits, due to the size of the organisation data and the depth of reporting, we still expect to see customers hitting GA4 quota limits. In addition, due to the limits of the extract data source of 100MB, I strongly recommend using BigQuery.</p><p>Even if you are using the GA4 connector at present for reporting, make sure you set up your GA4 to BigQuery connection, even if you do not plan to use the data for some time. For most users, you will find there is zero spend to store the data and if you do exceed the 10GB allowance per month, you shouldn’t expect the spend to exceed $20 per month for the stored data. You cannot backfill the GA4 BigQuery data but you can delete it if you eventually decide it’s not useful for your business.</p><p>At <a href="https://www.freshegg.co.uk/training/">Fresh Egg</a> we are constantly looking at ways to ensure clients are able to maintain and leverage their GA4 data during what is a challenging period for us web analysts and data scientists, so be sure to reach out to us if you have any questions or require support on your GA4 journey.</p><h4>Useful links</h4><ul><li><a href="https://developers.google.com/analytics/devguides/reporting/data/v1/quotas">API quota limits</a></li><li><a href="https://support.google.com/looker-studio/answer/12182485#ga4-quota&amp;zippy=%2Cin-this-article">Googles Advice</a></li><li><a href="https://support.google.com/looker-studio/answer/11521624?hl=en">Looker Studio Release notes</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=80679b764c7f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery and GA4: how to redact parameters in code]]></title>
            <link>https://lace-chantelle-rogers.medium.com/bigquery-and-ga4-how-to-redact-parameters-in-code-7af8a8d70487?source=rss-a7b267f4c117------2</link>
            <guid isPermaLink="false">https://medium.com/p/7af8a8d70487</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[ga4]]></category>
            <category><![CDATA[google-analytics-4]]></category>
            <category><![CDATA[google-analytics]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Lace Chantelle Rogers]]></dc:creator>
            <pubDate>Mon, 03 Oct 2022 19:48:23 GMT</pubDate>
            <atom:updated>2022-10-03T21:05:23.202Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/620/0*18T-b0AL66XoQVCF.png" /></figure><p>As more and more people migrate from Universal Analytics to Google Analytics 4, we see more opportunities for people to use BigQuery to build truly enhanced reporting and optimised analytics.</p><p>However, it is also now more challenging to redact PII via Google Tag Manager which creates more reliance on robust code methodically to encrypt and prevent your websites and apps from sending PII to Google Analytics. This is a <strong>really positive </strong>move in the fact, since if the correct methodology is employed then you can be confident PII is not processed by Google.</p><p>So what happens when PII is sent to GA4? Firstly, you can process data deletion requests via the Google Analytics 4 interface as described in this link <a href="https://support.google.com/analytics/answer/9940393?hl=en">https://support.google.com/analytics/answer/9940393?hl=en</a>.</p><p>But what happens to your BigQuery GA4 data when the worst happens and you detect PII? At present — nothing.</p><p>However, there are a number of choices, you could quite simply delete the tables with PII detected OR use a little BigQuery mastery to clean and redact the impacted data — allowing you to keep a clean and complete dataset.</p><h4>The Code</h4><p>This code will let you run each table update manually, with the condition you require to remove the PII. In this case, I have added two conditions, firstly for where the string value contains an @ but not where I validly have an email address and only for specific parameters where I have detected PII. The second is cleaning a Page string where I have defined conditions that are erroneously reporting PII. Instead of redacting this entire string, which would cause the loss of valuable UTM data, I have used safe split on the string to only return results after the UTM campaign.</p><p>If PII is present it will normally be in the string-based parameters, but since we are updating a nested value we still need to call the other parameter types to retain the nested array’s integrity. You’ll also notice there is mention of two arrays and this is because the param values are nested within the param keys, which are nested on in the events.</p><p>To update the code you must first edit the project name, I highly recommend making a copy of your impacted datasets to test against.</p><p>Add your case statement, run retrospective tests to ensure the right strings were redacted.</p><pre>UPDATE</pre><pre>`project_name.analytics_123456789.events_20221002`</pre><pre>SET</pre><pre>event_params = (<br>SELECT<br>ARRAY_AGG(<br>STRUCT(<br>key,<br>STRUCT( <br>(CASE WHEN value.string_value LIKE &#39;%@%&#39; AND value.string_value NOT LIKE &#39;%data1%&#39; AND key IN (&#39;element_text&#39;, &#39;clean_page_path&#39; ) THEN &#39;redact&#39;</pre><pre>WHEN value.string_value LIKE &#39;%name=%&#39;<br>AND value.string_value LIKE &#39;%issue%&#39; THEN CONCAT(&#39;utm_campaign=&#39;, SPLIT( value.string_value, &#39;utm_campaign=&#39;) [SAFE_OFFSET(1)])</pre><pre>ELSE value.string_value END) AS value.string_value,<br>value.int_value,<br>value.float_value,<br>value.double_value )))<br>FROM<br>UNNEST(event_params) )<br>WHERE TRUE ;</pre><h4>How to automate this?</h4><p>Let’s be realistic, this code is handy but what if you had 100s of tables? Well, the solution is automation using the metadata from your BigQuery project.</p><p>The code below takes away some of the pain of running this process across multiple tables. It effectively uses the above code as a string and then writes the code for you. You could additionally use the <strong>where</strong> statement to only run this against a specific group of tables.</p><p>You will need to add in your case statement as developed above, and then change the project name to reflect the project you are editing. Once you run this code you can either use execute immediate to run automatically or copy the code from the results and run it in a new window.</p><pre>DECLARE PAGEPATH_SQL STRING;</pre><pre>DECLARE APPINFO_SQL STRING;</pre><pre>DECLARE query STRING;</pre><pre>SET</pre><pre>PAGEPATH_SQL =  &quot; SET event_params     = (   SELECT    ARRAY_AGG(STRUCT(key,        STRUCT(          (case when value.string_value like &#39;%@%&#39; and value.string_value not like &#39;%data1%&#39; and key in (&#39;element_text&#39;,  &#39;clean_page_path&#39; ) then &#39;redact&#39;      when value.string_value like &#39;%name=%&#39; and value.string_value like &#39;%data1%&#39; then concat(&#39;utm_campaign=&#39;, split( value.string_value, &#39;utm_campaign=&#39;) [safe_offset(1)])  else value.string_value end) ,       value.int_value,           value.float_value,          value.double_value                    )))  FROM UNNEST(event_params) )  where true  ;&quot;;</pre><pre>WITH<br>base AS (<br>SELECT<br>table_schema,<br>(SPLIT(table_name, &#39;_&#39;) [    OFFSET      (1)] ) AS date_val,<br>table_name<br>FROM<br>`analytics_123456789`.INFORMATION_SCHEMA.TABLES</pre><pre>WHERE<br>table_name LIKE &#39;%events_2%&#39;)<br>(SELECT<br>STRING_AGG( CONCAT( &#39;UPDATE `projectid.&#39;, table_schema,&#39;.events_&#39;,date_val, &quot;`&quot;, PAGEPATH_SQL ), &#39;  &#39;) AS PAGE_PATH,<br>FROM<br>base<br>#WHERE    DATE_VAL BETWEEN   &#39;20220717&#39;  AND    &#39;20220718&#39;<br>);</pre><p>One note to make is it’s key that you ensure you run this code up to 12 days after your initial redaction on your app or website as there is some risk of some PII impacted data being cached on customer devices. You should also remember whilst this code cleans PII which meets your logic from your tables, it does not ensure that no one else in your team has used your GA4 tables in ETL processes or in external tools so make sure you review these too.</p><p>This approach can be used on any nested fields including those in the user properties on the GA4 dataset with slight editing or even be used when you have erroneously named parameter outputs to make long-term analysis easier.</p><p>Happy BigQuerying and be sure to send me suggestions for future GA4 BigQuery blog posts.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7af8a8d70487" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery: Easily manage dataset access permissions with DCL]]></title>
            <link>https://lace-chantelle-rogers.medium.com/bigquery-easily-manage-dataset-access-permissions-with-dcl-abb5878fdf55?source=rss-a7b267f4c117------2</link>
            <guid isPermaLink="false">https://medium.com/p/abb5878fdf55</guid>
            <category><![CDATA[google-analytics]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Lace Chantelle Rogers]]></dc:creator>
            <pubDate>Wed, 29 Jun 2022 07:26:37 GMT</pubDate>
            <atom:updated>2022-06-29T07:26:37.639Z</atom:updated>
            <content:encoded><![CDATA[<p>As we all know one of the biggest challenges of managing a data warehouse is permission to access data. Often this can be challenging to keep on top off, and even more so when you have specific dataset level permissions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SF_sK9QuQxZu3AAcdDGt5g.png" /><figcaption>Types of SQL</figcaption></figure><p>Luckily BigQuery has recently released <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/data-control-language">Data Control Language</a> functionality, which means you can quickly grant BigQuery dataset level access without manually using the UI or using tools such as Python.</p><p>This means you can have a BigQuery data warehouse which services multiple departments or different levels of permissions depending on the team. This is key when your warehouse may have valid PII data which you only want specific team members to have access to.</p><h3>General</h3><h4>Permissions</h4><p>Before you start ensure you have the permissions as mentioned in this<a href="https://cloud.google.com/bigquery/docs/dataset-access-controls#required_permissions"> link</a>. I also recommend ensuring you double-check you know what region your dataset is stored in since you will need to ensure you run the DCL in this region.</p><h4>The Syntax</h4><p>The syntax below allows you to either grant access to or revoke access from either at a dataset or table level. In testing, I have not seen an error occur if the user is granted or revoked access multiple times, however, an error will occur if the user does not exist on GCP.</p><pre><strong>GRANT/REVOKE</strong> `roles/bigquery.dataViewer` <br><strong>ON SCHEMA/ON TABLE</strong> `projectname`.dataset<br><strong>TO/FROM</strong> &quot;user:tom@example.com&quot;, &quot;user:sara@example.com&quot;, group:data@ilovedata.com ,<br>&quot;serviceAccount:genericservice@ilovedata.iam.gserviceaccount.com<br>; </pre><h4>Google Groups</h4><p>If you are part of a larger business where department heads or IT security managers onboard new teams, I highly recommend creating <a href="https://support.google.com/groups/answer/2464926?hl=en">google groups</a> per department, with the onus on these being managed by department heads. This way you will not have to manage the individual level access and the responsibility to remove and add new users. Likewise, you can design groups and control them as the warehouse administrator.</p><p>You’ll then be able to add these groups with the required permissions, which limits maintenance. This does come at risk if department managers do not effectively control groups so I suggest inforcing quarterly access reviews and data access agreements with the managers of these lists.</p><h4>Basic access</h4><p>Before getting started with DCL, consider whether the user will be accessing your warehouse via the project/billing account where the data is stored, or if they have departmental projects which have unique billing accounts or project names. The benefit of the second approach is managing billing to a department level but will come with some risks such as not having oversight of how the data is used and where.</p><p>Firstly add all users BigQuery Job User(roles/bigquery.jobUser) if your groups or users are going to be working on your project and billing account. This means they have access to BigQuery but not to specific datasets or tables.</p><h4>More advanced processes</h4><p>If you have a warehouse in which you have a standard naming schema for datasets, you can combine DCL with tools such as Infomation_schema, to write your DCL code for you.</p><p>If you have a structured schema for your datasets you can then automate adding or revoking users to multiple datasets. For example:<strong><br>Department_type_detail </strong>for example would be <strong>analytics_reporting_snapshots</strong></p><p>If you then run the following code, editing the string to reflect the accesses you’re granting or revoking, a string of code will be generated. Copy and paste this output into a new BigQuery tab and voila — you have written your DCL.</p><pre>WITH<br>BASE AS (<br>SELECT<br>CONCAT(&quot;GRANT  `roles/bigquery.admin` ON SCHEMA &quot;, schema_name, &quot; TO &#39;user:lace.rogers@dataclinic.co.uk&#39; ;&#39;&quot;) AS string_value,<br>FROM<br>region-europe-west2.INFORMATION_SCHEMA.SCHEMATA<br>WHERE<br>schema_name LIKE &#39;analytics_reporting_%&#39; )<br>SELECT<br>STRING_AGG(string_value, &quot;\n&quot;) AS OUT_VAL<br>FROM<br>base ;</pre><ul><li>hint: make sure you are processing the code in the same location as you are granting permissions.</li></ul><p>As always scripting and procedural language helps you work smarter for less time. And be sure to send me suggestions for other GCP tools you would like me to write about.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=abb5878fdf55" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery: A greener way to warehouse]]></title>
            <link>https://lace-chantelle-rogers.medium.com/bigquery-a-greener-way-to-warehouse-c47644307008?source=rss-a7b267f4c117------2</link>
            <guid isPermaLink="false">https://medium.com/p/c47644307008</guid>
            <dc:creator><![CDATA[Lace Chantelle Rogers]]></dc:creator>
            <pubDate>Sat, 11 Jun 2022 08:45:22 GMT</pubDate>
            <atom:updated>2022-06-11T08:45:22.221Z</atom:updated>
            <content:encoded><![CDATA[<p>Nowadays it’s incredibly easy to upload, store query and edit data, however, how often do we consider the carbon (and other) footprints in doing so. I attended an eye-opening conference hosted by Silicon Brighton which triggered how can I make my warehouses more sustainable.</p><p>I am not an expert in green policy however, I am able to contribute and support the journey toward greener warehouses, with engineering skillsets and so can you in a few simple steps.</p><p>Google Cloud Platform as always had already considered this and I will take you through a few steps which you can do without huge upheaval to start to tame your warehouse carbon footprint.</p><p>Google has recently released <a href="https://cloud.google.com/carbon-footprint">The new Carbon Footprint tool</a> which is associated with your billing account. Simply follow the link and if you have access to a billing account you can see gross calculations on your carbon footprint. This is in preview mode at the moment, so I’m sure they will be adding more features as time goes by. However it identifies the emissions from a service level, which helps you establish where to focus your initial efforts.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S6Zz5HiT0dJ515QHak29jg.png" /><figcaption>Carbon Footprint Scorecards</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/767/1*l1EZVQHo-hslOWqI3kbplA.png" /></figure><h3>Steps you can take today for a greener data warehouse</h3><p>Before getting started I highly recommend reading this <a href="https://cloud.google.com/sustainability">article</a> which shows some of the approaches GCP is taking to help move toward cloud sustainability.</p><p>When you get started on your data warehouse green plan, I highly recommend spending time mapping out your warehouse from source to reporting. This will enable you to understand what you’ll need or have in place already and help identify opportunities to create a greener warehouse. I personally use tools like Visio, but there are also amazing tools out there to help with this challenge including the good old whiteboard.</p><h4>Simple steps to greener</h4><h4>Storage Region</h4><p><strong>What region</strong> is your data to be stored in, and is it viable to store your datasets in a low-carbon data centre? GCP provides handy icons to show data centres which have low emissions and are increasing the sites all the time. This said remember to consider where your other data is stored and whether you have legal restrictions on where your data is stored or processed.</p><p>One important note is if you choose to migrate your current datasets to one of these locations, use the data transfer service tool provided by Google and this should be done across any datasets you may want to join in the future.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/670/1*TifKn7UB8ULDLXHMpoWnXQ.png" /></figure><h4>Do you need this data?</h4><p>It’s super easy nowadays to simply upload all data for all sources into your cloud-based warehouse, but have you considered if it will be used and if it is even required anymore?</p><p>If you need to retain your data but you’ll be very unlikely to use it, utilise GCP cloud storage functionality with either <a href="https://cloud.google.com/blog/products/storage-data-transfer/archive-storage-class-for-coldest-data-now-available">coldline or achive</a> storage. This also carries the benefit of being cheaper than BigQuery storage and will make your active warehouse easier to manage.</p><p>If it’s data which will need transforming, use cloud storage as a data lake and set up automated logic to move this into cold line storage once transformed and streamed into BigQuery.</p><p>Don’t be afraid to delete data (this sends a shiver down any data engineer&#39;s spine). If you know you’ll never need this data and it is not retained for legal reasons, consider deletion. This is even more true for transformed data where you may be storing the raw data in your cloud storage.</p><p>A question to always think about when storing, transforming or managing data is, just because you can, should you?</p><h4>Are you duplicating your data?</h4><p>How often do you end up with the same source in multiple lakes and warehouses across your company? Well, the good news is, GCP provides both OMNI and data transfer tools, which enable you to query data from other providers such as AWS, and pull data from lakes such as the S3 storage buckets on AWS. This means you do not need to duplicate data stored in other cloud providers, simply access them via the GCP native tools. You can also use tools such as Python, SAS and R to combine and process sources locally if doing one-off analyses.</p><h4>Auditing your warehouse</h4><p>How often do you check your data warehouse and lake for unused tables, scheduled queries and processes backend? Often this can feel like a huge minefield, and let’s be honest an uphill task if inheriting a warehouse or lake. But never fear, <a href="https://cloud.google.com/bigquery/docs/information-schema-tables">Information_schema</a> is here. This is a set of code, which enable you to effectively check when tables were updated last, how many rows and much much more. I personally create small tables which allow me to review what is in my warehouse and alert me when a table has not been updated in some time.</p><p>This coupled with <a href="https://lace-chantelle-rogers.medium.com/bigquery-using-gcp-audit-logs-to-estimate-user-level-spend-bd88bc075d24">audit log</a> management, where you can see table dependencies alongside who is using the data, allows you to handle opportunities for table deletion.</p><p>Effective controls, such as ensuring only certain people can create tables, helps make this task manageable and prevent your warehouses from scaling out of control.</p><p>Likewise, ensuring you have oversight of your company&#39;s projects, allows a cohesive approach to overall GCP data management.</p><h4>Switch it off</h4><p>If it’s not useful then turn it off, this seems simple, but how often are we afraid of turning off ETL processes? If you have compute engine running code which is now redundant, get to know what this is, likewise with scheduled querries and data studio tools which also process data. This comes with the added benefit of impressing your finance officer in the reduction of GCP spend.</p><h4>Keep it in GCP</h4><p>Where possible build your processes to be self-contained in GCP, this includes when using ETL tools and Cloud run. If you have the data in your BigQuery warehouse — transform the data in that location rather than extracting and transferring it to a third-party tool. Likewise frequently checking which native data transfer tools are available means you no longer need to use pipelines to get your play store or google ads data into BigQuery.</p><h4>Code effectively</h4><p>We all know coding effectively reduces cost, but have you ever considered effective coding actually reduces your carbon footprint? This is because when you run a code you effectively are using resources. So if you’re planning to build code, keep up to date with new BigQuery features, alongside using a sample to develop from rather than utilising the full table.</p><p>Using DDL and <a href="https://lace-chantelle-rogers.medium.com/bigquery-debugging-spotting-errors-in-your-data-before-running-code-89d3a3a24c39">Assert</a> means you will not process data if it is not available or if there is an error in the table.</p><p>When considering data-heavy processes like machine learning, consider and plan before you jump into the code and establish whether you can reduce how many variables or data sources will be utilised.</p><h4>Plan your tables</h4><p>Map out how your data will be used and design tables based on the output requirements. For example when you have a task of building a Google Analytics 4 Data Studio report from the BigQuery dataset, consider creating daily aggregated tables, which update each morning, rather than connecting to granular tables or custom SQL. Likewise, enable a daily PDF report for people who will be reviewing this daily, but won’t need to filter the data.</p><p>You could also align the update frequency in Data Studio to when the source table updates, meaning much fewer data processing jobs.</p><h4>Keep reading and learning</h4><p>Take this course to become a pro: Cloud boost-<a href="https://www.cloudskillsboost.google/focuses/32138?locale=zh_TW&amp;parent=catalog">A tour of Cloud sustainability</a>.</p><p>There are many, many ways of making your warehouse more sustainable, and by small simple steps, you can start to work towards a much more manageable journey to a greener warehouse. These are by no means an exhaustive list so be sure to follow Google&#39;s regular updates on their green policy and tools and if you can attend their first <a href="https://cloudonair.withgoogle.com/events/summit-sustainability-2022">green sustainability conference</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c47644307008" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>