How do I baseline a project or snapshot a sales pipeline?
[The Quick Base Knowledge Base is your library of frequently-asked questions that help you better customize your apps to solve your business problems.]
Does your data change over time and would you like to track how that data changes? For example:
* Do you track tasks and do those task end dates and status change? Would you like to "baseline" the dates associated with these tasks?
* Do you have a sales pipeline where the opportunity size or status changes? Would you like to be able to look at each of sales rep's entire pipelines over time?
If either of these situations apply to you, we have good news...Quick Base can track these changes and you can build all sorts of reports that show how the information has changed over time and how the current values compare to previous values (e.g. has the opportunity size increased or decreased over time).
1) Create a new table that will track the changes. You won't need any fields in this table to start as we'll be adding them in later steps. This additional table will be where you track the "snapshots" of the task end dates or opportunity size information. For the purposes of explaining this, I'll refer to this new table as the "baseline" or "pipeline snapshot" table(s).
2) Set up a relationship between the pre-existing task / opportunity tables where these pre-existing tables are the "masters" and the new "baseline" or "pipeline snapshot" tables are the "details". When you are asked about what field to use as the Reference field in the details table, accept the default that will create a new field for you. (To learn more, please see our Create a Relationship help topic).
3) Set up Lookup fields in the details tables for any fields you want to track changes on. In this example, you'll want Lookup fields for the task end dates and opportunity size fields. (To learn more, please see our Create a Lookup Field help topic).
4) Create Snapshot fields that will "snapshot" the Lookup fields you want to track changes in. Because of the way Snapshot fields work, this will make it so that you will capture the lookup fields you want to snapshot at the point when the new detail records are created. (To learn more, please see our Set Up a Snapshot Field help topic).
5) Set up an import that will take all outstanding tasks or sales opportunities and will import them into the details tables. When mapping the fields, you will just need to pull the "key fields" (generally the record ID# field) of the open tasks or opportunities tables and will need to import them into the reference fields in the details tables. (To learn more, please see our Import Data from Other Quick Base Applications help topic). Each time you run this import later, your task end dates and your opportunity sizes will be captured in the Snapshot fields.
Once you have the above infrastructure in place, you can do many different things to help you manage your team and to get valuable insights out of this additional data. Here are some additional, optional things you can do to further improve your application:
* You can set up formulas that will compare the snapshotted value to the current value. This will help because if anyone changes the values in the master tables these formulas will show you the difference. (To learn more, please see our Using Formulas in Quick Base help topic)
* You can build crosstab reports in the details tables (the "baseline" and "pipeline snapshot" tables) that will show you how the numeric values have changed over time. To do this, you'll want to place the "date created" field as the column field. (To learn more, please see our Create a Summary or Crosstab Report help topic).
* You can add "embedded reports" to the master table's forms that will show you all the records where you have snapshotted the related details records. (To learn more, please see our Embed a Report in a Form help topic)
The one challenge with this approach is that you need to remember to manually fire off the import on a regular basis.