Explanation of Problem: I have built an e-commerce inventory accounting application using Firestore as my database. I use Firestore to save order/fee/shipment/inventory data. However, I don’t offer full support for advertising data since Firestore is not set up well to handle large amounts of data that I wish to save and more importantly, it is extremely cost inefficient to read and process this advertising data since it requires analysis for large subsets of a user’s data and flexible queries.
What I’m looking for:
- A different database base solution within GCP that can power our advertising analytics features.
- I are trying to optimize for low latency, high-throughput.
- I want to optimize for low cost, Firestore is too slow, reads too expensive, and not flexible enough
- I want to be able to have applications that can quickly read from both Firestore and the other solution to combine data from both data sets.
Info about the data:
- The data breaks is hierarchical but can use a table approach. I.e. Portfolio have campaigns which have ad groups which have product ads which have keywords. Each component can be linked via unique IDs which are returned from the API.
- We’re talking about tables for each user with several hundred thousands to low digit millions of rows. I.e. there might be 2 million rows of Date * unique keyword entries where the metrics for that keyword on a specific day is stored.
Use case of the data:
- Our intention is to store advertising data in a database and allow the user to access various functions in the application that perform analysis on the advertising data or queries and filters data and outputs the data in Google Sheets.
- Ex Query 1: I want to query datasets for each user and create insights from the data using multiple filters (i.e. fetch campaigns that meet a,b,c,d,e,f,g based on h,i,j,k,l,m columns, over x,y,z date range). In Firestore, these queries are very limited.
- Ex Query 2: Show the user a dashboard with their conversion rate this week over last for different ad segments. I.e. conversion rate of video ads vs product ads or conversion rate of campaigns with products 1-n vs campaigns with products 1-m
- I intend on making this data accessible via our Node.js application but also via Datastudio and Google Sheets. Our intention is to create custom google sheets functions that are able to query the database to allow the user or advertising agency to use the data inside Google Sheets.
- For instance I may want to query the top 100 campaigns from X date range
- Or fetch the top 1000 keywords that meet some criteria
Possible Solutions:
- Google Datastore
- Google BigQuery
- Google Bigtable - Best option I think but I’m not sure.
- Google Cloud SQL