Google BigQuery Best Practices РHow to Optimize the Cost? 

Querying BigQuery Involves cost.  We are going to discuss the best practices to optimize the cost.

Google BigQuery Best Practices

  • Best Practice 1 –  Select Command:  Don't use Select * command to query data. It involves huge cost. Instead, Query Specific fields to optimize the cost
     
  • Best Practice 2 – BigQuery Scheme: Check the BigQuery Scheme before creating and submitting the query. Because if the field that is requested is wrong, BigQuery will query and fetch no results, but that query involves cost
     
  • Best Practice 3 – Query Time Window: Query the data for shorter time window (Example 1-day data query) then expand the time window to optimize the cost
     
  • Best Practice 4 –  BigQuery Result Row Limit: Query Result Max Row Limit 16000. If the query result is more than 16000, no use  of querying. Then Switch to DataStudio and query the data
     
  • Best Practice 5 Big query is no sql datbase. So,understand the query attritubes and frame the query accordingly
     
  • Best Practice 6 – Query the data once and build the data repository (Data lake) in Local environment. This will reduce query duplication and cost
     
  • Best Practice 7 – While pulling the hourly data, ensure the hours is in GMT or in Local time (By default, it is GMT). Don't  forget to convert from GMT to Local time
     
  • Best Practice 8 – Dataset Optimization: Accommodate multiple platform (Native, Hybrid, PWA, WEB) data of a domain in one dataset. So that it is easy to query the multi-platform in one shot


Image Courtesy: wpengine.netdna-ssl.com


 

Firebase Analytics Best Practices  РHow to Optimize COST?

Firebase Analytics is a 360 digital analytics tool that tracks APP, WAP, WEB data. It is a subscription based tool that consumes cost based on the usage. 

Let discuss how to optimize Firebase analytics cost

1. Project Structure – Properly structuring  your project helps to reduce your subscription cost. 
Scenario – A digital property XYZ has WEB, PWA & APP  (Native, Hybrid) Platform.  Want to track it via Firebase analytics

Bad Practice Creating Each Project for Each Platform

Disadvantage
– You are wasting the project 
– You need to query each property separately

Best Practice Creating Single Project for all the platform
– You optimize the project
– Single query fetch all the project data
– Helps the track the user journey across Platform


2. Event Optimization
Scenario: XYZ has 3 screen form to register that needs to be tracked. 

Bad Practice – Building events for each touch points. Example Event Name Reg 1, Reg 2, Reg 3 

Disadvantage
– You are wasting the Event Quota (Max Event Allowed in Firebase is 500)
Best Practice – Build 1 Event and frame attributes for each touch points (Event Name – Reg Attribute 1 – Reg1,  Reg Attribute 2 – Reg2,  Reg Attribute 3 – Reg3). You are build many attributes and build awesome data structure. 

Best Practice 
– Build event scope for each event. Please remember, Firebase analytics push data to Google BigQuery which is a no SQL database. Structure the event in such way & put maximum attributes from each event
– Build 1 Event and frame attributes for each touch points (Event Name – Reg Attribute 1 – Reg1,  Reg Attribute 2 – Reg2,  Reg Attribute 3 – Reg3). You are build many attributes and build awesome data structure

Advantage
– You use 1 Event instead of 3 Events
– You optimize the Query cost 


3. Custom Definitions

Scenario: Segment data based on member type 

Bad Practice – Built Segment metrics in Events
Disadvantage
– You can't apply segment on events, if the segment itself is an event
– won't give reporting flexibility
– Increase query cost 

Best Practice – Built segments using Custom Definitions with proper scope
Advantage
– Provide report flexibility and scalability
– Optimize query cost

Image Courtesy: cloudfront.net