How To Grab Your Medium Earnings to Excel With One Click?

A free tool to grab your data from Medium.com

Aug 20, 2024

I was looking for an easy way to import Medium Earnings Summary into a spreadsheet but couldn’t find one, so I decided to build a tool for that.

Specifically, I wanted to get earnings of all my stories by month. This way I can easily compare stories are earning over a longer time period against duds that fall off in 1-2 months. With this data, I can focus my efforts on stories and topics that that resonate with my audience.

I have already shared a simple method to grab your story stats in Excel; please look at this story.

As I examined how Medium has built this Earnings Summary page, it became clear that just copying and pasting this data would not work.

The page is created dynamically by selecting the month and year from the dropdown. As I looked more closely, I saw that browser is calling GraphQL API to populate the page.

I decided to build a Chrome browser extension, partly as a learning exercise. I haven’t built one before, so this was a good practical problem to tackle.

This Chrome extension uses GraphQL API to extract each story's title, earnings by month, and lifetime earnings, and then neatly re-organizes the data and stores the table in a CSV file.

Creating a Chrome Browser Extension

I started by creating a directory, MediumChromeExtension, and added the following empty files:

├── background.js
├── graphql.json
├── manifest.json
├── popup.html
├── popup.js
└── style.css

The first file I created was manifest.json. This file defines the metadata and permissions required by your extension.

{
    "manifest_version": 3,
    "name": "Medium Partner Program Earnings Summary",
    "version": "1.0",
    "permissions": ["activeTab", "scripting", "downloads", "storage"],
    "background": {
      "service_worker": "background.js"
    },
    "action": {
      "default_popup": "popup.html"
    }
  }

Next, I wrote graphql.json. This file contains the GraphQL API query to get the earnings data we are seeking to grab. This JSON can be extracted using Chrome developer tools.

You need to replace the username “FinnTropy” with your own, if you want to use this.

