Ever wanted to baseline a project or snapshot a sales pipeline?

Tips & Tricks
Apr 4, 2006
7 Min Read

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...QuickBase 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 another 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. Setup 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.  For help in creating relationships, see the Creating a Relationship on-line help topic.
  3. Setup "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.  For help in creating lookup fields, see the Adding a Field to a Relationship on-line 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.  For help in what snapshot fields are or how to set them up, checkout our Setting Up a Snapshot Field on-line help topic.
  5. Setup 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 see how to create an import, checkout our Importing Data from other QuickBase Applications on-line help topic. Each time you then run this import, 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 setup 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.  For help in creating formulas, see our Using Formulas in QuickBase on-line help topic.
  • You can build crosstab views 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.  For help in creating crosstab views, see the About  Summary and Crosstab Views on-line help topic.
  • You can add "embedded views" in the master table's forms that will
    show you all the records where you have snapshotted the related details

The one problem with this approach is that you need to remember to manually fire off the import on a regular basis.  For example, we use this approach to track our sales pipeline and we run the imports on a weekly basis.  By doing this, we can see how each prospect's probability changes from week to week.  If many people start to use the import functionality in this way, we'll definitely look into automating this kind of import.

I hope this triggers some great ideas for everyone.  Like most other things in QuickBase, you are only limited by your imagination on how to apply different pieces of functionality and this one leverages all sorts of different features...Enjoy...

Recomended Posts