10 Backfill Metrics with Older Data Shared by Stephan Ichiriu, Faith Church one year ago 7.0 Administration / Finance Intermediate 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 CSVGet all the data for a particular metric into an Excel worksheet with the following columns:YValueMetricValueDateTimePartition1EntityIdPartition2EntityId...PartitionNEntityIdFor example, our youth attendance metric, which is partitioned by Campus, Schedule, and Group Type, looked like this:2. Temporarily reconfigure the metricIf 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: 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.
Jesse McColm Reply one year ago This has been super helpful for me when creating aggregate metrics and inputting old data. However, today, I ran across a "gotcha". If entering data for a new partition where data already exists for the dates and a different partition, it will overwrite all of the previous data on those dates. Make sure to include your previous data as well as the new data when you do it!
Miles Carmany Reply one year ago This is awesome! Really good way to help onboard ministries that have siloed data.