SWK 3402.4-3.1 Graphing Single Systems Design Data


A client and social worker evaluated a technology support intervention for supporting a drinking “quit” attempt by a university undergraduate student who has experienced an alcohol use disorder that began when he was 15 years old. It is a medication assisted therapeutic intervention (MAT) with monthly injection of a medication called Vivitrol, accompanied by Skype medication management/check-in sessions on a weekly basis. The additional therapy is delivered by a phone app that is available on an as-needed basis, on demand, 24/7. The student clicks on the app when experiencing a craving and the app delivers a relapse prevention strategy message tailored to the circumstances. The app also collects data concerning how many craving “clicks” occur in each 24-hour day. The client signs into the app when first waking up daily so that a difference between “0” cravings and no data collected can be determined.

The app collected craving data for 1 week prior to beginning to deliver the relapse prevention strategy messages, delivered these strategy messages for 2 weeks, and collected craving data without delivering strategy messages for 1 more week. Note that during Skype check-in, the client explained the “missed” day (day 15 happened because he started drinking on the night of day 14 and did not wake up for most of day 15. He got back on track for day 16. Together, the client and social worker (you) are scheduled to review the craving data from the past 4 weeks. As the social worker, you need to prepare the graphs and analysis.

The data

Baseline 1 weeks (A1phase)

Day # Craving Count
d1 55
d2 49
d3 50
d4 46
d5 42
d6 44
d7 38


Intervention 4 weeks (B phase)

Day # Craving Count Day # Craving Count
d8 30 d15  
d9 31 d16 7
d10 26 d17 4
d11 18 d18 4
d12 14 d19 2
d13 11 d20 0
d14 10 d21 2


Post-intervention 1 weeks (Aphase)

Day # Craving Count
d22 1
d23 2
d24 4
d25 6
d26 5
d27 3
d28 5


Step 1. Open a blank workbook in Excel. Enter the data into the first two columns, day number in column “A” and craving count in column “B” (consider your variable names carefully, remember the naming rules in Excel). Save your data file with a meaningful file name!!!!

PRO TIP #1:You need to do this in columns rather than rows to make a line graph—the “B column” is the series data, the “A column” replaces the list of categories. Otherwise, you end up with a mess of lines that mean nothing.

PRO TIP #2: To minimize data entry errors, feel free to copy and paste the data from the Word version data file “cravings data.docx”—you need to do this one table column at a time, copy the column (for example, d1 to d7) and paste it into one cell of Excel (for example, cell A2) and it will fill them in as far down as they should go (cells A2:A8). Then, you can remove those dark borders if you like (they won’t hurt anything being left alone) by highlighting the cells, right clicking and selecting format cells, clicking on “border” and “none.”

Step 2. Select all the data (cells A2:B29) and click on “Insert” in the top menu bar. Then, in about the middle of the menu, a section called “Charts” should appear. You want to select the one in the middle row on the left that looks like two zigzag lines overlapping—this is the “line graph” selection. (NOTE: You do not want the one further to the right called “Line” in the “Sparkgraphs” section.) From the options available, select the 4thone over which has the line with data point dots in it. Save your work.

Step 3. A graph should appear in the middle of your worksheet. Try clicking on the border tags (circles) and dragging them wider, narrower, taller, shorter to see what happens to the line. This gets to the issue of “scale” that we have discussed in previous material. For your working chart, try dragging the upper left corner to cell F6 and the lower right corner to cell P25. Save your work.

Step 4. Click on the area called “Chart Title” and change it to something meaningful. Click on the chart so the menu short cuts open on the far right side—select the one that looks like a green plus sign (+). This allows you to edit the chart elements. You want to check the box for axes, axis title, chart title, data labels, and gridlines. See what happens to your chart as you click on them. Save your work.

Step 5. Click on the Axis title boxes and change them to something meaningful—something about time or days on the “x” axis, # of craving episodes on the “y” axis. Save your work.

Step 6. The interpretation of the information will be aided by having lines showing where the phases shifted. This can be accomplished by clicking on “Insert” in the top menu bar, selecting “shapes” and selecting the straight line option. Then click on your “x” axis between day 7 and day 8 and drag the line up to the top line (60 on the “y” axis). This will mark the transition from phase A1to B. If you want to get fancier, you can click on the line and change its color. You can also change its “weight” (how wide/bold the line is) and make it dotted or dashed by clicking on “shape outline” and picking from the options that drop down. Save your work.

Step 7. You can either repeat these steps for the space between day 21 and day 22 to mark the transition from phase B to A2. Or, you can click on the first line, copy, and paste it, then drag it to the correct location. Save your work.

Step 8. Your chart is going to be quite informative, but you can report a lot more information to your client. Click on the chart, copy and paste it into an empty cell on a new worksheet—you open a new worksheet by clicking on the + in the bottom left corner of the screen, next to the “Sheet1” tab. Try pasting it in cell A2 of the new worksheet. (The lines for the transitions may be lost and can be copied and pasted from the earlier graph into the new graph.) Save your work.

Step 9. It would be helpful to show your client the mean number of cravings experienced in each phase of the evaluation. Back on Sheet1, in cell D2, compute the average for the phase A1days 1 to 7 (cells B2:B8). Then repeat this in cell D9 for the phase B days 8 to 21 (cells B9:B22), and again in cell D23 for the phase A2days 22 to 28 (cells B23:B29). Save your work.

Step 10. Insert lines in each phase representing these means on your graph in Sheet2: about 46 for phase A1, about 12 for phase B, and about 3.8 for phase A2. Save your work.

Step 11. Once more, copy the graph to a new location on Sheet2—try starting it in cell A24 perhaps. (You may need to re-copy and paste the lines for the transitions.) Now you can insert estimated trend lines. (Note: while Excel can compute the slope of a line very easily, it is harder to translate slope into a graph without more understanding of slope than we have developed in this course.) Your phase A1 arrow (select the “insert” option with an arrowhead on it) should start at about 55 and end about 40, phase B arrow should start at about 30 and end at about 1, and phase A2arrow should start at about 1 and end about 5.

Step 12. Now is time to conduct your analysis of the information you will present to the client.

  • What are the overall impressions to communicate from the Sheet1 graph (levels in general)?
    • Progress appears to be occurring—levels are dropping during intervention and variability might be declining, as well.
    • There seems to be some lag in terms of response to changes—perhaps about 2-3 days.
    • The relapse drinking episode occurred on a day when the craving episodes were about 10. Discuss what was different about that experience of 10 than days when a relapse did not occur?
  • What is the primary message from the means graph?
    • Progress appears to be occurring in the intervention phase—dropped from a mean of about 46 episodes per day to about 12 per day.
    • The mean is maybe not the best indicator, though since there is so much variability between the first day of intervention (day 8) and the last day (day 21)—from 30 episodes to 2 per day.
    • The mean is lower in the post-intervention phase which MIGHT indicate that the intervention is no longer necessary. However, look at the trends data.
  • What is the primary message from the trend lines graph?
    • oThe trend is in the desired direction with the intervention (it continued a trend that began during the baseline period—possibly a function of the medication and medication management sessions, and/or the possible impact of having committed to making a change and entering the intervention program).
    • oThe trend is going in the undesired direction with removal of the intervention (phase A2).
    • oIt would be worth discussing continuation of the intervention (phase B2) and continuing to monitor the trend data for another period of time.

Step 13. Feel free to compare your file with the file called “cravings finish.xlsx”—check both Sheet1 and Sheet2. If yours looks similar and you came to similar conclusions, you have successfully completed this learning activity. Nicely done!


Icon for the Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License

Social Work 3401/3402 Workbook Copyright © by Dr. Audrey Begun is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License, except where otherwise noted.

Share This Book