{
        "variables": {
            "username": "FinnTropy",
            "first": 10,
            "after": "",
            "startAt": 1704067200000,
            "endAt": 1724059287000
        },
        "query": "query StoryEarningsQuery($username: ID!, $first: Int!, $after: String!, $startAt: Long!, $endAt: Long!) {\n  userResult(username: $username) {\n    __typename\n    ... on User {\n      id\n      postsConnection(\n        first: $first\n        after: $after\n        orderBy: {lifetimeEarnings: DESC}\n        filter: {published: true}\n        timeRange: {startAt: $startAt, endAt: $endAt}\n      ) {\n        __typename\n        edges {\n          node {\n            ...StoryEarningsTable_post\n            ...MobileStoryEarningsTable_post\n            __typename\n          }\n          __typename\n        }\n        pageInfo {\n          endCursor\n          hasNextPage\n          __typename\n        }\n      }\n      __typename\n    }\n  }\n}\n\nfragment StoryEarningsTable_post on Post {\n  id\n  ...StoryEarningsTableRow_post\n  __typename\n}\n\nfragment StoryEarningsTableRow_post on Post {\n  id\n  firstPublishedAt\n  earnings {\n    monthlyEarnings: total(input: {between: {startAt: $startAt, endAt: $endAt}}) {\n      ...moneyUtils_money\n      __typename\n    }\n    lifetimeEarnings: total {\n      currencyCode\n      ...moneyUtils_money\n      __typename\n    }\n    __typename\n  }\n  ...TablePostInfos_post\n  ...usePostStatsUrl_post\n  __typename\n}\n\nfragment moneyUtils_money on Money {\n  currencyCode\n  nanos\n  units\n  __typename\n}\n\nfragment TablePostInfos_post on Post {\n  id\n  title\n  readingTime\n  isLocked\n  visibility\n  ...usePostUrl_post\n  ...Star_post\n  ...PostPreviewByLine_post\n  __typename\n}\n\nfragment usePostUrl_post on Post {\n  id\n  creator {\n    ...userUrl_user\n    __typename\n    id\n  }\n  collection {\n    id\n    domain\n    slug\n    __typename\n  }\n  isSeries\n  mediumUrl\n  sequence {\n    slug\n    __typename\n  }\n  uniqueSlug\n  __typename\n}\n\nfragment userUrl_user on User {\n  __typename\n  id\n  customDomainState {\n    live {\n      domain\n      __typename\n    }\n    __typename\n  }\n  hasSubdomain\n  username\n}\n\nfragment Star_post on Post {\n  id\n  creator {\n    id\n    __typename\n  }\n  __typename\n}\n\nfragment PostPreviewByLine_post on Post {\n  id\n  creator {\n    ...PostPreviewByLine_user\n    __typename\n    id\n  }\n  collection {\n    ...PostPreviewByLine_collection\n    __typename\n    id\n  }\n  ...CardByline_post\n  __typename\n}\n\nfragment PostPreviewByLine_user on User {\n  id\n  __typename\n  ...CardByline_user\n}\n\nfragment CardByline_user on User {\n  __typename\n  id\n  name\n  username\n  mediumMemberAt\n  socialStats {\n    followerCount\n    __typename\n  }\n  ...useIsVerifiedBookAuthor_user\n  ...userUrl_user\n  ...UserMentionTooltip_user\n}\n\nfragment useIsVerifiedBookAuthor_user on User {\n  verifications {\n    isBookAuthor\n    __typename\n  }\n  __typename\n  id\n}\n\nfragment UserMentionTooltip_user on User {\n  id\n  name\n  bio\n  ...UserAvatar_user\n  ...UserFollowButton_user\n  ...useIsVerifiedBookAuthor_user\n  __typename\n}\n\nfragment UserAvatar_user on User {\n  __typename\n  id\n  imageId\n  membership {\n    tier\n    __typename\n    id\n  }\n  name\n  username\n  ...userUrl_user\n}\n\nfragment UserFollowButton_user on User {\n  ...UserFollowButtonSignedIn_user\n  ...UserFollowButtonSignedOut_user\n  __typename\n  id\n}\n\nfragment UserFollowButtonSignedIn_user on User {\n  id\n  name\n  __typename\n}\n\nfragment UserFollowButtonSignedOut_user on User {\n  id\n  ...SusiClickable_user\n  __typename\n}\n\nfragment SusiClickable_user on User {\n  ...SusiContainer_user\n  __typename\n  id\n}\n\nfragment SusiContainer_user on User {\n  ...SignInOptions_user\n  ...SignUpOptions_user\n  __typename\n  id\n}\n\nfragment SignInOptions_user on User {\n  id\n  name\n  __typename\n}\n\nfragment SignUpOptions_user on User {\n  id\n  name\n  __typename\n}\n\nfragment PostPreviewByLine_collection on Collection {\n  id\n  __typename\n  ...CardByline_collection\n  ...CollectionLinkWithPopover_collection\n}\n\nfragment CardByline_collection on Collection {\n  name\n  ...collectionUrl_collection\n  __typename\n  id\n}\n\nfragment collectionUrl_collection on Collection {\n  id\n  domain\n  slug\n  __typename\n}\n\nfragment CollectionLinkWithPopover_collection on Collection {\n  ...collectionUrl_collection\n  ...CollectionTooltip_collection\n  __typename\n  id\n}\n\nfragment CollectionTooltip_collection on Collection {\n  id\n  name\n  slug\n  description\n  subscriberCount\n  customStyleSheet {\n    header {\n      backgroundImage {\n        id\n        __typename\n      }\n      __typename\n    }\n    __typename\n    id\n  }\n  ...CollectionAvatar_collection\n  ...CollectionFollowButton_collection\n  __typename\n}\n\nfragment CollectionAvatar_collection on Collection {\n  name\n  avatar {\n    id\n    __typename\n  }\n  ...collectionUrl_collection\n  __typename\n  id\n}\n\nfragment CollectionFollowButton_collection on Collection {\n  __typename\n  id\n  name\n  slug\n  ...collectionUrl_collection\n  ...SusiClickable_collection\n}\n\nfragment SusiClickable_collection on Collection {\n  ...SusiContainer_collection\n  __typename\n  id\n}\n\nfragment SusiContainer_collection on Collection {\n  name\n  ...SignInOptions_collection\n  ...SignUpOptions_collection\n  __typename\n  id\n}\n\nfragment SignInOptions_collection on Collection {\n  id\n  name\n  __typename\n}\n\nfragment SignUpOptions_collection on Collection {\n  id\n  name\n  __typename\n}\n\nfragment CardByline_post on Post {\n  ...DraftStatus_post\n  ...Star_post\n  ...shouldShowPublishedInStatus_post\n  __typename\n  id\n}\n\nfragment DraftStatus_post on Post {\n  id\n  pendingCollection {\n    id\n    creator {\n      id\n      __typename\n    }\n    ...BoldCollectionName_collection\n    __typename\n  }\n  statusForCollection\n  creator {\n    id\n    __typename\n  }\n  isPublished\n  __typename\n}\n\nfragment BoldCollectionName_collection on Collection {\n  id\n  name\n  __typename\n}\n\nfragment shouldShowPublishedInStatus_post on Post {\n  statusForCollection\n  isPublished\n  __typename\n  id\n}\n\nfragment usePostStatsUrl_post on Post {\n  id\n  creator {\n    id\n    username\n    __typename\n  }\n  __typename\n}\n\nfragment MobileStoryEarningsTable_post on Post {\n  id\n  firstPublishedAt\n  earnings {\n    monthlyEarnings: total(input: {between: {startAt: $startAt, endAt: $endAt}}) {\n      ...moneyUtils_money\n      __typename\n    }\n    lifetimeEarnings: total {\n      currencyCode\n      ...moneyUtils_money\n      __typename\n    }\n    __typename\n  }\n  ...TablePostInfos_post\n  ...usePostStatsUrl_post\n  __typename\n}\n"
}

