Reporting Over Time (Year-Over Year / YoY) with Summary Tables & Scripting Block

[VictoriaPlummer]

Sometimes you want to visualize some metrics. For example, I may want to see the different delivery metrics of email campaigns by different segments across many years. I can do this nicely with blocks if each campaign is delivered to only one segment. However, if I’ve got an email that hits two (or more birds) with one stone…aka one email delivered to multiple segments, this is where my chart gets wonky.

I can’t break out the multiple segments while still looking at my chart over time. (Note: You can do this in a pivot table block, but charts are just way more impactful sometimes.) To get a better chart that does break these segments out, I’ll need a Summary Table, which is a nice way of saying I’ll need a junction table to break out this many-to-many relationship. Each record in the summary table will be a single campaign and segment it was sent to. So, if Campaign 2 was sent to three segments, there will be three records.

This is great, but it presents us with another problem. If I try to summarize all of these records I’ll be double-counting campaigns that appear in the table more than once because they were sent to more than one segment.

To make things easy on myself, I’ve assumed all segments sizes were evenly distributed, so I divided each of my summary metrics by the number of segments in the campaign. For example, if there were 3 Segments in Campaign 2 I’ll divide Deliveries by 3. Now my chart will look like this:

After writing this out, I realized that I may have started with a flawed database design. The summary table shouldn’t be a work-around table to get a nice block. Instead, it should be the main table where I’m actually inputting campaign data broken down by segment (If I had access to that data). Oh well, a lesson learned. Any thoughts on this?

Here’s the example base and script I used to create the summary table and would love other people’s insight into how to make things like this easier.

// pick tables from your base here
let campaignsTable = base.getTable('Campaigns');
let needsSummary = campaignsTable.getView('Needs Summary');
let campaignQuery = await needsSummary.selectRecordsAsync();
let campaignRecords = campaignQuery.records;

let summaryTable = base.getTable('Summary');
let summaryQuery = await summaryTable.selectRecordsAsync();

// Go through all the records in the campaigns table
for (let campaignRecord of campaignRecords) {
    let summarySegments = campaignRecord.getCellValue('Segment');
    let campaignRecordId = campaignRecord.id;
    output.markdown('# Checking Campaign: ' + campaignRecord.getCellValueAsString('Name') + '...');

// For each segment in a campagin, create a new record in the summary table
    for (let summarySegment of summarySegments){

   
        await summaryTable.createRecordsAsync([
            {
                fields: {
                    'Campaign': [{id: campaignRecordId}],
                    'Segment': [{id: summarySegment.id}],
                },
            },
        ])

    }

}

    output.text('Done!');