We set up some SQL queries to track metrics like kids attendance, but had old data in Excel that needed to be backfilled in. The Lava metric Source Type allows metrics to entered using commas to separate the columns and newlines to enter multiple metric values—sounded like a CSV file to me!

Here's the approach we took to get this data into Rock:

1. Shape the data and export to CSV

Get all the data for a particular metric into an Excel worksheet with the following columns:

  • YValue
  • MetricValueDateTime
  • Partition1EntityId
  • Partition2EntityId
  • ...
  • PartitionNEntityId

For example, our youth attendance metric, which is partitioned by Campus, Schedule, and Group Type, looked like this:

Screen Shot 2019-07-22 at 10.32.41 AM.png


2. Temporarily reconfigure the metric

If you've already configured your metric SQL query or Lava statement, copy and paste that code to another location while you work on backfilling your data. Change the Source Type for the metric to Lava, open your CSV file in a text editor, and copy and paste the data into the Source Lava box. Start with a small representative sample of the data to confirm the process is working before attempting to backfill everything:

Screen Shot 2019-07-22 at 11.12.49 AM.png

Set the metric Schedule to run in the next few minutes. After your scheduled run time passes, you'll either need to wait until the Calculate Metrics job fires again, or you can run it yourself. Once you confirm the process works as expected, you can copy and paste the rest of your data into the Source Lava box and rerun the metric calculation.