Next, I wrote background.js. This script reads the graphql.json, listens for messages from the popup.js script and handles data loading and CSV file download.

The script has paging logic, so users with hundreds of stories published should be able to get all of them.

// Function to read the graphql.json file using fetch
async function readJsonFile(filePath) {
    try {
      const response = await fetch(chrome.runtime.getURL(filePath));
      if (!response.ok) {
        throw new Error(`Failed to fetch ${filePath}: ${response.statusText}`);
      }
      const jsonData = await response.json();
      return jsonData;
    } catch (error) {
      console.error('Error reading JSON file:', error);
    }
  }
  
// Function to fetch GraphQL data
  async function fetchGraphQLData(query, variables) {
    const url = 'https://medium.com/_/graphql';
    const headers = {
      "Content-Type": "application/json",
      "Accept": "application/json"
    };
  
    const body = JSON.stringify({
      query: query,
      variables: variables
    });
  
    console.log('Request Payload:', body);
  
    try {
      const response = await fetch(url, {
        method: 'POST',
        headers: headers,
        body: body
      });
  
      if (!response.ok) {
        const errorText = await response.text();
        throw new Error(`HTTP error! status: ${response.status} - ${errorText}`);
      }
  
      const result = await response.json();
      return result;
    } catch (error) {
      console.error('Error fetching GraphQL data:', error);
    }
}
  

