If you're having issues loading data in your reporting platform, it may be due to the dashboard "re-pulling" data every time a change or filter is applied. Google Data Studio in particular pulls data directly from the database for every change and does not store or "cache" the data in its application and relies on the database only. This is not efficient for large data sets such as those seen in the keywordtargetingmetric and productadmetric tables. In order to improve the load times, you can use a few of the strategies below:
1) A Data Extract in Google Data Studio essentially "caches" a data set in GDS, preventing the "re-pulling" described above. Data Extracts in Google Data Studio can be used for larger data sets like the keywordtargetingmetric and productadmetric data tables (or any data set that is loading slowly). Instructions for setting up an extracted data source found here: https://support.google.com/datastudio/answer/9019969?hl=en6
2) We've also found it is a best practice to create a data source for each merchant's respective report filtered to that merchant's data via the custom query capabilities of Google Data Studio. It is possible to filter an extracted data source via an applied filter on the extract, or, if you aren't yet using extracted data sources, you can use the following custom query convention to limit data to a specific seller:
select * from the metrictable
where metrictable.SellerID = X
In practice, you can use any metric table in your db. Below is an example for the keywordtargetingmetric table.
select * from keywordtargetingmetric
where keywordtargetingmetric.SellerID = 21
To find the seller ID's, you can create a data source from the "seller" table in your db and create a table with ID, Name, and MarketplaceName to see all available sellers + IDs in your db.
3) Also, it may help to filter the data to a limited date range. An example query can be seen in the data dictionary per the below:7
(k.SellerID = '1' OR k.SellerID = '2'
OR k.SellerID = '3')
AND k.DateTime >= DATE_FORMAT(CURDATE(), '%Y-%m-%d') - INTERVAL 90 DAY
Note that you can change the SellerID references to include only those sellers for which you want to pull data into a reporting dashboard. This line limits the data results to specified merchants to avoid loading data for
all merchants in your db and crashing the reporting platform. Limiting the merchants referenced in the query greatly improves load times of the data and is considered a best practice.
Also, the DateTime line for the query limits the data to pull in for the last 90 days. You can adjust this window as needed.