Most of the time you can pull in data directly from the data source through a custom report or a dataset. However, sometimes the amount of data might be too large to quickly create or update metrics without running into errors. To help alleviate this issue when you are working with large reports, you can create a stored report.
There’s no one standard time that you will need to use stored reports. A lot of it will depend on the data source and your business, but here are some common times you will want to create a stored report:
- If you often run into API rate limits with a specific data source.
- When your report contains a lot of data.
- If you will be building a lot of metrics off of one report.
- When you are using an ecommerce data source. (Especially for an “orders” report when you have hundreds of orders a day.)
There are two types of stored reports: Time-based and rip-and-replace. In time-based reports each row of the report is based on a specific time occurrence, like an online order or the number of views and impressions per day. Rip-and-replace reports don’t have a date attribute, so Grow will recreate the entire report each time it updates.
How to Create a Stored Report
- Select your data source and fill out the report parameters.
Don’t worry about choosing the full date range that you want to store—you can do that later. For now you can select a few days’ worth of data.
- Expand the Advanced Settings options on the right and click the “Get data sample” link under Stored Report.
- If you see the correct columns and settings that you want to store, click “Create Stored Report” at the bottom of the advanced settings on the right.
- You will now see a pop-up window in Grow with some additional settings based on the type of report you are saving.
- Give the stored report a descriptive name to help you know what kind of report and date range you are saving.
- Confirm that the columns have the correct data type listed.
- If your report is time-based, then you will need to select one or more columns as a primary key. Grow will look at the data based on that primary key and add any rows that doesn’t match that key.
If the report is rip-and-replace, then you won’t see the option to select a primary key.
Here are some best practices for selecting a primary key:
- Try to pick the one thing that separates each row from the rest and makes it unique.
- It’s best to pick some kind of unique ID number for each row (like an order number for sales).
- It’s not always a good idea to only use the date since there’s a chance two rows could have the same date and time.
- You can select multiple columns as a primary key, so perhaps a combination of 2-3 columns will make each one unique.
- Select the refresh interval. This is how often the stored report will refresh the data. While it’s tempting to just opt for the shorted time span, remember that the reason you are creating a stored report is likely because of the amount of data you are bringing in, so a short refresh interval might still hit those issues.
- Select the initial population range. This determines how much data you want to start with for your stored report. For example, if you select one year, then the stored report will start with data going one year back, and then add to that going forward.
- Select the number of days to fetch at a time. This mainly affects how Grow initially builds the report. If you have a lot of data each day, then you want to select a smaller group of days. Basically, if your report data has one row for each day, then selecting “29 days” will start building your report 29 rows at a time. But if you are storing an orders report with an average of 100 orders/day, then selecting “29 days” would bring in 2900 rows at a time.
- After you click “Create Stored Report” it takes you to the metric builder using the new stored report. It will be using the preview data you used earlier as Grow starts to populate the stored report in the background. Depending on how much data it is and how much you’re pulling at a time, it could even take days to fully populate.
- You can start to build a metric right then, or you can exit the metric builder and wait while the stored report populates. You can tell when the report is done by going to your account settings > Grow Storage. Find your new report in the list and if it shows a date when it was last updated, it is ready. If it says “Invalid Date” then it is still populating the report. (The date is invalid since it hasn’t updated yet.)
Stored Report Settings
You can see all of the stored reports in your account by going to Settings > Grow Storage. There are five columns with details about the stored report:
- Name of the stored report
- The original data source
- The user that created the stored report
- When the data in the report was last updated
- How many metrics are using that stored report
- The actions menu, where you can preview the data, go to the edit settings screen, or start building a new metric with this stored report.
Edit the Stored Report
If you click on “Edit” in the action menu, you will see the edit settings screen. In the top section you can rename the stored report, preview the data, and start building a new metric.
In the “Settings” section you can change some ways the data is being stored:
- The update interval is how often Grow will refresh the data in the report. If you edit this, be sure to hit the blue “Save” button at the top right.
- The “Update Now” link will set the report to refresh in the next minute, and it will reset the stored update interval to start again at that point.
- The “Rebuild Stored Report” button will reset the entire stored report. You usually do not want to do this. Depending on the report type, the confirmation screen will look different:
- Time-based reports will ask for the initial population (how far back the initial report should go).
- Rip-and-replace reports will just ask for a confirmation.
Delete the Stored Report
You can also delete a stored report at the top right of the edit screen. It will ask for a confirmation before deleting the report.
Unlike deleting a data source, the metrics that use a stored report will not be deleted. If you want a metric to use a different data source or stored report, update that metric to point to the new data source before deleting a stored report.
Tips and FAQ
To use a stored report in a metric, select “Stored Reports” in the data sources list, or click on “Build new metric” in the stored report settings.
You cannot create a stored report after adding any transforms to the data. You can only create a stored report from the data direct from the data source. The stored report options will be grayed out if there are any transforms added.
If you want to replace a stored report being used in a metric with a different one, you can select the new stored report in the data settings of the metric.
If you add or remove columns in the original data/report (this is especially common with databases) it will break the stored report. Just create a new stored report and switch out the old one.
If you want to create a stored report from a database (e.g., PostgreSQL, MySQL), then it would be better to refine your query rather than create a stored report. When creating a stored report from a database it will only create it as a "rip and replace" report which would still take a long time to refresh each time if there is a lot of data involved.