async function fetchAllPages(query, variables) {
    let allData = [];
    let hasNextPage = true;
    let cursor = null;
  
    while (hasNextPage) {
        // Set the cursor variable if it exists
        if (cursor) {
            variables.after = cursor;
        }

        const result = await fetchGraphQLData(query, variables);

        if (result && result.data && result.data.userResult) {
            console.log('got data:', result.data.userResult);
            // Append the current page of data to allData
            allData = allData.concat(result.data.userResult.postsConnection.edges.map(edge => edge.node)); 
            
            // Update pagination info
            const pageInfo = result.data.userResult.postsConnection.pageInfo;
            hasNextPage = pageInfo.hasNextPage;
            cursor = pageInfo.endCursor;
            console.log('hasNextPage:', hasNextPage);
            console.log('cursor value:', cursor);
        } else {
            // If the result is null or an error occurred, break the loop
            hasNextPage = false;
            console.error('Error or no data returned', result);
        }
    }
  
    return allData;
}

  
  

  // Listen for messages from the popup
  chrome.runtime.onMessage.addListener(async (request, sender, sendResponse) => {
    if (request.action === 'fetchGraphQLData') {
        try {
            const graphqlData = await readJsonFile('graphql.json');
            const query = graphqlData.query;
            const year = request.year;
            let allMonthlyData = {};

            for (let month = 0; month < 12; month++) {
                const startAt = new Date(year, month, 1).getTime();
                const endAt = new Date(year, month + 1, 1).getTime();

                // Update variables for the current month
                const variables = {
                    ...graphqlData.variables,
                    startAt,
                    endAt
                };

                // Fetch all pages of data for the current month
                const monthData = await fetchAllPages(query, variables);

                if (monthData && monthData.length > 0) {
                    allMonthlyData[`month_${month + 1}`] = monthData.map(post => ({
                        id: post.id,
                        title: post.title,
                        mediumUrl: post.mediumUrl,
                        firstPublishedAt: post.firstPublishedAt,
                        monthlyEarnings: post.earnings.monthlyEarnings.units + post.earnings.monthlyEarnings.nanos / 1e9,
                        lifetimeEarnings: post.earnings.lifetimeEarnings.units + post.earnings.lifetimeEarnings.nanos / 1e9
                    }));
                } else {
                    console.error(`No data returned for month ${month + 1}`);
                }
            }

            // Combine data across all months into CSV format
            const csvData = generateCSV(allMonthlyData);

            if (csvData) {
                saveCSVToFile(csvData, `medium_earnings_${year}.csv`);
                sendResponse({ success: true });
            } else {
                sendResponse({ success: false, error: 'No data to save' });
            }
        } catch (error) {
            console.error('Error processing GraphQL request:', error);
            sendResponse({ success: false, error: error.message });
        }
        return true; // Keep the message channel open until sendResponse is called
    }
});

function convertEpochToDatetime(epochMillis) {
    const date = new Date(epochMillis);
    return date.toISOString().replace('T', ' ').split('.')[0]; // Converts to 'YYYY-MM-DD HH:MM:SS'
}

function generateCSV(data) {
    const headers = ['id', 'title', 'mediumUrl', 'firstPublishedAt', ...Array.from({ length: 12 }, (_, i) => `month_${i + 1}_Earnings`), 'lifetimeEarnings'];
    let rows = [];

    // Collect all post IDs to ensure each row represents a unique post across months
    const allPostIds = new Set();
    Object.values(data).forEach(monthData => {
        monthData.forEach(post => {
            allPostIds.add(post.id);
        });
    });

    allPostIds.forEach(postId => {
        let row = { id: postId, title: '', mediumUrl: '', firstPublishedAt: '', lifetimeEarnings: 0 };

        for (let month = 0; month < 12; month++) {
            const post = data[`month_${month + 1}`]?.find(post => post.id === postId);

            if (post) {
                // Only set these values if they haven't been set yet
                if (!row.title) {
                    row.title = post.title || '';
                    row.mediumUrl = post.mediumUrl || '';
                    row.firstPublishedAt = convertEpochToDatetime(post.firstPublishedAt) || '';
                    row.lifetimeEarnings = post.lifetimeEarnings || 0;
                }
                row[`month_${month + 1}_Earnings`] = post.monthlyEarnings || 0;
            } else {
                row[`month_${month + 1}_Earnings`] = 0;
            }
        }

        rows.push(row);
    });

    const csvRows = [headers.join(',')];
    rows.forEach(row => {
        const csvRow = headers.map(header => `"${row[header]}"`).join(',');
        csvRows.push(csvRow);
    });

    return csvRows.join('\n');
}

