Match up and aggregate revenue and cost data

[Dean_Wiers-Wind]

I own a music lessons studio and the software we use (WellnessLiving) reports revenue per lesson and instructor pay per lesson in two separate reports. I’m hoping to find a way to match up a lesson record that contains the revenue for that lesson with a separate lesson record that contains the instructor pay in order to see my gross profit. In essence I have two sets of the same data (e.g. two entries for a 30 min. guitar lesson from March 28 at 2:30pm) that both contain different data that I want to combine. I could do this manually using the Dedupe app but I figured there has to be a way to automate this.

[Rose_K]

Hello and welcome @Dean_Wiers-Windemull!

I’d love to gather a little more information about your table setup and how you are importing the information into that setup. From your description it sounds like two tables would be helpful in your situation: Instructors and Lessons (which contain a lesson date)? This would allow you to use a Rollup field to display the profit for an instructor for all their lessons.

I also wanted to gather a little more information about the exported reports from WellnessLiving—are they CSVs? You could use the CSV import app to import the information and ensure duplicates are taken care of up front.

Let me know if I can help provide any more information, and if this helped answer your question please mark it as the solution :white_check_mark: .

[Dean_Wiers-Wind]

Thanks much for getting involved, here Rose!

I do already have separate tables for Lesson Data and Staff. See attached screen shots.

I can export the reports from WellnessLiving as CSV - I didn’t know about the CSV import and will explore that.

[Dean_Wiers-Wind]

You’ll notice from the screen shot of the Staff table that I have “Hourly Pay In-Store” as a currency field right now. What I need to do is to be able to reflect the fact I am increasing the pay for an instructor as of this date. All former lessons for that instructor need to reflect their former pay rate while new lessons need to reflect the new rate.

[Rose_K]

@Dean_Wiers-Windemull your table set up is awesome :sparkles: ! Thank you so much for sharing the screenshots of it. Let me know if you have any question about the CSV import app, and I hope it helps with the deduping process.

Dean_Wiers-Windemull:

What I need to do is to be able to reflect the fact I am increasing the pay for an instructor as of this date. All former lessons for that instructor need to reflect their former pay rate while new lessons need to reflect the new rate.

One possible workflow would be to add fields on your Instructor table to keep track of:

1. the current pay for an instructor
2. the historical pay (what they made before their pay increased)
3. the date of the last pay raise.

Here is an example I created to demonstrate this workflow—I’m using a base tracking employees who work as tour guides and the tours they lead. So my employee Betty got a raise on 3/16/21. I keep track of that date in one field, and I use two additional fields to keep track of her current pay, and her historical pay:

In the Tours table(which would be Lessons in your scenario) I created three lookup fields to pull the information for each employee’s current rate, past rate, and the date they got a raise:

Now with that information in place, I created a formula field that will check if the current tour date happened before or after that employee’s raise.

If the tour occurred before the raise then the formula will use the historical apay If the tour occurred after the raise date then the formula will use the current employee’s rate.

(I hid a few fields in the above screenshots for clarity)

Let me know if you have any questions on the above! If this helped answer your question please mark it as the solution :white_check_mark: .

[Dean_Wiers-Wind]

Hey Rose, the CSV import app solution did not work because each lesson unfortunately does not have one unique identifier - I would have to match the imported lessons up to the existing set using two or more fields (e.g. day, client, appointment) and that doesn’t seem to be supported in the CSV import app.

You’re definitely on the right track with the new fields for historical rate and date of raise, etc. The drawback here is that over time there get to be quite a few raises and dates and it seems cumbersome. What I really need is a new table to house the pay rates as in the screen shot attached here. I know how to create this table just fine but don’t know how I would match up the lesson date with the proper pay rate… Any ideas there?

[Rose_K]

I’m sorry the CSV import app didn’t work well for your workflow. You might be able to use a third party service, like Zapier, to create a more customized CSV import workflow than our app currently permits. I’d recommend taking a look at this guide for more information about integrating Airtable with Zapier.

Dean_Wiers-Windemull:

how I would match up the lesson date with the proper pay rate

As for the other situation of associating tracked pay raises for instructors to lesson dates: I came up with a workflow that might work, though it may be a little cumbersome for your use case.

Going off my previous example of Tours and Employees, I updated the Tours table to have the primary column be a date field (for the when that tour occurred).

I then created a new Pay Rates table where I linked the employee and, using the tour’s new primary date field as my visual guide for where a tour lands, manually linked tours that occurred within the date ranges shown on that table(see below):

With that link in place I created a lookup on the Tours table:

The Pay Rates table would then effectively join the two tables together, allowing you to see different rates for different tour dates depending on when a raise was given:

I understand you probably have multiple lessons on the same date so you could differentiate lessons by using a formula as the primary field. I’d recommend concatenating the name of the teacher and the date of the lesson.

I hope that helps, and I’d love to hear any feedback or any improvements you made to the workflow above! If this helped answer your question please mark it as the solution :white_check_mark: .

[Dean_Wiers-Wind]

Thanks Rose. Unfortunately my data source (WellnessLiving) doesn’t integrate with Zapier - otherwise that may have been a great solution. The other solution you offered would in theory work but as you alluded to, it would be too cumbersome to have to manually assign the appropriate pay rate for each lesson, though it could be a temporary work around for me. At this point…I remain stuck without a solution.

peace,

Dean Wiers-Windemuller

owner
Southtown Guitar
cell: (616) 617-4642

	Rose_K Airtable April 13

I’m sorry the CSV import app didn’t work well for your workflow. You might be able to use a third party service, like Zapier, to create a more customized CSV import workflow than our app currently permits. I’d recommend taking a look at this guide for more information about integrating Airtable with Zapier.

Dean_Wiers-Windemull:

how I would match up the lesson date with the proper pay rate

As for the other situation of associating tracked pay raises for instructors to lesson dates: I came up with a workflow that might work, though it may be a little cumbersome for your use case.

Going off my previous example of Tours and Employees, I updated the Tours table to have the primary column be a date field (for the when that tour occurred).

I then created a new Pay Rates table where I linked the employee and, using the tour’s new primary date field as my visual guide for where a tour lands, manually linked tours that occurred within the date ranges shown on that table(see below):

With that link in place I created a lookup on the Tours table:

The Pay Rates table would then effectively join the two tables together, allowing you to see different rates for different tour dates depending on when a raise was given:

I understand you probably have multiple lessons on the same date so you could differentiate lessons by using a formula as the primary field. I’d recommend concatenating the name of the teacher and the date of the lesson.

I hope that helps, and I’d love to hear any feedback or any improvements you made to the workflow above! If this helped answer your question please mark it as the solution :white_check_mark: .