I have hard time wrapping my head around and finding a solution on how to design a working database structure for this situation:
We have a system that should collect data from Google Analytics & Search Console API - The connection works fine.
What I am stuck with is figuring out how I should structure the database to store data from those APIs, to avoid reaching the quota limits, and to speed up the system itself.
The question that I ask myself whenever I get an idea of possible structures is "How do I tell that the data is invalid now, be it because do not need some data anymore, or if a filter has been changed" - this is because the system is not yet in a fixed state, and people have ideas :/
The current idea is that we have different "sets" that we want from Analytics, they might contain the same data, but for different reasons, so something like:
'paidConversions' => [ 'metrics' => ['ga:goalCompletionsAll','ga:adCost', 'ga:transactions'], 'dimensions' => ['ga:date'], 'sort' => ['ga:date'], 'filters' => ['ga:sourceMedium=@google / cpc'] ], trafficDetailsUser' => [ 'metrics' => ['ga:sessions'], 'dimensions' => ['ga:date', 'ga:userGender', 'ga:userAgeBracket'], 'sort' => ['-ga:date'], 'filters' => , ], 'landingPages' => [ 'metrics' => ['ga:sessions'], 'dimensions' => ['ga:date', 'ga:landingPagePath'], 'sort' => ['-ga:date'], 'filters' => ['ga:sessions>30'], ],
What I am thinking is something like this: Landing Pages: -id -date -analytics_account_id -sessions -landing_page_path -id_hash (I would stringify the set array, and hash it to create this, then I could use that to identify if the content is out of date)
Traffic Details User: -id -date -analytics_account_id -user_gender -user_age_bracket -sessions -id_hash (I would stringify the set array, and hash it to create this, then I could use that to identify if the content is out of date)
But something else bugs me about this, that I cannot figure out :(
I've also considered of just storing the raw result of the API call, that then is instead loaded whenever the call is made again, but I'm not sure what is the best way to store that either...
What we have is (simplified version):
Client: -id -name -analytics_account_id (foreign key to "Analytics Account" table) -search_console_account_id (foreign key to "Search Console Account" table) Analytics Account: - id - analytics_id (used for the API) Search Console Account: - id - search_console_id (used for the API)