function saveCSVToFile(csvContent, filename) {
    const blob = new Blob([csvContent], { type: 'text/csv' });
    const reader = new FileReader();

    reader.onload = function (event) {
        const url = event.target.result;

        chrome.downloads.download({
            url: url,
            filename: filename,
            conflictAction: 'overwrite',
            saveAs: true
        }, () => {
            // Cleanup
            console.log(`Downloaded: ${filename}`);
        });
    };

    reader.readAsDataURL(blob);
}

I wanted a simple UI where users can enter the year (like 2024) and click a button to fetch the data.

For that, I created popup.html page:

<!DOCTYPE html>
<html>
<head>
    <title>Medium Data Fetcher</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            padding: 20px;
        }
        input[type="number"] {
            width: 100%;
            padding: 8px;
            margin-bottom: 10px;
            box-sizing: border-box;
        }
        button {
            padding: 10px 15px;
            background-color: #4CAF50;
            color: white;
            border: none;
            cursor: pointer;
        }
    </style>
</head>
<body>
    <h2>Fetch Medium Partner Program Earnings Data</h2>
    <label for="year">For Year:</label>
    <input type="number" id="year" name="year" placeholder="Enter year (e.g., 2024)">

    <button id="fetchData">Fetch Data</button>

    <script src="popup.js"></script>
</body>
</html>

and popup.js script:

// popup.js
document.addEventListener('DOMContentLoaded', function () {
    const fetchDataButton = document.getElementById('fetchData');
    const yearInput = document.getElementById('year');

    if (fetchDataButton) {
        fetchDataButton.addEventListener('click', () => {
            const year = yearInput.value ? parseInt(yearInput.value) : null;

            if (year) {
                chrome.runtime.sendMessage({
                    action: 'fetchGraphQLData',
                    year: year
                }, (response) => {
                    if (chrome.runtime.lastError) {
                        console.error('Runtime error:', chrome.runtime.lastError.message);
                    } else if (response && response.success) {
                        console.log('Data fetched successfully:', response.data);
                    } else {
                        console.error('Error fetching data:', response.error || 'Unknown error');
                    }
                });
            } else {
                console.error('Year input is required');
            }
        });
    } else {
        console.error('Fetch Data button not found');
    }
});

With these files in place I started testing this new Chrome Browser Extension.

Load and Test the Extension

Open Chrome, go to chrome://extensions/.
Enable "Developer mode" in the top-right corner.
Click "Load unpacked" and select the directory where your extension is located. In my case, it was the MediumChromeExtension folder.

You should see the extension like the one on the left:

You need to enable the extension using the toggle on the bottom corner.

Your extension should now appear in the toolbar or as an icon in Chrome.

You may want to load this dashboard page first.

Click the M extension icon, enter the year, like 2024, and click “Fetch Data” button.

Once you click the button, the Chrome Extension will grab the earnings data and save them into a CSV file. You should see a popup window like this below:

Below is the CSV file contents from my account.

It has all my stories, links and publish dates, as well as monthly earnings neatly organized in the 12 columns, and LifetimeEarnings as the last column.

Conclusion

I built a simple Google Chrome Browser extension to grab my Medium Earnings for the selected year into a big spreadsheet.

This helps me to track which stories have performed over time and where I should focus my time.

Please leave a comment if you find this tool useful or for any improvement ideas.

How To Grab Your Medium Earnings to Excel With One Click?

A free tool to grab your data from Medium.com

Creating a Chrome Browser Extension

Load and Test the Extension

Conclusion

Discussion about this post